-
Production Server Performance issue – Oracle 9.2.0.1 Windows
I have a heavy report (Crystal Report – Oracle ODBC) that initially retrieve data through a db link. It took about 1hour in development environment, and in production environment it threw ‘Rollback segment too old’ and never ended.
In order to improve the report, I made 2 materialized views to replace the db link.
This change reduced time response by 50% or more, in development environment, everyone was happy, but in production it went on as is nothing had changed.
I asked about memory configuration in both environments:
Development: Windows 2000 -Sever 1 GB RAM. 2 Oracle 9.2.0.1 instances Dedicate Server
SH Pool: 32 MB
B. Cache: 72 MB
L. Pool : 8 MB
J. Pool :32 MB
PGA: 24 MB
Cache hit % : 60%
Production: Windows 2000 - Sever 3.5 GB RAM. 2 Oracle 9.2.0.1 instances (about 700 MB to each…) about 70 users. Dedicated Server
SH Pool: 580 MB
B. Cache: 32 MB
L. Pool : 8 MB
J. Pool :32 MB
PGA: 16 MB
Cache hit % : 41%
When I saw it, I was almost pretty sure, there was the problem, so I asked to change memory configuration in Production Server in this way:
SH Pool: 220 MB
B. Cache: 200 MB
L. Pool : 8 MB
J. Pool :32 MB
PGA: 500 MB
Cache hit % : 77%
But there wasn’t any changes. In Development environment the report, (depending on settings), takes 8 minutes, the same report, same settings, in production has to be stopped after 40 minutes without response.
Nobody complaint about performance in any other situation now or before, is just this only report (which is very important) with this problem.
I asked for pagination problems, there weren’t.
I asked to run analyze for tables and indexes…
I asked about tablespaces storage they are ok, with enough space.
Has anyone any idea or suggestion to try to improve performance in this case?
Thanks in advance.
-
how about tracing the query, getting the explain plan, running a 10046 trace to see where the time is being taken instead of guessing and playing around with some memory parameters?
-
What is the refresh rate on the view? Are the objects refrenced by the query indexed alike? What was the datasize and load size differences between the DEV and PROD? As the previous suggestions, start with explain plan to see the query execution path? Also, check for the optimizer setup on PROD.
Hope it would help you.
Thanx
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Originally Posted by sambavan
What is the refresh rate on the view? Are the objects refrenced by the query indexed alike? What was the datasize and load size differences between the DEV and PROD? As the previous suggestions, start with explain plan to see the query execution path? Also, check for the optimizer setup on PROD.
Hope it would help you.
Thanx
Sam
Sambavan,
Thanks a lot for your advice I'll follow it.
The datasize and load size is the same in both instances, because dev is updated every day by an import, optimizer mode is CHOOSE in both env.
I´ll go on with explain plan.
Thank you very much.
-
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
|