| works in test env but very slow in production [message #293327] |
Fri, 11 January 2008 13:00  |
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 #293358 is a reply to message #293357] |
Fri, 11 January 2008 15:21  |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
