askMLabs

SQL> conn sh/sh
Connected

SQL> sho user
USER is "SH"
SQL> execute dbms_advisor.create_task ( 'SQL Access Advisor','SQL_ACC_TASK1','New SQLAccess  Task');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.reset_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

SQL> create table temp_table AS SELECT * FROM SYS.WRI$_ADV_SQLW_STMTS WHERE NULL IS NOT NULL;

Table created.

SQL> create table sql_access_temp_table (c number, d varchar2(1000));

Table created.

SQL> begin
for i in 1..20000 loop
insert into sql_access_temp_table values(-i,'aoiejnflamnskdfjnsijndfklsjpoakneflkajsdfkjankdsnflkasjdnfkjasndjkfnjdklfsbkbsdnbaiuebdfiausdybfaouisdbflkjabdoiufbaklsdjfaksdiufakjsdofiuasdjfalkjsdfdjkhfakjshsjdfkjasksdfkjalkaksdjhfkjaakjdsfsdjfklasjdlkadfoiuaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklk');
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> create table customers_askm as select * from customers;

Table created.

SQL> @askm_sts.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.add_sqlwkld_ref('SQL_ACC_TASK1','SQLSET_MY_SQLACCESS_WORKLOAD',1);

PL/SQL procedure successfully completed.

SQL> @askm_param.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('SQL_ACC_TASK1');

PL/SQL procedure successfully completed.

$ cat askm_sts.sql
DECLARE
sql_stmt   varchar2(2000);
sqlsetname  VARCHAR2(30);
sqlsetcur   dbms_sqltune.sqlset_cursor;
refid    NUMBER;
k NUMBER := 0;
num_queries NUMBER := 500;
BEGIN

sql_stmt := 'SELECT /* QueryASKM 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-01'',''1999-02'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA'' AND ch.channel_desc in (''Internet'',''Catalog'') AND t.calendar_quarter_desc IN (''1999-03'',''1999-04'') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 4 */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'select /* func_indx */ count(*) from sql_access_temp_table where abs(c)=5';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sql_stmt := 'SELECT /* QueryASKM 5 */ * FROM sh.customersjfv WHERE cust_state_province = ''CA''';

insert into temp_table values(1,1,NULL,0,'SH','Access Advisor','Workload',0,0,0,0,1,100,2,to_date('02-FEB-2007'),3,0,sql_stmt,1);

sqlsetname := 'SQLSET_MY_SQLACCESS_WORKLOAD';

dbms_sqltune.create_sqlset(sqlsetname, 'Generated STS');

OPEN sqlsetcur FOR
SELECT
SQLSET_ROW(null,null, sql_text, null, null, username, module,
action, elapsed_time, cpu_time, buffer_gets, disk_reads,
0,rows_processed, 0, executions, 0, optimizer_cost, null,
priority, command_type,
to_char(last_execution_date,'yyyy-mm-dd/hh24:mi:ss'),
0,0,NULL,0,NULL,NULL
)
FROM temp_table;

dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
END;
/

$

$ cat askm_param.sql
/* Set STS Workload Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','SQL_LIMIT','25');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','INVALID_SQLSTRING_LIST','"@!"');

/* Set Task Parameters */
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','ANALYSIS_SCOPE','ALL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','TIME_LIMIT',10000);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','MODE','LIMITED');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DML_VOLATILITY','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','WORKLOAD_SCOPE','PARTIAL');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','CREATION_COST','TRUE');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','JOURNALING','4');
exec dbms_advisor.set_task_parameter('SQL_ACC_TASK1','DAYS_TO_EXPIRE','30');

$

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

SCRIPT
--------------------------------------------------------------------------------
Rem  SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            SQL_ACC_TASK1
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 maritalstatus; low cardinality attribute';

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

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_POSTAL_CODE" IS 'postal 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 mainphone 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" NOT 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"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY","CUST_STATE_PROVINCE")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."CHANNELS"
WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_DESC","CHANNEL_CLASS")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH"."TIMES"
WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
INCLUDING NEW VALUES;

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

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH
.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM("
SH"."SALES"."AMOUNT_SOLD")
M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.C
HANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOME
RS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN ('1999-04', '1999-03', '1999-02'
, '1999-01')) AND (SH.CHANNELS.CHANNEL_DESC IN ('Internet', 'Catalog'
)) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = 'CA') GROUP BY SH.CUSTOMERS.CU
ST_STATE_PROVINCE,
SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS, SH.CHANNELS.CHANNEL_DESC,
SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats('"SH"','"MV$$_004D0000"',NULL,dbms_stats.auto_sa
mple_size);
end;
/

CREATE MATERIALIZED VIEW "SH"."MV$$_004D0001"
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT "SH"."CUSTOMERS"."COUNTRY_ID" M1, "SH"."CUSTOMERS"."CUST_CITY" M2,
"SH"."CUSTOMERS"."CUST_LAST_NAME"
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

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

CREATE INDEX "SH"."CUSTOMERS_IDX$$_004D0000" ON "SH"."CUSTOMERS" ("COUNTRY_ID","CUST_CITY","CUST_LAST_NAME") COMPUTE STATISTICS;

SQL>