| |
 |
|
Oracle Tips by Burleson Consulting
|
Using the Oracle v$sysstat
view
The following query gives the total number and the percentage of
times work areas were executed in these three modes since the
database instance was started.
work_area.sql
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 output of this query might look like the following:
PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0
This output of this query is used to tell the DBA when to
dynamically adjust pga_aggregate_target. In general the value of
pga_aggregate_target should be increased when multi-pass executions
is greater than zero, and reduced whenever the optimal executions is
100%
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
