|
-
When the query is executed on Prod DB, I observed that the particular oracle unix session is taking up 1 CPU out of the 32 and is working 100% for the full 5+mins. If I kick off multiple SQLPLUS sessions running the same query, multiple CPUs will be working 100% each too.
I observe this CPU info from unix command "mpstat 2 100".
This Prod server had OS hardening done to it before.
However, I could not determine if that is the root cause as there was not performance test run performed before and after hardening.
Since the explain plan and stats are the same on both Prod and Dev DBs, I would assume we can rule out the Oracle optimizer and bad SQL on this 2 systems. The query uses a costly UNION. Even though it is recommended to use UNION ALL instead of UNION in the query, it did not improve the response time on Prod DB.
Appreciate any advise as to how to narrow down the root cause of my performance issue.
Cheers
Orac
Last edited by oracbase; 05-22-2009 at 07:53 AM.
What's next after 10g?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote