Home » RDBMS Server » Performance Tuning » works in test env but very slow in production
works in test env but very slow in production [message #293327] Fri, 11 January 2008 13:00 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member

It seems simple yet, finding difficult to trace the problem.

I have this following simple SQL which works great in test env, but does not give me result quickly in prod since it is doing a full table scan on it.

select *
from person_detail pe
where pe.person_key = 964459874;

in both TEST and PROD this table has similar volume of records. TEST has 75M records while PROD has 79M records. TEST has a snapshop of production few days back.

Table structure and indexes are same in both the environements. Both the databases are on 10g R2.

Test database produces results in 31 mili seconds while the prod does not return until 5 min.

Checked the explain plan in both environments.
In the PROD, it is doing a full table scan in stead of using global index scan.
the TEST environment is using correctly the global index scan.

I tried recreating the index on PROD, tried analyzing the index, compute table stats. Nothing works.
What's wrong the PROD environment.

Can anyone please help.
Thanks
Re: works in test env but very slow in production [message #293328 is a reply to message #293327] Fri, 11 January 2008 13:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> tried analyzing the index, compute table stats.
Please post exactly what you did.
Did you use DBMS_STATS?
Re: works in test env but very slow in production [message #293357 is a reply to message #293327] Fri, 11 January 2008 15:18 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
used
DBMS_STATS.GATHER_TABLE_STATS for the table that is used in the SQL.
Thanks

[Updated on: Fri, 11 January 2008 15:19]

Report message to a moderator

Re: works in test env but very slow in production [message #293358 is a reply to message #293357] Fri, 11 January 2008 15:21 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

used
DBMS_STATS.GATHER_SYSTEM_STATS for the table that is used in the SQL.


Did you gather stats on table? Using dbms_stats?
Try
dbms_stats.gather_table_stats('OWNER','TABLE',METHOD_OPT=>'For all indexed columns size 250',cascade=>true);

if does not give desired results, use AUTO SIZE

since, you updated your post, ignore whatever is not relevant.
Again,
Post exactly what you did. Explaining what you did does not help.

[Updated on: Fri, 11 January 2008 15:25]

Report message to a moderator

Previous Topic: PL SQL Merge of tables - efficiency
Next Topic: Help- Tuning Query
Goto Forum:
  


Current Time: Wed Dec 07 03:18:42 CST 2016

Total time taken to generate the page: 0.08420 seconds