|
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
114 consistent gets
50 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此时表是没有索引的，走的全表扫描
我们使用sql_id的方式来创建task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';
SQL_TEXT SQL_ID
------------------------------------------------------------ -------------
select * from t4 where empno=200 5avs113b5fn8v
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID => '5avs113b5fn8v',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tunning_task_5avs113b5fn8v',
description => 'Task to tune a query on 5avs113b5fn8v');
END;
/
启动这个task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/
查看report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tunning_task_5avs113b5fn8v
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/21/2014 13:48:02
Completed at : 12/21/2014 13:48:03
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 5avs113b5fn8v
SQL Text : select * from t4 where empno=200
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."T4" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'T4', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 86.7%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and addit |