Re: Question On Statistics Gathering

Statistics management is probably a topic all by itself for large systems.

The larger the system, the more critical the statistics, but the smaller a window you have. Enter "monitoring". This is my approach - I wrote a PL/SQL package to manage this:

• switch all objects of interest to monitoring (cost is minimal - not having the appropriate statistics is a lot more expensive)
• from monitoring - compute those that need analyzing and call them "is stale"
• sample queries in shared-pool for those SQL that could benefit from histograms - "need histy". Sample over time and build up a history.
• select all monitored tables where "is stale" or "need histy", order by last_analyzed
• analyze in single stream the top table in the list
• if within analyze window, do next one, otherwise stop (next window, pick up the next top candidate).

Using this approach, you are not confined to a specified window, andyet pick up the highest priority candidates. Histograms are essential for large systems, the CBO inefficienciies are magnified accordingly.

sqlldr direct path, tell the developers to include an analyze estimate (at least) at the end.

• Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Saturday, June 28, 2003 8:34 AM

> Hi All,
> Currently we have a very large database ( 8.1.7.4 ) on Tru64 that is
having query optimization problems. Lacking good, up-to-date statistics is the main cause of it. Because of the size and availability window we can't afford to analyze the whole schema. Now 8i has
> a new feature that only gathers stats when stale. You have to set table to
monitoring mode first.
> I am intrigued by this new feature and am reading up on it and have 2
questions:
> 1. How reliable and bug-free is this feature?
> 2. We do a lot of sqlldr direct path, which bypasses table insert. Will
this feature catch the rows inserted that way?
>
> TIA
> Dennis
> -----Original Message-----
> Sent: Friday, June 27, 2003 2:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
> One of our third party applications gave us the following message:
>
> ORA-04031: unable to allocate 4192 bytes of shared memory
>
> Environment is Oracle 8.1.7 on AIX 4.3.2
>
> The output from "select * from v$sgastat showed about 3 mb free
> out of a total of 40 mb of shared pool. I increased the
> size of shared pool to 60 mb and started to watch the
> "shared pool"/"free memory" value in v$sgastat. It seems to vary
> between about 3 and 11 mb.
>
> 1. What is the application likely to be doing that requires it to
> allocate shared pool?
>
> 2. How do I know when I have the shared pool value in init.ora set high
> enough so that I won't get the ORA-04031 errors?
>
> 3. Is there any other init.ora parameter I ought to be looking at here?
>
> Thanks,
> Peter Schauss
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Schauss, Peter
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Meng, Dennis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).