Базы данных 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 FULLDEPT |     4 |    36 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULLEMP  |    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 ROWIDDEPT    |     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")

------------------------------------------------------------------------



Содержание раздела