CREATE TABLE T_OFFSET
AS
    SELECT A.LV, ROWNUM AS RID,  OBJECT_TYPE, OBJECT_NAME 
FROM DBA_OBJECTS , (
           SELECT LEVEL LV 
   FROM DUAL 
   CONNECT BY LEVEL <=10000
)A ;

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'T_OFFSET', CASCADE => TRUE, ESTIMATE_PERCENT =>'10', DEGREE => '1', granularity => 'ALL');


select count(*) from t_offset;  

T_OFFSET
--------
Rows=544,377,340                        Blocks=3,530,057
Empty_Blocks=0                          Avg_Space=0
Chain_Count=0                           Avg_Row_Length=41
Avg_Space_Freelist_Blocks=0             Freelist_Blocks=0
Sample_Size=54,437,734                  Last_Analyzed='2020-04-01 11:02:23'
Partitioned='NO'                        Tablespace='USERS'


  Column Name                      Nullable Column Type           Distinct    Buckets
  -------------------------------- -------- ------------------- ---------- ----------
  LV                                        NUMBER                  10,000          1
  RID                                       NUMBER              544,377,340          1
  OBJECT_TYPE                               VARCHAR2(23)                41          1
  OBJECT_NAME                               VARCHAR2(128)           24,006          1

  INDEX
  -----------------------------------------------------------------------------
  'No Index'

SELECT *
FROM   (SELECT rid, rownum AS rnum
        FROM   (SELECT *
                FROM   T_OFFSET
                ORDER BY rid)
        WHERE rownum <= 100
        )
WHERE  rnum >= 90;



SQL_ID  ca44dx44qrq6s, child number 0
-------------------------------------
SELECT * FROM   (SELECT rid, rownum AS rnum         FROM   (SELECT *    
             FROM   T_OFFSET                 ORDER BY rid)         
WHERE rownum <= 100         ) WHERE  rnum >= 90
 
Plan hash value: 1388389564
 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |       |       |  6733K(100)|          |     11 |00:01:58.32 |    3526K|   3526K|       |       |          |
|*  1 |  VIEW                    |          |      1 |    100 |  2600 |       |  6733K  (1)| 00:04:24 |     11 |00:01:58.32 |    3526K|   3526K|       |       |          |
|*  2 |   COUNT STOPKEY          |          |      1 |        |       |       |            |          |    100 |00:01:58.32 |    3526K|   3526K|       |       |          |
|   3 |    VIEW                  |          |      1 |    544M|  6749M|       |  6733K  (1)| 00:04:24 |    100 |00:01:58.32 |    3526K|   3526K|       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|          |      1 |    544M|    20G|    28G|  6733K  (1)| 00:04:24 |    100 |00:01:58.32 |    3526K|   3526K|  6144 |  6144 | 6144  (0)|
|   5 |      TABLE ACCESS FULL   | T_OFFSET |      1 |    544M|    20G|       |   959K  (1)| 00:00:38 |    544M|00:01:12.09 |    3526K|   3526K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$3 / from$_subquery$_002@SEL$2
   4 - SEL$3
   5 - SEL$3 / T_OFFSET@SEL$3
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RNUM">=90)
   2 - filter(ROWNUM<=100)
   4 - filter(ROWNUM<=100)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "from$_subquery$_001"."RID"[NUMBER,22], "RNUM"[NUMBER,22]
   2 - "RID"[NUMBER,22], ROWNUM[8]
   3 - "RID"[NUMBER,22]
   4 - (#keys=1) "T_OFFSET"."RID"[NUMBER,22]
   5 - "T_OFFSET"."RID"[NUMBER,22]
 
또는 아래 처럼 찾아 보기도 합니다.

selecT SQL_ID , SQL_TEXT, SQL_FULLTEXT from V$SQLAREA WHERE SQL_FULLTEXT LIKE '%T_OFFSET%'; 

SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS  FROM DBA_ADVISOR_LOG ;

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                          sql_id      => '7r036z8114hd8',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 1800,
                          task_name   => '7r036z8114hd8_tuning_task',
                          description => 'Tuning task for statement 7r036z8114hd8.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'ca44dx44qrq6s_tuning_task');

-- 반드시 sqlplus 상에서 확인 하셔야 합니다. 일부 툴 사용시에는 제공을 해 주는 경우도 있습니다.

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
col  RECOMMENT FORMAT a350
SELECT DBMS_SQLTUNE.report_tuning_task('ca44dx44qrq6s_tuning_task') AS RECOMMENT FROM dual;
SET PAGESIZE 24

sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on 화 3월 31 16:27:49 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

사용자명 입력: xxxxx/"xxxxx!@"@xxx
마지막 성공한 로그인 시간: 화 3월  31 2020 16:21:12 +09:00

다음에 접속됨:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SET LONG 10000;
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 200
SQL> col  RECOMMENT FORMAT a350
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('ca44dx44qrq6s_tuning_task') AS RECOMMENT FROM dual;

RECOMMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ca44dx44qrq6s_tuning_task
Tuning Task Owner  : OWNER명
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/09/2020 11:13:41
Completed at       : 04/09/2020 11:13:42

-------------------------------------------------------------------------------
Schema Name: OWNER명
SQL ID     : ca44dx44qrq6s
SQL Text   : SELECT *
             FROM   (SELECT rid, rownum AS rnum
                     FROM   (SELECT *
                             FROM   T_OFFSET
                             ORDER BY rid)
                     WHERE rownum <= 100
                     )
             WHERE  rnum >= 90

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  이 명령문에 대해 잠재적으로 더 나은 실행 계획이 발견되었습니다.

  Recommendation (estimated benefit: 93.05%)
  ------------------------------------------
  - 이 명령문에 병렬 실행을 사용하려면 권장 SQL 프로파일을 그대로 적용하는 것이 좋습니다.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'ca44dx44qrq6s_tuning_task', task_owner => 'owner명',
            replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

  DOP 16을(를) 사용하여 이 병렬 질의를 실행하면 원래 계획에 대한 응답 시간 93.06%이(가) 향상됩니다. 단, 병렬 실행을
  사용으로 설정하면 명령문의 리소스 소비가 11.06%까지 증가하여 시스템 처리량이 줄어들 수 있습니다. 또한 훨씬 짧은 기간에 리소스가
  소비되므로 사용 가능한 하드웨어 용량이 부족할 경우 동시 명령문의 응답 시간에 부정적인 영향을 줄 수 있습니다.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 최적기는 실행 계획의 행 ID 3에서 뷰를 병합할 수 없었습니다. 명령문이 "DELETE" 또는 "UPDATE"이고 상위 질의가
  명령문에서 최상위 질의가 아닌 경우 최적기는 "ORDER BY" 절을 포함하는 뷰를 병합할 수 없습니다.
- 최적기는 실행 계획의 행 ID 1에서 뷰를 병합할 수 없었습니다. 최적기는 "ROWNUM" 의사 열을 포함하는 뷰를 병합할 수 없습니다.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1388389564

---------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   100 |  2600 |       |  6733K  (1)| 00:04:24 |
|*  1 |  VIEW                    |          |   100 |  2600 |       |  6733K  (1)| 00:04:24 |
|*  2 |   COUNT STOPKEY          |          |       |       |       |            |          |
|   3 |    VIEW                  |          |   544M|  6749M|       |  6733K  (1)| 00:04:24 |
|*  4 |     SORT ORDER BY STOPKEY|          |   544M|    20G|    28G|  6733K  (1)| 00:04:24 |
|   5 |      TABLE ACCESS FULL   | T_OFFSET |   544M|    20G|       |   959K  (1)| 00:00:38 |
---------------------------------------------------------------------------------------------

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

   1 - filter("RNUM">=90)
   2 - filter(ROWNUM<=100)
   4 - filter(ROWNUM<=100)

2- Using Parallel Execution
---------------------------
Plan hash value: 162111451

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |   100 |  2600 |       |   467K  (1)| 00:00:19 |        |      |            |
|*  1 |  VIEW                         |          |   100 |  2600 |       |   467K  (1)| 00:00:19 |        |      |            |
|*  2 |   COUNT STOPKEY               |          |       |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR             |          |       |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (ORDER)        | :TQ10001 |   544M|  6749M|       |   467K  (1)| 00:00:19 |  Q1,01 | P->S | QC (ORDER) |
|   5 |      VIEW                     |          |   544M|  6749M|       |   467K  (1)| 00:00:19 |  Q1,01 | PCWP |            |
|*  6 |       SORT ORDER BY STOPKEY   |          |   544M|    20G|    28G|   467K  (1)| 00:00:19 |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE             |          |   100 |  1300 |       |            |          |  Q1,01 | PCWP |            |
|   8 |         PX SEND RANGE         | :TQ10000 |   100 |  1300 |       |            |          |  Q1,00 | P->P | RANGE      |
|*  9 |          SORT ORDER BY STOPKEY|          |   100 |  1300 |       |            |          |  Q1,00 | PCWP |            |
|  10 |           PX BLOCK ITERATOR   |          |   544M|    20G|       | 66590   (1)| 00:00:03 |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS FULL  | T_OFFSET |   544M|    20G|       | 66590   (1)| 00:00:03 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RNUM">=90)
   2 - filter(ROWNUM<=100)
   6 - filter(ROWNUM<=100)
   9 - filter(ROWNUM<=100)

-------------------------------------------------------------------------------



SQL> SET PAGESIZE 24
SQL>

## 삭제전 내용 확인
SELECT OWNER, TASK_NAME, EXECUTION_START, EXECUTION_END, STATUS  FROM DBA_ADVISOR_LOG WHERE OWNER='owner명';

## 삭제 하기 
BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'ca44dx44qrq6s_tuning_task');
END;
/