| Does high volume of data can change plan [message #459339] |
Fri, 04 June 2010 07:46  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.
My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.
Please advise.
Thanks
Ved
[Updated on: Fri, 04 June 2010 07:55] Report message to a moderator |
|
|
|
| Re: Does high volume of data can change plan [message #459345 is a reply to message #459339] |
Fri, 04 June 2010 07:55  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Maybe, maybe not, data volumes aren't the only things that can effect plans.
Changes to data volumes (assuming all other factors remain constant) won't necessarily change the plans either.
We would need a lot more info of what you've done, along with some example plans to say more than that. you know the drill.
|
|
|
|
|
|
|
|
| Re: Does high volume of data can change plan [message #459351 is a reply to message #459349] |
Fri, 04 June 2010 08:27 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
For the same sql I ran, below are the two diff plan in DEV and PROD:
PROD:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5151 | 508K| | 13176 (4)| 00:02:39 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN OUTER | | 5151 | 508K| | 13176 (4)| 00:02:39 |
|* 3 | HASH JOIN RIGHT OUTER| | 5151 | 462K| | 13094 (4)| 00:02:38 |
| 4 | TABLE ACCESS FULL | COUNTRY | 406 | 6902 | | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 5151 | 377K| 41M| 13091 (4)| 00:02:38 |
| 6 | TABLE ACCESS FULL | ADRESS_L_TBL | 1549K| 23M| | 2409 (5)| 00:00:29 |
| 7 | TABLE ACCESS FULL | ADRESS_TBL | 1289K| 72M| | 4161 (4)| 00:00:50 |
| 8 | TABLE ACCESS FULL | LOCATION_TBL | 60757 | 533K| | 79 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
DEV:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 193K| 18M| | 11970 (2)| 00:02:24 |
| 1 | CONCATENATION | | | | | | |
|* 2 | FILTER | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | ADRESS_L_TBL | 1 | 16 | | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 101 | | 9 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 85 | | 6 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 76 | | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| ADRESS_TBL | 1 | 59 | | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ADR_INDX_FK04 | 1 | | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| COUNTRY | 1 | 17 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | COUNTRY_CNST_PK01 | 1 | | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | LOCATION_TBL | 1 | 9 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | LOC_CNST_PK01 | 1 | | | 0 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ALT_CNST_UK01 | 1 | | | 2 (0)| 00:00:01 |
|* 14 | FILTER | | | | | | |
|* 15 | HASH JOIN RIGHT OUTER | | 193K| 18M| | 11961 (2)| 00:02:24 |
| 16 | TABLE ACCESS FULL | COUNTRY | 406 | 6902 | | 3 (0)| 00:00:01 |
|* 17 | HASH JOIN RIGHT OUTER | | 193K| 15M| 1240K| 11955 (2)| 00:02:24 |
| 18 | TABLE ACCESS FULL | LOCATION_TBL | 60329 | 530K| | 80 (2)| 00:00:01 |
|* 19 | HASH JOIN | | 193K| 13M| 5176K| 11013 (2)| 00:02:13 |
|* 20 | TABLE ACCESS FULL | ADRESS_L_TBL | 189K| 2953K| | 2460 (3)| 00:00:30 |
| 21 | TABLE ACCESS FULL | ADRESS_TBL | 1291K| 72M| | 3936 (2)| 00:00:48 |
-----------------------------------------------------------------------------------------------------------------
Here is some more info:
In Production:
Total count/num_rows
select count(*) from dis_zip_codes
actual count 60333 num rows: 60757
select count(*) from ADRESS_TBL
actual 1309298 num rows 1289456
select count(*) from ADRESS_L_TBL
actual 1574672 ,num rows 1549773
select count(*) from COUNTRY
406
Any suggestion other than to make statistics upto date. Does statistics is an issue here?
Regards
Ved
CM: tiedied up formatting of the explain plans.
[Updated on: Fri, 04 June 2010 09:05] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
