在Oracle 10gR2中为单条sql运行SQL Access Advisor

最新推荐文章于 2021-04-15 02:45:54 发布

原创最新推荐文章于 2021-04-15 02:45:54 发布 · 501 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #access #advisor

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文介绍如何使用Oracle的SQLAccessAdvisor针对特定SQL语句进行优化建议。通过实例演示创建索引的过程，并提供完整的PL/SQL脚本及输出结果。

为单条sql运行SQL Access Advisor
参考自:
https://oracle-base.com/articles/10g/sql-access-advisor-10g

[oracle@test ~]$ sqlplus scott/aaaaaa

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jul 1 07:22:20 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> BEGIN
  2  DBMS_ADVISOR.quick_tune(
  3  advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
  4  task_name    => 'emp_quick_tune_12345',  
  5  attr1        => 'SELECT e.* FROM emp e WHERE UPPER(e.ename) = ''SMITH''');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune_12345') AS script FROM   dual;

SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem  Username:          SCOTT
Rem  Task:          emp_quick_tune_12345
Rem  Execution date:  01/07/2017 07:23
Rem

CREATE INDEX "SCOTT"."EMP_IDX$$_00710001"
    ON "SCOTT"."EMP"
    (UPPER("ENAME"))
    COMPUTE STATISTICS;



SQL>