| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance issue and statspack
I believe that the CPU speed does make a difference!
I ran the following code on Oracle Server running on Solaries,
alter session set events '10046 trace name context forever, level 12';
declare
l_start number default dbms_utility.get_time;
BEGIN
for i in 1..100000 loop
INSERT into t values ('xxxxxxxxxxxxxxxx');
end loop;
COMMIT;
dbms_output.put_line ( 'Elapsed ' || round(
(dbms_utility.get_time-l_start)/100, 2 ) );
END;
/
Elapsed 21.8
I ran the same code on Oracle running on Win2K, much faster CPU
Elapsed 7.48
And the TKPROF for insert statment on Solaris is:
INSERT INTO T
VALUES
( 'xxxxxxxxxxxxxxxx' )
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0
0
Execute 100000 12.42 12.80 0 303 103218
100000
Fetch 0 0.00 0.00 0 0 0
0
100000
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22 (recursive depth: 1)
For windows, the tkprof result is
INSERT into t
values
('xxxxxxxxxxxxxxxx')
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0
0
Execute 100000 4.26 3.83 0 407 104601
100000
Fetch 0 0.00 0.00 0 0 0
0
100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
HTH,
http://www.webyourcompany.com
kamal80_at_virgilio.it (Kamal) wrote in message news:<4e766a02.0306030253.5f0cb661_at_posting.google.com>...
> I have a strong suspect that something is wrong with the relation
> between Oracle and the operating system i'm using... There must be
> something wrong with the OS.
> Oracle 9iR2 on Tru64 5.1A.
>
> The database seems to be quite slow on writing data.
> I used this very stupid script to test the performance between
> installations on different machines, obviously it's not a common
> behavior of our applications against the db, bit i think it's useful
> for testing between different instances.
>
>
> set timing on
>
> begin
> for i in 1..100000 loop
> INSERT into t values ('xxxxxxxxxxxxxxxx');
> end loop;
> end;
> /
>
>
> On an installation with tha same db version and w2k it took about 13
> secs to run.
> On the Tru64 instance it took from 55 to 65 secs.
>
> In this moment the w2k instance has a bigger sized SGA, but the other
> instance gave us the same results before even with a similar sized
> SGA.
>
> Sorry for the big post, here are the statspack reports of the two
> instances for just the time of running that script.
> Don't look at the users tbs that looks terrible at a first glance,
> it's just for testing (i tried with different sized tbs and with a
> table with initial extent quite big etc etc: nothing changed).
> I'm impressed by the top five wait events: it seems that the big
> difference between the two reports is just the CPU time (used in
> what?), the alphaserver with Tru64 has a 600Mhz cpu, the w2k a 2800Mhz
> P4, but i don't think here it makes the difference... (I tried the
> script even on a notebook and it went faster than the alphaserver...
> imagine).
>
> /***************************************/
>
> _TO MAKE THE POINT_: Do you think could be useful to reinstall all the
> operating system and then Oracle on the machine? (I know it sounds
> horrible, but...). Could it be a problem of the installation? Or there
> is a magical parameter that i don't know specific for Tru64? (I tried
> any combination of disk_asynch_io and filesystemio_options: nothing
> new).
>
> /***************************************/
>
>
>
> The Tru64 report:
>
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster
> Host
> ------------ ----------- ------------ -------- ----------- -------
> ------------
> ALEF 454647123 alef 1 9.2.0.1.0 NO
> alpha
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- ---------
> -------------------
> Begin Snap: 3 21-May-03 13:46:34 8 6.5
> End Snap: 4 21-May-03 13:47:45 8 7.9
> Elapsed: 1.18 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 40M Std Block Size:
> 32K
> Shared Pool Size: 60M Log Buffer:
> 512K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 375,690.93
> 13,337,028.00
> Logical reads: 1,455.85
> 51,682.50
> Block changes: 2,832.31
> 100,547.00
> Physical reads: 0.87
> 31.00
> Physical writes: 9.87
> 350.50
> User calls: 0.04
> 1.50
> Parses: 5.34
> 189.50
> Hard parses: 0.41
> 14.50
> Sorts: 4.08
> 145.00
> Logons: 0.00
> 0.00
> Executes: 1,416.73
> 50,294.00
> Transactions: 0.03
>
> % Blocks changed per Read: 194.55 Recursive Call %: 100.00
> Rollback per transaction %: 0.00 Rows per Sort: 17.77
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 99.94 In-memory Sort %: 100.00
> Library Hit %: 99.61 Soft Parse %: 92.35
> Execute to Parse %: 99.62 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 77.36 % Non-Parse CPU: 99.32
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 33.62 35.60
> % SQL with executions>1: 55.95 71.20
> % Memory for SQL w/exec>1: 38.96 72.13
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 60
> 94.42
> log file parallel write 115 3
> 4.68
> control file parallel write 23 1
> .81
> log file sync 2 0
> .06
> LGWR wait for redo copy 12 0
> .03
> -------------------------------------------------------------
> Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
> -> s - second
> -> cs - centisecond - 100th of a second
> -> ms - millisecond - 1000th of a second
> -> us - microsecond - 1000000th of a second
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file parallel write 115 0 3 26
> 57.5
> control file parallel write 23 0 1 23
> 11.5
> log file sync 2 0 0 20
> 1.0
> LGWR wait for redo copy 12 0 0 2
> 6.0
> db file sequential read 60 0 0 0
> 30.0
> control file sequential read 54 0 0 0
> 27.0
> db file parallel write 45 0 0 0
> 22.5
> latch free 1 0 0 0
> 0.5
> SQL*Net message from client 2 0 189 94685
> 1.0
> SQL*Net message to client 2 0 0 0
> 1.0
> -------------------------------------------------------------
> Background Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file parallel write 115 0 3 26
> 57.5
> control file parallel write 23 0 1 23
> 11.5
> LGWR wait for redo copy 12 0 0 2
> 6.0
> log file sync 1 0 0 10
> 0.5
> db file parallel write 45 0 0 0
> 22.5
> control file sequential read 12 0 0 0
> 6.0
> db file sequential read 10 0 0 0
> 5.0
> latch free 1 0 0 0
> 0.5
> rdbms ipc message 179 63 207 1156
> 89.5
> SQL*Net message from client 1 0 118 ######
> 0.5
> SQL*Net message to client 1 0 0 0
> 0.5
> -------------------------------------------------------------
> SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Buffer Gets Threshold: 10000
> -> Note that resources reported for PL/SQL includes the resources used
> by
> all SQL statements called within the PL/SQL code. As individual
> SQL
> statements are also reported, it is possible and valid for the
> summed
> total % to exceed 100
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 101,890 1 101,890.0 98.6 58.63 62.15
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 101,828 100,000 1.0 98.5 50.43 52.60
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 156 38 4.1 0.2 0.00 0.01
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 36 13 2.8 0.0 0.00 0.00
> 2591785020
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
> spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
> ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
> nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
> or subname is null and :6 is null)
>
> 15 5 3.0 0.0 0.00 0.00
> 2385919346
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
> e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
> ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
> rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
> d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
>
> 12 4 3.0 0.0 0.00 0.00
> 189272129
> select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
> bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
>
> 12 4 3.0 0.0 0.00 0.00
> 4059714361
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> 8 1 8.0 0.0 0.00 0.00
> 1930240031
> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
> =:1
>
> -------------------------------------------------------------
> SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 10 1 10.0 16.1 58.63 62.15
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 9 100,000 0.0 14.5 50.43 52.60
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 1 5 0.2 1.6 0.00 0.00
> 2385919346
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
> e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
> ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
> rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
> d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
>
> 0 4 0.0 0.0 0.00 0.00
> 189272129
> select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
> bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
>
> 0 1 0.0 0.0 0.00 0.00
> 1930240031
> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
> =:1
>
> 0 38 0.0 0.0 0.00 0.01
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 0 13 0.0 0.0 0.00 0.00
> 2591785020
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
> spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
> ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
> nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
> or subname is null and :6 is null)
>
> 0 4 0.0 0.0 0.00 0.00
> 4059714361
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> -------------------------------------------------------------
> SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> 100,000 100,000 1.0 0.00 0.00
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 38 40 1.1 0.00 0.00
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 13 10 0.8 0.00 0.00
> 2591785020
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
> spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
> ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
> nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
> or subname is null and :6 is null)
>
> 5 24 4.8 0.00 0.00
> 2385919346
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
> e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
> ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
> rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
> d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
>
> 4 4 1.0 0.00 0.00
> 189272129
> select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
> bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
>
> 4 4 1.0 0.00 0.00
> 4059714361
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> 1 3 3.0 0.00 0.00
> 1930240031
> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
> =:1
>
> 1 1 1.0 58.63 62.15
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> -------------------------------------------------------------
> SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 4 4 1.06 4059714361
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> 3 5 0.79 2385919346
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
> e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
> ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
> rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
> d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
>
> 3 13 0.79 2591785020
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
> spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
> ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
> nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
> or subname is null and :6 is null)
>
> 2 4 0.53 189272129
> select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
> bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
>
> 2 38 0.53 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 1 1 0.26 1930240031
> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
> =:1
>
> 1 1 0.26 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 1 100,000 0.26 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> -------------------------------------------------------------
> Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> CPU used by this session 6,038 85.0
> 3,019.0
> CPU used when call started 174 2.5
> 87.0
> CR blocks created 18 0.3
> 9.0
> DBWR checkpoint buffers written 701 9.9
> 350.5
> DBWR transaction table writes 23 0.3
> 11.5
> DBWR undo block writes 405 5.7
> 202.5
> SQL*Net roundtrips to/from client 2 0.0
> 1.0
> active txn count during cleanout 80 1.1
> 40.0
> background timeouts 68 1.0
> 34.0
> buffer is not pinned count 947 13.3
> 473.5
> buffer is pinned count 729 10.3
> 364.5
> bytes received via SQL*Net from c 481 6.8
> 240.5
> bytes sent via SQL*Net to client 322 4.5
> 161.0
> calls to get snapshot scn: kcmgss 100,628 1,417.3
> 50,314.0
> calls to kcmgas 358 5.0
> 179.0
> calls to kcmgcs 74 1.0
> 37.0
> change write time 4,044 57.0
> 2,022.0
> cleanout - number of ktugct calls 80 1.1
> 40.0
> cluster key scan block gets 253 3.6
> 126.5
> cluster key scans 177 2.5
> 88.5
> commit cleanout failures: block l 4 0.1
> 2.0
> commit cleanouts 144 2.0
> 72.0
> commit cleanouts successfully com 140 2.0
> 70.0
> consistent changes 18 0.3
> 9.0
> consistent gets 1,834 25.8
> 917.0
> consistent gets - examination 912 12.9
> 456.0
> cursor authentications 9 0.1
> 4.5
> data blocks consistent reads - un 18 0.3
> 9.0
> db block changes 201,094 2,832.3
> 100,547.0
> db block gets 101,531 1,430.0
> 50,765.5
> deferred (CURRENT) block cleanout 28 0.4
> 14.0
> dirty buffers inspected 2 0.0
> 1.0
> enqueue conversions 27 0.4
> 13.5
> enqueue releases 202 2.9
> 101.0
> enqueue requests 202 2.9
> 101.0
> execute count 100,588 1,416.7
> 50,294.0
> free buffer inspected 2 0.0
> 1.0
> free buffer requested 604 8.5
> 302.0
> hot buffers moved to head of LRU 206 2.9
> 103.0
> immediate (CURRENT) block cleanou 3 0.0
> 1.5
> index fetch by key 281 4.0
> 140.5
> index scans kdiixs1 939 13.2
> 469.5
> logons cumulative 0 0.0
> 0.0
> messages received 161 2.3
> 80.5
> messages sent 161 2.3
> 80.5
> no buffer to keep pinned count 0 0.0
> 0.0
> no work - consistent read gets 574 8.1
> 287.0
> opened cursors cumulative 357 5.0
> 178.5
> parse count (hard) 29 0.4
> 14.5
> parse count (total) 379 5.3
> 189.5
> parse time cpu 41 0.6
> 20.5
> parse time elapsed 53 0.8
> 26.5
> physical reads 62 0.9
> 31.0
> physical reads direct 0 0.0
> 0.0
> physical writes 701 9.9
> 350.5
> physical writes direct 0 0.0
> 0.0
> Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> physical writes non checkpoint 573 8.1
> 286.5
> prefetched blocks 0 0.0
> 0.0
> process last non-idle time 0 0.0
> 0.0
> recursive calls 107,163 1,509.3
> 53,581.5
> recursive cpu usage 5,627 79.3
> 2,813.5
> redo blocks written 26,557 374.0
> 13,278.5
> redo entries 100,514 1,415.7
> 50,257.0
> redo ordering marks 298 4.2
> 149.0
> redo size 26,674,056 375,690.9
> ############
> redo synch time 4 0.1
> 2.0
> redo synch writes 2 0.0
> 1.0
> redo wastage 89,880 1,265.9
> 44,940.0
> redo write time 1 0.0
> 0.5
> redo writer latching time 2 0.0
> 1.0
> redo writes 115 1.6
> 57.5
> rollbacks only - consistent read 18 0.3
> 9.0
> rows fetched via callback 67 0.9
> 33.5
> session connect time 0 0.0
> 0.0
> session logical reads 103,365 1,455.9
> 51,682.5
> session pga memory 24,992 352.0
> 12,496.0
> session pga memory max 0 0.0
> 0.0
> session uga memory 65,408 921.2
> 32,704.0
> session uga memory max 0 0.0
> 0.0
> shared hash latch upgrades - no w 292 4.1
> 146.0
> sorts (memory) 290 4.1
> 145.0
> sorts (rows) 5,154 72.6
> 2,577.0
> switch current to new buffer 0 0.0
> 0.0
> table fetch by rowid 337 4.8
> 168.5
> table scan blocks gotten 1 0.0
> 0.5
> table scan rows gotten 1 0.0
> 0.5
> table scans (long tables) 0 0.0
> 0.0
> table scans (short tables) 2 0.0
> 1.0
> user calls 3 0.0
> 1.5
> user commits 2 0.0
> 1.0
> workarea executions - optimal 211 3.0
> 105.5
> write clones created in backgroun 1 0.0
> 0.5
> write clones created in foregroun 33 0.5
> 16.5
> -------------------------------------------------------------
> Session Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
> Session Id: 7 Serial#: 5
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file sync 1 0 0 30
> 0.5
> db file sequential read 65 0 0 0
> 32.5
> control file sequential read 42 0 0 0
> 21.0
> SQL*Net message from client 1 0 71 71380
> 0.5
> SQL*Net message to client 1 0 0 0
> 0.5
> -------------------------------------------------------------
> Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4
> Session Id: 7 Serial#: 5
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> active txn count during cleanout 80 1.1
> buffer is not pinned count 322 4.5
> buffer is pinned count 5 0.1
> bytes received via sql*net from c 214 3.0
> bytes sent via sql*net to client 161 2.3
> calls to get snapshot scn: kcmgss 100,174 1,410.9
> calls to kcmgas 356 5.0
> calls to kcmgcs 74 1.0
> change write time 4,044 57.0
> cleanout - number of ktugct calls 80 1.1
> cluster key scan block gets 114 1.6
> cluster key scans 49 0.7
> commit cleanout failures: block l 4 0.1
> commit cleanouts 88 1.2
> commit cleanouts successfully com 84 1.2
> consistent changes 18 0.3
> consistent gets 589 8.3
> consistent gets - examination 252 3.6
> cpu used by this session 5,864 82.6
> cr blocks created 18 0.3
> cursor authentications 8 0.1
> data blocks consistent reads - un 18 0.3
> db block changes 200,909 2,829.7
> db block gets 101,301 1,426.8
> deferred (current) block cleanout 15 0.2
> enqueue releases 104 1.5
> enqueue requests 104 1.5
> execute count 100,141 1,410.4
> free buffer requested 536 7.6
> hot buffers moved to head of lru 201 2.8
> index fetch by key 60 0.9
> index scans kdiixs1 82 1.2
> messages sent 91 1.3
> no work - consistent read gets 220 3.1
> opened cursors cumulative 82 1.2
> opened cursors current
> parse count (hard) 11 0.2
> parse count (total) 89 1.3
> parse time cpu 5 0.1
> parse time elapsed 9 0.1
> physical reads 10 0.1
> recursive calls 101,586 1,430.8
> recursive cpu usage 5,550 78.2
> redo entries 100,362 1,413.6
> redo ordering marks 298 4.2
> redo size 26,468,032 372,789.2
> redo synch time 1 0.0
> redo synch writes 1 0.0
> rollbacks only - consistent read 18 0.3
> rows fetched via callback 4 0.1
> session logical reads 101,890 1,435.1
> session pga memory 213,000 3,000.0
> session uga memory 65,408 921.2
> shared hash latch upgrades - no w 83 1.2
> sorts (memory) 17 0.2
> Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4
> Session Id: 7 Serial#: 5
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> sorts (rows) 13 0.2
> sql*net roundtrips to/from client 1 0.0
> table fetch by rowid 82 1.2
> user calls 1 0.0
> user commits 1 0.0
> workarea executions - optimal 8 0.1
> write clones created in foregroun 33 0.5
> -------------------------------------------------------------
> Tablespace IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->ordered by IOs (Reads + Writes) desc
>
> Tablespace
> ------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> UNDOTBS
> 3 0 0.0 1.0 452 6 0
> 0.0
> SYSTEM
> 11 0 0.0 1.0 160 2 0
> 0.0
> USERS
> 38 1 0.0 1.0 89 1 0
> 0.0
> -------------------------------------------------------------
> File IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->ordered by Tablespace, File
>
> Tablespace Filename
> ------------------------
> ----------------------------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> SYSTEM /oradata/alef/sys/system.dbf
> 11 0 0.0 1.0 160 2 0
>
> UNDOTBS /oradata/alef/sys/undotbs.dbf
> 3 0 0.0 1.0 452 6 0
>
> USERS /oradata/alef/users/users.dbf
> 38 1 0.0 1.0 89 1 0
>
> -------------------------------------------------------------
> Buffer Pool Statistics for DB: ALEF Instance: alef Snaps: 3 -4
> -> Standard block size Pools D: default, K: keep, R: recycle
> -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
>
> Free
> Write Buffer
> Number of Cache Buffer Physical Physical Buffer
> Complete Busy
> P Buffers Hit % Gets Reads Writes Waits
> Waits Waits
> --- ---------- ----- ----------- ----------- ---------- -------
> -------- ------
> D 630 99.9 103,973 55 701 0
> 0 0
> 8k 2,475 0 0 0 0
> 0 0
> -------------------------------------------------------------
>
> Instance Recovery Stats for DB: ALEF Instance: alef Snaps: 3 -4
> -> B: Begin snapshot, E: End snapshot
>
> Targt Estd Log File Log Ckpt
> Log Ckpt
> MTTR MTTR Recovery Actual Target Size Timeout
> Interval
> (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
> Redo Blks
> - ----- ----- ---------- ---------- ---------- ---------- ----------
> ----------
> B 0 7 87 2 1 7371
> 1
> E 0 7 52 90 1 7371
> 1
> -------------------------------------------------------------
>
> Buffer Pool Advisory for DB: ALEF Instance: alef End Snap: 4
> -> Only rows with estimated physical reads >0 are displayed
> -> ordered by Block Size, Buffers For Estimate
>
> Size for Size Buffers for Est Physical
> Estimated
> P Estimate (M) Factr Estimate Read Factor Physical
> Reads
> --- ------------ ----- ---------------- -------------
> ------------------
> D 4 .2 126 1.76
> 1,604
> D 8 .4 252 1.40
> 1,277
> D 12 .6 378 1.05
> 961
> D 16 .8 504 1.02
> 931
> D 20 1.0 630 1.00
> 911
> D 24 1.2 756 0.97
> 881
> D 28 1.4 882 0.97
> 881
> D 32 1.6 1,008 0.97
> 881
> D 36 1.8 1,134 0.97
> 881
> D 40 2.0 1,260 0.97
> 881
> D 44 2.2 1,386 0.97
> 881
> D 48 2.4 1,512 0.97
> 881
> D 52 2.6 1,638 0.97
> 881
> D 56 2.8 1,764 0.97
> 881
> D 60 3.0 1,890 0.97
> 881
> D 64 3.2 2,016 0.97
> 881
> D 68 3.4 2,142 0.97
> 881
> D 72 3.6 2,268 0.97
> 881
> D 76 3.8 2,394 0.97
> 881
> D 80 4.0 2,520 0.97
> 881
> -------------------------------------------------------------
> PGA Aggr Target Stats for DB: ALEF Instance: alef Snaps: 3 -4
> -> B: Begin snap E: End snap (rows dentified with B or E contain
> data
> which is absolute i.e. not diffed over the interval)
> -> PGA cache hit % - percentage of W/A (WorkArea) data processed only
> in-memory
> -> Auto PGA Target - actual workarea memory target
> -> W/A PGA Used - amount of memory used for all Workareas (manual +
> auto)
> -> %PGA W/A Mem - percentage of PGA memory allocated to workareas
> -> %Auto W/A Mem - percentage of workarea memory controlled by Auto
> Mem Mgmt
> -> %Man W/A Mem - percentage of workarea memory under manual
> control
>
> PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
> --------------- ---------------- -------------------------
> 100.0 5 0
>
> %PGA %Auto %Man
> PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
> Global Mem
> Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem
> Bound(K)
> - --------- --------- ---------- ---------- ------ ------ ------
> ----------
> B 14 7 13.9 0.0 .0 .0 .0
> 732
> E 14 7 14.0 0.0 .0 .0 .0
> 732
> -------------------------------------------------------------
>
> PGA Aggr Target Histogram for DB: ALEF Instance: alef Snaps: 3 -4
> -> Optimal Executions are purely in-memory operations
>
> Low High
> Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
> ------- ------- -------------- ------------- ------------ ------------
> 32K 64K 130 130 0 0
> 64K 128K 1 1 0 0
> 128K 256K 3 3 0 0
> -------------------------------------------------------------
>
> PGA Memory Advisory for DB: ALEF Instance: alef End Snap: 4
> -> When using Auto Memory Mgmt, minimally choose a
> pga_aggregate_target value
> where Estd PGA Overalloc Count is 0
>
> Estd Extra Estd PGA Estd
> PGA
> PGA Target Size W/A MB W/A MB Read/ Cache
> Overalloc
> Est (MB) Factr Processed Written to Disk Hit %
> Count
> ---------- ------- ---------------- ---------------- --------
> ----------
> 11 0.8 34.4 0.0 100.0
> 0
> 14 1.0 34.4 0.0 100.0
> 0
> 17 1.2 34.4 0.0 100.0
> 0
> 20 1.4 34.4 0.0 100.0
> 0
> 23 1.6 34.4 0.0 100.0
> 0
> 26 1.8 34.4 0.0 100.0
> 0
> 29 2.0 34.4 0.0 100.0
> 0
> 43 3.0 34.4 0.0 100.0
> 0
> 57 4.0 34.4 0.0 100.0
> 0
> 86 6.0 34.4 0.0 100.0
> 0
> 114 8.0 34.4 0.0 100.0
> 0
> -------------------------------------------------------------
> Rollback Segment Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->A high value for "Pct Waits" suggests more rollback segments may be
> required
> ->RBS stats may not be accurate between begin and end snaps when using
> Auto Undo
> managment, as RBS may be dynamically created and dropped as needed
>
> Trans Table Pct Undo Bytes
> RBS No Gets Waits Written Wraps Shrinks
> Extends
> ------ -------------- ------- --------------- -------- --------
> --------
> 0 1.0 0.00 0 0 0
> 0
> 3 1.0 0.00 0 0 0
> 0
> 4 1.0 0.00 0 0 0
> 0
> 5 1.0 0.00 0 0 0
> 0
> 6 2.0 0.00 0 0 0
> 0
> 7 1.0 0.00 0 0 0
> 0
> 8 2.0 0.00 0 0 0
> 0
> 9 4.0 0.00 49,432 1 0
> 0
> 10 1.0 0.00 0 0 0
> 0
> 11 473.0 0.00 7,405,076 15 0
> 12
> 12 3.0 0.00 2,476 0 0
> 0
> -------------------------------------------------------------
> Rollback Segment Storage for DB: ALEF Instance: alef Snaps: 3 -4
> ->Optimal Size should be larger than Avg Active
>
> RBS No Segment Size Avg Active Optimal Size Maximum Size
> ------ --------------- --------------- --------------- ---------------
> 0 753,664 0 753,664
> 3 360,448 0 360,448
> 4 27,230,208 0 27,230,208
> 5 557,056 0 557,056
> 6 13,598,720 0 13,598,720
> 7 14,647,296 0 14,647,296
> 8 28,475,392 0 28,475,392
> 9 360,448 6,553 360,448
> 10 425,984 0 425,984
> 11 39,813,120 7,975,047 39,813,120
> 12 26,181,632 0 26,181,632
> -------------------------------------------------------------
> Latch Activity for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics
> for
> willing-to-wait latch get requests
> ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get
> requests
> ->"Pct Misses" for both should be very close to 0.0
>
> Pct Avg Wait
> Pct
> Get Get Slps Time
> NoWait NoWait
> Latch Requests Miss /Miss (s)
> Requests Miss
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> Consistent RBA 115 0.0 0
> 0
> FOB s.o list latch 1 0.0 0
> 0
> SQL memory manager latch 1 0.0 0
> 23 0.0
> SQL memory manager worka 1,614 0.0 0
> 0
> active checkpoint queue 70 0.0 0
> 0
> cache buffers chains 509,543 0.0 0
> 606 0.0
> cache buffers lru chain 1,319 0.0 0
> 11 0.0
> channel operations paren 46 0.0 0
> 0
> checkpoint queue latch 4,023 0.0 0
> 702 0.0
> child cursor hash table 435 0.0 0
> 0
> dml lock allocation 125 0.0 0
> 0
> enqueue hash chains 417 0.0 0
> 0
> enqueues 279 0.0 0
> 0
> file number translation 18 0.0 0
> 0
> lgwr LWN SCN 115 0.0 0
> 0
> library cache 208,066 0.0 0
> 0
> library cache load lock 348 0.0 0
> 0
> library cache pin 203,276 0.0 0
> 0
> library cache pin alloca 1,684 0.0 0
> 0
> list of block allocation 9 0.0 0
> 0
> messages 566 0.0 0
> 0
> mostly latch-free SCN 115 0.0 0
> 0
> ncodef allocation latch 1 0.0 0
> 0
> object stats modificatio 44 0.0 0
> 0
> post/wait queue 230 0.0 0
> 2 0.0
> redo allocation 100,735 0.0 1.0 0
> 0
> redo copy 1 0.0 0
> 100,515 0.0
> redo writing 465 0.0 0
> 0
> row cache enqueue latch 795 0.0 0
> 0
> row cache objects 1,021 0.0 0
> 0
> sequence cache 3 0.0 0
> 0
> session allocation 1,157 0.0 0
> 0
> session idle bit 9 0.0 0
> 0
> session switching 1 0.0 0
> 0
> session timer 25 0.0 0
> 0
> shared pool 104,365 0.0 0
> 0
> simulator hash latch 9,819 0.0 0
> 0
> simulator lru latch 41 0.0 0
> 74 0.0
> sort extent pool 1 0.0 0
> 0
> transaction allocation 1 0.0 0
> 0
> transaction branch alloc 1 0.0 0
> 0
> undo global data 219 0.0 0
> 0
> -------------------------------------------------------------
> Latch Sleep breakdown for DB: ALEF Instance: alef Snaps: 3 -4
> -> ordered by misses desc
>
> Get
> Spin &
> Latch Name Requests Misses Sleeps
> Sleeps 1->4
> -------------------------- -------------- ----------- -----------
> ------------
> redo allocation 100,735 1 1
> 0/1/0/0/0
> -------------------------------------------------------------
> Latch Miss Sources for DB: ALEF Instance: alef Snaps: 3 -4
> -> only latches with sleeps are shown
> -> ordered by name, sleeps desc
>
> NoWait
> Waiter
> Latch Name Where Misses Sleeps
> Sleeps
> ------------------------ -------------------------- ------- ----------
> --------
> redo allocation kcrfwr 0 1
> 0
> -------------------------------------------------------------
> Dictionary Cache Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Pct Misses" should be very low (< 2% in most cases)
> ->"Cache Usage" is the number of cache entries being used
> ->"Pct SGA" is the ratio of usage to allocated size for that cache
>
> Get Pct Scan Pct Mod
> Final
> Cache Requests Miss Reqs Miss Reqs
> Usage
> ------------------------- ------------ ------ ------- ----- --------
> ----------
> dc_free_extents 12 50.0 6 0.0 6
> 0
> dc_object_ids 96 24.0 0 0
> 228
> dc_objects 203 47.3 0 0
> 392
> dc_segments 93 4.3 0 6
> 158
> dc_tablespace_quotas 6 16.7 0 6
> 1
> dc_tablespaces 54 0.0 0 0
> 25
> dc_used_extents 6 100.0 0 6
> 6
> dc_user_grants 12 8.3 0 0
> 19
> dc_usernames 48 0.0 0 0
> 4
> dc_users 56 0.0 0 0
> 21
> -------------------------------------------------------------
>
>
> Library Cache Activity for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Pct Misses" should be very low
>
> Get Pct Pin Pct
> Invali-
> Namespace Requests Miss Requests Miss Reloads
> dations
> --------------- ------------ ------ -------------- ------ ----------
> --------
> CLUSTER 16 0.0 24 0.0 0
> 0
> SQL AREA 516 2.1 102,349 0.1 0
> 0
> TABLE/PROCEDURE 401 28.4 528 62.1 0
> 0
> -------------------------------------------------------------
> Shared Pool Advisory for DB: ALEF Instance: alef End Snap: 4
> -> Note there is often a 1:Many correlation between a single logical
> object
> in the Library Cache, and the physical number of memory objects
> associated
> with it. Therefore comparing the number of Lib Cache objects (e.g.
> in
> v$librarycache), with the number of Lib Cache Memory Objects is
> invalid
>
> Estd
> Shared Pool SP Estd Estd Estd Lib LC Time
> Size for Size Lib Cache Lib Cache Cache Time Saved Estd
> Lib Cache
> Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
> Obj Hits
> ----------- ----- ---------- ------------ ------------ -------
> ---------------
> 36 .6 6 1,391 12 1.0
> 2,669
> 44 .7 6 1,391 12 1.0
> 2,669
> 52 .9 6 1,391 12 1.0
> 2,669
> 60 1.0 6 1,391 12 1.0
> 2,669
> 68 1.1 6 1,391 12 1.0
> 2,669
> 76 1.3 6 1,391 12 1.0
> 2,669
> 84 1.4 6 1,391 12 1.0
> 2,669
> 92 1.5 6 1,391 12 1.0
> 2,669
> 100 1.7 6 1,391 12 1.0
> 2,669
> 108 1.8 6 1,391 12 1.0
> 2,669
> 116 1.9 6 1,391 12 1.0
> 2,669
> 124 2.1 6 1,391 12 1.0
> 2,669
> -------------------------------------------------------------
> SGA Memory Summary for DB: ALEF Instance: alef Snaps: 3 -4
>
> SGA regions Size in Bytes
> ------------------------------ ----------------
> Database Buffers 41,943,040
> Fixed Size 732,904
> Redo Buffers 811,008
> Variable Size 100,663,296
> ----------------
> sum 144,150,248
> -------------------------------------------------------------
>
>
> SGA breakdown difference for DB: ALEF Instance: alef Snaps: 3 -4
>
> Pool Name Begin value End
> value % Diff
> ------ ------------------------------ ----------------
> ---------------- -------
> java free memory 12,582,912
> 12,582,912 0.00
> large free memory 4,194,304
> 4,194,304 0.00
> shared 1M buffer 2,098,176
> 2,098,176 0.00
> shared Checkpoint queue 513,280
> 513,280 0.00
> shared FileIdentificatonBlock 349,824
> 349,824 0.00
> shared FileOpenBlock 1,191,104
> 1,191,104 0.00
> shared KGK heap 7,000
> 7,000 0.00
> shared KGLS heap 1,094,672
> 1,252,320 14.40
> shared KQR L PO 531,480
> 634,904 19.46
> shared KQR M PO 132,640
> 138,784 4.63
> shared KQR S SO 4,608
> 4,864 5.56
> shared KSXR pending messages que 853,952
> 853,952 0.00
> shared KSXR receive buffers 1,034,000
> 1,034,000 0.00
> shared PL/SQL DIANA 625,360
> 638,696 2.13
> shared PL/SQL MPCODE 441,576
> 450,864 2.10
> shared PLS non-lib hp 2,088
> 2,088 0.00
> shared character set object 323,752
> 323,752 0.00
> shared dictionary cache 3,221,760
> 3,221,760 0.00
> shared enqueue 309,528
> 309,528 0.00
> shared errors 22,960
> 22,960 0.00
> shared event statistics per sess 1,728,560
> 1,728,560 0.00
> shared fixed allocation callback 408
> 408 0.00
> shared free memory 55,685,760
> 54,026,720 -2.98
> shared joxs heap init 4,240
> 4,240 0.00
> shared kgl simulator 1,038,616
> 1,038,616 0.00
> shared krvxrr 253,056
> 253,056 0.00
> shared library cache 3,986,512
> 4,381,328 9.90
> shared message pool freequeue 940,944
> 940,944 0.00
> shared miscellaneous 4,672,080
> 4,693,040 0.45
> shared parameters 9,576
> 10,640 11.11
> shared pl/sql source 4,968
> 4,968 0.00
> shared sessions 462,400
> 462,400 0.00
> shared sim memory hea 46,880
> 46,880 0.00
> shared sql area 1,909,584
> 2,859,336 49.74
> shared table definiti 1,792
> 4,144 131.25
> shared transaction 330,704
> 330,704 0.00
> shared trigger defini 1,112
> 1,112 0.00
> shared trigger inform 1,320
> 1,320 0.00
> shared trigger source 1,144
> 1,144 0.00
> shared type object de 48,664
> 48,664 0.00
> buffer_cache 41,943,040
> 41,943,040 0.00
> fixed_sga 732,904
> 732,904 0.00
> log_buffer 788,480
> 788,480 0.00
> -------------------------------------------------------------
> init.ora Parameters for DB: ALEF Instance: alef Snaps: 3 -4
>
> End
> value
> Parameter Name Begin value (if
> different)
> ----------------------------- ---------------------------------
> --------------
> background_dump_dest /oracle/admin/alef/bdump
> compatible 9.2.0.0.0
> control_files /oradata/alef/sys/control01.ctl,
> core_dump_dest /oracle/admin/alef/cdump
> db_8k_cache_size 20971520
> db_block_size 32768
> db_cache_size 20971520
> db_domain
> db_file_multiblock_read_count 32
> db_name alef
> filesystemio_options none
> hash_join_enabled TRUE
> instance_name alef
> java_pool_size 12582912
> large_pool_size 4194304
> log_checkpoint_interval 1
> log_checkpoint_timeout 0
> open_cursors 300
> pga_aggregate_target 15000000
> processes 150
> query_rewrite_enabled FALSE
> shared_pool_size 62914560
> sort_area_retained_size 1000000
> sort_area_size 1000000
> timed_statistics TRUE
> undo_management AUTO
> undo_retention 3600
> undo_tablespace UNDOTBS
> user_dump_dest /oracle/admin/alef/udump
> utl_file_dir /usr/users/oracle/utl_file
> -------------------------------------------------------------
>
> End of Report
>
>
>
>
>
>
>
>
>
>
>
> The w2k report:
>
>
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster
> Host
> ------------ ----------- ------------ -------- ----------- -------
> ------------
> ALEF 470618733 alef 1 9.2.0.1.0 NO
> JACK
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- ---------
> -------------------
> Begin Snap: 3 21-May-03 19:10:22 9 6.0
> End Snap: 4 21-May-03 19:10:43 9 8.7
> Elapsed: 0.35 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 240M Std Block Size:
> 16K
> Shared Pool Size: 240M Log Buffer:
> 4,883K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 1,162,755.05
> 12,208,928.00
> Logical reads: 6,533.71
> 68,604.00
> Block changes: 9,612.38
> 100,930.00
> Physical reads: 32.57
> 342.00
> Physical writes: 32.57
> 342.00
> User calls: 0.43
> 4.50
> Parses: 7.90
> 83.00
> Hard parses: 0.38
> 4.00
> Sorts: 3.57
> 37.50
> Logons: 0.00
> 0.00
> Executes: 4,777.48
> 50,163.50
> Transactions: 0.10
>
> % Blocks changed per Read: 147.12 Recursive Call %: 99.99
> Rollback per transaction %: 0.00 Rows per Sort: 317.07
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 100.00 In-memory Sort %: 97.33
> Library Hit %: 99.98 Soft Parse %: 95.18
> Execute to Parse %: 99.83 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 75.00 % Non-Parse CPU: 99.54
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 93.78 93.78
> % SQL with executions>1: 36.98 37.03
> % Memory for SQL w/exec>1: 49.29 49.33
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 13
> 87.83
> log file parallel write 26 1
> 4.12
> direct path read 96 1
> 3.67
> control file sequential read 55 0
> 2.19
> log file switch completion 2 0
> 1.49
> -------------------------------------------------------------
> Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
> -> s - second
> -> cs - centisecond - 100th of a second
> -> ms - millisecond - 1000th of a second
> -> us - microsecond - 1000000th of a second
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file parallel write 26 25 1 23
> 13.0
> direct path read 96 0 1 6
> 48.0
> control file sequential read 55 0 0 6
> 27.5
> log file switch completion 2 0 0 110
> 1.0
> control file parallel write 14 0 0 3
> 7.0
> log file sync 2 0 0 16
> 1.0
> log file sequential read 2 0 0 6
> 1.0
> direct path write 8 0 0 1
> 4.0
> log file single write 2 0 0 0
> 1.0
> SQL*Net more data to client 11 0 0 0
> 5.5
> LGWR wait for redo copy 6 0 0 0
> 3.0
> SQL*Net message from client 6 0 232 38633
> 3.0
> SQL*Net more data from clien 4 0 0 0
> 2.0
> SQL*Net message to client 6 0 0 0
> 3.0
> -------------------------------------------------------------
> Background Wait Events for DB: ALEF Instance: alef Snaps: 3 -4
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file parallel write 26 25 1 23
> 13.0
> control file sequential read 13 0 0 13
> 6.5
> control file parallel write 14 0 0 3
> 7.0
> log file sequential read 2 0 0 6
> 1.0
> log file single write 2 0 0 0
> 1.0
> LGWR wait for redo copy 6 0 0 0
> 3.0
> rdbms ipc message 79 51 61 767
> 39.5
> -------------------------------------------------------------
> SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Buffer Gets Threshold: 10000
> -> Note that resources reported for PL/SQL includes the resources used
> by
> all SQL statements called within the PL/SQL code. As individual
> SQL
> statements are also reported, it is possible and valid for the
> summed
> total % to exceed 100
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 102,128 1 102,128.0 74.4 11.98 12.39
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 102,127 100,000 1.0 74.4 10.92 10.88
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 34,959 1 34,959.0 25.5 0.94 3.85
> 2118091657
> Module: SQL*Plus
> begin statspack.snap (i_snap_level => 7, i_session_id => 10); en
> d;
>
> 30,150 1 30,150.0 22.0 0.22 0.22
> 4059808258
> Module: SQL*Plus
> INSERT into stats$sqltext ( hash_value
> , text_subset , piece
> , sql_text , address , comman
> d_type , last_snap_id )
> select st1.hash_value , ss.text_subset
>
> 229 3 76.3 0.2 0.58 2.11
> 1017492279
> Module: SQL*Plus
> INSERT into stats$sql_summary ( snap_id
> , dbid , instance_number , text_subset
> , sharable_mem , sorts , modu
> le , loaded_versions , fetches
> , executions , loads , invalidations
>
> 130 3 43.3 0.1 0.02 0.01
> 1354174952
> SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT
> N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M
> KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_
> RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN
> " = :6 AND "MESI" = :7 AND "LABEL" = :8 OR "ID" = :9 AND "CDC_RA
>
> 109 109 1.0 0.1 0.00 0.00
> 787810128
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
> estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
> density, col#, spare1, spare2, avgcln from hist_head$ where obj#
> =:1 and intcol#=:2
>
> 76 19 4.0 0.1 0.00 0.00
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 70 10 7.0 0.1 0.00 0.00
> 2249281901
> update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
> =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
> , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
> int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
> SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Buffer Gets Threshold: 10000
> -> Note that resources reported for PL/SQL includes the resources used
> by
> all SQL statements called within the PL/SQL code. As individual
> SQL
> statements are also reported, it is possible and valid for the
> summed
> total % to exceed 100
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> and file#=:2 and block#=:3
>
> 60 6 10.0 0.0 0.02 0.01
> 448729003
> declare p varchar2(32767); begin p := LBAC_R
> LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
> p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
> substr(p,8001,4000); :v4 := substr(p,12001,4000);
> :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
>
> 60 20 3.0 0.0 0.00 0.00
> 1705880752
> select file# from file$ where ts#=:1
>
> 60 10 6.0 0.0 0.00 0.00
> 1877781575
> delete from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 50 10 5.0 0.0 0.00 0.00
> 3936714171
> update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
> 2=:7,priv3=:8 where ts#=:1 and user#=:2
>
> 30 10 3.0 0.0 0.00 0.00
> 904892542
> select file#,block#,length from fet$ where length>=:1 and
> ts#=:2 and file#=:3
>
> 30 5 6.0 0.0 0.00 0.00
> 1536916657
> select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
> vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
> def$ where obj#=:1
>
> 30 10 3.0 0.0 0.00 0.00
> 1839874543
> select file#,block#,length from uet$ where ts#=:1 and segfile#=:
> 2 and segblock#=:3 and ext#=:4
>
> 30 10 3.0 0.0 0.00 0.00
> 3687396716
> insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
> h)values (:1, :2, :3, :4, :5, :6, :7)
>
> 30 6 5.0 0.0 0.00 0.00
> 3816270963
> SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
> AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
> AND POLT.OWNER = :b1 AND POLT.TBL_NAME = :b2 AND BITAND(POLT
> .FLAGS,1) = 1 AND BITAND(POLT.OPTIONS,:b3) = :b3 AND POL.POL#
> = POLT.POL#
>
> 20 10 2.0 0.0 0.00 0.00
> 2913840444
> select length from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 20 10 2.0 0.0 0.00 0.00
> 3230982141
> insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
>
> 18 6 3.0 0.0 0.00 0.00
> 3696784358
> SQL ordered by Gets for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Buffer Gets Threshold: 10000
> -> Note that resources reported for PL/SQL includes the resources used
> by
> all SQL statements called within the PL/SQL code. As individual
> SQL
> statements are also reported, it is possible and valid for the
> summed
> total % to exceed 100
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 O
> RDER BY PACKAGE
>
> 14 7 2.0 0.0 0.00 0.00
> 1966425544
> select text from view$ where rowid=:1
>
> 12 1 12.0 0.0 0.20 0.70
> 2734029811
> Module: SQL*Plus
> INSERT into stats$sql_statistics ( snap_id
> , dbid , instance_number , total_
> sql , total_sql_mem , single_use_sql
> , single_use_sql_mem ) select :
> b3 , :b2 , :b1 , count(1)
>
> 6 6 1.0 0.0 0.00 0.00
> 667543881
> SELECT A.pol#, nlabel, CASE WHEN A.pol# IN (0) THEN DECODE(LBAC$
> SA.enforce_write(pol_name,ilabel),1,21,2,42,0) END FROM lbac$lab
>
> -------------------------------------------------------------
> SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 1,014 3 338.0 148.2 0.58 2.11
> 1017492279
> Module: SQL*Plus
> INSERT into stats$sql_summary ( snap_id
> , dbid , instance_number , text_subset
> , sharable_mem , sorts , modu
> le , loaded_versions , fetches
> , executions , loads , invalidations
>
> 684 1 684.0 100.0 0.94 3.85
> 2118091657
> Module: SQL*Plus
> begin statspack.snap (i_snap_level => 7, i_session_id => 10); en
> d;
>
> 343 1 343.0 50.1 0.20 0.70
> 2734029811
> Module: SQL*Plus
> INSERT into stats$sql_statistics ( snap_id
> , dbid , instance_number , total_
> sql , total_sql_mem , single_use_sql
> , single_use_sql_mem ) select :
> b3 , :b2 , :b1 , count(1)
>
> 0 5 0.0 0.0 0.00 0.00
> 114078687
> select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
> j#=:1
>
> 0 6 0.0 0.0 0.02 0.01
> 448729003
> declare p varchar2(32767); begin p := LBAC_R
> LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
> p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
> substr(p,8001,4000); :v4 := substr(p,12001,4000);
> :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
>
> 0 6 0.0 0.0 0.00 0.00
> 667543881
> SELECT A.pol#, nlabel, CASE WHEN A.pol# IN (0) THEN DECODE(LBAC$
> SA.enforce_write(pol_name,ilabel),1,21,2,42,0) END FROM lbac$lab
> A, lbac$pol B WHERE B.pol# = A.pol# AND bitand(A.flags,1)=1 AND
> bitand(B.flags,1)=1 AND tag# > sys_context('LBAC$LABELS','LBAC$
> LASTSEQ') AND (CASE WHEN A.pol# IN (0) AND sys_context('LBAC$'|
>
> 0 109 0.0 0.0 0.00 0.00
> 787810128
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
> estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
> density, col#, spare1, spare2, avgcln from hist_head$ where obj#
> =:1 and intcol#=:2
>
> 0 10 0.0 0.0 0.00 0.00
> 904892542
> select file#,block#,length from fet$ where length>=:1 and
> ts#=:2 and file#=:3
>
> 0 3 0.0 0.0 0.02 0.01
> 1354174952
> SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT
> N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M
> KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_
> RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN
> SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> " = :6 AND "MESI" = :7 AND "LABEL" = :8 OR "ID" = :9 AND "CDC_RA
>
> 0 5 0.0 0.0 0.00 0.00
> 1536916657
> select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
> vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
> def$ where obj#=:1
>
> 0 20 0.0 0.0 0.00 0.00
> 1705880752
> select file# from file$ where ts#=:1
>
> 0 10 0.0 0.0 0.00 0.00
> 1839874543
> select file#,block#,length from uet$ where ts#=:1 and segfile#=:
> 2 and segblock#=:3 and ext#=:4
>
> 0 10 0.0 0.0 0.00 0.00
> 1877781575
> delete from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 0 7 0.0 0.0 0.00 0.00
> 1966425544
> select text from view$ where rowid=:1
>
> 0 19 0.0 0.0 0.00 0.00
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 0 10 0.0 0.0 0.00 0.00
> 2249281901
> update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
> =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
> , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
> int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
> and file#=:2 and block#=:3
>
> 0 1 0.0 0.0 11.98 12.39
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 0 6 0.0 0.0 0.00 0.00
> 2721705697
> SELECT MAX(TAG#) FROM LBAC$LAB
>
> 0 10 0.0 0.0 0.00 0.00
> 2913840444
> select length from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 0 10 0.0 0.0 0.00 0.00
> 3230982141
> insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
>
> 0 10 0.0 0.0 0.00 0.00
> 3687396716
> insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
> h)values (:1, :2, :3, :4, :5, :6, :7)
>
> 0 6 0.0 0.0 0.00 0.00
> 3696784358
> SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 O
> RDER BY PACKAGE
>
> 0 6 0.0 0.0 0.00 0.00
> 3816270963
> SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
> AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
> SQL ordered by Reads for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> AND POLT.OWNER = :b1 AND POLT.TBL_NAME = :b2 AND BITAND(POLT
> .FLAGS,1) = 1 AND BITAND(POLT.OPTIONS,:b3) = :b3 AND POL.POL#
> = POLT.POL#
>
> 0 100,000 0.0 0.0 10.92 10.88
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 0 10 0.0 0.0 0.00 0.00
> 3936714171
> update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
>
> -------------------------------------------------------------
> SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> 100,000 100,000 1.0 0.00 0.00
> 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 109 0 0.0 0.00 0.00
> 787810128
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
> estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
> density, col#, spare1, spare2, avgcln from hist_head$ where obj#
> =:1 and intcol#=:2
>
> 20 20 1.0 0.00 0.00
> 1705880752
> select file# from file$ where ts#=:1
>
> 19 19 1.0 0.00 0.00
> 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 10 10 1.0 0.00 0.00
> 904892542
> select file#,block#,length from fet$ where length>=:1 and
> ts#=:2 and file#=:3
>
> 10 0 0.0 0.00 0.00
> 1839874543
> select file#,block#,length from uet$ where ts#=:1 and segfile#=:
> 2 and segblock#=:3 and ext#=:4
>
> 10 10 1.0 0.00 0.00
> 1877781575
> delete from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 10 10 1.0 0.00 0.00
> 2249281901
> update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
> =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
> , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
> int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
> and file#=:2 and block#=:3
>
> 10 0 0.0 0.00 0.00
> 2913840444
> select length from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 10 10 1.0 0.00 0.00
> 3230982141
> insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
>
> 10 10 1.0 0.00 0.00
> 3687396716
> insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
> h)values (:1, :2, :3, :4, :5, :6, :7)
>
> 10 10 1.0 0.00 0.00
> 3936714171
> update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
> 2=:7,priv3=:8 where ts#=:1 and user#=:2
>
> 7 7 1.0 0.00 0.00
> 1966425544
> select text from view$ where rowid=:1
>
> 6 6 1.0 0.00 0.00
> 448729003
> declare p varchar2(32767); begin p := LBAC_R
> LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
> SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
> substr(p,8001,4000); :v4 := substr(p,12001,4000);
> :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
>
> 6 0 0.0 0.00 0.00
> 667543881
> SELECT A.pol#, nlabel, CASE WHEN A.pol# IN (0) THEN DECODE(LBAC$
> SA.enforce_write(pol_name,ilabel),1,21,2,42,0) END FROM lbac$lab
> A, lbac$pol B WHERE B.pol# = A.pol# AND bitand(A.flags,1)=1 AND
> bitand(B.flags,1)=1 AND tag# > sys_context('LBAC$LABELS','LBAC$
> LASTSEQ') AND (CASE WHEN A.pol# IN (0) AND sys_context('LBAC$'|
>
> 6 6 1.0 0.00 0.00
> 2721705697
> SELECT MAX(TAG#) FROM LBAC$LAB
>
> 6 6 1.0 0.00 0.00
> 3696784358
> SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 O
> RDER BY PACKAGE
>
> 6 6 1.0 0.00 0.00
> 3816270963
> SELECT POL.POL#,POL.COLUMN_NAME,POLT.PREDICATE FROM LBACSYS.LB
> AC$POL POL,LBACSYS.LBAC$POLT POLT WHERE BITAND(POL.FLAGS,1) = 1
> AND POLT.OWNER = :b1 AND POLT.TBL_NAME = :b2 AND BITAND(POLT
> .FLAGS,1) = 1 AND BITAND(POLT.OPTIONS,:b3) = :b3 AND POL.POL#
> = POLT.POL#
>
> 5 0 0.0 0.00 0.00
> 114078687
> select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
> j#=:1
>
> 5 19 3.8 0.00 0.00
> 1536916657
> select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
> vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
> def$ where obj#=:1
>
> 3 748 249.3 0.19 0.70
> 1017492279
> Module: SQL*Plus
> INSERT into stats$sql_summary ( snap_id
> , dbid , instance_number , text_subset
> , sharable_mem , sorts , modu
> le , loaded_versions , fetches
> , executions , loads , invalidations
>
> 3 30 10.0 0.01 0.00
> 1354174952
> SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT
> N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M
> KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_
> RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN
> " = :6 AND "MESI" = :7 AND "LABEL" = :8 OR "ID" = :9 AND "CDC_RA
>
> 1 1 1.0 0.94 3.85
> 2118091657
> Module: SQL*Plus
> begin statspack.snap (i_snap_level => 7, i_session_id => 10); en
> d;
>
> SQL ordered by Executions for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> 1 1 1.0 11.98 12.39
> 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 1 1 1.0 0.20 0.70
> 2734029811
> Module: SQL*Plus
> INSERT into stats$sql_statistics ( snap_id
> , dbid , instance_number , total_
> sql , total_sql_mem , single_use_sql
> , single_use_sql_mem ) select :
>
> -------------------------------------------------------------
> SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 20 20 12.05 1705880752
> select file# from file$ where ts#=:1
>
> 10 10 6.02 904892542
> select file#,block#,length from fet$ where length>=:1 and
> ts#=:2 and file#=:3
>
> 10 10 6.02 1839874543
> select file#,block#,length from uet$ where ts#=:1 and segfile#=:
> 2 and segblock#=:3 and ext#=:4
>
> 10 10 6.02 1877781575
> delete from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 10 10 6.02 2249281901
> update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
> =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
> , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
> int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
> and file#=:2 and block#=:3
>
> 10 10 6.02 2913840444
> select length from fet$ where file#=:1 and block#=:2 and ts#=:3
>
> 10 10 6.02 3230982141
> insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
>
> 10 10 6.02 3687396716
> insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
> h)values (:1, :2, :3, :4, :5, :6, :7)
>
> 10 10 6.02 3936714171
> update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv
> 2=:7,priv3=:8 where ts#=:1 and user#=:2
>
> 7 7 4.22 1966425544
> select text from view$ where rowid=:1
>
> 6 6 3.61 448729003
> declare p varchar2(32767); begin p := LBAC_R
> LS.READCHECK_FILTER(:sn, :on); :v1 := substr(
> p,1,4000); :v2 := substr(p,4001,4000); :v3 :=
> substr(p,8001,4000); :v4 := substr(p,12001,4000);
> :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000);
>
> 6 6 3.61 667543881
> SELECT A.pol#, nlabel, CASE WHEN A.pol# IN (0) THEN DECODE(LBAC$
> SA.enforce_write(pol_name,ilabel),1,21,2,42,0) END FROM lbac$lab
> A, lbac$pol B WHERE B.pol# = A.pol# AND bitand(A.flags,1)=1 AND
> bitand(B.flags,1)=1 AND tag# > sys_context('LBAC$LABELS','LBAC$
> LASTSEQ') AND (CASE WHEN A.pol# IN (0) AND sys_context('LBAC$'|
>
> 4 5 2.41 114078687
> select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
> SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> j#=:1
>
> 4 109 2.41 787810128
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
> estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
> density, col#, spare1, spare2, avgcln from hist_head$ where obj#
> =:1 and intcol#=:2
>
> 4 5 2.41 1536916657
> select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
> vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
> def$ where obj#=:1
>
> 2 3 1.20 1017492279
> Module: SQL*Plus
> INSERT into stats$sql_summary ( snap_id
> , dbid , instance_number , text_subset
> , sharable_mem , sorts , modu
> le , loaded_versions , fetches
> , executions , loads , invalidations
>
> 2 1 1.20 2118091657
> Module: SQL*Plus
> begin statspack.snap (i_snap_level => 7, i_session_id => 10); en
> d;
>
> 1 3 0.60 1354174952
> SELECT "ID","CDC_RAT_DTA","TPO_ID_RTN","TPO_MCR_SET","CDC_SCT_RT
> N","DEF_RAT","DVSDEFRAT","SCN","MESI","LABEL" FROM "DATALEF"."M
> KT_DEF_RAT" WHERE "ID" = :1 AND "CDC_RAT_DTA" = :2 AND "TPO_ID_
> RTN" = :3 AND "TPO_MCR_SET" = :4 AND "CDC_SCT_RTN" = :5 AND "SCN
> " = :6 AND "MESI" = :7 AND "LABEL" = :8 OR "ID" = :9 AND "CDC_RA
>
> 1 19 0.60 2085632044
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 1 1 0.60 2617268171
> Module: SQL*Plus
> BEGIN test; END;
>
> 1 100,000 0.60 3860503990
> Module: SQL*Plus
> INSERT into t values ('xxxxxxxxxxxxxxxx')
>
> 1 1 0.60 4059808258
> Module: SQL*Plus
> INSERT into stats$sqltext ( hash_value
> , text_subset , piece
> , sql_text , address , comman
> d_type , last_snap_id )
> select st1.hash_value , ss.text_subset
>
> 0 0 0.00 11393400
> SELECT "DATALEF"."DATIAZ"."DATA_RILEV" ,"SUPPORTO"."COD_DIV"."CD
> SQL ordered by Parse Calls for DB: ALEF Instance: alef Snaps: 3 -4
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> C_DVS_ALF" FROM "DATALEF"."DATIAZ","DATALEF"."COD_IDN","SUPPORT
> O"."CDC_TPL_DTG","SUPPORTO"."COD_DIV","DATALEF"."COD_PTF" WHERE
> (((((("DATALEF"."DATIAZ"."DATA_RILEV" = TO_DATE ('2003-04-14','Y
> YYY-MM-DD') ) AND ("DATALEF"."DATIAZ"."COD_ALMAX" = "DATALEF"."C
>
> 0 0 0.00 29771263
> insert into argument$( obj#,procedure$,procedure#,overload#,posi
> tion#,sequence#,level#,argument,type#,default#,in_out,length,pre
>
> -------------------------------------------------------------
> Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> CPU used by this session 1,295 61.7
> 647.5
> CPU used when call started 97 4.6
> 48.5
> CR blocks created 30 1.4
> 15.0
> DBWR checkpoint buffers written 0 0.0
> 0.0
> DBWR checkpoints 1 0.1
> 0.5
> DBWR transaction table writes 0 0.0
> 0.0
> DBWR undo block writes 0 0.0
> 0.0
> SQL*Net roundtrips to/from client 6 0.3
> 3.0
> active txn count during cleanout 183 8.7
> 91.5
> background checkpoints started 1 0.1
> 0.5
> background timeouts 20 1.0
> 10.0
> branch node splits 0 0.0
> 0.0
> buffer is not pinned count 4,208 200.4
> 2,104.0
> buffer is pinned count 34,882 1,661.1
> 17,441.0
> bytes received via SQL*Net from c 10,818 515.1
> 5,409.0
> bytes sent via SQL*Net to client 25,777 1,227.5
> 12,888.5
> calls to get snapshot scn: kcmgss 100,407 4,781.3
> 50,203.5
> calls to kcmgas 345 16.4
> 172.5
> calls to kcmgcs 165 7.9
> 82.5
> change write time 905 43.1
> 452.5
> cleanout - number of ktugct calls 184 8.8
> 92.0
> cleanouts and rollbacks - consist 0 0.0
> 0.0
> cleanouts only - consistent read 0 0.0
> 0.0
> cluster key scan block gets 1,357 64.6
> 678.5
> cluster key scans 1,080 51.4
> 540.0
> commit cleanout failures: buffer 0 0.0
> 0.0
> commit cleanout failures: callbac 2 0.1
> 1.0
> commit cleanouts 280 13.3
> 140.0
> commit cleanouts successfully com 278 13.2
> 139.0
> commit txn count during cleanout 9 0.4
> 4.5
> consistent changes 30 1.4
> 15.0
> consistent gets 35,055 1,669.3
> 17,527.5
> consistent gets - examination 2,931 139.6
> 1,465.5
> cursor authentications 15 0.7
> 7.5
> data blocks consistent reads - un 30 1.4
> 15.0
> db block changes 201,860 9,612.4
> 100,930.0
> db block gets 102,153 4,864.4
> 51,076.5
> deferred (CURRENT) block cleanout 72 3.4
> 36.0
> dirty buffers inspected 0 0.0
> 0.0
> enqueue conversions 29 1.4
> 14.5
> enqueue releases 233 11.1
> 116.5
> enqueue requests 233 11.1
> 116.5
> enqueue timeouts 0 0.0
> 0.0
> execute count 100,327 4,777.5
> 50,163.5
> free buffer inspected 0 0.0
> 0.0
> free buffer requested 615 29.3
> 307.5
> hot buffers moved to head of LRU 89 4.2
> 44.5
> immediate (CR) block cleanout app 0 0.0
> 0.0
> immediate (CURRENT) block cleanou 17 0.8
> 8.5
> index fast full scans (full) 0 0.0
> 0.0
> index fetch by key 2,638 125.6
> 1,319.0
> index scans kdiixs1 30,109 1,433.8
> 15,054.5
> leaf node 90-10 splits 2 0.1
> 1.0
> leaf node splits 8 0.4
> 4.0
> logons cumulative 0 0.0
> 0.0
> messages received 26 1.2
> 13.0
> Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> messages sent 26 1.2
> 13.0
> no buffer to keep pinned count 0 0.0
> 0.0
> no work - consistent read gets 5,617 267.5
> 2,808.5
> opened cursors cumulative 166 7.9
> 83.0
> parse count (failures) 0 0.0
> 0.0
> parse count (hard) 8 0.4
> 4.0
> parse count (total) 166 7.9
> 83.0
> parse time cpu 6 0.3
> 3.0
> parse time elapsed 8 0.4
> 4.0
> physical reads 684 32.6
> 342.0
> physical reads direct 684 32.6
> 342.0
> physical writes 684 32.6
> 342.0
> physical writes direct 684 32.6
> 342.0
> physical writes non checkpoint 684 32.6
> 342.0
> pinned buffers inspected 0 0.0
> 0.0
> prefetched blocks 0 0.0
> 0.0
> prefetched blocks aged out before 0 0.0
> 0.0
> process last non-idle time 0 0.0
> 0.0
> recursive calls 103,762 4,941.1
> 51,881.0
> recursive cpu usage 1,238 59.0
> 619.0
> redo blocks written 49,345 2,349.8
> 24,672.5
> redo buffer allocation retries 2 0.1
> 1.0
> redo entries 100,943 4,806.8
> 50,471.5
> redo log space requests 2 0.1
> 1.0
> redo log space wait time 22 1.1
> 11.0
> redo ordering marks 294 14.0
> 147.0
> redo size 24,417,856 1,162,755.1
> ############
> redo synch time 2 0.1
> 1.0
> redo synch writes 2 0.1
> 1.0
> redo wastage 8,436 401.7
> 4,218.0
> redo write time 82 3.9
> 41.0
> redo writer latching time 0 0.0
> 0.0
> redo writes 26 1.2
> 13.0
> rollback changes - undo records a 0 0.0
> 0.0
> rollbacks only - consistent read 30 1.4
> 15.0
> rows fetched via callback 1,006 47.9
> 503.0
> session connect time 0 0.0
> 0.0
> session logical reads 137,208 6,533.7
> 68,604.0
> session pga memory 65,312 3,110.1
> 32,656.0
> session pga memory max 1,324,180 63,056.2
> 662,090.0
> session uga memory 130,928 6,234.7
> 65,464.0
> session uga memory max 65,464 3,117.3
> 32,732.0
> shared hash latch upgrades - no w 29,851 1,421.5
> 14,925.5
> shared hash latch upgrades - wait 0 0.0
> 0.0
> sorts (disk) 2 0.1
> 1.0
> sorts (memory) 73 3.5
> 36.5
> sorts (rows) 23,780 1,132.4
> 11,890.0
> summed dirty queue length 0 0.0
> 0.0
> switch current to new buffer 0 0.0
> 0.0
> table fetch by rowid 2,619 124.7
> 1,309.5
> table fetch continued row 0 0.0
> 0.0
> table scan blocks gotten 12 0.6
> 6.0
> table scan rows gotten 1,308 62.3
> 654.0
> table scans (long tables) 0 0.0
> 0.0
> table scans (short tables) 9 0.4
> 4.5
> transaction rollbacks 0 0.0
> 0.0
> Instance Activity Stats for DB: ALEF Instance: alef Snaps: 3 -4
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> user calls 9 0.4
> 4.5
> user commits 2 0.1
> 1.0
> user rollbacks 0 0.0
> 0.0
> workarea executions - onepass 2 0.1
> 1.0
> workarea executions - optimal 98 4.7
> 49.0
> -------------------------------------------------------------
> Session Statistics for DB: ALEF Instance: alef Snaps: 3 -4
> Session Id: 10 Serial#: 12631
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> active txn count during cleanout 170 8.1
> buffer is not pinned count 160 7.6
> bytes received via sql*net from c 165 7.9
> bytes sent via sql*net to client 121 5.8
> calls to get snapshot scn: kcmgss 100,154 4,769.2
> calls to kcmgas 335 16.0
> calls to kcmgcs 150 7.1
> change write time 905 43.1
> cleanout - number of ktugct calls 170 8.1
> cluster key scan block gets 100 4.8
> cluster key scans 60 2.9
> commit cleanouts 181 8.6
> commit cleanouts successfully com 181 8.6
> consistent changes 30 1.4
> consistent gets 483 23.0
> consistent gets - examination 310 14.8
> cpu used by this session 1,198 57.1
> cr blocks created 30 1.4
> data blocks consistent reads - un 30 1.4
> db block changes 201,336 9,587.4
> db block gets 101,645 4,840.2
> deferred (current) block cleanout 30 1.4
> enqueue releases 137 6.5
> enqueue requests 137 6.5
> execute count 100,102 4,766.8
> free buffer requested 561 26.7
> hot buffers moved to head of lru 70 3.3
> index fetch by key 80 3.8
> index scans kdiixs1 20 1.0
> messages sent 24 1.1
> no work - consistent read gets 110 5.2
> opened cursors cumulative 103 4.9
> opened cursors current
> parse count (hard) 1 0.1
> parse count (total) 103 4.9
> recursive calls 101,084 4,813.5
> recursive cpu usage 1,148 54.7
> redo buffer allocation retries 2 0.1
> redo entries 100,649 4,792.8
> redo log space requests 2 0.1
> redo log space wait time 22 1.1
> redo ordering marks 294 14.0
> redo size 23,690,488 1,128,118.5
> redo synch time 2 0.1
> redo synch writes 1 0.1
> rollbacks only - consistent read 30 1.4
> session logical reads 102,128 4,863.2
> shared hash latch upgrades - no w 20 1.0
> sql*net roundtrips to/from client 1 0.1
> table fetch by rowid 20 1.0
> user calls 2 0.1
> user commits 1 0.1
> -------------------------------------------------------------
> Tablespace IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->ordered by IOs (Reads + Writes) desc
>
> Tablespace
> ------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> TMP
> 212 10 6.1 3.2 236 11 0
> 0.0
> -------------------------------------------------------------
> File IO Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->ordered by Tablespace, File
>
> Tablespace Filename
> ------------------------
> ----------------------------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> TMP C:\ORACLE\ORADATA\ALEF\TMP.DBF
> 212 10 6.1 3.2 236 11 0
>
> -------------------------------------------------------------
> Buffer Pool Statistics for DB: ALEF Instance: alef Snaps: 3 -4
> -> Standard block size Pools D: default, K: keep, R: recycle
> -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
>
> Free
> Write Buffer
> Number of Cache Buffer Physical Physical Buffer
> Complete Busy
> P Buffers Hit % Gets Reads Writes Waits
> Waits Waits
> --- ---------- ----- ----------- ----------- ---------- -------
> -------- ------
> D 15,180 100.0 166,312 0 0 0
> 0 0
> -------------------------------------------------------------
>
> Instance Recovery Stats for DB: ALEF Instance: alef Snaps: 3 -4
> -> B: Begin snapshot, E: End snapshot
>
> Targt Estd Log File Log Ckpt
> Log Ckpt
> MTTR MTTR Recovery Actual Target Size Timeout
> Interval
> (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
> Redo Blks
> - ----- ----- ---------- ---------- ---------- ---------- ----------
> ----------
> B 0 58 1900 194210 194210 921600 194210
> E 0 58 2549 243555 243555 921600 243555
> -------------------------------------------------------------
>
> Buffer Pool Advisory for DB: ALEF Instance: alef End Snap: 4
> -> Only rows with estimated physical reads >0 are displayed
> -> ordered by Block Size, Buffers For Estimate
>
> Size for Size Buffers for Est Physical
> Estimated
> P Estimate (M) Factr Estimate Read Factor Physical
> Reads
> --- ------------ ----- ---------------- -------------
> ------------------
> D 24 .1 1,518 20.56
> 49,508,886
> D 48 .2 3,036 8.13
> 19,569,781
> D 72 .3 4,554 6.32
> 15,224,822
> D 96 .4 6,072 5.42
> 13,042,042
> D 120 .5 7,590 3.67
> 8,834,123
> D 144 .6 9,108 3.20
> 7,711,832
> D 168 .7 10,626 2.30
> 5,528,156
> D 192 .8 12,144 1.85
> 4,464,981
> D 216 .9 13,662 1.54
> 3,719,772
> D 240 1.0 15,180 1.00
> 2,408,492
> D 264 1.1 16,698 0.95
> 2,286,679
> D 288 1.2 18,216 0.80
> 1,934,676
> D 312 1.3 19,734 0.71
> 1,709,859
> D 336 1.4 21,252 0.65
> 1,567,446
> D 360 1.5 22,770 0.63
> 1,512,809
> D 384 1.6 24,288 0.63
> 1,509,226
> D 408 1.7 25,806 0.63
> 1,508,330
> D 432 1.8 27,324 0.62
> 1,504,748
> D 456 1.9 28,842 0.62
> 1,504,748
> D 480 2.0 30,360 0.62
> 1,504,748
> -------------------------------------------------------------
> PGA Aggr Target Stats for DB: ALEF Instance: alef Snaps: 3 -4
> -> B: Begin snap E: End snap (rows dentified with B or E contain
> data
> which is absolute i.e. not diffed over the interval)
> -> PGA cache hit % - percentage of W/A (WorkArea) data processed only
> in-memory
> -> Auto PGA Target - actual workarea memory target
> -> W/A PGA Used - amount of memory used for all Workareas (manual +
> auto)
> -> %PGA W/A Mem - percentage of PGA memory allocated to workareas
> -> %Auto W/A Mem - percentage of workarea memory controlled by Auto
> Mem Mgmt
> -> %Man W/A Mem - percentage of workarea memory under manual
> control
>
> PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
> --------------- ---------------- -------------------------
> 60.5 9 6
>
> %PGA %Auto %Man
> PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
> Global Mem
> Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem
> Bound(K)
> - --------- --------- ---------- ---------- ------ ------ ------
> ----------
> B 29 19 11.3 0.0 .0 .0 .0
> 1,464
> E 29 20 11.4 0.0 .0 .0 .0
> 1,464
> -------------------------------------------------------------
>
> PGA Aggr Target Histogram for DB: ALEF Instance: alef Snaps: 3 -4
> -> Optimal Executions are purely in-memory operations
>
> Low High
> Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
> ------- ------- -------------- ------------- ------------ ------------
> 16K 32K 89 89 0 0
> 32K 64K 4 4 0 0
> 64K 128K 1 1 0 0
> 512K 1024K 2 2 0 0
> 2M 4M 2 0 2 0
> -------------------------------------------------------------
>
> PGA Memory Advisory for DB: ALEF Instance: alef End Snap: 4
> -> When using Auto Memory Mgmt, minimally choose a
> pga_aggregate_target value
> where Estd PGA Overalloc Count is 0
>
> Estd Extra Estd PGA Estd
> PGA
> PGA Target Size W/A MB W/A MB Read/ Cache
> Overalloc
> Est (MB) Factr Processed Written to Disk Hit %
> Count
> ---------- ------- ---------------- ---------------- --------
> ----------
> 14 0.5 42,411.1 42.6 100.0
> 12
> 21 0.8 42,411.1 14.0 100.0
> 7
> 29 1.0 42,411.1 5.5 100.0
> 6
> 34 1.2 42,411.1 5.5 100.0
> 5
> 40 1.4 42,411.1 5.5 100.0
> 4
> 46 1.6 42,411.1 5.5 100.0
> 3
> 51 1.8 42,411.1 5.5 100.0
> 3
> 57 2.0 42,411.1 2.9 100.0
> 2
> 86 3.0 42,411.1 0.0 100.0
> 0
> 114 4.0 42,411.1 0.0 100.0
> 0
> 172 6.0 42,411.1 0.0 100.0
> 0
> 229 8.0 42,411.1 0.0 100.0
> 0
> -------------------------------------------------------------
> Rollback Segment Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->A high value for "Pct Waits" suggests more rollback segments may be
> required
> ->RBS stats may not be accurate between begin and end snaps when using
> Auto Undo
> managment, as RBS may be dynamically created and dropped as needed
>
> Trans Table Pct Undo Bytes
> RBS No Gets Waits Written Wraps Shrinks
> Extends
> ------ -------------- ------- --------------- -------- --------
> --------
> 0 1.0 0.00 0 0 0
> 0
> 1 1.0 0.00 0 0 0
> 0
> 2 1.0 0.00 0 0 0
> 0
> 3 1.0 0.00 0 0 0
> 0
> 4 1.0 0.00 0 0 0
> 0
> 5 45.0 0.00 253,802 2 0
> 0
> 6 2.0 0.00 0 0 0
> 0
> 7 495.0 0.00 6,607,496 19 0
> 10
> 8 2.0 0.00 2,436 0 0
> 0
> 9 1.0 0.00 0 0 0
> 0
> 10 1.0 0.00 0 0 0
> 0
> -------------------------------------------------------------
> Rollback Segment Storage for DB: ALEF Instance: alef Snaps: 3 -4
> ->Optimal Size should be larger than Avg Active
>
> RBS No Segment Size Avg Active Optimal Size Maximum Size
> ------ --------------- --------------- --------------- ---------------
> 0 442,368 6,553 442,368
> 1 507,904 289,784 5,357,568
> 2 442,368 1,542,769 9,551,872
> 3 7,454,720 4,842,640 18,989,056
> 4 507,904 127,533 6,406,144
> 5 7,323,648 5,596,574 7,323,648
> 6 1,163,264 2,803,943 7,520,256
> 7 7,323,648 8,093,488 20,037,632
> 8 376,832 139,607 17,940,480
> 9 638,976 2,238,838 8,372,224
> 10 14,663,680 5,649,321 14,663,680
> -------------------------------------------------------------
> Latch Activity for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics
> for
> willing-to-wait latch get requests
> ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get
> requests
> ->"Pct Misses" for both should be very close to 0.0
>
> Pct Avg Wait
> Pct
> Get Get Slps Time
> NoWait NoWait
> Latch Requests Miss /Miss (s)
> Requests Miss
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> Consistent RBA 27 0.0 0
> 0
> FIB s.o chain latch 4 0.0 0
> 0
> FOB s.o list latch 6 0.0 0
> 0
> SQL memory manager latch 1 0.0 0
> 6 0.0
> SQL memory manager worka 477 0.0 0
> 0
> active checkpoint queue 9 0.0 0
> 0
> cache buffer handles 2 0.0 0
> 0
> cache buffers chains 575,427 0.0 0
> 610 0.0
> cache buffers lru chain 622 0.0 0
> 635 0.0
> channel operations paren 12 0.0 0
> 0
> checkpoint queue latch 372 0.0 0
> 641 0.0
> child cursor hash table 118 0.0 0
> 0
> dml lock allocation 160 0.0 0
> 0
> enqueue hash chains 497 0.0 0
> 0
> enqueues 298 0.0 0
> 0
> file number translation 476 0.0 0
> 0
> global ctx hash table la 568 0.0 0
> 0
> hash table column usage 0 0
> 80 0.0
> lgwr LWN SCN 26 0.0 0
> 0
> library cache 220,911 0.0 0
> 6 0.0
> library cache load lock 18 0.0 0
> 0
> library cache pin 201,886 0.0 0
> 0
> library cache pin alloca 901 0.0 0
> 0
> list of block allocation 21 0.0 0
> 0
> loader state object free 12 0.0 0
> 0
> messages 185 0.0 0
> 0
> mostly latch-free SCN 26 0.0 0
> 0
> multiblock read objects 130 0.0 0
> 0
> object stats modificatio 487 0.0 0
> 0
> post/wait queue 54 0.0 0
> 4 0.0
> redo allocation 101,019 0.0 0
> 0
> redo copy 0 0
> 100,962 0.0
> redo writing 105 0.0 0
> 0
> row cache enqueue latch 1,161 0.0 0
> 0
> row cache objects 1,572 0.0 0
> 0
> sequence cache 3 0.0 0
> 0
> session allocation 64 0.0 0
> 0
> session idle bit 50 0.0 0
> 0
> session timer 8 0.0 0
> 0
> shared pool 102,067 0.0 0
> 0
> simulator hash latch 6,195 0.0 0
> 0
> simulator lru latch 37 0.0 0
> 48 0.0
> sort extent pool 18 0.0 0
> 0
> undo global data 375 0.0 0
> 0
> -------------------------------------------------------------
> Top 5 Logical Reads per Segment for DB: ALEF Instance: alef Snaps:
> 3 -4
> -> End Segment Logical Reads Threshold: 10000
>
> Subobject Obj.
> Logical
> Owner Tablespace Object Name Name Type
> Reads %Total
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> PERFSTAT USERS STATS$SQL_SUMMARY_PK INDEX
> 29,504 96.14
> SYS SYSTEM USER$ TABLE
> 448 1.46
> SYS SYSTEM I_OBJ1 INDEX
> 336 1.09
> SYS SYSTEM I_FILE#_BLOCK# INDEX
> 64 .21
> SYS SYSTEM I_CCOL2 INDEX
> 64 .21
> -------------------------------------------------------------
> Dictionary Cache Stats for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Pct Misses" should be very low (< 2% in most cases)
> ->"Cache Usage" is the number of cache entries being used
> ->"Pct SGA" is the ratio of usage to allocated size for that cache
>
> Get Pct Scan Pct Mod
> Final
> Cache Requests Miss Reqs Miss Reqs
> Usage
> ------------------------- ------------ ------ ------- ----- --------
> ----------
> dc_free_extents 30 33.3 10 0.0 30
> 3
> dc_histogram_defs 163 66.9 0 0
> 278
> dc_object_ids 91 0.0 0 0
> 771
> dc_objects 48 0.0 0 0
> 1,496
> dc_segments 55 0.0 0 10
> 533
> dc_tablespace_quotas 10 0.0 0 10
> 9
> dc_tablespaces 82 0.0 0 0
> 18
> dc_used_extents 10 100.0 0 10
> 97
> dc_user_grants 32 0.0 0 0
> 39
> dc_usernames 15 0.0 0 0
> 29
> dc_users 86 0.0 0 0
> 43
> -------------------------------------------------------------
>
>
> Library Cache Activity for DB: ALEF Instance: alef Snaps: 3 -4
> ->"Pct Misses" should be very low
>
> Get Pct Pin Pct
> Invali-
> Namespace Requests Miss Requests Miss Reloads
> dations
> --------------- ------------ ------ -------------- ------ ----------
> --------
> CLUSTER 7 0.0 11 0.0 0
> 0
> SQL AREA 143 0.0 100,759 0.0 1
> 0
> TABLE/PROCEDURE 123 0.0 330 2.7 0
> 0
> -------------------------------------------------------------
> Shared Pool Advisory for DB: ALEF Instance: alef End Snap: 4
> -> Note there is often a 1:Many correlation between a single logical
> object
> in the Library Cache, and the physical number of memory objects
> associated
> with it. Therefore comparing the number of Lib Cache objects (e.g.
> in
> v$librarycache), with the number of Lib Cache Memory Objects is
> invalid
>
> Estd
> Shared Pool SP Estd Estd Estd Lib LC Time
> Size for Size Lib Cache Lib Cache Cache Time Saved Estd
> Lib Cache
> Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
> Obj Hits
> ----------- ----- ---------- ------------ ------------ -------
> ---------------
> 120 .5 119 7,623 30,994 1.0
> 7,313,661
> 144 .6 142 9,460 30,995 1.0
> 7,313,864
> 168 .7 165 11,320 30,995 1.0
> 7,314,067
> 192 .8 188 12,796 30,995 1.0
> 7,314,195
> 216 .9 211 14,626 30,995 1.0
> 7,314,311
> 240 1.0 234 16,471 31,000 1.0
> 7,315,055
> 264 1.1 257 18,341 31,002 1.0
> 7,315,464
> 288 1.2 280 20,506 31,006 1.0
> 7,315,938
> 312 1.3 303 22,228 31,009 1.0
> 7,316,207
> 336 1.4 326 24,203 31,011 1.0
> 7,316,956
> 360 1.5 349 25,233 31,015 1.0
> 7,317,565
> 384 1.6 372 26,986 31,020 1.0
> 7,318,547
> 408 1.7 395 28,894 31,024 1.0
> 7,319,058
> 432 1.8 418 30,292 31,024 1.0
> 7,319,133
> 456 1.9 441 32,095 31,024 1.0
> 7,319,148
> 480 2.0 464 33,992 31,024 1.0
> 7,319,269
> -------------------------------------------------------------
> SGA Memory Summary for DB: ALEF Instance: alef Snaps: 3 -4
>
> SGA regions Size in Bytes
> ------------------------------ ----------------
> Database Buffers 251,658,240
> Fixed Size 454,156
> Redo Buffers 5,140,480
> Variable Size 293,601,280
> ----------------
> sum 550,854,156
> -------------------------------------------------------------
>
>
> SGA breakdown difference for DB: ALEF Instance: alef Snaps: 3 -4
>
> Pool Name Begin value End
> value % Diff
> ------ ------------------------------ ----------------
> ---------------- -------
> java free memory 16,777,216
> 16,777,216 0.00
> large free memory 8,388,608
> 8,388,608 0.00
> shared 1M buffer 2,098,176
> 2,098,176 0.00
> shared Checkpoint queue 282,304
> 282,304 0.00
> shared FileIdentificatonBlock 323,292
> 323,292 0.00
> shared FileOpenBlock 695,504
> 695,504 0.00
> shared KGK heap 3,768
> 3,768 0.00
> shared KGLS heap 2,697,968
> 2,740,792 1.59
> shared KQR M PO 1,286,608
> 1,342,456 4.34
> shared KQR S PO 256,936
> 256,936 0.00
> shared KQR S SO 9,984
> 9,984 0.00
> shared KQR X PO 4,864
> 4,864 0.00
> shared KSXR large reply queue 166,104
> 166,104 0.00
> shared KSXR pending messages que 841,036
> 841,036 0.00
> shared KSXR receive buffers 1,033,000
> 1,033,000 0.00
> shared PL/SQL DIANA 3,349,584
> 3,349,584 0.00
> shared PL/SQL MPCODE 780,136
> 780,136 0.00
> shared PLS non-lib hp 2,068
> 2,068 0.00
> shared character set object 392,368
> 392,368 0.00
> shared dictionary cache 1,610,880
> 1,610,880 0.00
> shared enqueue 171,860
> 171,860 0.00
> shared errors 42,612
> 42,612 0.00
> shared event statistics per sess 1,718,360
> 1,718,360 0.00
> shared fixed allocation callback 180
> 180 0.00
> shared free memory 16,697,172
> 16,693,304 -0.02
> shared joxs heap init 1,244
> 1,244 0.00
> shared kgl simulator 4,648,196
> 4,648,196 0.00
> shared library cache 27,913,852
> 27,874,604 -0.14
> shared message pool freequeue 834,752
> 834,752 0.00
> shared miscellaneous 4,783,592
> 4,813,440 0.62
> shared parameters 22,596
> 22,596 0.00
> shared pl/sql source 7,752
> 7,752 0.00
> shared sessions 410,720
> 410,720 0.00
> shared sim memory hea 71,996
> 71,996 0.00
> shared sql area 195,185,012
> 195,099,384 -0.04
> shared table definiti 8,296
> 8,520 2.70
> shared trigger defini 23,236
> 23,236 0.00
> shared trigger inform 2,324
> 2,324 0.00
> shared trigger source 3,736
> 3,736 0.00
> shared type object de 53,388
> 53,388 0.00
> buffer_cache 251,658,240
> 251,658,240 0.00
> fixed_sga 454,156
> 454,156 0.00
> log_buffer 5,132,288
> 5,132,288 0.00
> -------------------------------------------------------------
> init.ora Parameters for DB: ALEF Instance: alef Snaps: 3 -4
>
> End
> value
> Parameter Name Begin value (if
> different)
> ----------------------------- ---------------------------------
> --------------
> background_dump_dest c:\oracle\admin\alef\bdump
> compatible 9.2.0.0.0
> control_files c:\oracle\oradata\alef\control01.
> core_dump_dest c:\oracle\admin\alef\cdump
> db_block_size 16384
> db_cache_size 251658240
> db_domain
> db_file_multiblock_read_count 64
> db_name alef
> hash_join_enabled TRUE
> ifile c:\oracle\admin\alef\pfile\init.o
> instance_name alef
> java_pool_size 16777216
> large_pool_size 8388608
> log_buffer 5000192
> open_cursors 300
> pga_aggregate_target 30000000
> processes 150
> query_rewrite_enabled FALSE
> shared_pool_size 251658240
> sort_area_retained_size 4000000
> sort_area_size 4000000
> undo_management AUTO
> undo_retention 3600
> undo_tablespace UNDOTBS
> user_dump_dest c:\oracle\admin\alef\udump
> utl_file_dir c:\oracle\utl_file
> -------------------------------------------------------------
>
> End of Report
>
>
>
>
> I hope SOMETHING.
>
> Thank you.
>
>
> Kamal
Received on Thu Jun 05 2003 - 20:38:42 CDT
 |
 |