SQL> exec plato.tuning('HTM',300);
TRUDBA Database report (Tuning)
The PLATO Package, version 45, build 1046
Report generated on 29-Sep-05 21:57:51
Geert De Paep, http://www.dba-village.com


General Files Storage Rbs/Undo Bckp/reco Problems Tables Jobs/AQ Memory Security Networking Various DBFeatures

Tuning information during 300 seconds top

Stats increase (300 secs)
Parameter Value
Logical reads 185954
Logical reads/second 615.74
Physical reads 19651
Physical reads/second 65.06
Buffer cache hit ratio this period 89.43

Session stats increase during 300 secs
Sid Ser# Username Program LogicRds CPU PhysRds RedoSize TScanRows SortsMem Status LastCallEt SQL Hash
Sysstat Sysstat 181.3K 2.1K 19.2K 1.2M 926.6K 736 0
1 1 oracle@trudba.iconos.be (PMON)
0
0
0
0
0
0
ACTIVE 1484489 0
2 1 oracle@trudba.iconos.be (DBW0)
0
0
0
5.2K
0
0
ACTIVE 1484489 0
3 1 oracle@trudba.iconos.be (LGWR)
0
0
0
0
0
0
ACTIVE 1484489 0
4 1 oracle@trudba.iconos.be (CKPT)
0
0
0
0
0
0
ACTIVE 1484489 0
5 1 oracle@trudba.iconos.be (SMON)
54
0
7
720
21
0
ACTIVE 1484489 2095543314
6 1 oracle@trudba.iconos.be (RECO)
0
0
0
0
0
0
ACTIVE 1484489 2618850598
7 1 oracle@trudba.iconos.be (CJQ0)
230
0
0
0
0
59
ACTIVE 1484489 0
8 1 oracle@trudba.iconos.be (QMN0)
7.2K
0
3.3K
181.7K
12.6K
0
ACTIVE 1484489 4223295093
9 1 oracle@trudba.iconos.be (QMN1)
7.2K
0
3.7K
181.6K
12.6K
0
ACTIVE 1484489 4223295093
11 49732 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
509
30
272
1.8K
51.5K
11
INACTIVE 8 0
17 5391 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
93
12
22
0
2.3K
0
INACTIVE 1 0
22 14044 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
428
25
203
1.9K
28.4K
11
INACTIVE 34 0
54 28676 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
871
53
157
4.6K
57.7K
22
INACTIVE 67 0
65 23349 SYSMAN OMS
0
0
0
0
0
0
INACTIVE 2065 2337849758
68 10700
0
0
0
0
0
0
ACTIVE 8438 1667689875
99 35576 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
12
3
2
0
0
0
INACTIVE 155 0
101 29553
0
0
0
0
0
0
ACTIVE 13929 3216157725
105 15398 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
262
18
30
1.8K
24.2K
10
INACTIVE 10 0
120 28276 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
779
44
175
3.7K
76.6K
21
INACTIVE 1 0
122 29796 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
371
27
154
1.8K
28.2K
10
INACTIVE 15 0
146 29162 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
69
3
18
0
2.3K
0
INACTIVE 48 0
161 20894 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
1.8K
158
946
1.9K
58.1K
23
INACTIVE 16 0
186 15340 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
92
6
25
0
3.1K
0
INACTIVE 10 0
196 1423 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
491
25
251
1.8K
51.5K
11
INACTIVE 34 0
197 15035 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
495
27
251
1.9K
51.5K
11
INACTIVE 47 0
198 19505 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
29
2
8
0
817
0
INACTIVE 102 0
211 21510 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
917
45
494
3.6K
100.6K
22
INACTIVE 48 0
213 31136 SYSMAN OMS
0
0
0
0
0
0
INACTIVE 2065 0
214 29083 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
109.4K
500
3.3K
1.2K
25.1K
11
INACTIVE 45 0
242 20235 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
29
4
8
0
817
0
INACTIVE 67 0
246 9334
0
0
0
0
0
0
ACTIVE 3966 1315911653
250 25870 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
528
23
269
2.0K
50.6K
10
INACTIVE 44 0
258 24537 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
41
5
10
0
817
0
INACTIVE 59 0
283 24185 ZAIN ? @fenris.dba-village.com (TNS V1-V3)
5.5K
140
1.9K
3.1K
99.8K
29
INACTIVE 40 0

SQL stats increase during 300 secs
Hash value Exec Buffer Disk Cpu (sec) SQL
392662514 1 103.9K 3.3K 4.58 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null;
null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if (owa_match.match_pattern('dvp_articles.listsectoren', simple_list__, complex_list__, true))
then rc__ := 2; else null; null; dvp_articles.listsectoren; if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit;
owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
3355109315 11 103.3K 3.2K 4.36 SELECT id, title, inhoud from dvt_articles art, dvt_cat_art cat where cat_sub_id = :b1 and art.id = cat.artikel_id and
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') = (select max(to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy'))
from dvt_articles art, dvt_cat_art cat
where cat_sub_id = :b1 and art.id = cat.artikel_id)
1350504753 2.4K 4.9K 0 .41 SELECT MAX(LOG_LEVEL) FROM SVT_GRAPHS WHERE ID = :b1
815501214 20 14.4K 7.0K 1.06 select t.schema, t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, system.aq$_queues q where aft.table_objno =
t.objno and aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name,
aft.table_objno skip locked
2762817116 5 25.9K 196 4.64 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN pandora_DBMON(60); :mydate := next_date; IF broken THEN :b := 1;
ELSE :b := 0; END IF; END;
1795127194 2 10.0K 5.0K 2.48 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null;
null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if (owa_match.match_pattern('dvp_base.main', simple_list__, complex_list__, true)) then rc__ :=
2; else null; null; dvp_base.main; if (wpg_docload.is_file_download) then rc__ := 1; wpg_docload.get_download_file(:doc_info); null; null;
null; commit; else rc__ := 0; null; null; null; commit; owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
1503154276 20 9.5K 7.0K .51 select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema,
exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protocol from MAILER.MLT_MAIL_QT where time_manager_info <=
:1 and state != :2 for update skip locked
3076344681 1 8.1K 4.3K 1.88 SELECT id, title, public_dag, public_maand from (select id, title, public_dag, public_maand from dvt_articles where benelux_vlag = 'J'
and status != 'H' and to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') <= trunc(sysdate) order by
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc, id desc) where rownum <= 5
4247629354 1 8.3K 3.1K 2.78 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null;
null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if (owa_match.match_pattern('dvp_articles.listbeneluxnews', simple_list__, complex_list__, true))
then rc__ := 2; else null; null; dvp_articles.listbeneluxnews; if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit;
owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
1323212220 1 4.0K 1.7K 1.43 SELECT * from dvt_articles where benelux_vlag = 'J' and status != 'H' order by to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc,
id desc
3438667703 1 5.4K 818 1.65 INSERT INTO plato_temp_uptime_gdp (module, n1,n2,n3,n4,n5) SELECT 'SQLSTATS', hash_value, executions, buffer_gets, disk_reads, cpu_time FROM v$sqlarea
561879453 7 3.0K 1.3K 1.4 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null;
null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if (owa_match.match_pattern('dvp_articles.articledetail', simple_list__, complex_list__, true))
then rc__ := 2; else null; null; dvp_articles.articledetail(ArtikelIdA=>:ArtikelIdA); if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit;
owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
4184193333 20 2.5K 1.7K 1.01 SELECT * from (select * from dvt_counter_articles where creatie_dt > (sysdate - 31) order by aantal desc) where rownum < 11
3231397038 2 10.8K 256 .78 declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null;
null; simple_list__(1) := 'sys.%'; simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%'; simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if (owa_match.match_pattern('dvp_articles.zinews', simple_list__, complex_list__, true)) then
rc__ := 2; else null; null; dvp_articles.zinews(SectorIdA=>:SectorIdA,ToonA=>:ToonA); if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null; null; null; commit;
owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
90447220 2 10.8K 256 .76 SELECT id, replace(title, '''', '') as title, public_dag, public_maand from dvt_articles art, dvt_cat_art catart where status = 'P' and id = artikel_id
and cat_sub_id = :b1 and to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') <= trunc(sysdate) order by
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc, id desc
1566662575 540 2.3K 47 .18 SELECT * FROM SVT_DATA WHERE GRAPH_ID = :b1 AND AGLVL_ID = :b2 AND FLOOR_X = :b3
1459198444 400 3.1K 1 .26 UPDATE svt_dbmon SET seq# = :b5, udate = sysdate, value = :b4, delta_value = :b3, delta_value_per_sec = :b2 WHERE name = :b1
2980007276 540 1.0K 0 .05 SELECT * FROM SVT_AGLEVELS WHERE ID = :b1
205559619 540 1.0K 0 .03 SELECT * FROM SVT_GRAPH_LEVELS WHERE GRAPH_ID = :b1 AND AGLVL_ID = :b2
2538187 396 2.1K 2 .31 UPDATE SVT_DATA SET Y=:b1,NR_AGGREGATED=NR_AGGREGATED + 1 WHERE GRAPH_ID = :b2 AND AGLVL_ID = :b3 AND FLOOR_X = :b4
2495772971 459 1.1K 0 .16 SELECT VARRAY_POS FROM SVT_MEASURES WHERE GRAPH_ID = :b1 AND NAME = :b2

System wait events
event Waits TimeWaited(sec)
latch free 6
.02
wait for unread message on broadcast channel 233
250.57
control file sequential read 405
1.08
control file parallel write 98
2.05
buffer busy waits 1
.04
log file parallel write 237
4.36
LGWR wait for redo copy 4
.07
log file sync 36
.94
db file sequential read 4134
28.34
db file scattered read 1593
20.36
db file parallel write 7
.88
direct path read 39
.3
direct path write 4
.09
direct path read (lob) 178
1.3
virtual circuit status 10
314.59
dispatcher timer 5
314.57
SQL*Net message to client 715
.01
SQL*Net more data to client 1401
.13
SQL*Net break/reset to client 10
.01

SQL of active sessions above
Address Hash SQL Text
0000000408EE
35E0
2095543314 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
000000040636
AC28
2618850598 select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, state, status, heuristic_dflt, session_vector, reco_vector,
3600*24*(sysdate-reco_time), 3600*24*(sysdate-nvl(heuristic_time,fail_time)), global_commit#, type# from pending_trans$ where session_vector !=
'00000000'
000000040639
B3C8
4223295093 select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema,
exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protocol from SYSMAN.MGMT_NOTIFY_QTABLE where msgid = :1
000000040E6B
4740
2337849758 select version, component_mode from mgmt_versions where component_name='CORE'
000000040A5C
C320
1667689875 SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
000000040666
C1D8
3216157725
000000040ACE
48E8
1315911653 SELECT c.target_guid, c.metric_guid, c.store_metric, c.schedule, c.coll_name, m.metric_name, m.eval_func FROM MGMT_METRIC_COLLECTIONS_REP r,
MGMT_METRIC_COLLECTIONS c, MGMT_METRICS m WHERE c.suspended = 0 AND c.is_repository = 1 AND (c.last_collected_timestamp IS NULL
OR c.last_collected_timestamp + c.schedule / 1440 < SYSDATE) AND c.metric_guid = m.metric_guid AND r.target_guid = c.target_guid AND
r.metric_guid = c.metric_guid AND r.coll_name = c.coll_name

Top 7 SQL sorted by buffer gets per execution (since startup)
Statement Buf/exec Dsk/exec Exectns BufGets DiskRds
BEGIN :a := nmp_monitor.CheckStorage; END; 395906 35985 74 27.9M 2.5M
select s.tablespace_name, s.owner, s.segment_name, s.bytes, f.maxfree, s.next_one, s.next_two,
s.next_three, s.next_four, t.extent_management, t.allocation_type from (SELECT
s.tablespace_name, s.owner, s.segment_name, s.bytes, s.next_extent AS next_one,
s.next_extent*(1+s.pct_increase/100) AS next_two, s.next_extent*(1+s.pct_increase/100)
*(1+s.pct_increase/100) AS next_three, s.next_extent*(1+s.pct_increase/100)
*(1+s.pct_increase/100)
*(1+s.pct_increase/100) AS next_four FROM dba_segments s, dba_tablespaces t WHERE s.tablespace_name
= t.tablespace_name AND t.extent_management = 'DICTIONARY' UNION ALL SELECT s.tablespace_name,
s.owner, s.segment_name, s.bytes, t.next_extent, t.next_extent, t.next_extent, t.next_extent FROM
dba_segments s, dba_tablespaces t WHERE s.tablespace_name = t.tablespace_name AND
t.extent_management = 'LOCAL' AND allocation_type = 'UNIFORM' UNION ALL SELECT s.tablespace_name,
s.owner, s.segment_name, s.bytes, 0, 0, 0, 0 FROM dba_segments s, dba_tablespaces t WHERE
s.tablespace_name = t.tablespace_name AND t.extent_management = 'LOCAL' AND allocation_type = 'SYSTEM'
) s, (select tablespace_name, max(bytes) as maxfree from (select tablespace_name, bytes
from dba_free_space union all select tablespace_name, maxbytes - least(maxbytes,bytes) as bytes
-- Note: greatest, because current size can be > autoext maxsize from
dba_data_files where autoextensible = 'YES' ) group by tablespace_name ) f,
dba_tablespaces t, nmt_tablespace_usage tu2,
nmt_tablespace_usage tu3 where s.tablespace_name = f.tablespace_name AND s.tablespace_name =
t.tablespace_name and next_one+next_two+next_three+next_four > maxfree and
t.tablespace_name = tu2.tablespace_name(+) and tu3.tablespace_name='DEFAULT'
and nvl(tu2.check_cannotalloc,tu3.check_cannotalloc) = 'Y'
142153 11332 74 10.0M 818.8K
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN
emd_maintenance.analyze_emd_schema('SYSMAN'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END
IF; END;
118440 4599 5 578.3K 22.4K
select owner,segment_name,extents, max_extents, max_extents-extents nr_ext_gap from
dba_segments s where segment_type not in ('CACHE','CLUSTER')
113965 13968 74 8.0M 1009.3K
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_articles.listsectoren', simple_list__, complex_list__, true)) then rc__ := 2;
else null; null; dvp_articles.listsectoren; if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null;
null; null; commit; owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
105875 1898 313 31.6M 579.8K
select owner,segment_name,segment_type,extents from dba_segments s, dba_tablespaces t
where s.tablespace_name = t.tablespace_name and t.extent_management <>
'LOCAL' order by extents desc
72819 2357 74 5.1M 170.3K
BEGIN plato.help; END; 70512 9455 1 68.8K 9.2K

Top 7 SQL sorted by executions (since startup)
Statement Buf/exec Dsk/exec Exectns BufGets DiskRds
SELECT MAX(LOG_LEVEL) FROM SVT_GRAPHS WHERE ID = :b1 2 1 12498886 23.8M 188
SELECT * FROM SVT_AGLEVELS WHERE ID = :b1 2 1 2608308 4.9M 189
SELECT * FROM SVT_GRAPH_LEVELS WHERE GRAPH_ID = :b1 AND AGLVL_ID = :b2 3 1 2608308 4.9M 375
SELECT * FROM SVT_DATA WHERE GRAPH_ID = :b1 AND AGLVL_ID = :b2 AND FLOOR_X = :b3 5 1 2608308 11.1M 122.3K
SELECT VARRAY_POS FROM SVT_MEASURES WHERE GRAPH_ID = :b1 AND NAME = :b2 3 1 2185058 5.4M 1.3K
UPDATE SVT_DATA SET Y=:b1,NR_AGGREGATED=NR_AGGREGATED + 1 WHERE GRAPH_ID = :b2 AND AGLVL_ID = :b3 AND
FLOOR_X = :b4
6 1 2065654 11.1M 2.1K
SELECT * from svt_dbmon where name = :b1 8 1 1929348 12.8M 22.4K

Top 7 SQL sorted by buffer gets (since startup)
Statement Buf/exec Dsk/exec Exectns BufGets DiskRds
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN
pandora_DBMON(60); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
5262 19 24151 121.1M 438.9K
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_articles.listarticles', simple_list__, complex_list__, true)) then rc__ := 2;
else null; null; dvp_articles.listarticles(SubCatIdA=>:SubCatIdA); if (wpg_docload.is_file_download)
then rc__ := 1; wpg_docload.get_download_file(:doc_info); null; null; null; commit; else
rc__ := 0; null; null; null; commit; owa.get_page(:data__,:ndata__); end if; end if;
:rc__ := rc__; end;
7524 291 11663 83.6M 3.2M
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_base.main', simple_list__, complex_list__, true)) then rc__ := 2; else
null; null; dvp_base.main; if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null;
null; null; commit; owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
5149 1855 14091 69.1M 24.9M
select t.schema, t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft,
system.aq$_queues q where aft.table_objno = t.objno and aft.owner_instance = :1 and q.table_objno
= t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name,
aft.table_objno skip locked
722 359 93566 64.3M 32.0M
select id, publi_datum, title from( SELECT distinct(id) id,
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') publi_datum, title
FROM (SELECT * FROM dvt_articles, dvt_cat_art where status = 'P'
and id = artikel_id and cat_sub_id = :b1
ORDER BY to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc)
WHERE ROWNUM < (16 + :b2) minus
SELECT distinct(id) id, to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') publi_datum,
title FROM (SELECT * FROM dvt_articles, dvt_cat_art
where status = 'P' and id = artikel_id and
cat_sub_id = :b1 ORDER BY
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc)
WHERE ROWNUM < (1 + :b2)) order by publi_datum desc
5210 182 12326 61.2M 2.1M
SELECT id, title, public_dag, public_maand from (select id, title, public_dag, public_maand from
dvt_articles where benelux_vlag = 'J' and status != 'H' and
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') <= trunc(sysdate) order by
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc, id desc) where rownum <= 5
4118 1702 14151 55.5M 22.9M
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_articles.zinews', simple_list__, complex_list__, true)) then rc__ := 2; else
null; null; dvp_articles.zinews(SectorIdA=>:SectorIdA,ToonA=>:ToonA); if
(wpg_docload.is_file_download) then rc__ := 1; wpg_docload.get_download_file(:doc_info); null;
null; null; commit; else rc__ := 0; null; null; null; commit;
owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
5498 64 9231 48.4M 574.2K

Top 7 SQL sorted by disk reads (since startup)
Statement Buf/exec Dsk/exec Exectns BufGets DiskRds
select t.schema, t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft,
system.aq$_queues q where aft.table_objno = t.objno and aft.owner_instance = :1 and q.table_objno
= t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name,
aft.table_objno skip locked
722 359 93566 64.3M 32.0M
select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no, local_order_no, enq_time,
enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info,
sender_name, sender_address, sender_protocol from MAILER.MLT_MAIL_QT where time_manager_info <= :1 and
state != :2 for update skip locked
475 362 92807 42.0M 31.9M
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_base.main', simple_list__, complex_list__, true)) then rc__ := 2; else
null; null; dvp_base.main; if (wpg_docload.is_file_download) then rc__ := 1;
wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0; null;
null; null; commit; owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__; end;
5149 1855 14091 69.1M 24.9M
SELECT id, title, public_dag, public_maand from (select id, title, public_dag, public_maand from
dvt_articles where benelux_vlag = 'J' and status != 'H' and
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') <= trunc(sysdate) order by
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc, id desc) where rownum <= 5
4118 1702 14151 55.5M 22.9M
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; begin
owa.init_cgi_env(:n__,:nm__,:v__); htp.HTBUF_LEN := 84; null; null; simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%'; simple_list__(3) := 'utl\_%'; simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%'; simple_list__(6) := 'htp.%'; simple_list__(7) := 'htf.%'; if
(owa_match.match_pattern('dvp_articles.listbeneluxnews', simple_list__, complex_list__, true)) then rc__ :=
2; else null; null; dvp_articles.listbeneluxnews; if (wpg_docload.is_file_download) then rc__ :=
1; wpg_docload.get_download_file(:doc_info); null; null; null; commit; else rc__ := 0;
null; null; null; commit; owa.get_page(:data__,:ndata__); end if; end if; :rc__ := rc__;
end;
8509 3802 4636 37.6M 16.8M
SELECT * from (select * from dvt_counter_articles where creatie_dt > (sysdate - 31) order by
aantal desc) where rownum < 11
129 90 123613 15.0M 10.5M
SELECT * from dvt_articles where benelux_vlag = 'J' and status != 'H' order by
to_date(public_dag||'-'||public_maand||'-'||public_jaar, 'dd-mm-yy') desc, id desc
4125 2055 5250 20.6M 10.2M


PGA Stats top

Workarea histogram
Low High Optimal OnePass MultiPass
8192 16383 5697236 0 0
16384 32767 99143 0 0
32768 65535 8024 0 0
65536 131071 13239 56 0
131072 262143 7352 9 0
262144 524287 54379 9 0
524288 1048575 29774 0 0
1048576 2097151 1658 243 0
2097152 4194303 0 10635 0
4194304 8388607 0 4 0
33554432 67108863 6 0 0
67108864 134217727 1 0 0
134217728 268435455 1 0 0

Parameter Value
pga_aggregate_target: 25165824


PGA Memory
Parameter sort_area_size: 524288
Parameter sort_area_retained_size: 0
Parameter hash_area_size: 1048576
Parameter pga_aggregate_target: 25165824
Parameter workarea_size_policy: AUTO

PGA Statistics
Name Value
aggregate PGA target parameter 25165824
aggregate PGA auto target 4194304
global memory bound 1257472
total PGA inuse 34054144
total PGA allocated 82551808
maximum PGA allocated 209080320
total freeable PGA memory 589824
PGA memory freed back to OS 327283507200
total PGA used for auto workareas 0
maximum PGA used for auto workareas 4047872
total PGA used for manual workareas 0
maximum PGA used for manual workareas 274432
over allocation count 477200
bytes processed 142427102208
extra bytes read/written 38538116096
cache hit percentage 78.7


(c) Geert De Paep, Uptime Technologies, Belgium - http://www.dba-village.com

[This report took 441 seconds]
[Usage: SQL*Plus: "exec plato.help" after "set serveroutput on format wrapped"]

SQL> spool off