Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Init.ora Parameters
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1085722484.631624_at_yasure...
> Mill wrote:
>
> > Hi
> >
> > I am running a Oracle 8.1.7.4 database on W2k Adv Server with 2gb Ram,
we
> > are
> > planning to upgrade the RAM to 8gb, i understand i will need to adjust
the
> > db_block_buffers & shared_pool_size in the init.ora to utilise the new
> > memory and
> > make a larger SGA.
> >
> > My parameters are as follows for these settings.
> >
> > db_block_buffers = 1200
> > shared_pool_size = 1000000000
> >
> >
> > Can someone recommend new settings for these given the fact that i am
adding
> > new
> > memory to the server, my main concern is to correctly configure these so
i
> > don't get
> > excessive paging and swapping, I appreciate it will need tweaking after
i
> > have run the
> > system for awhile and monitored things
> >
> > Your comments greatly Appreciate
> >
> > Mill.
>
> Throwing more RAM at a server is not a recipe for better performance. In
> some cases it can make performance worse. So just throwing a few larger
> integers at you is a meaningless exercise and as I am sure you know
> tuning is not a trivial matter.
>
> First ... why are you buying the RAM? What is it in your STATSPACK
> analysis that makes you think this is a good idea?
>
> When we know that we may be able to help you with whether you just
> wasted money and, if not, where the resource might be best put to use.
> We need numbers and facts related to resources. If you don't have
> them ... I'd send the RAM back to the supplier.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Statspack results are as follows.
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- ------------
TEST 3136346323 test 1 8.1.7.4.1 NO ORACLETEST Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 35 21-Apr-04 08:50:36 14 End Snap: 37 21-Apr-04 09:22:41 14 Elapsed: 32.08 (mins)
Cache Sizes
db_block_buffers: 12000 log_buffer: 1572864 db_block_size: 8192 shared_pool_size: 600000000 Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 1,240.41 1,410.39 Logical reads: 379.78 431.82 Block changes: 7.99 9.09 Physical reads: 157.62 179.22 Physical writes: 2.12 2.41 User calls: 2.64 3.01 Parses: 6.70 7.62 Hard parses: 0.01 0.01 Sorts: 0.96 1.09 Logons: 0.84 0.96 Executes: 9.55 10.85 Transactions: 0.88 % Blocks changed per Read: 2.10 Recursive Call %: 95.65 Rollback per transaction %: 0.00 Rows per Sort: 13.81
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.96 Redo NoWait %: 100.00 Buffer Hit %: 58.50 In-memory Sort %: 100.00 Library Hit %: 99.58 Soft Parse %: 99.87 Execute to Parse %: 29.78 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 100.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 4.61 4.70% SQL with executions>1: 60.46 66.46 % Memory for SQL w/exec>1: 67.35 79.69
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) WtTime
-------------------------------------------- ------------ ------------ ----- -- db file sequential read 301,405 129 46.91 control file parallel write 624 53 19.27 log file sync 1,063 46 16.73 db file scattered read 143 14 5.09 db file parallel write 230 12 4.36 -------------------------------------------------------------Wait Events for DB: TEST Instance: test Snaps: 35 -37
-> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms)/txn
---------------------------- ------------ ---------- ----------- ------ ---- -- db file sequential read 301,405 0 129 0 178.0 control file parallel write 624 0 53 1 0.4 log file sync 1,063 0 46 0 0.6 db file scattered read 143 0 14 1 0.1 db file parallel write 230 0 12 1 0.1 control file sequential read 52 0 12 2 0.0 log file parallel write 991 0 5 0 0.6 file identify 9 0 4 4 0.0 buffer busy waits 322 0 0 0 0.2 SQL*Net more data to client 19 0 0 0 0.0 file open 19 0 0 0 0.0 refresh controlfile command 12 0 0 0 0.0 latch free 5 5 0 0 0.0 SQL*Net message from client 388 0 15,005 387 0.2 SQL*Net message to client 389 0 0 0 0.2 SQL*Net more data from clien 1 0 0 0 0.0 -------------------------------------------------------------Background Wait Events for DB: TEST Instance: test Snaps: 35 -37 -> ordered by wait time desc, waits desc (idle events last)
Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms)/txn
---------------------------- ------------ ---------- ----------- ------ ---- -- control file parallel write 624 0 53 1 0.4 db file scattered read 138 0 14 1 0.1 db file parallel write 230 0 12 1 0.1 log file parallel write 991 0 5 0 0.6 db file sequential read 37 0 0 0 0.0 control file sequential read 12 0 0 0 0.0 latch free 2 2 0 0 0.0 rdbms ipc message 3,842 1,882 49,075 128 2.3 pmon timer 1,648 1,648 16,325 99 1.0 smon timer 6 6 0 0 0.0 -------------------------------------------------------------SQL ordered by Gets for DB: TEST Instance: test Snaps: 35 -37 -> End Buffer Gets Threshold: 10000
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
Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
440,734 1 440,734.0 60.3 3001180066 SELECT "INVENTORY_TRANSACTION_HIST2\"."PART_NO", "INVENTORY_TRAN SACTION_HIST2\"."CONTRACT", "INVENTORY_TRANSACTION_HIST2\"."LOCA TION_NO", "INVENTORY_TRANSACTION_HIST2\"."REJECT_CODE", "INVENTO RY_TRANSACTION_HIST2\"."TRANSACTION", "INVENTORY_TRANSACTION_HIS T2\"."DATE_APPLIED", "INVENTORY_TRANSACTION_HIST2\"."QUANTITY", 183,930 56 3,284.5 25.2 3942090408 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(2,'Heavy Queue','en',job); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
181,954 2 90,977.0 24.9 778547950 BEGIN Shop_Ord_Auto_Processing_API.Execute_Auto_Process__(:attr_ ); END; 78,284 2 39,142.0 10.7 4015038009 SELECT ORDER_NO,RELEASE_NO,SEQUENCE_NO,CONTRACT,PART_NO,REVISED_ START_DATE,PROCESS_TYPE FROM SHOP_ORD_TAB WHERE PROCESS_TYPE LIKE :b1 AND ROWSTATE = 'Planned' AND PART_NO LIKE :b2 AND CONTRACT = :b3
77,322 2 38,661.0 10.6 2664332437 SELECT ORDER_NO,RELEASE_NO,SEQUENCE_NO,CONTRACT,PART_NO,REVISED_ START_DATE,PROCESS_TYPE,NVL(SHOP_ORDER_PRIORITY_API.GET_ADJUSTME NT_FACTOR(CONTRACT,PRIORITY_CATEGORY),0) ADJUSTMENT_FACTOR FRO M SHOP_ORD_TAB WHERE PROCESS_TYPE LIKE :b1 AND ROWSTATE = 'Re leased' AND PART_NO LIKE :b2 AND CONTRACT = :b3 ORDER BY ADJU
31,160 679 45.9 4.3 3013728279 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
24,882 2,092 11.9 3.4 1576697787 select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env from sys.job$ j, sys.user$ u1, sys.user$ u 2, sys.user$ u3 where job=:1 and (next_date < sysdate or :2 != 0) and lowner = u1.name and powner = u2.name and cowner = u3.na me for update nowait
24,594 2 12,297.0 3.4 4021946156 SELECT PROPOSAL_NO,CONTRACT,PART_NO,PLAN_ORDER_REC,PROP_START_DA TE,PROCESS_TYPE FROM SHOP_ORDER_PROP_TAB WHERE PROCESS_TYPE L IKE :b1 AND ROWSTATE = 'ProposalCreated' AND PART_NO LIKE :b 2 AND CONTRACT = :b3
10,671 2 5,335.5 1.5 3376831664 BEGIN statspack.snap; END;
SQL ordered by Gets for DB: TEST Instance: test Snaps: 35 -37
-> 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
Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
10,140 2,028 5.0 1.4 2016510618 SELECT USER FROM SYS.DUAL 6,361 187 34.0 0.9 3629259683 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(1,'Fast Queue','en',job); :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END;
6,361 187 34.0 0.9 4121793289 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(3,'GL Update','en',job); :mydate := next_date; IF broken TH EN :b := 1; ELSE :b := 0; END IF; END;
6,360 187 34.0 0.9 3258801479 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(4,'Shop Order','en',job); :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END;
5,413 676 8.0 0.7 1857448525 update sys.job$ set failures=0, this_date=null, flag=:1, last_da te=:2, next_date=:3, total=total+(sysdate-nvl(this_date,sysdate )) where job=:4
4,054 2,027 2.0 0.6 1924617220 SELECT IDENTITY FROM FND_USER_TAB WHERE ORACLE_USER = :b1
3,740 748 5.0 0.5 2049785732 select sysdate + 2/86400 from dual
3,072 6 512.0 0.4 1714733582 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
2,936 1,046 2.8 0.4 313510536 select job from sys.job$ where next_date < sysdate and (field1
2,734 1,367 2.0 0.4 506185880 SELECT VALUE FROM FND_SETTING WHERE PARAMETER = :b1
2,703 2,027 1.3 0.4 1272233705 SELECT VALUE FROM FND_USER_PROPERTY WHERE IDENTITY = :b1 AND NAME = :b2
2,033 673 3.0 0.3 47694755
SELECT * FROM TRANSACTION_SYS_LOCAL_TAB WHERE STATE = 'Posted
' AND QUEUE_ID = :b1 AND LANG_CODE LIKE :b2 ORDER BY CREATED,
ID
SQL ordered by Gets for DB: TEST Instance: test Snaps: 35 -37
-> 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
Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
1,905 56 34.0 0.3 747581250 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(0,'Default Queue','%',job); :mydate := next_date; IF broken
Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
300,368 1 300,368.0 99.0 3001180066 SELECT "INVENTORY_TRANSACTION_HIST2\"."PART_NO", "INVENTORY_TRAN SACTION_HIST2\"."CONTRACT", "INVENTORY_TRANSACTION_HIST2\"."LOCA TION_NO", "INVENTORY_TRANSACTION_HIST2\"."REJECT_CODE", "INVENTO RY_TRANSACTION_HIST2\"."TRANSACTION", "INVENTORY_TRANSACTION_HIS T2\"."DATE_APPLIED", "INVENTORY_TRANSACTION_HIST2\"."QUANTITY", 1,994 6 332.3 0.7 1714733582 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
561 2 280.5 0.2 3376831664 BEGIN statspack.snap; END;
298 2,092 0.1 0.1 1576697787 select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env from sys.job$ j, sys.user$ u1, sys.user$ u 2, sys.user$ u3 where job=:1 and (next_date < sysdate or :2 != 0) and lowner = u1.name and powner = u2.name and cowner = u3.na me for update nowait
211 676 0.3 0.1 1857448525 update sys.job$ set failures=0, this_date=null, flag=:1, last_da te=:2, next_date=:3, total=total+(sysdate-nvl(this_date,sysdate )) where job=:4
92 213 0.4 0.0 2654989612 SELECT ESTIMATED_MATERIAL_COST FROM INVENTORY_PART_CONFIG_TAB WHERE CONTRACT = :b1 AND PART_NO = :b2 AND CONFIGURATION_ID = :b3
41 748 0.1 0.0 2049785732 select sysdate + 2/86400 from dual
8 12 0.7 0.0 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 an d(remoteowner=:4 or remoteowner is null and :4 is null)and(linkn ame=:5 or linkname is null and :5 is null)and(subname=:6 or subn ame is null and :6 is null)
8 5 1.6 0.0 1737259834 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_ obj#, d_owner#, nvl(property,0) from dependency$,obj$ where d_ob j#=:1 and p_obj#=obj#(+) order by order#
7 8 0.9 0.0 395844583
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$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, charsetid,ch arsetform,spare1,spare2 from col$ where obj#=:1 order by intcol#
7 5 1.4 0.0 4049165760 select order#,columns,types from access$ where d_obj#=:1 SQL ordered by Reads for DB: TEST Instance: test Snaps: 35 -37 -> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
5 5 1.0 0.0 365454555 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1
5 6 0.8 0.0 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
5 6 0.8 0.0 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 3 1.0 0.0 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt ,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n
3 3 1.0 0.0 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt, blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols, property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,nvl(spa
2 1,046 0.0 0.0 313510536 select job from sys.job$ where next_date < sysdate and (field1
2 6 0.3 0.0 1966425544 select text from view$ where rowid=:1
2 679 0.0 0.0 3013728279 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
2 3 0.7 0.0 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
1 224 0.0 0.0 282324099 select sysdate + 30/86400 from dual
0 673 0.0 0.0 47694755 SELECT * FROM TRANSACTION_SYS_LOCAL_TAB WHERE STATE = 'Posted ' AND QUEUE_ID = :b1 AND LANG_CODE LIKE :b2 ORDER BY CREATED, ID
0 1 0.0 0.0 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
SQL ordered by Reads for DB: TEST Instance: test Snaps: 35 -37 -> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------ 2,092 1,722 0.8 1576697787select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env from sys.job$ j, sys.user$ u1, sys.user$ u 2, sys.user$ u3 where job=:1 and (next_date < sysdate or :2 != 0) and lowner = u1.name and powner = u2.name and cowner = u3.na me for update nowait
2,028 2,028 1.0 2016510618 SELECT USER FROM SYS.DUAL
2,027 1 0.0 1272233705SELECT VALUE FROM FND_USER_PROPERTY WHERE IDENTITY = :b1 AND NAME = :b2
2,027 2,027 1.0 1924617220 SELECT IDENTITY FROM FND_USER_TAB WHERE ORACLE_USER = :b1
1,367 1,367 1.0 506185880 SELECT VALUE FROM FND_SETTING WHERE PARAMETER = :b1
1,046 2,577 2.5 313510536 select job from sys.job$ where next_date < sysdate and (field1
748 748 1.0 2049785732 select sysdate + 2/86400 from dual
679 7,450 11.0 3013728279 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
676 676 1.0 493392946 update sys.job$ set this_date=:1 where job=:2
676 676 1.0 1857448525 update sys.job$ set failures=0, this_date=null, flag=:1, last_da te=:2, next_date=:3, total=total+(sysdate-nvl(this_date,sysdate )) where job=:4
676 676 1.0 3760204083 SELECT NAME FROM SYS.USER$ WHERE USER# = USERENV('SCHEMAID')
675 0 0.0 187676852alter session set NLS_LANGUAGE='ENGLISH' NLS_TERRITORY='UNITED K INGDOM' NLS_CURRENCY='£' NLS_ISO_CURRENCY='UNITED KINGDOM' NLS_N UMERIC_CHARACTERS='. ' NLS_DATE_FORMAT='YYYYMMDD' NLS_DATE_LANGU AGE='ENGLISH' NLS_SORT='BINARY'
673 2 0.0 47694755SELECT * FROM TRANSACTION_SYS_LOCAL_TAB WHERE STATE = 'Posted ' AND QUEUE_ID = :b1 AND LANG_CODE LIKE :b2 ORDER BY CREATED, ID
566 0 0.0 713944817 SQL ordered by Executions for DB: TEST Instance: test Snaps: 35 -37 -> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------SELECT 1 FROM SHOP_ORD_PROC_TYPE_EVENT_TAB WHERE PROCESS_TYPE
566 566 1.0 1010136994 SELECT OFFSET FROM SITE_TAB WHERE CONTRACT = :b1
374 374 1.0 1180350016 select sysdate + 5/86400 from dual
224 224 1.0 282324099 select sysdate + 30/86400 from dual
213 213 1.0 2654989612 SELECT ESTIMATED_MATERIAL_COST FROM INVENTORY_PART_CONFIG_TAB WHERE CONTRACT = :b1 AND PART_NO = :b2 AND CONFIGURATION_ID = :b3
187 187 1.0 3258801479 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(4,'Shop Order','en',job); :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END;
187 187 1.0 3629259683 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(1,'Fast Queue','en',job); :mydate := next_date; IF broken T HEN :b := 1; ELSE :b := 0; END IF; END;
187 187 1.0 4121793289 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(3,'GL Update','en',job); :mydate := next_date; IF broken TH EN :b := 1; ELSE :b := 0; END IF; END;
56 56 1.0 747581250 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(0,'Default Queue','%',job); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
56 56 1.0 3942090408 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi ng__(2,'Heavy Queue','en',job); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
12 11 0.9 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 an d(remoteowner=:4 or remoteowner is null and :4 is null)and(linkn ame=:5 or linkname is null and :5 is null)and(subname=:6 or subn ame is null and :6 is null)
8 85 10.6 395844583 SQL ordered by Executions for DB: TEST Instance: test Snaps: 35 -37 -> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------ select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ CPU used by this session 21 0.0 0.0 CPU used when call started 21 0.0 0.0 CR blocks created 174 0.1 0.1 DBWR buffers scanned 8,552 4.4 5.1 DBWR checkpoint buffers written 4,029 2.1 2.4 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 8,532 4.4 5.0 DBWR lru scans 39 0.0 0.0 DBWR make free requests 45 0.0 0.0 DBWR summed scan depth 8,552 4.4 5.1 DBWR transaction table writes 1,695 0.9 1.0 DBWR undo block writes 1,738 0.9 1.0 SQL*Net roundtrips to/from client 355 0.2 0.2 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 2,445 1.3 1.4 buffer is not pinned count 679,430 353.0 401.3 buffer is pinned count 1,522,584 791.0 899.3 bytes received via SQL*Net from c 23,030 12.0 13.6 bytes sent via SQL*Net to client 118,942 61.8 70.3 calls to get snapshot scn: kcmgss 20,600 10.7 12.2 calls to kcmgas 1,708 0.9 1.0 calls to kcmgcs 151 0.1 0.1 change write time 2 0.0 0.0 cleanouts and rollbacks - consist 22 0.0 0.0 cleanouts only - consistent read 80 0.0 0.1 cluster key scan block gets 1,196 0.6 0.7 cluster key scans 733 0.4 0.4 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 5 0.0 0.0 commit cleanout failures: cannot 5 0.0 0.0 commit cleanouts 4,190 2.2 2.5 commit cleanouts successfully com 4,180 2.2 2.5 consistent changes 70 0.0 0.0 consistent gets 698,106 362.7 412.4 cursor authentications 38 0.0 0.0 data blocks consistent reads - un 68 0.0 0.0 db block changes 15,384 8.0 9.1 db block gets 32,965 17.1 19.5 deferred (CURRENT) block cleanout 2,809 1.5 1.7 dirty buffers inspected 45 0.0 0.0 enqueue conversions 678 0.4 0.4 enqueue releases 7,236 3.8 4.3 enqueue requests 7,408 3.9 4.4 enqueue timeouts 172 0.1 0.1 execute count 18,377 9.6 10.9 free buffer inspected 221 0.1 0.1 free buffer requested 303,736 157.8 179.4 hot buffers moved to head of LRU 61,694 32.1 36.4 immediate (CR) block cleanout app 102 0.1 0.1 immediate (CURRENT) block cleanou 260 0.1 0.2 leaf node splits 15 0.0 0.0 logons cumulative 1,619 0.8 1.0 logons current messages received 1,462 0.8 0.9Instance Activity Stats for DB: TEST Instance: test Snaps: 35 -37
Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ messages sent 1,462 0.8 0.9 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 654,056 339.8 386.3 opened cursors cumulative 12,589 6.5 7.4 parse count (hard) 17 0.0 0.0 parse count (total) 12,905 6.7 7.6 parse time elapsed 3 0.0 0.0 physical reads 303,421 157.6 179.2 physical reads direct 0 0.0 0.0 physical writes 4,078 2.1 2.4 physical writes direct 0 0.0 0.0 physical writes non checkpoint 857 0.5 0.5 pinned buffers inspected 176 0.1 0.1 prefetched blocks 1,878 1.0 1.1 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 61,704,731,001 ############ ############ recursive calls 111,842 58.1 66.1 recursive cpu usage 21 0.0 0.0 redo blocks written 5,376 2.8 3.2 redo buffer allocation retries 0 0.0 0.0 redo entries 7,867 4.1 4.7 redo log space requests 0 0.0 0.0 redo size 2,387,796 1,240.4 1,410.4 redo synch time 46 0.0 0.0 redo synch writes 1,010 0.5 0.6 redo wastage 277,548 144.2 163.9 redo write time 10 0.0 0.0 redo writes 991 0.5 0.6 rollbacks only - consistent read 32 0.0 0.0 rows fetched via callback 13,590 7.1 8.0 session connect time 61,704,731,001 ############ ############ session cursor cache hits 721 0.4 0.4 session logical reads 731,071 379.8 431.8 session pga memory 7,427,028,000 3,858,196.4 4,386,903.7 session pga memory max 7,427,032,784 3,858,198.9 4,386,906.6 session uga memory 10,192,392 5,294.8 6,020.3 session uga memory max 119,683,280 62,173.1 70,693.0 sorts (disk) 0 0.0 0.0 sorts (memory) 1,852 1.0 1.1 sorts (rows) 25,581 13.3 15.1 summed dirty queue length 0 0.0 0.0 switch current to new buffer table fetch by rowid 1,088,165 565.3 642.7 table fetch continued row 225 0.1 0.1 table scan blocks gotten 5,978 3.1 3.5 table scan rows gotten 3,872 2.0 2.3 table scans (long tables) 6 0.0 0.0 table scans (short tables) 3,433 1.8 2.0 total file opens 19 0.0 0.0 user calls 5,088 2.6 3.0 user commits 1,693 0.9 1.0 user rollbacks 0 0.0 0.0 write clones created in foregroun 2 0.0 0.0 -------------------------------------------------------------Tablespace IO Stats for DB: TEST Instance: test Snaps: 35 -37 ->ordered by IOs (Reads + Writes) desc
Tablespace
Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s WaitsWt(ms)
Tablespace Filename
------------------------ --------------------------------------------------- - Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s WaitsWt(ms)
IFSAPP_DATA D:\ORADB\TEST\IFSAPP_DATA2.ORA 296,993 154 0.0 1.0 0 0 0 E:\ORADB\TEST\IFSAPP_DATA01.DBF 182 0 0.0 1.3 3 0 0 IFSAPP_INDEX E:\ORADB\TEST\IFSAPP_INDEX01.DBF 3,155 2 0.0 1.0 6 0 0 PERFSTAT E:\ORADB\TEST\PERFSTAT01.DBF 447 0 2.7 1.0 229 0 0 ROLLBACK E:\ORADB\TEST\ROLLBACK01.DBF 509 0 0.0 1.0 3,433 2 40.0
SYSTEM D:\ORADB\TEST\SYSTEM01.DBF 306 0 0.8 7.0 407 0 3180.0
Free Write Buffer Buffer Consistent Physical Physical Buffer Complete Busy P Gets Gets Reads Writes Waits WaitsWaits
-- D 303,766 665,275 303,450 4,078 0 0 322 ------------------------------------------------------------- Buffer wait Statistics for DB: TEST Instance: test Snaps: 35 -37 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (cs) Time (cs) ------------------ ----------- ---------- --------- data block 318 0 0 undo header 4 0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: TEST Instance: test Snaps: 35 -37 ->A high value for "Pct Waits" suggests more rollback segments may be required Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ ------------ ------- --------------- -------- -------- -------- 0 8.0 0.00 0 0 0 0 1 120.0 0.00 19,552 0 0 0 2 120.0 0.00 19,590 0 0 0 3 120.0 0.00 19,178 0 0 0 4 120.0 0.00 19,500 0 0 0 5 120.0 0.00 18,626 0 0 0 6 120.0 0.00 19,208 0 0 0 7 120.0 0.00 18,712 0 0 0 8 158.0 0.00 127,378 0 0 0 9 122.0 0.00 18,718 0 0 0 10 123.0 0.00 19,362 0 0 0 11 123.0 0.00 18,616 0 0 0 12 122.0 0.00 19,494 0 0 0 13 123.0 0.00 19,352 0 0 0 14 122.0 0.00 19,968 0 0 0 15 122.0 0.00 18,718 0 0 0 16 122.0 0.00 19,934 0 0 0 17 122.0 0.00 19,170 0 0 0 18 122.0 0.00 19,742 0 0 0 19 122.0 0.00 18,552 0 0 0 20 122.0 0.00 19,026 0 0 0 21 121.0 0.00 20,686 0 0 0 22 120.0 0.00 19,612 0 0 0 23 120.0 0.00 18,902 0 0 0 24 150.0 0.00 18,872 0 0 0 25 120.0 0.00 18,666 0 0 0 26 150.0 0.00 113,364 0 0 0 27 120.0 0.00 18,094 0 0 0 28 122.0 0.00 18,770 0 0 0 29 120.0 0.00 19,340 0 0 0 30 121.0 0.00 19,314 0 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: TEST Instance: test Snaps: 35 -37 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 401,408 0 401,408 1 46,129,152 0 41,943,040 46,129,152 2 46,129,152 838,860 41,943,040 46,129,152 3 46,129,152 0 41,943,040 46,129,152 4 46,129,152 0 41,943,040 46,129,152 5 46,129,152 838,860 41,943,040 46,129,152 6 41,934,848 0 41,943,040 41,934,848 7 46,129,152 0 41,943,040 46,129,152 8 46,129,152 0 41,943,040 46,129,152 9 46,129,152 0 41,943,040 46,129,152 10 46,129,152 0 41,943,040 46,129,152 11 46,129,152 0 41,943,040 46,129,152 12 46,129,152 0 41,943,040 46,129,152 13 46,129,152 838,860 41,943,040 46,129,152 14 46,129,152 0 41,943,040 46,129,152 15 41,934,848 0 41,943,040 41,934,848 16 46,129,152 0 41,943,040 46,129,152 17 46,129,152 0 41,943,040 46,129,152 18 46,129,152 0 41,943,040 46,129,152 19 46,129,152 0 41,943,040 46,129,152 20 46,129,152 0 41,943,040 46,129,152 21 46,129,152 0 41,943,040 46,129,152 22 46,129,152 0 41,943,040 46,129,152 23 46,129,152 0 41,943,040 46,129,152 24 46,129,152 0 41,943,040 46,129,152 25 46,129,152 0 41,943,040 46,129,152 26 46,129,152 0 41,943,040 46,129,152 27 46,129,152 0 41,943,040 46,129,152 28 46,129,152 0 41,943,040 46,129,152 29 46,129,152 0 41,943,040 46,129,152 30 41,934,848 0 41,943,040 41,934,848 ------------------------------------------------------------- Latch Activity for DB: TEST Instance: test Snaps: 35 -37 ->"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 Pct Get Get Slps NoWait NoWait Latch Name Requests Miss /Miss Requests Miss ----------------------------- -------------- ------ ------ ------------ ---- -- Token Manager 10 0.0 0 active checkpoint queue latch 1,084 0.0 0 cache buffer handles 6 0.0 0 cache buffers chains 1,768,809 0.0 305,629 0.0 cache buffers lru chain 6,595 0.0 303,720 0.0 channel handle pool latch 23 0.0 0 channel operations parent lat 35 0.0 0 checkpoint queue latch 25,598 0.0 0 dml lock allocation 5,982 0.0 0 enqueue hash chains 15,320 0.0 0 enqueues 21,678 0.0 0 event group latch 12 0.0 0 job_queue_processes parameter 30 0.0 0 ktm global data 6 0.0 0 latch wait list 1 0.0 1 0.0 library cache 263,214 0.0 1.0 0 library cache load lock 114 0.0 0 list of block allocation 3,426 0.0 0 messages 12,409 0.0 0 multiblock read objects 340 0.0 0 ncodef allocation latch 30 0.0 0 process allocation 12 0.0 12 0.0 process group creation 23 0.0 0 redo allocation 10,282 0.0 1.0 0 redo writing 8,512 0.0 1.0 0 row cache objects 15,911 0.0 0 sequence cache 21 0.0 0 session allocation 13,741 0.0 0 session idle bit 11,848 0.0 0 session switching 30 0.0 0 shared pool 16,782 0.0 0 sort extent pool 6 0.0 0 transaction allocation 5,389 0.0 0 transaction branch allocation 30 0.0 0 undo global data 6,014 0.0 0 user lock 10 0.0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: TEST Instance: test Snaps: 35 -37 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ---------- -- redo allocation 10,282 2 2 0/2/0/0/0 redo writing 8,512 2 2 0/2/0/0/0 library cache 263,214 1 1 0/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: TEST Instance: test Snaps: 35 -37 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- ----- -- library cache kglhdgn: child: 0 1 0 redo allocation kcrfwr: redo allocation 0 2 0 redo writing kcrfwi: after write 0 2 4 ------------------------------------------------------------- Dictionary Cache Stats for DB: TEST Instance: test Snaps: 35 -37 ->"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 Pct Cache Requests Miss Requests Miss Req Usage SGA ---------------------- ------------ ------ -------- ----- -------- ------ -- -- dc_constraints 0 0 0 33 89 dc_database_links 0 0 0 0 0 dc_files 0 0 0 0 0 dc_free_extents 426 0.0 0 0 429 25 dc_global_oids 0 0 0 0 0 dc_histogram_data 0 0 0 0 0 dc_histogram_data_valu 0 0 0 0 0 dc_histogram_defs 0 0 0 1 4 dc_object_ids 37 43.2 0 0 457 98 dc_objects 126 36.5 0 0 839 99 dc_outlines 0 0 0 0 0 dc_profiles 1,355 0.0 0 0 2 50 dc_rollback_segments 372 0.0 0 0 32 78 dc_segments 58 10.3 0 0 477 99 dc_sequence_grants 0 0 0 0 0 dc_sequences 0 0 0 4 27 dc_synonyms 17 76.5 0 0 32 82 dc_tablespace_quotas 0 0 0 2 9 dc_tablespaces 0 0 0 6 86 dc_used_extents 0 0 0 1,982 99 dc_user_grants 27 0.0 0 0 18 86 dc_usernames 49 0.0 0 0 6 29 dc_users 3,459 0.0 0 0 22 71 ifs_acl_cache_entries 0 0 0 0 0 ------------------------------------------------------------- Library Cache Activity for DB: TEST Instance: test Snaps: 35 -37 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- ------- - BODY 6,827 0.0 6,827 0.0 0 0 CLUSTER 0 0 0 0 INDEX 0 0 0 0 OBJECT 0 0 0 0 PIPE 0 0 0 0 SQL AREA 8,045 0.1 40,913 0.1 0 0 TABLE/PROCEDURE 292 19.9 7,323 2.4 0 0 TRIGGER 0 0 0 0 ------------------------------------------------------------- SGA Memory Summary for DB: TEST Instance: test Snaps: 35 -37 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 98,304,000 Fixed Size 75,804 Redo Buffers 1,581,056 Variable Size 608,772,096 ---------------- sum 708,732,956 ------------------------------------------------------------- SGA breakdown difference for DB: TEST Instance: test Snaps: 35 -37 Pool Name Begin value End value Difference ----------- ------------------------ -------------- -------------- --------- -- java pool free memory 32,768 32,768 0 large pool free memory 614,400 614,400 0 shared pool Checkpoint queue 295,056 295,056 0 shared pool DML locks 234,720 234,720 0 shared pool KGFF heap 10,464 10,464 0 shared pool KGK heap 17,548 17,548 0 shared pool KQLS heap 2,874,724 2,916,400 41,676 shared pool PL/SQL DIANA 3,110,060 3,110,060 0 shared pool PL/SQL MPCODE 4,138,864 4,149,472 10,608 shared pool PLS non-lib hp 2,096 2,096 0 shared pool State objects 533,360 533,360 0 shared pool branches 117,600 117,600 0 shared pool db_block_buffers 1,632,000 1,632,000 0 shared pool db_block_hash_buckets 323,080 323,080 0 shared pool db_files 370,988 370,988 0 shared pool db_handles 200,000 200,000 0 shared pool dictionary cache 1,638,092 1,673,148 35,056 shared pool enqueue_resources 216,000 216,000 0 shared pool event statistics per ses 1,530,800 1,530,800 0 shared pool fixed allocation callbac 320 320 0 shared pool free memory 580,087,168 579,494,436 -592,732 shared pool ktlbk state objects 209,292 209,292 0 shared pool library cache 4,401,368 4,533,396 132,028 shared pool long op statistics array 110,000 110,000 0 shared pool message pool freequeue 124,552 124,552 0 shared pool messages 70,400 70,400 0 shared pool miscellaneous 831,432 835,564 4,132 shared pool processes 323,200 323,200 0 shared pool sessions 959,420 959,420 0 shared pool simulator trace entries 80,000 80,000 0 shared pool sql area 3,148,628 3,516,500 367,872 shared pool table columns 42,468 43,028 560 shared pool table definiti 1,920 2,720 800 shared pool transactions 436,188 436,188 0 shared pool trigger inform 300 300 0 shared pool view columns d 31,428 31,428 0 db_block_buffers 98,304,000 98,304,000 0 fixed_sga 75,804 75,804 0 log_buffer 1,572,864 1,572,864 0 ------------------------------------------------------------- init.ora Parameters for DB: TEST Instance: test Snaps: 35 -37 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- ------------ -- background_dump_dest e:\oradb\test\bdump compatible 8.1.7.4.1 control_files E:\oradb\test\control01.ctl db_block_buffers 12000 db_block_checking TRUE db_block_lru_latches 4 db_block_size 8192 db_file_multiblock_read_count 94 db_files 1024 db_name TEST distributed_transactions 10 global_names TRUE instance_name TEST java_pool_size 32768 job_queue_interval 10 job_queue_processes 3 large_pool_size 614400 log_archive_dest_1 location=e:\oradb\test\arch log_archive_format arch%S.arc log_archive_start TRUE log_buffer 1572864 log_checkpoint_interval 100000 log_checkpoint_timeout 10 max_dump_file_size 10240 max_enabled_roles 120 nls_date_format YYYY/MM/DD open_cursors 600 optimizer_mode RULE oracle_trace_collection_name os_authent_prefix parallel_max_servers 5 processes 400 remote_login_passwordfile SHARED rollback_segments rb1, rb2, rb3, rb4, rb5, rb6, rb7 service_names TEST session_cached_cursors 2097152 shared_pool_size 600000000 sort_area_size 1572864 timed_statistics FALSE user_dump_dest e:\oradb\test\bdump ------------------------------------------------------------- End of ReportReceived on Fri May 28 2004 - 02:41:51 CDT
![]() |
![]() |