Home » RDBMS Server » Performance Tuning » Explain plan - cost(CPU%) (10g, 10.2.0.5, windows)
Explain plan - cost(CPU%) [message #578318] Tue, 26 February 2013 22:13 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,


Here, i am confused about the reading of cast through explain plan. Do i read like this -

total cost= sum of cost / number of rows - 35/8 (as per the above attached xplain plan).

Please correct me, as i have to give feedback on explain plan.

waiting for your reply...




SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                           
----------------------------------------------------------------------------------------------------------------
Plan hash value: 1584826709

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |     2 |    70 |     7  (29)| 00:00:01 |
|   1 |  HASH UNIQUE                     |                               |     2 |    70 |     7  (29)| 00:00:01 |
|*  2 |   COUNT STOPKEY                  |                               |       |       |            |          |
|   3 |    VIEW                          | CNFGTR_USER_OFFICE_PERMISSION |     2 |    70 |     6  (17)| 00:00:01 |
|*  4 |     SORT GROUP BY STOPKEY        |                               |     2 |   196 |     6  (17)| 00:00:01 |
|   5 |      NESTED LOOPS                |                               |     2 |   196 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN           | TMP_CNFGTR_USER_LOC           |     2 |    30 |     3   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| GENMST_TAB_OFFICE             |     1 |    83 |     1   (0)| 00:00:01 |
|*  8 |        INDEX UNIQUE SCAN         | PK_GENMST_OFFICE              |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------


Regards,
Ishika

[Updated on: Thu, 28 February 2013 00:35] by Moderator

Report message to a moderator

Re: Explain plan - cost(CPU%) [message #578320 is a reply to message #578318] Tue, 26 February 2013 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94595
Re: Explain plan - cost(CPU%) [message #578321 is a reply to message #578320] Tue, 26 February 2013 22:54 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Thanks BlackSwan,

Great link for the explain plan. I just want to know, whether total cost for the plan is 35 or 35/8=4.35.

Correct me here.

Regards
Re: Explain plan - cost(CPU%) [message #578323 is a reply to message #578321] Tue, 26 February 2013 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I just want to know, whether total cost for the plan is 35 or 35/8=4.35.
no

>Correct me here.
OK, you are not correct.
Re: Explain plan - cost(CPU%) [message #578329 is a reply to message #578323] Wed, 27 February 2013 00:52 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. BlackSwan,

Thanks for your response. Ok, I am wrong. In my report to management, i have replied total cost to the query is 35/8=4.35.
Can you please help me out to find out the cost for the query?

Waiting for your positive feedback...

regards
ishika
Re: Explain plan - cost(CPU%) [message #578330 is a reply to message #578329] Wed, 27 February 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cost in which unit?

Note that there are no 35 and no 8 in the explain plan you posted.

Regards
Michel
Re: Explain plan - cost(CPU%) [message #578331 is a reply to message #578321] Wed, 27 February 2013 01:15 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I just want to know, whether total cost for the plan is 35 or 35/8=4.35.

Neither. The cost is 7. But the figure in isolation is meaningless: it has no units.
What are you saying in your report to management? What are you trying to tell them?
Re: Explain plan - cost(CPU%) [message #578339 is a reply to message #578331] Wed, 27 February 2013 02:03 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear John,

Management asked me to clarify the cost for each queries. I told them the average cost to the query.
like, total cost is 35 and total ID's are 8. So, total cost to the query is 4.35.
I really don't know how to represent cost to the query in sentences.

Help me out for this confusion.

Regards,
Ishika
Re: Explain plan - cost(CPU%) [message #578340 is a reply to message #578339] Wed, 27 February 2013 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should read the link that have been provided to you and you stated as a "great link".
All what you ask is well explained in it.

Note: you are completely wrong on the cost.

Regards
Michel
Re: Explain plan - cost(CPU%) [message #578342 is a reply to message #578339] Wed, 27 February 2013 02:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Management asked me to clarify the cost for each queries.

In that case, you are providing meaningless (as well as inaccurate) information. EXPLAIN PLAN is estimates,
but presumably your management is interested in reality.
To obtain the actual execution statistics (in terms of CPU time, disc I/O, and so on) you need either to trace
the statement and analyze the trace file, or gather execution statistics while the statement is running.
This article I wrote a while back may help, http://www.orafaq.com/node/2746 my original hypothesis is not
wholly correct so pay particular attention to the response from rleishman which corrects it.
Re: Explain plan - cost(CPU%) [message #578421 is a reply to message #578318] Wed, 27 February 2013 21:24 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
The "COST" of the query is in the first line where the SELECT keyword is presented. It is 7.

Some DBAs (and Oracle) compare the cost of different execution plans for the same SQL statement to try to identify the "better" execution plan. However, a lower "COST" doesn't always mean better execution because the runtime execution may require more CPU and I/O then what the optimizer estimated.

Hemant K Chitale
Re: Explain plan - cost(CPU%) [message #578550 is a reply to message #578421] Fri, 01 March 2013 03:26 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member

Dear Mr. Hkchital,

Thanks you very much. I just want to know this thing only. Apologize to you all. Might be my way of asking was not clear. Thanks to u all.


The "COST" of the query is in the first line where the SELECT keyword is presented. It is 7.


Regards,
Ishika
Re: Explain plan - cost(CPU%) [message #578551 is a reply to message #578550] Fri, 01 March 2013 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the cost value is meaningless in itself.

Regards
Michel
Re: Explain plan - cost(CPU%) [message #578601 is a reply to message #578551] Fri, 01 March 2013 23:32 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Mr. Hkchital,

Below query shows full table scan. Even after FTS, its cost is zero. Now, do i need to create index on where condition?


SQL> explain plan for
  2  SELECT TXT_COURT_NAME "Court Name",
  3         Num_Court_code "Court Code",
  4         TXT_ADDRESS " Court Address"
  5    FROM Claimmst_court
  6   WHERE ROWNUM < 2 AND 1 = 2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1502089720

--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    32 |     0   (0)|          |
|*  1 |  COUNT STOPKEY      |                |       |       |            |          |
|*  2 |   FILTER            |                |       |       |            |          |
|   3 |    TABLE ACCESS FULL| CLAIMMST_COURT | 18586 |   580K|    70   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<2)
   2 - filter(NULL IS NOT NULL)

16 rows selected.

SQL> explain plan for
  2  SELECT TXT_COURT_NAME "Court Name",
  3         Num_Court_code "Court Code",
  4         TXT_ADDRESS " Court Address"
  5    FROM Claimmst_court
  6   WHERE NUM_LOCATION_CD=12900032201
  7   and ROWNUM < 2 AND 1 = 2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3842241891

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    40 |     0   (0)|          |
|*  1 |  COUNT STOPKEY                |                     |       |       |            |          |
|*  2 |   FILTER                      |                     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| CLAIMMST_COURT      |     1 |    40 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NUM_LOCATION_CD |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<2)
   2 - filter(NULL IS NOT NULL)
   4 - access("NUM_LOCATION_CD"=12900032201)

18 rows selected.



Dear Michel,

Let me know, how can i know which is in optimized state if cost value is meaningless?
Explain plan is to check whether queries are FTS, nested loop or hash join?
I have gone through the link provided by John but still confused. Kindly make me understand please.

Regards,
Ishika
Re: Explain plan - cost(CPU%) [message #578605 is a reply to message #578601] Sat, 02 March 2013 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
It is meaningful to compare different plan for a single query.
It is meaningless to compare different queries.

2/
Cost is 0 because optimizer recognizes 1=2 (which it converts to "NULL IS NOT NULL") and so it knows that nothing have to be done to return the result (no rows). It will not execute the steps below FILTER

Regards
Michel
Re: Explain plan - cost(CPU%) [message #578607 is a reply to message #578601] Sat, 02 March 2013 02:00 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
Ishika,

For the Full Table Scan execution (to read an estimated count 18,586 rows), Oracle begins with a COST of 70.
Similarly, for the Index Range Scan execution (to read an estimated 1 row), Oracle begins with a COST of 1 for the Index and 2 (3-1) for retrieving the row from the Table.

However, in both cases, it then short-circuits the execution and cost when it evaluates the predicate " AND 1 = 2". It knows that this predicate will result in 0 rows because it will always (irrespective of the values in the rows in the table) evaluate to FALSE. Therefore, it then converts the final COST to 0.
Essentially, it knows that it will not need to read the Table (or Index) because of this predicate.

If you want to see realistic COST estimates, you should never include such a predicate ("AND 1 = 2") in your query.


Hemant K Chitale
Re: Explain plan - cost(CPU%) [message #578608 is a reply to message #578607] Sat, 02 March 2013 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to confirm what I've said.

Regards
Michel
Re: Explain plan - cost(CPU%) [message #578619 is a reply to message #578608] Sat, 02 March 2013 04:05 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Thank you very much to Hkchital and Michel.

I was confused but now i m clear about the plan and cost.

Regards,
Ishika
Previous Topic: Query Re-write for connect by
Next Topic: problem with statspack
Goto Forum:
  


Current Time: Fri Mar 29 09:37:50 CDT 2024