AWR报告
手工产生报告
SYS@ orcl>exec dbms_workload_repository.create_snapshot();
产生报告
SYS@ orcl>@?/rdbms/admin/awrrpt
按照提示点回车
在虚拟机中查看报告文件/home/oracle
查看SQLAWR报告
SYS@ orcl>@?/rdbms/admin/awrsqrpt
生成另一种AWR报告
SYS@ orcl>@?/rdbms/admin/awrddrpt
里面有两份报告的对比
ASHRPT
SYS@ orcl>@?/rdbms/admin/ashrpt
ADDMRPT
SYS@ orcl>@?/rdbms/admin/addmrpt
对指定SQL生成优化建议
授权
SYS@ orcl>grant advisor to scott;
Grant succeeded.
Elapsed: 00:00:00.01
执行脚本
conn scott/scott
declare
my_task_name varchar(30);
my_sql_text clob;
begin
my_sql_text :='select * from emp';
my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=>my_sql_text,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning_task',
description => 'task to tune a query on a specified product');
dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning_task');
end;
/
SYS@ orcl>conn scott/scott
Connected.
SCOTT@ orcl>declare
2 my_task_name varchar(30);
3 my_sql_text clob;
4 begin
5 my_sql_text :='select * from emp';
6 my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text=>my_sql_text,
8 user_name => 'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'test_sql_tuning_task',
12 description => 'task to tune a query on a specified product');
13 dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning_task');
14 end;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.68
查看结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning_task')
FROM DUAL;
SCOTT@ orcl>SET LONG 10000
SCOTT@ orcl>SET LONGCHUNKSIZE 1000
SCOTT@ orcl>SET LINESIZE 100
SCOTT@ orcl>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning_task')
2 FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test_sql_tuning_task
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 07/31/2018 16:40:46
Completed at : 07/31/2018 16:40:46
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 59bfjg9yaf1v6
SQL Text : select * from emp
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', 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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
-------------------------------------------------------------------------------
Elapsed: 00:00:00.43