RE: Creating Histograms

  • From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 23 Jul 2004 16:42:14 -0500

We tend to create a stats table for each application
STATID                           COUNT(*)
------------------------------ ----------
APR11SUN2000                         6738
APR12MON2000                         6738
APR13TUE2000                         6738
APR14WED2000                         7461 Analyze -client requirement
APR15THU2000                         7423
APR16FRI2000                         7423
APR17SAT2000                         7423
APR18SUN2000                         6793
APR19MON2000                         6793
APR20TUE2000                         6793
APR21WED2000                         7461 Analyze -client requirement
APR22THU2000                         7423
APR23FRI2000                         7423
APR24SAT2000                         7423
D    D  T
 A    A  I
  T    Y  M
   E       E
Hmmmm, we seem to loose stats on Thursdays and Sundays.

"Can I keep multiple sets of statistics for a single schema? Is that what
the statid is for?"
YES
        You export(wish they used different term like "save") them to a stat
table and then you can exp them to a file you can use on another DB.

        If you create under SYS you could make application name part of the
STATID.

        I think someone explained you may not always get same plan.  One
reason is CBO still looks at table HWM.  But it's still worth doing.
        We've taken stats from test systems (close to production) and put
them in production.
        May save time on upgrade or if you're changing the way you generate
them.
        Normally an upgrade is not best time to change your methods.
        Of course, you back up your current stats first.

        Larry

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman, Donald
Sent: Friday, July 23, 2004 10:38 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Creating Histograms


I have Wolfgang's presentation from =
http://www.centrexcc.com/SQL%20Tuning%20with%20Statistics.ppt.pdf but =
have a question about exporting statistics.  I understand that before I =
start mucking about I need to save them.  And, I'm gathering that each =
time before we rerun them in our current Russian Roulette system we =
should save them, just 'in case.'=20

When creating the stats table to whom should I grant ownership?   I =
found an article with an example creating it under SYS schema in the =
SYSTEM tablespace.  Wolfgang's example creates it in Scott's schema and =
tablespace.  If this is going to be a production process should I set it =
up under an application schema owner?  Or does each application schema =
owner need their own stats table? Can I keep multiple sets of statistics =
for a single schema? Is that what the statid is for?   Or, do I need to =
export them to save them?  I'm a little bit foggy on the big picture =
here since I'm not currently doing any of this. Sorry if this is =
sounding ignorant!

I want to verify that if I export my production statistics to a test =
machine I will get the same execution plan regardless of the fact that I =
only have 10% of the data on that system.  Right?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: