iSelfSchooling - Application tuning in the Oracle 10g database

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 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 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 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, 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’,

‘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;