RE: Creating Histograms
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jul 2004 10:59:38 -0500
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@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
-----------------------------------------------------------------
-- 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@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:
