In order to fine tune the application(SQL & PL/SQL statements) first we need to understand how optimizer is thinking about the statements. Which means we need to generate the execution plans.In order to generate the execution plans, we need to depend on any one of the following options :
1.explain plan
2.tfprof
3.autotrace
Explain plan is a pre-execution utility which means optimizer determines the execution plan based on the
statistics that are available.
Execution plan can be used either by DBA or Developer to generate the execution plans.
Execution plan contains table access methods, joining techniques & cot involved in executing.
The goal of the DBA or Developer is to minimise the cost.
Tkprof is a post execution utility.
SYS>exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
In oracle 10g, trace file will be generated in udump directory, in 11g in trace directory.
SYS>select sid,serial# from v$session where username='SCOTT';
Once we enable the trace of user session, a trace file will be created in udump or trace directory.
The trace file is in the raw format and it contains table access methods, joining techniques, parse time, elapsed time, logical reads, physical reads etc.
In order to convert the trace file into understandable format we use tkprof utility. We can diable the trace of user session by executing the following command :
SYS>exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
We can also enable the trace at instance level by setting the init.ora parameter:
sql_trace = true
Note : Enabling trace at instance level is not recommanded because it generates a trace file for every user session.
in order to use auto trace, user should have "plustrace" role.
oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
SYS>startup
SYS>desc dba_tables
SYS>select owner,table_name,chain_cnt,num_rows,flash_cache,last_analyzed from dba_tables where owner='SCOTT';
SYS>save usertables.sql
SYS>col table_name for a15
SYS>col owner for a10
SYS>@usertables.sql
SYS>exec dbms_stats.gather_table_stats('&OWNERNAME','&TABLENAME');
enter value for ownname : scott
enter value for tablename : tiger
SYS>@usertables.sql
SYS>desc dba_indexes
SYS>select owner,index_name,index_type,table_name,last_analyzed,degree,visibility from dba_indexes where owner='SCOTT';
SYS>save userindexes.sql
SYS>exec dbms_stats.gather_index_stats(indname=>'&INDNAME',ownname=>'&OWNNAME');
enter value for indname : PK_DEPT
enter value for ownname : scott
SYS>exec dbms_stats.gather_schema_stats(ownname=>'SCOTT');
SYS>@usertables.sql
oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
SYS>conn scott/tiger
scott>insert into salgrade select * from salgrade;
scott>commit;
scott>select count(*) from salgrade;
scott>create bitmap index emp_ind_deptno on emp(deptno);
scott>explain plan set statement_id='stat1'
for
select empno,ename,dname from emp join dept using(deptno);
scott>select * from tab;
scott>desc a
scott>desc plan_table
Note : prior to 10g, in order to use explain plan we need to have plan_table in the user schema who ever is
using the explain plan.
We can create the plan_table by executing the following script :
scott>@$ORACLE_HOME/rdbms/admin/utlxplan.sql
scott>select * from tab;
scott>explain plan set statement_id='stat2'
for
select empno,ename,dname from emp join dept using(deptno);
scott>@$ORACLE_HOME/rdbms/admin/utlxpls.sql
scott>set linesize 160
scott>select * from table(dbms_xplan.display);
scott>set linesize 120
#####Interpreting an execution plan#####
Inner most or most indented statements will be executed first.
scott>select empno,ename,dname from emp join dept using(deptno);
scott>select /*+ parallel(emp,8) */ empno,ename,dname from emp join dept using(deptno);
We can also enable parallel degree at the table level.
scott>desc user_tables
scott>select table_name,last_analyzed,degree from user_tables;
scott>set autotrace off
scott>select table_name,last_analyzed,degree from user_tables;
scott>alter table emp parallel 4;
scott>set autotrace traceonly
scott>select deptno,sum(sal) from emp group by deptno;
scott>alter table emp parallel 1;
scott>select deptno,sum(sal) from emp group by deptno;
scott>set autotrace off
scott>insert into emp select * from emp;
scott>commit;
SYS>exec dbms_stats.gather_schema_stats(ownname=>'SCOTT');
scott>set autotrace traceonly
scott>select deptno,sum(sal) from emp group by deptno;
scott>alter table emp parallel 8;
scott>select deptno,sum(sal) from emp group by deptno;
#####Enabling Query rewrites#####
In order to enable query rewrites, we need to set the following parameters :
query_rewrite_intigrity=enforced
By default this parameters are set from 11g.
scott>create materialized view emp_mview
enable query rewrite
as
select deptno,sum(sal) from emp group by deptno;
SYS>grant create materialized view to scott;
scott>create materialized view emp_mview
enable query rewrite
as
select deptno,sum(sal) from emp group by deptno;
scott>select deptno,sum(sal) from emp group by deptno;
scott>create index emp_mview_index on emp_mview(deptno) reverse;
Note : hints are going to change the behaviour of the optimizer, they improves the performance and at the same time they are also going to degrade the performance if we dont pass them properly.
