Bo's Oracle Station

查看: 2492|回复: 0

课程第11/12次(2018-07-09星期一,2018-07-13星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-9 19:49:34 | 显示全部楼层 |阅读模式
制造SAA新模板:

  1. variable     l_task_id number;
  2. variable     l_name varchar2(30);


  3. Rem make a new template to have a minimum of 100 rows only for partitioning

  4. begin
  5. :l_name := 'SQLACCESS_PARTITION_SMALL';
  6. end;
  7. /

  8. begin
  9. dbms_advisor.update_task_attributes(:l_name,null,null,'FALSE');
  10. dbms_advisor.delete_task(:l_name);
  11. end;
  12. /

  13. begin
  14. dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,:l_task_id,:l_name,'For demo purposes only');
  15. dbms_advisor.set_task_parameter(:l_name,'_PA_MIN_TABLE_SIZE_PART',100);
  16. dbms_advisor.set_task_parameter(:l_name,'EXECUTION_TYPE','FULL');
  17. dbms_advisor.set_task_parameter(:l_name,'ANALYSIS_SCOPE','ALL');
  18. dbms_advisor.set_task_parameter(:l_name,'MODE','COMPREHENSIVE');

  19. dbms_advisor.update_task_attributes(:l_name,null,null,'TRUE','TRUE');
  20. end;
  21. /
复制代码

Tasks and Templates
View


SelectNameDescriptionLast ModifiedType

SQLACCESS_EMTASKDefault Enterprise Manager task templateJul 10, 2018 6:06:22 AM CSTDefault Template

SQLACCESS_GENERALGeneral purpose database templateJul 10, 2018 6:06:21 AM CSTTemplate

SQLACCESS_OLTPOLTP database templateJul 10, 2018 6:06:21 AM CSTTemplate

SQLACCESS_PARTITION_SMALLFor demo purposes onlyJul 10, 2018 6:28:29 AM CSTTemplate

SQLACCESS_WAREHOUSEData Warehouse database templateJul 10, 2018 6:06:22 AM CSTTemplate

以下是SAA的典型的建议(数据仓库模板):
Rem  SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem  
Rem  Username:        SYS
Rem  Task:            SQLACCESS1553744
Rem  Execution date:   
Rem  

Rem  
Rem  Repartitioning table "SH"."CUSTOMERS"
Rem  

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
  
CREATE TABLE "SH"."CUSTOMERS1"
   
(    "CUST_ID" NUMBER,
   
"CUST_FIRST_NAME" VARCHAR2(20),
   
"CUST_LAST_NAME" VARCHAR2(40),
   
"CUST_GENDER" CHAR(1),
   
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
   
"CUST_MARITAL_STATUS" VARCHAR2(20),
   
"CUST_STREET_ADDRESS" VARCHAR2(40),
   
"CUST_POSTAL_CODE" VARCHAR2(10),
   
"CUST_CITY" VARCHAR2(30),
   
"CUST_CITY_ID" NUMBER,
   
"CUST_STATE_PROVINCE" VARCHAR2(40),
   
"CUST_STATE_PROVINCE_ID" NUMBER,
   
"COUNTRY_ID" NUMBER,
   
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25),
   
"CUST_INCOME_LEVEL" VARCHAR2(30),
   
"CUST_CREDIT_LIMIT" NUMBER,
   
"CUST_EMAIL" VARCHAR2(30),
   
"CUST_TOTAL" VARCHAR2(14),
   
"CUST_TOTAL_ID" NUMBER,
   
"CUST_SRC_ID" NUMBER,
   
"CUST_EFF_FROM" DATE,
   
"CUST_EFF_TO" DATE,
   
"CUST_VALID" VARCHAR2(1)
   
) SEGMENT CREATION IMMEDIATE
  
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  
TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("CUST_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
      
);

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_ID" IS 'primary key';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_FIRST_NAME" IS 'first name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_LAST_NAME" IS 'last name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_GENDER" IS 'gender; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_YEAR_OF_BIRTH" IS 'customer year of birth';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MARITAL_STATUS" IS 'customer marital status; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STREET_ADDRESS" IS 'customer street address';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_POSTAL_CODE" IS 'po
stal code of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CITY" IS '
city where the customer lives';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STATE_PROVINCE" IS '
customer geography: state or province';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."COUNTRY_ID" IS '
foreign key to the countries table (snowflake)';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MAIN_PHONE_NUMBER" IS '
customer main phone number';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_INCOME_LEVEL" IS '
customer income level';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CREDIT_LIMIT" IS '
customer credit limit';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_EMAIL" IS '
customer email id';

COMMENT ON TABLE "SH"."CUSTOMERS1"  IS '
dimension table';

Rem
Rem Copying constraints to new partitioned table
Rem
  ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_PK1" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE;

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_ID" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_FIRST_NAME" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_LAST_NAME" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_GENDER" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_YEAR_OF_BIRTH" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STREET_ADDRESS" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_POSTAL_CODE" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY_ID" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE_ID" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_MAIN_PHONE_NUMBER" NO

T NULL ENABLE);

  
ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);

  
ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
  
ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_COUNTRY_FK1" FOREIGN KEY ("COUNTRY_ID")
      
REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE;

Rem
Rem Copying indexes to new partitioned table
Rem
  
CREATE UNIQUE INDEX "SH"."CUSTOMERS_PK1" ON "SH"."CUSTOMERS1" ("CUST_ID")
  
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  
TABLESPACE "EXAMPLE" ;

  
CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX1" ON "SH"."CUSTOMERS1" ("CUST_GENDER")
  
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  
TABLESPACE "EXAMPLE" LOCAL;

  
CREATE BITMAP INDEX "SH"."CUSTOMERS_MARITAL_BIX1" ON "SH"."CUSTOMERS1" ("CUST_MARITAL_STATUS")
  
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  
TABLESPACE "EXAMPLE" LOCAL;

  
CREATE BITMAP INDEX "SH"."CUSTOMERS_YOB_BIX1" ON "SH"."CUSTOMERS1" ("CUST_YEAR_OF_BIRTH")
  
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  
TABLESPACE "EXAMPLE" LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON "SH"."CUSTOMERS1" TO "BI";

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO "SH"."CUSTOMERS1"
   
SELECT * FROM "SH"."CUSTOMERS";
COMMIT;

begin
  
dbms_stats.gather_table_stats('"SH"', '"CUSTOMERS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE "SH"."CUSTOMERS" RENAME TO "CUSTOMERS11";
ALTER TABLE "SH"."CUSTOMERS1" RENAME TO "CUSTOMERS";

Rem
Rem Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION "SH"."CUSTOMERS_DIM" COMPILE;


CREATE MATERIALIZED VIEW LOG ON
   
"SH"."PRODUCTS"
   
WITH ROWID, SEQUENCE("PROD_ID","SUPPLIER_ID","PROD
_LIST_PRICE")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "
SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("
CUST_ID","CUST_GENDER","CUST_YEAR_OF_BIRTH","CUST_CREDIT_LIMIT")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "
SH"."SALES"
    WITH ROWID, SEQUENCE("
PROD_ID","CUST_ID","TIME_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "
SYS"."MV$$_009C0000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.CUSTOMERS.CUST_CREDIT_LIMIT C1, SH.CUSTOMERS.CUST_YEAR_OF_BIRTH C2,
       SH.CUSTOMERS.CUST_GENDER C3, COUNT(*) M1 FROM SH.CUSTOMERS, SH.SALES WHERE
       SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.CUSTOMERS.CUST_CREDIT_LIMIT
       <= 14962) GROUP BY SH.CUSTOMERS.CUST_CREDIT_LIMIT, SH.CUSTOMERS.CUST_YEAR_OF_BIRTH,
       SH.CUSTOMERS.CUST_GENDER;

begin
  dbms_stats.gather_table_stats('"
SYS"','"MV$$_009C0000"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "
SYS"."MV$$_009C0001"
PARTITION BY RANGE ("
C3") INTERVAL( NUMTOYMINTERVAL( 1, 'MONTH')) ( PARTITION
       VALUES LESS THAN (TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
       , 'NLS_CALENDAR=GREGORIAN')) )
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, SH.SALES.TIME_ID
       C3, COUNT(*) M1 FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
       GROUP BY SH.PRODUCTS.PROD_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID, SH.SALES.TIME_ID;

begin
  dbms_stats.gather_table_stats('"
SYS"','"MV$$_009C0001"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "
SYS"."MV$$_009C0002"
PARTITION BY RANGE ("
C1") INTERVAL( 40) ( PARTITION VALUES LESS THAN (40) )
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, SH.SALES.CUST_ID
       C3, COUNT(*) M1 FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
       GROUP BY SH.PRODUCTS.PROD

_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID, SH.SALES.CUST_ID;

begin
  
dbms_stats.gather_table_stats('"SYS"','"MV$$_009C0002"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SYS"."MV$$_009C0003"
   
REFRESH FAST WITH ROWID
   
ENABLE QUERY REWRITE
   
AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, COUNT(*) M1
      
FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
      
GROUP BY SH.PRODUCTS.PROD_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID;

begin
  
dbms_stats.gather_table_stats('"SYS"','"MV$$_009C0003"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX "SYS"."MV$$_009C0002_IDX$$_009C0000"
   
ON "SYS"."MV$$_009C0001"
   
("C3")
   
COMPUTE STATISTICS;

CREATE INDEX "SH"."PRODUCTS_IDX$$_009C0001"
   
ON "SH"."PRODUCTS"
   
("PROD_LIST_PRICE","SUPPLIER_ID","PROD_NAME")
   
COMPUTE STATISTICS;

CREATE INDEX "SYS"."MV$$_009C0004_IDX$$_009C0002"
   
ON "SYS"."MV$$_009C0002"
   
("C1")
   
COMPUTE STATISTICS
   
LOCAL;

DROP MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV";

DROP MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV";

DROP INDEX "SH"."SALES_PROD_BIX";

DROP INDEX "SH"."SALES_CUST_BIX";

DROP INDEX "SH"."SALES_TIME_BIX";

DROP INDEX "SH"."SALES_CHANNEL_BIX";

DROP INDEX "SH"."SALES_PROMO_BIX";

DROP INDEX "SH"."PRODUCTS_PROD_SUBCAT_IX";

DROP INDEX "SH"."PRODUCTS_PROD_CAT_IX";

DROP INDEX "SH"."PRODUCTS_PROD_STATUS_BIX";

DROP INDEX "SH"."CUSTOMERS_GENDER_BIX";

DROP INDEX "SH"."CUSTOMERS_MARITAL_BIX";

DROP INDEX "SH"."CUSTOMERS_YOB_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_SUBCAT_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_CHAN_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_PROMO_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_WD_BIX";

相同的SQL Tunning Set用OLTP模板建议就会完全不一样。



a.png
b.png
c.png
d.png
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 12:45 , Processed in 0.058686 second(s), 27 queries .

快速回复 返回顶部 返回列表