LISTING 4: This script, condiAnalyze.install, installs all of the scripts needed for recurring analysis and histogram updates. /* # Filename : condiAnalyze.install CONDItion ANALYZE installation procedure # Author : Julius Lin 11/18/96 # Purpose : create environment for database procedure condiAnalyze() # This script checks the current statistics recorded in dba_tables and the actual # rows for the target table, if the difference is greater than specified percent x # ( default 5%), then analyze with estimated percent y ( default 25% ), with # (table% * ratio) for index analyzed, eg. 25 * 1.5 = 38 % # # Usage : sqlplus sys/passwd @condiAnalyze.install # in sqlplus do : exec condiAnalyze( 'sdg','product%') ; # Note !! 0. ALWAYS MAKE another version for V.7.2 named condiAnalyzV72.install by deleting 'for table' from analyze table statement. This procedure should be run by SYS as default. Anyone else without grant privilege directly will get compile error when trying to create or replace procedure condiAnalyze, although PL/SQL non-named block version still works under sqlplus user SYSTEM. This is because under Oracle, the stored procedure object privilege has to be granted to the user directly. Grant through ROLE will not work under stored procedure. 2. Before calling this procedure in sqlplus : set serveroutput on size 20000 so you can redirect the output to a log file for review. 3. Change the last section for appropriate users in the database. # See Also : updAnaObj.prc, condiAnal.prc, dba_tab_columns, dba_tables|indexes ==================================================================== */ prompt You must login to Oracle as user SYS to make this installation work prompt or else hit CTRL-C to stop and exit SQLPLUS accept dummy -- enable next line only during test installation -- WHENEVER SQLERROR EXIT SQL.SQLCODE ; /* ==================== table for histogram control ========================== Use skewDscv.sql to find out which tables need to do histogram. Use condi_analyze_ctrl to CONTROL conditional Analyzer, Column Descriptions: 3. group : parallel analyze or operational group ID, each group is represented by a char. A table can belong to multiple groups. Make sure there is no duplication. CHAR=[0-9]+[A-Z]+[a-z]+[other ascii -[%_]] WHERE: a-z are reserved for nightly analyze process which are based on row_count and weight by analyzed_count. A-Z are reserved for operational group in application. eg. sale_update, account_receivable, account_payable ... Every table belongs to '*' which means all groups. Group should add a check ( condition ) for no duplication. 4. action : SX = Soft eXclude from analyze, but still controlled by check_days. Assign to medium size and very static tables HX = hard exclude from being analyzed. Assign to very large tables, say 4 million rows and above. This type of table needs to be analyzed less frequently by a schedule. you define what action to take as long as it doesn't mess up the statistics. Remember, statistics just need to be able to cause the optimizer to make the right decision. 5. level : level of details for analyze. See table condi_analyze_level * Need to have a SQL*FORM V.4.5 for maintenance of table condi_analyze_ctrl and a SQL*LOAD control file for initial load. NOTE !! I use OBJECT instead of TABLE because I want to preserve the flexibility, in case some condition occurs that requires the analyze of an index to be separated from the analyze of a table adjust values in condi_analyze_ctrl by comparing fields in dba_tab_columns, such as NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER these fields record the actual values after 'analyze' is complete, while table condi_analyze_ctrl specify target values. */ -- could possible explore down to column level of analyze. column_name VARCHAR2(30) drop TABLE condi_analyze_ctrl ; CREATE TABLE condi_analyze_ctrl ( owner VARCHAR2(10) NOT NULL, object_name VARCHAR2(30) NOT NULL, anal_group VARCHAR2(20), -- check no duplicated char action VARCHAR2(3), hstgrm_buckets NUMBER(3) default 75, -- histogram bucket size check_days NUMBER(3) default 0 check ( check_days >= 0 ), pct_diff NUMBER(2), -- overwrite default percentage if exist lvl NUMBER(2), -- level, see sys.condi_analyze_level day_of_week NUMBER(1), -- Force analyze on a weekly base day_of_month NUMBER(2), -- If non-null, analyze monthly month NUMBER(2) -- If non-null, further restrict MM/DD ) STORAGE( INITIAL 64K NEXT 32K PCTINCREASE 0 ) TABLESPACE DATA ; ALTER TABLE condi_analyze_ctrl ADD CONSTRAINT condi_analyze_ctrl_PK PRIMARY KEY ( OWNER, OBJECT_NAME ) USING INDEX STORAGE ( INITIAL 32K NEXT 16K PCTINCREASE 0 ) TABLESPACE INDX ; /* -------------------------------------------------------------------------- condi_analyze_level only affectS the CAPITAL PORTION of the 2nd analyze when analyze command is executed, eg. if level=3, it will generate next two analyze commands : analyze table schema.table estimate statistics for table sample 25 percent analyze table schema.table ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS size 1 sample 38 percent */ drop TABLE condi_analyze_level; CREATE TABLE condi_analyze_level ( lvl NUMBER(2), command varchar2(50) ) STORAGE( INITIAL 16K NEXT 16K PCTINCREASE 0 ) -- 1 oracle block only TABLESPACE DATA CACHE -- because it is so small and used frequently ; ALTER TABLE condi_analyze_level ADD CONSTRAINT condi_analyze_level_PK PRIMARY KEY ( lvl ) USING INDEX STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0 ) TABLESPACE INDX ; /* level 2,3 and 6,7 are the most appropriate option. Any of the following clauses will update table dba_histograms. for tables only will update columns in data dictionary DBA_TABLES, eg. NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN */ insert into condi_analyze_level values ( 1, 'ESTIMATE STATISTICS FOR TABLE' ) ; /* for indexes only, view entire index as a whole, update columns in DBA_INDEXES, eg. BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR */ insert into condi_analyze_level values ( 2, 'ESTIMATE STATISTICS FOR ALL INDEXES' ) ; /* for indexed columns, each column in the INDEXES has statistics, it will lookup DBA_IND_COLUMNS and then update columns in DBA_TAB_COLUMNS, eg. NUM_DISTINCT, LOW_VALUE, HIGH_VALUE , DENSITY NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE */ insert into condi_analyze_level values ( 3, 'ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS' ) ; /* for columns, each column in the TABLE has statistics. update dba_tab_columns to include non-indexed columns */ insert into condi_analyze_level values ( 4, 'ESTIMATE STATISTICS FOR ALL COLUMNS' ) ; /* if you try to verify my explanation in Fig. 3, then compute is necessary. Your SQL will look like select count(*) from &table where column between &previous_bucket_value +1 and ¤t_bucket_endpoint_value. for table only */ insert into condi_analyze_level values ( 5, 'COMPUTE STATISTICS FOR TABLE' ) -- not used currently ; -- for indexes only, view entire index as a whole insert into condi_analyze_level values ( 6, 'COMPUTE STATISTICS FOR ALL INDEXES' ) ; -- for every indexed column in the INDEXES defined in dba_ind_columns insert into condi_analyze_level values ( 7, 'COMPUTE STATISTICS FOR ALL INDEXED COLUMNS' ) ; -- for every column in the TABLE defined in dba_tab_columns insert into condi_analyze_level values ( 8, 'COMPUTE STATISTICS FOR ALL COLUMNS' ) ; -- ================== log table for conditional analyze control ================ -- LOG table for condition analyzer DROP TABLE sys_ANALYZED_OBJECTS ; CREATE TABLE sys_ANALYZED_OBJECTS ( OWNER VARCHAR2(10) NOT NULL, OBJECT_NAME VARCHAR2(34) NOT NULL, -- table_name||'*NDX' for indexes OBJECT_TYPE VARCHAR2( 7) NOT NULL, ROW_COUNT INTEGER, -- physical row count(*) STARTED_DATE DATE, END_DATE DATE, -- NULL if count(*) only w/o analyzing STATUS VARCHAR2( 7) NOT NULL, ANALYZED_COUNT INTEGER, -- indicates object activity LAST_1_ANALYZED DATE, LAST_2_ANALYZED DATE, LAST_3_ANALYZED DATE ) STORAGE( INITIAL 128K NEXT 64K PCTINCREASE 0 ) TABLESPACE DATA ; ALTER TABLE sys_ANALYZED_OBJECTS ADD CONSTRAINT sys_ANALYZED_OBJECTS_PK PRIMARY KEY ( OWNER, OBJECT_NAME, OBJECT_TYPE ) USING INDEX STORAGE ( INITIAL 64K NEXT 32K PCTINCREASE 0 ) TABLESPACE INDX ; -- GRANT SELECT ON sys_ANALYZED_OBJECTS TO READONLY_USER ; -- *************************************************************************** CREATE or REPLACE FORCE VIEW V_sys_ANALYZED_OBJECTS AS SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, ROW_COUNT, substr(to_char( STARTED_DATE, 'YY-MM-DD HH24:MI:SS'),1,18) START_TIME, substr(to_char( END_DATE, 'YY-MM-DD HH24:MI:SS'),1,18) END_TIME, STATUS, ANALYZED_COUNT, substr(to_char(LAST_1_ANALYZED,'YY-MM-DD HH24:MI:SS'),1,18) LAST_1_ANALYZED, substr(to_char(LAST_2_ANALYZED,'YY-MM-DD HH24:MI:SS'),1,18) LAST_2_ANALYZED, substr(to_char(LAST_3_ANALYZED,'YY-MM-DD HH24:MI:SS'),1,18) LAST_3_ANALYZED FROM sys_ANALYZED_OBJECTS ; -- *********************** create procedure UPDATE_ANALYZED_OBJ ************** @updAnaObj.prc @execAnalNlog.prc /* ************* Initialize an ordered rows based on table/index ************* orderAnalObjs.crt depends on UPDATE_ANALYZED_OBJ() */ @orderAnalObjs.crt -- ====================== conditional analysis statistics ==================== @condiAnal.prc @reCondiAnal.prc -- ************************************************************************** DROP PUBLIC SYNONYM condi_analyze_ctrl ; DROP PUBLIC SYNONYM condi_analyze_level ; DROP PUBLIC SYNONYM sys_ANALYZED_OBJECTS ; DROP PUBLIC SYNONYM v_sys_ANALYZED_OBJECTS ; DROP PUBLIC SYNONYM execAnalNlog ; DROP PUBLIC SYNONYM update_analyzed_obj ; DROP PUBLIC SYNONYM condiAnalyze ; DROP PUBLIC SYNONYM reCondiAnalyze ; create public synonym CONDI_ANALYZE_CTRL for CONDI_ANALYZE_CTRL ; create public synonym CONDI_ANALYZE_LEVEL for CONDI_ANALYZE_LEVEL ; create public synonym sys_ANALYZED_OBJECTS for sys_ANALYZED_OBJECTS ; create public synonym v_sys_ANALYZED_OBJECTS for v_sys_ANALYZED_OBJECTS ; create public synonym execAnalNlog for execAnalNlog ; create public synonym UPDATE_ANALYZED_OBJ for UPDATE_ANALYZED_OBJ ; create public synonym condiAnalyze for condiAnalyze ; create public synonym reCondiAnalyze for reCondiAnalyze ; -- ************************************************************************** set serveroutput on size 20000 DECLARE PROCEDURE grant_anal_obj( usr varchar2 ) IS v_cs_id integer ; BEGIN v_cs_id := dbms_sql.open_cursor ; dbms_sql.parse( v_cs_id, 'grant all on CONDI_ANALYZE_CTRL to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant all on CONDI_ANALYZE_LEVEL to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant all on sys_ANALYZED_OBJECTS to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant select on v_sys_ANALYZED_OBJECTS to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant execute on UPDATE_ANALYZED_OBJ to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant execute on condiAnalyze to '|| usr, dbms_sql.v7 ); dbms_sql.parse( v_cs_id, 'grant execute on reCondiAnalyze to '|| usr, dbms_sql.v7 ); dbms_sql.close_cursor( v_cs_id ); EXCEPTION when others then dbms_output.put_line( 'GRANT_ANAL_OBJ: '||substr( SQLERRM, 1, 150)) ; END ; BEGIN grant_anal_obj( 'system' ) ; -- grant_anal_obj( 'ops' ) ; -- for rampr, sdgpr only grant_anal_obj( '&&majorUsr' ) ; END ; / ! echo " Please type password for Oracle user SYS \c" ! sqlldr sys control=condiAnalCtrl.ctl data=../RAMS/condiAnalCtrl.data log=condiAnalCtrl.log select object_name, status from V_sys_ANALYZED_OBJECTS ;