- KGH: NO ACCESS – Buffer cache inside streams pool too! | www.oaktable.net
- ORA-04031 AND ASMM
- KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!
- ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation [ID 1127833.1]
- Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] [ID 801787.1]
Wednesday, April 27, 2011
KGH: NO ACCESS
Finally there is information on the KGH: NO ACCESS shared pool component appearing on BLOGs and at Oracle Support.
Friday, April 15, 2011
Monday, August 23, 2010
Oracle Block Remastering
Here's good arcticle on dynamic remastering.
Remasting Technique
Remastering granularity
- Oracle 10g-11g. Block range (128 blocks in a range)
Queries:
select kj.*, le.le_Addr from ( select kjblname, kjblname2, kjblowner, kjblmaster, kjbllockp, substr ( kjblname2, instr(kjblname2,',')+1, instr(kjblname2,',',1,2)-instr(kjblname2,',',1,1)-1)/65536 fl, substr ( kjblname2, 1, instr(kjblname2,',')-1) blk from x$kjbl ) kj, x$le le where le.le_kjbl = kj.kjbllockp order by le.le_addr /
select * from x$object_affinity_statistics
/
select * from v$gcspfmaster_info
/
- With object remastering feature, if an object is accessed by an instance aggressively, then that instance will become the master of the object reducing gc remote grants improving performance of the application. In the prior sentence, I used the word “accessed”, but it is a loose term, and the correct term is if the instance is requesting much BL locks on an object, then that object can be remastered.
- Instance GRD is frozen during reconfiguration and in a very busy instances, this can take many seconds leading to instance freeze for several seconds.
Internal Parameters
- _gc_affinity_limit - the number of "opens" before an object is considered for remastering (defaults to 50)
- _gc_affinity_time - how often the queue is checked for remastering (defaults to 10 minutes)
- _gc_affinity_minimum - minimum amount of dynamic affinity activity per minute to be a candidate for remastering (defaults to 600/minute/cpu)
- _gc_undo_affinity - controls whether dynamic undo remastering is enabled
Evidence of remastering performance problems
- Wait events: "gcs drm freeze", "gc remaster", "gcs freeze"
- Log entries at the same time "DRM start"
Wednesday, July 14, 2010
Facebook Defaults
I thought when I first joined facebook that the default privacy rules were quite good. Look again now (2010-07-14) I see that they are not very private at all. So what "should" they be, especially for kids? Here's a great article. Basically, almost everything should be "Friends Only".
Tuesday, June 1, 2010
Oracle Clusterware Failures: Useful Logs
To determine why a node failed, try the following clusterware logs (in order of usefulness):
- alert log
- ocssd log
- evm log
Oracle Clusterware Parameters: misscount, disktimeout, reboottime
Clusterware timeout parameters:
- misscount - It represents maximum time in seconds that, a heartbeat can be missed before entering into a cluster reconfiguration to evict the node.
- disktimeout - It is the maximum amount of time allowed for a voting file I/O to complete; if this time is exceeded the voting disk will be marked as offline.
- reboottime - It is the amount of time allowed for a node to complete a reboot after the CSS daemon has been evicted.
- misscount = 60 seconds
- disktimeout = 200 seconds
- reboottime = 3 seconds
- crsctl get css misscount ---------- to check misscount value
- crsctl get css disktimeout --------- to check disktimeout value
- crsctl get css reboottime ---------- to check reboottime value
- crsctl set css misscount 120 --------- to set misscount to 120 seconds
- crsctl set css disktimeout 200 ------- to set disktimeout to 200 seconds
- crsctl set css reboottime 3 ----------- to set reboottime to 3 seconds
lssnmNMInitialize: misscount set to (30) clssnmNMInitialize: Network heartbeat thresholds are: impending reconfig 15000 ms, reconfig start (misscount) 30000 ms clssgmInitCMInfo: Wait for remote node termination set to 13 seconds clssnmNMInitialize: misscount set to (60), impending reconfig threshold set to (56000) clssnmNMInitialize: diskShortTimeout set to (57000)ms clssnmNMInitialize: diskLongTimeout set to (200000)ms clssnmHandleUpdate: diskTimeout set to (200000)ms
Friday, November 27, 2009
[Oracle] Consistent gets from cache (fastpath)
Looking for information on "consistent gets from cache (fastpath)", google found this great blog entry.
10g:
THE Q U A D R O BLOG: Consistent gets from cache (fastpath)
In cases I've seen the number of consistent gets between 10g and 11g are the same. This equates to a comment by Roderick in the above post.
In my scenario, there were two identical plans and an identical number of consistent gets. In 11g it ran more than twice as fast.FYI. The reduction in consistent gets is actually due to a separate internal optimization. The fastpath gets allows those fewer consistent gets to use a shorter code path.
10g:
Plan hash value: 4121601496 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |* 1 | FILTER | | 142 | | | | | 100K|00:02:32.29 | 20M| 57 | | 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_NODE_HISTORY | 142 | 2 | 98 | 5 (0)| 00:00:01 | 2840K|00:00:11.74 | 2777K| 0 | |* 3 | INDEX RANGE SCAN | P_CUSTOMER_NODE_HISTORY | 142 | 2 | | 2 (0)| 00:00:01 | 2840K|00:00:02.84 | 10542 | 0 | |* 4 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INSTANCE_HISTORY | 2840K| 1 | 30 | 199 (3)| 00:00:01 | 100K|00:02:07.75 | 17M| 57 | | 5 | AND-EQUAL | | 2840K| | | | | 100K|00:01:59.76 | 17M| 54 | |* 6 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_CN | 2840K| 6 | | 3 (0)| 00:00:01 | 3118K|00:00:26.55 | 8998K| 0 | |* 7 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_PRD | 2840K| 72000 | | 195 (3)| 00:00:01 | 347K|00:01:24.34 | 8844K| 54 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ consistent gets from cache 23,100,14611g:
Plan hash value: 4121601496 --------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 142 | | | 228 (100)| | 100K|00:01:08.66 | 20M| |* 1 | FILTER | | 142 | | | | | 100K|00:01:08.66 | 20M| | 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_NODE_HISTORY | 142 | 2 | 98 | 5 (0)| 00:00:01 | 2840K|00:00:09.16 | 2796K| |* 3 | INDEX RANGE SCAN | P_CUSTOMER_NODE_HISTORY | 142 | 2 | | 2 (0)| 00:00:01 | 2840K|00:00:01.22 | 10684 | |* 4 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INSTANCE_HISTORY | 2840K| 1 | 30 | 199 (3)| 00:00:02 | 100K|00:00:47.42 | 17M| | 5 | AND-EQUAL | | 2840K| | | | | 100K|00:00:45.24 | 17M| |* 6 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_CN | 2840K| 6 | | 3 (0)| 00:00:01 | 3118K|00:00:20.43 | 8998K| |* 7 | INDEX RANGE SCAN | I_PRODUCT_INSTANCE_HISTORY_PRD | 2840K| 72000 | | 195 (3)| 00:00:01 | 347K|00:00:21.17 | 8854K| --------------------------------------------------------------------------------------------------------------------------------------------------------- consistent gets from cache 23,012,794 consistent gets from cache (fastp 10,022,007
Subscribe to:
Posts (Atom)
