在EM中使用SQL Access Advisor（SQL访问建议）优化SQL

以下实验来自Oracle 11.2.0.4.0

在EM中，可以先创建SQL优化集，选定若干需要优化的SQL语句。

然后选中刚刚创建的SQL优化集，使用SQL访问建议器对其进行分析。

分析结果查看：
由于在sqlplus中无法完整显示出输出，故先在SQL Developer中输出，再复制到文本文档：
在SQL Developer中运行

点击(此处)折叠或打开

1. select dbms_advisor.get_task_script(task_name => 'SQLACCESS4138632') from dual;

其中TASK_NAME可以在运行SQL访问建议时显示的PL/SQL代码中看到。

以下是部分输出实例：

点击(此处)折叠或打开

1. "Rem SQL Access Advisor: Version 11.2.0.4.0 - Production
   
2. Rem
   
3. Rem Username: SYS
   
4. Rem Task: SQLACCESS7370760
   
5. Rem Execution date:
   
6. Rem
   
7. 
   
8. Rem
   
9. Rem Repartitioning table "SH"."SALES
10. Rem 
    Rem Creating new partitioned table
    Rem 
      CREATE TABLE "SH"."SALES1" 
       ( "PROD_ID" NUMBER, 
    "CUST_ID" NUMBER, 
    "TIME_ID" DATE, 
    "CHANNEL_ID" NUMBER, 
    "PROMO_ID" NUMBER, 
    "QUANTITY_SOLD" NUMBER(10,2), 
    "SELLER" NUMBER(6,0), 
    "FULFILLMENT_CENTER" NUMBER(6,0), 
    "COURIER_ORG" NUMBER(6,0), 
    "TAX_COUNTRY" VARCHAR2(3), 
    "TAX_REGION" VARCHAR2(3), 
    "AMOUNT_SOLD" NUMBER(10,2)
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 16 MAXTRANS 255 
     NOCOMPRESS LOGGING
      TABLESPACE "USERS" 
    PARTITION BY HASH ("TIME_ID") PARTITIONS 32;
    
    
    Rem 
    Rem Copying constraints to new partitioned table
    Rem 
      ALTER TABLE "SH"."SALES1" ADD CONSTRAINT "SALES_UK1" UNIQUE ("PROD_ID", "CUST_ID", "PROMO_ID", "CHANNEL_ID", "TIME_ID") RELY DISABLE;
      ALTER TABLE "SH"."SALES1" MODIFY ("AMOUNT_SOLD" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("TAX_COUNTRY" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("COURIER_ORG" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("FULFILLMENT_CENTER" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("SELLER" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("QUANTITY_SOLD" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("PROMO_ID" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("CHANNEL_ID" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("TIME_ID" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("CUST_ID" NOT NULL ENABLE);
      ALTER TABLE "SH"."SALES1" MODIFY ("PROD_ID" NOT NULL ENABLE);
    Rem 
    Rem Copying referential constraints to new partitioned table
    Rem 
      ALTER TABLE "SH"."SALES1" ADD CONSTRAINT "SALES_CHANNELS_FK1" FOREIGN KEY ("CHANNEL_ID")
     REFERENCES "SH"."CHANNELS" ("CHANNEL_ID") DISABLE;
      ALTER TABLE "SH"."SALES1" ADD CONSTRAINT "SALES_CUSTOMERS_FK1" FOREIGN KEY ("CUST_ID")
     REFERENCES "SH"."CUSTOMERS" ("CUST_ID") DISABLE;
      ALTER TABLE "SH"."SALES1" ADD CONSTRAINT "SALES_PRODUCTS_FK1" FOREIGN KEY ("PROD_ID")
     REFERENCES "SH"."PRODUCTS" ("PROD_ID") DISABLE;
      ALTER TABLE "SH"."SALES1" ADD CONSTRAINT "SALES_PROMOTIONS_FK1" FOREIGN KEY ("PROMO_ID")
     REFERENCES "SH"."PROMOTIONS" ("PROMO_ID") DISABLE;
    Rem 
    Rem Populating new partitioned table with data from original table
    Rem 
    INSERT /*+ APPEND */ INTO "SH"."SALES1"
        SELECT * FROM "SH"."SALES";
    COMMIT;
    
    
    begin
      dbms_stats.gather_table_stats('"SH"', '"SALES1"', NULL, dbms_stats.auto_sample_size);
    end;
    /
    
    
    Rem 
    Rem Renaming tables to give new partitioned table the original table name
    Rem 
    ALTER TABLE "SH"."SALES" RENAME TO "SALES11";
    ALTER TABLE "SH"."SALES1" RENAME TO "SALES";
    
    
    
    
    Rem  
    Rem  Repartitioning table "SH"."TIMES"
    Rem  
    
    
    SET SERVEROUTPUT ON
    SET ECHO ON
    
    
    Rem 
    Rem Creating new partitioned table
    Rem 
      CREATE TABLE "SH"."TIMES1" 
       ( "TIME_ID" DATE, 
    "DAY_NAME" VARCHAR2(13), 
    "DAY_NUMBER_IN_MONTH" VARCHAR2(2), 
    "DAY_NUMBER_IN_YEAR" VARCHAR2(3), 
    "CALENDAR_YEAR" VARCHAR2(4), 
    "CALENDAR_QUARTER_NUMBER" VARCHAR2(1), 
    "CALENDAR_MONTH_NUMBER" VARCHAR2(2), 
    "CALENDAR_WEEK_NUMBER" VARCHAR2(2), 
    "CALENDAR_MONTH_DESC" VARCHAR2(7), 
    "CALENDAR_QUARTER_DESC" VARCHAR2(6)
       ) SEGMENT CREATION IMMEDIATE 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS LOGGING
      TABLESPACE "USERS" 
    PARTITION BY HASH ("TIME_ID") PARTITIONS 32;
    
    
    Rem 
    Rem Copying constraints to new partitioned table
    Rem 
      ALTER TABLE "SH"."TIMES1" ADD CONSTRAINT "TIMES_PK1" PRIMARY KEY ("TIME_ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      TABLESPACE "USERS"  ENABLE NOVALIDATE;
    Rem 
    Rem Copying indexes to new partitioned table
    Rem 
      CREATE UNIQUE INDEX "SH"."TIMES_PK1" ON "SH"."TIMES1" ("TIME_ID") 
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
      TABLESPACE "USERS" ;
    Rem 
    Rem Populating new partitioned table with data from original table
    Rem 
    INSERT /*+ APPEND */ INTO "SH"."TIMES1"
        SELECT * FROM "SH"."TIMES";
    COMMIT;
    
    
    begin
      dbms_stats.gather_table_stats('"SH"', '"TIMES1"', NULL, dbms_stats.auto_sample_size);
    end;
    /
    
    
    Rem 
    Rem Renaming tables to give new partitioned table the original table name
    Rem 
    ALTER TABLE "SH"."TIMES" RENAME TO "TIMES11";
    ALTER TABLE "SH"."TIMES1" RENAME TO "TIMES";
    
    
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."SALES"
        WITH ROWID, SEQUENCE("TIME_ID")
        INCLUDING NEW VALUES;
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."PRODUCTS"
        WITH PRIMARY KEY ;
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."CUSTOMERS"
        WITH PRIMARY KEY ;
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."TIMES"
        WITH PRIMARY KEY ;
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."CHANNELS"
        WITH PRIMARY KEY ;
    
    
    CREATE MATERIALIZED VIEW LOG ON
        "SH"."PROMOTIONS"
        WITH PRIMARY KEY ;
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170000"
        REFRESH FAST WITH PRIMARY KEY
        ENABLE QUERY REWRITE
        AS SELECT "SH"."PROMOTIONS"."PROMO_COST" M1, "SH"."PROMOTIONS"."PROMO_ID" M2, "SH"."PROMOTIONS"."PROMO_NAME"
           M3, "SH"."PROMOTIONS"."PROMO_TOTAL" M4 FROM SH.PROMOTIONS;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170000"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170001"
        REFRESH FAST WITH PRIMARY KEY
        ENABLE QUERY REWRITE
        AS SELECT "SH"."CHANNELS"."CHANNEL_DESC" M1, "SH"."CHANNELS"."CHANNEL_ID" M2 FROM
           SH.CHANNELS;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170001"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170002"
        REFRESH FAST WITH PRIMARY KEY
        ENABLE QUERY REWRITE
        AS SELECT "SH"."TIMES"."CALENDAR_YEAR" M1, "SH"."TIMES"."TIME_ID" M2 FROM SH.TIMES;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170002"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170003"
        REFRESH FAST WITH PRIMARY KEY
        ENABLE QUERY REWRITE
        AS SELECT "SH"."CUSTOMERS"."CUST_FIRST_NAME" M1, "SH"."CUSTOMERS"."CUST_GENDER"
           M2, "SH"."CUSTOMERS"."CUST_ID" M3, "SH"."CUSTOMERS"."CUST_LAST_NAME" M4
           FROM SH.CUSTOMERS;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170003"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170004"
        REFRESH FAST WITH PRIMARY KEY
        ENABLE QUERY REWRITE
        AS SELECT "SH"."PRODUCTS"."PROD_DESC" M1, "SH"."PRODUCTS"."PROD_ID" M2, "SH"."PRODUCTS"."PROD_LIST_PRICE"
           M3, "SH"."PRODUCTS"."PROD_MIN_PRICE" M4, "SH"."PRODUCTS"."PROD_NAME" M5,
           "SH"."PRODUCTS"."PROD_TOTAL" M6 FROM SH.PRODUCTS;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170004"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170005"
        REFRESH FAST WITH ROWID
        ENABLE QUERY REWRITE
        AS SELECT SH.SALES.TIME_ID C1, MAX("SH"."SALES"."TIME_ID") M1, COUNT(*) M2 FROM
           SH.SALES GROUP BY SH.SALES.TIME_ID;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170005"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE MATERIALIZED VIEW "SYS"."MV$$_00170006"
        REFRESH FAST WITH ROWID
        ENABLE QUERY REWRITE
        AS SELECT MAX("SH"."SALES"."TIME_ID") M1, COUNT(*) M2 FROM SH.SALES;
    
    
    begin
      dbms_stats.gather_table_stats('"SYS"','"MV$$_00170006"',NULL,dbms_stats.auto_sample_size);
    end;
    /
    
    
    CREATE INDEX "SH"."SALES_IDX$$_00170000"
        ON "SH"."SALES"
        ("TIME_ID")
        COMPUTE STATISTICS
        LOCAL;
    
    
    "
    
    

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-2074386/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-2074386/