RE: Creating Histograms

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 23 Jul 2004 11:22:12 -0500
Message-id: <003801c470d1$35da6f00$212f200a@rshamsudxp>

Pushed that send button too early...
> 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?

That depends upon many factors. For the execution plans to be the same, following need to be considered:

Various optimizer specific parameters should match. Event 10053 trace will show you the relevant optimizer parameters.
DB block size should match.( I don't even want to think about throwing in multiple block size here).
If you are collecting system stats, then the execution plans can be different if your system stats are different(9i). So, you might have to copy those also.
Of course, object statistics should match. (Assumption is that the objects have same structures).
If you don't have stats on few objects, but CBO is being used anyway, then the size of the object can cause optimizer to select different execution plan.
If you are in 9i, then dynamic sampling can change the execution plan depending upon the level of sampling you have setup.
Software versions should match. Hopefully, you won't get hit with Port specific bugs.

So, I guess, you will have to consider all the above and check out the explain plans between production and test databases. Granted, no easy task.

HTH
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj Shamsudeen Sent: Friday, July 23, 2004 11:00 AM
To: oracle-l_at_freelists.org
Subject: RE: Creating Histograms

Hi Donald,
>If this is going to be a production process should I set it up under an
application schema owner?
Yes. I would and have a schema which stores the system level information such as stats, space usage etc..It is also easier for me to export this schema and stick it in another database, for further analysis.

> Or does each application schema owner need their own stats table?
Not necessarily..

> Can I keep multiple sets of statistics for a single schema? Is that
what the statid is for ?
Yes. You can keep multiple sets, with statid..

Here is a procedure I use :This is called for various app schemas, every week ( Statistics are collected per table level in somewhat 'intelligent' fashion, at the most, stats can change once per week and storing stats every week is sufficient for us.).

Believe it or not, I have rolled back stat changes for one table already using these stored stats..

Cut--- procedure save_schema_stats ( v_schema IN VARCHAR2, load_status OUT NUMBER, load_error OUT VARCHAR2, load_comment OUT VARCHAR2 ) AS
- Author: Riyaj Shamsudeen
- To store the image of schema stats so that we can rollback the stat changes, in case, we have an issue.
- Also, can be used to see the change pattern.. v_date date; l_script_name VARCHAR2(50) := 'save_schema_stats'; begin select to_char(sysdate,'DD-MON-YY') into v_date from dual; dbms_stats.export_schema_stats ( ownname=>v_schema, stattab=>'STATTAB', statid=>v_schema||'_'||v_date, statown=>'DBMON' ); EXCEPTION when others then load_status:=1; load_comment:='Error occurred stats collection package'; load_error:=SQLCODE||'-'||SQLERRM; raise_application_error(-20001, 'Unexpected error.' || '; ' || SQLERRM); end; END; /
cut---

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----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
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any
review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
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
-----------------------------------------------------------------



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


----------------------------------------------------------------
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
-----------------------------------------------------------------

Received on Fri Jul 23 2004 - 11:19:29 CDT