Using DBMS_STATS package
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Using DBMS_STATS package

  1. #1
    Join Date
    Feb 2002
    Posts
    23
    Dear All,

    We are doing performance-testing of SQL-statements in our project. Its heard DBMS_STATS package can be used for the same . Can anybody tell me how that can be used for performance-testing and how much it is useful .
    Can it be used as an alternative of ANALYZE ?.

    Louis.

  2. #2
    Join Date
    Feb 2002
    Posts
    166
    Yes. Detail information in "Oracle8i Supplied Packages Reference".
    MCSE/MCDBA, OCP DBA 8i
    Working on SCJP -> SCJD

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    DBMS_STATS can be used to collect the statistics of schema objects, just like we did ANALYZE on schema objects. But there are few differences between these two.

    Analyze is a depricated feature in 9i. But to get the CHAINED_ROWS , you may wish to use ANALYZE command.

    Oracle recomends to use the DBMS_STATS since 8i

    Never analyze the SYS schema, with DBMS_STATS package, It makes the things worse , when you query a data dictionary table, since Oracle will be using the RULE based optimizer when you query data dictionary, but when you use DBMS_STATS to gather stats for a schema, those will be PRO COST based optimizer. I am not sure if this is fixed in 9i or not.

    we use as follows:
    DBMS_STATS.GATHER_SCHEMA_STATS ('SCHEMA1',25,FALSE,'FOR ALL COLUMNS SIZE 1',NULL,'DEFAULT',TRUE ) ;

    Hope this helps!!
    Madhu Reddy
    xdollor@yahoo.com

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