Oracle mechanics

Scripts

прежняя версия страницы Scripts

Описание запросов и скриптов, частоиспользуемых/встречающихся на сайте:zip-архив, или github

A_WAITS.SQLскрипт Steve Adams для мониторинга динамики ожиданий локальных сессий:

SQL> @a_waits
 
EVENT                                                       T0  T1  T2  T3  T4  T5  T6  T7  T8  T9
---------------------------------------------------------- --- --- --- --- --- --- --- --- --- ---
row cache lock                                               1                                 
log file parallel write                                              1           1             
log file sync                                                        1           1             
SQL*Net more data from client                                1       1   1                     
REPL Capture/Apply: RAC AQ qm                                1   1   1   1   1   1   1   1   1   1
heartbeat redo informer                                      1   1   1   1   1   1   1   1   1   1
direct path write temp                                       1   1   1   1   1   1   1   1   1   1
db file sequential read                                      1   1   1   1   1   1   1   1   1   1
LNS ASYNC dest activation                                    1   1   1   1   1   1   1   1   1   1
wait for unread message on br                                2   2   2   2   2   2   2   2   2   2
ges remote message                                           2   2   2   2   2   2   2   2   2   2
gcs remote message                                           3   3   3   3   3   3   3   3   3   3
SQL*Net message to client                                    6   7   6   6   6   6   6   6   7   7

ASH_BLOCK_TREE.SQL — структура блокировок ASH, начиная от блокирующих сессий, т.е. вначале/левее показаны блокеры, последними/правее — заблокированные сессии:

SQL> -- кого блокируют сессии, ожидающие log file parallel write на 2-м инстансе?
SQL> @ash_block_tree "event = 'log file parallel write' and inst_id = 2"
 
LVL INST_ID BLOCKING_TREE        EVENT                      WAITS_COUNT SESS_COUNT AVG_WA
--- ------- -------------------- -------------------------- ----------- ---------- ------
  1       2 (LG..)               log file parallel write           3400          4     69 -- LGWR workers on LOG FILE PARALLEL WRITE block
  2       2   (LGWR)             LGWR any worker group              182          1    183 -- following session/event types,
  2       2   (LG..)             LGWR worker group ordering         170          4    189
  2       2   (LGWR)             LGWR all worker groups              76          1    201
  3       2     (FOREGROUND)     log file sync                     2961       1412    250 -- which block FG user session waiting on LOG FILE SYNC
  3       2     (LMS.)           gcs log flush sync                 545          6     11
...

ASH_IO_WAITS.SQLТоп SQL/процессов в разрезе I/O ожиданий/блоков/операций по записям ASH

ASH_IOBJ_WAITS.SQL — аналогичный предыдущему топ сегментов бд в разрезе I/O ожиданий|блоков|операций/requests по записям ASH, например, ТОП-10 сегментов по кол-ву прочитанных/записанных блоков бд за последний час:

SQL> @ash_iobj_waits blocks 10 "where sample_time > sysdate - 1/24"

OBJECT_NAME                           TABLESPACE_NAME  SUM(WAIT_COUNT) waits%  SUM(REQUESTS) reqs%   SUM(BLOCKS) blocks% event(waits:requests:blocks)
------------------------------------- ---------------- --------------- ------- ------------- ------- ----------- ------- ----------------------------
TABLE PARTITION XL.EXPORTS.TRANSFER   TABLESPACE#1                1610   11.60          3141    3.83      146395   31.87 (1610:3141:146395)
TABLE XL.RESERVES.                    TABLESPACE#1                 450    3.24           450    0.55      114942   25.02 (450:450:114942)
TABLE XS.ORDERS.                      TABLESPACE_XS                376    2.71           376    0.46       45722    9.95 (376:376:45722)
TABLE PARTITION XL.CONSUMERS.ARCHIVE  TABLESPACE_ARCH             1632   11.76         40385   49.18       40466    8.81 (1632:40385:40466)
TABLE XL.PAYMENTS.                    TABLESPACE_R                 611    4.40          1381    1.68       31201    6.79 (611:1381:31201)
TABLE XL.PARTNER_T_STATS.             TABLESPACE#1                  81    0.58          8347   10.16        8347    1.82 (81:8347:8347)
TABLE XL.INVOICES.                    TABLESPACE#2                 361    2.60          7756    9.45        7910    1.72 (361:7756:7910)
TABLE XL.SHIPMENTS.                   TABLESPACE#2                 253    1.82           255    0.31        7854    1.71 (253:255:7854)
TABLE XL.TRANSACTIONS.                TABLESPACE#2                  60    0.43            60    0.07        5918    1.29 (60:60:5918)
INDEX XL.PK_PAYMENTS.                 TABLESPACE_R                  53    0.38          3536    4.31        3536    0.77 (53:3536:3536)

— в дополнение к DBA_HIST_SEG_STAT / AWR

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

ASH_SNAP.SQL — снапшот ASH (с доп.полями из GV$SQL) в форме таблицы ASH_YYYYMMDDHH24MI в табл.пр-ве USERS

SQL> @ash_snap
 
INST_ID MIN(SAMPLE_TIME)
------- --------------------------------------------------------------------------------
      1 17-OCT-16 06.45.17.505 PM
 
 --- 
 --- The Snap Table ASH_201610171935 was successfully created in tablespace USERS
 --- 
 
PL/SQL procedure successfully completed

ASH_SQL_CLUSTER_BLOCK_WAITS.SQL — ASH-поиск совпадающих по времени кластерных ожиданий (с разных инстансов) одних и тех же блоков бд в разрезе CLIENT_ID, SQL_ID, EVENT:

SQL> --                            begin_time    end_time      SQL_ID  Лаг(в сек.) между Cluster waits на инстансах  Ограничение по мин.кол-ву (having count(*) > ...)
SQL> @ash_sql_cluster_block_waits "24.10 10:00" "24.10 15:00" ""       2                                             10
 
INST_ID CLIENT_ID  SQL_OPNAME1  SQL_ID1       EVENT1                  ASH_BLOCK_INST REM_BLOCK_INST SQL_OPNAME2  SQL_ID2       EVENT2                 OBJECT_TYPE OBJECT_NAME WAITS_COUNT CONC_BLOCK_COUNT MIN_SAMPLE_TIME           MAX_SAMPLE_TIME
------- ---------- ------------ ------------- ----------------------- -------------- -------------- ------------ ------------- ---------------------- ----------- ----------- ----------- ---------------- ------------------------- -------------------------
      2 Client_1#  INSERT       0dcx2dj09bwab gc buffer busy release                              1 SELECT       1qr3asxwhuczq gc buffer busy acquire                                  21                1 24-OCT-16 12.53.37.051 PM 24-OCT-16 12.53.37.051 PM
      1            SELECT       05uqdabhzncdc gc buffer busy acquire               1              2 SELECT       9f0j8pj4fvj7h gc buffer busy acquire                                  17                1 24-OCT-16 01.04.08.575 PM 24-OCT-16 01.04.12.575 PM
      2            SELECT       9f0j8pj4fvj7h gc buffer busy acquire                              1 SELECT       05uqdabhzncdc gc buffer busy acquire                                  12                1 24-OCT-16 01.04.06.638 PM 24-OCT-16 02.14.41.143 PM
      1 Payments#  INSERT       7fzz1w1ckfnqx gc buffer busy acquire               1              2 SELECT       cumjq42201t37 gc buffer busy acquire                                  11                1 24-OCT-16 10.31.14.378 AM 24-OCT-16 10.31.14.378 AM
SQL> --"Ожидающие" сессии                                                             "Блокирующие" сессии                                                                                   Кол-во блоков, за которые происходила конкуренция

ASH_SQL_LOCK.SQLСкрипт для асинхронных блокировок/блокеров из [истории] ASH

ASH_SQL_WAIT_TREE.SQL — «дерево блокировок» по записям ASH (левее/вначале — заблокированные сессии, далее/правее — блокеры), например, для сессий, ожидавщих library cache lock в течение посл.часа, можно увидеть следующую картину:

SQL> --                  Условие выбора ожиданий/блокировок  Ограничение мин.кол-ва (having count(*) > ...)  Доп.условие выбора ASH-записей
SQL> @ash_sql_wait_tree "event = 'library cache lock'"       5                                              "where sample_time > sysdate-1/24"
 
LVL INST_ID BLOCKING_TREE  CLIENT_ID  EVENT               WAIT_CLASS   DATA_OBJECT_P1RAW  IN_PARSE WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID       MIN_STIME          MAX_STIME          SQL_ID        TOP_LEVEL_SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_TEXT  
--- ------- -------------- ---------- ------------------- ------------ ------------------ -------- ----------- ----------- ------ ---------- --------------- ------------------ ------------------ ------------- ---------------- ------------------- ----------- ---------------- ------------------ ----------
  1       2 (P...)         Client2#   library cache lock  Concurrency  0000000F253FAEE8   N                 39           0    963          6 VALID i#2       24-OCT-16 04.18.47 24-OCT-16 04.19.15 2s6tf19ru2pwc 2s6tf19ru2pwc              132196338           0                0 SELECT STATEMENT   select ...
  1       1 (USER)                    library cache lock  Concurrency  00000027D6960AB0   N                 37           0    236         37 VALID i#1       24-OCT-16 03.44.54 24-OCT-16 04.36.02                                                  0           0                                     
  1       2 (USER)                    library cache lock  Concurrency  00000027DE800660   N                 31           0    485         31 GLOBAL i#       24-OCT-16 03.42.56 24-OCT-16 04.36.00                                                  0           0                                     
  1       1 (USER)                    library cache lock  Concurrency  00000027D6960AB0   N                 17           0     14         17 GLOBAL i#       24-OCT-16 03.46.06 24-OCT-16 04.39.13                                                  0           0                                     
  2       2   (P...)       Client2#   On CPU / runqueue                ..                 Y                 41           0      0          2 NOT IN WAIT i#  24-OCT-16 04.18.48 24-OCT-16 04.18.57 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...
  2       2   (P...)       Client2#   On CPU / runqueue                XS.ORDERRS.        Y                 31           0      0          2 NOT IN WAIT i#  24-OCT-16 04.19.04 24-OCT-16 04.19.14 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...
  3       2     (P...)     Client2#   On CPU / runqueue                XS.ORDERRS.        Y                 14           0      0          3 NOT IN WAIT i#  24-OCT-16 04.18.58 24-OCT-16 04.19.14 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...

--LVL                Уровень вложенности
--BLOCKING_TREE      Тип сессии, (P...) - PX processes, (J...) - Job processes, etc 
--DATA_OBJECT_P1RAW  Для Shared Pool-related ожиданий - KGL Handle Address; иначе - DATA_OBJECT (ash.current_obj#)
--IN_PARSE           Состояние выполнения запроса
--EXECS_COUNT        Кол-во неповторяющихся SQL_EXEC_ID
--SESS_COUNT         Кол-во неповторяющихся сессий
--BLOCK_SID          ash.blocking_session_status

, или, уточнив SQL_ID:

SQL> @ash_sql_wait_tree "event = 'library cache lock' and sql_id = '2s6tf19ru2pwc'" 5 "where sample_time > sysdate-1/24"
 
LVL INST_ID BLOCKING_TREE  CLIENT_ID  EVENT               WAIT_CLASS   DATA_OBJECT_P1RAW  IN_PARSE WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID       MIN_STIME          MAX_STIME          SQL_ID        TOP_LEVEL_SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_TEXT
--- ------- -------------- ---------- ------------------- ------------ ------------------ -------- ----------- ----------- ------ ---------- --------------- ------------------ ------------------ ------------- ---------------- ------------------- ----------- ---------------- ------------------ ----------
  1       2 (P...)         Client2#   library cache lock  Concurrency  0000000F253FAEE8   N                 39           0    963          6 VALID i#2       24-OCT-16 04.18.47 24-OCT-16 04.19.15 2s6tf19ru2pwc 2s6tf19ru2pwc              132196338           0                0 SELECT STATEMENT   select ...
  2       2   (P...)       Client2#   On CPU / runqueue                ..                 Y                 41           0      0          2 NOT IN WAIT i#  24-OCT-16 04.18.48 24-OCT-16 04.18.57 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...
  2       2   (P...)       Client2#   On CPU / runqueue                XS.ORDERRS.        Y                 31           0      0          2 NOT IN WAIT i#  24-OCT-16 04.19.04 24-OCT-16 04.19.14 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...
  3       2     (P...)     Client2#   On CPU / runqueue                XS.ORDERRS.        Y                 14           0      0          3 NOT IN WAIT i#  24-OCT-16 04.18.58 24-OCT-16 04.19.14 2s6tf19ru2pwc 2s6tf19ru2pwc             2374764132           0                                     select ...

— из чего видно, как PX сессии (P…) количеством (SESS_COUNT=6) в первой строке в процессе генерации курсора с PHV=132196338, заблокированые на library cache lock другими PX сессиями в состоянии разбора (IN_PARSE=Y) курсора с другим PHV=2374764132, ожидают объекта с KGL Handle Address = 0000000F253FAEE8 типа:

SQL> select * from x$kglob w where w.kglhdadr = '0000000F253FAEE8';
 
ADDR               INDX INST_ID KGLHDADR         KGLHDCLT KGLNAOWN KGLNAOBJ         KGLFNOBJ           KGLNAHSH KGLNAHSV                         KGLHDNSP KGLHDNSD        KGLOBTYD
---------------- ------ ------- ---------------- -------- -------- ---------------- ---------------- ---------- -------------------------------- -------- --------------- --------
00007FF6F29269B0 131613       2 0000000F253FAEE8    30414 $BUILD$  2c1b2e0a6fa1578c 2c1b2e0a6fa1578c  612529870 4b3ad127ec9c06db267394c0248276ce       82 SQL AREA BUILD  CURSOR

, многократно упоминаемого, например, в Bug 12633340 Heavy «library cache lock» and «library cache: mutex X» contention for a «$BUILD$.xx» lock с рекомендациями Find the SQL statement that the $BUILD$ entry relates to and check why this cursor seems to be having problems building a child cursor. Try to eliminate any reasons for not sharing that SQL
И далее если с помощью другого скрипта SHARED_CU12_NOXML.SQL посмотреть на курсоры этого запроса только с PHV=2374764132:

SQL> @shared_cu12_noxml 2s6tf19ru2pwc 2374764132
 
INST EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ROWS_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC PLSQL_PER_EXEC CURSOR_STATUS       PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON OPTIMIZER_STATS  BIND_EQ_FAILURE  CURSOR_PARTS_MISMATCH ROLL REASON#1                        SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
---- ----- ------------- -------------------- ------------------- ------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- ------------ -------------- ------------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --- ---------- ---------------- ---------------- --------------------- ---- ------------------------------- ----------------- --------- ---------------- -----------
   2     1             0 2016-10-24/05:56:40  24.10.2016 05:56:47             1      7465254      5427000               1        470928           6383      1057544        742722       393836          10677 INVALID_UNAUTH           2374764132           1916532150             47     2 N          N          N          Y                  Y              12 N     86        7         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
   1     3             0 2016-10-24/07:56:42  24.10.2016 07:56:47             1      5189775      1391000               1        126976           1127       370131       3408848        91102           2932 INVALID_UNAUTH           2374764132           1916532150             47     0 N          N          N          Y                  Y              19 N     86        7         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
   2     2             0 2016-10-24/07:57:15  24.10.2016 07:57:20             1      5651411      1931500               1        129412           1144       623533       3071538       133580           5112 INVALID_UNAUTH           2374764132           1916532150             47     4 N          N          N          Y                  Y              10 N     84        8         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
   1     1             0 2016-10-24/13:42:09  24.10.2016 13:42:13             1      4628904      4550000               1        641067             96        54748           631        10242              0 INVALID_UNAUTH           2374764132           1916532150             50     7 N          N          N          Y                  Y               9 N     93        9         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
   2     1             0 2016-10-24/14:01:58  24.10.2016 14:02:06             1      9480705      7226000               1        705414           2459      1375002        547982       497431              0 INVALID_UNAUTH           2374764132           1916532150             50    12 N          N          N          Y                  Y              20 N     93        9         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
   2     1             0 2016-10-24/17:18:37  24.10.2016 17:18:42             1      5458370      4984000               1       1754281              0            0        408767       166630              0 VALID                    2374764132           1916532150             50     0 N          N          N          Y                  Y               9 N     95        7         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)
...

— можно видеть, как курсоры с этим PHV многократно генерируются по причине Auto Reoptimization Mismatch(1) с применением набора Oracle 12.1 Adaptive Features: Feedback Statistics (USE_FEEDBACK_STATS), и, как следствие, Reoptimization (REOPT, REOPT_HINTS), SQL Plan Directives (SPD_Valid, SPD_Used) и Adaptive Dynamic Sampling (DS_LEVEL)

ASH_SQLMON2.SQL — мониторинг выполнения запроса по записям ASH с фильтром по SQL_ID [PLAN_HASH_VALUE] [SQL_EXEC_ID], включая рекурсивные запросы и PL/SQL вызовы — Большой план выполнения и трассировка рекурсивных запросов с помощью ASH, например:

SQL> -- для запроса с SQL_ID=8b2pdkpkzbzku PHV=1849780498 можно видеть:
SQL> @ash_sqlmon2 8b2pdkpkzbzku 1849780498 "" "where sample_time > sysdate - 1/24"
 
LAST_PLSQL                   SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                            QBLOCK_NAME  CARDINALITY      BYTES       COST MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE
---------------------------- ------------- --------------- ---- --------------------------------------------------------- ------------ ----------- ---------- ---------- ----------------- ------------------------ ---------- ----------------------------------------------------------------------------------
Main Query                   8b2pdkpkzbzku      1849780498    0   SELECT STATEMENT                                                                                 15609           9821184                  1048576          4 ON CPU(4)
                                                              1     HASH GROUP BY                                         SEL$9ACFD7AD          27       3267      15609          25615360                  1048576         10 ON CPU(10)
                                                              2       FILTER                                                                                                                                                   
                                                              3         NESTED LOOPS                                                            27       3267      15608                                                       
                                                              4           HASH JOIN                                                             27       3105      15608                                                       
                                                              5             MAT_VIEW ACCESS FULL                          SEL$9ACFD7AD           9         72        107                                                       
                                                              6             NESTED LOOPS                                                      6521     697747      15502                                                       
                                                              7               NESTED LOOPS                                                    6521     697747      15502                                                       
                                                              8                 NESTED LOOPS                                                  6521     586890       8977                                                       
                                                              9                   NESTED LOOPS                                                6506     461926       2467                                                       
                                                             10                     NESTED LOOPS                                               958      46942       1515                                                       
                                                             11                       TABLE ACCESS BY INDEX ROWID BATCHED SEL$9ACFD7AD         954      12402        125                                                       
                                                             12                         INDEX RANGE SCAN                  SEL$9ACFD7AD         954                    13                                                       
                                                             13                       TABLE ACCESS BY INDEX ROWID BATCHED SEL$9ACFD7AD           1         36          2                                                       
                                                             14                         INDEX RANGE SCAN                  SEL$9ACFD7AD           1                     0                                                       
>>>                                                          15                     TABLE ACCESS BY INDEX ROWID BATCHED   SEL$9ACFD7AD           7        154          1           9117696                  1048576          3 db file sequential read(1); gc cr multi block request(1); db file parallel read(1)
                                                             16                       INDEX RANGE SCAN                    SEL$9ACFD7AD           7                     0           9117696                  1048576          2 db file sequential read(2)
                                                             17                   TABLE ACCESS BY GLOBAL INDEX ROWID      SEL$9ACFD7AD           1         19          1          24780800                  1048576          3 db file sequential read(2); ON CPU(1)
>                                                            18                     INDEX UNIQUE SCAN                     SEL$9ACFD7AD           1                     0          24780800                  1048576         12 ON CPU(6); db file sequential read(6)
                                                             19                 INDEX UNIQUE SCAN                         SEL$9ACFD7AD           1                     0           9117696                  1048576          1 ON CPU(1)
                                                             20               TABLE ACCESS BY INDEX ROWID                 SEL$9ACFD7AD           1         17          1          12704768                  1048576          2 ON CPU(2)
                                                             21           INDEX UNIQUE SCAN                               SEL$9ACFD7AD           1          6          0                                                       
PACKAGE#01.GET_CURRENCY_RATE 8h1971fbw4asf      2413335411    0   SELECT STATEMENT                                                                                   186                 0                        0          3 ON CPU(3)
                                                              1     VIEW                                                  SEL$2               1124      34844        186                 0                        0         38 ON CPU(38)
                                                              2       WINDOW SORT                                         SEL$2               1124      25852        186                 0                        0        418 ON CPU(418)
                                                              3         MAT_VIEW ACCESS FULL                              SEL$2               1124      25852        183                 0                        0        564 ON CPU(564)
                                                              4           MAT_VIEW ACCESS FULL                            SEL$3                  1          9          2                 0                        0            
PL/SQL                                                   0    0    PACKAGE#01.GET_CURRENCY_RATE                                                                                          0                        0          1 ON CPU(1)
SQL Summary                                              0    0 ASH fixed 11 execs from 8 sessions                                                                                                                          37  ash rows were fixed from 10.10.2016 15:19:46 to 10.10.2016 16:17:59
SQL> -- 1) что основная часть записей в ASH (ASH_ROWS) относится к рекурсивному запросу SQL_ID=8h1971fbw4asf, вызываемому через PACKAGE#01.GET_CURRENCY_RATE:
SQL> @ash_sqlmon2 8h1971fbw4asf "" "" "where sample_time > sysdate - 1/24 and top_level_sql_id = '8b2pdkpkzbzku'"
 
LAST_PLSQL                   SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                            QBLOCK_NAME     OBJECT_ALIAS              OBJECT_NAME        MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE
---------------------------- ------------- --------------- ---- --------------------------------------------------------- --------------- ------------------------- ------------------ ----------------- ------------------------ ---------- --------------------------------------------------------------------
Soft Parse                   8h1971fbw4asf      2413335411   -1 sql_plan_hash_value > 0; sql_exec_id is null                                                                                    17807360                  2097152          5 ON CPU(5)
Main Query                   8h1971fbw4asf      2413335411    0   SELECT STATEMENT                                                                                                              10673152                  1048576          8 ON CPU(8)
>>                                                            1     VIEW                                                  SEL$2           from$_subquery$_001@SEL$1                             23470080                  2097152         91 ON CPU(91)
>>>                                                           2       WINDOW SORT                                         SEL$2                                                                 25746432                  2097152        987 ON CPU(987)
>>>                                                           3         MAT_VIEW ACCESS FULL                              SEL$2           T_CURRENCY_RATE_V2@SEL$2  T_CURRENCY_RATE_V2          25746432                  2097152       1271 ON CPU(1271)
                                                              4           MAT_VIEW ACCESS FULL                            SEL$3           T_CURRENCY@SEL$3          T_CURRENCY                  11787264                  1048576          3 ON CPU(3)
SQL Summary                                              0    0 ASH fixed 2360 execs from 8 sessions                                                                                                                                    2365  ash rows were fixed from 10.10.2016 15:19:26 to 10.10.2016 16:19:27
SQL> -- 2) в основном, SQL_ID=8h1971fbw4asf рекурсивно вызывается именно запросом SQL_ID=8b2pdkpkzbzku
SQL> select top_level_sql_id, count(*)
  2    from gv$active_session_history
  3   where sql_id = '8h1971fbw4asf'
  4   group by top_level_sql_id
  5   order by count(*) desc
  6  /
 
TOP_LEVEL_SQL_ID   COUNT(*)
---------------- ----------
8b2pdkpkzbzku         29283 -- тут
1hfg5z4hd7faj          1080
da0qdhtw4mhqh           171
6cc8msf4r7pzj            97
SQL> -- 3) оптимизация SQL_ID=8h1971fbw4asf определённо улучшит время выполнения запроса SQL_ID=8b2pdkpkzbzku

Кроме ожиданий, которые можно видеть и в текстовом выводе SQL Monitor-а, скрипт показывает кол-во работавших PX-процессов на каждом шаге плана выполнения при запуске по конкретному SQL_EXEC_ID, и статистику выполнений коротких запросов — при запуске скрипта с указанием SQL_ID [и PLAN_HASH_VALUE]:

SQL> @ash_sqlmon2 3z4sm5uqfyyfn 167101290
 
LAST_PLSQL  SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                               QBLOCK_NAME  CARDINALITY      BYTES       COST TEMP_SPACE         PX MAX_PGA_ALLOCATED MAX_TEMP_SPACE_ALLOCATED   ASH_ROWS WAIT_PROFILE
----------- ------------- --------------- ---- -------------------------------------------- ------------ ----------- ---------- ---------- ---------- ---------- ----------------- ------------------------ ---------- ------------------------------------------------------------------------------------------------------------------------
Soft Parse  3z4sm5uqfyyfn       167101290   -1 sql_plan_hash_value > 0; sql_exec_id is null                                                                    0           4464640                        0          2 db file scattered read(1); ON CPU(1)
Main Query  3z4sm5uqfyyfn       167101290    0   SELECT STATEMENT                                                                    78142                                                                             
                                             1     FOR UPDATE                               SEL$1                                                                                                                      
                                             2       SORT ORDER BY                                                52       1612      78142                                                                             
                                             3         TABLE ACCESS FULL                    SEL$1                 52       1612      78141                     6           4464640                  1048576       2969 ON CPU(2939); db file scattered read(13); gc cr multi block request(8); db file sequential read(7); gc cr grant 2-way(2)
SQL Summary                             0    0 ASH fixed 1985 execs from 7 sessions                                                                                                                               2971  ash rows were fixed from 22.11.2016 17:28:24 to 22.11.2016 18:28:33

ASH_SQLMON12.SQL — аналогичен предыдущему с добавлением возможности
мониторинга запросов, использующих 12c Adaptive Plan, пример и описание — 12c: 2 x FULL_PLAN_HASH_VALUE для одного плана выполнения / PLAN_HASH_VALUE и скрипт ASH_SQLMON12.SQL

SQL> @ash_sqlmon12 fkw6hb5mrw02t 3887377781
 
LAST_PLSQL       ID    DISP  PLAN_OPERATION                                                       COST ASH_ROWS WAIT_PROFILE
---------------- ----- ----- ------------------------------------------------------------------- ----- -------- -------------------------------------------------------------------- 
Main Query           0    0    SELECT STATEMENT                                                    572                     
                     1    1      SORT AGGREGATE                                                                            
                     2    2        VIEW                                                            572                     
                     3    3          SORT UNIQUE                                                   572       38 ON CPU(38) 
                     4    4            UNION-ALL                                                              9 ON CPU(9)  
                 -   5    4              HASH JOIN                                                  20                     
                     6    5                NESTED LOOPS                                             20                     
                     7    6                  NESTED LOOPS                                           20                     
                 -   8    6                    STATISTICS COLLECTOR                                                        
                     9    7                      NESTED LOOPS                                       17                     
                    10    8                        NESTED LOOPS OUTER                               14                     
                    11    9                          PARTITION LIST ALL                             11                     
                    12   10                            TABLE ACCESS BY LOCAL INDEX ROWID BATCHED    11        4 ON CPU(4)  
                    13   11                              INDEX RANGE SCAN                            7                     
                    14   12                          TABLE ACCESS BY INDEX ROWID                     1        2 ON CPU(2)  
                    15   13                            INDEX UNIQUE SCAN                             0        6 ON CPU(6)  
                    16   14                        TABLE ACCESS BY INDEX ROWID                       1                     
                    17   15                          INDEX UNIQUE SCAN                               0        1 ON CPU(1)  
                    18   16                    INDEX RANGE SCAN                                      0        2 ON CPU(2)  
                    19   17                  MAT_VIEW ACCESS BY INDEX ROWID                          1        3 ON CPU(3)  
                 -  20   17                MAT_VIEW ACCESS FULL                                      1                     
                    21   18              NESTED LOOPS                                              550                     
                    22   19                NESTED LOOPS                                            550        2 ON CPU(2)  
                    23   20                  NESTED LOOPS OUTER                                    492        1 ON CPU(1)  
                    24   21                    HASH JOIN                                           435       33 ON CPU(33) 
                    25   22                      NESTED LOOPS                                      407        1 ON CPU(1)  
                    26   23                        NESTED LOOPS                                    407                     
                    27   24                          TABLE ACCESS BY INDEX ROWID BATCHED           158       54 ON CPU(54) 
                    28   25                            INDEX RANGE SCAN                              4        6 ON CPU(6)  
                    29   26                          INDEX UNIQUE SCAN                               0       18 ON CPU(18) 
                    30   27                        TABLE ACCESS BY INDEX ROWID                       1       20 ON CPU(20) 
                    31   28                      MAT_VIEW ACCESS FULL                               28                     
                    32   29                    TABLE ACCESS BY INDEX ROWID                           1       44 ON CPU(44) 
                    33   30                      INDEX UNIQUE SCAN                                   0      112 ON CPU(112)
                    34   31                  INDEX UNIQUE SCAN                                       0       37 ON CPU(37) 
                    35   32                TABLE ACCESS BY GLOBAL INDEX ROWID                        1       27 ON CPU(27) 
Main SQL Summary             ASH fixed 420 execs from 149 sessions                                          420  ash rows were fixed from 26.10.2016 11:58:39 to 26.10.2016 18:54:24

ASH_SQLMON12S.SQL — ASH мониторинг выполнения запроса с группировкой по Query Block (QBLOCK_NAME) — ASH_SQLMON12S.SQL: блочный мониторинг длинных планов выполнения:

SQL> @ash_sqlmon12s duwnj7x2rabfu
 
SQL_ID        SQL_PLAN_HASH_VALUE QBLOCK_NAME  ASH_ROWS WAIT_PROFILE                                                                                                                 MAX_PX_COUNT MIN_SAMPLE_TIME           MAX_SAMPLE_TIME           MAX_TEMP_SPACE_ALLOCATED MAX_PGA_ALLOCATED
------------- ------------------- ------------ -------- ---------------------------------------------------------------------------------------------------------------------------- ------------ ------------------------- ------------------------- ------------------------ -----------------
duwnj7x2rabfu           868370966                    66 db file sequential read(53); ON CPU(8); gc cr grant 2-way(2); ON CPU(1); read by other session(1); gc current block 2-way(1)           37 27-OCT-16 01.46.54.586 PM 27-OCT-16 03.52.32.337 PM                  2097152          10419200
duwnj7x2rabfu           868370966 SEL$2C0D7645       38 db file sequential read(12); db file sequential read(5); db file sequential read(4); db file sequential read(3); db file seq            9 27-OCT-16 01.34.29.770 PM 27-OCT-16 03.51.07.157 PM                  2097152          10419200
duwnj7x2rabfu           868370966 SEL$BF82FE11        3 ON CPU(2); ON CPU(1)                                                                                                                    1 27-OCT-16 02.21.33.843 PM 27-OCT-16 03.45.40.322 PM                  2097152           9191424
duwnj7x2rabfu          1526528793                    30 ON CPU(13); ON CPU(12); ON CPU(3); ON CPU(2)                                                                                           11 27-OCT-16 05.08.37.556 PM 27-OCT-16 05.33.26.791 PM                  4194304          19282944
duwnj7x2rabfu          1526528793 SEL$2C0D7645        9 db file sequential read(6); db file sequential read(2); ON CPU(1)                                                                       5 27-OCT-16 05.03.46.920 PM 27-OCT-16 05.31.00.434 PM                  2097152          10419200
duwnj7x2rabfu          1526528793 SEL$BF82FE11      155 ON CPU(42); ON CPU(42); ON CPU(36); ON CPU(20); ON CPU(15)                                                                             40 27-OCT-16 05.04.16.960 PM 27-OCT-16 05.33.28.791 PM                  6291456          30505984
duwnj7x2rabfu          1665129135                    46 ON CPU(17); db file sequential read(8); ON CPU(6); ON CPU(4); ON CPU(3); db file parallel read(2); ON CPU(1); gc current blo           16 27-OCT-16 02.03.31.940 PM 27-OCT-16 04.01.03.571 PM                  1048576          10690560
duwnj7x2rabfu          1665129135 SEL$BF82FE11       67 ON CPU(27); ON CPU(16); ON CPU(14); ON CPU(10)                                                                                         25 27-OCT-16 02.02.43.810 PM 27-OCT-16 04.01.42.621 PM                  1048576           6872064
duwnj7x2rabfu          3625987712                    41 ON CPU(17); ON CPU(12); ON CPU(10); SQL*Net more data to client(1); ON CPU(1)                                                          16 27-OCT-16 01.39.42.503 PM 27-OCT-16 04.53.54.662 PM                  3145728          17915904
duwnj7x2rabfu          3625987712 SEL$2C0D7645      378 ON CPU(238); db file sequential read(34); ON CPU(29); db file sequential read(25); db file parallel read(23); db file sequen          210 27-OCT-16 01.31.54.376 PM 27-OCT-16 05.03.03.810 PM                  4194304          26763264
duwnj7x2rabfu          3625987712 SEL$BF82FE11      163 ON CPU(43); ON CPU(36); ON CPU(35); ON CPU(25); ON CPU(22); ON CPU(2)                                                                  41 27-OCT-16 01.01.44.124 PM 27-OCT-16 05.02.57.810 PM                  4194304          26763264

ASH_WAIT_TREE.SQL — ASH мониторинг блокировок сессий (более грубый чем ASH_SQL_WAIT_TREE.SQL) в поле BLOCKING_TREE вначале/слева идут заблокированные, далее/правее — блокеры

AVG_WA[IT_TIME_MS] — среднее ash.time_wated, мс
EST_WAITS и EST_AVG_LATENCY_MS — ср.кол-во и продолжительность ожиданий, рассчитанное по методике разработчиков ASH

SQL> @ash_wait_tree "event = 'read by other session'" 10 "where sample_time < sysdate"
 
LVL INST_ID BLOCKING_TREE     EVENT                    WAIT_CLASS WAITS_COUNT SESS_COUNT AVG_WA  EST_WAITS EST_AVG_LATENCY_MS
--- ------- ----------------- ------------------------ ---------- ----------- ---------- ------ ---------- ------------------
  1       1 (FOREGROUND)      read by other session    User I/O         28384        518     31    2349985                 12 -- сессии 1-го уровня блокированы
  2       1   (FOREGROUND)    db file scattered read   User I/O         18638         44     32    1180964                 16 -- сессиями 2-го уровня, кот. могут быть блокированы
  2       1   (FOREGROUND)    db file sequential read  User I/O          8509        312     28     843561                 10
  2       1   (FOREGROUND)    On CPU / runqueue                           652         50      0          0               1000
  2       1   (FOREGROUND)    db file parallel read    User I/O           316         15     34      23450                 13
  2       1   (FOREGROUND)    read by other session    User I/O           119         24     10     175434                  1
  3       1     (FOREGROUND)  db file scattered read   User I/O            34          9      5      45743                  1 -- сессиями 3-го уровня и т.д.
  3       1     (FOREGROUND)  On CPU / runqueue                            22         11      0          0               1000

BL_CREATE.SQL — [пере]создание SPM baseline на основе курсора, находящегося в Shared Pool

SQL> --         SQL_ID        PHV       "Baseline Name"
SQL> @bl_create 60dqb8a0mtfbp 459354512 "Test Baseline"
 
Baseline SQL_238c751dabd2f87c SQL_PLAN_2733p3qpx5y3wcbec8f58 was [re]created
for SQL_ID=60dqb8a0mtfbp, SQL_PLAN_HASH=459354512

BL_CREATE_AWR.SQL — [пере]создание SPM baseline на основе курсора из AWR

BL_DROP.SQL — удаление SPM baseline

BL_FIND_SQL_ID.SQL — поиск SQL_ID, для которого был создан SPM baseline

BL_CHECK4SQL_ID.SQL — список SPM baseline-ов, созданных для SQL_ID

BL_HINTS.SQL — список хинтов SPM baseline версии 11g, аналогично выводу:

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=> '&SQL_PLAN_NAME', format => '+OUTLINE'));

BL12_HINTS.SQL — список хинтов + доп.инфо для SPM baseline версии 12c, примеры:

SQL> select * from dba_sql_plan_baselines where version > '11.2.0.3.0' and rownum <= 1; -- для первого попавшегося Baseline
 
 SIGNATURE SQL_HANDLE           SQL_TEXT                                   PLAN_NAME                      ORIGIN         VERSION    CREATED            LAST_MODIFIED      LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE ADAPTIVE OPTIMIZER_COST
---------- -------------------- ------------------------------------------ ------------------------------ -------------- ---------- ------------------ ------------------ ------------- ------------- ------- -------- ----- ---------- --------- -------- --------------
6,00419915 SQL_53533350ea55b581 INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO  SQL_PLAN_56ntma3p5bdc16d0865dc AUTO-CAPTURE   12.1.0.2.0 28-MAY-16 03.39.12 28-MAY-16 03.39.12                             YES     NO       NO    YES        YES       NO               228853

SQL> @bl12_hints SQL_PLAN_56ntma3p5bdc16d0865dc
 
OUTLINE_HINTS
-------------------------------------------------------------------------------- можно оценить набор хинтов
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CAT"@"SEL$21")
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CLT"@"SEL$18")
SWAP_JOIN_INPUTS(@"SEL$DEBAA3C7" "CL0"@"SEL$20")
...
ALL_ROWS
OPT_PARAM('optimizer_index_caching' 10)
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
 
1458 rows selected
 
Notes
-------------------------------------------------------------------------------- и параметров
   sql_profile:
   sql_patch:
   baseline:
   outline:
   dyn_sampling:    2
   dop:
   dop_reason:
   card_feedback:   yes
   perf_feedback:
   adaptive_plan:
   spd_used:        5
   spd_valid:       32
   gtt_sess_stat:
   db_version:      12.1.0.2
   plan_hash_full:  2406441943
   plan_hash:       3928505669
   plan_hash_2:     1829266908

SQL> @bl_find_SQL_ID SQL_53533350ea55b581 SQL_PLAN_56ntma3p5bdc16d0865dc -- , найти SQL_ID
 
SQL_ID        PLAN_HASH_VALUE SQL_TYPE        SQL_EXEC_TIMESTAMP   SQL_HANDLE           PLAN_NAME                      ENABLED ACCEPTED FIXED REPRODUCED    BL_LAST_EXECUTED
------------- --------------- --------------- -------------------- -------------------- ------------------------------ ------- -------- ----- ------------- ----------------
4twn1z5sqfrrm               0 Baseln not used                      SQL_53533350ea55b581 SQL_PLAN_56ntma3p5bdc16d0865dc YES     NO       NO    YES

SQL> @bl_check4sql_id 4twn1z5sqfrrm -- , посмотреть полный список Baseline-ов для этого SQL_ID

SQL_HANDLE            PLAN_NAME                      ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE PHV_FULL   PHV              PHV2
--------------------- ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------------- ------- -------- ----- ---------- --------- ---------- ---------- ----------
SQL_53533350ea55b581  SQL_PLAN_56ntma3p5bdc1fddc407d AUTO-CAPTURE   12.1.0.2.0 2016\05\26 14:37:49 2016\05\26 14:37:49                                   YES     NO       NO    YES        YES       3688690662 4142983967 4259070077
...
SQL_53533350ea55b581  SQL_PLAN_56ntma3p5bdc139d5c3c8 AUTO-CAPTURE   12.1.0.2.0 2016\10\28 05:37:59 2016\10\28 05:37:59                                   YES     NO       NO    YES        YES       1362462354 640424688   970310600
SQL_53533350ea55b581  SQL_PLAN_56ntma3p5bdc101672b46 AUTO-CAPTURE   12.1.0.2.0 2016\10\28 13:54:28 2016\10\28 13:54:28                                   YES     NO       NO    YES        YES       4073200853 2216316815   23538502
SQL_53533350ea55b581  SQL_PLAN_56ntma3p5bdc1691f8954 MANUAL-LOAD    12.1.0.2.0 2016\05\26 14:30:40 2016\10\23 08:29:37 2016\10\23 08:29:37               YES     YES      NO    YES        YES       3237297515 3938539332 1763674452
 
457 rows selected -- с момента ручного MANUAL-LOAD создания Baseline, AUTO-CAPTURE Baseline-ы создаются автоматически для всех новых PHV, и в немалом кол-ве)

SQL> @bl_drop SQL_PLAN_56ntma3p5bdc16d0865dc -- удалить ненужный / Non-ACCEPTED
 
PL/SQL procedure successfully completed

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

SQL> --            owner segment_name part_name
SQL> @db_seg_cache XO    EXPORT       PROGRAM_COMPETITION
 
AREA         PARTITION_NAME       STATUS                         DISTINCT_BLOCK_COUNT                BLOCK_COUNT
------------ -------------------  ------------------------------ ----------------------------------- ------------
BUFFER CACHE PROGRAM_COMPETITION  cr                             348                                 603          -- consistent read
BUFFER CACHE PROGRAM_COMPETITION  scur                           608                                 608          -- shared current
BUFFER CACHE PROGRAM_COMPETITION  xcur                           33                                  33           -- exclusive
BUFFER CACHE ALL                  cr                             348                                 603          -- ROLLUP sums
BUFFER CACHE ALL                  scur                           608                                 608          -- ...
BUFFER CACHE ALL                  xcur                           33                                  33
BUFFER CACHE ALL                  ALL                            927                                 1244
DATABASE     PROGRAM_COMPETITION  db blocks                      891136                                           -- db blocks
SGA                               BUFFER CACHE of MAX SGA SIZE   155,155,693,568 of 187,904,819,200  (Resizeable) -- SGA info

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

SQL> @dba_hist_sqlstat "sql_id = '4twn1z5sqfrrm' and snap_id in (332565, 334166)"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME EXECS ROWS_PROCESSED SQL_ID              PLAN    COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US APWAITS_PER_EXEC CCWAITS_PER_EXEC PARSE_PER_EXEC PLSQL_PER_EXEC PX_PER_EXEC CLWAITS_SEC
---- ------------- --------------- ----- -------------- ------------- ---------- ------- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------------ --------------- ---------------------- ------------- ---------------- ---------------- ------------------- ---------------- ---------------- -------------- -------------- ----------- -----------
   1        332564 09.09 01:30         3         129987 4twn1z5sqfrrm 3938539332  260122             11     51457764     45893000       2560381               23065                0              0                  0               0                   6300         43329                0          4431215               65659                0          1116600             11          69090          10           0
   1        334165 12.10 10:00         2          89627 4twn1z5sqfrrm 3938539332  258763             11     23630913     23469000       1636428                6555                0              0                  0               0                   6615         44814                0            55952                3961                0            39102             11          96696          10           0
   2        334165 12.10 10:00         1          44813 4twn1z5sqfrrm 3938539332  258877             11     89606370     67907000       3368754               72868                0              0                  0               0                   6615         44813                0         19034422              411311                0          2529929             11          84273          10           0

DBA_HIST_SQLSTAT111.SQL то же для Oracle 11g

DEP_TREE.SQL — зависимости объекта бд:

SQL> @dep_tree XL V_CONTRACT_ATTRIBUTES
 
LEVEL DEP_TREE                                    DEPENDENCY REFERENCED_TYPE    REFERENCED_OBJ                      STATUS___LAST_DDL_TIME
----- ------------------------------------------- ---------- ------------------ ----------------------------------- -------------------------
    1 VIEW XL.V_CONTRACT_ATTRIBUTES               >          TABLE              XL.CONTRACT2                        VALID 12.10.2016 02:12:02
    1 VIEW XL.V_CONTRACT_ATTRIBUTES               >          VIEW               XL.V_CONTRACT_ATTRIBUTES2           VALID 30.06.2016 17:17:04
    2   VIEW XL.V_CONTRACT_ATTRIBUTES2            >>         VIEW               XL.V_CONTRACT_ATTRIBUTES_HISTORY    VALID 19.07.2016 18:23:51
    3     VIEW XL.V_CONTRACT_ATTRIBUTES_HISTORY   >>>        TABLE              XL.CONTRACT_ATTRIBUTES              VALID 30.06.2016 17:16:21
    3     VIEW XL.V_CONTRACT_ATTRIBUTES_HISTORY   >>>        TABLE              XL.CONTRACT_COLLOQUIAL              VALID 03.08.2016 04:59:52
    3     VIEW XL.V_CONTRACT_ATTRIBUTES_HISTORY   >>>        TABLE              XL.CONTRACT_COLLOQUIAL_TYPES        VALID 29.06.2016 12:25:36
    3     VIEW XL.V_CONTRACT_ATTRIBUTES_HISTORY   >>>        SYNONYM            PUBLIC.DBMS_LOB                     VALID 26.10.2015 03:14:00
    4       SYNONYM PUBLIC.DBMS_LOB               >>>>       PACKAGE            SYS.DBMS_LOB                        VALID 26.10.2015 03:22:08
    5         PACKAGE SYS.DBMS_LOB                >>>>>      PACKAGE            SYS.STANDARD                        VALID 26.10.2015 03:12:55
    5         PACKAGE SYS.DBMS_LOB                >>>>>      PACKAGE            SYS.SYS_STUB_FOR_PURITY_ANALYSIS    VALID 26.10.2015 03:07:46
    5         PACKAGE SYS.DBMS_LOB                >>>>>      PACKAGE            SYS.UTL_IDENT                       VALID 26.10.2015 03:13:55
    6           PACKAGE SYS.UTL_IDENT             >>>>>>     PACKAGE            SYS.STANDARD                        VALID 26.10.2015 03:12:55

FIX.SQL — поиск по полям BUGNO, SQL_FEATURE, DESCRIPTION обзора V$SESSION_FIX_CONTROL:

SQL> @fix "first k rows"
 
SESSION_ID      BUGNO VALUE SQL_FEATURE                         DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT     CON_ID
---------- ---------- ----- ----------------------------------- ---------------------------------------------------------------- ------------------------- ----- ---------- ----------
      4041    7135745     1 QKSFM_CURSOR_SHARING_8356253        allow first K rows optimization for all CTAS and IAS             10.2.0.5                      0          1          0
      4041    9881812     1 QKSFM_JPPD_10428278                 no first k rows in view under aggregate functions                11.2.0.3                      0          1          0
      4041   13489017     1 QKSFM_STATS_FEEDBACK_12954320       allow first K rows for DML/CTAS when mode specified via hint/par 11.2.0.4                      0          1          0
      4041   15996520     1 QKSFM_SQL_PLAN_MANAGEMENT_16026776  restore All Rows stats after costing next First K Rows permutati 11.2.0.4                      0          1          0
      4041   13040171     1 QKSFM_SQL_CODE_GENERATOR_13406619   limit prorated First K Rows cardinality by All Rows estimate     11.2.0.4                      0          1          0
      4041    9002958     1 QKSFM_CBO_12591120                  allow first k rows optimization for aggregate queries and PQ     12.1.0.1                      0          1          0
      4041   13848786     1 QKSFM_PARTITION_13522189            First K Rows costing for UNION ALL branches                      12.1.0.2                      0          1          0

INST.SQL — краткая информация об инстансах, включая текущий:

SQL> @inst
 
INST_ID INSTANCE_NAME  SERVICE  HOST_NAME        VERSION    PLATFORM_NAME    DATABASE_STATUS DATABASE_ROLE    STATUS   OPEN_MODE  STARTUP_TIME         RESETLOGS_TIME      INSTANCE_ROLE    ARCHIVER ACTIVE_STATE PARALLEL THREAD#
------- -------------- -------- ---------------- ---------- ---------------- --------------- ---------------- -------- ---------- -------------------- ------------------- ---------------- -------- ------------ -------- -------
1       orcl1          orcldb   db1.hostname.ru  12.1.0.2.0 Linux x86 64-bit ACTIVE          PRIMARY          OPEN     READ WRITE 30.10.2016 23:52:33  17.04.2015 18:02:19 PRIMARY_INSTANCE STARTED  NORMAL       YES      1
2*      orcl2          orcldb   db2.hostname.ru  12.1.0.2.0 Linux x86 64-bit ACTIVE          PRIMARY          OPEN     READ WRITE 31.10.2016 00:03:16  17.04.2015 18:02:19 PRIMARY_INSTANCE STARTED  NORMAL       YES      2

KGLLOCKPIN.SQL — online мониторинг объектов и субъектов блокировок Shared Pool library cache lock / library cache pin внутри инстанса, с генерацией запроса для поиска блокеров на др.инстансах:

SQL> @kgllockpin
 
HANDLER          KGLLKTYPE  NAMESP KGLOBTYD      KGLNAOWN KGLNAOBJ        MODE_HELD  MODE_REQ   SECS_IN_WAIT EVENT             SECONDS_IN_WAIT CLIENT_IDENTIFIER SID    SERIAL  SADDR            PROGRAM           SQL_ID        SQL_TEXT
---------------- ---------- ------ ------------- -------- --------------- ---------- ---------- ------------ ----------------- --------------- ----------------- ------ ------- ---------------- ----------------- ------------- ----------
00000014B69FA830 Lock       BODY   PACKAGE BODY  APPS     XXOR_PRINT_PKG  Exclusive  None       335          library cache pin             335 EVGENY            2355   63158   0000001541780AA8 JDBC Thin Client  9vqa7s2r3gc49 SELECT TT.
00000014B69FA830 Pin        BODY   PACKAGE BODY  APPS     XXOR_PRINT_PKG  None       Exclusive  335          library cache pin             335 EVGENY            2355   63158   0000001541780AA8 JDBC Thin Client  9vqa7s2r3gc49 SELECT TT.
...
00000014B69FA830 Lock       BODY   PACKAGE BODY  APPS     XXOR_PRINT_PKG  Null       None       1            PL/SQL lock timer               1 EVGENY            662    5269    0000001581494B88 JDBC Thin Client  5wqdq7d1tfwmw BEGIN
00000014B69FA830 Pin        BODY   PACKAGE BODY  APPS     XXOR_PRINT_PKG  Share      None       1            PL/SQL lock timer               1 EVGENY            662    5269    0000001581494B88 JDBC Thin Client  5wqdq7d1tfwmw BEGIN

LOCK_TREE_LOCAL.SQL — online мониторинг блокировок на основе GV$LOCK и GV$SESSION, например, (первыми/левее показаны блокеры, ниже/правее — заблокированные сессии) — взаимоблокировка 4-х сессий с CLIENT_ID типов Client_type#1 и Client_type#2:

SQL> @lock_tree_local
 
CHAR_DATE
-------------------
28.10.2016 11:09:56
 
Session altered
 
BLOCKING_TREE        PROGRAM                USERNAME          EVENT                         LAST_CALL_ET SECS_IN_WAIT BLOCK_SESSTAT PDML_ENABLED SQL_ID        OSUSER     SPID       CLNT_HOST  CLNT_PID CLNT_PORT SQL_TEXT REQ_ROWID
-------------------- ---------------------- ----------------- ----------------------------- ------------ ------------ ------------- ------------ ------------- ---------- ---------- --------- --------- --------- -------- ------------------
INST#1 SID#1613      python2.7@(TNS V1-V3)  JO Client_type#1  SQL*Net message from client            515          514 NO HOLDER     NO           6k04cpmqkwd7d jo         18829      host1g.   20228         36954 SELECT   AAABAAAAVAAARvyAAA
  INST#2 SID#1424    python2.7@(TNS V1-V3)  JO Client_type#2  enq: TX - row lock contention          514          514 VALID         NO           9p80y2zcm5hgy jo         19163      host1f.   19993         51898 UPDATE   AAJec2AB+AAAR8bAAr
    INST#1 SID#4127  python2.7@(TNS V1-V3)  JO Client_type#1  enq: TX - row lock contention          515          514 VALID         NO           531yvbgnufa63 jo         18960      host1g.   20228         37155 SELECT   AAABAAAAVAAARvyAAT
    INST#2 SID#329   python2.7@(TNS V1-V3)  JO Client_type#2  enq: TX - row lock contention          428          428 VALID         NO           97vzx5g9bzyqs jo         10286      host1f.   20153         37253 SELECT   AAABAAAAVAAARvyAAT

MUTEX_WAITS.SQL — online мониторинг mutex ожиданий:

SQL> @mutex_waits

LVL WAITING_SID PROGRAM                 SQL_ID        EVENT                   SECONDS_IN_WAIT BLOCKING_SID LC_SQL_ID     SID_HOLDING_MUTEX MUTEX_TYPE  MUTEX_LOCATION       LC_SQL_TEXT
--- ----------- ----------------------- ------------- ----------------------- --------------- ------------ ------------- ----------------- ----------- -------------------- ---------------
  1         576 python2.7@ (TNS V1-V3)  cfw8zfkubfmk5 cursor: pin S wait on X               5              cfw8zfkubfmk5              3030 Cursor Pin  kkslce [KKSCHLPIN2]  SELECT nvl_1 AS
  1         898 python2.7@ (TNS V1-V3)  ctbk9nadpwpk8 cursor: pin S wait on X               3              ctbk9nadpwpk8               466 Cursor Pin  kkslce [KKSCHLPIN2]           SELECT
  1        2283 python2.7@ (TNS V1-V3)  duwnj7x2rabfu cursor: pin S wait on X               3              duwnj7x2rabfu               859 Cursor Pin  kkslce [KKSCHLPIN2]  SELECT t_invoic
  1        2569 python2.7@ (TNS V1-V3)  cfw8zfkubfmk5 cursor: pin S wait on X               4              cfw8zfkubfmk5              3030 Cursor Pin  kkslce [KKSCHLPIN2]  SELECT nvl_1 AS
  1        3000 python2.7@ (TNS V1-V3)  ctbk9nadpwpk8 cursor: pin S wait on X               4              ctbk9nadpwpk8               466 Cursor Pin  kkslce [KKSCHLPIN2]           SELECT
  1        3016 python2.7@ (TNS V1-V3)  ctbk9nadpwpk8 cursor: pin S wait on X               3              ctbk9nadpwpk8               466 Cursor Pin  kkslce [KKSCHLPIN2]           SELECT
  1        3579 python2.7@ (TNS V1-V3)  ctbk9nadpwpk8 cursor: pin S wait on X               4              ctbk9nadpwpk8               466 Cursor Pin  kkslce [KKSCHLPIN2]           SELECT
  1        3888 python2.7@ (TNS V1-V3)  ctbk9nadpwpk8 cursor: pin S wait on X               4              ctbk9nadpwpk8               466 Cursor Pin  kkslce [KKSCHLPIN2]           SELECT

PARAM_.SQL — поиск по названиям, значениям и описаниям параметров:

SQL> @param_ feedback
 
NAME                              VALUE  IS_DEF   IS_MOD     IS_ADJ   DSC
--------------------------------- ------ -------- ---------- -------- -----------------------------------------
_optimizer_feedback_control              TRUE     FALSE      FALSE    controls the optimizer feedback framework
_optimizer_gather_feedback        TRUE   TRUE     FALSE      FALSE    optimizer gather feedback
_optimizer_performance_feedback   OFF    TRUE     FALSE      FALSE    controls the performance feedback
_optimizer_use_feedback           TRUE   TRUE     FALSE      FALSE    optimizer use feedback

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

SQL> @purge_cu 4nut8pr74vpff
 
PL/SQL procedure successfully completed

PVALID.SQL — список доступных значений для параметров

PVALID_.SQL — то же, включая скрытые параметры:

SQL> @pvalid cursor_sharing
 
Valid values for parameters %cursor_sharing%
 
PVALID_NAME                              ORD PVALID_VALUE  PVALID_
---------------------------------------- --- ------------- -------
cursor_sharing                             1 FORCE         
cursor_sharing                             2 EXACT         DEFAULT
cursor_sharing                             3 SIMILAR       

SQL> @pvalid_ cursor_sharing
 
PVALID_NAME                              PVALID_VALUE  PVALID_
---------------------------------------- ------------- -------
_optimizer_extended_cursor_sharing       NONE          
_optimizer_extended_cursor_sharing       UDO           
_optimizer_extended_cursor_sharing_rel   NONE          
_optimizer_extended_cursor_sharing_rel   SIMPLE        
cursor_sharing                           FORCE         
cursor_sharing                           EXACT         DEFAULT
cursor_sharing                           SIMILAR

REDOGEN_OBJ_HIST.SQL — Оценка генерации REDO по сегментам на основе записей ASH — Damir Vadas. How to … redo logs generation?

SQL> @redogen_obj_hist "17-Oct-16 18:00" "17-Oct-16 19:00" 10
 
WHEN                               OBJECT_NAME                    SUBOBJECT_NAME OBJECT_TYPE TABLE_NAME         DB_BLOCK_CHANGES REDO_P
---------------------------------- ------------------------------ -------------- ----------- ------------------ ---------------- ------
17-Oct-16 18:30 - 17-Oct-16 19:00  SYS_C_SNAP$_3164T_PERSON_PK                   INDEX       T_PERSON                    5942256  17.65
17-Oct-16 18:30 - 17-Oct-16 19:00  MY_INV_BALANCES_1_N4                          INDEX       MY_INV_BALANCES_1           5733872  17.03
17-Oct-16 18:30 - 17-Oct-16 19:00  MY_INV_BALANCES_1_N2                          INDEX       MY_INV_BALANCES_1           5729680  17.02
17-Oct-16 18:30 - 17-Oct-16 19:00  MY_INV_BALANCES_1_N1                          INDEX       MY_INV_BALANCES_1           5711680  16.96
17-Oct-16 18:00 - 17-Oct-16 19:00  XXNF_EVENT_QUEUE                              TABLE                                   1549744   4.60
17-Oct-16 18:00 - 17-Oct-16 19:00  MY_INV_BALANCES_1                             TABLE                                   1422800   4.23
17-Oct-16 18:00 - 17-Oct-16 19:00  FND_CONCURRENT_REQUESTS                       TABLE                                   1295920   3.85
17-Oct-16 18:30 - 17-Oct-16 19:00  V_ONLINE_PAYMENT                              TABLE                                   1219920   3.62
17-Oct-16 18:00 - 17-Oct-16 18:30  obj#1714549 dataobj#13473536                                                           798560   2.37
17-Oct-16 18:00 - 17-Oct-16 19:00  USER$                                         TABLE                                    392976   1.17
 
10 rows selected

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

SQL> @redogen_sql_hist "17-Oct-16 18:00" "17-Oct-16 19:00" 10
 
INST_ID WHEN                               EXEC_DELTA ROWS_PROC_DELTA BUFFER_GETS_DELTA BUFFER_RANGE SQL_ID        SQL_TEXT
------- ---------------------------------- ---------- --------------- ----------------- ------------ ------------- ------------------------------------------
      1 17-Oct-16 18:30 - 17-Oct-16 19:00           1         5557984          82296078            7 40pm95zyzhzfv INSERT INTO MY_INV_BALANCES_1       (appl
      2 17-Oct-16 18:00 - 17-Oct-16 19:00         520         3480360          32015171            8 7pnk789c9n0gs INSERT INTO XXXX_MANAGER_LEAVES_TEMP (A
      1 17-Oct-16 18:00 - 17-Oct-16 19:00         471         3152403          29393784            9 7pnk789c9n0gs INSERT INTO XXXX_MANAGER_LEAVES_TEMP (A
      1 17-Oct-16 18:30 - 17-Oct-16 19:00           1         2956010          18670116           11 4cp60t4xrd41k INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO 
      1 17-Oct-16 18:30 - 17-Oct-16 19:00           1         2955876          18570523           12 9k9v9hggqafc7  delete from "APPS"."T_PERSON"
      2 17-Oct-16 18:00 - 17-Oct-16 19:00         520         1407120          10376577           15 czp1wy2g1pzkm INSERT INTO XXXX_MANAGER_LEAVES_RUK_TMP (O
      1 17-Oct-16 18:00 - 17-Oct-16 19:00         472         1277232           9950866           16 czp1wy2g1pzkm INSERT INTO XXXX_MANAGER_LEAVES_RUK_TMP (O
      1 17-Oct-16 18:30 - 17-Oct-16 19:00           1          191424          16773151           13 aua62zy7qa4ph INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO 
      2 17-Oct-16 18:00 - 17-Oct-16 18:30           1           40684           1286587           18 d2t2d7fnzn5q0 MERGE INTO MY_INV_BALANCES_1 X USING ( WI
      2 17-Oct-16 18:30 - 17-Oct-16 19:00       18922           18922            368182           20 997s18pvtv7kc INSERT INTO GLOBAL_ORGANIZATION_TMP (ORGANI
 
10 rows selected

SESSON.SQL — запуск мониторинга сессии по SID для последующего отображения с помощью SESSOF.SQL

SQL> @sesson 910
 
PL/SQL procedure successfully completed

SESSOF.SQL — вывод статистик сессии (для предварительно кот.был выполнен SESSON.SQL):

SQL> @sessof
 
Session Time Model
 
STAT_NAME                                                               DELTA
---------------------------------------------------------------- ------------
sql execute elapsed time                                              2912601
DB time                                                               1883537
DB CPU                                                                1002000
PL/SQL execution elapsed time                                             287
 
 
Session Statistics
 
NAME                                                                    DELTA
---------------------------------------------------------------- ------------
process last non-idle time                                         1478008515
session connect time                                               1478008515
session uga memory                                                  445409040
session pga memory                                                  424345600
temp space allocated (bytes)                                          3145728
workarea memory allocated                                              424328
physical write total bytes                                             159744
physical write bytes                                                   159744
non-idle wait count                                                     20091
redo size                                                                2680
redo synch time overhead (usec)                                          2374
enqueue requests                                                         1344
enqueue conversions                                                      1150
user calls                                                               1087
enqueue releases                                                          809
enqueue waits                                                             756
PX remote messages sent                                                   649
PX remote messages recv'd                                                 648
PX local messages recv'd                                                  628
PX local messages sent                                                    627
...
 
48 rows selected
 
Session Wait Events
 
NAME                        WAIT_CLASS    WAITS TIME_MS TIMEOUTS AVG_WAIT_MS
--------------------------- ------------- ----- ------- -------- -----------
latch: shared pool          Concurrency       2       0        0         0,1
SQL*Net message to client   Network           6       0        0           0

SGA.SQL — краткая диагностика SGA:

SQL> @sga
 
ASMM_STATUS
-----------
ASMM:on
 
 
SGA Parameters
 
NAME                           MB DISPLAY_VALUE        DESCRIPTION
---------------------- ---------- -------------------- ------------------------------------------------------------
sga_max_size               179200 175G                 max total SGA size
sga_target                 179200 175G                 Target size of SGA
db_cache_size              136192 133G                 Size of DEFAULT buffer pool for standard block size buffers
shared_pool_size            27648 27G                  size in bytes of shared pool
streams_pool_size            1024 1G                   size in bytes of the streams pool
java_pool_size                128 128M                 size in bytes of java pool
db_16k_cache_size               0 0                    Size of cache for 16K buffers
db_2k_cache_size                0 0                    Size of cache for 2K buffers
db_32k_cache_size               0 0                    Size of cache for 32K buffers
db_4k_cache_size                0 0                    Size of cache for 4K buffers
db_8k_cache_size                0 0                    Size of cache for 8K buffers
db_flash_cache_size             0 0                    flash cache size for db_flash_cache_file
db_keep_cache_size              0 0                    Size of KEEP buffer pool for standard block size buffers
db_recycle_cache_size           0 0                    Size of RECYCLE buffer pool for standard block size buffers
large_pool_size                 0 0                    size in bytes of large pool
olap_page_pool_size             0 0                    size of the olap page pool in bytes
 
 
SGAINFO
 
NAME                                     MB RESIZEABLE
-------------------------------- ---------- ----------
Maximum SGA Size                     179200 No
Buffer Cache Size                    147968 Yes
Shared Pool Size                      27648 Yes
Startup overhead in Shared Pool       16345 No
Large Pool Size                        1664 Yes
Streams Pool Size                      1024 Yes
Java Pool Size                          768 Yes
Shared IO Pool Size                     512 Yes
Granule Size                            128 No
Redo Buffers                            121 No
Fixed SGA Size                            7 No
In-Memory Area Size                       0 No
Data Transfer Cache Size                  0 Yes
Free SGA Memory Available                 0 
 
 
Shared Pool Components - TOP10
 
NAME                               MB
-------------------------- ----------
gcs resources                    8753
gcs shadows                      4863
SQLA                             4355
free memory                      2742
KGLH0                            1951
db_block_hash_buckets            1408
SMODI                             413
gcs res hash bucket               384
KGLHD                             350
dbktb: trace buffer               276
 
 
Shared Pool Cache Top 10
 
HASH_VALUE OBJECT_NAME                           NAMESPACE       TYPE            KEPT   COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- ------------------------------------- --------------- --------------- ---- ---------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961                   HINTSET OBJECT  HINTSET OBJECT  NO            1                                  6718              6718          9               0         423056984 -- *
 517867923 INSERT /*+ BYPASS_RECURSIVE_CHECK */  SQL AREA        CURSOR          NO           23 2016-10-31/00:04:18              2349              1234         87             346          69046376
 269081421 select                 place_id,      SQL AREA        CURSOR          NO          156 2016-10-31/00:07:03              5165              2971        352            4269          63019832
3911925616 select                 place_id,      SQL AREA        CURSOR          NO          105 2016-10-31/00:07:21              5096              2961        391            2964          53619112
1290361854 INSERT /*+ BYPASS_RECURSIVE_CHECK */  SQL AREA        CURSOR          NO           27 2016-10-31/00:04:17              4840              2505        129             594          50093000
1165307354 SELECT invoice.id AS invoice_id, sum  SQL AREA        CURSOR          NO          231 2016-10-31/00:05:29           7359201           7287868        702         3872625          46722208
3646754055 select /*+ opt_param(             */  SQL AREA        CURSOR          NO           94 2016-11-01/00:21:53              1646              1326        277             486          38049312
1902599923 INSERT /*+ BYPASS_RECURSIVE_CHECK */  SQL AREA        CURSOR          NO           17 2016-10-31/00:04:20              2307              1316        221             271          31756104
3081682953 INSERT /*+ BYPASS_RECURSIVE_CHECK */  SQL AREA        CURSOR          NO           22 2016-10-31/00:04:15               284               194         83             132          28139608
2170648174 INSERT /*+ BYPASS_RECURSIVE_CHECK */  SQL AREA        CURSOR          NO           60 2016-10-31/00:09:46               503               364        179             228          27673608
 
SQL_ID        OBJECT_NAME                           VERSION_COUNT EXECUTIONS      LOADS FIRST_LOAD_TIME     PARSE_CALLS SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
------------- ------------------------------------- ------------- ---------- ---------- ------------------- ----------- ------------ -------------- -----------
1g0uy9hgdw2cm INSERT /*+ BYPASS_RECURSIVE_CHECK */             22         21         43 2016-10-31/00:04:18         208     69046573       26035040    25126160
8wrqcx080mqud select                 place_id,                155        840        163 2016-10-31/00:07:03         840     63026828       26106200    25579608
b45s597nkqgvh select                 place_id,                104        370        203 2016-10-31/00:07:21         370     53626639       14620384    14217696
5q7t8bx6fkqzy INSERT /*+ BYPASS_RECURSIVE_CHECK */             26         34         64 2016-10-31/00:04:17         334     50092487       17983872    16870080
duwnj7x2rabfu SELECT invoice.id AS invoice_id, sum            230    1292374        361 2016-10-31/00:05:29       54248     46728844       18148128    15074016
92vtdpgcpu387 select /*+ opt_param(             */             93        115        139 2016-11-01/00:21:53         147     38050035       17704832    17071232
4twn1z5sqfrrm INSERT /*+ BYPASS_RECURSIVE_CHECK */             16         10         55 2016-10-31/00:04:20         107     31755463       11726960    11528160
4da0p92vuxh09 INSERT /*+ BYPASS_RECURSIVE_CHECK */             21         20         41 2016-10-31/00:04:15          20     28138905        9091336     8899176
85mr8m60q2xmf INSERT /*+ BYPASS_RECURSIVE_CHECK */             59         52         89 2016-10-31/00:09:46          51     27671251        9397496     9338008
f43rvxnfry6nk select /*+ opt_param(             */             56        694         88 2016-11-01/00:00:50         698     27395061       11550480    11152208
 
 
SGA Resize Ops
 
SGA_COMPONENT                  OPER_TYPE     OPER_MODE SGA_PARAMETER     INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS       START_TIME          END_TIME
------------------------------ ------------- --------- ----------------- ------------ ----------- ---------- ------------ ------------------- -------------------
DEFAULT buffer cache           STATIC                  db_cache_size                0 15448460492 1544846049 COMPLETE     31.10.2016 00:03:26 31.10.2016 00:03:26
ASM Buffer Cache               STATIC                  db_cache_size                0           0          0 COMPLETE     31.10.2016 00:03:26 31.10.2016 00:03:26
...
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size     154484604928 15502147584 1550214758 COMPLETE     31.10.2016 00:05:44 31.10.2016 00:05:44
large pool                     SHRINK        DEFERRED  large_pool_size     2281701376  1744830464 1744830464 COMPLETE     31.10.2016 00:05:44 31.10.2016 00:05:44
DEFAULT buffer cache           SHRINK        IMMEDIATE db_cache_size     155021475840 15448460492 1544846049 COMPLETE     31.10.2016 00:09:14 31.10.2016 00:09:15
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size     154484604928 15461882265 1546188226 COMPLETE     31.10.2016 05:09:14 31.10.2016 05:09:14
java pool                      SHRINK        DEFERRED  java_pool_size       939524096   805306368  805306368 COMPLETE     31.10.2016 05:09:14 31.10.2016 05:09:14
shared pool                    GROW          DEFERRED  shared_pool_size   28991029248 29125246976 2899102924 CANCELLED    31.10.2016 21:10:59 31.10.2016 21:11:59
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size     154618822656 15448460492 1546188226 CANCELLED    31.10.2016 21:10:59 31.10.2016 21:11:59
Shared Pool Subpool distribution:
 
SUBPOOL                                                      BYTES         MB
------------------------------------------------------- ---------- ----------
shared pool (1):                                        4026531840       3840
shared pool (2):                                        4160749568       3968
shared pool (3):                                        4294967296       4096
shared pool (4):                                        3892314112       3712
shared pool (5):                                        4026531840       3840
shared pool (6):                                        4026531840       3840
shared pool (7):                                        4563402752       4352
shared pool (Total):                                    2899102924      27648

— в частности, Shared Pool Cache Top 10 показывает, как HINTSET OBJECT (*) 6004199150836888961 занимает 400+ MB SGA:

SQL> select sql_handle,
  2         max(cast(created as date)) as MAX_CREATED,
  3         origin,
  4         count(*)
  5    from dba_sql_plan_baselines
  6   where signature = 6004199150836888961
  7   group by sql_handle, origin;
 
SQL_HANDLE              MAX_CREATED ORIGIN           COUNT(*)
----------------------- ----------- -------------- ----------
SQL_53533350ea55b581    26.05.2016  MANUAL-LOAD             1
SQL_53533350ea55b581    01.11.2016  AUTO-CAPTURE          470

— и относится к вышеупомянутым Baseline-ам, автоматически генерируемым в режиме AUTO-CAPTURE после создания Baseline-а в ручном режиме (MANUAL-LOAD)

SHARED_CU12_NOXML.SQL (для Oracle 11g — SHARED_CU_NOXML.SQL) — диагностика состояния и истории генерации и выполнения курсора в Shared Pool, например:

SQL> --                 SQL_ID        PHV или 0 для всех PHV этого запроса
SQL> @SHARED_CU12_NOXML 1g0uy9hgdw2cm 0
-- статистика текущих выполнений запроса (V$SQL) + причин генерации новых курсоров (V$SQL_SHARED_CURSOR) + адаптивные эффекты, влиявшие на планы выполнения
INST EXECS USERS_OPENING FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ROWS_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC PLSQL_PER_EXEC CURSOR_STATUS  PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS V$SQL.Adapt XML.Adapt SPD_Valid SPD_Used  DS_LEVEL DOP DOP_REASON OPTIMIZER_STATS  BIND_EQ_FAILURE  CURSOR_PARTS_MISMATCH ROLL REASON#1                         REASON#2                                  FIX_CONTROL#1    SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
---- ----- ------------- -------------------- -------------------- ------------------- ------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- ------------ -------------- -------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----------- --------- --------- --------- -------- --- ---------- ---------------- ---------------- --------------------- ---- -------------------------------- --------------------------------------... -------------... ----------------- --------- ---------------- -----------
...
   2     1             0 2016-10-31/00:04:18  2016-11-01/10:28:21  01.11.2016 10:29:08         45788     64385215     49208000              10       1662051           8052      2254641      10515393       369278         128947 INVALID_UNAUTH        75854145           1556056057         242384     8 N          N          N          Y                  Y             116 N                     32        5         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)  Rolling Invalidate Window Exceeded(3)
   2     1             0 2016-10-31/00:04:18  2016-11-01/11:19:19  01.11.2016 11:19:53         45791     45845318     42385000              11       1843773           4419        13759       2565632       265091          90557 INVALID_UNAUTH        75854145           1556056057         242384    13 N          N          N          Y                  Y             116 N                     31        6         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)  Rolling Invalidate Window Exceeded(3)
...
   2     1             0 2016-10-31/00:04:18  2016-11-02/11:00:26  02.11.2016 11:01:15         45850     56198263     49618000              10       1676125           4757        30001       4185113         1348         130234 INVALID_UNAUTH       286102361           3516815164         246464     6 N          N          Y          Y                  Y             119 N                     31        5         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)                                       
   1     1             0 2016-10-31/00:24:45  2016-11-02/11:10:34  02.11.2016 11:11:11         45850     43331762     39709000              11       1661762           5524       745744       1878953       910800          80356 INVALID_UNAUTH      1258170139           4037261508         243306     1 N          N          N          Y                  Y             111 N                     31        5         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)  Auto Reoptimization Mismatch(1)      
   1     1             0 2016-10-31/00:24:45  2016-11-02/11:31:02  02.11.2016 11:31:39         45850     42839577     41318000              11       1694251           4819       240720       1173646         6358          83449 VALID               2697024553            422804854         243397     2 N          N          Y          Y                  Y             116 N                     31        5         2                       N                N                N                     N    Auto Reoptimization Mismatch(1)  Auto Reoptimization Mismatch(1)      
 
38 rows selected

-- те же данные, сгруппированные по PHV, INST_ID
--------------------------------------------------------------
SQL_ID=1g0uy9hgdw2cm Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST        EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC PLSQL_PER_EXEC FETCH_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE              SQL_PATCH                      OUTLINE_CATEGORY                                                 SQL_PROFILE
---- ------------ -------------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- ------------ -------------- -------------- ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ------------------------------ ------------------------------ ---------------------------------------------------------------- ----------------------------------------------------------------
   2            2 2016-11-01/02:31:09  01.11.2016 02:31:53     44215451     42048500              11       4682920           6056       907721       1174153        33399          88727              0         45788      2894433648           2995081746       243640          2 N          N          Y     N                                                                                                                                    
   2            1 2016-11-01/08:35:55  01.11.2016 08:36:30     40101219     38540000               9       1608245           7774      1007458        419714        40570         105182              0         45788       397641148            823461851       242292          1 N          N          Y     N                                                                                                                                    
   2            1 2016-11-01/12:51:20  01.11.2016 12:52:08     48316925     44811000               1       1787590           4429      2456765        306760       600643         171265              0         45799      3077416953           3895185959       241676          1 N          N          Y     N                                                                                                                                    
   2            8 2016-11-01/23:30:15  01.11.2016 23:30:48     49663363     44413625              11       1783764           6948      1658357       2707060       252075         113557              0         45813        75854145           1556056057       242384          8 N          N          Y     N                                                                                                                                    
   1            9 2016-11-02/00:40:52  02.11.2016 00:41:30     48523420     42846778              11       1729025           9749      3304019       1196790       619994          97246              0         45833        75854145           1556056057       242384          9 N          N          Y     N                                                                                                                                    
   2            1 2016-11-02/04:13:30  02.11.2016 04:14:05     53192879     48040000              11       1442779           5490       653275       2796302         8895         145831              0         45845      3127888737           3586703530       242231          1 N          N          Y     N                                                                                                                                    
   2            3 2016-11-02/09:59:29  02.11.2016 10:00:00     40773327     38325333              11       1558581           8812      2017651        253994       216459          86717              0         45808       532402221           1635509495       241416          3 N          N          Y     N                                                                                                                                    
   1            1 2016-11-02/10:29:53  02.11.2016 10:30:26     45033634     40020000              11       1567088          10914      3962818        613432       692611         138925              0         45849       532402221           1635509495       243197          1 N          N          Y     N                                                                                                                                    
   2            2 2016-11-02/10:40:00  02.11.2016 10:40:33     41292965     39212000              11       1602630           5133       370706       1490631         3665         102130              0         45849      1258170139           4037261508       243306          2 N          N          Y     N                                                                                                                                    
   2            7 2016-11-02/11:00:26  02.11.2016 11:01:15     48021008     45070429               9       1632777           4268       169056       1910314        67984         113736              0         45833       286102361           3516815164       245857          7 N          N          Y     N                                                                                                                                    
   1            1 2016-11-02/11:10:34  02.11.2016 11:11:11     43331762     39709000              11       1661762           5524       745744       1878953       910800          80356              0         45850      1258170139           4037261508       243306          1 N          N          Y     N                                                                                                                                    
   1            1 2016-11-02/11:31:02  02.11.2016 11:31:39     42839577     41318000              11       1694251           4819       240720       1173646         6358          83449              0         45850      2697024553            422804854       243397          1 N          N          Y     N                                                                                                                                    
   2            1 2016-10-31/21:26:35  31.10.2016 21:27:05     41363056     39858000              10       1689405           5448       588269        869401         2658          92450              0         45788      1568798363           4156351632       240666          1 N          N          Y     N                                                                                                                                    
 
-- данные V$SQLSTATS
--------------------------------------------------------------
SQL_ID=1g0uy9hgdw2cm Shared Pool GV$SQLSTATS
--------------------------------------------------------------
 
INST        EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ------------ ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2           26 1g0uy9hgdw2cm  286102361     46529417     43059538       1902862       1750477       148799      1067916               23           1502                   0                 16              65                   4158         45818           9
   1           12 1g0uy9hgdw2cm 2697024553     47326313     42222333       1707027       1203095       599143      2890455               35           4540                   0                 17              70                   4410         45837          10

-- Топ выполнений запроса по записям ASH
--------------------------------------------------------------
ASH TOP5 SQL_ID=1g0uy9hgdw2cm Executions by Elapsed Time
--------------------------------------------------------------
 
INST_ID SQL_ID        SQL_PLAN_HASH_VALUE SQL_EXEC_ID   CHILD_ID   ASH_ROWS DURATIONs                 MIN_SAMPLE_TIME           MAX_SAMPLE_TIME
------- ------------- ------------------- ----------- ---------- ---------- ------------------------- ------------------------- -------------------------
      1 1g0uy9hgdw2cm          3909913996    16777399         17          9 +000000000 00:00:19.020   02-NOV-16 07.47.18.586 AM 02-NOV-16 07.47.37.606 AM
      1 1g0uy9hgdw2cm          3909913996    16777398         17          8 +000000000 00:00:13.060   02-NOV-16 06.56.44.978 AM 02-NOV-16 06.56.58.038 AM
      1 1g0uy9hgdw2cm          3909913996    16777402         17          8 +000000000 00:00:20.020   02-NOV-16 08.28.12.816 AM 02-NOV-16 08.28.32.836 AM
      1 1g0uy9hgdw2cm           532402221    16777404          0          8 +000000000 00:00:13.010   02-NOV-16 10.30.13.609 AM 02-NOV-16 10.30.26.619 AM
      1 1g0uy9hgdw2cm          3909913996    16777401         17          7 +000000000 00:00:06.010   02-NOV-16 08.18.15.558 AM 02-NOV-16 08.18.21.568 AM

SHARED_CU12S_NOXML.SQL сгруппированные по PHV данные по запросу:

SQL> @SHARED_CU12S_NOXML 1g0uy9hgdw2cm
 
INST_ID PLAN_HASH_VALUE OPTIMIZER_MODE LAST_ACTIVE_TIME    BIND_SENSE BIND_AWARE FEEDBACK_STATS OPT_STATS REOPT ADAPT BIND_EQ_FAIL BIND_MISMATCH LANGUAGE_MISMATCH STATS_ROW_MISMATCH PQ_SLAVE_MISMATCH REASON#1                        FIX_CONTROL#1 CURSOR_COUNT  PHV_COUNT FPHV_COUNT EXECS USERS_OPENING SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
------- --------------- -------------- ------------------- ---------- ---------- -------------- --------- ----- ----- ------------ ------------- ----------------- ------------------ ----------------- ------------------------------- ------------- ------------ ---------- ---------- ----- ------------- ----------------- --------- ---------------- -----------
      1        75854145 ALL_ROWS       02.11.2016 00:41:30 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          9          1          1     9             0
      2        75854145 ALL_ROWS       01.11.2016 23:30:48 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          8          1          1     8             0
      2       286102361 ALL_ROWS       02.11.2016 11:01:15 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          7          1          1     7             0
      2       532402221 ALL_ROWS       02.11.2016 10:00:00 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          3          1          1     3             0
      2      2894433648 ALL_ROWS       01.11.2016 02:31:53 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          2          1          1     2             0
      2      1258170139 ALL_ROWS       02.11.2016 10:40:33 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          2          1          1     2             0
      2      3077416953 ALL_ROWS       01.11.2016 12:52:08 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      2      1568798363 ALL_ROWS       31.10.2016 21:27:05 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      2      3127888737 ALL_ROWS       02.11.2016 04:14:05 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      1      2697024553 ALL_ROWS       02.11.2016 11:31:39 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      1      1258170139 ALL_ROWS       02.11.2016 11:11:11 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      1       532402221 ALL_ROWS       02.11.2016 10:30:26 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0
      2       397641148 ALL_ROWS       01.11.2016 08:36:30 N          N          Y              N         Y     N     N            N             N                 N                  N                 Auto Reoptimization Mismatch(1)                          1          1          1     1             0                                       
 
13 rows selected

SID.SQL — данные текущей сессии:

11.2.0.3.@ SQL> @sid

INST_ID SID   SERIAL# SADDR            TX_START_TIME SESSION_USER CURRENT_SCHEMA SERVER    PDML  PID SPID  DEF_TS DEF_TEMP TRACEFILE
------- ----- ------- ---------------- ------------- ------------ -------------- --------- ---- ---- ----- ------ -------- ---------------------------------------------------------------------------
1       68      32825 000007FF272A16D0               SCOTT        SCOTT          DEDICATED NO     21 26272 USERS  TEMP     IGOR_W7:C:\Oracle\diag\rdbms\orcl1123\orcl1123\trace\orcl1123_ora_26272.trc

SPD_ID.SQL — вывод информации по [списку] Sql Plan Directive (SPD), например:

12.1.0.2.@ SQL> explain plan for
  2  select count(*) from (
...
 26  /

Explained.

SQL> select * from table(dbms_xplan.display('','','+metrics -predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 245120980

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |     1 |       |   277   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                    |     1 |       |            |          |       |       |
|   2 |   VIEW                             |    63 |       |   277   (1)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                     |    63 |  6531 |   277   (1)| 00:00:01 |       |       |
...
|  29 |        TABLE ACCESS BY INDEX ROWID |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  30 |       MAT_VIEW ACCESS FULL         |   506 |  4554 |    28   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

Sql Plan Directive information:
-------------------------------

  Valid directive ids:   -- список SPD
    18269311243053837209
    17375190045144571515
    7188593366307015073
    8418190807588301910
    3728388731884828861
    9285978768072890620
    6510390471537129132
    15417038795848731167
    2075460834532683041


65 rows selected.

SQL> -- содержимое списка SPD
SQL> @spd_id "18269311243053837209,17375190045144571515,7188593366307015073"
 
DIRECTIVE_ID           TYPE             ENABLED INT_STATE     STATE      REASON                                  TAB_CNT REDUNDANT TAB_COL_LIST                        EQ_PRED_ONLY  SIMPLE_COL_PRED_ONLY  IND_ACCESS_BY_JOIN_PRED  FILTER_ON_JOIN_OBJ  CREATED           LAST_MODIFIED     LAST_USED
---------------------- ---------------- ------- ------------- ---------- ------------------------------------ ---------- --------- ----------------------------------- ------------- --------------------- ------------------------ ------------------- ----------------- ----------------- -----------------
  18269311243053837209 DYNAMIC_SAMPLING YES     PERMANENT     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE          1 NO        #6 XL.INVOICESS.PAYSYS_ID           YES           YES                   YES                      NO                  27.10.15 07:25:42 27.10.15 07:55:46 31.10.16 04:22:59
  17375190045144571515 DYNAMIC_SAMPLING YES     PERMANENT     USABLE     SINGLE TABLE CARDINALITY MISESTIMATE          1 NO        #1 XL.INVOICESS.ID                  YES           YES                   YES                      NO                  27.10.15 13:10:29 27.10.15 13:25:30 26.05.16 19:32:48
   7188593366307015073 DYNAMIC_SAMPLING YES     PERMANENT     USABLE     JOIN CARDINALITY MISESTIMATE                  2 NO        #0 XL.CONSUMERZ.; #0 XL.INVOICESS.  NO            NO                    NO                       NO                  01.11.15 10:26:37 01.11.15 10:41:39 29.10.16 21:51:47

SPD_OBJ.SQL — поможет вывести информацию о Sql Plan Directive-ах для объекта бд (таблицы):

SQL> @spd_obj XL CONSUMERZ USABLE
 
DIRECTIVE_ID           SPD_TYPE         ENABLED INT_STATE     STATE      REASON                                  TAB_CNT REDUNDANT TAB_COL_LIST                                                                                         EQ_PRED_ONLY  SIMPLE_COL_PRED_ONLY  IND_ACCESS_BY_JOIN_PRED  FILTER_ON_JOIN_OBJ  CREATED             LAST_MODIFIED       LAST_USED
---------------------- ---------------- ------- ------------- ---------- ------------------------------------ ---------- --------- ---------------------------------------------------------------------------------------------------- ------------- --------------------- ------------------------ ------------------- ------------------- ------------------- -----------------
  18442540313460081144 DYNAMIC_SAMPLING YES     NEW           USABLE     JOIN CARDINALITY MISESTIMATE                  3 NO        #0 XL.TRANSACTION.; #0 XL.CONSUMERZ.; #0 XL.TAX_PERCENT_POL.                                         NO            NO                    NO                       YES                 05.07.16 18:59:17                       
  18425020049496584842 DYNAMIC_SAMPLING YES     PERMANENT     USABLE     JOIN CARDINALITY MISESTIMATE                  9 NO        #0 XL.CLIENTOS.; #0 XL.CONSUMERZ.; #0 XL.FIRMAZ.; #0 XL.INVOICESS.; #0 XL.INVOICESS_STATUS.; #0 XL.I NO            NO                    NO                       YES                 23.08.16 18:52:19   25.08.16 16:26:57   25.08.16 16:26:57
                                                                                                                                      NVOICESS_TEMPLATE.; ...                                                                                                                                                     
...

668 rows selected -- , которых может быть много

SPM_CHECK4SQL_ID.SQL — проверка SQL_ID на наличие в бд элементов Sql Plan Management(SQL Patch, SQL Profile, SQL Plan Baseline):

SQL> @spm_check4sql_id 9fgs3y97wc9cz
 
SIGNATURE             SPM_TYPE          SQL_HANDLE            PATCH_NAME                     ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED       LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
--------------------- ----------------- --------------------- ------------------------------ -------------- ---------- ------------------- ------------------- ------------------- ------------- ------- -------- ----- ---------- ---------
 16477148273854161214 SQL Patch         SQL_e4aa970e2e84a13e  NO_ADAPT_9fgs3y97wc9cz         MANUAL-SQLTUNE 12.1.0.2.0 25.04.2016 18:29:45 25.04.2016 18:29:45                                   YES     NO       NO    YES        NO
 16477148273854161214 SQL Plan Baseline SQL_e4aa970e2e84a13e  SQL_PLAN_f9anr1sr8989ya0b5f8b0 MANUAL-LOAD    12.1.0.2.0 25.04.2016 18:31:50 30.10.2016 22:14:45 30.10.2016 22:14:45               YES     YES      YES   YES        NO

SQL_PLAN_DEP3.SQL — зависимости плана выполнения запроса от объектов бд
Если TYPE = PLAN — прямое использование таблицы/индекса в плане, иначе — коственное, через DBA_DEPENDENCIES, например:

SQL> @sql_plan_dep3 4twn1z5sqfrrm 3938539332
 
LEVEL DEP_TREE                                       DEPENDS_ON   TYPE REFERENCED                                 LAST_MODIFIED     LAST_DDL
----- ---------------------------------------------- ------------ ---- ------------------------------------------ ----------------- -----------------
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN TABLE XL.CONTRASTOS_ATTRIBUTES             31.10.16 14:49:56 30.06.16 17:16:21
...
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN TABLE XL.PERSONAS                          31.10.16 14:49:56 28.07.16 20:50:42
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN INDEX XL.PERSONAS_PK                                         11.07.08 15:42:19
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN TABLE XL.MV_YE_CONTRAST                    31.10.16 14:38:45 26.09.16 23:02:24
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN MATERIALIZED VIEW XL.MV_YE_CONTRAST        31.10.16 14:38:45 31.10.16 15:01:06
    0 SQL_ID = '4twn1z5sqfrrm' ; PHV = 3938539332                 PLAN VIEW XL.V_CONTRAST_PAINTED_COL                               30.06.16 17:17:04
...
    1   MATERIALIZED VIEW XL.MV_YE_CONTRAST          >            HARD FUNCTION XL.SF_NULLIF                                                          
    2     FUNCTION XL.SF_NULLIF                      >>           HARD PACKAGE SYS.STANDARD                                                           
    1   MATERIALIZED VIEW XL.MV_YE_CONTRAST          >            HARD FUNCTION SYS.SYS_IXMLAGG                                                       
    2     FUNCTION SYS.SYS_IXMLAGG                   >>           HARD PACKAGE SYS.SYS_STUB_FOR_PURITY_ANALYSIS                                       
...
    1   VIEW XL.V_CONTRAST_PAINTED_COL               >            HARD TABLE XL.CONTRASTOS2                       31.10.16 14:49:56 
    1   VIEW XL.V_CONTRAST_PAINTED_COL               >            HARD VIEW XL.V_CONTRAST_PAINTED_COL2                              
    2     VIEW XL.V_CONTRAST_PAINTED_COL2            >>           HARD VIEW XL.V_CONTRAST_PAINTED_COL_HISTORY                       
    3       VIEW XL.V_CONTRAST_PAINTED_COL_HISTORY   >>>          HARD SYNONYM PUBLIC.DBMS_LOB                                      
    4         SYNONYM PUBLIC.DBMS_LOB                >>>>         HARD PACKAGE SYS.DBMS_LOB                                         
    5           PACKAGE SYS.DBMS_LOB                 >>>>>        HARD PACKAGE SYS.STANDARD                                         
    5           PACKAGE SYS.DBMS_LOB                 >>>>>        HARD PACKAGE SYS.SYS_STUB_FOR_PURITY_ANALYSIS                     
    5           PACKAGE SYS.DBMS_LOB                 >>>>>        HARD PACKAGE SYS.UTL_IDENT                                        
    6             PACKAGE SYS.UTL_IDENT              >>>>>>       HARD PACKAGE SYS.STANDARD                                         
    3       VIEW XL.V_CONTRAST_PAINTED_COL_HISTORY   >>>          HARD TABLE XL.CONTRASTOS_ATTRIBUTES             31.10.16 14:49:56 
    3       VIEW XL.V_CONTRAST_PAINTED_COL_HISTORY   >>>          HARD TABLE XL.CONTRASTOS_COLLATERAL             31.10.16 14:49:56 
    3       VIEW XL.V_CONTRAST_PAINTED_COL_HISTORY   >>>          HARD TABLE XL.CONTRASTOS_COLLATERAL_TYPES                         
 
71 rows selected

SQL_PLAN_DIFF.SQL12c: Сравнение планов выполнения…

SQL_PLAN_HASHS.SQL — планы выполнения запроса по SQL_ID из AWR и SQL[area]:

SQL> -- перечень планов для SQL_ID = f43rvxnfry6nk
SQL> @sql_plan_hashs f43rvxnfry6nk
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR f43rvxnfry6nk             34803466       233198552 01.11.2016 00:12:46
AWR f43rvxnfry6nk             34803466       700459191 01.10.2016 12:23:52
AWR f43rvxnfry6nk             34803466       863432812 01.10.2016 00:05:35
AWR f43rvxnfry6nk             73568689       316108412 01.10.2016 20:07:33
AWR f43rvxnfry6nk            412414461      3172244480 01.11.2016 14:46:32
...
SQL f43rvxnfry6nk           4255269297      2250628550 01.11.2016 16:31:48
 
79 rows selected
 
SQL> -- по SQL_ID и FULL_PLAN_HASH_VALUE = 34803466
SQL> @sql_plan_hashs f43rvxnfry6nk 34803466
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR f43rvxnfry6nk             34803466       233198552 01.11.2016 00:12:46
AWR f43rvxnfry6nk             34803466       700459191 01.10.2016 12:23:52
AWR f43rvxnfry6nk             34803466       863432812 01.10.2016 00:05:35

SQL> -- по SQL_ID и PLAN_HASH_VALUE = 3887377781
SQL> @sql_plan_hashs fkw6hb5mrw02t "" 3887377781
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
SQL fkw6hb5mrw02t           1266372674      3887377781 03.11.2016 11:11:22
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

SQL_PROFILE_FROM_SQL.SQL — создание SQL Profile для SQL_ID с указанным PHV из AWR или Shared Pool

SQL_PROFILE_FROM_SQL2.SQL — создание SQL Profile для запроса с планом (PHV) другого запроса ( с отличным SQL_ID) из AWR или Shared Pool

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

SQLPATCH-.SQL — удаление SPM SQL Patch

SQLPATCH_12C.SQLSQL Patch для решения стандартных проблем версии 12c

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

SQL> select sql_patch from v$sql where sql_patch is not null and rownum <= 1;
 
SQL_PATCH
------------------------------
NO_ADAPT_4xu0dm5t4w6k4
 
SQL> @sqlpatch_hints NO_ADAPT_4xu0dm5t4w6k4
 
SQL_PATCH_HINTS
--------------------------------------------------------------------------------------------------------
opt_param('optimizer_adaptive_features' 'false') no_bind_aware OPT_PARAM('optimizer_dynamic_sampling' 0)

SQLPATCH+.SQL — Создание SQL Patch для SQL_ID с ручным указанием подсказок:

SQL> @sqlpatch+
&SQL_ID: 5rrd8z9nt7t2j
&SQL_PATCH_TEXT: OPT_PARAM(''optimizer_dynamic_sampling'' 0)
&SQL_PATCH_NAME: 5rrd8z9nt7t2j_NO_ADS
 
PL/SQL procedure successfully completed
 
SQL_PATCH_HINTS
-----------------------------------------
OPT_PARAM('optimizer_dynamic_sampling' 0)

TEMP_TOP_ASH_HIST.SQL — топ N выполнений запросов по потреблению TEMP за последние M дней, например — ТОП-10 за последние 10 дней:

SQL> @temp_top_ash_hist 10 10
 
INST_ID SQL_ID        SQL_EXEC_START      DURATION            SQL_EXEC_ID SQL_PLAN_HASH_VALUE MODULE     ACTION   MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED
------- ------------- ------------------- ------------------- ----------- ------------------- ---------- -------- ----------- ---------- -----------
      2 2qzfpcc179qfc 07.11.2016 18:34:06 +000000000 09:59:44    33554432          1624394070 e:INV:cp:  INV/RU_      223,729      0,614           0
      2 fxgzfhx4fr9rv 09.11.2016 17:55:08 +000000000 00:34:45    33554441          3206141728 e:PER:cp:  PER/HR_       73,624      0,155           0
      1 46pntf019mztq 01.11.2016 16:45:44 +000000000 01:02:12    16777224          3452198171 e:AR:frm:  AR/RU_        42,126      1,272           0
      2 46pntf019mztq 01.11.2016 16:48:39 +000000000 01:07:34    33554433          3452198171 e:AR:frm:  AR/RU_        41,844      2,057           0
      1 46pntf019mztq 31.10.2016 13:51:53 +000000000 01:16:17    16777223          3452198171 e:AR:frm:  AR/RU_        41,589      2,078           0
      1 46pntf019mztq 07.11.2016 14:27:37 +000000000 01:00:58    16777216          3452198171 e:AR:bes:  AR/RU_        39,619      1,061           0
      2 8ny76sfxnkpr9 02.11.2016 13:43:53 +000000000 01:00:10    33554507          2233057940 e:PER:cp:  PER/HR_       27,253       0,72           0
      2 87tw4bmw1qv48 08.11.2016 19:02:08 +000000000 01:57:25    33554433           351383920 e:PER:cp:  PER/HR_       26,457      0,032           0
      2 8ny76sfxnkpr9 08.11.2016 16:35:22 +000000000 01:01:49    33554524          2233057940 e:PER:cp:  PER/HR_       25,566      0,513           0
      2 8ny76sfxnkpr9 08.11.2016 17:04:36 +000000000 00:45:57    33554525          2233057940 e:PER:cp:  PER/HR_       25,566       0,25           0

— показывает запросы/модули/действия вплоть до SQL_EXEC_ID и SQL_EXEC_START

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

Комментариев нет.

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 такие блоггеры, как: