Oracle-38-报告与数据向导

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