RE: Creating Histograms
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jul 2004 11:22:12 -0500
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:
1. Various optimizer specific parameters should match. Event
10053 trace will show you the relevant optimizer parameters.
2. DB block size should match.( I don't even want to think about
throwing in multiple block size here).
3. 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.
4. Of course, object statistics should match. (Assumption is
that the objects have same structures).
5. 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.
6. If you are in 9i, then dynamic sampling can change the
execution plan depending upon the level of sampling you have setup.
7. 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Riyaj Shamsudeen
Sent: Friday, July 23, 2004 11:00 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------
-- 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:
