Hello,

I have an environment with Java applications with a growing number of
connections that I'd like to determine:

1. If my PGA and session related values appropriate?
2. What are some best practices on changing the related values for PGA
and connections?

Thanks,

Michael42

"Michael42" <melliott42@yahoo.com> wrote in news:1139447002.522044.103990
@z14g2000cwz.googlegroups.com:

> Hello,
>
> I have an environment with Java applications with a growing number of
> connections that I'd like to determine:
>
> 1. If my PGA and session related values appropriate?

Define "appropriate" based upon which metric(s).
Quantify appropriate.


> 2. What are some best practices on changing the related values for PGA
> and connections?

Do not change any value in production until you've have sufficient
benchmarks to validate the results.

WRT to "connections", make sure you have enough of them & resources to
handle the SQL coming thru the connections.

On Wed, 08 Feb 2006 17:03:22 -0800, Michael42 wrote:

> 1. If my PGA and session related values appropriate?
> 2. What are some best practices on changing the related values for PGA
> and connections?

PGA is used primarily for sorts and hashes. V$SYSSTAT and V$SESSTAT have
statistics that tell you how many sorts have gone to the disk and how many
have not.
1* select name from v$statname where lower(name) like '%sort%'
SQL> /

NAME
----------------------------------------------------------------
sorts (memory)
sorts (disk)
sorts (rows)

Unfortunately, nothing tells you how many hash operations have gone
to disk. That is probably the most significant measure that Oracle
misses right now. You will have to rely on V$PGASTAT which will tell you
the maximum of PGA in use at any time. The common wisdom tells you that
you don't want frequent memory adjustment operations to take place, sto
that would be an ideal choice for the PGA aggregate target.

--
http://www.mgogala.com

>> Define "appropriate" based upon which metric(s).

How can I determine if my PGA and\or connection related spfile settings
are effective or need to be changed?

This alludes to:
1. What needs to be monitored?
2. What types of values would warrant a change?

I do not know.

Thanks for your advice,

Michael42

OEM has pretty advisors, including one for the PGA. Basically does,
and is limited by, what Mladen says.

Try searching for pga_aggregate_target at http://tahiti.oracle.com .
Start with the concepts manual.

Don't forget to post proper information:
http://www.dbaoracle.net/readme-cdos.htm

jg
--
@home.com is bogus.
http://www.zillow.com/search/Search....CA&mode=search

On Thu, 09 Feb 2006 15:06:49 -0800, Michael42 wrote:

> 1. What needs to be monitored?

V$PGASTAT

--
http://www.mgogala.com

"Michael42" <melliott42@yahoo.com> wrote in news:1139526409.150874.5000
@z14g2000cwz.googlegroups.com:

>>> Define "appropriate" based upon which metric(s).
>
> How can I determine if my PGA and\or connection related spfile settings
> are effective or need to be changed?
>
> This alludes to:
> 1. What needs to be monitored?
> 2. What types of values would warrant a change?
>
> I do not know.
>
> Thanks for your advice,
>
> Michael42
>

No single metric is any where close to being a decent indicator of overall
database performance.

In many/most cases the biggest bang for the buck WRT performance tuning
comes from identifying the poorset running SQL statements & tuning them.

You might consider getting this book, if are serious about Oracle tuning.

Optimizing Oracle Performance

Cary Millsap, Jeff Holt
O'Reilly & Associates, Paperback, Published September 2003, 388 pages, ISBN
059600527X