|
Application tuning in the Oracle 10g database
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.
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:
Examples of using the
dbms_stats package:
To gather statistics for entire database:
SQL> declare
v_time varchar2(50);
begin
-- Display starting time. Make sure set
serveroutpu on
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
into v_time from dual;
dbms_output.put_line('Started: ' || v_time);
-- gather statistics for entire database.
dbms_stats.gather_database_stats();
-- Display ending time:
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
into v_time from dual;
dbms_output.put_line(' Ended: ' || v_time);
end;
/
Example of gather statistics for entire schema
(tables and indexes).
SQL> declare
v_time varchar2(50);
begin
-- Display starting date and time.
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
into v_time from dual;
dbms_output.put_line('Started: ' || v_time);
-- Gather statistics for a schema
dbms_stats.gather_schema_stats(
ownname => 'FALCON',
method_opt => 'FOR ALL COLUMNS SIZE 1', -
granularity => 'ALL',
options => 'GATHER',
cascade => TRUE);
-- Display end date and time
select to_char(sysdate(), 'DD-MM-YY HH24:SS:MI')
into v_time from dual;
dbms_output.put_line(' Ended: ' || v_time);
end;
/
Example of gather statistics for entire schema
(tables and indexes).
--Make sure your buffer size is big enough.
SQL> SET SERVEROUTPUT ON
SIZE 1000000
Create a PL/SQL
procedure to gather statistics for each schema in
the database.
SQL> CREATE OR REPLACE
PROCEDURE gather_stats
IS
v_time VARCHAR2(50);
CURSOR c_schema IS
SELECT username FROM dba_users;
BEGIN
FOR v_user IN c_schema LOOP
-- Display Starting Time.
SELECT to_char(sysdate(), 'DD-MM-YY
HH24:SS:MI')
INTO v_time FROM dual;
dbms_output.put_line('Started: ' || v_time);
-- Gather statistics for each schema.
dbms_stats.gather_schema_stats(
ownname => v_user.username,
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'ALL',
options => 'GATHER',
cascade => TRUE);
-- Display ending time.
SELECT to_char(sysdate(), 'DD-MM-YY
HH24:SS:MI')
INTO v_time FROM dual;
dbms_output.put_line(' Ended: ' || v_time);
END LOOP;
END gather_stats;
/
You should grant the following object and system
privileges to the user (For example: dba1) who was
assigned to run the gather_stats procedure.
SQL> GRANT execute ON
gather_stats TO dba1;
SQL> GRANT ANALYZE ANY TO dba1;
Hands-On #2:
Create a binding variable and then move your query
into it.
SQL> VARIABLE my_query VARCHAR2(1000)
SQL> BEGIN
:my_query := �SELECT ename FROM
oracle.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 =>
�ORACLE�,
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 a SQL Profile is recommended by the SQL Tuning
Advisor, 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:
Let see the advantage of creating mview.
Connect as the ORACLE user.
SQL> CONNECT oracle/learning
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, then query the REWRITE_TABLE. If you don�t
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�,
�ORACLE.MY_MVIEW�, �This is my
explain_rewrite practice��);
END;
/
SQL> COL message FORMAT a35
SQL> SELECT message, original_cost, rewritten_cost
FROM rewrite_table;
|
