What's in analyze_my.bbtabs (BB9.1) ?

select OWNER, NAME from dba_source where name like '%analy%'


SQL> desc bb_bb60.analyze_my
PROCEDURE ANALYZE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABNAME VARCHAR2 IN
PROCEDURE BBTABS


Checking under the hood

SQL> select text from dba_source where name = 'ANALYZE_MY' and owner = 'BB_BB60';


TEXT
--------------------------------------------------------------------------------
PACKAGE analyze_my AS

PROCEDURE bbtabs;
PROCEDURE analyze_table(tabname VARCHAR2);

END analyze_my;
PACKAGE BODY analyze_my AS

PROCEDURE parse_n_execute_ddl(strng VARCHAR2) IS
c1 NUMBER;
r1 NUMBER;

TEXT
--------------------------------------------------------------------------------
BEGIN
c1 := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c1,strng,DBMS_SQL.NATIVE);
r1 := DBMS_SQL.EXECUTE(c1);
DBMS_SQL.CLOSE_CURSOR(c1);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(c1);
END parse_n_execute_ddl;

PROCEDURE bbtabs

TEXT
--------------------------------------------------------------------------------
IS
db_block_size_v NUMBER;
db_block_buffers_v NUMBER;
cache_size_v NUMBER;
strng VARCHAR2(100);
p_large_thresh NUMBER := 10000000;
p_degree_large NUMBER := null;
p_degree NUMBER := null;
p_est_pct_large NUMBER := 20;
p_est_pct NUMBER := null;


TEXT
--------------------------------------------------------------------------------
-- Cursor to list all tables that appear to have no rows and which haven't b
een analyzed in the past day

CURSOR c_zerotabs IS SELECT table_name FROM user_tables WHERE num_rows = 0 A
ND last_analyzed < (SYSDATE -1); -- Cursor to look for large tables exceeding the table monitoring first CURSOR c_large_tab (cp_thresh NUMBER) IS SELECT m.table_name FROM user_tab_modifications m, user_tables t WHERE TEXT -------------------------------------------------------------------------------- t.table_name = m.table_name AND t.num_rows > 0
AND (m.inserts + m.updates + m.deletes)/t.num_rows > 0.095
AND t.num_rows > cp_thresh;

BEGIN
-- Proactively gather stats for large tables that are getting close, so that th
ey can be estimated

FOR r_large_tab IN c_large_tab (p_large_thresh) LOOP
dbms_stats.gather_table_stats(

TEXT
--------------------------------------------------------------------------------
ownname => NULL,
tabname => r_large_tab.table_name,
estimate_percent => p_est_pct_large,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree_large,
cascade => TRUE
);
END LOOP;

-- Gather stats where none exist

TEXT
--------------------------------------------------------------------------------
dbms_stats.gather_schema_stats(
ownname => NULL,
estimate_percent => p_est_pct,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree,
options => 'GATHER EMPTY',
cascade => TRUE
);

-- Gather stats for those that are stale (i.e. have seen signficant change)

TEXT
--------------------------------------------------------------------------------
dbms_stats.gather_schema_stats(
ownname => NULL,
estimate_percent => p_est_pct,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => p_degree,
options => 'GATHER STALE',
cascade => TRUE
);

-- Gather stats for those tables which stats show are empty, since this is s

TEXT
--------------------------------------------------------------------------------
ometimes wrong and can lead to problems if it is

FOR r_zerotabs IN c_zerotabs LOOP
dbms_stats.gather_table_stats(
ownname => NULL,
tabname => r_zerotabs.table_name,
estimate_percent => NULL,
granularity => 'ALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => NULL,
cascade => TRUE

TEXT
--------------------------------------------------------------------------------
);
END LOOP;

BEGIN
SELECT value
INTO db_block_size_v
FROM v$parameter
WHERE name = 'db_block_size';
SELECT value
INTO db_block_buffers_v
FROM v$parameter

TEXT
--------------------------------------------------------------------------------
WHERE name = 'db_block_buffers';
cache_size_v := (db_block_size_v*db_block_buffers_v)/10;
EXCEPTION
WHEN others THEN
db_block_size_v := 8192;
cache_size_v := 999999999;
END;

FOR c2 IN (SELECT table_name
FROM user_tables
WHERE cache = ' N'

TEXT
--------------------------------------------------------------------------------
AND NVL(blocks,999999999)*db_block_size_v <= cache_size_v) LOOP BEGIN strng := 'ALTER TABLE '||c2.table_name||' CACHE'; parse_n_execute_ddl(strng); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; TEXT -------------------------------------------------------------------------------- FOR c3 IN (SELECT table_name FROM user_tables WHERE cache = ' Y' AND NVL(blocks,0)*db_block_size_v > cache_size_v)
LOOP
BEGIN
strng := 'ALTER TABLE '||c3.table_name||' NOCACHE';
parse_n_execute_ddl(strng);
EXCEPTION
WHEN OTHERS THEN
NULL;

TEXT
--------------------------------------------------------------------------------
END;
END LOOP;

END bbtabs;

PROCEDURE analyze_table(tabname varchar2) IS
strng VARCHAR2(100);
BEGIN

BEGIN
strng := 'dbms_stats.gather_table_stats(tabname=>'''||tabname||''',

TEXT
--------------------------------------------------------------------------------
method_opt=>''for all indexed columns size auto'',estimate_percent=>100)';

parse_n_execute_ddl(strng);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END analyze_table;

END analyze_my;

154 rows selected.