SQL Profile 如何使用
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table t1 (n number);
Table created.
插入数据
SQL> declare
2begin
3for i in 1..10000
4loop
5insert into t1 values(i);
6commit;
7end loop;
8end;
9/
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL> create index idx_t1 on t1(n);
Index created.
收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',method_opt=>'for all columns size 1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID1kg76709mx29d, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | | | 6 (100)| |
|*1 |TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"
41 rows selected.
针对上述SQL创建一个Automatic类型的SQL Profile的自动调整任务：
SQL> declare
2my_task_name varchar2(30);
3my_sqltext clob;
4begin
5my_sqltext :='select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
6my_task_name := dbms_sqltune.create_tuning_task(
7sql_text => my_sqltext,
8user_name => 'SCOTT',
9scope => 'COMPREHENSIVE',
10time_limit => 60,
11task_name => 'my_sql_tuning_task_2',
12description => 'Task to tune a query on table t1');
13end;
14/
PL/SQL procedure successfully completed.
执行上述自动调整任务
SQL> begin
2dbms_sqltune.execute_tuning_task( task_name => 'my_sql_tuning_task_2');
3end;
4/
PL/SQL procedure successfully completed.
查看刚刚运行的自动调整任务的调整结果：
SQL> set long 9000
SQL> set longchunksize 1000
SQL> set lines 800
SQL> select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SCOTT
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 07/12/2015 04:34:15
Completed at : 07/12/2015 04:34:16
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 4bh6sn1zvpgq7
SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
-------------------------------------------------------------------------------
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: 83.98%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 1 | 3 | 6 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 1 | 3 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
2- Using SQL Profile
--------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|*1 |INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
-------------------------------------------------------------------------------
按照上面提示，输入下面的命令，接收这个SQL Profile：
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID1kg76709mx29d, child number 1
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|*1 |INDEX RANGE SCAN| IDX_T1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
1 - access("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"
Note
-----
- SQL profile "SYS_SQLPROF_01539fb60e15c000" used for this statement
45 rows selected.
页:
[1]