PERFORMANCE TUNNING
How to generate explain plan?
set autotrace tracelonly explain.
How to check explain plan for past ran SQLs and currently running SQLs statements?
select * from TABLE(dbms_xplan.display_awr('&SQL_ID'));
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));
How to check SQL Execution Plan is in the Plan History or SQL Plan Baseline?
Select sql_id from v$sql where sql_text like ‘select%’;
To check plan is in the baseline:
select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines where SQL_TEXT like '%SPM_TEST%'
Query the DBA_SQL_PLAN_BASELINES.ACCEPTED
If ACCEPTED=NO , the plan is in the history and will not be used by the optimizer and If ACCEPTED=YES the plan is in the Plane Baseline and will be used by the optimizer
Load plans from SQL SET
DBMS_SPM.LOAD_PLANS_FROM_SQLSET()
Load plans from Cursor Cache
DBMS_SPM.LOAD_PLANS_FROM_CURSOR(‘SQL_ID’)
To change the status of plans in SQL history to ACCPETED which in turn moves them to SQL BASELINES
DBMS_SPM.EVOLVE_SQL_PLANS_BASELINE()
Use display_sql_plan_baseline to see Baseline Plan
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
What are the init parameters related to performance/optimizer?
To influence the behavior of optimizer you can set the following parameters:
optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;
db_file_multiblock_read_count=64
Why does the same query takes different amount of time to run?
There are three broad reasons that queries take longer at different times. Either you are getting different performance because the system is under a different sort of load, you are getting different performance because of data volume changes, or you are getting different performance because you are getting different query plans
Different Data Volume
When you generate your initial timings, are you using data volumes that are similar to the volumes that your query will encounter when it is actually run? If you test a query on the first of the month and that query is getting all the data for the current month and performing a bunch of aggregations, you would expect that the query would get slower and slower over the course of the month because it had to process more and more data. Or you may have a query that runs quickly outside of month-end processing because various staging tables that it depends on only get populated at month end. If you are generating your initial timings in a test database, you'l very likely get different performance because test databases frequently have a small subset of the actual production data.
Different System Load
Different Query Plans
Over time, your query plan may also change because statistics have changed (or not changed depending on the statistic in question). Normally, that indicates that Oracle has found a more efficient plan or that your data volumes have changed and Oracle expects a different plan would be more efficient with the new data volume. If, however, you are giving Oracle bad statistics (if, for example, you have tables that get much larger during month-end processing but you gather statistics when the tables are almost empty), you may induce Oracle to choose a very bad query plan. Depending on the version of Oracle, there are various ways to force Oracle to use the same query plan. If you can drill down and figure out what the problem with statistics is, Oracle probably provides a way to give the optimizer better statistics.
Consolidating Multiple Trace Files
Use the trcsess command to merge multiple trace files into a single trace file.
c:\trace> trcsess output=combined.trc session=196.614 orcl1_ora_8432_mytrace1.trc
orcl1_ora_8432_mytrace2.trc
The trcsess command shown here combines two trace files generated for a session into a single trace file. The session parameter identifies the session with a session identifier, consisting of the session index and session serial number, which you can get from the V$SESSION view.
Tracing a SQL Session
execute dbms_monitor.session_trace_enable(session_id=>138,serial_num=>242,waits=>true,binds=>false);
Setting a Trace in a Running Session
You can set a trace in a running session using the operating system process ID (PID), with the help of the
oradebug utility. Once you identify the PID of the session you want to trace, issue the following
commands to trace the session.
SQL> connect / as sysdba
SQL> oradebug setospid <SPID>
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever,level 12
SQL> oradebug event 10046 trace name context off
Why SQL statements regress?
Optimizer related-
->Stale/missing statistics, Improper optimizer configuration Upgraded DB to new optimizer, Changing statistics( refresh) ,Changing data (plan do not scale with data)
,Bind sensitive SQL with bind peaking ,Not parallized (not scalling to large data) ,Improper parallized (skew plan)
Resource and contention issue related-
->Hardware resource crunch such as CPU, Memory, IO and network ,Data fragmentation ,Logical contention ,row lock or latch contention , Block update contention.
Database is Hung?
->Temp tbs is full and no space for sorting.
->Undo tbs is full and no space to generate redo.
->Archive log mount point or FRA full.
->Online redo log file is missed.
->Physical memory is exhausted or CPU utilization is high.
->No space left on $ORACLE_HOME mount point to generate audit trace file.
