RE: Creating Histograms

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 23 Jul 2004 10:59:38 -0500
Message-id: <003701c470ce$0f53caa0$212f200a@rshamsudxp>

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

Received on Fri Jul 23 2004 - 10:58:14 CDT