The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
From: Darrell Landrum [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: 20 March 2003 21:28
Subject: Re: Explain Plan vs Actual Execution Plan
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
From: Darrell Landrum [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: 20 March 2003 21:28
Subject: Re: Explain Plan vs Actual Execution Plan
This query was reading data from
[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
racle.com cc:
Sent by: Subject: RE: Explain Plan vs Actual
Please exlain the difference between local and global statistics.
Is this statisitics on a partition?
Brad Odland
-Original Message-
Sent: Thursday, March 20, 2003 3:29 PM
To: Multiple recipients of list ORACLE-L
Brad, you're absolutely correct. Explain plan is what is intended, but
Yes, local statistics are the partition specific statitics. Global stats are
on the partitioned object as a whole. Global stats are vitally important
when you are accessing more than one partition.
If you are accessing 1 partition and you do not have global stats, then
the optimizer will use the
Title: RE: Explain Plan vs Actual Execution Plan
I'm open to abuse if I'm wrong, but I think that the intended distinction was:
let's assume table T with partitions P1 and P2
analyze table t compute statistics ; --- global statistics
analyze table t partition (p1) compute statistics
Yes, that is specific for a partitioned table.
Using dbms_stats.gather_table_stats, there is a parameter called granularity with
which you can specify to gather stats for your table at different levels:
DEFAULT: Gather global- and partition-level statistics.
SUBPARTITION: Gather
Odland, Brad wrote:
Hello,
Heres a question to ponder. While tuning a SQL statment for a user I noticed
that the explain plan from SQL Analyze was not the same plan that was found
when I used OEM Top Sessions (9.2.0.1) upon executing to collect execution
stats. Database is on HP/UX 11
I think you can trust the STAT lines in a SQL trace file to give you
accurate information about what execution plan *did* take place. The test I
would suggest is to compare the information in these lines to what EXPLAIN
PLAN shows you. You'll need to look at the raw trace data, though, because
Brad, you're absolutely correct. Explain plan is what is intended, but isn't what
always happens. It probably is most of the time, but not always. I recently had a
circumstance in which a long running job (the explain plan of) was telling me it was
reading from a big partitioned table and
The really cute thing about the need for global statistics
to be reasonable - a few pages further on you'll find the
comment that in 8i you can't generate global histograms !
(Fixed in 9i)
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
One-day tutorials:
(see
11 matches
Mail list logo
