[20130626]11GR2 SQL Tuning Advisor.txt

11GR2加入了sql tuning advisor,缺省是打开的,我发现一些dba建议安装11G后,直接关闭它,好像因为消耗资源.

SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

实际上,如果能看看里面信息,对于优化还是有许多帮助的.

SQL> select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME                              STATUS   ATTRIBUTES                                                   SERVICE_NAME

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

auto optimizer stats collection          ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL

auto space advisor                       ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL

sql tuning advisor                       ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

select * from dba_advisor_findings  where task_name like 'SYS_AUTO_SQL_TUNING_TASK';

--可以获得信息.

--如果想知道如何建议,执行如下:

--我喜欢显示信息是英文的,定义环境变量NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.

spool suggest.txt

select dbms_sqltune.report_auto_tuning_task(

  (select min(execution_name) from dba_advisor_findings

    where task_name like 'SYS_AUTO_SQL_TUNING_TASK'),

  (select max(execution_name) from dba_advisor_findings

    where task_name like 'SYS_AUTO_SQL_TUNING_TASK')

) from dual;

spool off

--抽取一段里面的信息,我看到都是访问系统表的一些信息:

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

      SQLs with SQL Profile Findings Ordered by Maximum Benefit, Object ID

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

execution name                 object ID  SQL ID        benefit

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

EXEC_10224                           6517 fpf9ztf0yw0fw   96.91%

EXEC_10177                           6462 g10n36gghz1rr   93.80%

EXEC_10177                           6465 cbmzd29dvsx8j   92.21%

EXEC_10177                           6479 7ptuqb2nxxzrv   92.02%

EXEC_10177                           6456 1s3w0r29yv8cv   90.92%

EXEC_10112                           6437 d0cdpbm2v4ubu   89.95%

EXEC_10177                           6466 2m5atu0grvtmq   84.04%

EXEC_10112                           6442 0kyb2cb1ra5aq   84.02%

EXEC_10177                           6469 c9fckvj9d4muu   82.77%

EXEC_10112                           6439 72yf8srrpkwmh   80.15%

....

-------------------------------------------------------------------------------
 Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID  : 6465
Schema Name: SCOTT
SQL ID     : cbmzd29dvsx8j
SQL Text   : SELECT u.NAME FROM sys.USER$ u WHERE  u.TYPE# = 1 AND    EXISTS
             (SELECT owner#                FROM sys.obj$ o
             WHERE o.OWNER# = u.USER#                AND (o.TYPE# <> 5)
                      AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))
                         AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) =
             2))) ORDER BY 1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
  The SQL profile was not automatically created because auto-creation was
  disabled.  Set task parameter ACCEPT_SQL_PROFILES to TRUE to enable
  auto-creation.

  Recommendation (estimated benefit: 92.21%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SYS_AUTO_SQL_TUNING_TASK', object_id => 6465, task_owner =>
            'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .065773           .000795      98.79 %

  CPU Time (s):                  .06569           .000799      98.78 %

  User I/O Time (s):            .000178                 0        100 %

  Buffer Gets:                     1973               154      92.19 %

  Physical Read Requests:            10                 0        100 %

  Physical Write Requests:            0                 0

  Physical Read Bytes:            82739                 0        100 %

  Physical Write Bytes:               0                 0

  Rows Processed:                    28                28

  Fetches:                           28                28

  Executions:                         1                 1

  Notes

  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.
--也许是这些消耗资源!!要做10次以上.试想如果语句本来就很慢,再这样做有点可怕!
-------------------------------------------------------------------------------

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 977673319

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

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT               |         |     2 |    40 |     6  (17)| 00:00:01 |

|   1 |  SORT ORDER BY                 |         |     2 |    40 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID  | USER$   |     2 |    40 |     2   (0)| 00:00:01 |

|*  3 |    INDEX SKIP SCAN             | I_USER2 |     2 |       |     1   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| OBJ$    |     1 |    10 |     3   (0)| 00:00:01 |

|*  5 |      INDEX SKIP SCAN           | I_OBJ4  |  1518 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("U"."TYPE#"=1)

       filter("U"."TYPE#"=1 AND  EXISTS (SELECT /*+ PUSH_SUBQ INDEX_SS ("O"

              "I_OBJ4") */ 0 FROM "SYS"."OBJ$" "O" WHERE ("O"."TYPE#"<>1 OR

              BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR BITAND("O"."FLAGS",2)<>2) AND

              "O"."OWNER#"=:B1 AND "O"."TYPE#"<>5))

   4 - filter(("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR

              BITAND("O"."FLAGS",2)<>2))

   5 - access("O"."OWNER#"=:B1)

       filter("O"."OWNER#"=:B1 AND "O"."TYPE#"<>5)

2- Using SQL Profile

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

Plan hash value: 2080390714

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

| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |        |     1 |    20 |    82   (2)| 00:00:01 |

|   1 |  SORT ORDER BY                |        |     1 |    20 |    82   (2)| 00:00:01 |

|*  2 |   FILTER                      |        |       |       |            |          |

|*  3 |    TABLE ACCESS FULL          | USER$  |    39 |   780 |     4   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    10 |     4   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN          | I_OBJ2 |       |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter( EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE "O"."OWNER#"=:B1

              AND ("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR

              BITAND("O"."FLAGS",2)<>2) AND "O"."TYPE#"<>5))

   3 - filter("U"."TYPE#"=1)

   4 - filter(("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2

              OR BITAND("O"."FLAGS",2)<>2))

   5 - access("O"."OWNER#"=:B1)

       filter("O"."TYPE#"<>5)

3.拿上面的例子做一个测试:

SQL> set autot traceonly ;

SELECT u.NAME FROM sys.USER$ u WHERE  u.TYPE# = 1 AND    EXISTS

             (SELECT owner#                FROM sys.obj$ o

             WHERE o.OWNER# = u.USER#                AND (o.TYPE# <> 5)

                      AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))

                         AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) =

                2))) ORDER BY 1;

28 rows selected.

Execution Plan

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

Plan hash value: 977673319

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

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT               |         |     1 |    20 |    61   (2)| 00:00:01 |

|   1 |  SORT ORDER BY                 |         |     1 |    20 |    61   (2)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID  | USER$   |     2 |    40 |     2   (0)| 00:00:01 |

|*  3 |    INDEX SKIP SCAN             | I_USER2 |     2 |       |     1   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| OBJ$    |     1 |    10 |     3   (0)| 00:00:01 |

|*  5 |      INDEX SKIP SCAN           | I_OBJ4  |       |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("U"."TYPE#"=1)

       filter("U"."TYPE#"=1 AND  EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE

              ("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR

              BITAND("O"."FLAGS",2)<>2) AND "O"."OWNER#"=:B1 AND "O"."TYPE#"<>5))

   4 - filter(("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR

              BITAND("O"."FLAGS",2)<>2))

   5 - access("O"."OWNER#"=:B1)

       filter("O"."OWNER#"=:B1 AND "O"."TYPE#"<>5)

Statistics

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

          1  recursive calls

          0  db block gets

       2062  consistent gets

          0  physical reads

          0  redo size

        883  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         28  rows processed

---逻辑读2062.

按照建议执行如下:

execute dbms_sqltune.accept_sql_profile(task_name =>'SYS_AUTO_SQL_TUNING_TASK', object_id => 6465, task_owner =>'SYS', replace => TRUE);

Execution Plan

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

Plan hash value: 2080390714

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

| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |        |     1 |    20 |    82   (2)| 00:00:01 |

|   1 |  SORT ORDER BY                |        |     1 |    20 |    82   (2)| 00:00:01 |

|*  2 |   FILTER                      |        |       |       |            |          |

|*  3 |    TABLE ACCESS FULL          | USER$  |    39 |   780 |     4   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    10 |     4   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN          | I_OBJ2 |       |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter( EXISTS (SELECT 0 FROM "SYS"."OBJ$" "O" WHERE "O"."OWNER#"=:B1

              AND ("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2 OR

              BITAND("O"."FLAGS",2)<>2) AND "O"."TYPE#"<>5))

   3 - filter("U"."TYPE#"=1)

   4 - filter(("O"."TYPE#"<>1 OR BITAND("O"."FLAGS",2)<>2) AND ("O"."TYPE#"<>2

              OR BITAND("O"."FLAGS",2)<>2))

   5 - access("O"."OWNER#"=:B1)

       filter("O"."TYPE#"<>5)

Note

-----

   - SQL profile "SYS_SQLPROF_013f7fba46910000" used for this statement

Statistics

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

          1  recursive calls

          0  db block gets

        157  consistent gets

          0  physical reads

          0  redo size

        883  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         28  rows processed

--可以发现逻辑读147,明显减少.sql显示使用sql profile来控制执行计划.
--至少这些对于优化经验不足的人还是有帮助,并且能获得一些oracle的建议,机器毕竟比人聪明.
--我越来越喜欢11G了.