| Environment problem... [message #386005] |
Thu, 12 February 2009 03:47  |
jkmurthy
Messages: 22
Registered: May 2005
Location: India
|
Junior Member |
|
|
Hi,
We have a query that is taking differnt times in pre-production and production environments. When its taking 1 sec in pre-prod, its taking 8 secs in prod.
The query is pasted below:
UPDATE FIMS_FIN_RPT_SUMRY x SET (x.Retail_TOTAL)=( SELECT SUM (NVL(NEW_FLG,0) + NVL(USED_FLG,0)) AS Retail_TOTAL from (SELECT a.sales_person_id user_id, a.retailer_id, new_flg,used_flg FROM FIMS_DOR a WHERE delivery_dt >= to_date( '01-JAN-2008','DD-MON-YYYY') AND delivery_dt <= to_date('31-DEC-2008','DD-MON-YYYY') AND NVL(close_match_flg,'N') = 'N' AND NVL(insurance_flg,'N') = 'N' AND a.country_cd = 'GB' AND a.sales_person_id is not null union all SELECT a.fi_manager_id user_id, a.retailer_id, new_flg,used_flg FROM FIMS_DOR a WHERE delivery_dt >= to_date( '01-JAN-2008','DD-MON-YYYY') AND delivery_dt <= to_date('31-DEC-2008','DD-MON-YYYY') AND NVL(close_match_flg,'N') = 'N' AND NVL(insurance_flg,'N') = 'N' AND a.country_cd = 'GB' AND a.fi_manager_id is not null and a.fi_manager_id <> a.sales_person_id ) a WHERE x.user_id = a.user_id AND a.RETAILER_ID IN (SELECT RETAILER_ID FROM gss_USER_RETAILER_MAP WHERE USER_ID = 'COLIN.USER@TEST.COM' AND MANAGED_BY = 'Y') GROUP BY a.user_id )
The execution plan of the query at both the environments is provided in the attached document.
We verified the oracle parameters as well and found that pre-prod and prod are exactly same except for the cpu_count parameter which is 12 in pre-prod and 16 in production!
Other observation that we could make is that pre-prod environment has slightly lesser data than production.
Please let me know what could be the cause of this difference.
Regards
Keshav
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Environment problem... [message #386046 is a reply to message #386005] |
Thu, 12 February 2009 06:02  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That execution plan you posted is COMPLETELY unreadable.
It might say it's a csv but it isn't - looks suspiciously like a binary.
Post the execution plans in plain text.
|
|
|
|
|
|
| Re: Environment problem... [message #386064 is a reply to message #386063] |
Thu, 12 February 2009 07:31 |
jkmurthy
Messages: 22
Registered: May 2005
Location: India
|
Junior Member |
|
|
The execution plan in both environments is as below:
-- preprod
UPDATE STATEMENT Cost = 2,
UPDATE FIMS_FIN_RPT_SUMRY ,
TABLE ACCESS FULL FIMS_FIN_RPT_SUMRY ,
SORT GROUP BY NOSORT ,
HASH JOIN SEMI ,
VIEW ,
UNION-ALL ,
TABLE ACCESS BY GLOBAL INDEX ROWID FIMS_DOR ,
INDEX RANGE SCAN IDX_FIMS_DOR_SALESPERSON ,
TABLE ACCESS BY GLOBAL INDEX ROWID FIMS_DOR ,
INDEX RANGE SCAN IDX_FIMS_DOR_FIMANAGER ,
VIEW VW_NSO_1 ,
TABLE ACCESS FULL GSS_USER_RETAILER_MAP ,
--prod
UPDATE STATEMENT Cost = 2,
UPDATE FIMS_FIN_RPT_SUMRY ,
TABLE ACCESS FULL FIMS_FIN_RPT_SUMRY ,
SORT GROUP BY NOSORT ,
HASH JOIN SEMI ,
VIEW ,
UNION-ALL ,
TABLE ACCESS BY GLOBAL INDEX ROWID FIMS_DOR ,
INDEX RANGE SCAN IDX_FIMS_DOR_SALESPERSON ,
TABLE ACCESS BY GLOBAL INDEX ROWID FIMS_DOR ,
INDEX RANGE SCAN IDX_FIMS_DOR_FIMANAGER ,
VIEW VW_NSO_1 ,
TABLE ACCESS FULL GSS_USER_RETAILER_MAP ,
|
|
|
|
| Re: Environment problem... [message #386066 is a reply to message #386005] |
Thu, 12 February 2009 07:34 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So - same plan in both cases.
Is the data the same in both DBs?
If so all you can do is trace the execution of the statement in both DB's and compare the tkprofs to see where all the extra time is being spent.
|
|
|
|
| Re: Environment problem... [message #386068 is a reply to message #386064] |
Thu, 12 February 2009 07:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
How is your Pre-prod database created?
Is it an Imp/Exp copy?
If so, that might be the cause, as the distribution of data may well be completely different.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
