Database Reference
In-Depth Information
select 'total: ' || a.name, sum(b.value)
from v$statname a, v$sesstat b, v$session c
where a.statistic# = b.statistic#
and (a.name like '%ga %'
or a.name like '%direct temp%')
and b.sid = c.sid
and c.username is not null
group by 'total: ' || a.name
);
l_first_time := false;
end if;
end loop;
end;
/
commit;
This script sorts the big table
T
using
PGA
automatic memory management. Then, for that session, it captures all
of the
PGA/UGA
memory settings as well as sort-to-disk activity. In addition, the
UNION ALL
adds system-level metrics
about the same (total PGA memory, total UGA memory and so on). I ran that script against a database started with the
following initialization settings:
*.compatible='12.1.0.1'
*.control_files='/u01/dbfile/ORA12CR1/control01.ctl','/u02/dbfile/ORA12CR1/control02.ctl'
*.db_block_size=8192
*.db_name='ORA12CR1'
*.pga_aggregate_target=256m
*.sga_target=256m
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.undo_tablespace='UNDOTBS1'
These settings show I was using automatic
PGA
memory management with a
PGA_AGGREGATE_TARGET
of 256MB,
meaning I wanted Oracle to use up to about 256MB of
PGA
memory for sorting.
I set up another script to be run in other sessions to generate a large sorting load on the machine. This script
loops and uses a built-in package,
DBMS_ALERT
, to see if it should continue processing. If it should, it runs the same big
query, sorting the entire
T
table. When the simulation finished, a session could signal all of the sorting processes, the
load generators, to “stop” and exit. Here's the script (stored in a file named
gen_load.sql
) used to perform the sort:
declare
l_msg long;
l_status number;
begin
dbms_alert.register( 'WAITING' );
for i in 1 .. 999999 loop
dbms_application_info.set_client_info( i );
dbms_alert.waitone( 'WAITING', l_msg, l_status, 0 );
exit when l_status = 0;
for x in ( select * from t order by 1, 2, 3, 4 )
