LISTING 8: This script, condiAnal.prc, creates the stored procedure condiAnalyze , which updates statistics for use by the Oracle optimizer. It is called by a cron job (Listing 15), which first calls dbAdmCron.sh (Listing 10), which in turn calls condiAnalyze1db.sh (Listing 13), which then calls condiAnalyze. /* Filename : condiAnal.prc Conditional Analyze Author : Julius J.Y. Lin Version : V.2 : 12/31/1996 : combine index with table. V.3 : 02/10/98 : add crontab like date control and analyze level command Purpose : this is an AD HOC procedure for conditional analysis. See Also : ./condi.analyzeV1/condiAnalyzV73.install dba_tab_columns, dba_histograms : for histogram data dba_tables, dba_indexes : for analyzed statistics --*************************************************************************** select_type = 'f' : instr( group, grp_code) > 0 -- force HX group to be -- analyzed. 'F' should be run manually once several months for some tables 'g' : instr( group, grp_code) > 0 -- This is for nightly analysis 'l' : like tbl_start -- Ad hoc 'b' : between tbl_start and tbl_end if sys_analyzed_object has buckets > 1 then the table should use histogram The sequence of parameters affects the caller's default value : eg. you can call condiAnalyze( 'RAMS41') : analyze all user's tables or call condiAnalyze( 'RAMS41', 'A%' ) analyze user RAMS41's tables begin with letter A or call condiAnalyze( 'RAMS41', 'A%','b','CZZZZ') The Between feature is for easier segment condiAnalyze to run in a parallel style. * parameter : if specified. pct_diff overwrite condi_analyze_ctrl.pct_diff, condi_analyze_ctrl.pct_diff overwrite default 5 percent. * index_ratio number default 1.5 : because analyze index uses fewer database resources, and is faster than table. */ CREATE OR REPLACE PROCEDURE condiAnalyze ( p_schema varchar2, p_tbl_start varchar2 default '%', p_select_type varchar2 default 'l', p_tbl_end varchar2 default 'ZZZZZ', p_pct_diff integer default 5, p_pct_estimate integer default 25, p_index_ratio number default 1.5 ) IS v_upc_owner varchar2( 10 ) := upper( p_schema ) ; v_upc_type varchar2(1) := upper( p_select_type ); v_cur_tbl varchar2( 30) ; v_ prev_row_cnt number ; -- rows in dba_tables v_act condi_analyze_ctrl.action%TYPE ; v_hg_buckets condi_analyze_ctrl.hstgrm_buckets%TYPE ; v_chk_days condi_analyze_ctrl.check_days%TYPE ; v_pct_df condi_analyze_ctrl.pct_diff%TYPE ; v_anlvl condi_analyze_ctrl.lvl%TYPE ; v_dow condi_analyze_ctrl.day_of_week%TYPE ; v_dom condi_analyze_ctrl.day_of_month%TYPE ; v_mnth condi_analyze_ctrl.month%TYPE ; v_ s_dow condi_analyze_ctrl.day_of_week%TYPE ; -- 1- 7 from sysdate v_ s_dom condi_analyze_ctrl.day_of_month%TYPE ; -- 1-31 v_ s_mnth condi_analyze_ctrl.month%TYPE ; -- 01-12 v_ level_string condi_analyze_level.command%TYPE ; v_ prev_analyze_date date ; v_actual_row_cnt number ; -- from select count(*) v_ row_count integer ; v_ rows_processed integer ; v_ difference number(10,3) ; v_pct_diff_gap number(10,3) ; v_cnt_cs_id integer ; -- count(*) cursor id v_cs_id integer ; -- tables cursor id v_sql_stmt varchar2(200) ; v_start_time date := sysdate ; v_end_time date ; -- =============== need a dynamic cursor : cursor variable ================ type tbl_cs_type is REF CURSOR ; -- return dba_tables.table_name%TYPE ; tbl_cv tbl_cs_type ; wrong_select_type exception ; BEGIN dbms_output.put_line( 'condiAnalyze ( '|| p_schema ||', '|| p_tbl_start ||', ' ||p_select_type||', '|| p_tbl_end ||', '|| to_char(p_pct_diff)||', ' ||to_char(p_pct_estimate) || ' );' ); IF v_upc_type = 'G' or v_upc_type = 'F' THEN -- regular or force group OPEN tbl_cv FOR select c.object_name, nvl(t.num_rows, 0), c.action, nvl(c.hstgrm_buckets, 1), nvl(c.check_days,0), nvl(c.pct_diff, 5), c.lvl, c.day_of_week, c.day_of_month, c.month from condi_analyze_ctrl c, dba_tables t where c.owner = v_upc_owner and instr( c.anal_group, p_tbl_start ) > 0 and t.owner = c.owner -- all tables in c will have 1 and t.table_name = c.object_name -- in dba_tables order by 1 ; ELSIF v_upc_type = 'L' THEN -- like pattern match OPEN tbl_cv FOR select t.table_name, nvl(t.num_rows, 0), c.action, nvl(c.hstgrm_buckets, 1), nvl(c.check_days,0), nvl(c.pct_diff, 5), c.lvl, c.day_of_week, c.day_of_month, c.month from dba_tables t, condi_analyze_ctrl c where t.owner = v_upc_owner and t.table_name like upper( p_tbl_start) and c.owner (+) = t.owner -- control table may not and c.object_name (+) = t.table_name -- have all tables order by 1 ; ELSIF v_upc_type = 'B' THEN -- between range selection OPEN tbl_cv FOR select t.table_name, nvl(t.num_rows, 0), c.action, nvl(c.hstgrm_buckets, 1), nvl(c.check_days,0), nvl(c.pct_diff, 5), c.lvl, c.day_of_week, c.day_of_month, c.month from dba_tables t, condi_analyze_ctrl c where t.owner = v_upc_owner and t.table_name between upper( p_tbl_start) and upper( p_tbl_end ) and c.owner (+) = t.owner and c.object_name (+) = t.table_name order by 1 ; ELSE raise wrong_select_type ; END IF ; select to_number(to_char(sysdate,'D')), to_number(to_char(sysdate,'DD')), to_number(to_char(sysdate,'MM')) into s_dow,v_ s_dom, v_ s_mnth from dual ; /*========================================================================*/ v_cnt_cs_id := dbms_sql.open_cursor ; v_cs_id := dbms_sql.open_cursor ; -- NOTE !! variable names are shorter than table.column name FETCH tbl_cv INTO v_cur_tbl, v_ prev_row_cnt, v_act, v_hg_buckets, v_chk_days, v_pct_df, v_anlvl, v_dow, v_dom, v_mnth ; -- date inform will override other rule. IF (v_dow =v_ s_dow) OR (v_dom =v_ s_dom and v_mnth is null) OR (v_dom =v_ s_dom and v_mnth = v_ s_mnth) THEN v_ difference := 200 ; -- mark as 200%, force to pass check and ready for analyze v_actual_row_cnt := -200 ; -- mark as -200 goto analyze_it ; END IF ; WHILE tbl_cv%found LOOP IF v_act = 'HX' THEN -- HX:Hard eXclude action, doesn't analyze unless forced -- or specified by day_of_week, or day_of_month IF v_upc_type != 'F' THEN -- or specified by day_of_week, or day_of_month dbms_output.put_line( 'ctrl action=''HX'': Hard eXclude ' || v_cur_tbl || ' from being analyzed' ); goto next_table ; ELSE -- unless use 'F' to force it to be analyzed dbms_output.put_line( '''F''orce HX (Hard eXclude) table '|| v_cur_tbl || ' to be analyzed' ); END IF ; END IF ; IF v_act = 'SX' THEN -- SX Soft eXclude avoid count(*) every day BEGIN -- unless specified by day_of_week, or day_of_month SELECT nvl(end_date, sysdate - v_chk_days) INTO v_ prev_analyze_date FROM sys_analyzed_objects WHERE owner = v_upc_owner AND object_name = v_cur_tbl ; EXCEPTION WHEN OTHERS THEN -- if NULL, then force analyze v_ prev_analyze_date := sysdate - v_chk_days ; END ; IF sysdate < v_ prev_analyze_date + v_chk_days THEN IF v_upc_type != 'F' THEN goto next_table ; ELSE dbms_output.put_line( '''F''orce SX (Soft eXclude) table '|| v_cur_tbl || ' to be analyzed' ); END IF ; ELSE -- sysdate >= v_ prev_analyze_date + v_chk_days, only = happen daily dbms_output.put_line( v_cur_tbl ||': action=SX (Soft eXcluded) ' || ' has reached check analyze days ' ); END IF ; END IF ; /*=====================================================================*/ -- get value for variable :v_actual_row_cnt needed to use dynamic sql -- select count(*) into v_actual_row_cnt from v_cur_tbl; will not work v_sql_stmt := 'select count(*) from ' || p_schema||'.'||v_cur_tbl ; dbms_sql.parse( v_cnt_cs_id, v_sql_stmt, dbms_sql.v7 ); dbms_sql.DEFINE_COLUMN( v_cnt_cs_id, 1, v_ row_count ); -- null stmt v_ rows_processed := dbms_sql.EXECUTE_AND_FETCH( v_cnt_cs_id ); dbms_sql.COLUMN_VALUE( v_cnt_cs_id, 1, v_actual_row_cnt ); /* ===================== decide whether to analyze or not ============ */ -- get v_ difference percentage; if v_chk_days=0, then analyze depend on rows IF v_actual_row_cnt != 0 THEN v_ difference := abs((v_actual_row_cnt -v_ prev_row_cnt)*100/v_actual_row_cnt ); ELSIF v_actual_row_cnt != v_ prev_row_cnt THEN v_ difference := 100 ; -- if v_actual_row_cnt= 0 < v_ prev_row_cnt ELSE v_ difference := 0 ; -- if v_actual_row_cnt =v_ prev_row_cnt = 0 END IF ; -- ============= get precedence of v_pct_diff_gap percentage ============ IF p_pct_diff != 5 then v_pct_diff_gap := p_pct_diff ; ELSE -- else pct_diff = 5, use v_pct_df specified in control table v_pct_diff_gap := v_pct_df ; END IF ; << analyze_it >> v_start_time := sysdate ; -- ============= determine to run analyze or not ================ IF v_ difference > v_pct_diff_gap THEN dbms_output.put_line( rpad(v_cur_tbl,30,' ') ||' '|| chr(9)|| to_char(v_ prev_row_cnt)||' vs '||to_char(v_actual_row_cnt)|| ' : v_ difference % = '|| to_char(v_ difference)||' > '|| to_char(v_pct_diff_gap) ); -- ===================== for table histogram ===================== v_sql_stmt := 'analyze table '|| p_schema||'.'|| v_cur_tbl || ' estimate statistics for table sample ' || to_char(p_pct_estimate) || ' percent'; dbms_output.put_line( v_sql_stmt ) ; execAnalNlog( p_schema, v_cur_tbl, v_start_time, v_cs_id, v_sql_stmt, 'TABLE', v_actual_row_cnt ); -- ================= for all indexed columns histogram ============= select command into v_ level_string from condi_analyze_level where lvl = v_anlvl ; v_sql_stmt := 'analyze table '|| p_schema||'.'|| v_cur_tbl || ' '|| v_ level_string ||' size '|| to_char(v_hg_buckets)||' sample '|| to_char(round(p_pct_estimate * p_index_ratio)) || ' percent'; dbms_output.put_line( v_sql_stmt ) ; v_start_time := sysdate ; execAnalNlog( p_schema, v_cur_tbl, v_start_time, v_cs_id, v_sql_stmt, 'index', v_actual_row_cnt ); ELSE update_analyzed_obj( p_schema, v_cur_tbl, 'TABLE', v_actual_row_cnt, v_start_time, sysdate, 'SKIP'); dbms_output.put_line( rpad(v_cur_tbl,30,' ')||' '||chr(9)|| to_char(v_ prev_row_cnt)|| ' vs ' ||to_char( v_actual_row_cnt)||' : difference % = '|| to_char(v_ difference) || ' <= '|| to_char(p_pct_diff) || ', SKIP' ); END IF ; -- difference > v_pct_diff_gap then << next_table >> FETCH tbl_cv INTO v_cur_tbl, v_ prev_row_cnt, v_act, v_hg_buckets, v_chk_days, v_pct_df, v_anlvl, v_dow, v_dom, v_mnth ; END LOOP ; -- tbl_cv%found loop CLOSE tbl_cv ; dbms_sql.close_cursor( v_cs_id ); dbms_sql.close_cursor( v_cnt_cs_id ); EXCEPTION when wrong_select_type then dbms_output.put_line( 'Wrong Selection : select_type must in [b,g,l]' ); when others then dbms_output.put_line( 'Anal Error: '||substr( SQLERRM, 1, 150)) ; END ; /