日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

• 這種方法不能指定outline的具體名字，由系統(tǒng)自動(dòng)生成，可以通過alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2修改
• 不指定類別的話默認(rèn)為default，而且此處創(chuàng)建時(shí)，不能指定為default類別（會(huì)報(bào)錯(cuò)）。
• 我們使用outline固定執(zhí)行計(jì)劃時(shí)，一般都是選用此種方法

參考：
http://www.bubuko.com/infodetail-216529.html

實(shí)驗(yàn)如下：
SQL>  create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects;

Table created.

SQL> create index ind_1 on dh_stat(id) compute statistics;                                          

Index created.

SQL> alter system flush shared_pool;

System altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'sys',TABNAME=>'dh_stat',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);

PL/SQL procedure successfully completed.

SQL> col name format a15
SQL> col name format a30
SQL> col sql_text for a55
SQL>  select /* outlinetest1 */ * from dh_stat where id=771;

        ID NAME                           TYPE
---------- ------------------------------ -------------------
       771 SYS_IL0000000772C00002$$       INDEX
       
SQL> set lines 200
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest1%' and sql_text not like '%v$sql%';

SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
 select /* outlinetest1 */ * from dh_stat where id=771  9a69w18a8uuhk  344812050            0
 
SQL>  select * from table(dbms_xplan.display_cursor('9a69w18a8uuhk',null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  9a69w18a8uuhk, child number 0
-------------------------------------
 select /* outlinetest1 */ * from dh_stat where id=771

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    39 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID"=771)


19 rows selected.

---根據(jù)sql已經(jīng)有的執(zhí)行計(jì)劃生成outline:

SQL> exec DBMS_OUTLN.create_outline(hash_value=>344812050,child_number => 0,category=>'TEST');        

PL/SQL procedure successfully completed.

--查詢outline情況：
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY                       USED   SQL_TEXT
------------------------------ ------------------------------ ------ -------------------------------------------------------
SYS_OUTLINE_17090216454529101  TEST                           UNUSED  select /* outlinetest1 */ * from dh_stat where id=771

此處outline的USED狀態(tài)沒有改變，因?yàn)槲覀儧]有激活TEST類別的outline，再次申明，outline必須通過use_stored_outlines參數(shù)激活后，優(yōu)化器才會(huì)使用outline

---下面這一步激活TEST類別的OUTLINE，也可以在系統(tǒng)級(jí)激活OUTLINE
SQL> alter session set use_stored_outlines=TEST;

驗(yàn)證省略。。。。。。。。。。。

創(chuàng)新互聯(lián)專注于中大型企業(yè)的網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì)和網(wǎng)站改版、網(wǎng)站營(yíng)銷服務(wù)，追求商業(yè)策劃與數(shù)據(jù)分析、創(chuàng)意藝術(shù)與技術(shù)開發(fā)的融合，累計(jì)客戶上千，服務(wù)滿意度達(dá)97%。幫助廣大客戶順利對(duì)接上互聯(lián)網(wǎng)浪潮，準(zhǔn)確優(yōu)選出符合自己需要的互聯(lián)網(wǎng)運(yùn)用，我們將一直專注高端網(wǎng)站設(shè)計(jì)和互聯(lián)網(wǎng)程序開發(fā)，在前進(jìn)的路上，與客戶一起成長(zhǎng)！

語(yǔ)法：
SQL text format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  user_name        IN VARCHAR2  := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;  

SQL ID format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

Workload Repository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

實(shí)驗(yàn)1：sql_id format
1. sys用戶創(chuàng)建oracle優(yōu)化任務(wù)(v_sqlid,task_name根據(jù)實(shí)際情況改變)
 set long 10000000 longchunksize 1000000 linesize 150 pagesize 0 serveroutput on size 1000000   verify off
 declare
  my_task_name varchar2(30);
  v_sqlid      varchar2(50);
 begin
  v_sqlid:='11wrxmug9y4a7';
  my_task_name := dbms_sqltune.create_tuning_task
                  (sql_id=> v_sqlid,
                   scope => 'comprehensive',
                   time_limit=>160,
                   task_name=>'task_00000',
                   description => 'tuning task');
  dbms_sqltune.execute_tuning_task('task_00000');              
  end;
 /
 
注：dbms_sqltune.execute_tuning_task('task_00000')，是執(zhí)行優(yōu)化任務(wù)
 
2. 打印優(yōu)化任務(wù)，里面有一些具體的改進(jìn)措施
 select dbms_sqltune.report_tuning_task('task_00000') from dual;
 
3.根據(jù)優(yōu)化建議，綁定profile，理論上可提高語(yǔ)句執(zhí)行效率 
execute dbms_sqltune.accept_sql_profile(task_name => 'task_00000',task_owner => 'sys', replace => true); 

實(shí)驗(yàn)2：sql_text format
SQL> alter session set statistics_level=all;

Session altered.

SQL> set serveroutput off
SQL> select * from scott.emp where ename='SCOTT' and DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4dsqbp572auuu, child number 0
-------------------------------------
select * from scott.emp where ename='SCOTT' and DEPTNO=20

Plan hash value: 3956160932

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       7 |

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

   1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))


18 rows selected.

SQL> DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := 'select * from scott.emp where ename= :name and DEPTNO= :deptno';
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
bind_list   => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
         user_name   => 'SYS',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'test_sql_tuning',
         description => 'Task to tune a query on emp');
END;
/

PL/SQL procedure successfully completed.

參數(shù)說(shuō)明：
bind_list：多個(gè)綁定變量以','逗號(hào)分隔。參數(shù)值一定要根據(jù)綁定變量對(duì)應(yīng)的列的類型書寫.如：emp.ename類型是VARCHAR2(10)，那么就要寫成 bind_list =>sql_binds(anydata.convertvarchar2(10)),
time_limit：執(zhí)行的最長(zhǎng)時(shí)間，默認(rèn)是60。
scope：LIMITED，用大概1秒時(shí)間去優(yōu)化SQL語(yǔ)句，但是并不進(jìn)行SQL Profiling分析;COMPREHENSIVE，進(jìn)行全面分析，包含SQL Profiling分析；比LIMITED用時(shí)更長(zhǎng)。

--查看任務(wù)名 
SQL> select task_name from dba_advisor_log where task_name='test_sql_tuning';

TASK_NAME
------------------------------
test_sql_tuning

--執(zhí)行sql tuning任務(wù)
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );

PL/SQL procedure successfully completed.

--查看sql tunning任務(wù)狀態(tài)
SQL> select task_name,status from dba_advisor_log where task_name='test_sql_tuning';

TASK_NAME                      STATUS
------------------------------ -----------
test_sql_tuning                COMPLETED

----展示sql tunning結(jié)果
set long 10000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;

...........省略

--根據(jù)建議accept_sql_profile
execute dbms_sqltune.accept_sql_profile(.....)

--完成后刪除sql tunning任務(wù)
exec dbms_sqltune.drop_tuning_task('test_sql_tuning');

--查看SQL Tuning Advisor的進(jìn)展（task執(zhí)行很久）
set lines 200
col opname for a20
col ADVISOR_NAME for a20
select sid,serial#,username,opname,advisor_name,target_desc,start_time sofar,totalwork from v$advisor_progress where username = 'SYS';