Hi

Searched the web and other groups a lot. But unable to get a right way
of doing this.

I am having the execution plan of a query. Now i want to validate
whether the figures given in that are valid or not, like the Rows,
Bytes and cost.

I also want to know how the over all cost is determined from the cost
of the children steps and how the cost of each and every individual
steps are identified.

Would be great if I am pointed to some relevant links or somone
explains how I can manually compute the cost given the execution plan

Thanks

Perform 10053 traces, plus read Wolfgang Breitling's A LOOK
UNDER THE HOOD OF CBO: THE 10053 EVENT and/or get a
copy of Jonathan Lewis' Cost-based Oracle: Fundamentals.

The estimated cost and cardinality is available from the plan_table
that you pull the explain plan from.

If you have version 9+ you can also look at v$sql_plan.

HTH -- Mark D Powell --

You might find this following link of interest:

http://asktom.oracle.com/pls/ask/f?p...A:313416745628

The gist of it is though, cost should not be used as a tuning stat. It
is almost like a random number. From my own experience, whenever you
apply a hint to a query, the cost increases. However, I've applied a
hint to a query, seen four times the cost, and the query has run over
30 times faster (Execution time dropped to 2 hours from 65 hours).

On Mon, 19 Dec 2005 09:32:03 -0800, Jimbo1 wrote:

Cost is expressed in single block I/O requests that need to be fulfilled
in order to execute the query. Problem with the cost is that it is only
an estimate and the estimate can be grossly incorrect.

--
http://www.mgogala.com

So what is a good measure - time it with a stopwatch?

Howdo Dean,

Switch on AUTOTRACE in SQLplus (SET AUTOTRACE ON). I'll give you an
example below:

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM DUAL;

D
-
X

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
363 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

In the stats output above, check the "Consistent Gets" measure. Each
consistent get represents a latch on a data structure in the buffer
cache. In plain English, this means that if you can reduce the number
of consistent gets, your query will run faster, and be less likely to
cause any contention in the buffer cach.

The "Physical Reads" stat is a funny one, as queries will run faster
when this is lower as it represents Disk IO. However, all this means in
reality is that the data blocks your query needs to read are not in the
buffer cache, and Oracle must therefore fetch them from disk. Whether
or not the blocks you need are in the buffer cache is not really
something you have control of under normal circumstances, unless you've
already been running lots of queries against the table concerned on a
very regular basis.

If anyone reading this disagrees or thinks I've garbled any of this,
please don't hesitate to shoot me down in flames. ;o)

In terms of query timing, you can use the SET TIMING ON command in
SQLplus before you run your query. If running from PL/SQL, use the
DBMS_UTILITY.GET_TIME function.

I don't think you need to resort to a stopwatch. ;o)

Hope this helps.

James

On Tue, 20 Dec 2005 06:27:56 -0800, dean wrote:

Trick with "set timing on" works for me. It's usually easier then the
stopwatch.

--
http://www.mgogala.com

Yes, time it with a stopwatch! Then ask the user/business if the
runtime is acceptable or not. If the time is acceptable, go on to the
next problem. If not, enable extended sql_trace, run the
statement/application/process, identify the component that is consuming
the most time and focus on tuning it.

correct data, update the date correctly, etc.)?
2) Did the statement/process perform the correct actions in an
acceptable amount of time?

They could care less about cost, cardinality, bchr, etc. So don't
approach performance in those terms.

Cost != Time

Explain Plan Cost is an estimate, not an actual. Execution plans tell
the actuals.
For actuals, you need to get the STAT lines in an extended sql_trace
file. If (quite often when) the Cardinality estimates and row actuals
differ, there might be an issue with the CBO and the statistics/inputs
that it uses.

IIRC, even in 8, cost was not purely single block i/o. For example, the
cost of a full table scan is the number of blocks to be read divided by
a factor determined by db_file_multiblock_read_count.