오라클에서 수행되는 cursor의 수행 정보와 이와 관련된 각종 statistic 정보를 확인할 수 있는 view가 몇개 있습니다.
다음은 해당 view에 대한 비교와 column 정보들, 그리고 해당 view를 이용한 몇개의 sql script 들입니다.
각 operation에 대한 execution statistics 정보를 제공한다.
1)EXECUTIONS:
Child cursor의 수행된 횟수 이다. 따라서 동일한 SQL이더라도 optimizer 환경이나 bind variable size 등의 변경에 의해 다른 child cursor를 가질 수 있으므로 이에 대한 고려도 필요하다.
두개의 세션에서 동일한 SQL을 2회 수행하는 경우나, 같은 세션에서 동일한 SQL을 2회 수행한 경우 동일하게 2의 값을 갖는다.
2)LAST_OUTPUT_ROWS and OUTPUT_ROWS:
LAST_OUTPUT_ROWS는 해당 operation에 의해 마지막에 처리된 실제 row 수 이며, OUTPUT_ROWS는 누적 합이다.
3)LAST_DISK_READS and DISK_READS:
LAST_DISK_READS는 해당 operation이 disk를 읽은 횟수를 의미하며, DISK_READS는 누적 합이다.
V$SQL_PLAN_STATISTICS_ALL:
다음의 관련 view의 데이터에 대한 전체적인 정보를 갖는다.
V$SQL_PLAN (all operations, objects, cost, cardinality etc)
V$SQL_PLAN_STATISTICS (execution statistics as above)
V$SQL_WORKAREA (memory usage)
1) estimated_optimal_size
해당 operation을 메모리상에서 처리하기 위해 측정된 memory size (KB)
2) last_memory_used
cursor의 마지막 execution 동안 사용된 memory (KB)
3) active_time
Average active time (in centi-seconds)
다음의 SQL script는 특정 SQL cursor에 대한 plan과 관련 statistic 정보를 보여줍니다.
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on
numwidth 10;
alter session set "_complex_view_merging"=false;
select hash_value||decode(child_number, 0, '', '/'||child_number)
sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;
select '| Operation | Name | Starts | E-Rows | A-Rows | Buffers |
Reads | Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */
select
'------------------------------------------------------------------------------------------------------------------------'
from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth))||operation||
decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'||
rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'||
lpad(decode(starts,null,' ',
decode(sign(starts-1000), -1, starts||' ',
decode(sign(starts-1000000), -1, round(starts/1000)||'K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||'M',
round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(outrows,null,' ',
decode(sign(outrows-1000), -1, outrows||' ',
decode(sign(outrows-1000000), -1, round(outrows/1000)||'K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(crgets,null,' ',
decode(sign(crgets-10000000), -1, crgets||' ',
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
lpad(decode(reads,null,' ',
decode(sign(reads-10000000), -1, reads||' ',
decode(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(writes,null,' ',
decode(sign(writes-10000000), -1, writes||' ',
decode(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(etime,null,' ',
decode(sign(etime-10000000), -1, etime||' ',
decode(sign(etime-1000000000), -1, round(etime/1000000)||'M',
round(etime/1000000000)||'G'))), 8, ' ') || '|' as "Explain
plan"
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from
v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value =
&hashvalue
and
p.CHILD_NUMBER= 0
and
p.hash_value = pa.hash_value(+)
and
pa.child_number(+) = 0 )
union all
select
'------------------------------------------------------------------------------------------------------------------------'
from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,'', substr(object_node,length(object_node)-3,1)
|| ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other
"SLAVE SQL"
from v$sql_plan vp
where other is not NULL
and hash_value = &hash_value
and CHILD_NUMBER= 0;
qeps.sql과 qep.sql을 생성해 수행하면 SQL의 성능을 평가하는 8개의 항목에 대해 지정된 갯수의 SQL과 그에 따른 statistic 정보를 보여줍니다. init parameter인 statistics_level를 ALL로 설정해야 정상적인 결과를 보여줍니다.
--
-- Script: qeps.sql
--
-- Script to report the explain plan for the most expensive N SQL statements
-- based on user specified criteria:
--
-- buffer_gets - 1
-- CPU time - 2
-- disk_reads - 3
-- rows_processed - 4
-- executions - 5
-- parse calls - 6
-- Buffers/Exec - 7
-- Cost per row - 8
--
-- Usage: start qeps.sql
--
-- This scripts requires qep.sql in order to function.
-- See Oracle Metalink Note: 550578.1 for more detail.
--
SET ECHO OFF
PROMPT
PROMPT Starting QEPS.SQL
PROMPT
PROMPT NOTES:
PROMPT
PROMPT The database parameter statistics_level should be set to ALL
PROMPT to obtain full statistics in the plan output.
PROMPT
PROMPT Script only works with Oracle 9.2 and above
PROMPT
PROMPT Script must be run from a database account with access to:
PROMPT
PROMPT . gv$sql_plan
PROMPT . gv$sqltext_with_newlines
PROMPT . gv$sql_plan_statistics_all
PROMPT
PROMPT Requires the partner script qep.sql
PROMPT
SET HEAD OFF
SET SERVEROUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
prompt Available Expense Criteria:
prompt
prompt buffer_gets - 1
prompt CPU time - 2
prompt disk_reads - 3
prompt rows_processed - 4
prompt executions - 5
prompt parse calls - 6
prompt Buffers/Exec - 7
prompt Cost per row - 8
prompt
accept sec_opt prompt "Please select required expense criteria [7]: "
accept top_n prompt 'Please enter the number of SQL Statements to report [5]: '
SET TERMOUT OFF
SPOOL gen_plans.sql
SELECT /* QWEKLOIPYRTJHH7 */
'define instid=' ||'''' || inst_id ||'''' || CHR(10) ||
'define hash_value=' ||'''' || hash_value ||'''' || CHR(10) ||
'define address=' ||'''' || address ||'''' || CHR(10) ||
'define child_number='||'''' || child_number ||'''' || CHR(10) ||
'@qep'
FROM
(SELECT inst_id, hash_value, child_number, address, buffer_gets
FROM gv$sql
WHERE sql_text NOT LIKE '%QWEKLOIPYRTJHH7%'
AND (UPPER(sql_text) like 'SELECT%'
OR UPPER(sql_text) like 'UPDATE%'
OR UPPER(sql_text) like 'DELETE%'
OR UPPER(sql_text) like 'INSERT%')
ORDER BY
DECODE('&sec_opt',
NULL,
buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions)),
1, buffer_gets,
2, cpu_time,
3, disk_reads,
4, rows_processed,
5, executions,
6, parse_calls,
7, buffer_gets / decode(executions,0,1, executions),
8, buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions))) DESC )
WHERE rownum < DECODE(TO_NUMBER('&&top_n'),NULL, 6, &&top_n + 1)
/
SPOOL OFF
SET HEAD ON PAGESIZE 66
--
-- Setup Sort Opt descr for main report
--
BREAK ON SORTOPT
COLUMN SORTOPT NEW_VALUE SORTOPT_VAR
SELECT /* QWEKLOIPYRTJHH7 */
decode(NVL('&sec_opt',7)
,1,'Buffer Gets',
2,'CPU time',
3,'Disk Reads',
4,'Rows Processed',
5,'Executions',
6,'Parse Calls',
7,'Buffers/Exec',
8,'Cost per Row') SORTOPT
FROM DUAL;
CLEAR BREAKS
COLUMN instance_name NEW_VALUE instance
SELECT instance_name
FROM v$instance;
COLUMN file_date NEW_VALUE file_date_var
SELECT /* QWEKLOIPYRTJHH7 */
TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') ||'.txt' file_date
FROM DUAL;
COLUMN SYSDATE new_value today
SELECT /* QWEKLOIPYRTJHH7 */
To_Char(SYSDATE,'mm/dd/yyyy') "sysdate" FROM DUAL
/
SPOOL qeps_&instance._&file_date_var
TTITLE
left today skip 2 center 'Top &&top_n Performing SQL Statements
for Database Instance &&instance by &&sortopt_var '
skip 2
SET TERMOUT ON
@gen_plans
spool
spool off
--
-- Script:
qep.sql
--
-- This script is called via the qeps.sql script. Please refer to Oracle
-- Metalink Note: 550578.1 for more detail.
--
set pagesize 600
set tab off
set linesize 133
set echo off
set long 4000
col Inst_ID format 999 heading "Inst ID"
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12 heading "SQL Address"
col sql_hash format A15 heading "SQL Hash"
col buffer_gets heading "Buffer|Gets "
col exec format 9999
col disk_reads heading "Disk |Reads"
col sorts heading "Sorts"
col rows_processed heading "Rows |Processed"
col parse_calls heading "Parse|Calls"
col cpu_time format 9999999.99 heading "CPU Time|(Secs)"
col executions format 999,999,999 heading "Execs"
col sql_text format A80 heading "SQL Text"
repfooter off;
SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;
ALTER SESSION SET "_complex_view_merging"=FALSE;
SELECT /* QWEKLOIPYRTJHH7 */ inst_id, address,
hash_value||DECODE(child_number, 0,
'', '/'||child_number) sql_hash,
executions,
parse_calls,
buffer_gets,
disk_reads,
rows_processed,
cpu_time/1000000 as cpu_time,
sorts
FROM gv$sql
WHERE inst_id = '&instid'
AND hash_value = '&hash_value'
AND child_number = '&child_number';
set head off
TTITLE OFF
SELECT /* QWEKLOIPYRTJHH7 */ REPLACE(sql_text,CHR(10),' ') as sql_text
FROM gv$sqltext_with_newlines
WHERE inst_id = '&instid'
AND address = '&address'
ORDER BY piece;
set head on
PROMPT
PROMPT C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
SELECT RPAD('| Operation',45) || RPAD('| Name',25) || ' |C-Rows|O-Rows|IN-OUT| Buffers |'
|| RPAD(' Reads',9) || RPAD('| Writes',10) || RPAD('| E-Time',10) || '|'
AS "Plan Table"
FROM dual
UNION ALL /* QWEKLOIPYRTJHH7 */
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual
UNION ALL
SELECT RPAD('| '||substr(LPAD(' ',1*(depth))||operation|| DECODE(options, null,'',' '||options), 1, 44), 45, ' ')||'|'||
RPAD(substr(object_name||' ',1, 25), 25, ' ')||'|'||
LPAD(DECODE(cardinality,null,' ',
DECODE(sign(cardinality-1000), -1, cardinality||' ',
DECODE(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
DECODE(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 6, ' ') || '|' ||
LPAD(DECODE(outrows,null,' ',
DECODE(sign(outrows-1000), -1, outrows||' ',
DECODE(sign(outrows-1000000), -1, round(outrows/1000)||'K',
DECODE(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 6, ' ') || '|' ||
RPAD(DECODE(other_tag,
'PARALLEL_TO_SERIAL', ' P->S',
'PARALLEL_FROM_SERIAL', ' P<-S',
'PARALLEL_FROM_REMOTE', ' P<-R',
'PARALLEL_TO_PARALLEL', ' P->P',
'PARALLEL_COMBINED_WITH_PARENT',' PCWP',' '),6,' ') || '|' ||
LPAD(DECODE(crgets,null,' ',
DECODE(sign(crgets-10000000), -1, crgets||' ',
DECODE(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(reads,null,' ',
DECODE(sign(reads-10000000), -1, reads||' ',
DECODE(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(writes,null,' ',
DECODE(sign(writes-10000000), -1, writes||' ',
DECODE(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 9, ' ') || '|' ||
REPLACE(TO_CHAR(NVL(etime/1000000,0), '99990.99'),' 0.00',' ') ||
'|' AS "Explain plan"
FROM
(SELECT /*+ no_merge */ /* QWEKLOIPYRTJHH7 */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
FROM gv$sql_plan_statistics_all pa,
gv$sql_plan p
WHERE p.inst_id = '&instid'
AND pa.inst_id = '&instid'
AND p.hash_value = '&hash_value'
AND p.child_number = '&child_number'
AND pa.child_number(+) = '&child_number'
AND p.id = pa.id(+)
AND p.address = pa.address(+)
ORDER BY p.id)
UNION ALL
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual;
REM
REM Print slave sql
REM
SELECT /* QWEKLOIPYRTJHH7 */
DECODE(object_node,null,'', substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other "Slave SQL"
FROM gv$sql_plan vp
WHERE vp.inst_id = '&instid'
AND vp.other IS NOT NULL
AND vp.hash_value = '&hash_value'
AND vp.child_number = '&child_number';
PROMPT
************************************************************************************************************************************
PROMPT
01/25/2008
Top
Performing SQL Statements for Database Instance V102 by
Buffers/Exec
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31ADC51C 2327026800 1 1 3835
778 0 .58 0
select
o.owner#,o.obj#,decode(o.linkname,null,
decode(u.name,nul
l,'SYS',u.name),o.remoteowner),
o.name,o.linkname,o.namespace,o.
subname
from user$ u, obj$ o where u.user#(+)=o.owner# and
o.typ
e#=:1
and not exists (select p_obj# from dependency$ where
p_obj
#
= o.obj#) order by o.obj# for
update
C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
--------------------------------------------------------------------------------------------------------------------------------
-----
|
FOR UPDATE |
| | 0 | | 3835 | 778 | 0 | 2.97|
|
FILTER |
| | 0 | | 3835 | 778 | 0 | 2.97|
|
NESTED LOOPS OUTER |
| | 543 | | 2206 | 720 | 0 | 2.64|
|
TABLE ACCESS BY INDEX ROWID |OBJ$ |
| 543 | | 1118 | 717 | 0 | 2.58|
| INDEX
FULL SCAN |I_OBJ1 | |
51K| | 127 | 120 | 0 | 0.46|
| TABLE
ACCESS CLUSTER |USER$ | |
543 | | 1088 | 3 | 0 | 0.06|
| INDEX
UNIQUE SCAN |I_USER# | | 543
| | 2 | 1 | 0 | 0.02|
| INDEX RANGE
SCAN |I_DEPENDENCY2 | | 543
| | 1629 | 58 | 0 | 0.32|
-------------------------------------------------------------------------------------------------------------------------------------
********************
****************************************************************************************************************
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
319882A0 3210731474 1 1 1856
36 1 .08 0
select
e.xmldata."sysconfig"."protocolconfig"."ftpconfig"."ftp-p
ort",
e.xmldata."sysconfig"."protocolconfig"."ftpconfig"."ftp-pr
otocol",
e.xmldata."sysconfig"."protocolconfig"."httpconfig"."h
ttp-port",
e.xmldata."sysconfig"."protocolconfig"."httpconfig".
"http-protocol",
e.xmldata."sysconfig"."protocolconfig"."httpco
nfig"."http2-port",
e.xmldata."sysconfig"."protocolconfig"."htt
pconfig"."http2-protocol"
from xdb.xdb$config
e
C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
TABLE ACCESS FULL |XDB$CONFIG
| 1 | 1 | | 7 | 6 | 0 | 0.01|
-------------------------------------------------------------------------------------------------------------------------------------
********************
****************************************************************************************************************
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
319B23DC 1187151836 1 1 920
32 1 .09 0
select
ee.ectx#, o.owner#, u.name, o.name, ee.num_rules,
ee.num_
boxes,
ee.ee_flags from rule_set_ee$ ee, obj$ o, user$ u
where
ee.rs_obj#
= :1 and ee.ectx# = o.obj# and o.owner# =
u.user#
C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
NESTED LOOPS |
| 2 | 1 | | 8 | 2 | 0 | 0.03|
|
NESTED LOOPS | |
2 | 1 | | 6 | 2 | 0 | 0.03|
| TABLE
ACCESS FULL |RULE_SET_EE$ | 2 |
1 | | 3 | 2 | 0 | 0.03|
| TABLE ACCESS
BY INDEX ROWID |OBJ$ | 1 | 1
| | 3 | 0 | 0 | |
| INDEX UNIQUE
SCAN |I_OBJ1 | 1 | 1
| | 2 | 0 | 0 | |
| TABLE ACCESS
CLUSTER |USER$ | 1 | 1
| | 2 | 0 | 0 | |
| INDEX UNIQUE
SCAN |I_USER# | 1 | 1
| | 1 | 0 | 0 | |
------------------------------------------------------------------------------------------------------------------------------
-------
**********************************************************************************************************************
**************
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A63F14 2372567631 1 1 867
803 0 .21 0
select
o.obj#, u.name, o.name, t.spare1,
DECODE(bitand(t
.flags,
268435456), 268435456, t.initrans, t.pctfree$) from
s
ys.obj$
o, sys.user$ u, sys.tab$ t where
(bitand(t.trigflag,
1048576)
= 1048576) and o.obj#=t.obj# and o.owner#
=
u.user#
C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
NESTED LOOPS |
| | 0 | | 867 | 803 | 0 | 1.46|
|
NESTED LOOPS |
| | 0 | | 867 | 803 | 0 | 1.46|
|
TABLE ACCESS FULL |TAB$
| | 0 | | 867 | 803 | 0 | 1.46|
|
TABLE ACCESS BY INDEX ROWID |OBJ$
| | 0 | | 0 | 0 | 0 | |
|
INDEX UNIQUE SCAN |I_OBJ1 |
| 0 | | 0 | 0 | 0 | |
| TABLE
ACCESS CLUSTER |USER$ |
| 0 | | 0 | 0 | 0 | |
| INDEX
UNIQUE SCAN |I_USER# | |
0 | | 0 | 0 | 0 | |
-------------------------------------------------------------------------------------------------------------------------------------
*************
***********************************************************************************************************************
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A9A254 162926978 1 1 764
113 0 .12 0
select
table_objno, primary_instance, secondary_instance,
owner_
instance
from sys.aq$_queue_table_affinities a where
a.owner_i
nstance
<> :1 and
dbms_aqadm_syscalls.get_owner_instance(a.prima
ry_instance,
a.secondary_instance,
a.owner_instance)
=
:2 order by table_objno for update of a.owner_instance
skip
locked
C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
FOR UPDATE |
| | 0 | | 371 | 19 | 0 | 0.06|
|
TABLE ACCESS BY INDEX ROWID
|AQ$_QUEUE_TABLE_AFFINITIE| | 0 | | 371 | 19
| 0 | 0.06|
| INDEX FULL SCAN
|AQ$_QTABLE_AFFINITIES_PK | | 14 | | 1 | 1
| 0 | |
-------------------------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************************
currently spooling to qeps_V102_20080125_213802.txt
Note 186548.1 9i Release 2 Cached cursors information in the row source level
Note 260942.1 Display Execution plans from Statement's in V$SQL_PLAN
Note 550578.1 How to Obtain the most Resource Intensive SQL Execution Plans using the Libr...
'Oracle Database' 카테고리의 다른 글
| oracle - recovery manager (RMAN) (0) | 2009.08.23 |
|---|---|
| Oracle OTN Developer Day (0) | 2009.08.20 |
| "A Tour of the AWR Tables" [NOCOUG Summer Conference] (0) | 2009.08.18 |
| Oracle AWR view MAP - Wait event 관련 (0) | 2009.08.12 |
| Oracle AWR table MAP - SQL 관련 (0) | 2009.08.12 |