drop user testuser cascade;
    drop tablespace test1 including contents and datafiles;
    drop tablespace indtbs including contents and datafiles;
    create tablespace test1 datafile '/tmp/test1.dbf' size 200M;
    create user testuser identified by testuser default tablespace test1;
    grant resource , connect to testuser;
    @?/sqlplus/admin/plustrce
    grant plustrace to testuser;
    grant select on DBA_SQL_PLAN_BASELINES to testuser;
    conn testuser/testuser
    DROP TABLE TEST1 PURGE;
    DROP TABLE TEST2 PURGE;       
    create table test1 (id number ,col1 date ,col2 number ,col3 number ,col4 varchar(20), col5 varchar(30));
    create table test2 (id number ,col1 date ,col2 number ,col3 number ,col4 varchar(20), col5 varchar(30));
    declare
      a integer;
      pk integer;
    begin
      for i in 1..10000 loop
        for j in 1..10 loop
        a :=  j + 1000;
        pk := i * 10 + j;
           insert into test1 values(pk ,sysdate ,j ,a ,'HAHAHAHA test1' ,'HAHAHAHA test1');
           insert into test2 values(pk ,sysdate ,j ,a ,'HAHAHAHA test2' ,'HAHAHAHA test2');
        end loop;
      commit;
      end loop;
    end;
    /
    # SQL Statement 
        SELECT      test1.col2, SUM(test1.col3)
        FROM        test1 ,test2
        WHERE       test1.id = test2.id
          AND       test2.col2 IN (1,2,4,6,3,5,7)
          GROUP BY  test1.col2
        /
        SELECT      test1.id, test2.col4
        FROM        test1 ,test2
        WHERE       test1.id = test2.id
          AND       test2.col2 IN (1,3,5,7)
        /
## sys
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
## testuser    
set autotrace traceonly
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;
SQL> set autotrace traceonly
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;
/SQL> SQL>   2    3    4    5    6  
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1994637339
-------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       | 64288 |  4080K|       |   827   (1)| 00:00:10 |
|   1 |  HASH GROUP BY      |       | 64288 |  4080K|       |   827   (1)| 00:00:10 |
|*  2 |   HASH JOIN         |       | 64288 |  4080K|  2392K|   824   (1)| 00:00:10 |
|*  3 |    TABLE ACCESS FULL| TEST2 | 64288 |  1632K|       |   239   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| TEST1 | 95391 |  3633K|       |   239   (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST1"."ID"="TEST2"."ID")
   3 - filter("TEST2"."COL2"=1 OR "TEST2"."COL2"=2 OR "TEST2"."COL2"=3 OR
              "TEST2"."COL2"=4 OR "TEST2"."COL2"=5 OR "TEST2"."COL2"=6 OR "TEST2"."COL2"=7)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        104  recursive calls
          0  db block gets
       1805  consistent gets
       1615  physical reads
          0  redo size
        721  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          7  rows processed
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;
## 
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;
 SQL> SELECT    SIGNATURE, 
        SQL_HANDLE, 
        PLAN_NAME, 
        ORIGIN, 
        ENABLED, 
        ACCEPTED, 
        FIXED, 
        AUTOPURGE,
        SQL_TEXT
  FROM    DBA_SQL_PLAN_BASELINES ;
 SIGNATURE SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX AUT
---------- ------------------------------ ------------------------------ -------------- --- --- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
1.2475E+18 SQL_115024ccba5e158c           SQL_PLAN_12n14tkx5w5cc52d2775d AUTO-CAPTURE   YES YES NO  YES
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
2.9758E+18 SQL_294c437e331fa51f           SQL_PLAN_2km23gstjz98zdf463620 AUTO-CAPTURE   YES YES NO  YES
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
1.3058E+19 SQL_b53655be57912101           SQL_PLAN_badkprtbt2881e2a1c06c AUTO-CAPTURE   YES YES NO  YES
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2
create index ind_test1_id_col2 on test1(id,col2);
create index ind_test2_id_col2 on test2(id,col2);
### sys
SQL> execute dbms_stats.gather_schema_stats('testuser');
### testuser 執行兩次 SQL, 應該都是 full table scan。
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994637339
-------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    10 |   200 |       |   661   (1)| 00:00:08 |
|   1 |  HASH GROUP BY      |       |    10 |   200 |       |   661   (1)| 00:00:08 |
|*  2 |   HASH JOIN         |       | 70069 |  1368K|  1376K|   659   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS FULL| TEST2 | 70069 |   547K|       |   239   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| TEST1 |   100K|  1171K|       |   239   (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST1"."ID"="TEST2"."ID")
   3 - filter("TEST2"."COL2"=1 OR "TEST2"."COL2"=2 OR "TEST2"."COL2"=3 OR
              "TEST2"."COL2"=4 OR "TEST2"."COL2"=5 OR "TEST2"."COL2"=6 OR "TEST2"."COL2"=7)
Note
-----
   - SQL plan baseline "SQL_PLAN_badkprtbt2881e2a1c06c" used for this statement
Statistics
----------------------------------------------------------
         25  recursive calls
         23  db block gets
       1632  consistent gets
          1  physical reads
       3288  redo size
        721  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed
### 查詢 DBA_SQL_PLAN_BASELINES 該語句再 create index後雖然走 full table scan但, plan baseline 裡面已經多一個 plan
 SIGNATURE SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX AUT
---------- ------------------------------ ------------------------------ -------------- --- --- --- ---
SQL_TEXT
--------------------------------------------------------------------------------
1.2475E+18 SQL_115024ccba5e158c           SQL_PLAN_12n14tkx5w5cc52d2775d AUTO-CAPTURE   YES YES NO  YES
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
2.9758E+18 SQL_294c437e331fa51f           SQL_PLAN_2km23gstjz98zdf463620 AUTO-CAPTURE   YES YES NO  YES
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
1.3058E+19 SQL_b53655be57912101           SQL_PLAN_badkprtbt288132575318 AUTO-CAPTURE   YES NO  NO  YES
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2
1.3058E+19 SQL_b53655be57912101           SQL_PLAN_badkprtbt2881e2a1c06c AUTO-CAPTURE   YES YES NO  YES
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2
# The SQL plan baseline now contains a second plan, but it has not yet been accepted.
# 可以看到 有兩個 plan但其中一個plan還沒被 accept。
### 比較 兩個 plan
 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_b53655be57912101',format=>'basic'));
SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_b53655be57912101',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_b53655be57912101
SQL text: SELECT      test1.col2, SUM(test1.col3) FROM        test1 ,test2 WHERE
               test1.id = test2.id   AND       test2.col2 IN (1,2,4,6,3,5,7)
          GROUP BY  test1.col2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_badkprtbt288132575318         Plan id: 844583704
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 4104780026
----------------------------------------------------
| Id  | Operation              | Name              |
----------------------------------------------------
|   0 | SELECT STATEMENT       |                   |
|   1 |  HASH GROUP BY         |                   |
|   2 |   HASH JOIN            |                   |
|   3 |    INDEX FAST FULL SCAN| IND_TEST2_ID_COL2 |
|   4 |    TABLE ACCESS FULL   | TEST1             |
----------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_badkprtbt2881e2a1c06c         Plan id: 3802251372
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1994637339
-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  HASH GROUP BY      |       |
|   2 |   HASH JOIN         |       |
|   3 |    TABLE ACCESS FULL| TEST2 |
|   4 |    TABLE ACCESS FULL| TEST1 |
-------------------------------------
41 rows selected.
## sys 演化一下
SQL> show user
USER is "SYS"
SQL> SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_b53655be57912101')
FROM   dual;SQL>   2  
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_B53655BE57912101')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
  SQL_HANDLE = SQL_b53655be57912101
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
Plan: SQL_PLAN_badkprtbt288132575318
------------------------------------
  Plan was verified: Time used .67 seconds.
  Plan failed performance criterion: 1.02 times worse than baseline plan.
                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       7              7
  Elapsed Time(ms):                30.838         31.525               .98
  CPU Time(ms):                    30.773         31.439               .98
  Buffer Gets:                       1610           1076               1.5
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1
-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0
## testuser
    declare
      a integer;
      pk integer;
    begin
      for i in 1..100000 loop
        for j in 1..10 loop
        a :=  j + 1000;
        pk := i * 10 + j;
           insert into test1 values(pk ,sysdate ,j ,a ,'HAHAHAHA test1' ,'HAHAHAHA test1');
           insert into test2 values(pk ,sysdate ,j ,a ,'HAHAHAHA test2' ,'HAHAHAHA test2');
        end loop;
      commit;
      end loop;
    end;
    /
## sys
execute dbms_stats.gather_schema_stats('testuser');
### testuser 執行兩次 SQL, 應該都是 full table scan。
SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994637339
-------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    10 |   200 |       |  6946   (1)| 00:01:24 |
|   1 |  HASH GROUP BY      |       |    10 |   200 |       |  6946   (1)| 00:01:24 |
|*  2 |   HASH JOIN         |       |   840K|    16M|    14M|  6925   (1)| 00:01:24 |
|*  3 |    TABLE ACCESS FULL| TEST2 |   765K|  5981K|       |  2475   (1)| 00:00:30 |
|   4 |    TABLE ACCESS FULL| TEST1 |  1100K|    12M|       |  2468   (1)| 00:00:30 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST1"."ID"="TEST2"."ID")
   3 - filter("TEST2"."COL2"=1 OR "TEST2"."COL2"=2 OR "TEST2"."COL2"=3 OR
              "TEST2"."COL2"=4 OR "TEST2"."COL2"=5 OR "TEST2"."COL2"=6 OR "TEST2"."COL2"=7)
Note
-----
   - SQL plan baseline "SQL_PLAN_badkprtbt2881e2a1c06c" used for this statement
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
      17160  consistent gets
          0  physical reads
          0  redo size
        728  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed 
 ### 在演化一下
SQL> SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_b53655be57912101')
FROM   dual;SQL>   2  
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_B53655BE57912101')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
  SQL_HANDLE = SQL_b53655be57912101
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES
Plan: SQL_PLAN_badkprtbt288132575318
------------------------------------
  Plan was verified: Time used 2.54 seconds.
  Plan passed performance criterion: 1.52 times better than baseline plan.
  Plan was changed to an accepted plan.
                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       7              7
  Elapsed Time(ms):               507.861        479.877              1.06
  CPU Time(ms):                   507.923        479.427              1.06
  Buffer Gets:                      17160          11302              1.52
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1
-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1   <==  
### 演化後的選擇
SQL> SELECT      test1.col2, SUM(test1.col3)
FROM        test1 ,test2
WHERE       test1.id = test2.id
  AND       test2.col2 IN (1,2,4,6,3,5,7)
 GROUP BY  test1.col2;  2    3    4    5  
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4104780026
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |    10 |   200 |       |  5209   (1)| 00:01:03 |
|   1 |  HASH GROUP BY         |                   |    10 |   200 |       |  5209   (1)| 00:01:03 |
|*  2 |   HASH JOIN            |                   |   840K|    16M|    14M|  5188   (1)| 00:01:03 |
|*  3 |    INDEX FAST FULL SCAN| IND_TEST2_ID_COL2 |   765K|  5981K|       |   738   (2)| 00:00:09 |
|   4 |    TABLE ACCESS FULL   | TEST1             |  1100K|    12M|       |  2468   (1)| 00:00:30 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TEST1"."ID"="TEST2"."ID")
   3 - filter("TEST2"."COL2"=1 OR "TEST2"."COL2"=2 OR "TEST2"."COL2"=3 OR "TEST2"."COL2"=4
              OR "TEST2"."COL2"=5 OR "TEST2"."COL2"=6 OR "TEST2"."COL2"=7)
Note
-----
   - SQL plan baseline "SQL_PLAN_badkprtbt288132575318" used for this statement
Statistics
----------------------------------------------------------
        163  recursive calls
         18  db block gets
      11436  consistent gets
          0  physical reads
       3204  redo size
        728  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          7  rows processed
SQL>

Posted by 啤酒與Gibson at 痞客邦 PIXNET Guestbook(0) 人氣()