Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unanswered: Analyze a Table and all underlying indexes

    There was recommended code posted under the oracle technology network website that analyzes a large table with many associated indexes. Here is the script as follows:


    create or replace procedure
    p_analyze_table_indexes(a_table_name IN
    user_indexes.TABLE_NAME%type)
    as
    v_cursorid integer;
    status integer;
    cursor c_user_indexes is
    select TABLE_NAME,INDEX_NAME
    from user_indexes
    where status='VALID'
    and TABLE_NAME=upper(a_table_name);
    v_user_indexes c_user_indexes%rowtype;
    begin
    open c_user_indexes;
    v_cursorid:=dbms_sql.open_cursor;
    fetch c_user_indexes into v_user_indexes;
    while ( c_user_indexes%found ) loop
    dbms_sql.parse(v_cursorid,
    'analyze index
    '||v_user_indexes.INDEX_NAME||' ESTIMATE STATISTICS SAMPLE 40 PERCENT
    ',dbms_sql.native);
    status:=dbms_sql.execute(v_cursorid);
    fetch c_user_indexes into
    v_user_indexes;
    end loop;
    dbms_sql.parse(v_cursorid,
    'analyze table '||v_user_indexes.TABLE_NAME||'
    ESTIMATE STATISTICS SAMPLE 40 PERCENT
    ',dbms_sql.native);
    status:=dbms_sql.execute(v_cursorid);
    close c_user_indexes;
    dbms_sql.close_cursor(v_cursorid);
    exception
    when others then
    dbms_output.put_line('Error...... ');
    dbms_sql.close_cursor(v_cursorid);
    raise;
    end p_analyze_table_indexes;
    /


    "Itemmaster" is a table within the schema

    When I try to run this stored procedure 'execute p_analyze_table_indexes(itemmaster)' , it gives me an error:

    Error at Line 1
    ORA-06550: line 1, column 31:
    PLS-00357: Table, View, or Sequence reference 'ITEMMASTER' not allowed in this context
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    I dont know why this is happening, any suggestions?
    Jason B. Simms

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what the hell is that??

    just use dbms_stats.

    PHP Code:
    create or replace procedure SP_GATHER_STATISTICS
    AS
    begin

        dbms_stats
    .gather_table_stats (
             
    ownname          => 'your_schema_name',
             
    tabname    => 'your_table_name',
            
    options          => 'GATHER AUTO',
             
    estimate_percent => 40,
             
    method_opt       => 'FOR ALL INDEXED COLUMNS SIZE 1',
             
    cascade          => TRUE,
             
    degree           => 3
        
    );
    end SP_GATHER_STATISTICS;

    Last edited by The_Duck; 03-09-04 at 12:50.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    16
    It was posted as being recommended by this website:


    http://otn.oracle.com/oramag/code/tips2003/121403.html
    Jason B. Simms

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, whether it is useful or not, the way to call it would be:

    execute p_analyze_table_indexes('itemmaster')

    Note the single quotes around the table name.

  5. #5
    Join Date
    Aug 2001
    Posts
    66
    > any suggestions?

    Further to Tony's comments it appears that the first 'analyze index' is missing a space after it (perhaps this is a formatting problem).

    > It was posted as being recommended by this website

    Well it probably shouldn't have been. If the above is true it suggests no-one even ran it. The Duck is right - you are looking for DBMS_STATS.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by padderz
    > any suggestions?

    Further to Tony's comments it appears that the first 'analyze index' is missing a space after it (perhaps this is a formatting problem).

    > It was posted as being recommended by this website

    Well it probably shouldn't have been. If the above is true it suggests no-one even ran it. The Duck is right - you are looking for DBMS_STATS.
    It does make you wonder about the quality control on OTN doesn't it? Though to be fair the published version seems to have a newline just after the word index.

Posting Permissions

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