Home » RDBMS Server » Performance Tuning » Difference in Cardinality, Cost, Temp Space (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
| Difference in Cardinality, Cost, Temp Space [message #449552] |
Tue, 30 March 2010 07:19  |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
I got two SQL queries - Original and Tuned.
The issue in the original was that it was using too much TEMP SPACE (>10GB) and executing for more than 1.5 hrs. This query was tuned (obviously, i didn't do it  ) and Tuned query is executing in less than 3 min using hardly 250MB temp space. (the temp stats were gathered from a monitoring tool that we use).
both return the same number of records.
However, when i run the explain plan for both, i get really huge differences.
The cardinality, cost and for that matter all other parameters in the Original query are way less than the one for the Tuned query. While the actual performance on execution suggests otherwise.
Is this expected behaviour ?
From what i gathered reading other posts is that there can be difference in the "estimated" cardinality. If so, is there anyway to get the actuals ?
|
|
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449556 is a reply to message #449554] |
Tue, 30 March 2010 07:37  |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Both the plans are being run on the same environment - development to be precise. Running the original one on production attracts the DBA like magnets (10GB temp space is not acceptable)
I am trying to figure out how to format the plans to post them here. I am using Toad for this. (any suggestions ? )
[Updated : Attached the explain plans in CSV]
[Updated on: Tue, 30 March 2010 08:14] Report message to a moderator |
|
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449560 is a reply to message #449552] |
Tue, 30 March 2010 08:42 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Run the plans in sqlplus and post in text format using code tags - see the orafaq forum guide if you don't know how.
If there is a large difference in the data volumes between prod and dev then the plans from dev are no use. Data volumes inform statistics which are in turn used to work out the plan. The plans from prod will probably be very different.
I don't see why the DBAs would know or care if you run the explain plans on prod. It doesn't actually execute the query and won't use any temp space - it'll just show you that it thinks it will need to.
However if you want to get the execution plan as shown in the link then you will need to run the query.
As for the permissions problem - get the permission granted. If you are expected to do any sort of performance tuning you will need it.
|
|
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449578 is a reply to message #449561] |
Tue, 30 March 2010 10:39 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Phew ! finally managed to get the plans from production using SQLPlus. I gotta learn SQLPlus 
Original query and Explain plan :
SQL> ed
Wrote file afiedt.buf
1 explain plan for
2 SELECT product_family_id, organization_code, SUM (shipset_revenue)
3 FROM erp_mfdr_shipset_revenue msr,
4 erp_product_families c,
5 (SELECT org.organization_code, ph.product_family product_family,
6 sir.so so, sir.ss ss
7 FROM erp_mfdr_shipment_revenue sir,
8 cmf_product_hierarchy ph,
9 mf_xxcmf_src_ca_lines abd,
10 erp_bcp_site_channels sc,
11 erp_organizations org,
12 erp_mfdr_full_bom fb
13 WHERE sc.channel_id = abd.supply_channel_id
14 AND org.organization_id = sc.organization_id
15 AND fb.component_id = abd.inventory_item_id
16 AND sir.pid_id = fb.pid_id
17 AND sir.fat != organization_code
18 AND ph.product_id = fb.pid_id
19 AND sir.pid_id = ph.product_id
20 GROUP BY organization_code, product_family, so, ss) a
21 WHERE a.so = msr.so AND a.ss = msr.ss
22 AND a.product_family = c.product_family
23* GROUP BY product_family_id, organization_code
SQL> /
Explained.
SQL> SELECT * FROM table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3852002279
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 819 | | 26792 (1)| 00:05:22 |
| 1 | HASH GROUP BY | | 39 | 819 | | 26792 (1)| 00:05:22 |
| 2 | VIEW | | 39 | 819 | | 26792 (1)| 00:05:22 |
| 3 | HASH GROUP BY | | 39 | 4758 | | 26792 (1)| 00:05:22 |
|* 4 | HASH JOIN | | 39 | 4758 | | 26791 (1)| 00:05:22 |
|* 5 | TABLE ACCESS BY INDEX ROWID| ERP_MFDR_SHIPSET_REVENUE | 1 | 27 | | 5 (0)| 0
| 6 | NESTED LOOPS | | 39 | 3861 | | 26788 (1)| 00:05:22 |
|* 7 | HASH JOIN | | 136 | 9792 | | 26108 (1)| 00:05:14 |
| 8 | INDEX FULL SCAN | ERP_ORGANIZATIONS_N1 | 131 | 1048 | | 1 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 137 | 8768 | 34M| 26106 (1)| 00:05:14 |
|* 10 | HASH JOIN | | 639K| 26M| 5816K| 5238 (1)| 00:01:03 |
| 11 | TABLE ACCESS FULL | CMF_PRODUCT_HIERARCHY | 258K| 2781K| | 378 (1)| 00:00:05 |
|* 12 | HASH JOIN | | 639K| 20M| | 3209 (1)| 00:00:39 |
|* 13 | HASH JOIN | | 32129 | 658K| | 449 (1)| 00:00:06 |
|* 14 | TABLE ACCESS FULL | ERP_BCP_SITE_CHANNELS | 13 | 117 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | MF_XXCMF_SRC_CA_LINES | 108K| 1274K| | 445 (1)| 00:00:06
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 16 | TABLE ACCESS FULL | ERP_MFDR_FULL_BOM | 1370K| 15M| | 2752 (1)| 00:00:34 |
| 17 | TABLE ACCESS FULL | ERP_MFDR_SHIPMENT_REVENUE | 1443K| 27M| | 16971 (1)| 00:03:24
|* 18 | INDEX RANGE SCAN | ERP_MFDR_SHPST_RV_N1 | 3 | | | 2 (0)| 00:00:01
| 19 | INDEX FAST FULL SCAN | ERP_PRODUCT_FAMILIES_N4 | 1040 | 23920 | | 3 (0)| 00:
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PH"."PRODUCT_FAMILY"="C"."PRODUCT_FAMILY")
5 - filter("SIR"."SS"="MSR"."SS")
7 - access("ORG"."ORGANIZATION_ID"="SC"."ORGANIZATION_ID")
filter("SIR"."FAT"<>"ORGANIZATION_CODE")
9 - access("SIR"."PID_ID"="FB"."PID_ID" AND "SIR"."PID_ID"="PH"."PRODUCT_ID")
10 - access("PH"."PRODUCT_ID"="FB"."PID_ID")
12 - access("FB"."COMPONENT_ID"="ABD"."INVENTORY_ITEM_ID")
13 - access("SC"."CHANNEL_ID"="ABD"."SUPPLY_CHANNEL_ID")
14 - filter("SC"."CHANNEL_ID" IS NOT NULL)
18 - access("SIR"."SO"="MSR"."SO")
40 rows selected.
Tuned query and explain plan :
SQL> ed
Wrote file afiedt.buf
1 explain plan for
2 SELECT product_family_id, organization_code, SUM (shipset_revenue)
3 FROM (SELECT DISTINCT c.product_family_id, orgmap.organization_code,
4 msr.ROWID, msr.shipset_revenue
5 FROM erp_mfdr_shipment_revenue sir,
6 erp_mfdr_shipset_revenue msr,
7 erp_product_families c,
8 (SELECT DISTINCT org.organization_code,
9 ph.product_family product_family,
10 ph.product_id
11 FROM cmf_product_hierarchy ph,
12 mf_xxcmf_src_ca_lines abd,
13 erp_bcp_site_channels sc,
14 erp_organizations org,
15 erp_mfdr_full_bom fb
16 WHERE sc.channel_id =
17 abd.supply_channel_id
18 AND org.organization_id =
19 sc.organization_id
20 AND fb.component_id =
21 abd.inventory_item_id
22 AND ph.product_id = fb.pid_id
23 AND ROWNUM > 0) orgmap
24 WHERE sir.pid_id = orgmap.product_id
25 AND sir.fat != orgmap.organization_code
26 AND orgmap.product_family = c.product_family
27 AND sir.so = msr.so
28 AND sir.ss = msr.ss) iv
29* GROUP BY product_family_id, organization_code
SQL> /
Explained.
SQL> SELECT * FROM table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2334793537
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96337 | 1975K| | 211K (1)| 00:42:24 |
| 1 | HASH GROUP BY | | 96337 | 1975K| | 211K (1)| 00:42:24 |
| 2 | VIEW | | 10M| 202M| | 211K (1)| 00:42:24 |
| 3 | HASH UNIQUE | | 10M| 704M| 1629M| 211K (1)| 00:42:24 |
|* 4 | HASH JOIN | | 10M| 704M| | 37600 (1)| 00:07:32 |
| 5 | INDEX FAST FULL SCAN | ERP_PRODUCT_FAMILIES_N4 | 1040 | 11440 | | 3 (0)|
|* 6 | HASH JOIN | | 10M| 598M| 16M| 37547 (1)| 00:07:31 |
| 7 | VIEW | | 639K| 9368K| | 13488 (1)| 00:02:42 |
| 8 | HASH UNIQUE | | 639K| 31M| 73M| 13488 (1)| 00:02:42 |
| 9 | COUNT | | | | | | |
|* 10 | FILTER | | | | | | |
|* 11 | HASH JOIN | | 639K| 31M| | 5242 (1)| 00:01:03 |
| 12 | INDEX FULL SCAN | ERP_ORGANIZATIONS_N1 | 131 | 1048 | | 1 (0)| 00:00:01
|* 13 | HASH JOIN | | 639K| 26M| 5816K| 5238 (1)| 00:01:03 |
| 14 | TABLE ACCESS FULL | CMF_PRODUCT_HIERARCHY | 258K| 2781K| | 378 (1)| 00:00
|* 15 | HASH JOIN | | 639K| 20M| | 3209 (1)| 00:00:39 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 16 | HASH JOIN | | 32129 | 658K| | 449 (1)| 00:00:06 |
|* 17 | TABLE ACCESS FULL| ERP_BCP_SITE_CHANNELS | 13 | 117 | | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL| MF_XXCMF_SRC_CA_LINES | 108K| 1274K| | 445 (1)| 00:00
| 19 | TABLE ACCESS FULL | ERP_MFDR_FULL_BOM | 1370K| 15M| | 2752 (1)| 00:00:34 |
|* 20 | HASH JOIN | | 415K| 18M| 15M| 22027 (1)| 00:04:25 |
| 21 | TABLE ACCESS FULL | ERP_MFDR_SHIPSET_REVENUE | 415K| 10M| | 2089 (1)|
| 22 | TABLE ACCESS FULL | ERP_MFDR_SHIPMENT_REVENUE | 1443K| 27M| | 16971 (1)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ORGMAP"."PRODUCT_FAMILY"="C"."PRODUCT_FAMILY")
6 - access("SIR"."PID_ID"="ORGMAP"."PRODUCT_ID")
filter("SIR"."FAT"<>"ORGMAP"."ORGANIZATION_CODE")
10 - filter(ROWNUM>0)
11 - access("ORG"."ORGANIZATION_ID"="SC"."ORGANIZATION_ID")
13 - access("PH"."PRODUCT_ID"="FB"."PID_ID")
15 - access("FB"."COMPONENT_ID"="ABD"."INVENTORY_ITEM_ID")
16 - access("SC"."CHANNEL_ID"="ABD"."SUPPLY_CHANNEL_ID")
17 - filter("SC"."CHANNEL_ID" IS NOT NULL)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
20 - access("SIR"."SO"="MSR"."SO" AND "SIR"."SS"="MSR"."SS")
43 rows selected.
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449579 is a reply to message #449578] |
Tue, 30 March 2010 10:44 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
The explain plan has been taken from Production for both the queries.
Notice the large difference in parameters.
As I earlier mentioned, when we actually execute them, the Original query uses >10GB temp_space and takes 1.5 hrs for execution.
While the Tuned query uses hardly 250MB temp_space and executes within 3 mins.
Both return the exact same data and number of rows (1829 rows - to be precise)
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449580 is a reply to message #449552] |
Tue, 30 March 2010 11:10 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lot of odd things here:
this:
Looks like a blatant attempt to trick the optimiser.
Your explain plans appear to bear no resemblance to reality. I would have suggested bind variable peeking as a possible cause but you don't have any bind variables.
Some of your stats and/or optimiser settings must be wrong. Really you want to get the execution plans and compare them to explain plans. That should give an indication of what the problem is. Can't really suggest anything else at the moment.
You could always ask the person who rewrote why they did what they did, it appears they have some idea where the optimiser is going wrong.
|
|
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449584 is a reply to message #449581] |
Tue, 30 March 2010 12:24 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
knw15pwr wrote on Tue, 30 March 2010 17:55Quote:Looks like a blatant attempt to trick the optimiser.
Trick the optimiser into showing the performace to be degraded ? 
I'm sure that wasn't the intention but it might be a side effect, remove it, see what happens. I can guarantee you'll get the same results without it, but not necessarily the same performance.
knw15pwr wrote on Tue, 30 March 2010 17:55
But the client is unrelenting ! He dosn't just want data and time. He needs to know if both the queries are logically the same and a comparison of their Explain plans to prove that there is actually a performance gain.
This is the reason I am trying to compare the explain plans and was actually spooked to see the results.
You might want to explain to the client that explain plans are just estimates and that they aren't always right.
That said if they explain plans are that divorced from reality, and they show the 1st being 8 times faster when it's actually 30 times slower, then something is seriously wrong somewhere. Probably time to ask the DBAs, or the tuning guy for an explanation.
And just to ask the really obvious question - are you sure those explain plans are from production, and are you sure the timings came from production?
Because something really doesn't add up.
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449586 is a reply to message #449584] |
Tue, 30 March 2010 12:45 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Quote:
And just to ask the really obvious question - are you sure those explain plans are from production, and are you sure the timings came from production?
Yes!
As a proof, you may notice the difference in the attached CSV file(taken from dev) and the code posted directly (after slogging for over 2 hrs trying to figure out how to get the desired results using SQLPlus).
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449591 is a reply to message #449552] |
Tue, 30 March 2010 13:40 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Just checking.
Some other questions spring to mind:
How were the timings taken?
How was the temp space usage calculated (from when it was actually run. not the explain plan obviously)?
Did whoever timed the queries allow for the effects of caching? - did they run each query multiple times and take the average time or did they just run them once each, one after the other?
If they did the later then the performance difference might not be as great as you think as the first query would have loaded most of the data into the buffer cache thus making the 2nd one faster.
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449616 is a reply to message #449591] |
Wed, 31 March 2010 00:21 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Thanks for all the help cookiemonster.
Answered your questions below -
1. Timings were taken through toad and informatica.
2. We have a GUI application for DBA related monitoring purpose. The temp space usage was monitored and noted based on the SID from there.
3. Queries were run in 2 separate windows (sessions) of Toad. I did run the queries multiple times in different instances and averaged the time.
4. Also, ran the queries in the format SELECT * from (<query>); and SELECT count(*) from (<query>); Noted the time and temp space for these also.
5. Created separate Informatica sessions and executed both queries at the same time. Noted the actual time - in this case data was also loaded in 2 separate target tables.
I presume 3,4 and 5 do take care of caching. So, the values posted are actually averages of all the above.
(I normally test thouroughly  - good to also be a certified tester)
Update : Comparative tests were carried out on DEV (recently refreshed from production)
I could only test the time for Tuned query in production and compare it to logs generated earlier for original query. I cannot run the Original in production as the DBA kills it instantly due to huge temp space.
[Updated on: Wed, 31 March 2010 00:29] Report message to a moderator |
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449633 is a reply to message #449552] |
Wed, 31 March 2010 02:18 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Basically at this point I think you need to get the DBAs involved.
It appears that all the information is gathered correctly.
It appears that the information is wildly contradictory.
I can't explain that contradiction, I'd like to hope they can.
|
|
|
|
|
|
| Re: Difference in Cardinality, Cost, Temp Space [message #449657 is a reply to message #449552] |
Wed, 31 March 2010 04:44 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Simple answer is yes, but it generally means your stats and/or optimser settings are wrong.
Card is oracles estimate of the number of rows it has to process to perform a given step. It works this out from the table statistics. Generally lower card means less rows to process means less work means faster query.
Cost is just a rough estimate of the overall work it needs to do.
Since in your case the card(rows) is much lower for the slow query that implies some of your stats are badly off.
What you really need is the execution plan - that shows the number of rows oracle actually processed for any given step as opposed to the number it thinks it's going to process. That would give some indication as to why the explain plans are so badly wrong.
I've seen queries with higher costs run faster than ones with lower ones, but never with the scale of difference you have here.
|
|
|
|
Goto Forum:
Current Time: Mon May 13 14:46:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
