Oracle on ASM

I discovered an interesting little by product of running Oracle on ASM.. Our SGA was sized a little small on a non-production box.  All of a sudden the Users started seeing some I/O errors.

Error: Selecting clients for minimum billing ORA-01115: IO error reading block
from file (block # )


ORA-01110: data file 49: '+DATA/orcl/data_128k_dt01.dbf'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk

Of course I looked at the sytem logs to find out if there was the dreaded corruption.. but nothing shows up.. I look in the alert log and I see

ORA-04031: unable to allocate 3240 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","FileOpenBlock")



ERROR: error ORA-4031 caught in ASM I/O path

 
 
Hmm.. looks like Oracle not being able to get to the ASM path causes I/O error messages.

SCAN (single client access name)

If you didn't know it, there is a new feature in 11.2 Grid called SCAN.  Here is a link to a little more information http://www.orafaq.com/node/2369 .

The reason I thought I should mention this, is that scan is a new concept you have to deal with when you upgrade/install 11.2 grid.. There is no getting around it in the installation.  It is possible to disable it however once you install it.

My suggestion on using it, is to move slowly.. There is still a lot of old clients out there that have hard time with scan, so you might find that your client won't connect the "new way"..  It is also very persnickity,.. I've seen some installations where the DNS resolution (files,dns), has thrown it off too.. For a new application, you can work through issues as you get ready to deploy, but for existing apps I would recommend you plan some time in your upgrade schedule to work through issues (or plan on disabling it).

Just  a word to the wise :)

Concurrency and parallelism

I've had a lot of discussions with some very "seasoned" professions on how to handle a high level concurrency. Most of these professions point to the new features of 11gr2 and and parallization..

True 11gr2 has added a new way of handling degree of parallization.

There are some new parameters

parallel_degree_policy
PARALLEL_MIN_TIME_THRESHOLD

These control how parallism is handled.. They can be used to actually create a funnel to ensure the system isn't flooded.

The problem is all this, is that parallelism has a price.. Take a small efficient query, and turn on these parameters.. Guess what happens when you ramp up and run 500 of the same query concurrently ? You see much lower throughput (I've seen as much as 10x lower throughput). Why ?? The overhead of parallel query can be quite high, and can consume more time than even CPU in your AWR report.

Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: JX - SQL statement queue            71       7,815 1.E+05   99.3 Scheduler 
DB CPU                                               96           1.2           
PX Nsq: PQ load info query               46           9    201     .1 Other     
enq: RD - RAC load                       90           2     20     .0 Other     
PX Deq: reap credit                 152,105           1      0     .0 Other

The moral of the story is, parallism is good for longer queries.. For shorter queries your milege may vary