| Explain plan - cost(CPU%) [message #578318] |
Tue, 26 February 2013 22:13  |
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 #578329 is a reply to message #578323] |
Wed, 27 February 2013 00:52  |
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 #578331 is a reply to message #578321] |
Wed, 27 February 2013 01:15 |
John Watson
Messages: 8962
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 |
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 #578342 is a reply to message #578339] |
Wed, 27 February 2013 02:23 |
John Watson
Messages: 8962
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 |
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 |
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 #578601 is a reply to message #578551] |
Fri, 01 March 2013 23:32 |
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 #578607 is a reply to message #578601] |
Sat, 02 March 2013 02:00 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
