Lets get more into Sql Plans
Ÿ
Re-analyze the schema statistics (done automatically, starting in 10g) –
auto stats
Ÿ object not
anlyze
Ÿ
cursor got invalidated
Ÿ
bind sensitive
Ÿ
database paramter change Changing an optimizer parameter (optimizer_mode,
optimizer_index_cost_adj)
Ÿ shared pool flush ::: new plan generation
Index got invalidated
Enabling dynamic sampling
Enabling parallelism
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
session tracing
•If you only know the package, enable trace 10046 before
execute the package
1.Example:
2.SQLPLUS>alter session set events „10046 trace name
context forever, level 12‟;
3.SQLPLUS>exec PKG_BL_RCR_REV_FACT.SP_BL_RCR_REV
4.After done, go the “user_dump_dest” and get the trace
file.
5.OS> cd/u01/app/oracle/diag/rdbms/dwdb/DWDB1/trace
6.OS>
tkproftracefile_nameoutputfile_nameexplain=hwdw/password waits=y
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Using hints
/*+ gather_plan_statistics */
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM
x;
>>>>>>>>>>>>>>>>>>>>>>>>>>>>> cursor
Set long 20000000
Set pagesize0
Set linesize200
set linesize 121
select sql_id from v$session where sid=112;
SELECT * FROM TABLE(dbms_xplan.display_cursor('sql_id',1));
select * from table(dbms_xplan.display_cursor); -- own session where query was run
SELECT * FROM
TABLE(dbms_xplan.display_cursor('gpv3kb4n2f2q1'));
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL,
'last allstats'));
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from
TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD_number,'ALL'));
>>>>>>>>>>>>>>>>>>>>>>>>>>>>> v$sql_plan
col PLAN FOR a150
SELECT
RPAD('(' ||
p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
RPAD(LPAD (' ',
2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
NVL2(p.plan_object_owner||p.plan_object_name, '(' ||
p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, '
|| p.plan_CARDINALITY || ' rows)','') || ' ' ||
NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' ||
p.plan_partition_stop,'') ||
NVL2(p.plan_time,
p.plan_time || '(s)','') AS PLAN
FROM
v$sql_plan_monitor p
WHERE sql_id =
'81c1xvnmsyr64'
AND sql_exec_id =
16777270
AND
sql_exec_start=TO_DATE('11-jan-2012 05:59:27','dd-mon-yyyy hh24:mi:ss')
ORDER BY
p.plan_line_id, p.plan_parent_id;
>>>>>>>>>>>>>>>>>>>>>>
explain plan for
EXPLAIN PLAN
1) Explain plan displays execution plan(sequence of
operation) chosen by oracle optimizer
To run statement of select , insert , update ,delete.
2)
CREATING EXPLAIN
TABLE - UTLXPLAN.SQL
HIDE PARALLEL QUERY
INFORMATION – UTLXPLS.SQL
(TO QUERY THE
PLAN_TABLE)
SHOW PARALLEL QUERY
INFORMATION – UTLXPLP.SQL
(TO QUERY THE
PLAN_TABLE)
3)
EXPLAIN Plan ****INTO
PLAN_TABLE SET STATEMENT_ID = ‘ ‘ ******** FOR SELECT ……………
NOTE : THE EXECUTION PLAN FOR EVERY SQL STATEMEN IS
GENERATED AND STORED BY DEFAULT IN V$SQL_PLAN.
BUT THE EXECUTION PLAN IS STORED ONLY TILL THE STATEMENT IS IN
LIBRARY CACHE.
select * from table(dbms_xplan.Display);
select plan_table_output from table(dbms_xplan.display()) ;
select plan_table_output
from TABLE(
dbms_xplan.display
(
'PLAN_TABLE',
(select rawtohex(address)||'_'||child_number x
from
v$sql
where
sql_text='select * from t t1 where object_id > 32000' ),
'serial' ) );
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
awr ( 11g.. will list all sql plans . historical )
Ÿ check from dba_hist_sqltext if sqlid has been AWR. If its not loaded create a
manual snapshot
Ø exec
dbms_workload_repository.modify_snapshot_settings(topnsql=>’MAXIMUM’);
Ø exec
dbms_workload_repository.create_snapshot();
Ø exec
dbms_workload_repository.modify_snapshot_settings(topnsql=>’DEFAULT’);
Ø select
sql_id,sql_text from dba_hist_sqltext where sql_id=’232xssd22232’;
select * from
TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
select * from
TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid',’plan_hash_value’));
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
set auto trace
SQL*PLUS AUT0TRACE / Explain plan
1) RUN SQLPLUS/
ADMIN / PLUSTRCE.SQL ? creates plus
trace role .
2) GRANT
PLUSTRACE TO SCOTT
3) SET
AUTOTRACE ON / OFF / TRACEONLY
ON EXPLAIN / ON STATISTICS
Set autotrace traceonly explain statistics
Note : TO SET AUTO TRACE WE MUST HAVE PLAN_TABLE AND
PLUSTRACE ROLE.
1)select SQL_TEXT, hash_value from V$sqltext where
ADDRESS='0700000B0BDC7B60' order by piece;
pause this query shows columns useful in Parallel Query or
distributed query
col id for 99
col Par for 99
col pos for 99
col "Table Queue" for a10
col Operation format a24
col Options format
a12
col Object format
a12
col "Row Source" for a10
col "Inter Step SQL" for a16
col "Parallel Oper Type" for a20
col "Row Dist" for a11
set long 500
pause Position has total cost in row when ID=0 if CBO is
used
select id "Id",
parent_id
"Par",
position
"Pos",
lpad(' ',
2*LEVEL) || OPERATION ||
decode(ID, 0,
' Cost='||POSITION) "Operation",
OPTIONS
"Options",
OBJECT_NAME
"Object",
OBJECT_NODE
"Table Queue",
other_tag
"Parallel Oper Type",
distribution
"Row Dist",
other
"Inter Step SQL"
from v$sql_plan
where address =
'&addr';
2)select
'--------------------------------------------------------------------------------'
from dual
union all
select '| Operation | PHV/Object Name |
Rows | Bytes| Cost |' as "Optimizer Plan:"
from dual
union all
select
'--------------------------------------------------------------------------------'
from dual
union all
select /*+ RULE */ *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id,
0, '----- '||to_char(hash_value)||' -----'
,
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1,
bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain
plan"
from
v$sql_plan
where
hash_value= nvl('&hash_value','0')
order by id
)
union all
select
'--------------------------------------------------------------------------------'
from dual;
set verify on
set heading on
undefine hash_value
set heading on;
è
Historical Sql plan :
SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME,
ELAPSED_TIME_DELTA, STAT.SNAP_ID, SS.END_INTERVAL_TIME FROM DBA_HIST_SQLSTAT
STAT, DBA_HIST_SQLTEXT TXT,
DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID
= TXT.DBID AND SS.DBID = STAT.DBID
AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND
STAT.SNAP_ID = SS.SNAP_ID AND SS.BEGIN_INTERVAL_TIME >= sysdate-60
AND UPPER(STAT.SQL_ID) = '&SQL_ID'
AND PLAN_HASH_VALUE = &PLAN_HASH
ORDER BY ELAPSED_TIME_DELTA DESC;
select s.begin_interval_time, s.end_interval_time ,
q.snap_id, q.dbid, q.sql_id,
q.plan_hash_value, q.optimizer_cost, q.optimizer_mode
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.sql_id =
'bpkgrf2awmhfc'
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-8 and sysdate
order by s.snap_id desc;
select sql_id, sql_text from dba_hist_sqltext where
upper(sql_text) like '%CUSTOMERS%';
SELECT tf.* FROM DBA_HIST_SQLTEXT ht, TABLE
(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,NULL, NULL,
'ALL' )) tf WHERE ht.sql_id='&1'
1) select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where
sql_text like ‘%your query%’;
-->> or see all available sql
variable snap_id_1 number;
variable snap_id_2 number;
exec :snap_id_1:=789;
exec :snap_id_2:=786;
select a.sql_id,
a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
from
dba_hist_sqlstat a, dba_hist_sqlstat b
where (a.snap_id =
:snap_id_1 and b.snap_id = :snap_id_2)
and (a.sql_id =
b.sql_id)
and
(a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;
select * from V$SQL_SHARED_CURSOR where sql_id='32927hsaftay0';
--> see sql whoes plan has change
select distinct sql_id, plan_hash_value, f snapshot,
(select begin_interval_time
from dba_hist_snapshot
where snap_id = f) snapdate
from
(select sql_id, plan_hash_value,
first_value (snap_id) over (partition by sql_id, plan_hash_value
order by snap_id) f
from (select sql_id,
plan_hash_value, snap_id,
count (distinct
plan_hash_value) over (partition by
sql_id) a
from dba_hist_sqlstat
where plan_hash_value > 0
order by sql_id)
where a > 1)
order by sql_id,
f;
Select Sql_Id,Count(*) From
(Select Distinct Sql_Id,Plan_Hash_Value From
Dba_Hist_Sqlstat where plan_hash_value <> 0 )
Group By Sql_Id
having count(*) > 1
2) get plan hash
value :
select snap_id, plan_hash_value
from
dba_hist_sqlstat
where snap_id in
(789, 786) and sql_id = 'dcxm4kr1urg93'
order by snap_id
desc;
or
select s.begin_interval_time, s.end_interval_time ,
q.snap_id, q.dbid, q.sql_id,
q.plan_hash_value, q.optimizer_cost, q.optimizer_mode
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.dbid = 1234567890 and q.sql_id = ‘12345xxxxxxxx’
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-2 and sysdate
order by s.snap_id desc;
3) compare explain plan of different hash value using below
query :
select id, operation, options, object_name, cost
from dba_hist_sql_plan
where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’
and plan_hash_value = <plan_hash_value1>
select * from
table(dbms_xplan.display_awr('32927hsaftay0',714092912));
select * from table(dbms_xplan.display_awr('dcxm4kr1urg93',1582437882));
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node,
begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','b55nnb6ykd2tr')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
SPOOL coe_gen_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000
PAGES 100 LONG 8000000 LONGC
800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000
NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_gen_sql_profile.sql 11.4.3.4
2011/07/20 carlos.sierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights
reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_gen_sql_profile.sql
REM
REM DESCRIPTION
REM Inputs one SQL_ID and outputs a set of scripts generated
by
REM coe_xfr_sql_profile.sql. One for each known plan of
given
REM SQL_ID.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as SYSDBA or user with access
to
REM data dictionary.
REM 2. Execute script coe_gen_sql_profile.sql passing SQL_ID
REM (parameter can be passed inline or until requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_gen_sql_profile.sql [SQL_ID];
REM SQL> START coe_gen_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_gen_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_gen_sql_profile.log
REM 2. Be aware that using DBMS_SQLTUNE requires a license
for
REM Oracle Tuning Pack.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id0 = '&1';
PRO
SET TERM OFF;
PRO
PRO Plans from GV$SQL
PRO ~~~~~~~~~~~~~~~~~
SELECT plan_hash_value,
ROUND(SUM(elapsed_time)/SUM(executions)/1e6, 3) avg_et_secs,
ROUND(SUM(cpu_time)/SUM(executions)/1e6, 3) avg_cpu_secs,
ROUND(SUM(user_io_wait_time)/SUM(executions)/1e6, 3)
avg_io_secs,
ROUND(SUM(buffer_gets)/SUM(executions)) avg_buffer_gets,
ROUND(SUM(disk_reads)/SUM(executions)) avg_disk_reads,
ROUND(SUM(rows_processed)/SUM(executions)) avg_rows,
SUM(executions) executions
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id0.')
AND executions > 0
GROUP BY
plan_hash_value
ORDER BY
2, 3;
PRO
PRO
PRO Plans from DBA_HIST_SQLSTAT
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT plan_hash_value,
ROUND(SUM(elapsed_time_total)/SUM(executions_total)/1e6, 3)
avg_et_secs,
ROUND(SUM(cpu_time_total)/SUM(executions_total)/1e6, 3)
avg_cpu_secs,
ROUND(SUM(iowait_total)/SUM(executions_total)/1e6, 3)
avg_io_secs,
ROUND(SUM(buffer_gets_total)/SUM(executions_total))
avg_buffer_gets,
ROUND(SUM(disk_reads_total)/SUM(executions_total))
avg_disk_reads,
ROUND(SUM(rows_processed_total)/SUM(executions_total))
avg_rows,
SUM(executions_total) executions
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id0.')
AND executions_total > 0
GROUP BY
plan_hash_value
ORDER BY
2, 3;
PRO
PRO
PRO Plans from GV$SQL and/or DBA_HIST_SQLSTAT
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BEGIN
FOR i IN (WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id0.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id0.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id0.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id0.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST)
LOOP
DBMS_OUTPUT.PUT_LINE('-- phv:'||i.plan_hash_value||'
avg_et_secs:'||i.avg_et_secs);
DBMS_OUTPUT.PUT_LINE('@@coe_xfr_sql_profile.sql
&&sql_id0. '||i.plan_hash_value||';');
END LOOP;
END;
/
SPO coe_gen_sql_profile_&&sql_id0..sql;
/
SPO OFF;
@coe_gen_sql_profile_&&sql_id0..sql;
HOS zip -mT coe_gen_sql_profile_&&sql_id0
coe_gen_sql_profile.log coe_xfr_sql_profile.log
coe_gen_sql_profile_&&sql_id0..sql
coe_xfr_sql_profile_&&sql_id0._*.sql
HOS zip -d coe_gen_sql_profile_&&sql_id0
coe_xfr_sql_profile.log
SET TERM ON;
HOS unzip -l coe_gen_sql_profile_&&sql_id0
UNDEFINE 1 sql_id0
CL COL
PRO
PRO coe_gen_sql_profile completed.
-------------------------------------------------------------------------------------------
Cost = (#SRds *
sreadtim +
#MRds *
mreadtim +
#CPUCycles /
cpuspeed ) / sreadtim
where:
#SRDs is the number of single block reads
#MRDs is the number of multi block reads
#CPUCycles is the number of CPU Cycles *)
sreadtim is the single block read time
mreadtim is the multi block read time
cpuspeed is the CPU cycles per second
CPUCycles includes CPU cost of query processing (pure CPU
cost) and CPU cost of data retrieval (CPU cost of the buffer cache get).
assume that our average disk I/O delay is 15
milliseconds.
A “cost” of 4 would mean that the optimizer expects the
query to take roughly
60 milliseconds to complete.
-------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 57 | 4
(0)| 00:00:01 |
| 1 | NESTED LOOPS | |
1 | 57 | 4
(0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP |
1 | 37 | 3
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
1 | 20 | 1
(0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------
cost = number of single block reads
card = number of rows
##########################
11g Automatic Plan Capture #############################
When automatic plan capture is enabled, the system
automatically creates and maintains the plan history for SQL statements using n
information provided by the optimizer. The plan history will include relevant
information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind
variables, and compilation environment.
The initial plan generated for a SQL statement is marked as
accepted for use by the optimizer, and represents both the plan history
and the SQL plan baseline. All
subsequent plans will be included in the plan history, and those plans that are
verified not to cause performance
regressions will be added to the SQL plan baseline during the SQL plan baseline
evolution phase.
To enable automatic plan capture, set
There’s lot more to it than just the parameter. There’s
something called optimizer_capture_sql_plan_baselines which is FALSE by
default and should stay FALSE, else we
have no control over the plans. Also, If its’ set to TRUE, any repeating SQL
would have its plan saved as a base lines.
Secondly, we have to load the proven plans manually using
any of the following methods.
From cursor cache (using sqlid in the sqlarea)
creating a SQL tuning set and loading baselines from it.
This gives the flexibility of adding and removing the SQLs from the tuning set.
From AWR as well. You
can use DBMS_SQLPLAN & DBMS_SPM packages. You can go through tuning guide
or SQL pl/sql package reference for examples as to how to
use the procedures inside the packages.
You can also use OEM. Anything you do, basic thing is that
the PLAN has to be the best one and that you’d want to lock in which turns
out to be a baseline that can be seen in
the views like dba_sql_plan_baselines as fixed or enabled or whatever
Automatic plan capture will not occur for a statement if a
stored outline exists for it and is enabled and the parameter
use_stored_outlines is TRUE. In this case, turn on incremental capture of plans
into an STS using the function capture_cursor_cache_sqlset() in the
DBMS_SQLTUNE package. After you have collected the plans for your workload into
the STS, manually create SQL plan baselines using the method described earlier.
Then, disable the stored outlines or set use_stored_outlines to FALSE. From now
on, SPM will manage your workload and stored outlines will not be used for
those statements.
OPTIMIZER_USE_SQL_PLAN_BASELINES – msut be true
