RE: Creating Histograms

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Fri, 23 Jul 2004 16:42:14 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BCA3@conmsx07.corp.acxiom.net>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman, Donald Sent: Friday, July 23, 2004 10:38 AM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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

Received on Fri Jul 23 2004 - 16:41:38 CDT