Hi :
Recently a olap system's cpu utilization is pretty high on the morning , I notice that there are a few reports running at that time.
So I want to tune this,first I want to tune the PGA size because there are a log of sort in those reports.
i have read the online document
http://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA95344 1) I query the view v$pgastat I got the 'cache hit percentage' is 82%,I know this is pretty low .
2) I query the SQL below ,but the result seems good
SELECT name profile, cnt, DECODE(total, 0, 0, ROUND(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (SUM(value) over ()) total
FROM V$SYSSTAT
WHERE name
LIKE 'workarea exec%');
the result is :
workarea executions - optimal 384089836 99
workarea executions - onepass 1981161 1
workarea executions - multipass 0 0
3) I query the V$PGA_TARGET_ADVICE , the suggestion size is same as my PGA size
So I do not whether my PGA size is set correctly .
Please correct me if I have something wrong or give me some advice on this confusion.
We setup the PGA to try be a "good size" for ALL users of the database. As such, there are limits on what a single session is allowed to consume - because it might be to the detriment of the others.
However, sometimes you WANT a single session to get some special treatment. In that case, for that single session you might flick back to manual pga, eg
alter session set workarea_size_policy = manual;
alter session set sort_area_size = ...;
alter session set sort_area_retained_size = ...;
and then run the queries.
There are equivalent techniques that let you stay under the auto pga scheme, but still get access to a larger "slice of the pie", but these involve underscore parameters so I'll direct you to liaise with Support for that.