SQL Access Advisor、SQL Tuning Advisor 测试

一.  SQL Access Advisor

前期准备创建测试表

SYS@jun1>drop table enmo.t5;

创建ENMO这张表

SYS@jun1>create table enmo.t5 tablespace users as select rownum id from dual connect by rownum<=100000;

Table created.
建立索引
SYS@jun1>create index enmo.idx_t5_id on enmo.t5(id) tablespace users;

Index created.
对表进行分析
SYS@jun1>exec dbms_stats.gather_table_stats(ownname=>'ENMO',tabname=>'T5');

PL/SQL procedure successfully completed.

做测试，使用hint强制走全表扫描 看是否给出建议

SYS@jun1>DECLARE
  2  v_task_name VARCHAR2(30);
  3  v_sql varchar2(200);
  4  BEGIN
  5  v_task_name :='MY_TASK_ACCESS_ADVISOR';
  6  v_sql:='select /*+ full(t) */ * from enmo.t5 where id=:1';
  7   DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,v_task_name,v_sql);
  8  END;
  9  /

PL/SQL procedure successfully completed.

查询，结果为1 。。。。刚才的全表扫Oracle已给出对应的建议

SYS@jun1>select count(*) from user_advisor_actions where task_name='MY_TASK_ACCESS_ADVISOR';

  COUNT(*)
----------
         1

查看给出的建议

SYS@jun1>set long 100000
SYS@jun1>set pagesize 50000

SYS@jun1>SELECT DBMS_ADVISOR.get_task_script('MY_TASK_ACCESS_ADVISOR') AS script FROM dual;

SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SYS
Rem  Task:            MY_TASK_ACCESS_ADVISOR
Rem  Execution date:
Rem

/* RETAIN INDEX "ENMO"."IDX_T5_ID" */

也可输出到到目录下查看

SYS@jun1>create or replace directory dir_aa as '/home/oracle';

Directory created.

SYS@jun1>!cat /home/oracle/s.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem 
Rem  Username:        SYS
Rem  Task:            MY_TASK_ACCESS_ADVISOR
Rem  Execution date:  
Rem 

/* RETAIN INDEX "ENMO"."IDX_T5_ID" */

二.SQL Tuning Advisor（）

创建用户授权，并授予advisor给调优用户

SYS@jun1>create user bamboo identified by bamboo;

User created.

SYS@jun1>grant connect,resource to bamboo;

Grant succeeded.

SYS@jun1>grant advisor to bamboo;

Grant succeeded.

SYS@jun1>create table bigtable (id number(10),name varchar2(100));

Table created.

创建两张测试表

SYS@jun1>begin
  2  for i in 1..5000000 loop
  3  insert into bigtable values(i,'test'||i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SYS@jun1>commit;

Commit complete.

SYS@jun1> create table smalltable (id number(10),name varchar2(100));

Table created.

SYS@jun1>begin
  2  for i in 1..100000 loop
  3  insert into smalltable values(i,'test'||i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SYS@jun1>commit;

对于两张表做一个等连接查询

首先开启跟踪计划

SYS@jun1>set autotrace on;

SYS@jun1>select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000;

        ID
----------
NAME
--------------------------------------------------------------------------------
        ID
----------
NAME
--------------------------------------------------------------------------------
     40000
test40000
     40000
test40000



Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322

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

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT   |            |   517 | 67210 |  4334   (2)| 00:00:53
|

|*  1 |  HASH JOIN         |            |   517 | 67210 |  4334   (2)| 00:00:53
|

|*  2 |   TABLE ACCESS FULL| SMALLTABLE |     3 |   195 |    77   (2)| 00:00:01
|

|*  3 |   TABLE ACCESS FULL| BIGTABLE   |   171 | 11115 |  4257   (2)| 00:00:52
|

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


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

   1 - access("A"."ID"="B"."ID")
   2 - filter("B"."ID"=40000)
   3 - filter("A"."ID"=40000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15825  consistent gets
       9520  physical reads
          0  redo size
        739  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)
          1  rows processed

两张都走了全表扫描，物理读9520  physical reads。设立优化器，看给出意见

通过 DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建任务，通过DBMS_SQLTUNE.CREATE_TUNING_TASK 执行

SYS@jun1>DECLARE 
  2  my_task_name VARCHAR2(30); 
  3  my_sqltext CLOB; 
  4  BEGIN 
  5  my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id=40000';   
  6  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 
  7  sql_text => my_sqltext, 
  8  user_name => 'BAMBOO', 
  9  scope => 'COMPREHENSIVE', 
10  time_limit => 60, 
11  task_name => 'test_sql_tuning_task1', 
12  description => 'Task to tune a query'); 
13  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
14  END; 
15  /

PL/SQL procedure successfully completed.

SYS@jun1> select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;

查看test_sql_tuning_task1  状态

SYS@jun1>set autotrace off;
SYS@jun1>set long 999999
SYS@jun1>set LONGCHUNKSIZE 999999 
SYS@jun1>set serveroutput on size 999999
SYS@jun1>set linesize 200
SYS@jun1>select
  2  dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30347819/viewspace-1783559/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30347819/viewspace-1783559/