수행 시간별 SQL 수집이 잘 되는지 테스트를 하기 위해서 만들었던 패키지 입니다.
각각 0.05/0.01/0.1/1초 쿼리를 입력한 횟수만큼 수행하는 패키지 입니다.
Scott 유저의 스키마를 가지고 만들었고, 필요에 따라서 emp 테이블의 데이터 건수를 늘려 주셔야 합니다.
1. 패키지 생성
|
패키지 생성 |
|
CREATE OR REPLACE PACKAGE DATA_VALIDATION AS -- This procedure execute query that query finished 0.001 sec. -- I_COMMENTS is query hint that used when I will find query. -- I_EXECUTION is count of SQL executions. -- This package was made by yj.choi on September 21.
PROCEDURE E_001 (i_comments IN varchar2 , i_executions IN Number) ; PROCEDURE E_005 (i_comments IN varchar2 , i_executions IN Number) ; PROCEDURE E_01 (i_comments IN varchar2 , i_executions IN Number) ; PROCEDURE E_1 (i_comments IN varchar2 , i_executions IN Number) ; END ; /
CREATE OR REPLACE PACKAGE BODY DATA_VALIDATION AS -- This procedure execute query that query finished 0.001 sec. -- I_COMMENTS is query hint that used when I will find query. -- I_EXECUTION is count of SQL executions. -- This package was made by yj.choi on September 21.
PROCEDURE E_001 (i_comments IN varchar2 , i_executions IN Number) IS
-- ----------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | -- ----------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1 |00:00:00.01 | 1 | 1 | -- | 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 1 | 1 | -- | 2 | INDEX FULL SCAN| PK_EMP | 1 | 14 | 1 (0)| 14 |00:00:00.01 | 1 | 1 | -- -----------------------------------------------------------------------------------------------------------
v_result number := 0 ; v_sql varchar2(3000) := '' ;
BEGIN
v_sql := 'SELECT /*+ Elapsed Time: 0.01 Executions: ' || i_executions || ' ' || i_comments || ' */ count(*) FROM emp ' ;
FOR i IN 1..i_executions LOOP EXECUTE IMMEDIATE v_sql INTO v_result ; END LOOP ;
END ;
PROCEDURE E_005 (i_comments IN varchar2 , i_executions IN Number) IS
-- -------------------------------------------------------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -- -------------------------------------------------------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 1 | | | 10 (100)| 1 |00:00:00.05 | 4 | | | | -- | 1 | SORT AGGREGATE | | 1 | 1 | 14 | | 1 |00:00:00.05 | 4 | | | | -- | 2 | MERGE JOIN | | 1 | 42 | 588 | 10 (20)| 25200 |00:00:00.05 | 4 | | | | -- | 3 | SORT JOIN | | 1 | 5 | 50 | 6 (17)| 9000 |00:00:00.01 | 2 | 337K| 337K| 299K (0)| -- | 4 | MERGE JOIN CARTESIAN | | 1 | 5 | 50 | 5 (0)| 9000 |00:00:00.01 | 2 | | | | -- | 5 | VIEW | | 1 | 1 | | 2 (0)| 1800 |00:00:00.01 | 0 | | | | -- | 6 | CONNECT BY WITHOUT FILTERING| | 1 | | | | 1800 |00:00:00.01 | 0 | | | | -- | 7 | FAST DUAL | | 1 | 1 | | 2 (0)| 1 |00:00:00.01 | 0 | | | | -- | 8 | BUFFER SORT | | 1800 | 5 | 50 | 5 (0)| 9000 |00:00:00.01 | 2 | 73728 | 73728 | | -- | 9 | TABLE ACCESS FULL | SALGRADE | 1 | 5 | 50 | 3 (0)| 5 |00:00:00.01 | 2 | | | | -- |* 10 | FILTER | | 9000 | | | | 25200 |00:00:00.02 | 2 | | | | -- |* 11 | SORT JOIN | | 9000 | 14 | 56 | 4 (25)| 72000 |00:00:00.02 | 2 | 73728 | 73728 | | -- | 12 | TABLE ACCESS FULL | EMP | 1 | 14 | 56 | 3 (0)| 14 |00:00:00.01 | 2 | | | | -- --------------------------------------------------------------------------------------------------------------------------------------------------------
v_result number := 0 ; v_sql varchar2(3000) := '' ;
BEGIN
v_sql := 'SELECT /*+ Elapsed Time: 0.05 Executions: ' || i_executions || ' ' || i_comments || ' */ max(grade) FROM ( SELECT * FROM emp e , (SELECT level FROM dual CONNECT BY level <= 1800) t ) a , salgrade s WHERE a.sal BETWEEN s.losal AND s.hisal' ;
FOR i IN 1..i_executions LOOP EXECUTE IMMEDIATE v_sql INTO v_result ; END LOOP ;
END ;
PROCEDURE E_01 (i_comments IN varchar2 , i_executions IN Number) IS
-- --------------------------------------------------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -- --------------------------------------------------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| 1 |00:00:00.10 | 4 | | | | -- | 1 | SORT AGGREGATE | | 1 | 1 | 14 | | 1 |00:00:00.10 | 4 | | | | -- |* 2 | HASH JOIN | | 1 | 8 | 112 | 8 (0)| 170K|00:00:00.10 | 4 | 3252K| 3252K| 3474K (0)| -- | 3 | MERGE JOIN CARTESIAN | | 1 | 2 | 22 | 5 (0)| 34000 |00:00:00.07 | 2 | | | | -- | 4 | VIEW | | 1 | 1 | | 2 (0)| 34000 |00:00:00.03 | 0 | | | | -- | 5 | CONNECT BY WITHOUT FILTERING| | 1 | | | | 34000 |00:00:00.02 | 0 | | | | -- | 6 | FAST DUAL | | 1 | 1 | | 2 (0)| 1 |00:00:00.01 | 0 | | | | -- | 7 | BUFFER SORT | | 34000 | 2 | 22 | 5 (0)| 34000 |00:00:00.02 | 2 | 73728 | 73728 | | -- |* 8 | TABLE ACCESS FULL | DEPT | 1 | 2 | 22 | 3 (0)| 1 |00:00:00.01 | 2 | | | | -- | 9 | TABLE ACCESS FULL | EMP | 1 | 14 | 42 | 3 (0)| 14 |00:00:00.01 | 2 | | | | -- ---------------------------------------------------------------------------------------------------------------------------------------------------
v_result number := 0 ; v_sql varchar2(3000) := '' ;
BEGIN
v_sql := 'SELECT /*+ Elapsed Time: 0.1 Executions: ' || i_executions || ' ' || i_comments || ' */ count(*) FROM ( SELECT * FROM emp e , (SELECT level FROM dual CONNECT BY level <= 34000) t ) a , dept d WHERE a.deptno = d.deptno AND ( d.loc LIKE ''D%'' OR d.loc LIKE ''N*'') ' ;
FOR i IN 1..i_executions LOOP EXECUTE IMMEDIATE v_sql INTO v_result ; END LOOP ;
END ;
PROCEDURE E_1 (i_comments IN varchar2 , i_executions IN Number) IS
-- --------------------------------------------------------------------------------------------------------------------------- -- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | -- --------------------------------------------------------------------------------------------------------------------------- -- | 0 | SELECT STATEMENT | | 1 | | | 5 (100)| 1 |00:00:00.99 | 2 | -- | 1 | SORT AGGREGATE | | 1 | 1 | 23 | | 1 |00:00:00.99 | 2 | -- | 2 | NESTED LOOPS | | 1 | 1 | 23 | 5 (0)| 84096 |00:00:00.97 | 2 | -- |* 3 | TABLE ACCESS FULL | SALGRADE | 1 | 1 | 10 | 3 (0)| 1 |00:00:00.01 | 2 | -- |* 4 | VIEW | | 1 | 1 | 13 | 2 (0)| 84096 |00:00:00.95 | 0 | -- | 5 | CONNECT BY WITHOUT FILTERING| | 1 | | | | 120K|00:00:00.09 | 0 | -- | 6 | FAST DUAL | | 1 | 1 | | 2 (0)| 1 |00:00:00.01 | 0 | -- ---------------------------------------------------------------------------------------------------------------------------
v_result number := 0 ; v_sql varchar2(3000) := '' ;
BEGIN
v_sql := 'SELECT /*+ Elapsed Time: 1 Executions: ' || i_executions || ' ' || i_comments || ' */ max(sal) FROM (SELECT sal FROM ( SELECT dbms_random.value(1,9999) sal FROM dual CONNECT BY level <= 120024) e , salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal AND grade = 5) a ' ;
FOR i IN 1..i_executions LOOP EXECUTE IMMEDIATE v_sql INTO v_result ; END LOOP ;
END ;
END; / |
2. 사용
|
수행
SQL> exec DATA_VALIDATION.E_1('TEST' , 10) ; PL/SQL procedure successfully completed.
확인
SQL> col sql_text format a100 SQL> set line 200 SQL> SELECT sql_text , loads , fetches , executions , disk_reads , buffer_gets , elapsed_time , elapsed_time/executions/100000 sec FROM v$sql WHERE sql_text like '%TEST%' ;
SQL_TEXT LOADS FETCHES EXECUTIONS DISK_READS BUFFER_GETS ELAPSED_TIME SEC ------------------------------------------------------------------------- ------- ------- ---------- ----------- ---------- ------------ ---- SELECT /*+ Elapsed Time: 1 Executions: 10 TEST */ max(sal) 1 10 10 23 343 2513012 2.51 FROM (SELECT sal FROM ( SELECT dbms_random.value(1,9999) sal FROM dual CONNECT BY level <= 120024) e , salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal AND grade = 5) a
Elapsed: 00:00:00.01 |