Oracle mechanics

Scripts

Запросы, скрипты и процедуры (скачать одним архивом)

Администрирование, мониторинг системы

PARAM.SQL Открытые параметры Oracle

SET VERIFY OFF
set linesize 200
set pagesize 200
column name format a42
column value format a40
column dsc format a84
column is_def format a8
column is_mod format a10
column is_adj format a8

select name, value, isdefault is_def, ismodified is_mod, description dsc
  from V$PARAMETER
 where name like lower('%' || '&&1' || '%')
    OR lower(description) like lower('%' || '&&1' || '%')
 order by name
/

SET VERIFY ON

PARAM_.SQL Открытые и скрытые (hidden) параметры Oracle

SET VERIFY OFF
set linesize 200
set pagesize 200
column name format a42
column value format a40
column dsc format a84
column is_def format a8
column is_mod format a10
column is_adj format a8

select x.ksppinm name,
       y.kspftctxvl value,
       y.kspftctxdf is_def,
       decode(bitand(y.kspftctxvf, 7),
              1, 'MODIFIED',
              4, 'SYSTEM_MOD',
              'FALSE') is_mod,
       decode(bitand(y.kspftctxvf, 2), 2, 'TRUE', 'FALSE') is_adj,
       x.Ksppdesc dsc
  from sys.x$ksppi x, sys.x$ksppcv2 y
 where x.inst_id = userenv('Instance')
   and y.inst_id = userenv('Instance')
   and x.indx + 1 = y.kspftctxpn
   and (ksppinm like lower('%' || '&&1' || '%') OR
       lower(Ksppdesc) like lower('%' || '&&1' || '%'))
 order by translate(x.ksppinm, ' _', ' ')
/

SET VERIFY ON

Классический запрос Т.Кайта для нахождения столбцов с ограничениями ссылочной целостности без индексов (Unindexed Foreign Keys)

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)
/

Тот же запрос с использованием LISTAGG для Oracle 11g+ из комментария к блогу Т.Кайта

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
/

Медленный запрос для подсчёта количества блоков бд, занятых строками сегмента таблицы в отсутствии 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)
/

ORA-30036: unable to extend segment by % in undo tablespace %:

использование UNDO текущими длинными транзакциями:

SQL> --
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> -- из статистики подключённых сессий
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
------- ---- ----------------------- ----------
2       1504 undo change vector size  710425472 -- bytes, статистика использования UNDO по сессиям
2        411 undo change vector size  522984516
1        705 undo change vector size   90755560
...

UNDOSTAT.SQL Запрос текущего, а также оценки среднего и максимально необходимого (при равномерной по времени генерации undo блоков) размера UNDO пространства на основании статистики V$UNDOSTAT

--
-- Undo space estimation and reqiurements from V$UNDOSTAT
-- Usage: SQL> @undostat
--

set verify off

col INST_ID for 999
col UNDO_RETENTION_PARAM for a20
col Undo_TS_Size_MB for 999,999,999
col Max_Rec_Undo_MB for 999,999,999
col Avg_Rec_Undo_MB for 999,999,999

with undo_retention as
 (select inst_id, value from gv$parameter where name = 'undo_retention'),
undo_ts as
 (select p.inst_id, t.blocksize, sum(f.bytes) BYTES
    FROM sys.ts$ t, gv$parameter p, v$datafile f
   WHERE p.name = 'undo_tablespace'
     and t.name = UPPER(p.value)
     and t.ts# = f.ts#
   group by p.inst_id, t.blocksize),
UndoBlockGeneration as
 (SELECT inst_id,
         max(undoblks / ((end_time - begin_time) * 86400)) max_bytes_per_sec,
         avg(undoblks / ((end_time - begin_time) * 86400)) avg_bytes_per_sec
    FROM gv$undostat
   group by inst_id),
UndoStat as
 (select inst_id,
         min(TUNED_UNDORETENTION) min_tuned_undoretention,
         avg(TUNED_UNDORETENTION) avg_tuned_undoretention,
         max(TUNED_UNDORETENTION) max_tuned_undoretention
    from gv$undostat
   group by inst_id)
select inst_id,
       round(undo_ts.bytes / 1024 / 1024) Undo_TS_Size_MB,
       round((undo_retention.value * undo_ts.blocksize *
             UndoBlockGeneration.avg_bytes_per_sec) / 1024 / 1024) Avg_Rec_Undo_MB,
       round((undo_retention.value * undo_ts.blocksize *
             UndoBlockGeneration.max_bytes_per_sec) / 1024 / 1024) Max_Rec_Undo_MB,
       undo_retention.value UNDO_RETENTION_PARAM,
       UndoStat.MIN_TUNED_UNDORETENTION,
       round(UndoStat.AVG_TUNED_UNDORETENTION) as AVG_TUNED_UNDORETENTION,
       UndoStat.MAX_TUNED_UNDORETENTION
  from undo_retention natural
  join undo_ts natural
  join UndoBlockGeneration natural
  join UndoStat
/

set verify on

, пример:

SQL> @undostat

INST_ID Max Req.Undo Space, MB Avg Req.Undo Space, MB UNDO_RETENTION  MIN_TUNED_UNDORETENTION MAX_TUNED_UNDORETENTION
------- ---------------------- ---------------------- --------------- ----------------------- -----------------------
1                 30,210                    887 4500                               4500           52442
2                 44,716                    813 4500                               3484           52864

Мониторинг использования индексовV$OBJECT_USAGE показывает только объекты текущей схемы, все наблюдаемые индексы можно наблюдать в обзоре V$ALL_OBJECT_USAGE:

select u.name as OWNER,
       io.name as INDEX_NAME,
       t.name as TABLE_NAME,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as MONITORING,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES')    as USED,
       ou.start_monitoring,
       ou.end_monitoring
  from sys.user$        u,
       sys.obj$         io,
       sys.obj$         t,
       sys.ind$         i,
       sys.object_usage ou
 where i.obj# = ou.obj#
   and io.obj# = ou.obj#
   and t.obj# = i.bo#
   and u.user# = io.owner#
/

JOB для мониторинга использования временных табличных пространств:

drop table TEMP_TEMP_SEG_USAGE
/
create table TEMP_TEMP_SEG_USAGE
(INST_ID number,
 DATE_TIME DATE,
 USERNAME VARCHAR2(30),
 SID VARCHAR2(6),
 SERIAL# VARCHAR2(6),
 OS_USER VARCHAR2(30),
 SQL_ID VARCHAR2(13),
 SQL_CHILD_NUMBER NUMBER,
 SQL_EXEC_START DATE,
 SQL_TEXT VARCHAR2(1000),
 TABLESPACE VARCHAR2(31),
 SEGTYPE VARCHAR2(9),
 BLOCKS NUMBER)
tablespace USERS
/
CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
  insert into TEMP_TEMP_SEG_USAGE
    SELECT b.INST_ID,
           sysdate,
           NVL(a.username, 'SUMMARY'),
           a.sid,
           a.serial#,
           a.osuser,
           a.sql_id,
           a.sql_child_number,
           a.sql_exec_start,
           c.sql_text,
           b.tablespace,
           b.segtype,
           sum(b.blocks) as BLOCKS
      FROM gv$tempseg_usage b
      LEFT JOIN gv$session a
        on a.saddr = b.session_addr
       and a.INST_ID = b.INST_ID
      LEFT JOIN gv$sqlarea c
        on c.INST_ID = b.INST_ID
       and c.address = a.sql_address
       and c.hash_value = a.sql_hash_value
     GROUP by GROUPING SETS((b.INST_ID, a.sid, a.username, a.serial#, a.osuser, a.sql_id, a.sql_child_number, a.sql_exec_start, c.sql_text, b.tablespace, b.segtype, sysdate),(sysdate));
  COMMIT;
END;
/
begin
  sys.dbms_scheduler.create_job(job_name        => 'TEMP_TEMP_SEG_USAGE',
                                job_type        => 'PLSQL_BLOCK',
                                job_action      => 'TEMP_TEMP_SEG_USAGE_INSERT;',
                                start_date      => SYSTIMESTAMP,
                                repeat_interval => 'Freq=minutely;interval=5',
                                enabled         => true,
                                auto_drop       => false,
                                comments        => 'TEMP_TEMP_SEG_USAGE');
end;
/
select * from TEMP_TEMP_SEG_USAGE where username = 'SUMMARY' order by date_time desc -- суммарное использование
/
with hottime as                                                                      -- детали распределения временных сегментов в момент
 (select date_time                                                                   -- максимального использования за последние &n часов
    from (select date_time, sum(blocks * t.block_size)
            from system.Temp_Temp_Seg_Usage u, dba_tablespaces t
           where u.tablespace = t.tablespace_name
             and date_time >= sysdate - &n / 24
           group by date_time
           order by sum(blocks * t.block_size) desc)
   where rownum <= 1)
select *
  from system.Temp_Temp_Seg_Usage u, hottime ht
 where u.date_time = ht.date_time
 order by blocks desc
/

Мониторинг длительных ASM операций

drop table SYSTEM.GV_D_ASM_DISK_LC
/
create global temporary table SYSTEM.GV_D_ASM_DISK_LC on commit preserve rows
as select * from SYS.GV$ASM_DISK where 1=0
/
begin
  insert into SYSTEM.GV_D_ASM_DISK_LC
    select * from SYS.GV$ASM_DISK;
  dbms_lock.sleep(10); -- XX sec timeout
  dbms_output.put_line('Inst #' || ' ' || lpad('Disk', 12) || ' ' ||
                       lpad('Reads', 6) || ' ' || 'Min - Max, ms' || ' ' ||
                       'Writes' || ' ' || 'Min - Max, ms' || ' ' ||
                       'Avg.COLDRead / WriteSize,Bytes' || ' ' ||
                       'Avg.HOT Read / WriteSize,Bytes');
  dbms_output.put_line('------ ------------ ------ ------------- ------ ------------- ------------------------------ ------------------------------');
  for reco in (select curr.inst_id,
                      curr.NAME as asm_disk,
                      sum((curr.reads - prev.reads)) reads,
                      min(round(((curr.read_time - prev.read_time) /
                                decode((curr.reads - prev.reads),
                                        0,
                                        -1,
                                        (curr.reads - prev.reads))),
                                4)) * 1000 ms_per_read_min,
                      max(round(((curr.read_time - prev.read_time) /
                                decode((curr.reads - prev.reads),
                                        0,
                                        -1,
                                        (curr.reads - prev.reads))),
                                4)) * 1000 ms_per_read,
                      sum((curr.WRITES - prev.WRITES)) writes,
                      min(round(((curr.WRITE_TIME - prev.WRITE_TIME) /
                                decode((curr.WRITES - prev.WRITES),
                                        0,
                                        -1,
                                        (curr.WRITES - prev.WRITES))),
                                4)) * 1000 ms_per_write_min,
                      max(round(((curr.WRITE_TIME - prev.WRITE_TIME) /
                                decode((curr.WRITES - prev.WRITES),
                                        0,
                                        -1,
                                        (curr.WRITES - prev.WRITES))),
                                4)) * 1000 ms_per_write,
                      avg(round(((curr.COLD_BYTES_READ -
                                prev.COLD_BYTES_READ) /
                                decode((curr.COLD_READS - prev.COLD_READS),
                                        0,
                                        -1,
                                        (curr.COLD_READS - prev.COLD_READS))))) avg_cold_read_size,
                      avg(round(((curr.COLD_BYTES_WRITTEN -
                                prev.COLD_BYTES_WRITTEN) /
                                decode((curr.COLD_WRITES - prev.COLD_WRITES),
                                        0,
                                        -1,
                                        (curr.COLD_WRITES - prev.COLD_WRITES))))) avg_cold_write_size,
                      avg(round(((curr.HOT_BYTES_READ - prev.HOT_BYTES_READ) /
                                decode((curr.HOT_READS - prev.HOT_READS),
                                        0,
                                        -1,
                                        (curr.HOT_READS - prev.HOT_READS))))) avg_hot_read_size,
                      avg(round(((curr.HOT_BYTES_WRITTEN -
                                prev.HOT_BYTES_WRITTEN) /
                                decode((curr.HOT_WRITES - prev.HOT_WRITES),
                                        0,
                                        -1,
                                        (curr.HOT_WRITES - prev.HOT_WRITES))))) avg_hot_write_size
                 from GV$ASM_DISK curr, SYSTEM.GV_D_ASM_DISK_LC prev
                where curr.name is not null
                  and prev.name is not null
                  and prev.disk_number = curr.DISK_NUMBER
                  and prev.group_number = curr.group_number
                  and prev.inst_id = curr.inst_id
                  and nvl(prev.failgroup, 'n') = nvl(curr.failgroup, 'n')
                  and ((curr.read_time - prev.read_time) /
                      decode((curr.reads - prev.reads),
                              0,
                              -1,
                              (curr.reads - prev.reads)) > 1 / 1000 -- read longer than XX ms
                      or (curr.WRITE_TIME - prev.WRITE_TIME) /
                      decode((curr.WRITES - prev.WRITES),
                                 0,
                                 -1,
                                 (curr.WRITES - prev.WRITES)) > 1 / 1000) -- write longer than XX ms
                  and curr.NAME like 'SSD%' -- filter
                group by curr.inst_id, curr.NAME
                order by 1, 2) loop
    dbms_output.put_line(lpad(reco.inst_id, 6) || ' ' ||
                         lpad(reco.asm_disk, 12) || ' ' ||
                         lpad(reco.reads, 6) || ' ' ||
                         lpad(to_char(reco.ms_per_read_min, '9990.9'), 6) || '-' ||
                         lpad(to_char(reco.ms_per_read, '9990.9'), 6) || ' ' ||
                         lpad(reco.writes, 6) || ' ' ||
                         lpad(to_char(reco.ms_per_write_min, '9990.9'), 6) || '-' ||
                         lpad(to_char(reco.ms_per_write, '9990.9'), 6) || ' ' ||
                         lpad(to_char(reco.avg_cold_read_size, '9999990'),
                              12) || ' / ' ||
                         rpad(lpad(to_char(reco.avg_cold_write_size,
                                           '9999990'),
                                   9),
                              15) || ' ' ||
                         lpad(to_char(reco.avg_hot_read_size, '9999990'),
                              12) || ' / ' ||
                         rpad(lpad(to_char(reco.avg_hot_write_size,
                                           '9999990'),
                                   9),
                              15));
  end loop;
  rollback;
end;
/

Online мониторинг блокировок на основе данных GV$LOCK и GV$SESSION

--
-- Current Lock Waits chains based on GV$LOCK
-- Usage: SQL> @lock_tree
-- Igor Usoltsev
--

set echo off feedback off heading on timi off pages 1000 lines 1000 VERIFY OFF

col BLOCKING_TREE for a20
col SQL_TEXT for a100
col EVENT for a40 head "Event name"
col USERNAME for a40
col CLIENT_IDENTIFIER for a30
col P1TEXT for a40
col P2TEXT for a40
col P3TEXT for a40
col BLOCK_SESSTAT for a13
col LAST_CALL_ET for 999999999999
col SECS_IN_WAIT for 999999999999
col CLNT_HOST   for a20
col CLNT_PID    for a10
col OSUSER      for a10
col SPID        for a10

alter session set "_with_subquery"=optimizer
/

with
 LOCKS as (select /*+ MATERIALIZE*/   * from gv$lock)
,S     as (select /*+ MATERIALIZE*/ s.* from gv$session s)
,BLOCKERS as
 (select distinct L1.inst_id, L1.sid
    from LOCKS L1, LOCKS L2
   where L1.block > 0
     and L1.ID1 = L2.ID1
     and L1.ID2 = L2.ID2
     and L2.REQUEST > 0)
,WAITERS as (select inst_id, sid from S where blocking_session is not null or blocking_instance is not null)
select--+ opt_param('_connect_by_use_union_all' 'false')
 LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || s.inst_id || ' SID#' || sid as BLOCKING_TREE,
 s.program,
 substr(s.USERNAME || ' ' || s.CLIENT_IDENTIFIER,1,40) as USERNAME,
 EVENT,
-- object_type || ' ' || owner ||'.'|| object_name req_object,
 last_call_et,
 seconds_in_wait as SECS_IN_WAIT,
 blocking_session_status as BLOCK_SESSTAT,
 pdml_enabled,
 s.sql_id,
 s.osuser,
 p.spid,
 s.machine as CLNT_HOST,
 s.process as CLNT_PID,
 s.port    as CLNT_PORT,
 substr(trim(NVL(sa1.sql_text,sa2.sql_text)), 1, 100) SQL_TEXT,
 decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid,
 p1text || ' ' || decode(p1text, 'name|mode', chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535)||' '||bitand(p1, 65535), p1text) as
 p1text,
 p1,
 p1raw,
 p2text || ' ' || decode(p2text, 'object #', o.object_name || ' ' || o.owner || '.' || o.object_name, p2text) as
 p2text,
 p2
/*,
 p2raw,
 p3text,
 p3,
 p3raw
*/
  from s
  left join gv$sqlarea sa1 on s.sql_id = sa1.sql_id and s.inst_id =  sa1.inst_id
  left join gv$sqlarea sa2 on s.prev_sql_id = sa2.sql_id and s.inst_id =  sa2.inst_id
  left join dba_objects o  on s.p2 = o.object_id
  left join gv$process p on s.paddr = p.addr and s.inst_id = p.inst_id
connect by NOCYCLE prior sid = blocking_session and prior s.inst_id = blocking_instance
 start with (s.inst_id, s.sid)
            in (select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS)
/
set feedback on echo off VERIFY ON

, пример (левее — блокеры, правее — заблокированные сессии):

SQL> @lock_tree

BLOCKING_TREE                           EVENT                         SECONDS_IN_WAIT BLOCKING_SESSION_STATUS PDML_ENABLED SQL_ID        SQL_TEXT
--------------------------------------- ----------------------------- --------------- ----------------------- ------------ ------------- ----------------------------
INST#1 SID#1241 JDBC Thin Client        db file sequential read                     0 NO HOLDER               NO           2r747vzr8fcv7 select * from feature_value
  INST#1 SID#974 JDBC Thin Client       enq: TX - row lock contention              10 VALID                   NO           dbur09rvytbx6 delete from entity_attr wher
INST#1 SID#931 JDBC Thin Client         db file sequential read                     0 NO HOLDER               NO           1y4jp6hk4yvdv INSERT INTO hist_entity_attr
  INST#1 SID#878 JDBC Thin Client       enq: TM - contention                     1267 VALID                   NO           9grspht614m79 delete from entity where ent
    INST#1 SID#685 JDBC Thin Client     enq: TM - contention                     1181 VALID                   NO           8bmzhmdx86rfh INSERT INTO entity (entity_i
    INST#1 SID#1075 JDBC Thin Client    enq: TM - contention                      389 VALID                   NO           1wdwbd2muqyd6 update feature_value set gen
    INST#1 SID#763 JDBC Thin Client     enq: TM - contention                     1124 VALID                   NO           8bmzhmdx86rfh INSERT INTO entity (entity_i
    INST#1 SID#1172 JDBC Thin Client    enq: TM - contention                      760 VALID                   NO           ggygr9v3r9f6t update feature_value set gen
    INST#1 SID#1189 JDBC Thin Client    enq: TM - contention                     1061 VALID                   NO           1wdwbd2muqyd6 update feature_value set gen
      INST#1 SID#1074 JDBC Thin Client  enq: TX - row lock contention             254 VALID                   NO           8vuxbnn2j70b7 delete from entity_attr wher
      INST#1 SID#742 JDBC Thin Client   enq: TX - row lock contention             828 VALID                   NO           dbur09rvytbx6 delete from entity_attr wher
      INST#1 SID#776 JDBC Thin Client   enq: TX - row lock contention            1003 VALID                   NO           dbur09rvytbx6 delete from entity_attr wher
      INST#1 SID#817 JDBC Thin Client   enq: TX - row lock contention             952 VALID                   NO           dbur09rvytbx6 delete from entity_attr wher
      INST#1 SID#820 JDBC Thin Client   enq: TX - row lock contention             725 VALID                   NO           dbur09rvytbx6 delete from entity_attr wher
      INST#1 SID#926 JDBC Thin Client   enq: TX - row lock contention             936 VALID                   NO           8vuxbnn2j70b7 delete from entity_attr wher

Генерация REDO по времени, сегментам, запросамDamir Vadas. How to … redo logs generation?

REDOGEN_SQL_HIST.SQL — топ запросов за период:

--
-- Redo generation SQL top estimation from AWR history
-- Usage: SQL> @redogen_sql_hist "03-Sep-13 16:00" "03-Sep-13 17:00" 10
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col WHEN for a34
col INST_ID for 9999999
col COMMAND for a20
col SQL_ID for a13
col SQL_TEXT for a100
col buffer_gets_range for a12

select *
  from (SELECT dhss.instance_number inst_id,
               to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||
               ' - ' ||
               to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
               dhcn.command_name as COMMAND,
               sum(executions_delta) exec_delta,
               sum(rows_processed_delta) rows_proc_delta,
               sum(dhss.buffer_gets_delta) buffer_gets_delta,
               ROW_NUMBER() OVER(ORDER BY sum(dhss.buffer_gets_delta) DESC) as buffer_range,
               dhss.sql_id,
               replace(dbms_lob.substr(sql_text, 100, 1), chr(10), ' ') sql_text
          FROM dba_hist_sqlstat         dhss,
               dba_hist_snapshot        dhs,
               dba_hist_sqltext         dhst,
               dba_hist_sqlcommand_name dhcn
         WHERE dhst.command_type not in (3, 47) -- != SELECT, PL/SQL EXECUTE
           AND dhss.snap_id = dhs.snap_id
           AND dhss.instance_Number = dhs.instance_number
           AND dhss.sql_id = dhst.sql_id
           and dhst.command_type = dhcn.command_type
           AND begin_interval_time BETWEEN
               to_date('&1', 'DD-Mon-YY HH24:MI') AND
               to_date('&2', 'DD-Mon-YY HH24:MI')
         GROUP BY dhcn.command_name,
                  replace(dbms_lob.substr(sql_text, 100, 1), chr(10), ' '),
                  dhss.instance_number,
                  dhss.sql_id
         order by rows_proc_delta desc, buffer_gets_delta desc)
 where rownum <= &3
/

set feedback on echo off VERIFY ON

REDOGEN_OBJ_HIST.SQL — топ объектов за период:

--
-- Redo generation top by object from AWR history
-- Usage: SQL> @redogen_obj_hist "03-Sep-13 16:00" "03-Sep-13 17:00" 10
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col WHEN for a34
col object_name for a30

select *
  from (SELECT to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||
               ' - ' ||
               to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
               dhso.object_name,
               sum(db_block_changes_delta) as db_block_changes,
               to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER()) * 100, 2), '99.00') as REDO_PERCENT
          FROM dba_hist_seg_stat     dhss,
               dba_hist_seg_stat_obj dhso,
               dba_hist_snapshot     dhs
         WHERE dhs.snap_id = dhss.snap_id
           AND dhs.instance_number = dhss.instance_number
           AND dhss.obj# = dhso.obj#(+)
           AND dhss.dataobj# = dhso.dataobj#(+)
           AND begin_interval_time BETWEEN
               to_date('&1', 'DD-Mon-YY HH24:MI') AND
               to_date('&2', 'DD-Mon-YY HH24:MI')
         GROUP BY dhso.object_name
         ORDER BY db_block_changes desc)
 where rownum <= &&3
/
set feedback on echo off VERIFY ON

, или напрямую Logminer-ом

Мониторинг производительности, ожидания, ASH, AWR

ASW.SQL Суммарные ожидания активных сессий

select RANK,
       WAIT_EVENT,
       lpad(TO_CHAR(PCTTOT, '990D99'), 6) || '% waits with avg.du =' ||
       TO_CHAR(AVERAGE_WAIT_MS, '9999990D99') || ' ms' as EVENT_VALUES
  from (select RANK() OVER(order by sum(time_waited) desc) as RANK,
               event as WAIT_EVENT,
               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
 where RANK <= 10
/

ASH_WAIT_TREE.SQL Очередь ожиданий из истории активных сессий по произвольному условию

--
-- ASH wait tree for Waits Event or SQL_ID
-- Usage: SQL> @ash_wait_tree.sql "event = 'log file sync'"
-- Igor Usoltsev
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col WAIT_LEVEL for 999
col BLOCKING_TREE for a30
col EVENT for a64
col WAITS for 999999
col AVG_TIME_WAITED_MS for 999999

select LEVEL as WAIT_LEVEL,
       LPAD(' ', (LEVEL - 1) * 2) || decode(ash.session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), 'FOREGROUND') as BLOCKING_TREE,
       ash.EVENT,
       count(*) as WAITS_COUNT,
       round(avg(time_waited) / 1000) as AVG_TIME_WAITED_MS,
       round(sum(case when time_waited > 0 then greatest(1, (1000000 / time_waited)) else 0 end)) as est_waits, -- http://www.nocoug.org/download/2013-08/NOCOUG_201308_ASH_Architecture_and_Advanced%20Usage.pdf
       round(sum(1000) / round(sum(case when time_waited > 0 then greatest(1, (1000000 / time_waited)) else 1 end))) as est_avg_latency_ms
  from gv$active_session_history ash
 where session_state = 'WAITING'
 start with &&1 --event = nvl('&&1',event) and sql_id = nvl('&&2',sql_id)
connect by nocycle prior ash.SAMPLE_ID = ash.SAMPLE_ID
       and ash.SESSION_ID = prior ash.BLOCKING_SESSION
 group by LEVEL,
          LPAD(' ', (LEVEL - 1) * 2) || decode(ash.session_type, 'BACKGROUND', REGEXP_SUBSTR(program, '\([^\)]+\)'), 'FOREGROUND'),
          ash.EVENT
 order by LEVEL, count(*) desc
/
set feedback on echo off VERIFY ON

ASH_WAIT_TREE_HIST.SQL Очередь ожиданий из DBA_HIST_ACTIVE_SESS_HISTORY по условию и начальному/конечному снапшотам

--
-- ASH wait tree history for Waits Event or SQL_ID
-- Usage: SQL> @ash_wait_tree_hist.sql "event = 'log file sync'" 1001           1002
--                                      ^condition               ^start snap_id ^finish snap_id
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col INST_ID for 9999999
col LVL for 999
col BLOCKING_TREE for a30
col EVENT for a64
col WAITS for 999999
col AVG_WAIT_TIME_MS for 999999

with ash as
 (select /*+ materialize*/
   *
    from dba_hist_active_sess_history
   where '&&2' is null
      OR snap_id between '&&2' and nvl('&&3', '&&2'))
select LEVEL as LVL,
       instance_number as INST_ID,
       LPAD(' ', (LEVEL - 1) * 2) ||
       decode(ash.session_type,
              'BACKGROUND',
              REGEXP_SUBSTR(program, '\([^\)]+\)'),
              nvl2(qc_session_id, 'PX', 'FOREGROUND')) as BLOCKING_TREE,
       --       ash.SQL_ID,
       decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue') as EVENT,
       count(*) as WAITS_COUNT,
       count(distinct session_id) as SESS_COUNT,
       round(avg(time_waited) / 1000) as AVG_WAIT_TIME_MS
  from ash
-- where session_state = 'WAITING'
 start with &&1
connect by nocycle
 prior ash.SAMPLE_ID = ash.SAMPLE_ID
       and ash.SESSION_ID = prior ash.BLOCKING_SESSION
       and ash.instance_number = prior ash.BLOCKING_inst_id
 group by LEVEL,
          instance_number,
          LPAD(' ', (LEVEL - 1) * 2) ||
          decode(ash.session_type,
                 'BACKGROUND',
                 REGEXP_SUBSTR(program, '\([^\)]+\)'),
                 nvl2(qc_session_id, 'PX', 'FOREGROUND')),
          --        ash.SQL_ID,
          decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue')
 order by instance_number, LEVEL, count(*) desc
/
set feedback on echo off VERIFY ON

ASH_WAIT_TREE4STBY.SQL Очередь ожиданий ASH для физического стендбая

--
-- ASH wait tree for Waits Event or SQL_ID
-- Usage: SQL> @ash_wait_tree4stby "program like 'oracle@db1.com.ru (PR%' and event = 'enq: CF - contention' and sample_time < trunc(sysdate-1)+19/24"
-- Igor Usoltsev
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col INST_ID for 9999999
col LVL for 999
col BLOCKING_TREE for a30
col EVENT for a64
col WAITS for 999999
col AVG_WAIT_TIME_MS for 999999
col SQL_ID for a13
col EST_WAITS for 999999
col EST_AVG_LATENCY_MS for 999999

select LEVEL as LVL,
       LPAD(' ',(LEVEL-1)*2)||decode(ash.session_type,'BACKGROUND',REGEXP_SUBSTR(program, '\([^\)]{3}'), nvl2(qc_session_id, 'PX', 'FOREGROUND')) as BLOCKING_TREE,
       decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue') as EVENT,
       count(*) as WAITS_COUNT,
       count(distinct session_id) as SESS_COUNT,
       round(avg(time_waited) / 1000) as AVG_WAIT_TIME_MS
       ,round(sum(case when time_waited > 0 then greatest(1, (1000000/time_waited)) else 0 end)) as EST_WAITS -- http://www.nocoug.org/download/2013-08/NOCOUG_201308_ASH_Architecture_and_Advanced%20Usage.pdf
       ,round(sum(1000)/round(sum(case when time_waited > 0 then greatest(1, (1000000/time_waited)) else 1 end))) as EST_AVG_LATENCY_MS
  from v$active_session_history ash
-- where session_state = 'WAITING'
 start with &&1
connect by nocycle prior ash.SAMPLE_ID = ash.SAMPLE_ID
       and ash.SESSION_ID = prior ash.BLOCKING_SESSION
 group by LEVEL,
          LPAD(' ',(LEVEL-1)*2)||decode(ash.session_type,'BACKGROUND',REGEXP_SUBSTR(program, '\([^\)]{3}'), nvl2(qc_session_id, 'PX', 'FOREGROUND')),
          decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue')
 order by LEVEL, count(*) desc
/
set feedback on echo off VERIFY ON

ASH_SQLMON2.SQL Статистика/трассировка выполнения запроса на основе ASH, включая рекурсивные запросы и PL/SQL

--
-- SQL Plan Statistics from ASH (including recursive queries and PL/SQL)
-- Usage: SQL> @ash_sqlmon2 &sql_id [&plan_hash_value] [&sql_exec_id]
-- 

set feedback off heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a120
col WAIT_PROFILE for a200
col LAST_PLSQL for a45
col ID for 9999
col OBJECT_OWNER for a12
col OBJECT_NAME for a30
col QBLOCK_NAME for a15

with
 ash0 as (select * from Gv$active_session_history),
 sid_time as -- List of sessions and their start/stop times
 (select nvl(qc_session_id, session_id) as qc_session_id,
         session_id,
         session_serial#,
         sql_id,
         min(sample_time) as MIN_SQL_EXEC_TIME,
         max(sample_time) as MAX_SQL_EXEC_TIME
    from ash0
   where sql_id = '&&1'
     and NVL(sql_plan_hash_value, 0) = nvl('&&2', NVL(sql_plan_hash_value, 0))
     and NVL(sql_exec_id, 0) = nvl('&&3', NVL(sql_exec_id, 0))
   group by nvl(qc_session_id, session_id), session_id, session_serial#, sql_id, sql_plan_hash_value, sql_exec_id)
, ash as (                               -- ASH part, consisting of direct SQL exec ONLy
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         0 as plsql_entry_object_id,     -- important for recrsv queries only
         0 as plsql_entry_subprogram_id, -- --//--
         sh.sql_id,
         NVL2(sql_exec_id,1,null) as SQL_EXEC_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0)                            as SQL_PLAN_LINE_ID,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         min(sample_time)                                    as MIN_SAMPLE_TIME,
         max(sample_time)                                    as MAX_SAMPLE_TIME
    from ash0 sh
   where (sh.sql_id              = '&&1' and                                -- direct SQL exec ONLY
          sh.sql_plan_hash_value = nvl('&&2', sh.sql_plan_hash_value) and
          NVL(sh.sql_exec_id, 0) = nvl('&&3', NVL(sh.sql_exec_id, 0)))
   group by sh.sql_id, NVL2(sql_exec_id,1,null), nvl(sql_plan_hash_value, 0), nvl(sql_plan_line_id, 0), decode(session_state,'WAITING',event,session_state))
, ash_stat as ( -- direct SQL exec stats
select  sql_id,
        SQL_EXEC_ID,
        sql_plan_hash_value,
        sql_plan_line_id,
        sum(WAIT_COUNT) as ASH_ROWS,
        rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc)--.getclobval ()
                                                                                                                   ,'; ') as WAIT_PROFILE,
        max(SID_COUNT)-1 as PX_COUNT,
        max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
from ash
group by sql_id,
         sql_exec_id,
         sql_plan_hash_value,
         sql_plan_line_id)
, ash_recrsv as ( -- ASH part, consisting of indirect / recursive SQLs execs ONLy
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         decode(sh.sql_id, sid_time.sql_id, 0, sh.plsql_entry_object_id)     as plsql_entry_object_id,    -- for recrsv queries only
         decode(sh.sql_id, sid_time.sql_id, 0, sh.plsql_entry_subprogram_id) as plsql_entry_subprogram_id,-- --//--
         sh.sql_id,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0)                            as SQL_PLAN_LINE_ID,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         min(sample_time)                                    as MIN_SAMPLE_TIME,
         max(sample_time)                                    as MAX_SAMPLE_TIME
    from ash0 sh, sid_time
   where ((sh.top_level_sql_id = sid_time.sql_id and sh.sql_id != sid_time.sql_id or sh.sql_id is null) and-- recursive SQLs
          sh.session_id       = sid_time.session_id and
          sh.session_serial#  = sid_time.session_serial# and
          nvl(sh.qc_session_id, sh.session_id) = sid_time.qc_session_id and
          sh.sample_time between sid_time.MIN_SQL_EXEC_TIME and sid_time.MAX_SQL_EXEC_TIME)
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), nvl(sql_plan_line_id, 0), decode(session_state,'WAITING',event,session_state),
            decode(sh.sql_id, sid_time.sql_id, 0, sh.plsql_entry_object_id),
            decode(sh.sql_id, sid_time.sql_id, 0, sh.plsql_entry_subprogram_id))
, ash_stat_recrsv as ( -- recursive SQLs stats
select  ash.plsql_entry_object_id,
        ash.plsql_entry_subprogram_id,
        ash.sql_id,
        sql_plan_hash_value,
        sql_plan_line_id,
        sum(WAIT_COUNT) as ASH_ROWS,
        rtrim(xmlagg(xmlelement(s, EVENT || '(' ||WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc)--.getclobval ()
                                                                                                                  ,'; ') as WAIT_PROFILE,
        max(SID_COUNT)-1 as PX_COUNT,
        max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
from ash_recrsv ash --join sid_time on ash.sql_id <> sid_time.sql_id or ash.sql_id is null
group by ash.plsql_entry_object_id,
         ash.plsql_entry_subprogram_id,
         ash.sql_id,
         sql_plan_hash_value,
         sql_plan_line_id)
, pt as( -- Plan Tables for all excuted SQLs (direct+recursive)
select   sql_id,
         plan_hash_value,
         id,
         operation,
         options,
         object_owner,
         object_name,
         qblock_name,
         nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash union select sql_id, sql_plan_hash_value from ash_recrsv)
  union                                          -- for plans not in dba_hist_sql_plan yet
  select distinct 
         sql_id,
         plan_hash_value,
         id,
         operation,
         options,
         object_owner,
         object_name,
         qblock_name,
         nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash union select sql_id, sql_plan_hash_value from ash_recrsv)
  union                                          -- for plans not in dba_hist_sql_plan not v$sql_plan (read-only standby for example)
  select distinct 
         sql_id,
         sql_plan_hash_value as plan_hash_value,
         sql_plan_line_id    as id,
         sql_plan_operation  as operation,
         sql_plan_options    as options,
         owner               as object_owner,
         object_name,
         ''                  as qblock_name,
         -2                  as parent_id
    from ash0 left join dba_objects on current_obj# = object_id
   where (sql_id, sql_plan_hash_value) in (select sql_id, sql_plan_hash_value from ash union select sql_id, sql_plan_hash_value from ash_recrsv)
     and (sql_id, sql_plan_hash_value) not in (select sql_id, plan_hash_value from gv$sql_plan union all select sql_id, plan_hash_value from dba_hist_sql_plan))
select 'Hard Parse' as LAST_PLSQL, -- the hard parse phase, sql plan does not exists yet, sql_plan_hash_value = 0
       sql_id,
       sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id as ID,
       'sql_plan_hash_value = 0' as PLAN_OPERATION,
       null as object_owner,
       null as object_name,
       null as QBLOCK_NAME,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat
 where sql_plan_hash_value = 0
UNION ALL
select 'Soft Parse' as LAST_PLSQL, -- the soft parse phase, sql plan exists but execution didn't start yet, sql_exec_id is null
       sql_id,              
       sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id as ID,
       'sql_plan_hash_value > 0; sql_exec_id is null' as PLAN_OPERATION,
       null as object_owner,
       null as object_name,
       null as QBLOCK_NAME,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat
 where sql_plan_hash_value > 0
   and sql_exec_id is null
UNION ALL
SELECT 'Main Query w/o saved plan'       -- direct SQL which plan not in gv$sql_plan, dba_hist_sql_plan (ro-standby)
                                                                 as LAST_PLSQL,
       pt.sql_id                                                 as SQL_ID,
       pt.plan_hash_value                                        as plan_hash_value,
       pt.id,
       lpad(' ', id) || pt.operation || ' ' || pt.options        as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.PX_COUNT                                         as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
  on --pt.parent_id       = -2 and
     pt.id              = NVL(ash_stat.sql_plan_line_id,0) and
     pt.sql_id          = ash_stat.sql_id and
     pt.plan_hash_value = ash_stat.sql_plan_hash_value         -- sql_plan_hash_value > 0
                      and ash_stat.sql_exec_id is not null
  where pt.parent_id       = -2
UNION ALL
SELECT case when pt.id =0 then 'Main Query' -- direct SQL plan+stats
            when ash_stat.MAX_SAMPLE_TIME > sysdate - 10/86400 then '>>>'
            when ash_stat.MAX_SAMPLE_TIME > sysdate - 30/86400 then '>> '
            when ash_stat.MAX_SAMPLE_TIME > sysdate - 60/86400 then '>  '
            else '   ' end as LAST_PLSQL,
       decode(pt.id, 0, pt.sql_id, null) as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as plan_hash_value,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
  on pt.id              = NVL(ash_stat.sql_plan_line_id,0) and
     pt.sql_id          = ash_stat.sql_id and
     pt.plan_hash_value = ash_stat.sql_plan_hash_value         -- sql_plan_hash_value > 0
                      and ash_stat.sql_exec_id is not null
  where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
SELECT decode(pt.id, 0, p.object_name||'.'||p.procedure_name, null) as LAST_PLSQL, -- recursive SQLs plan+stats
       decode(pt.id, 0, pt.sql_id, null) as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as plan_hash_value,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat_recrsv ash_stat
  on pt.id              = NVL(ash_stat.sql_plan_line_id,0) and
     pt.sql_id          = ash_stat.sql_id and
    (pt.plan_hash_value = ash_stat.sql_plan_hash_value or ash_stat.sql_plan_hash_value = 0)
  left join dba_procedures p on ash_stat.plsql_entry_object_id     = p.object_id and
                                ash_stat.plsql_entry_subprogram_id = p.subprogram_id
  where pt.sql_id in (select sql_id from ash_stat_recrsv)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'Recurs.waits' as LAST_PLSQL, -- non-identified SQL (PL/SQL?) exec stats
       '',
       0 as plan_hash_value,
       ash_stat.sql_plan_line_id,
       'sql_id is null and plsql[_entry]_object_id is null' as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat_recrsv ash_stat
 where sql_id is null
   and ash_stat.plsql_entry_object_id is null
UNION ALL
select 'PL/SQL' as LAST_PLSQL, -- non-identified SQL (PL/SQL?) exec stats
       '',
       0 as plan_hash_value,
       ash_stat.sql_plan_line_id,
       p.owner ||' '|| p.object_name||'.'||p.procedure_name as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.PX_COUNT as PX,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat_recrsv ash_stat
  join dba_procedures p on ash_stat.plsql_entry_object_id     = p.object_id and
                                ash_stat.plsql_entry_subprogram_id = p.subprogram_id
 where sql_id is null
/
set feedback on VERIFY ON timi on

ASH_SQLMON.SQL Статистика ожиданий плана выполнения из ASH по SQL_ID, PLAN_HASH_VALUE, SQL_EXEC_ID

--
-- SQL Plan Statistics for SQL_EXEC_ID from ASH
-- Usage: SQL> @ash_sqlmon &sql_id [&plan_hash_value] [&sql_exec_id]
-- https://iusoltsev.wordpress.com
--

set feedback off heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a100
col WAIT_PROFILE for a200

with ash as
 (select sql_id,
         sql_plan_hash_value,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT
    from v$active_session_history
   where sql_id = '&1'
     and sql_plan_hash_value = nvl('&2', sql_plan_hash_value)
     and NVL(sql_exec_id, 0) = nvl('&3', NVL(sql_exec_id, 0))
   group by sql_id,
            sql_plan_hash_value,
            sql_plan_line_id,
            decode(session_state, 'WAITING', event, session_state)),
ash_stat as
 (select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ')
                      .extract('//text()') order by WAIT_COUNT desc),
               ',') as WAIT_PROFILE
    from ash
   group by sql_id, sql_plan_hash_value, sql_plan_line_id),
pt as
 (select id, operation, options, object_owner, object_name, parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) =
         (select distinct sql_id, sql_plan_hash_value from ash_stat)
  union -- for plans not in dba_hist_sql_plan yet
  select id, operation, options, object_owner, object_name, parent_id
    from v$sql_plan
   where (sql_id, plan_hash_value) =
         (select distinct sql_id, sql_plan_hash_value from ash_stat))
SELECT pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = ash_stat.sql_plan_line_id
CONNECT BY PRIOR pt.id = pt.parent_id
 START WITH pt.id = 0
/
set feedback on VERIFY ON timi on

ASH_SQLMON_HIST.SQL Статистика ожиданий плана выполнения из истории ASH по SQL_ID, [PLAN_HASH_VALUE], [SQL_EXEC_ID], [FROM_SNAP_ID]

--
-- Real SQL Plan Execution Statistics by SQL_EXEC_ID, FROM_SNAP_ID from DBA_HIST_ACTIVE_SESS_HISTORY
-- Usage: SQL> @ash_sqlmon_hist &sql_id [&plan_hash_value] [&sql_exec_id] [&from_snap_id]
-- https://iusoltsev.wordpress.com
--

set feedback off heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a100
col WAIT_PROFILE for a200

with ash as
 (select sql_id,
         sql_plan_hash_value,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT
    from dba_hist_active_sess_history
   where sql_id = '&1'
     and sql_plan_hash_value = nvl('&2', sql_plan_hash_value)
     and NVL(sql_exec_id, 0) = nvl('&3', NVL(sql_exec_id, 0))
     and snap_id >= nvl(&4, snap_id)
   group by sql_id,
            sql_plan_hash_value,
            sql_plan_line_id,
            decode(session_state, 'WAITING', event, session_state)),
ash_stat as
 (select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ')
                      .extract('//text()') order by WAIT_COUNT desc),
               ',') as WAIT_PROFILE
    from ash
   group by sql_id, sql_plan_hash_value, sql_plan_line_id),
pt as
 (select *
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) =
         (select distinct sql_id, sql_plan_hash_value from ash_stat))
SELECT pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.cost,
       pt.cardinality,
       pt.bytes,
       pt.qblock_name,
       pt.temp_space,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = ash_stat.sql_plan_line_id
CONNECT BY PRIOR pt.id = pt.parent_id
 START WITH pt.id = 0
/
set feedback on VERIFY ON timi on

ASH_PLSQLMON.SQL Трассировка PL/SQL блока/процедуры по [TOP_LEVEL_]SQL_ID на основе записей ASH, включая статистику планов выполнения рекурсивных SQL запросов

--
-- PL/SQL trace with recursive SQL Plan Statistics from ASH (including recursive queries and PL/SQL)
-- Usage: SQL> @ash_plsqlmon 5t39uchjqpyfm
--                           ^[top_level_]sql_id
-- 

set feedback on heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a180
col WAIT_PROFILE for a200
col SQL_TEXT for a80
col MIN_TIME for a8
col MAX_TIME for a8

PROMPT
PROMPT ***** Summary by SQL execs *****

with hash as (select /*+ INLINE*/ * from gv$active_session_history where (sql_id = '&&1' or top_level_sql_id = '&&1'))
, ash as (
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         count( distinct SQL_EXEC_ID)                        as EXECS,
         MIN(SAMPLE_TIME)                                    as MIN_SAMPLE_TIME,
         max(SAMPLE_TIME)                                    as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), decode(session_state,'WAITING',event,session_state),PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID)
select  sql_id,
        sql_plan_hash_value,
        sum(WAIT_COUNT)                                         as ASH_ROWS,
        max(EXECS)                                              as EXECS,
        to_char(min(min_sample_time),'hh24:mi:ss')              as MIN_TIME,
        to_char(max(max_sample_time),'hh24:mi:ss')              as MAX_TIME,
        trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as sql_text,
        substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE
from ash left join dba_hist_sqltext using (sql_id)
group by sql_id,
         sql_plan_hash_value,
         trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))
order by sum(WAIT_COUNT) desc
/

PROMPT
PROMPT
PROMPT ***** SQL Plan/PLSQL execs details *****

with 
hash as (select /*+ INLINE*/ * from gv$active_session_history where (sql_id = '&&1' or top_level_sql_id = '&&1')),
ash as
 (select count(distinct sh.session_id || sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0) as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT,
         count(distinct SQL_EXEC_ID) as EXECS,
         min(sample_time) as MIN_SAMPLE_TIME,
         max(sample_time) as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id,
            nvl(sql_plan_hash_value, 0),
            nvl(sql_plan_line_id, 0),
            decode(session_state, 'WAITING', event, session_state),
            PLSQL_ENTRY_OBJECT_ID,
            PLSQL_ENTRY_SUBPROGRAM_ID),
ash_stat as
 (                                     -- all SQL exec stats
  select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sum(WAIT_COUNT) as ASH_ROWS,
         substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
         max(EXECS) as EXECS,
         max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
    from ash
   group by sql_id,
             sql_plan_hash_value,
             sql_plan_line_id,
             PLSQL_ENTRY_OBJECT_ID,
             PLSQL_ENTRY_SUBPROGRAM_ID),
pt as                                  -- Plan Tables for all excuted SQLs (direct+recursive)
 (select sql_id,
          plan_hash_value,
          id,
          operation,
          options,
          object_owner,
          object_name,
          qblock_name,
          nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in
         (select sql_id, sql_plan_hash_value from ash)
  union all                            -- for plans not in dba_hist_sql_plan yet
  select distinct sql_id,
                  plan_hash_value,
                  id,
                  operation,
                  options,
                  object_owner,
                  object_name,
                  qblock_name,
                  nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)
     and not exists
        (select 1 from dba_hist_sql_plan where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)))
SELECT                                 -- standard recursive SQLs 
       decode(pt.id, 0, 'SQL Query', null)        as SQL_PLSQL,
       decode(pt.id, 0, pt.sql_id, null)          as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as PLAN_HASH_VALUE,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = NVL(ash_stat.sql_plan_line_id, 0)
   and pt.sql_id = ash_stat.sql_id
   and pt.plan_hash_value = ash_stat.sql_plan_hash_value
 where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'PL/SQL' as SQL_PLSQL,          -- non-identified by SQL or PLSQL exec stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       nvl2(p.object_name, p.owner||'.'||p.object_name||'.'||p.procedure_name||'"', trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
                left join dba_procedures p on ash_stat.PLSQL_ENTRY_OBJECT_ID = p.object_id and ash_stat.PLSQL_ENTRY_SUBPROGRAM_ID = p.subprogram_id
 where sql_id is null
    or (sql_plan_hash_value = 0 and sql_id not in (select sql_id from pt))
UNION ALL
select 'SQL w/o plan' as SQL_PLSQL,    -- SQL with non-identified plan stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
 where sql_id not in (select sql_id from pt)
   and sql_id is not null
   and sql_plan_hash_value != 0
/
set VERIFY ON timi on

ASH_PLSQLMON_HIST.SQL Трассировка PL/SQL блока/процедуры на основе истории ASH

--
-- PL/SQL trace with recursive SQL Plan Statistics from ASH history (including recursive queries and PL/SQL)
-- Usage: SQL> @ash_plsqlmon_hist 5t39uchjqpyfm 73250       [73252]
--                                ^sql_id       ^start_snap ^stop_snap
-- 

set feedback on heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col PLAN_OPERATION for a180
col WAIT_PROFILE for a200
col SQL_TEXT for a80
col MIN_TIME for a8
col MAX_TIME for a8

PROMPT
PROMPT ***** Summary by SQL execs *****

with hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
              where (sql_id = '&&1' or top_level_sql_id = '&&1') and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2')))
, ash as (
  select count(distinct sh.session_id||sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0)                         as SQL_PLAN_HASH_VALUE,
         decode(session_state,'WAITING',event,session_state) as EVENT,
         count(*)                                            as WAIT_COUNT,
         count( distinct SQL_EXEC_ID)                        as EXECS,
         MIN(SAMPLE_TIME)                                    as MIN_SAMPLE_TIME,
         max(SAMPLE_TIME)                                    as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id, nvl(sql_plan_hash_value, 0), decode(session_state,'WAITING',event,session_state),PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID)
select  sql_id,
        sql_plan_hash_value,
        sum(WAIT_COUNT)                                         as ASH_ROWS,
        max(EXECS)                                              as EXECS,
        to_char(min(min_sample_time),'hh24:mi:ss')              as MIN_TIME,
        to_char(max(max_sample_time),'hh24:mi:ss')              as MAX_TIME,
        trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as sql_text,
        substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE
from ash left join dba_hist_sqltext using (sql_id)
group by sql_id,
         sql_plan_hash_value,
         trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))
order by sum(WAIT_COUNT) desc
/

PROMPT
PROMPT
PROMPT ***** SQL Plan/PLSQL execs details *****

with 
hash as (select /*+ INLINE*/ * from dba_hist_active_sess_history
              where (sql_id = '&&1' or top_level_sql_id = '&&1') and ('&&2' is null OR snap_id between '&&2' and nvl('&&3', '&&2'))),
ash as
 (select count(distinct sh.session_id || sh.session_serial#) as SID_COUNT,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sh.SQL_ID,
         nvl(sql_plan_hash_value, 0) as SQL_PLAN_HASH_VALUE,
         nvl(sql_plan_line_id, 0) as SQL_PLAN_LINE_ID,
         decode(session_state, 'WAITING', event, session_state) as EVENT,
         count(*) as WAIT_COUNT,
         count(distinct SQL_EXEC_ID) as EXECS,
         min(sample_time) as MIN_SAMPLE_TIME,
         max(sample_time) as MAX_SAMPLE_TIME
    from hash sh
   group by sh.sql_id,
            nvl(sql_plan_hash_value, 0),
            nvl(sql_plan_line_id, 0),
            decode(session_state, 'WAITING', event, session_state),
            PLSQL_ENTRY_OBJECT_ID,
            PLSQL_ENTRY_SUBPROGRAM_ID),
ash_stat as
 (                                     -- all SQL exec stats
  select sql_id,
         sql_plan_hash_value,
         sql_plan_line_id,
         PLSQL_ENTRY_OBJECT_ID,
         PLSQL_ENTRY_SUBPROGRAM_ID,
         sum(WAIT_COUNT) as ASH_ROWS,
         substr(rtrim(xmlagg(xmlelement(s, EVENT || '(' || WAIT_COUNT, '); ').extract('//text()') order by WAIT_COUNT desc),'; '),1,200) as WAIT_PROFILE,
         max(EXECS) as EXECS,
         max(MAX_SAMPLE_TIME) as MAX_SAMPLE_TIME
    from ash
   group by sql_id,
             sql_plan_hash_value,
             sql_plan_line_id,
             PLSQL_ENTRY_OBJECT_ID,
             PLSQL_ENTRY_SUBPROGRAM_ID),
pt as                                  -- Plan Tables for all excuted SQLs (direct+recursive)
 (select sql_id,
          plan_hash_value,
          id,
          operation,
          options,
          object_owner,
          object_name,
          qblock_name,
          nvl(parent_id, -1) as parent_id
    from dba_hist_sql_plan
   where (sql_id, plan_hash_value) in
         (select sql_id, sql_plan_hash_value from ash)
  union all                            -- for plans not in dba_hist_sql_plan yet
  select distinct sql_id,
                  plan_hash_value,
                  id,
                  operation,
                  options,
                  object_owner,
                  object_name,
                  qblock_name,
                  nvl(parent_id, -1) as parent_id
    from gv$sql_plan
   where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)
     and not exists
        (select 1 from dba_hist_sql_plan where (sql_id, plan_hash_value) in (select sql_id, sql_plan_hash_value from ash)))
SELECT                                 -- standard recursive SQLs 
       decode(pt.id, 0, 'SQL Query', null)        as SQL_PLSQL,
       decode(pt.id, 0, pt.sql_id, null)          as SQL_ID,
       decode(pt.id, 0, pt.plan_hash_value, null) as PLAN_HASH_VALUE,
       pt.id,
       lpad(' ', 2 * level) || pt.operation || ' ' || pt.options as PLAN_OPERATION,
       pt.object_owner,
       pt.object_name,
       pt.qblock_name,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  FROM pt
  left join ash_stat
    on pt.id = NVL(ash_stat.sql_plan_line_id, 0)
   and pt.sql_id = ash_stat.sql_id
   and pt.plan_hash_value = ash_stat.sql_plan_hash_value
 where pt.sql_id in (select sql_id from ash_stat)
CONNECT BY PRIOR pt.id = pt.parent_id
       and PRIOR pt.sql_id = pt.sql_id
       and PRIOR pt.plan_hash_value = pt.plan_hash_value
 START WITH pt.id = 0
UNION ALL
select 'PL/SQL' as SQL_PLSQL,          -- non-identified by SQL or PLSQL exec stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       nvl2(p.object_name, p.owner||'.'||p.object_name||'.'||p.procedure_name||'"', trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9)))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
                left join dba_procedures p on ash_stat.PLSQL_ENTRY_OBJECT_ID = p.object_id and ash_stat.PLSQL_ENTRY_SUBPROGRAM_ID = p.subprogram_id
 where sql_id is null
    or (sql_plan_hash_value = 0 and sql_id not in (select sql_id from pt))
UNION ALL
select 'SQL w/o plan' as SQL_PLSQL,    -- SQL with non-identified plan stats
       sql_id,
       ash_stat.sql_plan_hash_value as plan_hash_value,
       ash_stat.sql_plan_line_id,
       trim(replace(replace(replace(dbms_lob.substr(sql_text,80),chr(10)),chr(13)),chr(9))) as PLAN_OPERATION,
       null,
       null,
       null,
       ash_stat.EXECS,
       ash_stat.ASH_ROWS,
       ash_stat.WAIT_PROFILE
  from ash_stat left join dba_hist_sqltext using (sql_id)
 where sql_id not in (select sql_id from pt)
   and sql_id is not null
   and sql_plan_hash_value != 0
/
set VERIFY ON timi on

KGLLOCKPIN.SQL Объекты и субъекты ожиданий library cache lock / library cache pin

set feedback off heading on timi off pages 100 lines 800 echo off  VERIFY OFF

col HANDLER      for a16
col kglobtyd     for a30
col kglnaown     for a30
col kglnaobj     for a30
col kgllktype    for a10
col mode_held    for a10
col mode_req     for a10
col secs_in_wait for a12
col event        for a40
col sid          for a6
col serial       for a7
col program      for a30
col sql_text     for a100

select --+ ordered
 l.kgllkhdl as HANDLER,
 w.kglobtyd,
 w.kglnaown,
 w.kglnaobj,
 l.kgllktype,
 decode(l.kgllkmod,
        0,
        'None',
        1,
        'Null',
        2,
        'Share',
        3,
        'Exclusive',
        'Unknown') mode_held,
 decode(l.kgllkreq,
        0,
        'None',
        1,
        'Null',
        2,
        'Share',
        3,
        'Exclusive',
        'Unknown') mode_req,
 to_char(s.seconds_in_wait) as secs_in_wait,
 s.event,
 to_char(s.sid) as sid,
 to_char(s.serial#) as serial,
 s.program,
 substr(a.sql_text, 1, 100) as sql_text
  from (select distinct p1raw
          from v$session
         where state = 'WAITING'
           and event in ('library cache lock', 'library cache pin')) b,
       dba_kgllock l,
       x$kglob w,
       v$session s,
       v$sqlarea a
 where l.kgllkhdl = b.p1raw
   and l.kgllkhdl = w.kglhdadr
   and l.kgllkuse = s.saddr
   and s.sql_address = a.address(+)
   and s.sql_hash_value = a.hash_value(+)
 order by kgllkhdl, s.seconds_in_wait desc, l.kgllktype
/
set feedback on VERIFY ON

ASH_LOCK3.SQL Блокировки сессий по данным ASH за период

--
-- Session lock tree from ASH
-- Usage: SQL> @ash_lock3 "20130519 20:15:27" "20130519 20:15:28"
-- https://iusoltsev.wordpress.com
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col LVL for a3
col BLOCKING_TREE for a20
col SQL_TEXT for a100
col EVENT for a34
col Lock_Mode for a12
col BLOCKER for a12
col OBJECT_NAME for a40
col SQL_PLAN_OPERATION for a50

with ash as
 (select *
    from v$active_session_history
   where sample_time between to_date('&1', 'yyyymmdd hh24:mi:ss') and
         to_date('&2', 'yyyymmdd hh24:mi:ss'))
select to_char(LEVEL) as LVL,
       LPAD(' ', (LEVEL - 1) * 2) || session_id || ',' || session_serial# as BLOCKING_TREE,
       to_char(min(sample_time), 'hh24:mi:ss') as START_WTIME,
       to_char(max(sample_time), 'hh24:mi:ss') as STOP_WTIME,
       xid,
       decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue') as EVENT,
       decode(event,
              'enq: TM - contention',
              chr(bitand(p1, -16777216) / 16777215) ||
              chr(bitand(p1, 16711680) / 65535) || ' ' || bitand(p1, 65535),
              '') as "Lock_Mode",
       blocking_session || ',' || blocking_session_serial# as BLOCKER,
       o.object_type || ' ' || o.object_name as OBJECT_NAME,
       count(distinct sample_id) as WAITS_COUNT,
       sql_plan_operation || ' ' || sql_plan_options as SQL_PLAN_OPERATION,
       trim(replace(replace(dbms_lob.substr(sql_text, 100), chr(10)),
                    chr(9))) as sql_text
  from ash
  left join dba_hist_sqltext hs
    on ash.sql_id = hs.sql_id
  left join dba_objects o
    on decode(event, 'enq: TM - contention', ash.p2, ash.current_obj#) =
       o.object_id
 where session_state = 'WAITING'
 start with BLOCKING_SESSION is not null
        and session_serial# > 1 -- для исключения системных процессов
        and event not like 'log file sync'
connect by nocycle ash.SAMPLE_ID = prior ash.SAMPLE_ID
       and ash.SESSION_ID = prior ash.BLOCKING_SESSION
 group by LEVEL,
          LPAD(' ', (LEVEL - 1) * 2) || session_id || ',' ||
          session_serial#,
          xid,
          decode(session_state, 'WAITING', EVENT, 'On CPU / runqueue'),
          decode(event,
                 'enq: TM - contention',
                 chr(bitand(p1, -16777216) / 16777215) ||
                 chr(bitand(p1, 16711680) / 65535) || ' ' ||
                 bitand(p1, 65535),
                 ''),
          blocking_session || ',' || blocking_session_serial#,
          o.object_type || ' ' || o.object_name,
          hs.sql_id,
          sql_plan_operation || ' ' || sql_plan_options,
          trim(replace(replace(dbms_lob.substr(sql_text, 100), chr(10)),
                       chr(9)))
 order by LEVEL,
          trim(LPAD(' ', (LEVEL - 1) * 2) || session_id || ',' ||
               session_serial#),
          min(sample_time)
/
set feedback on echo off VERIFY ON

Скрипт, показывающий запросы и объекты (вплоть до конкретных блоков бд) кросс-инстасных кластерных ожиданий на основе совпадений файл-блок-ориентированных параметров ожиданий в ASH. Результат может заметно не совпадать с указанными в ASH BLOCKING_INST_ID, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, т.о. дополняя картину происходящего

--
-- ASH cluster waits by concurrent SQLs and objects from the simultaneous request for the same db blocks
-- Most useful for the Cluster waits problem periods
-- Usage: SQL> @ash_sql_cluster_block_waits "5cbp4s5jq04xg','2f2k6jtdtk1cq"
-- by Igor Usoltsev
--

set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col INST_ID1 for 9999999
col INST_ID2 for 9999999
col EVENT1 for a40
col EVENT2 for a40
col object_type for a20
col object_name for a40

with ash as
 (select /*+ MATERIALIZE*/
   *
    from gv$active_session_history)
select ash1.inst_id as INST_ID1,
       ash1.sql_id as SQL_ID1,
       ash1.event as EVENT1,
       ash1.blocking_inst_id as ASH_BLOCK_INST, -- блокирующий INST_ID по версии ASH
       ash2.inst_id as REM_BLOCK_INST, -- блокирующий INST_ID по конкуренции за блоки бд
       ash2.sql_id as SQL_ID2,
       ash2.event as EVENT2,
       o.object_type,
       o.object_name,
       count(*) as WAITS_COUNT,
       count(distinct ash1.current_file# || ash1.current_block#) as CONC_BLOCK_COUNT
  from ash ash1
  join ash ash2
    on ash1.current_file# = ash2.current_file#
   and ash1.current_block# = ash2.current_block#
   and ash1.wait_class = ash2.wait_class
   and ash1.session_state = ash2.session_state
   and ash1.p1text = ash2.p1text
   and ash1.inst_id <> ash2.inst_id                       -- с разных нод
   and (ash1.sql_id in ('&&1') or ash2.sql_id in ('&&1'))
   and ash2.sample_time > ash1.sample_time                -- недублированные
  join dba_objects o
    on ash1.current_obj# = object_id
 where to_char(ash2.sample_time, 'SSSSS') - to_char(ash1.sample_time, 'SSSSS') <= 1 -- почти одновременные
   and ash1.wait_class = 'Cluster'                        -- кластерные
   and ash1.p1text = 'file#'                              -- блок-ориентированные
   and ash1.session_state = 'WAITING'                     -- ожидания
 group by ash1.inst_id,
          ash1.sql_id,
          ash1.event,
          ash1.blocking_inst_id,
          ash2.inst_id,
          ash2.sql_id,
          ash2.event,
          o.object_type,
          o.object_name
 order by count(*) desc
/
set feedback on echo off VERIFY ON

SESSON.SQL — запуск мониторинга сессии по SID

set feedback off
declare
 gtt_not_exists EXCEPTION;
 PRAGMA EXCEPTION_INIT(gtt_not_exists, -942);
begin
--V$SESS_TIME_MODEL
  begin
     execute immediate 'truncate table gtt$sess_time_model';
     execute immediate 'insert into gtt$sess_time_model select * from v$sess_time_model where sid = '||&&1;
  exception
      when gtt_not_exists then
             execute immediate 'create global temporary table gtt$sess_time_model on commit preserve rows
             as
             select * from v$sess_time_model where sid = ' || &&1;
  end;
--V$SESSTAT
  begin
     execute immediate 'truncate table gtt$sesstat';
     execute immediate 'insert into gtt$sesstat select * from v$sesstat where sid = '||&&1;
  exception
      when gtt_not_exists then
             execute immediate 'create global temporary table gtt$sesstat on commit preserve rows
             as
             select * from v$sesstat where sid = ' || &&1;
  end;
--V$SESSION_EVENT
  begin
     execute immediate 'truncate table gtt$session_event';
     execute immediate 'insert into gtt$session_event select * from v$session_event where sid = '||&&1;
  exception
      when gtt_not_exists then
             execute immediate 'create global temporary table gtt$session_event on commit preserve rows
             as
             select * from v$session_event where sid = ' || &&1;
  end;
end;
/
set feedback on

SESSOF.SQL — [промежуточные] рез-ты мониторинга сессии

--
-- Session query statistics
-- Usage:
-- SQL> @sesson &SID
-- SQL> @sessof
--
-- For summary (QC+PX) execution only stats (without fetch stats):
-- SQL> set pages 1 pau on
--
-- For summary execution and fetch stats:
-- SQL> set pages 0 arraysize 5000
-- 

col name for a64
col delta for 999999999999
col waits for 999999999999
col time_ms for 999999999999
col TIMEOUTS for 999999999999
col avg_wait_ms for 999,999

set pagesize 1000 feedback off VERIFY OFF timi off

prompt
prompt
prompt Session Time Model

select stm.stat_name, sum(stm.value) - nvl(sum(gtm.value),0) as delta
  from v$sess_time_model stm left join gtt$sess_time_model gtm using (stat_id)
 where stm.sid in (select sid from v$px_session where qcsid = &&1 and sid <> qcsid
                   union all
                   select &&1 from dual)
 group by stm.stat_name
having sum(stm.value) - nvl(sum(gtm.value),0) > 0
 order by 2 desc
/

prompt
prompt
prompt Session Statistics

select --+ RULE
 v$statname.name, sum(sst.value) - nvl(sum(gst.value),0) as delta
  from v$sesstat sst
       join v$statname using (statistic#)
       left join gtt$sesstat gst using (statistic#)
 where sst.sid in (select sid from v$px_session where qcsid = &&1 and sid <> qcsid
                   union all
                   select &&1 from dual)
 group by v$statname.name, gst.value
having sum(sst.value) - nvl(sum(gst.value),0) > 0
 order by 2 desc
/

prompt
prompt
prompt Session Wait Events

select EVENT as name,
       wait_class,
       sum(sev.TOTAL_WAITS)              - nvl(sum(gev.TOTAL_WAITS),0)                as WAITS,
       round((sum(sev.TIME_WAITED_micro) - nvl(sum(gev.TIME_WAITED_micro),0)) / 1000) as TIME_MS,
       sum(sev.TOTAL_TIMEOUTS)           - nvl(sum(gev.TOTAL_TIMEOUTS),0)             as TIMEOUTS,
       round((sum(sev.TIME_WAITED_micro) - nvl(sum(gev.TIME_WAITED_micro),0)) / 1000 /
             decode((sum(sev.TOTAL_WAITS)- nvl(sum(gev.TOTAL_WAITS),0))
                   , 0, 1
                   ,(sum(sev.TOTAL_WAITS)    - nvl(sum(gev.TOTAL_WAITS),0)))
         , 1)                                                                         as AVG_WAIT_MS
  from v$session_event sev left join gtt$session_event gev using (EVENT, wait_class)
 where sev.sid in (select sid from v$px_session where qcsid = &&1 and sid <> qcsid
                   union all
                   select &&1 from dual)
   and wait_class <> 'Idle'
 group by EVENT, wait_class
having (sum(sev.TOTAL_WAITS) - nvl(sum(gev.TIME_WAITED_micro),0)) > 0
--   and (sum(sev.TOTAL_WAITS)       - nvl(sum(gev.TOTAL_WAITS),0)) > 0
 order by (sum(sev.TIME_WAITED_micro) - nvl(sum(gev.TIME_WAITED_micro),0)) desc
/
prompt
prompt
set VERIFY ON timi on
История запросов, ожиданий, статистик, etc.

DBA_HIST_SQLSTAT.SQL Сравнительная история выполнения запросов из AWR

--
-- short SQL execution history
-- SQL> @dba_hist_sqlstat "sql_id = 'acn3a69qq1shm' and snap_id >= 13381 and executions_delta > 0"
--
select
instance_number as inst,
(snap_id - 1) as Begin_Snap_id,
to_char(sn.begin_interval_time,'dd.mm hh24:mi') as begin_snap_time,
round(st.executions_delta) as execs,
st.sql_id,
st.plan_hash_value as plan,
st.optimizer_cost as cost,
round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta),3)                   as PARSE_PER_EXEC,
round(st.elapsed_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                    as ELA_PER_EXEC,
round(st.cpu_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                        as CPU_PER_EXEC,
round(st.buffer_gets_delta/decode(st.executions_delta,0,1,st.executions_delta))                     as GETS_PER_EXEC,
round(st.physical_read_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024)   as READ_MB_PER_EXEC,
round(st.physical_read_requests_delta/decode(st.executions_delta,0,1,st.executions_delta))          as READS_PER_EXEC,
round(st.physical_write_bytes_delta/decode(st.executions_delta,0,1,st.executions_delta)/1024/1024)  as WRITES_MB_PER_EXEC,
round(st.physical_write_requests_delta/decode(st.executions_delta,0,1,st.executions_delta))         as WRITES_PER_EXEC,
round(st.direct_writes_delta/decode(st.executions_delta,0,1,st.executions_delta))                   as DIRECT_WRITES_PER_EXEC,
round(st.rows_processed_delta/decode(st.executions_delta,0,1,st.executions_delta))                  as ROWS_PER_EXEC,
round(st.fetches_delta/decode(st.executions_delta,0,1,st.executions_delta))                         as FETCHES_PER_EXEC,
round(st.iowait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as IOWAITS_PER_EXEC,
round(st.clwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as CLWAITS_PER_EXEC,
round(st.apwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as APWAITS_PER_EXEC,
round(st.ccwait_delta/decode(st.executions_delta,0,1,st.executions_delta))                          as CCWAITS_PER_EXEC,
round(st.parse_calls_delta/decode(st.executions_delta,0,1,st.executions_delta))                     as PARSE_PER_EXEC,
round(st.plsexec_time_delta/decode(st.executions_delta,0,1,st.executions_delta))                    as PLSQL_PER_EXEC,
round(st.px_servers_execs_delta/decode(st.executions_delta,0,1,st.executions_delta))                as PX_PER_EXEC
from dba_hist_sqlstat st join dba_hist_snapshot sn using(snap_id,instance_number)
where &1
order by snap_id desc, instance_number
/
SQL запросы, планы выполнения, оптимизатор

CREATE_BSLINE.SQL [Пере]создание SPM Baseline из курсора, находящегося в Shared Pool

--
-- [Re]create SPM baseline from cursor in Shared Pool
-- Usage: SQL> @create_bsline g1jratsw6ujcz 2375777697 "Halty''s task"
-- https://iusoltsev.wordpress.com
--

set echo off feedback off heading on VERIFY OFF serveroutput on

declare
  res               number;
  v_sql_handle      varchar2(30);
  v_plan_name       varchar2(30);
  v_sql_id          varchar2(13) := '&1';
  v_plan_hash_value number       :=  &2;
  v_desc            varchar2(30) := '&3';
begin
  for reco in (select sql_handle, plan_name
                 from dba_sql_plan_baselines bl, v$sqlarea sa
                where dbms_lob.compare(bl.sql_text, sa.sql_fulltext) = 0
                  and sa.sql_id = v_sql_id)
  loop
    res := DBMS_SPM.drop_sql_plan_baseline(reco.sql_handle, reco.plan_name);
  end loop;
  res := dbms_spm.load_plans_from_cursor_cache(sql_id          => v_sql_id,
                                               plan_hash_value => v_plan_hash_value);
  select sql_handle, plan_name
    into v_sql_handle, v_plan_name
    from dba_sql_plan_baselines bl, v$sqlarea sa
   where dbms_lob.compare(bl.sql_text, sa.sql_fulltext) = 0
     and sa.sql_id = v_sql_id
     and origin = 'MANUAL-LOAD';
  res := DBMS_SPM.alter_sql_plan_baseline(v_sql_handle, v_plan_name, 'fixed', 'yes');
  res := DBMS_SPM.alter_sql_plan_baseline(v_sql_handle, v_plan_name, 'autopurge', 'no');
  res := DBMS_SPM.alter_sql_plan_baseline(v_sql_handle, v_plan_name, 'description', v_desc);
  dbms_output.put_line('');
  dbms_output.put_line('Baseline ' || v_sql_handle || ' ' || v_plan_name || ' was [re]created');
  dbms_output.put_line('for SQL_ID=' || v_sql_id || ', SQL_PLAN_HASH=' || v_plan_hash_value);
end;
/
set feedback on echo off VERIFY ON serveroutput off

BL_CHECK4SQL_ID.SQL Проверка существования Baseline по SQL_ID

--
-- Check SPM baseline existance for exact SQL_ID
-- Usage: SQL> @bl_check4sql_id 4y4bvy7bhkqbn
--

set echo off feedback on heading on VERIFY OFF serveroutput on

col sql_handle for a30
col plan_name  for a30

select sql_handle, plan_name
  from dba_sql_plan_baselines bl, gv$sqlarea sa
 where dbms_lob.compare(bl.sql_text, sa.sql_fulltext) = 0
   and sa.sql_id = '&&1'
union
select sql_handle, plan_name
  from dba_sql_plan_baselines bl, dba_hist_sqltext sa
 where dbms_lob.compare(bl.sql_text, sa.sql_text) = 0
   and sa.sql_id = '&&1'
/
set feedback on echo off VERIFY ON serveroutput off

BL_FIND_SQL_ID.SQL Определение соответствия SQL_ID / PLAN_HASH_VALUE конкретному Baseline (по SQL_HANDLE)

--
-- 2find exact SQL_ID and PLAN_HASH_VALUE for SPM baseline, searching for the matching sql text and plan outlines in Shared pool and AWR
-- Usage: SQL> @bl_find_SQL_ID SQL_148cdf9124bc55dd [SQL_PLAN_1936zk4kbspfxee773f4d]
--

set echo off feedback on heading on VERIFY OFF serveroutput on
BREAK ON BL_EXEC_TIMESTAMP ON BL_REPRODUCED

col sql_id             for a13
col plan_hash_value    for 999999999999999
col SQL_EXEC_TIMESTAMP for a20
col SQL_TYPE           for a8
col BL_EXEC_TIMESTAMP  for a20
col BL_REPRODUCED      for a13

with sql_list as
 (select distinct sql_id, plan_hash_value
    from dba_sql_plan_baselines bl, gv$sql s
   where bl.sql_handle = '&&1'
     and bl.plan_name = nvl('&&2', bl.plan_name)
     and dbms_lob.compare(bl.sql_text, s.sql_fulltext) = 0
     and not exists
   (select distinct substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints
            from xmltable('/*/outline_data/hint' passing
                          (select xmltype(other_xml) as xmlval
                             from gv$sql_plan
                            where inst_id = s.inst_id
                              and sql_id = s.sql_id
                              and plan_hash_value = s.plan_hash_value
                              and other_xml is not null)) d
          minus
          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 od.obj_type = 2
                              and o.name = bl.plan_name
                              and o.signature = od.signature
                              and o.plan_id = od.plan_id
                              and comp_data is not null)) d)
  union
  select distinct sa.sql_id, plan_hash_value
    from dba_sql_plan_baselines bl,
         dba_hist_sqltext       sa,
         dba_hist_sql_plan      spa
   where bl.sql_handle = '&&1'
     and bl.plan_name = nvl('&&2', bl.plan_name)
     and dbms_lob.compare(bl.sql_text, sa.sql_text) = 0
     and sa.sql_id = spa.sql_id
     and not exists
   (select substr(extractvalue(value(d), '/hint'), 1, 200) as outline_hints
            from xmltable('/*/outline_data/hint' passing
                          (select xmltype(other_xml) as xmlval
                             from DBA_HIST_SQL_PLAN
                            where sql_id = spa.sql_id
                              and plan_hash_value = spa.plan_hash_value
                              and other_xml is not null)) d
          minus
          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 od.obj_type = 2
                              and o.name = bl.plan_name
                              and o.signature = od.signature
                              and o.plan_id = od.plan_id
                              and comp_data is not null)) d))
select l.SQL_ID,
       l.PLAN_HASH_VALUE,
       to_char(max(nvl(s.last_active_time, h.timestamp)),
               'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_TIMESTAMP,
       nvl2(max(s.sql_id), 'CURRENT', 'ARCHIVE') as SQL_TYPE,
       to_char(max(bl.last_executed), 'dd.mm.yyyy hh24:mi:ss') as BL_EXEC_TIMESTAMP,
       max(bl.REPRODUCED) as BL_REPRODUCED
  from sql_list l
  left join gv$sql s
    on l.sql_id = s.sql_id
   and l.plan_hash_value = s.plan_hash_value
  left join dba_hist_sql_plan h
    on l.sql_id = h.sql_id
   and l.plan_hash_value = h.plan_hash_value
  join dba_sql_plan_baselines bl
    on bl.sql_handle = '&&1'
   and bl.plan_name = nvl('&&2', bl.plan_name)
 group by l.sql_id, l.plan_hash_value
 order by to_char(max(nvl(s.last_active_time, h.timestamp)),
                  'dd.mm.yyyy hh24:mi:ss') desc
/
set feedback on echo off VERIFY ON serveroutput off

BL_HINTS.SQL Список подсказок, составляющих SPM Baseline:

--
-- Baseline hints list
-- Usage: SQL> @bl_hints SQL_PLAN_acg49cdw0088v4085ecd2
--
set verify off

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 od.obj_type = 2
                    and o.name = '&&1'
                    and o.signature = od.signature
                    and o.plan_id = od.plan_id
                    and comp_data is not null)) d
/

set verify on

SQLPATCH_HINTS.SQL Список подсказок SPM SQL Patch:

-- SQL Patch hints list
-- Usage: SQL> @SQLPATCH_HINTS "Patch Name"
--
set verify off

select substr(extractvalue(value(d), '/hint'), 1, 512) as sql_patch_hints
  from xmltable('/outline_data/hint' passing
                (select xmltype(comp_data) as xmlval
                   from sys.sqlobj$data od, sys.sqlobj$ o
                  where od.obj_type = 3
                    and (o.name = '&&1' and o.obj_type = 3)
                    and o.signature = od.signature
                    and comp_data is not null)) d
/

set verify on

sql_profile_hints.sql Список подсказок SQL Profile, включая Profiles, созданные с помощью DBMS_SQLTUNE.IMPORT_SQL_PROFILE из списка хинтов:

--
-- SQL Profile hints list, including Profiles created by DBMS_SQLTUNE.IMPORT_SQL_PROFILE
-- Usage: SQL> @sql_profile_hints "SQL_Profile_name"
--
set verify off timi off lines 500
col HINT for a400

select substr(extractvalue(value(d), '/hint'), 1, 400) as outline_hints
  from xmltable('/outline_data/hint' passing
                (select xmltype(comp_data) as xmlval
                   from sys.sqlobj$data od join sys.sqlobj$ o using (signature, category,obj_type,plan_id)
                  where obj_type = 1 -- type "SQL Profile"
                    and o.name = '&1'
                    and comp_data is not null)) d
/

set verify on timi on

sqltext->sql_id Получение SQL_ID из текста запроса

Начиная с 11.2.0.1 — http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/:

11.2.0.1.@ SQL> select sysdate from dual;

SYSDATE
---------
12-DEC-12

SQL> @sql_id.sql

SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT
------------- ---------- ------------ --------------- ------------------------
7h35uxf5uhmm1 2343063137            0      1388734953 select sysdate from dual

SQL> select dbms_sqltune_util0.sqltext_to_sqlid(sql_fulltext || chr(0)) as sqltext_to_sqlid,
2         sql_id
3    from v$sql
4   where sql_id = '7h35uxf5uhmm1';

SQLTEXT_TO_SQLID     SQL_ID
-------------------- -------------
7h35uxf5uhmm1        7h35uxf5uhmm1

Начиная с 10.2:

SQL> CREATE OR REPLACE FUNCTION sqltext_to_sqlid(p_sql IN VARCHAR2)
2    RETURN VARCHAR2 IS
3    -- Author: Sean Stuber
4    -- based on algorithm described by Tanel Poder here
5    -- http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/
6    -- Generate md5 hash of the sql text
7    -- Oracle appends a null-char (0) before hashing
8    v_md5 RAW(16) := DBMS_CRYPTO.hash(UTL_I18N.string_to_raw(p_sql || CHR(0),
9                                                             'AL32UTF8'),
10                                      DBMS_CRYPTO.hash_md5);
11    -- pull the last 64bits from the md5
12    -- reverse each of the 32bit words to fix endianness
13    -- convert the 16 digit hex string to a number
14    v_temp  NUMBER := TO_NUMBER(RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 9, 4))) ||
15                                RAWTOHEX(UTL_RAW.reverse(UTL_RAW.SUBSTR(v_md5, 13, 4))),
16                                RPAD('x', 16, 'x'));
17    v_sqlid VARCHAR2(13);
18  BEGIN
19    -- Convert number to base32 string
20    -- use alphabet defined in Tanel Poder’s sqlid to hash query
21    FOR i IN 0 .. FLOOR(LN(v_temp) / LN(32)) LOOP
22      v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',
23                        FLOOR(MOD(v_temp / POWER(32, i), 32)) + 1,
24                        1) || v_sqlid;
25    END LOOP;
26    RETURN LPAD(v_sqlid, 13, '0');
27  END;
28  /

Function created.

SQL> select sqltext_to_sqlid('select sysdate from dual') from dual;

SQLTEXT_TO_SQLID('SELECTSYSDATEFROMDUAL')
-----------------------------------------
7h35uxf5uhmm1

1 row selected.

plan_ol_hints.sql Outline секция/список подсказок, составляющих план выполнения из V$SQL_PLAN, либо из AWR

--
-- Outline hints list for sql execution plan from Shared Pool or AWR
-- Usage: SQL> @plan_ol_hints guh7dff6fsac9 [1752872774]     [index]
--                            ^SQL_ID       ^PLAN_HASH_VALUE  ^Hint like upper('%index%')
--

set verify off feedback off timi off lines 500
col HINT for a400

select distinct plan_hash_value, hint
  from (select plan_hash_value, b.hint
          from gv$sql_plan m,
               xmltable('/other_xml/outline_data/hint' passing
                        xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
         where sql_id = '&&1'
           and plan_hash_value = nvl('&&2', plan_hash_value)
           and trim(OTHER_XML) is not null
        union all
        select plan_hash_value, b.hint
          from dba_hist_sql_plan m,
               xmltable('/other_xml/outline_data/hint' passing
                        xmltype(m.OTHER_XML) columns hint varchar2(4000) path '/hint') b
         where sql_id = '&&1'
           and plan_hash_value = nvl('&&2', plan_hash_value)
           and trim(OTHER_XML) is not null)
 where hint like upper('%&&3%')
 order by 1
/

set verify on feedback on timi on

Значения связанных переменных, использованных при генерации плана, из 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

Секция INFO плана выполнения

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 в запросы библиотечного кэша (v$sql.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'
/

Список невоспроизводимых BASELINE-ов: может быть использован для коственного выявления существенных изменений как объектов бд (view), так и текстов зависимых от этих объектов запросов, план выполнения которых был ранее зафиксирован:

select *
  from DBA_SQL_PLAN_BASELINES b
 where ACCEPTED   = 'YES'
   and ENABLED    = 'YES'
   and REPRODUCED = 'NO'
   and not exists (select *
          from DBA_SQL_PLAN_BASELINES
         where SQL_HANDLE = b.SQL_HANDLE
           and ACCEPTED   = 'YES'
           and ENABLED    = 'YES'
           and REPRODUCED = 'YES')
/

Дерево зависимостей плана выполнения по SQL_ID, CHILD_NUMBER, (PLAN_HASH_VALUE): список зависимых объектов бд с LAST_DDL_TIME, LAST_ANALYZED

set feedback off heading on timi off pages 200 lines 500 echo off  VERIFY OFF

col DEP_TREE_BY_ID for a80
col DEP_TREE for a60
COLUMN LAST_TIME FORMAT A30 HEADING 'LAST_DDL \\ LAST_ANALYZED'

with pd as
 (select /*+ materialize*/
   1 as object_id, object_id as referenced_object_id
    from dba_objects
   where (owner, object_name) in
         (select distinct object_owner, object_name
            from v$sql_plan
           where sql_id = '&&1'
             and child_number = nvl('&&2', 0) --and plan_hash_value = ...
             and object_owner is not null
             and object_name is not null)
  union all
  select * from public_dependency)
select distinct REGEXP_REPLACE(sys_connect_by_path(object_id, ' / '),
                               '^ \/ 1',
                               '&&1') as DEP_TREE_BY_ID,
                lpad(' ', 2 * level) ||
                (select object_type || ' ' || owner || '.' || object_name
                   from dba_objects
                  where object_id = pd.referenced_object_id) as DEP_TREE,
                --sys_connect_by_path((select object_type || ' ' || owner || '.' || object_name from dba_objects where object_id = pd.referenced_object_id), ' / ') as DEP_TREE_BY_NAME,
                (select to_char(o.last_ddl_time, 'DD-MON-YY') || ' \\ ' ||
                        to_char(nvl(i.last_analyzed, t.last_analyzed),
                                'DD-MON-YY')
                   from dba_objects o
                   left join dba_tables t
                     on o.object_type = 'TABLE'
                    and o.owner = t.owner
                    and o.object_name = t.table_name
                   left join dba_indexes i
                     on o.object_type = 'INDEX'
                    and o.owner = i.owner
                    and o.object_name = i.index_name
                  where o.object_id = pd.referenced_object_id) as LAST_TIME
  from pd
connect by prior referenced_object_id = object_id
 start with object_id = 1
 ORDER BY 1
/

set feedback on VERIFY ON

PLAN_OL_DIFF_AWR.SQL — отличия между секциями Outline планов выполнения запроса из AWR с возможностью просмотра и сравнения хинтов, относящикся к конкретному блоку запроса (Query Block)

--
-- SQL Plan Outline Hints Differences from AWR [for pointed Query block only]
-- Usage: SQL> @plan_ol_diff_awr 6r6sanrs05550 3541904711        [2970372553]       [SEL$A7C6D689]
--                               ^sql_id       ^plan_hash_value1  ^plan_hash_value2  ^query_block_name
-- by Igor Usoltsev
--

set feedback off heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col plh_&&2 for a100
col plh_&&3 for a100

with plh1 as
 (select substr(extractvalue(value(d), '/hint'), 1, 200) as plh_ &&2
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from dba_hist_sql_plan
                    where sql_id = '&&1'
                      and plan_hash_value = nvl('&&2', 0)
                      and other_xml is not null)) d),
plh2 as
 (select substr(extractvalue(value(d), '/hint'), 1, 200) as plh_ &&3
    from xmltable('/*/outline_data/hint' passing
                  (select xmltype(other_xml) as xmlval
                     from dba_hist_sql_plan
                    where sql_id = '&&1'
                      and plan_hash_value = nvl('&&3', 0)
                      and other_xml is not null)) d)
select *
  from plh1
  full join plh2
    on plh1.plh_ &&2 = plh2.plh_ &&3
 where -- not (plh1.plh_&&2 || plh2.plh_&&3 like 'INDEX%' or        -- may be useful to exclude a lot of non-principal hints
       --      plh1.plh_&&2 || plh2.plh_&&3 like 'NLJ_BATCHING%' or -- --//--
       --      plh1.plh_&&2 || plh2.plh_&&3 like 'OUTLINE%')        -- --//--
       -- and
 plh1.plh_ &&2 || plh2.plh_ &&3 like '%' || '&&4' || '%'
minus
select * from plh1 join plh2 on plh1. plh_ &&2 = plh2.plh_ &&3
/

set feedback on VERIFY ON timi on

PLAN_QB_DIFF_AWR.SQL — сравнение планов или отдельных блоков различающихся планов выполнения запроса из AWR

--
-- SQL Plan QBlock Section[s] Differences from AWR
-- Usage: SQL> @plan_qb_diff_awr 6r6sanrs05550 3541904711        2970372553        "SEL$6444526D','SET$1"
--                               ^SQL_ID       ^PLAN_HASH_VALUE1 ^PLAN_HASH_VALUE2  ^QBLOCK_LIST in form "[QB1[','QB2...]]"
-- when QBLOCK_LIST parameter is null then script outputs the whole plans comparision
-- by Igor Usoltsev
--

set feedback off heading on timi off pages 500 lines 500 echo off  VERIFY OFF

col OPERATION    for a60
col ID           for 999
col OBJECT_OWNER for a30
col OBJECT_NAME  for a30
col QBLOCK_NAME  for a20

with pt1 as
 (select *
    from dba_hist_sql_plan
   where sql_id = '&&1'
     and plan_hash_value = nvl('&&2', 0)),
pt2 as
 (select *
    from dba_hist_sql_plan
   where sql_id = '&&1'
     and plan_hash_value = nvl('&&3', 0))
SELECT pt1.plan_hash_value,
       pt1.qblock_name,
       pt1.id,
       lpad(' ', 2 * level) || pt1.operation || ' ' || pt1.options as OPERATION,
       pt1.object_owner,
       pt1.object_name,
       pt1.cardinality,
       pt1.bytes,
       pt1.cost
  FROM pt1
CONNECT BY PRIOR pt1.id = pt1.parent_id
 START WITH pt1.id in (select MIN_ID
                         from (select qblock_name, min(id) as MIN_ID
                                 from pt1
                                where qblock_name in ('&&4')
                                group by qblock_name)
                       union all
                       select 0 from dual where '&&4' is null)
union all
select null, null, null, null, null, null, null, null, null
  from dual
union all
SELECT pt2.plan_hash_value,
       pt2.qblock_name,
       pt2.id,
       lpad(' ', 2 * level) || pt2.operation || ' ' || pt2.options as OPERATION,
       pt2.object_owner,
       pt2.object_name,
       pt2.cardinality,
       pt2.bytes,
       pt2.cost
  FROM pt2
CONNECT BY PRIOR pt2.id = pt2.parent_id
 START WITH pt2.id in (select MIN_ID
                         from (select qblock_name, min(id) as MIN_ID
                                 from pt2
                                where qblock_name in ('&&4')
                                group by qblock_name)
                       union all
                       select 0 from dual where '&&4' is null)
/

set feedback on VERIFY ON timi on
Статистика Oracle

JOB для удаления нулевой статистики сегодняшних партиций

Процедура переноса статистики таблицы/схемы через db link (например, с production на тестовую бд)

create or replace procedure stats_imp_over_dblink( -- procedure for the schema/table statistics moving over database link - for example between the production and testing envs
                                                  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;
/
SGA shared pool

high version count: причины большого значения версий / повторного неиспользования курсоров из обзора V$SQL_SHARED_CURSOR по SQL_ID для Oracle 10g

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
/

Тот же запрос для Oracle 11g

--
-- High version count reason for 11.1
-- Usage:
-- 11.1.0.7.@ SQL> @shared_cu111 &sql_id
--
col sql_id for a13
col version_count for 999999
col SHARED_CU_CNT for 999999
col PARSING_SCHEMA_NAME for a30
col Reason for a40
col sql_text for a100

select/*+RULE*/ sql_id as sql_id, version_count, SHARED_CU_CNT, address, hash_value, parsing_schema_name, reason, substrb(sql_text,1,100) as 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')
as Reason
, count(child_number) as SHARED_CU_CNT
from v$sql_shared_cursor
where sql_id = '&&1'
group by sql_id, address)
join v$sqlarea using(address, sql_id)
where version_count>1
order by version_count desc,address
/
set feedback on pages 200 lines 2000  VERIFY ON

Script — Determine reason for high Child Cursor Count — V$SQL_SHARED_CURSOR [ID 1422573.1]

SHARED_CU.SQL Причины большого значения версий курсора, использования Bind-Aware Cursor Sharing, Cardinality Feedback, механизмов SPM и SQL Profile для Oracle 11.2.0.2+

--
-- High version count reason for 11.2.0.2+
-- Usage: 
-- SQL> @shared_cu &sql_id
--

set feedback off heading on pages 200 lines 600 echo off  VERIFY OFF
col INST for 9999
col EXECS for 99999999
col CHILD for 99999
col BIND_SENSE for a10
col BIND_AWARE for a10
col SHAREABLE for a10
col USE_FEEDBACK_STATS for a18
col OPTIMIZER_STATS for a16
col BIND_EQ_FAILURE for a16
col Reason for a60
col SQL_PLAN_BASELINE for a30
col SQL_PATCH for a30
col SQL_PROFILE for a64

/*
select  sql_id,
	child_number as CHILD,
	use_feedback_stats as USE_FEEDBACK_STATS,
	load_optimizer_stats as OPTIMIZER_STATS,
	bind_equiv_failure as BIND_EQ_FAILURE, 
(select reasons||'  |  '||details from xmltable
('/ChildNode' passing
(select xmltype(reason) as xmlval
 from v$sql_shared_cursor
 where dbms_lob.substr(reason,256) <> ' '
 and sql_id = sc.sql_id
 and child_number = sc.child_number)
columns
Reasons varchar2(60) path '/ChildNode/reason',
Details varchar2(60) path '/ChildNode/details')) as Reason
from v$sql_shared_cursor sc
where sc.sql_id = '&1';
*/

select s.inst_id as INST,
       s.EXECUTIONS as EXECS,
       to_char(to_date(s.last_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi') as last_load_time,
       s.users_opening,
       to_char(s.last_active_time, 'dd.mm hh24:mi') as last_active_time,
       round(s.elapsed_time/decode(s.EXECUTIONS,0,1,s.EXECUTIONS)) as ELA_PER_EXEC,
       s.PLAN_HASH_VALUE,
       s.optimizer_cost,
       s.child_number as CHILD,
       s.IS_BIND_SENSITIVE as "BIND_SENSE",
       s.IS_BIND_AWARE as "BIND_AWARE",
       s.IS_SHAREABLE as "SHAREABLE",
       use_feedback_stats as USE_FEEDBACK_STATS,
       load_optimizer_stats as OPTIMIZER_STATS,
       bind_equiv_failure as BIND_EQ_FAILURE,
       ROLL_INVALID_MISMATCH,
       bind_uacs_diff,
       (select reasons || '  |  ' || details
          from xmltable('/ChildNode' passing
                        (select case when dbms_lob.instr(reason, '<ChildNode>', 1, 2) = 0 then
                                   xmltype(reason)
                                  else
                                    xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '<ChildNode>', 1, 2) - 1))
                                  end as xmlval
                           from gv$sql_shared_cursor
                          where dbms_lob.substr(reason, 256) <> ' '
                            and sql_id = sc.sql_id
                            and inst_id = sc.inst_id
                            and child_address = sc.child_address)
                        columns Reasons varchar2(60) path '/ChildNode/reason',
                        Details varchar2(60) path '/ChildNode/details')) as Reason1,
/*
       (select reasons || '  |  ' || details
          from xmltable('/ChildNode' passing
                        (select xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '</ChildNode>', 1, 2) - dbms_lob.instr(reason, '</ChildNode>', 1, 1), dbms_lob.instr(reason, '<ChildNode>', 1, 2)))
                           from gv$sql_shared_cursor
                          where dbms_lob.substr(reason, 256) <> ' '
                            and sql_id = sc.sql_id
                            and inst_id = sc.inst_id
                            and child_address = sc.child_address)
                        columns Reasons varchar2(60) path '/ChildNode/reason',
                        Details varchar2(60) path '/ChildNode/details')) as Reason2,
*/
       SQL_PLAN_BASELINE,
       SQL_PATCH,
       OUTLINE_CATEGORY,
       SQL_PROFILE,
       IS_OBSOLETE
  from gv$sql_shared_cursor sc, gv$sql s
 where sc.sql_id = '&1'
   and sc.inst_id = s.inst_id
   and sc.child_address = s.child_address
   and sc.sql_id = s.sql_id
   and sc.inst_id > 0
and (s.EXECUTIONS>0 or s.users_opening>0)
order by s.inst_id, --s.child_number
	s.last_active_time desc
/
set feedback on VERIFY ON timi on

SHARED_CU12.SQL Причины большого значения версий курсора, использования Bind-Aware Cursor Sharing, Cardinality Feedback, механизмов SPM и SQL Profile для Oracle 12.1.0.1+

--
-- SQL child cursor generation properties for 12.1.0.1+
-- Usage: 
-- SQL> @shared_cu12 &sql_id
--

set feedback off heading on timi off pages 200 lines 500 echo off  VERIFY OFF

col INST for 9999
col EXECS for 99999999
col CHILD for 99999
col BIND_SENSE for a10
col BIND_AWARE for a10
col SHAREABLE for a10
col USE_FEEDBACK_STATS for a18
col OPTIMIZER_STATS for a16
col BIND_EQ_FAILURE for a16
col Reason1 for a60
col SQL_PLAN_BASELINE for a30
col SQL_PATCH for a30
col SQL_PROFILE for a64
col ROLL for a4
col REOPT for a5
col ADAPT for a5
col FIRST_LOAD_TIME for a20
col LAST_LOAD_TIME for a20

select s.inst_id as INST,
       s.EXECUTIONS as EXECS,
s.first_load_time,
s.last_load_time,
       to_char(s.last_active_time, 'dd.mm.yyyy hh24:mi:ss') as last_active_time,
       round(s.elapsed_time/decode(s.EXECUTIONS,0,1,s.EXECUTIONS)) as ELA_PER_EXEC,
       s.PLAN_HASH_VALUE,
       s.optimizer_cost,
       s.child_number as CHILD,
       s.IS_BIND_SENSITIVE as "BIND_SENSE",
       s.IS_BIND_AWARE as "BIND_AWARE",
       s.IS_SHAREABLE as "SHAREABLE",
       s.IS_REOPTIMIZABLE as "REOPT",
       (select count(*) from gv$sql_reoptimization_hints rh where rh.sql_id = s.sql_id and rh.child_number = s.child_number) as REOPT_HINTS,
       s.IS_RESOLVED_ADAPTIVE_PLAN as "ADAPT",
       use_feedback_stats as USE_FEEDBACK_STATS,
      (SELECT 'valid:' || extractvalue(xmlval, '/*/spd/cv') || '; used:' ||
             extractvalue(xmlval, '/*/spd/cu')
        from (select xmltype(other_xml) xmlval
                from gv$sql_plan p
               where p.inst_id(+) = s.inst_id
                 and p.child_address = s.child_address
                 and p.sql_id = s.sql_id
                 and p.other_xml is not null)) as SQL_PLAN_DIRECTIVES,
       load_optimizer_stats as OPTIMIZER_STATS,
       bind_equiv_failure as BIND_EQ_FAILURE,
       ROLL_INVALID_MISMATCH as "ROLL",
       (select reasons || '  |  ' || details
          from xmltable('/ChildNode' passing
                        (select case when dbms_lob.instr(reason, '<ChildNode>', 1, 2) = 0
                                       then xmltype(reason)
                                     when dbms_lob.instr(reason, '<ChildNode>', 1, 2) > 4000
                                       then xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '</reason>', 1, 1) + 8) || '</ChildNode>')
                                  else xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '<ChildNode>', 1, 2) - 1))
                                  end as xmlval
                           from gv$sql_shared_cursor
                          where dbms_lob.substr(reason, 256) <> ' '
                            and sql_id = sc.sql_id
                            and inst_id = sc.inst_id
                            and child_address = sc.child_address)
                        columns Reasons varchar2(4000) path '/ChildNode/reason',
                                Details varchar2(4000) path '/ChildNode/details')) as Reason1,
       SQL_PLAN_BASELINE,
       SQL_PATCH,
       OUTLINE_CATEGORY,
       SQL_PROFILE,
       IS_OBSOLETE
  from gv$sql_shared_cursor sc,
       gv$sql s
 where sc.sql_id = '&1'
   and sc.inst_id = s.inst_id
   and sc.child_address = s.child_address
   and sc.sql_id = s.sql_id
order by s.inst_id, s.last_active_time
/
set feedback on VERIFY ON

SHARED_CU_SUM.SQL Причины большого значения версий курсора с группировкой по 1-му значению v$sql_shared_cursor.reason и v$sql.is_obsolete, 11.2.0.2+

--11.2.0.2+
set feedback off heading on timi on pages 300 lines 1000 echo off  VERIFY OFF
col INST for 9999
col EXECS for 99999999
col CHILD for 99999
col BIND_SENSE for a10
col BIND_AWARE for a10
col SHAREABLE for a10
col USE_FEEDBACK_STATS for a18
col OPTIMIZER_STATS for a16
col BIND_EQ_FAILURE for a16
col Reason1 for a60
col Reason2 for a60
col SQL_PLAN_BASELINE for a30
col SQL_PATCH for a30
col SQL_PROFILE for a64

with R as
 (select sc.inst_id as INST,
         is_obsolete,
         pq_slave_mismatch,
         top_level_rpi_cursor,
         (select reasons || '  |  ' || details
            from xmltable('/ChildNode' passing
                          (select case when dbms_lob.instr(reason, '', 1, 2) = 0 then xmltype(reason)
                                    else xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '', 1, 2) - 1))
                                  end as xmlval
                             from gv$sql_shared_cursor
                            where dbms_lob.substr(reason, 256) <> ' '
                              and sql_id = sc.sql_id
                              and inst_id = sc.inst_id
                              and child_address = sc.child_address) columns
                          Reasons varchar2(60) path '/ChildNode/reason',
                          Details varchar2(60) path '/ChildNode/details')) as Reason
    from gv$sql_shared_cursor sc, gv$sql s
   where sc.sql_id = '&1'
     and sc.inst_id = s.inst_id
     and sc.sql_id = s.sql_id
     and sc.child_address = s.child_address)
select inst, count(*), is_obsolete, Reason
  from R
 group by inst, is_obsolete, Reason
 order by count(*) desc
/
set feedback on VERIFY ON

PURGE_CU.SQL Удаление курсора из SGA по sql_id Доступно с версии 10.2.0.4 — Kerry Osborne: Flush A Single SQL Statement

--
-- 2 purge cursor from Shared Pool
-- Usage: SQL> @purge_cu &sql_id
--

set verify off

declare
  v_address_hash varchar2(60);
begin
  select address || ', ' || hash_value
    into v_address_hash
    from v$sqlarea
   where sql_id = '&1';
  sys.dbms_shared_pool.purge(v_address_hash, 'c');
end;
/

set verify on
SGA, кэширование сегментов бд

V$BH.SQL — оценка кол-ва блоков buffer cache, занятых объектом бд по статусам

SET VERIFY OFF
col OWNER       for a30
col OBJECT_NAME for a30
col STATUS      for a10
col blk_count   for 999999999999

select o.OWNER,
       o.OBJECT_NAME,
       nvl(bh.status, 'SUMMARY') as STATUS,
       count(*) as blk_count
  from v$bh bh, dba_objects o
 where bh.OBJD = o.DATA_OBJECT_ID
   and o.OWNER = nvl(upper('&1'), user)
   and o.OBJECT_NAME like upper('&2%')
 group by o.OWNER, o.OBJECT_NAME, rollup(bh.status)
/
SET VERIFY ON

SQL> @v$bh "" emp

OWNER  OBJECT_NAME  STATUS   BLK_COUNT
------ ------------ -------- ---------
SCOTT  EMP          xcur             6
SCOTT  EMP          SUMMARY          6

DB_SEG_CACHE.SQL — текущее кэширование и размер сегмента со статусами блоков в кэше и с размерами buffer cache и SGA

--
-- DB Segment Caching
-- Oracle 10.2+
-- Usage: SQL> @db_seg_cache SCOTT EMP
-- Igor Usoltsev
--

SET VERIFY OFF

col AREA                 for a12
col STATUS               for a30
col DISTINCT_BLOCK_COUNT for a40
col BLOCK_COUNT          for a12

with OBJLIST as
 (select DATA_OBJECT_ID
    from dba_objects
   where (owner, object_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
     and DATA_OBJECT_ID is not null)
select 'BUFFER CACHE' as AREA,
       nvl(status, 'summary') as STATUS,
       to_char(count(distinct(file# || '#' || block#))) as DISTINCT_BLOCK_COUNT,
       to_char(count(*)) as BLOCK_COUNT
  from V$BH, OBJLIST
 where objd = OBJLIST.DATA_OBJECT_ID
 group by rollup(status)
union all
select 'DATABASE', 'db blocks', to_char(blocks), '' as BH_COUNT
  from dba_segments
 where (owner, segment_name) in ((upper(nvl('&&1', 'user')), upper('&&2')))
union all
select 'SGA',
       'BUFFER CACHE of MAX SGA SIZE',
       trim(to_char(s1.bytes, '999,999,999,999,999')) || ' of ' ||
       trim(to_char(s2.bytes, '999,999,999,999,999')),
       '(' || decode(s1.resizeable, 'Yes', 'Resizeable', 'Fixed') || ')'
  from v$sgainfo s1, v$sgainfo s2
 where s1.name = 'Buffer Cache Size'
   and s2.name = 'Maximum SGA Size'
/

SET VERIFY ON
SQL утилиты

REAL_TAB_CARD.SQL Динамический SQL с использованием DBMS_XMLGEN.GETXMLTYPE — отсюда

SQL> select table_name,
2         extractvalue
3           (dbms_xmlgen.getxmltype
4             ('select count(1) cnt FROM '||owner||'.'||table_name)
5             , '/ROWSET/ROW/CNT'
6           ) cnt
7    from all_tables
8   where owner = user
9   order by to_number(cnt) desc
10  /

TABLE_NAME  CNT
----------- ---
EMP         14
SALGRADE    5
DEPT        4
BONUS       0

Вертикальный вывод содержимого строк, возвращаемых курсором, в виде 2-х столбцов имя / значение (print table) с использованием xmltable — от Саяна Малакшинова

SQL> select *
2  from
3     xmltable( '/ROWSET/ROW/*'
4               passing xmltype(cursor(select * from scott.emp where rownum <= 2))
5               columns
6                  col varchar2(100) path 'name()'
7                 ,val varchar2(100) path '.'
8        );

COL        VAL
---------- ---------
EMPNO      7369
ENAME      SMITH
JOB        CLERK
MGR        7902
HIREDATE   17-DEC-80
SAL        800
DEPTNO     20
EMPNO      7499
ENAME      ALLEN
JOB        SALESMAN
MGR        7698
HIREDATE   20-FEB-81
SAL        1600
COMM       300
DEPTNO     30
CRS / bash

crs_stat с форматированным выводом — отсюда

$ crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-60s%-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

3 комментария »

  1. Скрипт для нахождения столбцов с ограничениями ссылочной целостности без индексов (Unindexed Foreign Keys)
    с использованием LISTAGG для Oracle 11g+ из комментария к блогу Т.Кайта на моих БД дает неверный результат.

    Написал свой вариант скрипта, пытаюсь для проверки создать Unindexed Foreign Keys:

    По крайней мере для версий начиная с 9-й

    ALTER TABLE SD_CHILD ADD
    CONSTRAINT SD_CHILD_R01
    FOREIGN KEY (ID)
    REFERENCES SD_PARENT (ID)

    ORA-02270: no matching unique or primary key for this column-list

    Для уже существующего ограничения при попытке удалить индекс
    ORA-02429: cannot drop index used for enforcement of unique/primary key

    Т.е. Oracle не дает создать такой CONSTRAINT.

    Насколько актуален тогда «Скрипт для нахождения столбцов с ограничениями ссылочной целостности без индексов» ?

    комментарий от Сергей Шумеев — 06.06.2014 @ 11:07 | Ответить

    • Актуален, т.к. под Unindexed Foreign Keys обычно понимается отсутствие индексов на дочерней (child) таблице, не на родительской
      В вашем случае вы сначала пытаетесь при создании FK сослаться на неуникальное поле (ORA-02270), потом удалить индекс, обеспечивающий уникальность (ORA-02429)
      Сделайте табличку SD_PARENT с уникальным индексированным (ID), для SD_CHILD добавьте FK для неиндексированного неуникального поля (ID) -> SD_PARENT (ID)

      комментарий от Игорь Усольцев — 06.06.2014 @ 12:42 | Ответить

  2. […] ASH_SQLMON […]

    Уведомление от Query optimization: | orabase.org — 07.08.2015 @ 15:53 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: