在EM中使用SQL Access Advisor（SQL訪問建議）優化SQL

hooca發表於2016-04-03
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

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章