Home » RDBMS Server » Performance Tuning » Query perfomance (Oracle 10 g)
Query perfomance [message #392598] Wed, 18 March 2009 10:30 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I am today troubleshooting a query perfomance problem.
Runs good in UAT but not in PROD. i have verified the execution plan and it goes for a FULL TABLESCAN for Table1 & Table with hight cost, which is not the case in UAT.

BUT When i checked the activity through Oracle EM ,it shows high activity (DB FILE SEQUENTIAL READ) for another table , Table3 .

Any idea what is the catch here.Should i consider the first case or second.Any tips are highly appreciated.
Re: Query perfomance [message #392601 is a reply to message #392598] Wed, 18 March 2009 10:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Statistics. Are they updated. If so, how?
Re: Query perfomance [message #392603 is a reply to message #392598] Wed, 18 March 2009 10:46 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
we have weekly stats collection and must be through DBMS_STATS .
i have compared NUM_ROWS and count(*) are almost similar
Re: Query perfomance [message #392605 is a reply to message #392603] Wed, 18 March 2009 10:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>must be through DBMS_STAT
How exactly?
Post the statement. Even a small difference in data
distribution could matter a lot.
If the stats are collected using same method in both
environments, look into sql.
Re: Query perfomance [message #392610 is a reply to message #392598] Wed, 18 March 2009 11:09 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
both the environment use DBMS_STATS with same params.We use same shell script in both environments.

And about the SQL, yes that must be the case.

but still i am wondering why its going for DB FILE SEQUENTIAL READ for table3 .This is the high activity it shows in Oracle EM.
Re: Query perfomance [message #392611 is a reply to message #392610] Wed, 18 March 2009 11:15 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>but still i am wondering why its going for DB FILE SEQUENTIAL READ for table3
So are we.
You have not posted anything useful, but just explained your observations.
No SQL, No plan, no stats information, no trace and no nothing.
Good luck!.
Previous Topic: How to release the un-used INDEX space, which were created by DELETE
Next Topic: Is there any way to rebuilds indexes online in Oracle SE !!
Goto Forum:
  


Current Time: Sat Dec 03 08:07:34 CST 2016

Total time taken to generate the page: 0.04897 seconds