DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: auto analyze partition !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    auto analyze partition !!

    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;

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Remove exec before sys.dbms_stats.
    It is not needed if it is called between begin and end.

    Tamil

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    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 ?

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You do not need REF Cursor because you are not returning any thing.

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

    Tamil

  5. #5
    Join Date
    Nov 2002
    Posts
    170
    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>

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Line 11
    Code:
    and partition_name like x_part_name||'%'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    and partition_name like 'x_part_name%'
    should be
    and partition_name like x_part_name||'%'
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by DaPi
    and partition_name like 'x_part_name%'
    should be
    and partition_name like x_part_name||'%'
    Your powers are weak, old man.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width