Problem solve Get help with specific problems with your technologies, process and projects.

Working with prediction views in 9i

This is about the prediction views in 9i. I am on Oracle9i Enterprise Edition Release - 64-bit Production Sun Sparc, and the application is Oracle Financials 11i (11.5.8). The intranet pc-to-pc network here is really slow. I need to get the database to a state to where the hit ratios are acceptable, once we start our dataload sims.

I am running Statspack snaps at level 10, once an hour, but the dataload simulaton is not read yet, so there is virtually no load on the system and there are no users pounding away at it. We are ramping up to do user load testing soon. The simulation tests will do things like have 500 users create multiple lines POs, etc.

I want to see if you use the following:
1) Bufer Cache Size Advisor view and put the db_cache_advice=ready in the init.ora file and rely on the prediction information if a significant load has been run through the system?
2) The Shared Pool Size Advice view -- can it be trusted as far as trending?
3) The PGA Aggregate Target Advice view. Are you changing it based on the cache hit precentage increasing?
I use the V$DB_CACHE_ADVICE all the time now! I don't rely on the old Buffer Cache Hit Ratio to tune the buffer cache. Instead, I use the V$DB_CACHE_ADVICE view to help me determine a good size for my Buffer Cache. With this view and the advice you get, it is better to simulate a "normal" workload to get the best advice possible.

As near as I can tell, the V$SHARED_POOL_ADVICE view is as good as the V$DB_CACHE_ADVICE view.

As for the PGA_AGGREGATE_TARGET parameter, I use a number of views. I have a white paper that I will be posting on my Web site (http://www.peasland.net) which talks about how to use these views to tune the PGA_AGGREGATE_TARGET parameter. I will be posting this paper in the next week so look for it there!

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.