程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 為什麼需要SQL Profile，SQLProfile

為什麼需要SQL Profile，SQLProfile

編輯：Oracle教程

為什麼需要SQL Profile，SQLProfile

為什麼需要SQL Profile

Why oracle need SQL Profiles,how it work and what are SQL Profiles...

使用DBMS_XPLAN.DISPLAY分析SQL執行計劃，通常會看到Note中有類似下面這樣的提示；

Note
-----
 
   - SQL profile "SYS_SQLPROF_0158283a9b920000" used for this statement

SQL profile由人為手工創建或在Automatic SQL Tunning階段由SQL tuning advisor創建，它看起來有如下的意思:

當看到這些信息，比較關心的是這個對象(SLQ profile)是什麼?它做了什麼?是否真的需要它?帶著這些疑問學習和探索，最終決解了遇到的問題。

SQL> @i

USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
OPS$SYWU             sydb                 sywu.com                  288   22197    11.2.0.4.0 20160421 13736      46    3392:1312       0000000071FE0DA0 0000000072149F40

遇到的問題

假設有這樣一張類似訂單的表orders；

create table orders(order_no,order_date)
as
select
    level,cast(sysdate-level/24 as date) 
from 
    dual
connect by level<=5E5;

SQL> @desc orders
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ORDER_NO                                 NUMBER
    2      ORDER_DATE                               DATE

保存訂單信息，order_date上創建了索引。

create index idx_orders_dt on orders(order_date);

在交易中可能經常遇到某些原因導致交易延期的情況，為了測試這個問題，開發人員添加了未來某一天這樣的日期值測試；這裡用一個清晰的時間來代替未來的日期；

INSERT INTO ORDERS VALUES (-1, DATE '9999-01-01');

和正常使用的一樣，該表定期收集了統計信息；

exec dbms_stats.gather_table_stats(user,'orders', cascade => true);

當系統查詢當天的交易記錄時發現優化器使用全表掃描，並非索引掃描；

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |       |   130 (100)|          |     10 |00:00:00.23 |     329 |    323 |
|*  1 |  TABLE ACCESS FULL| ORDERS |      1 |    496K|  6302K|   130  (26)| 00:00:02 |     10 |00:00:00.23 |     329 |    323 |
-------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / ORDERS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "ORDERS"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - filter("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ORDERS"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]

顯然對於這樣一張交易記錄表，實際當天的記錄數據只占全表數據量的4.1%左右，使用索引掃描的方式開銷小於全表掃描，但優化器對范圍評估錯誤。接著使用DBMS_SQLTUNE分析SQL；

var task_name varchar2(30)

BEGIN
    :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => 'select * from orders where order_date>=trunc(sysdate,''DD'')',
         user_name   => user,
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'orders_tuning_task');
END;
/

執行分析；

alter session set events '10046 trace name context forever,level 12';
exec DBMS_SQLTUNE.execute_tuning_task(task_name => :task_name);

分析結果；

col REPORT_TUNING format a200
select
    dbms_sqltune.report_tuning_task(:task_name) REPORT_TUNING
from
    dual;

REPORT_TUNING
----------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : orders_tuning_task
Tuning Task Owner  : OPS$SYWU
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 11/07/2016 21:43:25
Completed at       : 11/07/2016 21:43:27

-------------------------------------------------------------------------------
Schema Name: OPS$SYWU
SQL ID     : 9ybj4xdc5hsrb
SQL Text   : select * from orders where order_date>=trunc(sysdate,'DD')

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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 98.78%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'orders_tuning_task', task_owner => 'OPS$SYWU', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .041546           .000132      99.68 %
  CPU Time (s):                 .029895             .0001      99.66 %
  User I/O Time (s):            .015204           .000032      99.78 %
  Buffer Gets:                      328                 4      98.78 %
  Physical Read Requests:            45                 0        100 %
  Physical Write Requests:            0                 0
  Physical Read Bytes:         10682368              9830       99.9 %
  Physical Write Bytes:               0                 0
  Rows Processed:                    10                10
  Fetches:                           10                10
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

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

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1275100350

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    18 |   234 |   130  (26)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ORDERS |    18 |   234 |   130  (26)| 00:00:02 |
----------------------------------------------------------------------------

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

   1 - filter("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))

2- Using SQL Profile
--------------------
Plan hash value: 3364688013

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    18 |   234 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS        |    18 |   234 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ORDERS_DT |    10 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))

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

oracle通過分析發現了問題，產生了新的執行計劃，並對比兩個執行計劃，新的執行計劃改善90%+的性能，並且改善性能問題只需要同意使用SQL Profile即可；然後允許數據庫使用SQL Profile。

exec dbms_sqltune.ACCEPT_SQL_PROFILE(TASK_NAME=>:task_name);

再次執行SQL時，優化器使用了SQL Profile和新的執行計劃。

select * from orders where order_date>=trunc(sysdate,'DD');

SQL_ID  3zcvw1pxfcypm, child number 0
-------------------------------------
select * from orders where order_date>=trunc(sysdate,'DD')

Plan hash value: 3364688013

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |     3 (100)|          |     10 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS        |      1 |     18 |   234 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | IDX_ORDERS_DT |      1 |     10 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / ORDERS@SEL$1
   2 - SEL$1 / ORDERS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "ORDERS"@"SEL$1" ("ORDERS"."ORDER_DATE"))
      END_OUTLINE_DATA
  */

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

   2 - access("ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ORDERS"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]
   2 - "ORDERS".ROWID[ROWID,10], "ORDER_DATE"[DATE,7]

Note
-----
 
   - SQL profile SYS_SQLPROF_01582d15092f0001 used for this statement


SQL>  @sql 2061925043
Show SQL text, child cursors and execution stats for SQL hash value 2061925043 child OPS$SYWU@sydb_111 report

HASH_VALUE   CH#  PLAN_HASH SQL_TEXT                                                                                                       SQL_PROFILE
---------- ----- ---------- -------------------------------------------------------------------------------------------------------------- ------------------------------
2061925043     0 3364688013 select * from orders where order_date>=trunc(sysdate,'DD')                                               SYS_SQLPROF_01582d15092f0001 


  CH# PARENT_HANDLE    OBJECT_HANDLE        PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED       LIOS       PIOS      SORTS     CPU_MS     ELA_MS USERS_EXECUTING
----- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
    0 0000000064618858 0000000063A03108          1          3          1          2             10         16          6          0      7.999      8.621               0

Oracle 分析背後做了什麼

很驚奇，為什麼分析後優化器就能找出問題所在，此時焦點都集中在trace文件了；分析trace文件，發現如下信息；

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) opt_param('parallel_execution_enabled',
  'false')  result_cache */ COUNT(C1)
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "ORDERS")  */ 1 AS C1 FROM
  "ORDERS" SAMPLE BLOCK(56.8182, 2) SEED(1)  "ORDERS") innerQuery

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) opt_param('parallel_execution_enabled',
  'false')  result_cache */ COUNT(C1)
FROM
 (SELECT /*+ qb_name("innerQuery") INDEX_FFS( "ORDERS" "IDX_ORDERS_DT")  */ 1
  AS C1 FROM "ORDERS" SAMPLE BLOCK(59.5238, 2) SEED(1)  "ORDERS" WHERE (
  "ORDERS".ORDER_DATE IS NOT NULL)) innerQuery

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) opt_param('parallel_execution_enabled',
  'false')  result_cache */ COUNT(C1)
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "ORDERS")  */ 1 AS C1 FROM
  "ORDERS" SAMPLE BLOCK(56.8182, 2) SEED(1)  "ORDERS" WHERE
  ("ORDERS"."ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))) innerQuery

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) opt_param('parallel_execution_enabled',
  'false')  result_cache OPT_ESTIMATE(@"innerQuery", TABLE, "ORDERS",
  SCALE_ROWS=3.545138895e-05) */ C1, C2, C3
FROM
 (SELECT /*+ qb_name("innerQuery") INDEX( "ORDERS" "IDX_ORDERS_DT")  */
  COUNT(*) AS C1, 4294967295 AS C2, COUNT(*) AS C3  FROM "ORDERS" "ORDERS"
  WHERE ("ORDERS"."ORDER_DATE">=TRUNC(SYSDATE@!,'fmdd'))) innerQuery

/* SQL Analyze(156,0) */ select * from orders where order_date>=trunc(sysdate,'DD')

從trace文件分析得出，oracle做了如下的操作:

Oracle對比舊的和新的執行計劃後，將消耗小的執行計劃信息保存到SQL profile中。

優化器的問題

通常優化器相信:

優化器信任收集的統計信息，這些信息包括表行記錄數,distinct value,max/min value,直方圖信息；換一種通俗的說法，統計信息捕獲了表整體形狀數據，但有些低級別的信息丟失了。這種分析對於大多數數據來說往往工作得很好，但實際情況中，不可避免的有違反規則的例外，比如對於一張大表，98%的數據可能以隨機的方式分配在整個段中(segment)，剩余的2%的數據可能只集中在幾個數據塊中；不幸的是收集統計信息時沒有記錄這些細節；這就引發一個問題，已經有的統計信息不能完全有效的幫助優化器生成正確的執行計劃，所以到了這裡問題轉變為什麼可以彌補或糾正這些信息，讓優化器面對這種特定的SQL時可以評估正確，生成好的執行計劃。

什麼是SQL Profile

通過上面的實驗大體將SQL Profile定義為：

首先通過dbms_sqltune.create_tunning_task創建任務告訴數據庫存在問題的可以改善的SQL，這個操作在11G或以後的版本中可以通過Automatic SQL Tuning在對"most active" SQL 分析時創建；然後運行dbms_sqltune.execute_tuning_task評估，這個過程包括三個主要步驟:

如果優化器試運行得出的結果為:

則表明優化器證明舊的評估對於特定的SQL是錯誤的，一些低級的數據被分析出，進一步，oracle會將這些信息保存供以後使用。但是如何保存這些信息呢?不可能通過定期性的更新統計信息，因為統計信息不包括這些信息。所以，數據庫使用一個獨立的對象(SQL Profile)保存SQL和這些(cardinality)信息。oracle 以opt_estimate hints的格式保存cardinality信息；
/+ opt_estimate(table, orders, scale_rows=10) */
或者
/+ opt_estimate(index_scan, orders, IDX_ORDERS_DT, scale_rows=0.001) */
所以如果使用了SQL Profile，評估時默認的cardinality將乘以這些數字，優化器會更真實的查看到表中的數據信息，然後做出評估。