SQL tunning Skills

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

/*AUTOTRACE 功能*/
-- 1. 開啟 Autotrace（只印出執行計畫與統計數字，不印出查詢結果）
SET AUTOTRACE TRACEONLY;

-- 2. 執行 SQL
select * from DISTRICT where d_name = '7F8KjbY9o';

-- 3. 用完後關閉 Autotrace
SET AUTOTRACE OFF;

----------------------------------
/*使用 DBMS_XPLAN.DISPLAY_CURSOR（最精準、詳細）*/
-- 1. 開啟會話層級的統計收集
ALTER SESSION SET STATISTICS_LEVEL = ALL;

-- 2. 執行你的目標 SQL（注意：加上 GATHER_PLAN_STATISTICS 提示）
select /*+ GATHER_PLAN_STATISTICS */ * from TPCC.DISTRICT where d_name = '7F8KjbY9o';

-- 3. 抓取剛才那條 SQL 真正的執行數據
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111

Autotrace TraceOnly
 Exhibits the performance statistics with silent query output

1 row selected. 


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f2xmscphq68ww, child number 0
-------------------------------------
select * from TPCC.DISTRICT where d_name = '7F8KjbY9o'
 
Plan hash value: 4256816630
 
-----------------------------------------------
| Id  | Operation         | Name     | E-Rows |
-----------------------------------------------
|   0 | SELECT STATEMENT  |          |        |
|*  1 |  TABLE ACCESS FULL| DISTRICT |      1 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("D_NAME"='7F8KjbY9o')
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 

Statistics
-----------------------------------------------------------
               2  CPU used by this session
               2  CPU used when call started
               2  DB time
               4  Requests to/from client
               3  SQL*Net roundtrips to/from client
               2  buffer is not pinned count
             338  bytes received via SQL*Net from client
           81605  bytes sent via SQL*Net to client
               3  calls to get snapshot scn: kcmgss
               6  calls to kcmgcs
            1508  consistent gets
            1508  consistent gets from cache
            1508  consistent gets pin
            1508  consistent gets pin (fastpath)
               1  enqueue releases
               1  enqueue requests
               2  execute count
        12353536  logical read bytes from cache
            1504  no work - consistent read gets
              14  non-idle wait count
               2  opened cursors cumulative
               1  opened cursors current
               1  parse count (hard)
               2  parse count (total)
               1  parse time elapsed
               1  recursive calls
            1508  session logical reads
               1  sorts (memory)
            2010  sorts (rows)
            1504  table scan blocks gotten
            1500  table scan disk non-IMC rows gotten
            1500  table scan rows gotten
               1  table scans (short tables)
               4  user calls
Autotrace Disabled

Session altered.


      D_ID     D_W_ID      D_YTD      D_TAX D_NEXT_O_ID D_NAME     D_STREET_1           D_STREET_2           D_CITY               D_ D_ZIP    
---------- ---------- ---------- ---------- ----------- ---------- -------------------- -------------------- -------------------- -- ---------
         2         31      30000        .12        3001 7F8KjbY9o  khlV4C7zLvaKTQ       psed51n6ElLtAWuo     BSdc6SrqhibDTFCho    QQ 160611111


PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5t0pkvg7urt8x, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from TPCC.DISTRICT where d_name 
= '7F8KjbY9o'
 
Plan hash value: 4256816630
 
----------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |      1 |        |      1 |00:00:00.01 |    1508 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| DISTRICT |      1 |      1 |      1 |00:00:00.01 |    1508 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("D_NAME"='7F8KjbY9o')
 

19 rows selected.