|
‘The difference
between what we do and what we are capable of doing would
suffice to solve most of the world's problems.’ Gandhi |
Application tuning in the
Oracle 10g database
Introduction
To enhance Query
Optimization, the OPTIMIZER_DYNAMIC_SAMPING is set to 2 by default.
Automatic PGA Memory
Management is now enabled by default and PGA_AGGREGATE_TARGET is set
to 20 percent. In order to disable the Automatic PGA Memory Management
set the parameter to 0.
Gathering Statistics
On Oracle Database 10g, you
can gather statistics on dictionary tables (both fixed and real) to
get the best performance. You use the DBMS_STATS.GATHER_DATABASE_STATS
procedure with GATHER_SYS argument set to TRUE or
DBMS_STATS.GATHER_DICTIONARY_STATS. To use this, you should have the
ANALYZE ANY DICTIONARY system privilege. For example:
SQL> BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(options=’GATHER
AUTO’);
END;
/ --
Note use GATHER only if you are using release 8i
Automatic Tuning Optimizer
(ATO): When the optimizer is tuning a SQL statement using ATO, it is
called Automatic SQL Tuning. To perform automatic SQL tuning, see the
following hands-on.
Hands-On #1-Performing
Automatic SQL tuning
Create a binding variable
and then move your query into it.
SQL> VARIABLE my_query
VARCHAR2(1000)
SQL> BEGIN
:my_query := ‘SELECT
ename FROM iself.emp WHERE empno = 100;’
END;
/
Use the DBMS_SQLTUNE
package to create a tuning task by calling the CREATE_TUNING_TASK
function. This procedure creates an advisor task and sets its
corresponding parameters according to the user-provided input
arguments. To execute this you need one more binding variable to keep
your task name.
SQL> VARIABLE my_task
VARCHAR2(100)
SQL> BEGIN
:my_task :=
DBMS_SQLTUNE.create_tuning_task (
SQL_TEXT => :my_query,
BIND_LIST =>
SQL_BINDS(anydata.ConvertNumber(100)),
USER_NAME => ‘ISELF’,
SCOPE => ‘COMPREHENSIVE’,
TIME_LIMIT => 60,
TASK_NAME => ‘my_tuning_task’,
DESCRIPTION => ‘Query
on EMP table …’);
END;
/
Invoke the
EXECUTE_TUNING_TASK procedure to start the tuning process.
SQL> BEGIN
DBMS_SQLTUNE.execute_tuning_task
(TASK_NAME=>:my_task);
END;
Call the REPORT_TUNING_TASK
function to visualize the tuning results.
SQL> SQL> SELECT
DBMS_SQLTUNE.report_tuning_task
(TASK_NAME=>:my_task)
FROM dual;
When the SQL Tuning Advisor
recommends a SQL Profile, then create the SQL Profile by calling the
ACCEPT_SQL_PROFILE function, which stores it in the data dictionary.
You should have the CREATE ANY SQL PROFILE privilege.
SQL> VARIABLE my_profile
VARCHAR2(1000)
SQL> BEGIN
:my_profile :=
DBMS_SQLTUNE.accept_sql_profile
(TASK_NAME => ’my_tuning_task’);
END;
/
SQL> SELECT :my_profile
FROM dual;
Hands-On # 2-Advantage of
MVIEW
Let see the advantage of
creating mview.
Connect as the ISELF user.
SQL> CONNECT iself/schooling
Create a materialized view
called my_mview using the EMP table. This view summarize department
total salaries.
SQL> CREATE MATERIALIZED
VIEW my_mview
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS SELECT deptno, SUM(sal)
as salary
FROM EMP
GROUP BY deptno;
Gather statistics against
the new materialized view.
SQL> BEGIN
DBMS_STATS.gather_table_stats(USER,
‘MY_MVIEW’);
END;
/
Query from new materialized
view.
SQL> SELECT * FROM
my_mview;
Execute EXPLAIN PLAN
against the query in the previous step.
SQL> EXPLAIN PLAN FOR
SELECT deptno, SUM(sal) as
salary
FROM EMP
GROUP BY deptno;
SQL> SELECT * FROM (
dbms_xplan.display );
Now, use the
DBMS_MVIEW.EXPLAIN_REWRITE procedure against the query and the
MY_MVIEW materialized view, and then query the REWRITE_TABLE. If you
do not have REWRITE_TABLE, then run the utlxrw.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/utlxrw.sql
SQL> BEGIN
DBMS_MVIEW.explain_rewrite
(
‘SELECT deptno, SUM(sal)
as salary FROM EMP GROUP BY deptno’,
‘ISELF.MY_MVIEW’, ‘This
is my explain_rewrite practice…’);
END;
/
SQL> COL message FORMAT
a35
SQL> SELECT message,
original_cost, rewritten_cost
FROM rewrite_table;
|
“Violence as a way
of achieving racial justice is both impractical and immoral. It
is impractical because it is a descending spiral ending in
destruction for all. It is immoral because it seeks to humiliate
the opponent rather than win his understanding; it seeks to
annihilate rather than to convert. Violence is immoral because
it thrives on hatred rather than love.” Martin Luther King Jr. |
Questions:
Questions on
Application
tuning in the Oracle 10g database
Q: What is the
OPTIMIZER_DYNAMIC_SAMPING setting default?
Q: How do you disable the
Automatic PGA Memory Management?
Q: How do you gather
statistics on dictionary tables in the Oracle 10g Database?
Q: What is the Automatic
Tuning Optimizer (ATO)?
Q: How do you perform
automatic SQL tuning?
Q: How do you use the
DBMS_SQLTUNE package to create a tuning task by calling the
CREATE_TUNING_TASK function?
Q: How do you use the
EXECUTE_TUNING_TASK procedure to start the tuning process?
Q: How do you call the
REPORT_TUNING_TASK function to visualize the tuning results?
Q: How do you store a SQL
profile in the data dictionary?
|