BUG: Veritas, Solaris, lgwr regulary stalls for 3 seconds

Bug 10318123 – lgwr regularly stalls for 3 seconds at a time [ID 10318123.8]

Oracle proposed resolution: “An upgrade in veritas library from 5.0 to 5.1 (which includes ODM), resolved the LGWR issue.”

BUG: 11gR2 performance/wrong results/etc bugs, Generic, Solaris, AIX

A good metalink note is here:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1320966.1

BUG: Solaris, memory, have to wait between shutdown and startup

Found it finally:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=790205.1

UNABLE TO START INSTANCE WITH LARGE SGA ORA-27102 SVR4 ERROR: 22: INVALID ARGUMENT [ID 790205.1]\

https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=1644977629090275&id=460424.1&_afrWindowMode=0&_adf.ctrl-state=cw8egzrhl_391

Solaris 10 memory management conflicts with Automatic PGA Memory Management [ID 460424.1]

Citation (this is what I do):
“…Sleep 5 minutes between the shutdown abort and startup (this doesn’t always work) …”

Workaround: “…Essentially the /etc/system settings disable Large Page Out Of Box (LPOOB) support which was enabled by default with Solaris 10…”

One more:
“Solaris 10 memory management conflicts with Automatic PGA Memory Management [ID 460424.1]“

BUG: MSQSUB When Select From V$SQL_PLAN

Workaround does not crash but “some information will be missing”:

ORA-07445 [MSQSUB()+32] When Select From V$SQL_PLAN [ID 791225.1]

Bug 7022234 is Fixed in 11.2
Patches exist for it on most platforms on top of 10.2.0.4 at the moment of writing this note

1- apply the patch 7022234 if available
2- If the patch is not available you may use the following workaround
alter session set “_cursor_plan_unparse_enabled”=false;

Reverse Engineering: consistent reads, multi versioning, ITL, ora-1555, row level locks, concurency

Here bellow is a draft list of resources and thoughts about how Oracle implements multi versioning.

>http://arup.blogspot.com/2011/01/more-on-interested-transaction-lists.html

But I had to add this to

http://avdeo.com/2008/06/16/interested-transaction-list-itl/

to

https://richardfoote.wordpress.com/2010/07/20/index-block-dump-block-header-part-i-editions-of-you/

and

https://richardfoote.wordpress.com/2010/07/28/index-block-dump-block-header-part-ii-and-read-consistency-i-cant-read/

to Jonatahn Lewis comments at:

http://forums.oracle.com/forums/thread.jspa?messageID=3725660

Jonathan is probably the first in the above mentioned links to talk about COMMIT scn not just CHANGE scn – who cares about change SCN anyway ?
But he makes one interesting statement:

“When all the ITL entries show SCNs no later than your query SCN the clone is read-consistent and the data can be used by the query.”

I’d like to know what was ment by that. My interpretation is:

one can reconstruct a consistent read of a particular row at query start SCN. Or one can reconstruct a consistent read of the whole block at query start SCN.

As oracle operates “in blocks” then I assume this is how oracle reconstructs the consistent read of a block: ALL ITL’s have to be delt with to have every row in a block rollbacked to commit SCN just before query start SCN.

and finally

http://forums.oracle.com/forums/thread.jspa?threadID=521913

Mark J Bobak tells what is really needed to complete the picture:

“Then, at the row level, in the row directory, there’s a lock byte. The value of the lock byte will be either 0, if there is no lock on the row, or it will be the number of the slot in the ITL that corresponds to the transaction that’s making a change, or just holding a lock, on that particular row. So, the locked row in the block points to the ITL slot, and the ITL slot points back at the undo segment/slot/sequence. “

Here, IMHO, the structure is complete:

Row Lock byte points to ITL, ITL points to transaction commit SCN and undo record.

Then everything adds up: row level locking, transaction isolation, all ITL’s traversal.

The block oriented nature of oracle is explained by Tom Kyte:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:27330770500351

“o we cache blocks
o we read blocks
o blocks have transaction tables on them, this is what drives the multi-versioning

relational databases tend to do things “to blocks”, it is basically the way they work.

We do not multi-version ROWS.
We multi-version BLOCKS.
“

Quite a list to have somehow complete picture , isn’t it ?

The last but not least:
Tanel Poder:
“If you can wait until November, then Jonathan Lewis’es new book will be the best book ever to explain this “
Let’s wait and see this: http://jonathanlewis.wordpress.com/2011/06/17/author-author/

Troubleshooting series: time wasted on app side for connections to database

The requirement is to finish transactions in 3 seconds. But during nightly batch load the requirement is violated.

Database ASH, AWR, redo Log Miner, v$event_histogram snapshots, even app logs show transactions are completed in 3 seconds.

Where is the time wasted?

It is wasted in obtaining connection from java connection pool.

Some indications can be found v$event_histogram snapshots: “os thread startup” event pops out occasionally during the problem period. But do not expect it to pop out in any aggregated report. Application pool is not requesting a connection that often after all.

Another place is Oracle grid “user logon time (msec)” metric. That one is said to be “time the agent takes to make a connection to the database”. Meaning Oracle grid actually performs a connection test and measures the time. It goes up to 2 seconds in that particular case but well, connections do take time. Starting a process is like building a house. Even worse.

However, the best way to diagnose it is to understand how application actually works. App administrator guessed the reason right away after presented with the fact that neither oracle nor app logs show excessive time spent in the database.

Bug series: 11g instance crash. Resource Manager switched off

Database version: 11.2.0.2

Instance would crash immediately after instance bounce with ORA-7445[ksldclt_cln]

Metalink note: Bug 9903826 – ORA-7445 [kcbsacc] and PMON crashing with ORA-7445 [kcbsslc] Causing Instance to Terminate [ID 9903826.8]

Solution _resource_manager_always_off = TRUE worked.

One more optional component switched off. Good for it.