auto analyze partition !!

[dbasupuser]

the following SP is not working tho it compiles fine.
Looks like the "for x in select " is not working....any suggestion ??

CREATE OR REPLACE PROCEDURE analyze_partition(
part_name IN VARCHAR2)
IS
TYPE x_type IS REF CURSOR;
x x_type;
x_part_name varchar2(200) := upper(part_name);

BEGIN
dbms_output.put_line('X_PART_NAME: '|| x_part_name);
for x in (select table_name,partition_name from dba_tab_partitions
where table_owner = 'MDM3'
and partition_name like 'x_part_name%'
and last_analyzed is null)
loop
begin
exec sys.dbms_stats.gather_table_stats(ownname => 'MDM3', -
tabname => x.table_name, -
partname => x.partition_name, -
estimate_percent => 30, -
block_sample => false,-
method_opt => 'FOR ALL COLUMNS SIZE 1', -
degree => 8, -
granularity => 'default', -
cascade => true , -
stattab => null, -
statid => null, -
statown => null);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;

[tamilselvan]

Remove exec before sys.dbms_stats.
It is not needed if it is called between begin and end.

Tamil

[dbasupuser]

Thanks..makes sence.

It is not getting inside the loop...is the declare for x and x_type ok ?is there abetter way to declare cur type ?

[tamilselvan]

You do not need REF Cursor because you are not returning any thing.

Also, "-" is not needed. For example: tabname => x.table_name, -

Tamil

[dbasupuser]

It is not getting in the loop....tho values exist...


1 CREATE OR REPLACE PROCEDURE analyze_partition(
2 part_name IN VARCHAR2)
3 IS
4 TYPE x_type IS REF CURSOR;
5 x x_type;
6 x_part_name varchar2(200) := upper(part_name);
7 BEGIN
8 dbms_output.put_line('X_PART_NAME: '|| x_part_name);
9 for x in (select table_name,partition_name from dba_tab_partitions
10 where table_owner = 'MDM3'
11 and partition_name like 'x_part_name%'
12 and last_analyzed is null)
13 loop
14 begin
15 dbms_output.put_line('TABLE : ' || x.table_name);
16 dbms_output.put_line('PART_NAME : ' || x.partition_name);
17 dbms_output.put_line('TESTING ');
18 /*
19 sys.dbms_stats.gather_table_stats(ownname => 'MDM3', -
20 tabname => x.table_name, -
21 partname => x.partition_name, -
22 estimate_percent => 30, -
23 block_sample => false,-
24 method_opt => 'FOR ALL COLUMNS SIZE 1', -
25 degree => 8, -
26 granularity => 'default', -
27 cascade => true , -
28 stattab => null, -
29 statid => null, -
30 statown => null);
31 */
32 exception
33 when others then
34 dbms_output.put_line('### ERROR: ' || sqlerrm);
35 end;
36 dbms_output.put_line('TESTING ');
37 end loop;
38* end;
SQL> /

Procedure created.

SQL>
SQL>
SQL>
SQL> exec analyze_partition('MTH2005JAN');
X_PART_NAME: MTH2005JAN

PL/SQL procedure successfully completed.

SQL>

1 select table_name,partition_name from dba_tab_partitions
2 where table_owner = 'MDM3'
3 and partition_name like 'MTH2005JAN%'
4* and last_analyzed is null
SQL> /

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_ENTITY_DIM MTH2005JAN_DIM_ENTITY
T_MODEL_DIM MTH2005JAN_DIM_MODEL
T_MILN_XTRCT MTH2005JAN_FACT_MILN
T_MLS_XTRCT MTH2005JAN_FACT_MLS
T_MMBA_XTRCT MTH2005JAN_FACT_MMBA
T_MMER_XTRCT MTH2005JAN_FACT_MMER
T_MMTG_XTRCT MTH2005JAN_FACT_MMTG
T_MPCB_XTRCT MTH2005JAN_FACT_MPCB

8 rows selected.

SQL>
SQL>

[slimdave]

Line 11

and partition_name like x_part_name||'%'

[DaPi]

and partition_name like 'x_part_name%'
should be
and partition_name like x_part_name||'%'

[slimdave]

and partition_name like 'x_part_name%'
should be
and partition_name like x_part_name||'%'

Your powers are weak, old man.

[DaPi]

I'm taking a more leisurely approach to life. And if you can only beat me to it by a matter of seconds, you'd better look to your laurels, young whipper-snapper.

[mike9]

Wouldn't it make sense to simply use dbms_stats?
I think GATHER_SCHEMA_STATS with the parameter OPTIONS set to GATHER AUTO, GATHER STALE or GATHER EMPTY would be a nicer solution.