Home » RDBMS Server » Performance Tuning » Environment problem... (9.2.0.6)
Environment problem... [message #386005] Thu, 12 February 2009 03:47 Go to next message
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 #386007 is a reply to message #386005] Thu, 12 February 2009 03:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Are the stats updated in production?
In certain cases, even the small difference in data distribution could matter a lot.
Re: Environment problem... [message #386013 is a reply to message #386007] Thu, 12 February 2009 04:05 Go to previous messageGo to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Yes, the tables involved have been analyzed in both the environments before the queries were executed.
Re: Environment problem... [message #386018 is a reply to message #386013] Thu, 12 February 2009 04:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How exactly?
Where the indexes analyzed too?
Re: Environment problem... [message #386027 is a reply to message #386005] Thu, 12 February 2009 04:48 Go to previous messageGo to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

No, the indexes that these queries are using were created only recently(just a week back). The statistics on tables were collected using the ANALYZE command.
Re: Environment problem... [message #386046 is a reply to message #386005] Thu, 12 February 2009 06:02 Go to previous messageGo to next message
cookiemonster
Messages: 12405
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 #386063 is a reply to message #386027] Thu, 12 February 2009 07:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to collect stats on indexes too.
Only in 10g, with certain options, with rebuilding indexes the stats are automatically collected.
And use dbms_stats.
Re: Environment problem... [message #386064 is a reply to message #386063] Thu, 12 February 2009 07:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12405
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 Go to previous message
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.
Previous Topic: Inconsistent query behaviour
Next Topic: How to reduce the optimizer cost for the below said query
Goto Forum:
  


Current Time: Tue Dec 06 02:49:04 CST 2016

Total time taken to generate the page: 0.14835 seconds