Администрирование
Параметры Oracle: открытые и “скрытые” (hidden)
Список “скрытых” (hidden) параметров
Тот же запрос для Oracle 11g из комментария к блогу Т.Кайта
Актуальные блоки бд, занятые строками сегмента таблицы/матвьюшки (в отсутствии chained rows)
Мониторинг, ожидания
Суммарные ожидания активных сессий
SQL запросы, планы, оптимизатор
Дерево зависимостей/иерархия используемых SQL запросом объектов по SQL_ID
Дерево зависимых объектов по OWNER, OBJECT_NAME
Outline секция реального плана выполнения, из V$SQL_PLAN
Подсказки, прописанные в SQL PLAN BASELINE
Связанные переменные, использованные при генерации плана, из V$SQL_PLAN/DBA_HIST_SQL_PLAN.OTHER_XML
INFO section from V$SQL_PLAN/DBA_HIST_SQL_PLAN.OTHER_XML
Предикаты, добавляемые VPD | RLS | FGAC для запросов из кэша (v$sql.sql_id)
SGA shared pool
Удаление курсора из shared pool по sql_id
SQL утилиты
Dion Cho: SQL реализация утилиты OERR для Windows
PL/SQL
Процедура переноса статистики таблицы/схемы через db link (например, с production на тестовую бд)
CRS / bash
crs_stat с “человеческим лицом”
----------------------------------------------------------------------------------- --Currently connected sessions waits, longops, sql, etc... --Oracle 10+ single instance and RAC env. --I.Usoltsev ----------------------------------------------------------------------------------- select/*+ RULE*/ distinct--because of multiple records in v$sql for PREV_SQL to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME , gv$transaction.START_TIME as TX_STIME , gv$session.LAST_CALL_ET as LAST_CALL_ET , gv$session.STATUS , gv$session.USERNAME , gv$session.PROGRAM , gv$session.SERVICE_NAME as service , gv$session.INST_ID , gv$session.SID , gv$session.SERIAL# , decode(gv$session_wait.state, 'WAITING', gv$session_wait.EVENT, 'On CPU / runqueue') as EVENT , v$latchname.NAME as LATCHNAME , gv$latchholder.sid , gv$session_wait.SECONDS_IN_WAIT , dba_objects.owner||'.'||dba_objects.object_name req_object , decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE' , DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ# , gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK# , gv$session.ROW_WAIT_ROW# )) req_rowid , lockhold.inst_id as BLOCKING_INSTANCE , lockhold.sid as BLOCKING_SESSION , gv$session.COMMAND , gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT , gv$session_longops.MESSAGE as LONGOPS_MESSAGE , gv$session_wait.P1 , gv$session_wait.P1TEXT , gv$session_wait.P1RAW /* X$BH.HLADDR for LATCH: CACHE BUFFERS CHAINS */ , gv$session_wait.P2 P2 , gv$session_wait.P2TEXT , gv$session_wait.P3 , gv$session_wait.P3TEXT , ses_optimizer_env38.VALUE as ses_optimizer_mode , ses_optimizer_env48.VALUE as ses_cursor_sharing , gv$sql.SQL_ID as SQL_ID , gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE , gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE , gv$sql.sql_text as SQL_TEXT , gv$sql.SQL_PROFILE , gv$session.SQL_CHILD_NUMBER SQL_CHILD_NUMBER , sql1.SQL_ID as PREV_SQL_ID , sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE , sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE , sql1.sql_text as PREV_sql_text , gv$session.PREV_CHILD_NUMBER --Network connection properties , gv$session.SERVER , gv$session.FAILOVER_TYPE , gv$session.FAILOVER_METHOD , gv$session.FAILED_OVER , gv$session.FIXED_TABLE_SEQUENCE --OS properties , gv$session.MACHINE , gv$session.MODULE , gv$session.OSUSER , gv$session.OWNERID , gv$session.TERMINAL , 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL# ||''';' as KILL_SESSION --, gv$session.PROCESS , gv$instance.HOST_NAME , 'kill -9 '||gv$process.SPID as KILL_SPID -- from 10g and above - SQL Trace info , gv$session.SQL_TRACE, gv$session.SQL_TRACE_WAITS, gv$session.SQL_TRACE_BINDS , 'begin sys.dbms_support.start_trace_in_session('||gv$session.SID||','|| gv$session.SERIAL#||', waits=>TRUE, binds=>TRUE );end;' , 'begin sys.dbms_support.stop_trace_in_session('||gv$session.SID||','|| gv$session.SERIAL#||' );end;' from gv$session , gv$instance , gv$process , gv$session_wait , gv$sql , gv$sql sql1 , gv$transaction , v$latchname , dba_objects , gv$session_longops , gv$ses_optimizer_env ses_optimizer_env38 , gv$ses_optimizer_env ses_optimizer_env48 , gv$lock lockwait , gv$lock lockhold , gv$latchholder where gv$session.INST_ID = gv$instance.INST_ID and gv$session.PADDR=gv$process.ADDR(+) and gv$session.INST_ID = gv$process.INST_ID(+) and gv$session.sql_address=gv$sql.address(+) and gv$session.sql_hash_value=gv$sql.hash_value(+) and gv$session.SQL_CHILD_NUMBER = gv$sql.CHILD_NUMBER(+) and gv$session.INST_ID = gv$sql.INST_ID(+) and gv$session.PREV_SQL_ADDR = sql1.address(+) and gv$session.PREV_HASH_VALUE = sql1.hash_value(+) and gv$session.PREV_CHILD_NUMBER = sql1.CHILD_NUMBER(+) and gv$session.SID=gv$session_wait.SID(+) and gv$session.INST_ID = gv$session_wait.INST_ID(+) and gv$session.SADDR=gv$transaction.SES_ADDR(+) and gv$session.INST_ID = gv$transaction.INST_ID(+) and gv$session.SERVICE_NAME not in ('SYS$BACKGROUND') and gv$session.PROGRAM not like '%QMNC%' --Queue Monitor Coordinator excluded and gv$session.PROGRAM not like '%q00%' --Queue monitor processes excluded --and v$session.PROGRAM not like '%J00%' --DBMS_JOB processes excluded and gv$session_wait.P2=v$latchname.LATCH#(+) and gv$session_wait.p1raw = gv$latchholder.laddr(+) and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+) and gv$session.SID = gv$session_longops.sid(+) and gv$session.INST_ID = gv$session_longops.INST_ID(+) and gv$session.SERIAL# = gv$session_longops.SERIAL#(+) and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+) and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+) and (nvl(gv$session_longops.TIME_REMAINING, 1) > 0 or nvl(gv$session_longops.START_TIME, sysdate) = (select max(START_TIME) from gv$session_longops gsl where gv$session.SID = gsl.sid and gv$session.INST_ID = gsl.INST_ID and gv$session.SERIAL# = gsl.SERIAL# and gv$session.sql_address = gsl.SQL_ADDRESS and gv$session.sql_hash_value = gsl.SQL_HASH_VALUE)) and gv$session.sid = ses_optimizer_env38.sid(+) and gv$session.INST_ID = ses_optimizer_env38.INST_ID(+) and nvl(ses_optimizer_env38.id,38) = 38 --optimizer_mode and gv$session.sid = ses_optimizer_env48.sid(+) and gv$session.INST_ID = ses_optimizer_env48.INST_ID(+) and nvl(ses_optimizer_env48.id, 48) = 48--cursor_sharing and gv$session.LOCKWAIT = lockwait.KADDR(+) and lockwait.id1 = lockhold.id1(+) and lockwait.id2 = lockhold.id2(+) and nvl(lockwait.REQUEST,1) > 0 and nvl(lockwait.LMODE,0) = 0 and nvl(lockhold.REQUEST,0) = 0 and nvl(lockhold.LMODE,1) > 0 and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1) order by tx_stime, status, LOGON_TIME, username, gv$session.sid; ----------------------------------------------------------------------------------- --Currently connected sessions waits, longops, sql, etc... Oracle 9- --single instance and RAC env. --I.Usoltsev ----------------------------------------------------------------------------------- select/*+ rule*/ distinct--because of multiple records in v$sql may exist to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME , gv$session.STATUS , gv$session.USERNAME , gv$session.PROGRAM , gv$session.INST_ID , gv$session.SID , gv$session.SERIAL# , gv$transaction.START_TIME as TX_STIME , gv$session.LAST_CALL_ET as LAST_CALL_ET , gv$session_wait.EVENT , v$latchname.NAME as LATCHNAME , gv$session_wait.SECONDS_IN_WAIT , dba_objects.owner||'.'||dba_objects.object_name req_object , decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#, gv$session.ROW_WAIT_ROW# )) req_rowid , lockhold.inst_id as BLOCKING_INSTANCE , lockhold.sid as BLOCKING_SESSION , gv$session.COMMAND , gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT , gv$session_longops.MESSAGE as LONGOPS_MESSAGE , gv$session_wait.P1 , gv$session_wait.P1TEXT , gv$session_wait.P2 P2 , gv$session_wait.P2TEXT , gv$session_wait.P3 , gv$session_wait.P3TEXT , gv$sql.ADDRESS as SQL_ADDRESS , gv$sql.HASH_VALUE as HASH_VALUE , gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE , gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE , gv$sql.sql_text as SQL_TEXT , sql1.ADDRESS as PREV_SQL_ADDRESS , sql1.HASH_VALUE as PREV_HASH_VALUE , sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE , sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE , sql1.sql_text as PREV_sql_text --Network connection properties , gv$session.SERVER , gv$session.FAILOVER_TYPE , gv$session.FAILOVER_METHOD , gv$session.FAILED_OVER , gv$session.FIXED_TABLE_SEQUENCE --OS properties , gv$session.MACHINE , gv$session.MODULE , gv$session.OSUSER , gv$session.OWNERID , gv$session.TERMINAL , 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';' as KILL_SESSION , gv$instance.HOST_NAME , 'kill '||gv$process.SPID as KILL_SPID from gv$session , gv$instance , gv$process , gv$session_wait , gv$sql , gv$sql sql1 , gv$transaction , v$latchname , dba_objects , gv$session_longops , gv$lock lockwait , gv$lock lockhold where gv$session.INST_ID = gv$instance.INST_ID and gv$session.PADDR=gv$process.ADDR(+) and gv$session.INST_ID = gv$process.INST_ID(+) and gv$session.sql_address=gv$sql.address(+) and gv$session.INST_ID = gv$sql.INST_ID(+) and gv$session.sql_hash_value=gv$sql.hash_value(+) and gv$session.PREV_SQL_ADDR = sql1.address(+) and gv$session.PREV_HASH_VALUE = sql1.hash_value(+) and gv$session.SID=gv$session_wait.SID(+) and gv$session.INST_ID = gv$session_wait.INST_ID(+) and gv$session.SADDR=gv$transaction.SES_ADDR(+) and gv$session.INST_ID = gv$transaction.INST_ID(+) and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%' or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded and gv$session_wait.P2=v$latchname.LATCH#(+) and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+) and gv$session.SID = gv$session_longops.sid(+) and gv$session.INST_ID = gv$session_longops.INST_ID(+) and gv$session.SERIAL# = gv$session_longops.SERIAL#(+) and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+) and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+) and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1) and gv$session.LOCKWAIT = lockwait.KADDR(+) and lockwait.id1 = lockhold.id1(+) and lockwait.id2 = lockhold.id2(+) and nvl(lockwait.REQUEST,1) > 0 and nvl(lockwait.LMODE,0) = 0 and nvl(lockhold.REQUEST,0) = 0 and nvl(lockhold.LMODE,1) > 0 and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1) order by tx_stime, status, LOGON_TIME, username, machine asc; ----------------------------------------------------------------------------------- --Active session summary waits ratio --I.Usoltsev ----------------------------------------------------------------------------------- select WAIT_EVENT ,lpad(TO_CHAR(PCTTOT,'990D99'),6)||'% waits with average duration =' ||TO_CHAR(AVERAGE_WAIT_MS,'9990D99')||'ms' as EVENT_VALUES from ( select --RANK() OVER (order by sum(time_waited) desc) as RANK, event as WAIT_EVENT--, sum(time_waited) time_waited_ms ,round(RATIO_TO_REPORT(sum(time_waited)) OVER ()*100,2) AS PCTTOT ,round(avg(average_wait)*10,2) as AVERAGE_WAIT_MS from (select se.SID, se.INST_ID, se.EVENT, se.TIME_WAITED, se.AVERAGE_WAIT from gv$session_event se where se.WAIT_CLASS not in ('Idle') union select ss.SID, ss.INST_ID, sn.NAME as EVENT , ss.VALUE as TIME_WAITED , 0 as AVERAGE_WAIT from gv$sesstat ss, v$statname sn where ss."STATISTIC#" = sn."STATISTIC#" and sn.NAME in ('CPU used when call started')) where (sid, inst_id) in (select sid, inst_id from gv$session where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND')) group by event order by PCTTOT desc) we; ----------------------------------------------------------------------------------- --select view_definition from v$fixed_view_definition where view_name = 'GV$PARAMETER' --and comment out the last "hiding" conditions --I.Usoltsev ----------------------------------------------------------------------------------- select x.inst_id as "INST_ID", x.indx + 1 as "NUM", ksppinm as "NAME", ksppity as "TYPE", ksppstvl as "VALUE", ksppstdvl as "DISPLAY_VALUE", ksppstdf as "ISDEFAULT", decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as "ISSES_MODIFIABLE", decode(bitand(ksppiflg / 65536, 3), 1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') as "ISSYS_MODIFIABLE", decode(bitand(ksppiflg, 4),4,'FALSE', decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')) as "ISINSTANCE_MODIFIABLE", decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') as "ISMODIFIED", decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as "ISADJUSTED", decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE') as "ISDEPRECATED", decode(bitand(ksppilrmflg / 268435456, 1), 1, 'TRUE', 'FALSE') as "ISBASIC", ksppdesc as "DESCRIPTION", ksppstcmnt as "UPDATE_COMMENT", ksppihash as "HASH" from x$ksppi x, x$ksppcv y where (x.indx = y.indx) --and ((translate(ksppinm, '_', '#') not like '##%') --and ((translate(ksppinm, '_', '#') not like '#%') -- or (ksppstdf = 'FALSE') or (bitand(ksppstvf, 5) > 0))); ------------------------------------------------------------------- ------------------------------------------------------------------- select/*+RULE*/ sql_id as sql_id, version_count, address, hash_value, parsing_schema_name, reason, sql_text from ( select sql_id, address , decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR') ||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH') ||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH') ||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH') ||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH') ||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH') ||decode(max( SEC_DEPTH_MISMATCH),'Y', ' SEC_DEPTH_MISMATCH') ||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR') ||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH') ||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH') ||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH') ||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH') ||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH') ||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH') ||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH') ||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH') ||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH') ||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH') ||decode(max( ROW_LEVEL_SEC_MISMATCH),'Y', ' ROW_LEVEL_SEC_MISMATCH') ||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS') ||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM') ||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH') ||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH') ||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH') ||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH') ||decode(max( SQL_REDIRECT_MISMATCH),'Y', ' SQL_REDIRECT_MISMATCH') ||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH') ||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH') ||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH') ||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH') ||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR') ||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION') ||decode(max( INCOMPLETE_CURSOR),'Y', ' INCOMPLETE_CURSOR') ||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR') ||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH') ||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY') ||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN') ||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF') ||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF') ||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH') ||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH') ||decode(max( ROW_SHIP_MISMATCH),'Y', ' ROW_SHIP_MISMATCH') ||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH') ||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH') ||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH') ||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH') ||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH') ||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH') ||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH') ||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH') ||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH') ||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH') ||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH') reason from v$sql_shared_cursor where sql_id = '&sql_id' group by sql_id, address ) join v$sqlarea using(address, sql_id) where version_count>1 order by version_count desc,address; ------------------------------------------------------------------- --From 11.1.0.7 patchset ------------------------------------------------------------------- select/*+RULE*/ sql_id as sql_id, version_count, address, hash_value, parsing_schema_name, reason, sql_text from ( select sql_id, address , decode(max( UNBOUND_CURSOR),'Y', ' UNBOUND_CURSOR') ||decode(max( SQL_TYPE_MISMATCH),'Y', ' SQL_TYPE_MISMATCH') ||decode(max( OPTIMIZER_MISMATCH),'Y', ' OPTIMIZER_MISMATCH') ||decode(max( OUTLINE_MISMATCH),'Y', ' OUTLINE_MISMATCH') ||decode(max( STATS_ROW_MISMATCH),'Y', ' STATS_ROW_MISMATCH') ||decode(max( LITERAL_MISMATCH),'Y', ' LITERAL_MISMATCH') -- ||decode(max( SEC_DEPTH_MISMATCH),'Y', ' SEC_DEPTH_MISMATCH') ||decode(max( EXPLAIN_PLAN_CURSOR),'Y', ' EXPLAIN_PLAN_CURSOR') ||decode(max( BUFFERED_DML_MISMATCH),'Y', ' BUFFERED_DML_MISMATCH') ||decode(max( PDML_ENV_MISMATCH),'Y', ' PDML_ENV_MISMATCH') ||decode(max( INST_DRTLD_MISMATCH),'Y', ' INST_DRTLD_MISMATCH') ||decode(max( SLAVE_QC_MISMATCH),'Y', ' SLAVE_QC_MISMATCH') ||decode(max( TYPECHECK_MISMATCH),'Y', ' TYPECHECK_MISMATCH') ||decode(max( AUTH_CHECK_MISMATCH),'Y', ' AUTH_CHECK_MISMATCH') ||decode(max( BIND_MISMATCH),'Y', ' BIND_MISMATCH') ||decode(max( DESCRIBE_MISMATCH),'Y', ' DESCRIBE_MISMATCH') ||decode(max( LANGUAGE_MISMATCH),'Y', ' LANGUAGE_MISMATCH') ||decode(max( TRANSLATION_MISMATCH),'Y', ' TRANSLATION_MISMATCH') -- ||decode(max( ROW_LEVEL_SEC_MISMATCH),'Y', ' ROW_LEVEL_SEC_MISMATCH') ||decode(max( INSUFF_PRIVS),'Y', ' INSUFF_PRIVS') ||decode(max( INSUFF_PRIVS_REM),'Y', ' INSUFF_PRIVS_REM') ||decode(max( REMOTE_TRANS_MISMATCH),'Y', ' REMOTE_TRANS_MISMATCH') ||decode(max( LOGMINER_SESSION_MISMATCH),'Y', ' LOGMINER_SESSION_MISMATCH') ||decode(max( INCOMP_LTRL_MISMATCH),'Y', ' INCOMP_LTRL_MISMATCH') ||decode(max( OVERLAP_TIME_MISMATCH),'Y', ' OVERLAP_TIME_MISMATCH') -- ||decode(max( SQL_REDIRECT_MISMATCH),'Y', ' SQL_REDIRECT_MISMATCH') ||decode(max( MV_QUERY_GEN_MISMATCH),'Y', ' MV_QUERY_GEN_MISMATCH') ||decode(max( USER_BIND_PEEK_MISMATCH),'Y', ' USER_BIND_PEEK_MISMATCH') ||decode(max( TYPCHK_DEP_MISMATCH),'Y', ' TYPCHK_DEP_MISMATCH') ||decode(max( NO_TRIGGER_MISMATCH),'Y', ' NO_TRIGGER_MISMATCH') ||decode(max( FLASHBACK_CURSOR),'Y', ' FLASHBACK_CURSOR') ||decode(max( ANYDATA_TRANSFORMATION),'Y', ' ANYDATA_TRANSFORMATION') ||decode(max( INCOMPLETE_CURSOR),'Y', ' INCOMPLETE_CURSOR') ||decode(max( TOP_LEVEL_RPI_CURSOR),'Y', ' TOP_LEVEL_RPI_CURSOR') ||decode(max( DIFFERENT_LONG_LENGTH),'Y', ' DIFFERENT_LONG_LENGTH') ||decode(max( LOGICAL_STANDBY_APPLY),'Y', ' LOGICAL_STANDBY_APPLY') ||decode(max( DIFF_CALL_DURN),'Y', ' DIFF_CALL_DURN') ||decode(max( BIND_UACS_DIFF),'Y', ' BIND_UACS_DIFF') ||decode(max( PLSQL_CMP_SWITCHS_DIFF),'Y', ' PLSQL_CMP_SWITCHS_DIFF') ||decode(max( CURSOR_PARTS_MISMATCH),'Y', ' CURSOR_PARTS_MISMATCH') ||decode(max( STB_OBJECT_MISMATCH),'Y', ' STB_OBJECT_MISMATCH') -- ||decode(max( ROW_SHIP_MISMATCH),'Y', ' ROW_SHIP_MISMATCH') ||decode(max( PQ_SLAVE_MISMATCH),'Y', ' PQ_SLAVE_MISMATCH') ||decode(max( TOP_LEVEL_DDL_MISMATCH),'Y', ' TOP_LEVEL_DDL_MISMATCH') ||decode(max( MULTI_PX_MISMATCH),'Y', ' MULTI_PX_MISMATCH') ||decode(max( BIND_PEEKED_PQ_MISMATCH),'Y', ' BIND_PEEKED_PQ_MISMATCH') ||decode(max( MV_REWRITE_MISMATCH),'Y', ' MV_REWRITE_MISMATCH') ||decode(max( ROLL_INVALID_MISMATCH),'Y', ' ROLL_INVALID_MISMATCH') ||decode(max( OPTIMIZER_MODE_MISMATCH),'Y', ' OPTIMIZER_MODE_MISMATCH') ||decode(max( PX_MISMATCH),'Y', ' PX_MISMATCH') ||decode(max( MV_STALEOBJ_MISMATCH),'Y', ' MV_STALEOBJ_MISMATCH') ||decode(max( FLASHBACK_TABLE_MISMATCH),'Y', ' FLASHBACK_TABLE_MISMATCH') ||decode(max( LITREP_COMP_MISMATCH),'Y', ' LITREP_COMP_MISMATCH') --Added in Oracle 11g ||decode(max( PLSQL_DEBUG),'Y', ' PLSQL_DEBUG') ||decode(max( LOAD_OPTIMIZER_STATS),'Y', ' LOAD_OPTIMIZER_STATS') ||decode(max( ACL_MISMATCH),'Y', ' ACL_MISMATCH') ||decode(max( FLASHBACK_ARCHIVE_MISMATCH),'Y', ' FLASHBACK_ARCHIVE_MISMATCH') ||decode(max( LOCK_USER_SCHEMA_FAILED),'Y', ' LOCK_USER_SCHEMA_FAILED') ||decode(max( REMOTE_MAPPING_MISMATCH),'Y', ' REMOTE_MAPPING_MISMATCH') ||decode(max( LOAD_RUNTIME_HEAP_FAILED),'Y', ' LOAD_RUNTIME_HEAP_FAILED') ||decode(max( HASH_MATCH_FAILED),'Y', ' HASH_MATCH_FAILED') ||decode(max( PURGED_CURSOR),'Y', ' PURGED_CURSOR') ||decode(max( BIND_LENGTH_UPGRADEABLE),'Y', ' BIND_LENGTH_UPGRADEABLE') reason from v$sql_shared_cursor where sql_id = '&sql_id' group by sql_id, address ) join v$sqlarea using(address, sql_id) where version_count>1 order by version_count desc,address; http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976 select table_name, constraint_name, cname1 || nvl2(cname2,','||cname2,null) || nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) columns from ( select b.table_name, b.constraint_name, max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count(*) col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, position from user_cons_columns ) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL ( select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 ) and i.column_position <= cons.col_cnt group by i.index_name ); http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html#c3494935918525451523 SELECT decode(b.table_name, NULL, '****', 'ok') status, a.table_name, a.columns fk_columns, b.columns index_columns FROM (SELECT a.table_name, a.constraint_name, LISTAGG(a.column_name, ',') within GROUP(ORDER BY a.position) columns FROM dba_cons_columns a, dba_constraints b WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R' AND a.owner = b.owner GROUP BY a.table_name, a.constraint_name) a, (SELECT table_name, index_name, LISTAGG(c.column_name, ',') within GROUP(ORDER BY c.column_position) columns FROM dba_ind_columns c GROUP BY table_name, index_name) b WHERE a.table_name = b.table_name(+) AND b.columns(+) LIKE a.columns || '%' ORDER BY status, table_name
procedure stats_imp_over_dblink(
-- procedure for the schema/table statistics moving over database link - for example between the production and testing environments
v_dblink2prod varchar2 := '', -- dblink name to source db
v_schema varchar2 := '', -- source/target schema name to synchronize stats
v_tabname varchar2 := '', -- source/target table name to synchronize stats
v_tbl_stattab_prod varchar2 := 'tbl_stattab_prod', -- stattab name (local and remote)
v_gtt4stattab varchar2 := 'gtt4stattab', -- GTT name for CLOB moving over dblink (local only)
v_force_stats_imp boolean := false) -- to force import despite of schema/table ddl differencies
is
TYPE TblLstCurType IS REF CURSOR;
TblLst_cv TblLstCurType;
v_discount number := 0;
r_tblname varchar2(30);
stattab_doesnt_exists exception;
tab_doesnt_exists exception;
PRAGMA EXCEPTION_INIT(stattab_doesnt_exists, -20002);
PRAGMA EXCEPTION_INIT(tab_doesnt_exists, -942);
begin
if v_dblink2prod is null then dbms_output.put_line('V_DBLINK2PROD var have to be specified!!!'); return; end if;
if v_schema is null then dbms_output.put_line('V_SCHEMA var have to be specified!!!'); return; end if;
if v_tabname is not null then
execute immediate 'select count(*) from
((select table_name, column_name, data_type, data_length from dba_tab_cols@'||v_dblink2prod||' where owner = '''||upper(v_schema)||''' and table_name = '''||upper(v_tabname)||'''
minus
select table_name, column_name, data_type, data_length from dba_tab_cols where owner = '''||upper(v_schema)||''' and table_name = '''||upper(v_tabname)||''')
union all
select table_name, column_name, descend, column_length from
(select table_owner, table_name, column_name, column_position, column_length, char_length, descend
from dba_ind_columns@'||v_dblink2prod||' where table_owner = '''||upper(v_schema)||''' and table_name = '''||upper(v_tabname)||'''
minus
select table_owner, table_name, column_name, column_position, column_length, char_length, descend
from dba_ind_columns where table_owner = '''||upper(v_schema)||''' and table_name = '''||upper(v_tabname)||'''))'
into v_discount;
if v_discount > 0 and not v_force_stats_imp then dbms_output.put_line('Table '||upper(v_tabname)||' DDL''s are different in local and remote schrmas!'); return; end if;
else
execute immediate 'select count(*) from
((select table_name, column_name, data_type, data_length from dba_tab_cols@'||v_dblink2prod||' where owner = '''||upper(v_schema)||''' and table_name not like ''BIN$%'' and table_name not in (select view_name from dba_views@'||v_dblink2prod||')
minus
select table_name, column_name, data_type, data_length from dba_tab_cols where owner = '''||upper(v_schema)||''' and table_name not like ''BIN$%'' and table_name not in (select view_name from dba_views))
union all
select table_name, column_name, descend, column_length from
(select table_owner, table_name, column_name, column_position, column_length, char_length, descend
from dba_ind_columns@'||v_dblink2prod||' where table_owner = '''||upper(v_schema)||''' and index_name not like ''BIN$%''
minus
select table_owner, table_name, column_name, column_position, column_length, char_length, descend
from dba_ind_columns where table_owner = '''||upper(v_schema)||''' and index_name not like ''BIN$%''))'
into v_discount;
if v_discount > 0 and not v_force_stats_imp then dbms_output.put_line('SCHEMAS have differences, sorry...'); return; end if;
end if;
begin execute immediate 'begin dbms_stats.drop_stat_table@'||v_dblink2prod||'('''||upper(v_schema)||''', '''||upper(v_tbl_stattab_prod)||'''); end;'; exception when stattab_doesnt_exists then null; end;
begin execute immediate 'begin dbms_stats.drop_stat_table('''||upper(v_schema)||''', '''||upper(v_tbl_stattab_prod)||'''); end;'; exception when stattab_doesnt_exists then null; end;
execute immediate 'begin dbms_stats.create_stat_table@'||v_dblink2prod||'('''||upper(v_schema)||''', '''||upper(v_tbl_stattab_prod)||'''); end;';
execute immediate 'begin dbms_stats.create_stat_table('''||upper(v_schema)||''', '''||upper(v_tbl_stattab_prod)||'''); end;';
begin execute immediate 'drop table '||v_gtt4stattab; exception when tab_doesnt_exists then null; end;
execute immediate 'create global temporary table '||v_gtt4stattab||' on commit delete rows as select * from "'||upper(v_schema)||'"."'||upper(v_tbl_stattab_prod)||'"@'||v_dblink2prod||' where 1=0';
if v_tabname is not null
then execute immediate 'begin dbms_stats.export_table_stats@'||v_dblink2prod||'(ownname => '''||upper(v_schema)||''', tabname => '''||upper(v_tabname)||''', stattab => '''||upper(v_tbl_stattab_prod)||'''); end;';
else
if v_discount = 0
then execute immediate 'begin dbms_stats.export_schema_stats@'||v_dblink2prod||'(ownname => '''||upper(v_schema)||''', stattab => '''||upper(v_tbl_stattab_prod)||'''); end;';
else
OPEN TblLst_cv FOR
'select table_name from dba_tables@'||v_dblink2prod||' where owner = '''||upper(v_schema)||''' and table_name not like ''BIN$%'' and table_name not in (select view_name from dba_views@'||v_dblink2prod||')
minus
select distinct table_name from
((select table_name, column_name, data_type, data_length from dba_tab_cols@'||v_dblink2prod||' where owner = '''||upper(v_schema)||''' and table_name not like ''BIN$%''
minus
select table_name, column_name, data_type, data_length from dba_tab_cols where owner = '''||upper(v_schema)||''' and table_name not like ''BIN$%'')
union all
select table_name, column_name, descend, column_length from
(select * from dba_ind_columns@'||v_dblink2prod||' where table_owner = '''||upper(v_schema)||''' and index_name not like ''BIN$%''
minus
select * from dba_ind_columns where table_owner = '''||upper(v_schema)||''' and index_name not like ''BIN$%''))';
LOOP
FETCH TblLst_cv INTO r_tblname;
EXIT WHEN TblLst_cv%NOTFOUND;
execute immediate 'begin dbms_stats.export_table_stats@'||v_dblink2prod||'(ownname => '''||upper(v_schema)||''', tabname => '''||r_tblname||''', stattab => '''||upper(v_tbl_stattab_prod)||'''); end;';
END LOOP;
end if;
end if;
execute immediate 'insert into '||v_gtt4stattab||' select * from "'||upper(v_schema)||'"."'||upper(v_tbl_stattab_prod)||'"@'||v_dblink2prod;
execute immediate 'insert/*+ APPEND*/ into "'||upper(v_schema)||'"."'||upper(v_tbl_stattab_prod)||'" select * from '||v_gtt4stattab;
commit;
if v_tabname is null
then execute immediate 'begin dbms_stats.import_schema_stats(ownname => '''||upper(v_schema)||''', stattab => '''||upper(v_tbl_stattab_prod)||'''); end;';
else execute immediate 'begin dbms_stats.import_table_stats(ownname => '''||upper(v_schema)||''', tabname => '''||upper(v_tabname)||''', stattab => '''||upper(v_tbl_stattab_prod)||'''); end;';
end if;
end stats_imp_over_dblink;
select --+ rule
lpad(' ', 2 * level) ||
(select object_type || ' ' || owner || '.' || object_name
from dba_objects
where object_id = pd.object_id) || ' <= ' ||
(select object_type || ' ' || owner || '.' || object_name
from dba_objects
where object_id = pd.referenced_object_id)
from public_dependency pd
connect by prior referenced_object_id = object_id
start with object_id in
(select object_id
from dba_objects
where (owner, object_name) in
(select distinct object_owner, object_name
from v$sql_plan
where sql_id = '&SQL_ID'
and object_owner is not null
and object_name is not null));
-- Dependent (from "OWNER"."OBJECT_NAME") objects tree
select --+ rule
lpad(' ', 2 * level) ||
(select object_type || ' ' || owner || '.' || object_name
from dba_objects where object_id = pd.referenced_object_id) ||
' >_is_referenced_by_> ' ||
(select object_type || ' ' || owner || '.' || object_name
from dba_objects where object_id = pd.object_id)
from public_dependency pd
connect by prior object_id = referenced_object_id
start with referenced_object_id in
(select object_id
from dba_objects where (owner, object_name) in (('&OWNER', '&OBJECT_NAME')));
crs_stat с нормальным, необрезанным выводом – взято отсюда
$ crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-30s%-30s%-10s%-30s\n", n,t,g,s)}'
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE on oracle-host01
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on oracle-host01
ora.asm ora.asm.type ONLINE ONLINE on oracle-host01
ora.cssd ora.cssd.type ONLINE ONLINE on oracle-host01
ora.diskmon ora.diskmon.type ONLINE ONLINE on oracle-host01
ora.evmd ora.evm.type ONLINE ONLINE on oracle-host01
ora.ons ora.ons.type ONLINE ONLINE on oracle-host01
ora.orc11202.db ora.database.type ONLINE ONLINE on oracle-host01
sqltext_to_sqlid
Начиная с 11.2.0.1 – http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/:
select dbms_sqltune_util0.sqltext_to_sqlid('select sysdate from dual'||chr(0)) sql_id from dual;
SQL> select dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext||chr(0)) sql_id from dual, gv$sql where sql_id = 'a7xju9dz0abdx' 2 / SQL_ID ------------- a7xju9dz0abdx
Начиная с 10.2
CREATE OR REPLACE FUNCTION sqltext_to_sqlid(p_sql IN VARCHAR2)
RETURN VARCHAR2
IS
-- Author: Sean Stuber
-- based on algorithm described by Tanel Poder here
-- http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
-- Generate md5 hash of the sql text
-- Oracle appends a null-char (0) before hashing
v_md5 RAW(16) := DBMS_CRYPTO.hash(UTL_I18N.string_to_raw(p_sql || CHR(0), 'AL32UTF8'), DBMS_CRYPTO.hash_md5);
-- pull the last 64bits from the md5
-- reverse each of the 32bit words to fix endianness
-- convert the 16 digit hex string to a number
v_temp NUMBER := TO_NUMBER( RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 9, 4)))
|| RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 13, 4))), RPAD('x', 16, 'x'));
v_sqlid VARCHAR2(13);
BEGIN
-- Convert number to base32 string
-- use alphabet defined in Tanel Poder’s sqlid to hash query
FOR i IN 0 .. FLOOR(LN(v_temp) / LN(32))
LOOP
v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_temp / POWER(32, i), 32)) + 1, 1) || v_sqlid;
END LOOP;
RETURN LPAD(v_sqlid, 13, '0');
END;
/
select sqltext_to_sqlid('select sysdate from dual') from dual;
Актуальные блоки бд, занятые строками сегмента таблицы/матвьюшки (в отсутствии chained rows)
with rowid_blocks as
(select /*+ materialize*/
dbms_rowid.rowid_relative_fno(rowid) relative_fno,
dbms_rowid.rowid_block_number(rowid) block_number
from "&&1"."&&2")
select relative_fno,
count(distinct block_number) used_blocks,
count(block_number) used_rows
from rowid_blocks
group by rollup(relative_fno)
/
Подсказки из V$SQL_PLAN outline section
select substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints
from xmltable('/*/outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '&sql_id'
and child_number = &child_number
--and plan_hash_value = &plan_hash_value
and other_xml is not null)) d
/
Подсказки из SQL PLAN BASELINE
select substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints
from xmltable('/outline_data/hint' passing
(select xmltype(comp_data) as xmlval
from sys.sqlobj$data od, sys.sqlobj$ o
where o.obj_type = 2
and o.name = '&SQL_PLAN_BASELINE'
and o.signature = od.signature
and o.plan_id = od.plan_id
and comp_data is not null)) d
/
Удаление курсора из shared pool по sql_id
declare
v_address_hash varchar2(60);
begin
select address || ', ' || hash_value
into v_address_hash
from v$sqlarea
where sql_id = '&sql_id';
sys.dbms_shared_pool.purge(v_address_hash, 'c');
end;
/
ORA-30036: кто съел UNDO?
SQL> SELECT b.start_time, a.inst_id, a.sid, a.username, a.program, b.used_urec, b.used_ublk 2 FROM gv$session a, gv$transaction b 3 WHERE a.saddr = b.ses_addr 4 and a.inst_id = b.inst_id 5 ORDER BY b.used_ublk DESC 6 / START_TIME INST_ID SID USERNAME PROGRAM USED_UREC USED_UBLK ----------------- ------- ---- -------- ----------------------------------- ---------- ---------- 03/01/12 15:51:46 1 19 APPS frmweb@appl1.oebs.net (TNS V1-V3) 9048 130 03/01/12 15:54:16 2 16 APPS STANDARD@appl1.oebs.net (TNS V1-V3) 2389 58 ... SQL> select inst_id, sid, name, value 2 from gv$sesstat s, v$statname n 3 where s.statistic# = n.statistic# 4 and name = 'undo change vector size' 5 order by value desc 6 / INST_ID SID NAME VALUE ------- --- ----------------------- --------- 1 925 undo change vector size 388021008 -- bytes ...
Peeked Binds from V$SQL_PLAN/DBA_HIST_SQL_PLAN.OTHER_XML
select sql_id,
child_number,
plan_hash_value,
EXTRACTVALUE(VALUE(D), '/bind/@nam') as NAME,
to_number(EXTRACTVALUE(VALUE(D), '/bind/@pos')) as POSITION,
EXTRACTVALUE(VALUE(D), '/bind/@ppo') as PPO,
EXTRACTVALUE(VALUE(D), '/bind/@dty') as DATATYPE,
EXTRACTVALUE(VALUE(D), '/bind/@csi') as CSI,
EXTRACTVALUE(VALUE(D), '/bind/@frm') as FRM,
EXTRACTVALUE(VALUE(D), '/bind/@pre') as PRE,
EXTRACTVALUE(VALUE(D), '/bind/@scl') as SCL,
EXTRACTVALUE(VALUE(D), '/bind/@mxl') as MAXLENGTH,
EXTRACTVALUE(VALUE(D), '/bind/@captured') as CAPTURED,
EXTRACTVALUE(VALUE(D), '/bind') as VALUE,
system.display_raw(EXTRACTVALUE(VALUE(D), '/bind'), -- DISPLAY_RAW function from Greg Rahn http://structureddata.org/2007/10/16/how-to-display-high_valuelow_value-columns-from-user_tab_col_statistics/
decode(EXTRACTVALUE(VALUE(D), '/bind/@dty'),
1,'VARCHAR2',
2,'NUMBER',
12,'DATE',
21,'BINARY_FLOAT',
22,'BINARY_DOUBLE',
69,'ROWID',
96,'CHAR'))
FROM gv$sql_plan,
TABLE(XMLSEQUENCE(EXTRACT(xmltype(other_xml), '/*/peeked_binds/bind'))) D
where sql_id = '&sqlid'
and other_xml is not null
order by child_number, POSITION
ИНФО из V$SQL_PLAN/DBA_HIST_SQL_PLAN.OTHER_XML
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
extractvalue(xmlval, '/*/info[@type = "db_version"]') as DB_VERSION,
xmlcast(extract(xmlval, '/*/info[@type="load_type"]') as varchar2(200)) as LOAD_TYPES,
extractvalue(xmlval, '/*/info[@type = "dop_reason"]') as DOP_REASON,
extractvalue(xmlval, '/*/info[@type = "dop"]') as DOP,
extractvalue(xmlval, '/*/info[@type = "px_in_memory"]') as PX_IN_MEMORY,
extractvalue(xmlval, '/*/info[@type = "sql_profile"]') as SQL_PROFILE,
extractvalue(xmlval, '/*/info[@type = "sql_patch"]') as SQL_PATCH,
extractvalue(xmlval, '/*/info[@type = "baseline"]') as BASELINE,
extractvalue(xmlval, '/*/info[@type = "outline"]') as OUTLINE,
extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]') as DYNAMIC_SAMPLING,
extractvalue(xmlval, '/*/info[@type = "dop"]') as DOP,
extractvalue(xmlval, '/*/info[@type = "row_shipping"]') as ROW_SHIPPING,
extractvalue(xmlval, '/*/info[@type = "index_size"]') as INDEX_SIZE,
extractvalue(xmlval, '/*/info[@type = "result_checksum"]') as RESULT_CHECKSUM,
extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]') as CARDINALITY_FEEDBACK,
extractvalue(xmlval, '/*/info[@type = "plan_hash"]') as PLAN_HASH,
extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]') as PLAN_HASH_2
from
(select xmltype(other_xml) xmlval from dba_hist_sql_plan where sql_id = '&sql_id' and other_xml is not null)
Предикаты, добавляемые VPD | RLS | FGAC для sql_id
SELECT sql_id,
object_owner,
object_name,
policy_group,
policy,
policy_function_owner,
predicate
FROM gv$vpd_policy
where sql_id = '&sql_id'
