Oracle SGA memory management on Linux

1. SGA auto sizing is not that dynamic. At startup, the oracle processes allocate memory up to the sga_max_size. Oracle keeps the unused sga memory under “Free SGA Memory Available”. For example, if we set sga_max_size = 600M and sga_target = 500M, then query v$sgainfo and we find that “Free SGA Memory Available” is 100M. I could not find a reason not to set sga_target equals to sga_max_size. (Well, only for very special needs.)
   
2. It is hard to know exactly how much memory is allocated to an Oracle instance at OS level. On Linux, you can use “ps aux” to find out how much memory is allocated to an oracle process in bytes by VSZ column. VSZ stands for “virtual memory size” and it quite misleading. In Linux, virtual memory is a memory map that can be point to physical memory, or a swap disk space, or a memory to be allocated. So it is more like a total memory allocated for the process. Secondly, the VSZ includes the memory dedicated to the process and shared memory with other processes as well. This is why the sum of VSZ for all processes from an Oracle instance is much larger that SGA and PGA combined because shared memory is counted multiple times. To find out what memory is shared and what is private, you can use “pmap –d ”. A sample output is as following:
   

   Address	Kbytes	Mode	Offset	Mapping
   ….
   0000003b27e13000	8	rw---	0000003b27e13000	[ anon ]
   0000003b2c500000	80	r-x--	0000000000000000	libnsl-2.3.4.so
   0000003b2c514000	1020	-----	0000000000014000	libnsl-2.3.4.so
   0000003b2c613000	8	rw---	0000000000013000	libnsl-2.3.4.so
   0000003b2c615000	8	rw---	0000003b2c615000	[ anon ]
   0000007fbfffa000	24	rwx--	0000007fbfffa000	[ stack ]
   ffffffffff600000	8192	-----	0000000000000000	[ anon ]

   
   mapped: 772456K writeable/private: 6824K shared: 620548K
   As it shows, pmap lists memory address, memory siz of all memory segments for a process. It also shows private memory and shared memory in last line. It is possible to write a program to find the total memory allocation for a set of processes based the memory address and size of pmap output from every single process in the set.
   
3. Not all SGA memory is in physical memory. Back to “ps aux” command and “RSS” column is for “resident set size” in KB. In English, it is the physical memory the process used, including shared and not shared. The following is the output of pmon process of an oracle instance:
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	10108	764264	17696	ora_pmon_orcl

   
   The data indicates that only 2% of the memory is actually physical memory. Because the majority of memory for an oracle process is shared, the percentage of physical memory out of total memory for other oracle processes is similar. I still do not have a way to know what Oracle memory structure is on physical memory or what percentage of it is physical memory. Normally, it is not a problem since Linux OS would move the active memory into physical memory. But in some case, for example, server is doing swapping, virtual machine is used, or high performance system that even first time access of a stale memory page needs to be fast, DBA may needs to pin all Oracle memory into not swappable physical memory. If certain Oracle memory structure is not in physical memory, such as library cache, the database is unusable because of performance.
   
4. Pin SGA into physical memory. Oracle init parameter lock_sga can be set to true and set the ulimit parameter memlock to greater than sga_max_size. Otherwise, the instance failed to start with ORA-27102: out of memory. Let’s check the process again and interestingly, the physical memory allocated to oracle process is still very little.
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	20420	764264	17696	ora_pmon_orcl

   
   init parameter lock_sga is OS dependent and looks like Linux is not supporting lock_sga to pin SGA into physical memory. Even the status of the shared memory is locked by “ipcs –m”. Next, let us try to set pre_page_sga = true. Run “ps aux” and see how much physical memory is used
   
   

   USER	PID	VSZ	RSS	COMMAND
   oracle	12278	764264	634348	ora_pmon_orcl

   
   The RSS is about 600M so sga is in physical memory now. However, sga in physical memory can be paged out by OS as needed. Again, the memory locking is not working in Linux even the flag is set.

Solid State Disk as Oracle storage

I recently completed a cool project: implementing 11g release 2 RAC on Solid State Drive (SSD). SSD is still too expensive except for the most advanced systems. With $10/gigabytes price tag, it is about 20 times more expensive than Hard Disk Drive (HDD). But it is fast, quit, more durable and greener with 90% less power consumption. It is projected that SSD will cost only few dollars per gigabytes within two years and likely will replace HDD like USB flash memory did to floppy disk.

In the blog, I present the IO testing result of SSD against SAN using ORION (Oracle IO Calibration tool). ORION result is more closely mimicking of Oracle database IO characteristics.

Large read throughput for SSD is at 116 MBPS (MegaBytes Per Second) vs. 40 MBPS for SAN. The transfer rate is much less than theoretical transfer rate of SDD and SAN but it is likely the throughput Oracle could achieve for such hardware. It is worth to note that SSD is only mirrored by not stripped, and SAN is mirrored and stripped. So it is reasonable to assume SDD throughput would be few times faster if it were in RAID 01 configuration. I use the same configuration of SAN and SDD of large read for all other tests as well. The random small read throughput for SAN is 1.8 MBPS or 225 IOPS (IO Per Second). The random small read throughput for SSD is 72 MBPS or 9000 IOPS, which is 40 times faster than HDD. Because SSD removes the latency to move the disk head to new disk sections, the random small reads is about 60% the speed of large read for SSD. The data suggests SDD is great for OLTP system and for control file and redo logfile of Oracle database.

Large write throughput for SAN is 79 MBPS and 160 MBPS for SDD. Small random write for SAN is 22 MBPS or 2750 IOPS. Small random write for SSD is 79 MBPS or 9875 IOPS. It is interesting to see that write IO performance is better than read for both SAN and SSD. My guess is that storage write forward cache play significant role here and it is hard to measure true disk write speed. However, it is clear that SDD outperformed SAN once again.

First Impression of Oracle 11gR2 RAC

11g Release 2 RAC has some major changes from 11g Release 1.

For starter, ASM and Clusterware are combined into single Oracle binary home called Oracle grid infrastructure. By default, the voting disks and OCR are in ASM diskgroup instead of raw devices recommended in previous releases. Such change contradicts the concept that ASM is on top of clusterware for previous releases and raise question such as, if ASM is crashed, does clusterware service dies as well because voting disks and OCR are not accessible? Well, the clusterware can access voting disks directly without going through ASM, so it will bring up ASM automatically after it is crashed. Restore an OCR is more difficult and requires to shutdown clusterware on all nodes, then start clusterware on one node exclusively with ASM online. Fortunately, OCR is not easy to corrupt because it is in a normal redundancy diskgroup or high redundancy diskgroup.

Another new feature of 11g Release 2 is SCAN (Single Client Access Name) which binds round-robin dynamic DNS to RAC nodes. It provides easy way of load balance and failover for any client connection. However, it could be a challenge to setup DNS and I wish the installation can have an option not to use SCAN for RAC.

Overall, release 2 RAC is easier to install and deinstall than that of earlier release and more stable because of cluster time service. It also has better scalability than earlier release because the performance improvement of inter-node communication.

Oracle rolling upgrade

Oracle rolling upgrade allows the Oracle RAC configuration to apply patch or upgrade one node at one time, so the RAC cluster is still online during the patch or upgrade. Currently, most new releases of CPU patches and upgrades support rolling upgrade. Rolling upgrade works very well for software upgrade but many patches and upgrades need to run database script in exclusive mode. Because RAC has only one database image so DBAs have to use logical standby database to do the rolling upgrade, which is complicated and costly in term of Oracle license and hardware. Until one day, Oracle can extend Edition-based Redefinition new feature in 11g R2 to sys tables and data dictionary, rolling upgrade will become more common practice.

Oracle row level locking

We all know that Oracle provides row level lock for database transaction. I’d like to blog how it is done in details.

Let us start with creating a test table:

SQL> create table test (name varchar2(100)) tablespace users;

Table created.

SQL> insert into test values ('Mike');

1 row created.

SQL> insert into test values ('Steve');

1 row created.

SQL> insert into test values ('Tom');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid, name from test;

ROWID NAME

------------------ --------------------

AAANzsAAEAAAAPHAAA Mike

AAANzsAAEAAAAPHAAB Steve

AAANzsAAEAAAAPHAAC Tom

SQL> select dbms_rowid.rowid_relative_fno('AAANzsAAEAAAAPHAAA') file_no from dual;

FILE_NO

----------

4

SQL> select dbms_rowid.rowid_block_number('AAANzsAAEAAAAPHAAA') block_no from dual;

BLOCK_NO

----------

967

SQL> alter system dump datafile 4 block 967;

System altered.

Now let us do some updates on the test table

SQL> update test set name = 'Ed' where name = 'Mike';

1 row updated.

The update QSL should start a transaction and place row level lock on the updated row. To see the locks, we query V$LOCK and V$TRANSACTION:

SQL> select ADDR, sid, type, id1, id2, lmode from v$lock where sid = 149;

ADDR SID TY ID1 ID2 LMODE

---------------- ---------- -- ---------- ---------- ----------

0000000087A8D950 149 TM 56556 0 3

0000000087AAD388 149 TX 655364 1172 6

SQL> select addr, xidusn, xidslot, xidsqn from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN

---------------- ---------- ---------- ----------

0000000087AAD388 10 4 1172

From V$LOCK, we get table level DML lock TM with lock mode 3, which is row exclusive lock. ID1 56556 is the object id of the locked table, which is “TEST”. Other lock related to the update session is transaction lock TX with ID1 655364, which is decimal value of undo segment number and slot number, and ID2 1172, which is transaction sequence number. Convert 655364 to hex is 0xA0004 and it means undo segment 10 (0xA = 10), slot 4.

From V$TRANSACTION with same ADDR (0000000087AAD388), we got XIDUSN (Undo Segment Number) 10, XIDSLOT (Undo segment slot) 4, and XIDSQN (transaction sequence number) 1172. All matches well with V$LOCK.

Now we do another update and exam the V$LOCK and V$TRQANSACTION again.

SQL> update test set name = 'David' where name = 'Steve';

1 row updated.

SQL> select ADDR, sid, type, id1, id2, lmode from v$lock where sid = 149;

ADDR SID TY ID1 ID2 LMODE

---------------- ---------- -- ---------- ---------- ----------

0000000087A8D950 149 TM 56556 0 3

0000000087AAD388 149 TX 655364 1172 6

SQL> select addr, xidusn, xidslot, xidsqn from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN

---------------- ---------- ---------- ----------

0000000087AAD388 10 4 1172

Well, we still find only one TM table lock with row exclusive lock mode for this session even we have two row level locks, one for each row. In fact, TM lock with row exclusive lock mode means that there is a shared lock on the table and some rows in the table have exclusive lock. Oracle does not provide a system interface to view the row level locking. To view the Oracle row level locking, we have to go deep into Oracle data block.

Do the data block dump again and we find the interesting output:

0067F39D0 00C07F43 0000000B 00000000 022CC506 [C.............,.]

0067F39E0 64450201 0301002C 2C6D6F54 44050102 [..Ed,...Tom,...D]

0067F39F0 64697661 0401002C 656B694D C6900601 [avid,...Mike....]

……

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 ---- 2 fsc 0x0000.00000000

-----

tab 0, row 0, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

The second section is Interested Transaction List (Itl) and has two entries, 01 and 02. The Flag for it is ---- and that means a active transaction. Lck is 2, means two row level locking in this block. The Xid (Transaction ID) is 0x000a.004.00000494 and it means undo segment A (Undo segment 10 in hex), slot 4 and sequence 0x494, which is 1172 in decimal. So Itl entry 2 is the transaction lock for the two updates. In the third section for detailed information on each row, you can see “lb: 0x2” for row 0 and row 1. “lb” here stands for “lock byte” and the value 0x2 is for Itl entry 2. That is our row level locking. It is actually under TX lock, not TM lock.

Oracle actually stores the row level locking at row heading highlighted in yellow in section 1 of the dump output.

Row 0 heading is 022C 0201, the block dump is from Linux system with small endian format. To put the heading in right sequence, it is 2C 02 01 02 and the “02” in green highlight is the row level locking byte. Similarly, the row 1 heading is 2C 02 01 05. The heading for the third row is 2C 00 01 03, which is highlighted in blue in the dump section 1 and the lock byte is “00”, which means not locking.

So far so good. We do a commit and another data block dump. I expect the lock bytes for row 0 and row one to be changed to 0.

SQL> commit;

Commit complete.

And of course the TM lock and TX lock for this session are release. The data block dump after commit is following:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 --U- 2 fsc 0x0000.002dc9b8

……

tab 0, row 0, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

Well, the lock byte for row 0 and row 1 do not change at all after commit. The only thing changed for this data block is the flag for Itl entry 2, besides block scn and checksum. Flag “--U-“ marks the transaction inactive and consider all row level locks under this Itl is release. This is why Oracle can do commit operation very fast. The row lock cleanup is piggy packed with next DML for the block and the Itl flag changes to “C---“ and lock byte changes to 0 after the clean up.

SQL> update test set name = 'Adam' where name = 'Ed';

1 row updated.

SQL> rollback;

Rollback complete.

SQL> alter system dump datafile 4 block 967;

System altered.

And the dump file is:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 C--- 0 scn 0x0000.002dc9b8

……

tab 0, row 0, @0x1f6c

tl: 6 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

To understand Oracle row level locking implementation is not just for fun, but has practical use as well. The row locking implementation for Oracle is at row heading level and do not have system wide structure to track them, so it is very scalable and make RAC row level locking relatively simple. Oracle do not have latch directly to protect row level locking, so if a process finds the row is locked by another session, it escalate row level locking to transaction lock and wait the transaction to be done. For rare case, such implementation blocks a session unnecessary.

Here is an example that was demonstrated by Scott Martin at http://www.tlingua.com/new/articles/Chapter1.html.

Let us start with session1:

SQL> update test set name = 'Adam' where name = 'Ed';

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update test set name = 'Mike' where name = 'David';

1 row updated.

Now open session 2 and update row 2:

SQL> update test set name = 'Dave' where name = 'David';

Session is blocked by session 1 and waits the row lock.

From session 1, do rollback of update the row 2.

SQL> rollback to savepoint a;

Rollback complete.

However, session 1 is still waiting the row locking. Do a block dump and lock byte for row 2 is 0x0, so no row locking on the row session 2 is updating. In fact, session 2 is not waiting on row locking any more, it is waiting on session 1 transaction.

Block dump is

tab 0, row 1, @0x1f53

tl: 9 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 5] 44 61 76 69 64

Let us start session 3 to update the same row:

session 3

SQL> update test set name = 'Magic' where name = 'David';

1 row updated.

The row is updated and session 2 is still blocked. Rollback session 1 and session 2 is waiting on session 3. Finally, the session 3 is rollback and session2’s update is completed.

Tuning SQL for third party application

DBAs often face the challenges to tuning the third party applications that SQL statements it interacts with database can not be changed and hinted. Additionally, SQL plan can change dramatically because of Oracle optimizer upgrade and/or statistics change of the database. Such changes may have negative impact on the application performance.

One way to tune such application is to add views between the application and base tables so application queries views created by DBA instead of base tables. DBA can add hints for the views to make the application SQL use right execution plan. However, it is kind of troublesome because you have to rename all base tables and create views with original base table name. Plus, each base table may serve more than one SQL statement, so the hint on view can be harmful for some SQL statements.

Another way is to change database init parameters. Because it is system level tuning, DBA makes balance to improve the critical SQL statements without making negative impact on other SQLs too much. If an upgrade makes performance to deteriorate, set optimizer_features_enable to old version is a quick fix before a better solution is found.

What if DBA can construct an execution plan just for the slow SQL statement that is generated from application without modify it with hints? There is a way to do so by using stored outline. A stored outline is a set of hints Oracle use for a matched SQL string. If Oracle finds a SQL string in stored outline, it will add the hints for this stored outline to cost based optimizer. DBA can run the SQL to be tuned in a testing environment, with the change of init parameter either at system level or session level and add hint at view level similar to the methods we discussed previously to generate desired execution plan for the SQL and generate stored outline. Then such stored outline can be import into production database. By doing this way, no impact on any other SQL except the one you want to tune.

As we know, the stored outline can not change based on database statistics and if the hint is not valid any more, for example, the index for index hint is dropped, the optimizer just ignore the hint and make the good execution plan into a bad one. Start with Oracle 11g, the preferred way is to use SQL plan baseline. The SQL plan baseline stores the execution plan instead of hints, so it is more robust to optimizer changes. Additional, the SQL plan baseline can evolve to accept the better execution plan either manually or automatically.

Here are links to mini how to for stored outline and to mini how to for SQL Plan Baseline

ORA-00054: resource busy with no wait

Often find Oracle is struggling and try to help with additional index or changing table parallel degree but only getting ORA-00054 "resource busy and acquire with NOWAIT specified" error? Well , you have few options:

1. Go away and have a cup of coffee, then try again. If you are lucky, you can do this time.
2. Kill the session that locks database objects you want to modify. You do not have to wait but users may not be too happy about it.
3. Quiesce database by “ALTER SYSTEM QUIESCE RESTRICTED”. Wait until all active transactions are completed. So you can modify objects as DBA. However, you may wait a long time for changing database into quiesce state if some long transaction is running. Additional, it will block all user sessions, not just one that locks your objects.
4. You can brute force the DDL on locked objects by simple PL/SQL, which I found from DBA Tools web site (http://www.dbatools.net/experience/oracle_resource_busy.html).

declare
resource_busy exception;
pragma exception_init (resource_busy,-54);
begin
loop
begin
execute immediate 'ddl sql …';
exit;
exception
when resource_busy then
dbms_lock.sleep(0.01);
end;
end loop;
end;

It works pretty well for me. Have a spare CPU and a cup of hot coffee before you run the script.

For 11g database, you can make ddl to wait by setting ddl_lock_timeout to number of seconds to wait before getting ORA-054 error. During the waiting time, Oracle tries to acquire the object lock needed until time out. No PL/SQL coding is necessary.