꿈꾸는 개발자, DBA 커뮤니티 구루비

동적 성능 뷰

4개의 성능 뷰를 통해 라이브러리 캐시의 커서에 대한 정보를 살펴볼 수 있다.

V$SQL_PLAN은 기본적으로 PLAN 테이블과 동일한 정보를 제공한다.
V$SQL_PLAN_STATISTICS는 V$SQL_PLAN 뷰의 각 오퍼레이션에 대한 경과시간 및 생성된 로우의 개수 등의 실행 통계를 제공한다.
V$SQL_WORKARE는 커서를 실행하는데 필요한 메모리 작업 영역에 대한 정보를 제공한다.
V$SQL_PLAN_STATISTICS_ALL은 V$SQL_PLAN, V$SQL_PLAN_STATISTICS, V$SQL_WORKAREA 뷰에서 제공하는 모든 정보를 한 번에 볼 수 있게 해준다.

지식 커서 식별하기


SQL> SELECT status, sql_id, sql_child_number
  2  FROM v$session
  3  WHERE username = 'CURTIS';

STATUS  SQL_ID        SQL_CHILD_NUMBER
------- ------------- ---------------
ACTIVE  1scu79x31qavt                1


SQL> SELECT sql_id, child_number, sql_text
  2  FROM v$sql
  3  WHERE sql_fulltext LIKE '%online discount%'
  4  AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------
1hqjydsjbvmwq            0 SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P
                           WHERE S.PROMO_ID = P.PROMO_ID AND PROMO_SUBCATEGORY
                           = 'online discount'

동적 성능 뷰 조회하기


SQL> SELECT * FROM table(dbms_xplan.display_cursor('1hqjydsjbvmwq', 0));

PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------
SQL_ID  1hqjydsjbvmwq, child number 0 ------------------------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID =
P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'

Plan hash value: 265338492

-----------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE       |            |     1 |    30 |            |          |
|*  2 |   HASH JOIN           |            |   913K|    26M|   139  (33)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | PROMOTIONS |    23 |   483 |     4   (0)| 00:00:01 |
|   4 |    PARTITION RANGE ALL|            |   918K|  8075K|   123  (27)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | SALES      |   918K|  8075K|   123  (27)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------

   2 - access("S"."PROMO_ID"="P"."PROMO_ID")
   3 - filter("PROMO_SUBCATEGORY"='online discount')

AWR과 Statpack


SQL> SELECT * FROM table(dbms_xplan.display_awr('1hqjydsjbvmwq'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 1hqjydsjbvmwq
-------------------
SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID
= P.PROMO_ID AND PROMO_SUBCATEGORY = 'online discount'

Plan hash value: 265338492

-----------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE       |            |     1 |    30 |            |          |
|   2 |   HASH JOIN           |            |   913K|    26M|   139  (33)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | PROMOTIONS |    23 |   483 |     4   (0)| 00:00:01 |
|   4 |    PARTITION RANGE ALL|            |   918K|  8075K|   123  (27)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | SALES      |   918K|  8075K|   123  (27)| 00:00:01 |
------------------------------------------------------------------------------------


SQL ID: 1hqjydsjbvmwq               DB/Inst: DBM11203/DBM11203  Snaps: 576-577
  -> 1st Capture and Last Capture Snap IDs
     refer to Snapshot IDs witin the snapshot range
     -> SELECT SUM(AMOUNT_SOLD) FROM SALES S, PROMOTIONS P WHERE S.PROMO_ID = ...

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- -------------
1   2446651477                 16,577             2           577            577
2   265338492                  14,736             4           577            577
--- ---------------------------------------------------------------------------

Plan 1(PHV: 2446651477)
----------------------

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- ------
Elapsed Time (ms)                            16,577        8,288.6    50.2
CPU Time (ms)                                16,071        8,035.3    50.9
Executions                                        2            N/A     N/A
Buffer Gets                                 163,606       81,803.0    90.1
Disk Reads                                  161,900       80,950.0    96.0
Parse Calls                                       2            1.0     1.0
Rows                                              2            1.0     N/A
-------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |       |  2798 (100)|          |
|   1 |  SORT AGGREGATE       |            |     1 |    30 |            |          |
|   2 |   NESTED LOOPS        |            |   913K|    26M|  2798  (27)| 00:00:12 |
|   3 |    TABLE ACCESS FULL  | PROMOTIONS |    23 |   483 |     4   (0)| 00:00:01 |
|   4 |    PARTITION RANGE ALL|            | 39950 |   351K|   121  (27)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | SALES      | 39950 |   351K|   121  (27)| 00:00:01 |
-----------------------------------------------------------------------------------

Plan 2(PHV: 265338492)
---------------------

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- ------
Elapsed Time (ms)                            14,736        3,684.0    44.6
CPU Time (ms)                                14,565        3,641.2    46.1
Executions                                        4            N/A     N/A
Buffer Gets                                   6,755        1,688.8     3.7
Disk Reads                                    6,485        1,621.3     3.8
Parse Calls                                       1            0.3     0.5
Rows                                              4            1.0     N/A
-------------------------------------------------------------------------

 -----------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE       |            |     1 |    30 |            |          |
|   2 |   HASH JOIN           |            |   913K|    26M|   139  (33)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | PROMOTIONS |    23 |   483 |     4   (0)| 00:00:01 |
|   4 |    PARTITION RANGE ALL|            |   918K|  8075K|   123  (27)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | SALES      |   918K|  8075K|   123  (27)| 00:00:01 |
 -----------------------------------------------------------------------------------