Базы данных Oracle - статьи
Text1.shtml
| DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK') ------------------------------------------------------------------------ GENERAL INFORMATION SECTION ------------------------------------------------------------------------ Tuning Task Name : my_sql_tuning_task Tuning Task Owner : SYS Scope : COMPREHENSIVE Time Limit(seconds) : 60 Completion Status : COMPLETED Started at : 03/14/2006 20:57:05 Completed at : 03/14/2006 20:57:05 Number of Statistic Findings : 1 Number of SQL Profile Findings : 1 ------------------------------------------------------------------------- Schema Name: SCOTT SQL ID : 3dcfttkf1kwmn SQL Text : SELECT ename, loc, sal, hiredate FROM emp, dept WHERE emp.deptno = dept.deptno ------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------- 1- Statistics Finding --------------------- Table "SCOTT"."DEPT" and its indices were not analyzed. Recommendation -------------- - Consider collecting optimizer statistics for this table and its indices. execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'DEPT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); Rationale --------- The optimizer requires up-to-date statistics for the table and its indices in order to select a good execution plan. DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK') ------------------------------------------------------------------------- 2- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 38.11%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task', replace => TRUE); ------------------------------------------------------------------------ EXPLAIN PLANS SECTION ------------------------------------------------------------------------ 1- Original With Adjusted Cost ------------------------------ Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 36 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") 2- Using SQL Profile -------------------- Plan hash value: 351108634 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK') ------------------------------------------------------------------------ -------------------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 9 | 1 (0)| 00:00:01 | |*4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") ------------------------------------------------------------------------ |
