| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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 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
2,028 2,028 1.0 2016510618 SELECT USER FROM SYS.DUAL
2,027 1 0.0 1272233705
SELECT 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 187676852
alter 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 47694755
SELECT * 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 Waits
Wt(ms)
Tablespace Filename
------------------------ ---------------------------------------------------
-
Av Av Av Av Buffer Av
Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(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 4
0.0
SYSTEM D:\ORADB\TEST\SYSTEM01.DBF
306 0 0.8 7.0 407 0 318
0.0
Free Write
Buffer
Buffer Consistent Physical Physical Buffer Complete
Busy
P Gets Gets Reads Writes Waits Waits
Waits
--
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 Report
Received on Fri May 28 2004 - 02:41:51 CDT
 |
 |