一.
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.
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.
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
COUNT(*)
----------
1
查看给出的建议
SYS@jun1>set long 100000
SYS@jun1>set pagesize 50000
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" */
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" */
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.
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;
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
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.
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 状态
查看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;
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/