Some concepts about Shared pool, lock and latches

In an interview, I was asked a question about how Oracle protects the shared pool if multiple users need to access and modify same memory location. (the exact question I can not recall, but it is about locks/latches). I failed to explain well.

Here are some reading notes about this topic.

What is "library cache lock"?

Oracle document describes "library cache lock" as one of the wait events as follows:

This event controls the concurrency between clients of the library cache.
It acquires a lock on the object handle so that either:
- One client can prevent other clients from accessing the same object
- The client can maintain a dependency for a long time which does
not allow another client to change the object

This lock is also obtained to locate an object in the library cache.


Library cache
* includes
- shared SQL areas
- private SQL areas
- PL/SQL procedures and packages
- Control structures: locks and library cache handles

Each SQL statment
* represented by
- a shared SQL area
- a private SQL area

Shared SQL area
* contains
- parse tree
- execution plan

Private SQL Area
* divided into
- persistent area: e.g. bind information
- run-time area
* located
- PGA or
- SGA (in the case of shared server)


Here is an article explaining the Oracle latch: http://www.pafumi.net/Oracle_Latches.html


Shared pool latch and libary cache latch

In the v$latch view, there are latch names such as shared pool and library cache:

Someone explained:

- Shared pool latch:

Shared pool latch is acquired to request/release space from the
shared pool free memory area and released immediately after that.

- Library cache latch

Library cache latch is held while searching for a parent cursor and
subsequent child cursor with similar attributes with parent cursor
pinned.

RAC: misconfigured or faulty interconnect

This note is to document the symptoms resulted from misconfigured or faulty Interconnect. The example given is based on an Oracle Openworld presentation "RAC Performances Experts Reveal All", which I found on web.

For RAC database one of the common problems is misconfigured or faulty Interconnect. This casued "Lost Blocks" problem, whose symptom can be identified by following means:

(1) ifconfig indicating NIC receive errors

ifconfig -a:

eth0 Link encap:Ethernet  HWaddr 00:0B:DB:4B:A2:04
     inet addr:130.35.25.110  Bcast:130.35.27.255  Mask:255.255.252.0
     UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
     RX packets:21721236 errors:135 dropped:0 overruns:0 frame:95
     TX packets:273120 errors:0 dropped:0 overruns:0 carrier:0

Notice the errors:135

Note: ifconfig command help: http://www.computerhope.com/unix/uifconfi.htm

(2) netstat showing ip packet ressembly failures

netstat -s

Ip:

  84884742 total packets received
  1201 fragments dropped after timeout
  3384 packet reassembles failed

(3) AWR report : "gc cr block lost" should never be there

Top 5 Timed Events                             
~~~~~~~~~~~~~~~~~~         

                                   %Total
                               Avg   wait  Call
Event                Waits  Time(s)  (ms)  Time    Wait Class
-----------------------------------------------------------------------
log file sync      286,038  49,872    174  41.7     Commit
gc buffer busy     177,315  29,021    164  24.3    Cluster
gc cr block busy   110,348   5,703     52   4.8    Cluster
gc cr block lost     4,272   4,953   1159   4.1    Cluster
cr request retry     6,316   4,668    739   3.9      Other

Below is from a Metalink Doc about check interconnect:

Q: How do I check for network problems on my interconect?
A:
1. Confirm that full duplex is set correctly for all interconnect links on all interfaces on both ends. Do not rely on auto negotiation.

2. ifconfig -a will give you an indication of collisions/errors/overuns and dropped packets

3. netstat -s will give you a listing of receive packet discards, fragmentation and reassembly errors for IP and UDP.

4. Set the udp buffers correctly

5. Check your cabling

Note: If you are seeing issues with RAC, RAC uses UDP as the protocol. Oracle Clusterware uses TCP/IP.

Create ASM instance and Diskgroups with Oracle 11g on Windows XP PC

Purpose:
Manually create an ASM instances and use blank files to simulate ASM disks for creating ASM diskgroups

Environment:
Oracle 11g on Window XP

Referenece
http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_22.shtml


Steps

1. Create an instance parameter file

----- file: c:\app\oracle\admin\+ASM\init.ora ----------
instance_type=asm
--------------------------------------------------------

Note: This file only contains one line

2. For windows, manually create a new Windows Service

C:\> oradim -new -asmsid +ASM -syspwd oracle

3. Starting the ASM instance

set ORACLE_SID=+ASM

C:\> sqlplus "/ as sysdba"

SQL&> startup pfile='c:\app\oracle\admin\+ASM\init.ora'
ASM instance started

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

4. create spfile from pfile

SQL> create spfile from pfile='c:\app\oracle\admin\+ASM\pfile\init.ora';

File created.

shutdown/startup (using spfile)

5. Modify init.ora parameter:

SQL> alter system set "_asm_allow_only_raw_disks"=false scope=spfile;
SQL> alter system set asm_diskstring='C:\asmdisks\_file*' scope=both;

6. Create files for ASM disks:

asmtool -create c:\asmdisks\_file_disk10 100
asmtool -create c:\asmdisks\_file_disk11 100
asmtool -create c:\asmdisks\_file_disk12 100
asmtool -create c:\asmdisks\_file_disk13 100


7. Create ASM diskgroups

e.g.

CREATE DISKGROUP orcl_dg1 external REDUNDANCY
DISK 'c:\asmdisks\_file_disk12',
     'c:\asmdisks\_file_disk13';


idle> @diskgroup_show.sql
idle> col name format a20
idle> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME                 STATE       TYPE     TOTAL_MB    FREE_MB
-------------------- ----------- ------ ---------- ----------
ORCL_DG2             MOUNTED     EXTERN        200        148
ORCL_DG1             MOUNTED     EXTERN        200        148


idle> @disk_discover.sql
idle> col path format a30
idle> SELECT name, mount_status, header_status, state, path
  2  FROM v$asm_disk
  3  order by name;

NAME                 MOUNT_S HEADER_STATU STATE    PATH
-------------------- ------- ------------ -------- ------------------------------
ORCL_DG1_0000        CACHED  MEMBER       NORMAL   C:\ASMDISKS\_FILE_DISK12
ORCL_DG1_0001        CACHED  MEMBER       NORMAL   C:\ASMDISKS\_FILE_DISK13
ORCL_DG2_0000        CACHED  MEMBER       NORMAL   C:\ASMDISKS\_FILE_DISK10
ORCL_DG2_0001        CACHED  MEMBER       NORMAL   C:\ASMDISKS\_FILE_DISK11

8. Using ASM disk group - example

dennis@TESTDB11> @cr_asm_ts1
dennis@TESTDB11> set echo on
dennis@TESTDB11> spool cr_asm_ts1
dennis@TESTDB11>
dennis@TESTDB11> drop tablespace asm_ts1 including contents and datafiles;

Tablespace dropped.

dennis@TESTDB11>
dennis@TESTDB11> create tablespace asm_ts1 datafile '+ORCL_DG1' size 20M;

Tablespace created.

dennis@TESTDB11>
dennis@TESTDB11> select tablespace_name, file_name from dba_data_files
  2  where tablespace_name like 'ASM%';

TABLESPACE_NAME
------------------------------
FILE_NAME
----------------------------------------------------------------------------------
----
ASM_TS1
+ORCL_DG1/testdb11g/datafile/asm_ts1.256.658928691


dennis@TESTDB11>
dennis@TESTDB11> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-00942: table or view does not exist


dennis@TESTDB11>
dennis@TESTDB11> create table t1 tablespace asm_ts1
  2  as
  3  select     * from all_objects
  4  where rownum < 10;

dennis@TESTDB11> select table_name, tablespace_name from user_tables  
2  where table_name='T1'; 

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             ASM_TS1