Histograms are only used to refine the selectivity of a predicate. This in
turn determines the cardinality estimate and various costs such as index
access cost and then of course join costs (NL, sort-merge, and hash) and
join cardinality. This ultimately will drive the decision whether a
partic
The join order of an access plan - in the absence of any leading or ordered
hints - is determined strictly like everything else by the CBO: the join
order with the lowest estimated cost wins. And the selectivity and
cardinality estimates play a big role in determining the cardinality and
thus c
> Date: 2003/11/05 Wed AM 11:49:26 EST
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: Re: Re: explain plan conundrum
> >
> > However, since it is a join predicate, the histogram data can not be used.
> > The CBO uses the d
are histograms only used to determine whether to use an index or join type, not join
order?
>
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/11/05 Wed AM 11:49:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re:
and only 4 distinct values on the join column) oracle
chooses the proper join 'order'
>
> From: Wolfgang Breitling <[EMAIL PROTECTED]>
> Date: 2003/11/05 Wed AM 11:49:26 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: Re: exp
However, since it is a join predicate, the histogram data can not be used.
The CBO uses the density values of the join column(s) to derive the join
selectivity. The density value of a column changes (from 1/num_distinct)
when you collect a histogram. If you create a frequency histogram (aka
val
other table and that filters out enough
> rows that that join should go first. The optimizer made a bad decision.
>
> how do i analyze why it made a bad join order decision? hints like this
are
> a stop gap fix.
> >
> > From: Yong Huang <[EMAIL PROTECTED]>
> > Date: 2
optimizer made a bad decision.
>
> how do i analyze why it made a bad join order decision? hints like this
are
> a stop gap fix.
> >
> > From: Yong Huang <[EMAIL PROTECTED]>
> > Date: 2003/11/04 Tue PM 02:09:30 EST
> > To: Multiple recipients of list ORACLE-
t; To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: explain plan conundrum
>
> Hi, Ryan,
>
> Where's the 20 billion rows? There's 1 G rows and 20 G bytes.
>
> What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES f
Could you please post the entire sql and plan and statistics of the tables
and indexes so that we can comment on it rather than letting us guess on
selective bits of the entire problem.
At 01:29 PM 11/4/2003, you wrote:
everything is analyzed. For all indexes, for all indexed columns.
I used an
bad decision.
how do i analyze why it made a bad join order decision? hints like this are a stop gap
fix.
>
> From: Yong Huang <[EMAIL PROTECTED]>
> Date: 2003/11/04 Tue PM 02:09:30 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: exp
Hi, Ryan,
Where's the 20 billion rows? There's 1 G rows and 20 G bytes.
What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES for
TABLE2? Did you analyze using ANALYZE command or DBMS_STATS?
Yong Huang
--- [EMAIL PROTECTED] wrote:
> I cant sql trace it now. I hae run statspack.
i figured it out. I use the ordered hint. Its actually a 3 table join and I took that
out for simplicity in my question. I drove off a more selective join. Got it to run in
1 minute.
i almost never have to use ordered hint
>
> From: <[EMAIL PROTECTED]>
> Date: 2003/11/04 Tue PM 01:34:26 EST
>
It's not 20 billion rows but 20 billion bytes. It's "only" 1 billion rows.
The cartesion product of 5K rows and 366K rows is 1830M rows or 1.8G. If
the join predicate is not very selective, .5 or .33 for example, that would
yield an estimated join cardinality of 1G (after rounding).
At 11:34 AM
some more info.
I checked distinct values in those two tables. and there are 366,000 records in one
table and 5,000 records in another however, the column that is joining to only has 4
distinct values. I added a bitmap index to the columns and its using it on the smaller
table.
Im willing to
If you all are in 9i and you have not used dbms_xplan.display, give it a
try... you will throw scripts like this one away forever!
RF
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 6/26/2003 11:19 AM
David - Here is the one I use. In my environment I just have one
deve
Is this what you wanted?
col OPERATION for a60
col OPTIONS for a40
col object_name for a32
select lpad(' ',2*(level-1)) || operation || ' ' || decode(id, 0, 'Cost = '
|| position) "OPERATION", options, object_name from plan_table start with
id = 0 connect by prior id = parent_id
/
> -Origina
David - Here is the one I use. In my environment I just have one developer
at a time using EXPLAIN, so I just have it truncate the table afterward.
select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name
||' '||decode(id,0,'Cost = '||position) "Query Plan"
from plan_table
>
>
> The three-day seminar:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> UK_(Manchester)_May
> USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> - Original Message --
ssage -
> 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 fro
"Kevin Toepke"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
racle.com> cc:
Sent by:
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 subpartition-leve
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)
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
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
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 http://www.jlcomp.demon.co.
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 usi
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
tkpr
"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/
>In the explain plan what is meant by "cost" ,
>"card" and "byte" ?=0D
>How may they be interpreted practically ?=0D
>Any Docs , Links on the Same ?=0D
> =0D
> =0D
'cost' like in 'cost based analyzer' - compound value which gives the CBO estimate of
how much dreadful it will be to bring
Cost is the optimizer cost. Card is cardinality. Byte is number of bytes
processed in that particular step. Lots and lots of documentation on this.
VIVEK_SHARMA wrote:
Message
In the explain
plan what is meant by "cost" , "card" and "byte" ?
How may they be interpret
if a database link is not involved you can use
alter session set current_schema = ;
After you explain it, be sure to reset the current_schema back to what it was.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, September 23, 2002 1:09
How about trying
"table_owner".table_name in your query where you specify the tablename
in your from clause?
Regards,
--- Van der Sande Patrick <[EMAIL PROTECTED]> wrote:
> Dear,
>
> As system I want to generate an explain plan of an end user query.
> In this query a number of tables are def
ALTER SESSION SET CURRENT_SCHEMA = ;
-Mark
On Sat, 2002-09-21 at 15:33, Van der Sande Patrick wrote:
> Dear,
>
> As system I want to generate an explain plan of an end user query.
> In this query a number of tables are defined which do not have a public synonym.
>
> The explain plan ... statem
No, it isn't correct. Explain plan gives you precisely what it should, and that is
the access plan. Here is an excerpt from the documentation:
The EXPLAIN PLAN statement displays e
As certain southern sportsmen often say, "My bad." It wasn't the explain statement
but the program which builds them. Work done at 1:30 AM is always suspect.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 10:09 A
Oracle Apps r10.7 and r11.0 had some SQL statements from PRO*C batch
programs that are over 20K in length. Utterly astounding!
Since VARCHAR2 variables can be up to 32767 in PL/SQL, I've written PL/SQL
packages that used string variables of that length for automating EXPLAIN
PLAN execution and s
Ian:
Any valid SQL statement can be explained. I've explained queries that were
well over 4k -- even when you excluded the whitespace!
Kevin
-Original Message-
Sent: Wednesday, July 24, 2002 11:50 AM
To: Multiple recipients of list ORACLE-L
What is the longest SQL statement that can b
Sore area, hmmm, sounds off topic to me. :)
joe
Gurelei wrote:
>Peter, Charile:
>
>Sore area size is 5 times more in prod DB, shared pool
>is larger in dev, db_block_buffers in dev is twice the
>size in prod. I guess this answers my question.
>
>thanks
>
>Gene
>
>
>
>
>--- Peter Gram <[EMAIL P
Peter, Charile:
Sore area size is 5 times more in prod DB, shared pool
is larger in dev, db_block_buffers in dev is twice the
size in prod. I guess this answers my question.
thanks
Gene
--- Peter Gram <[EMAIL PROTECTED]> wrote:
> Gurelei
>
> Are the parameter's the same ?
>
> sort*
> hash
Gurelei
Are the parameter's the same ?
sort*
hash*
*pool*
db*
Gurelei wrote:
>Hi.
>
>I have executed an explain plan on a dev and prod
>databases. Both databases have the same data, use the
>same version of ORacle (8.1.7.0) and the same OS (AIX
>4.3.3). All the tables are analyzed. The plans h
!! Please do not post Off Topic to this List !!
Does estimate without samples size or percentage not just use 1024 as the
sample size?. If you look at dba_tab_columns the samples size will be in
there. If the table is not too big could you try it with compute statistics
or estimate statistics wi
!! Please do not post Off Topic to this List !!
Christopher:
I don't have the histograms. Regarding the data,
I actually think they are equally spread, but I will
check. Is there any tool to do this, or is it just
select count() from .. where ...?
--- Christopher Spence <[EMAIL PROTECTED]> wro
!! Please do not post Off Topic to this List !!
I have analyzed them today via:
analyze table ... estimate statistics;
analyze table ... estimate statistics for all indexed
columns;
No data were added/modified agter that
--- "Nicoll, Iain (Calanais)"
<[EMAIL PROTECTED]> wrote:
> !! Please do
!! Please do not post Off Topic to this List !!
You probably have histograms, and very skewed data.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-
!! Please do not post Off Topic to this List !!
Have they been analyzed recently? as if you were using histograms then if
the last two months were added after your last analyze it would think they
were fairly rare.
Cheers
Iain Nicoll
-Original Message-
Sent: 13 September 2001 19:11
To:
Just for your reading...
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Fri, 03 Aug 2001 15:36:05 -0800
Brijesh,
Even though OPTIMIZER_MODE=RULE, the CBO will be used
in the presence of other features (e.g. partitioning,
degree o
Title: RE: Explain Plan different on TEST and PRODUCTION instance
Thanks Anita,
It was the degree of parallelism that was creating the problem.
I was just checking the degree column of the dba_indexes to set the parallelism
, which was 1 but the INSTANCES column was set to DEFAULT
Brijesh,
Another thing to check is if the tables involved have
been analyzed on prod database.
Sukumar Kurup
__
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the
--- "A. Bardeen" <[EMAIL PROTECTED]> wrote:
> Brijesh,
>
> Even though OPTIMIZER_MODE=RULE, the CBO will be
> used
> in the presence of other features (e.g.
> partitioning,
> degree of parallelism, etc...).
>
> In this case TEST is getting a better plan because
> the
> CBO is being used. You c
The access plan obtained with bind variables is a
guess plan because the optimiser lacks information.
The plan obtained with real data is a definitive plan
because the optimiser knows everything he needs in
order to make a decision.
It's nice to used bind variables to save on parsing
but real
Depends, if your using bind variables in production, then you can expect to
have a very static choice of explain plans.
Although bind variables are great for promoting more efficient use of the
shared pool, you do hinder the optimizer's ability to use selectivity and
histograms to detirmine if an
Gene:
which everone is used in production. If the statements are dynamically built
with "variable" hard-coded values then you have a couple options.
1) Tune the query for the worst case
2) Tune the query for bind-variables and set "cursor_sharing = force"
in your init.ora
HTH
Kevin
--
On Fri, Jun 15, 2001 at 02:05:44AM -0800, Raymond Lee Meng Hong wrote:
> Hei Guru.
>
> I have a query which involve to some across table , here is my explain plan
> . As a developer is we alway choice a best way to select table , I do join
> those field related together ,but why it still perform
54 matches
Mail list logo
