SQL Repair Advisor: Further Step to Optimize SQL Performance

Days ago, user reported an issue with a regularly running report. It didn’t generate output but raised error like below.


Upon receiving the error ORA-04031, my first thinking was the insufficient size of large pool size in SGA. For specific reasons, we use Automatic Shared Memory Management (ASMM) instead of Automatic Memory Management (AMM). After reviewing the size of init parameter LARGE_POOL_SIZE, I just simply increased to the size of it Memory Advisor suggests. The new size of it would be the minimum size of Large Pool.  However, user kept getting this error.

Further investigation through Oracle Enterprise Manager (OEM), it looked like the running process was holding top 10 sessions. The color pink shows it’s not regular activity like others.


By analyzing the session above, it’s obvious that “TABLE ACCESS FULL” on this specific table, which has over 7 millions records, is the root cause of performance degradation.


Till now, it’s straightforward to generate optimal execution plan. However, OEM just simply tell the existing one is the optimal one based on current statistics. Re-collecting statistics on this specific table won’t help as well.

I’ll then go deeper workaround by using SQL Repair Advisor. Once the SQL Profile is generated, the user can run this report with expecting result.

SQL Profile is quite mysterious other than SQL Plan Baselines as few of details are disclosed.  To have better understanding with SQL Profile, the following twos are the good resources.

1. What is the difference between SQL Profiles and SQL Plan Baselines? pdf

2. What is the difference between SQL Profile and SPM baseline? pdf

Posted in Oracle Case Study, Oracle Point | Tagged , , | Leave a comment

Windows and X11 forwarding with Xming on Unix/Linux


To get an oracle database clone from one unix server to another one, I’ve tried to use graphical DBCA tool to achieve it.

My working environment to get X11 forwarding working on Unix is Putty along with Xming. After have them installed properly on your windows desktop, just lunch Xming and SSH connection through Putty as below.


And then, log in as my/your own unix account.

-bash-3.2$ cd /usr/openwin/bin

-bash-3.2$ pwd

-bash-3.2$ /usr/openwin/bin/xauth list
dbserver/unix:10  MIT-MAGIC-COOKIE-1  a502d285bf72584145f2147ae360f507
dbserver/unix:10  XDM-AUTHORIZATION-1  f6c65da4c46c000d0024fdf729b85e2e

-bash-3.2$ sudo -u oracle -i
Oracle Corporation      SunOS 5.10      Generic Patch   January 2005

oradb> /usr/openwin/bin/xauth add dbserver/unix:10  MIT-MAGIC-COOKIE-1  a502d285bf72584145f2147ae360f507

oradb> dbca

That’s it. (pdf)

Posted in Oracle Point | Tagged , | Leave a comment

Determining if the OHS is Standalone or Managed


Do the following to determine if the OHS you are trying to upgrade is managed or standalone:

If you are an 11g user: Check for the registered property in the file $ORACLE_INSTANCE/config/OPMN/opmn/instance.properties. If it is set to true, then the instance is registered. A managed Oracle HTTP Server (OHS) will be registered, if it is not registered then it is Standalone OHS.

If you are a 12c user: Check the element extention-template-ref and its attribute name in the file $DOMAIN_HOME/init-info/domain-info.xml.If you find an element with the name Oracle HTTP Server (Standalone), then it is a standalone OHS. And if you find an element with name Oracle HTTP Server (Collocated), then it is collocated.

Posted in Oracle Point, Oracle Utilities | Tagged , | Leave a comment

Database Crashes with Error “ORA-04030: out of process memory when trying to allocate xxx bytes”

The recent database issue we experienced is the error “ORA-04030: out of process memory when trying to allocate 2097248 bytes (pga heap,KCRAHT Context & Hash)”, which crashed one of the testing databases and in turn couldn’t be restarted.

In alert log file, message below is found.

Tue Aug 4 15:01:50 2015
Errors in file /oracle11/diag/rdbms/test/TEST/trace/TEST_pmon_6271.trc:
ORA-04030: out of process memory when trying to allocate 8389144 bytes (pga heap,redo overflow buffer)
Tue Aug 4 15:01:50 2015
Doing block recovery for file 2 block 404106
Tue Aug 4 15:01:50 2015
Errors in file /oracle11/diag/rdbms/test/TEST/trace/TEST_psp0_29201.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

In trace files, the following error message is found.

ORA-04030: out of process memory when trying to allocate 2097248 bytes (pga heap,KCRAHT Context & Hash)

========= Dump for incident 260399 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
32%  176 KB,  23 chunks: "permanent memory          "  
         pga heap        ds=10d042330  dsprt=0
14%   75 KB,   3 chunks: "permanent memory          "  
         session heap    ds=ffffffff78e25bd0  dsprt=10d0481f0
 7%   38 KB,   1 chunk : "Fixed Uga                 "  
         pga heap        ds=10d042330  dsprt=0
 6%   32 KB,   2 chunks: "permanent memory          "  
         top call heap   ds=10d047fd0  dsprt=0
 6%   30 KB,   1 chunk : "free memory               "  
         top call heap   ds=10d047fd0  dsprt=0

Based on above information, Oracle Support Document “Database Crashes With ORA-27300, ORA-27301, ORA-27302 On Solaris SPARC (Doc ID 1333824.1)”offers the solution to solve it by adjusting OS parameters in /etc/system.

Thanks to system administrator, who sent me the error message from os level.

Aug  3 06:09:01 ldau1 genunix: [ID 470503 kern.warning] 
WARNING: Sorry, no swap space to grow stack for pid 10996 (oracle)
Aug  4 15:33:04 ldau1 tmpfs: [ID 518458 kern.warning] 
WARNING: /tmp: File system full, swap space limit exceeded
Aug  4 15:06:32 ldau1 genunix: [ID 470503 kern.warning] 
WARNING: Sorry, no swap space to grow stack for pid 12923 (oracle)

These message indicates that we might have issue with insufficient swap area. As swap area acts as virtual memory, it explains above oracle related error “out of process memory”.

Further, my research guides me to find Oracle Support Document “What Do I Do When Physical Memory is Being Reserved Because Swap Reservations Exceed Disk Based Swap? (Doc ID 1275006.1)” PDF

This document brings very informative explanation of relation between virtual memory and swap area.

With virtual memory, user processes no longer directly access memory and all memory accesses reference 
virtual memory which is managed by the kernel. This allows the kernel to move the contents of virtual 
memory into physical memory so that processes can actually work with their data but the same data can
 then be moved out of physical memory to allow other processes to use the same physical memory 
 which effectively allows processes to use more memory that is physically in the system.

This out of memory storage is called a backing store and for purposes of this document this is swap. 
Historically, swap referred to disk based swap and initially there was a requirement that the amount 
of disk based swap available at least matched the amount of physical memory as most memory requests 
reserved an equal amount of swap as memory.

With the advent of large memory systems, customers no longer wanted to 'waste' large amounts of 
disk for swap that would never be used, and as a result the entire swap system was virtualized which 
removed the need for disk based swap.This Virtual swap, was based on the amount of disk based swap plus
any unused memory. This removed the need for disk based swap, but can also present issues in some cases.

While a process may request memory, what it actually gets from the kernel is swap. This swap represents 
the total amount of 'memory' that the kernel can 'provide' to the process. Note the quotes as to the 
kernel this is swap and only a small portion of it may ever actually occupy physical memory at any time, 
but the process doesn't know this and can't know this without asking the kernel as this entire process 
is hidden from the process.

This leads to a two stage process for memory requests and usage, the first is the request which results 
in a process being told that it has been given some memory. For this step all that the kernel does is 
update the process and reserve the swap by subtracting the swap provided to the process from it's total 
available swap.

The second stage happens when the process actually tries to use the memory that it has been given. 
When this happens the kernel gets notified and verifies that it has promised swap to the process, 
and assuming that there are no problems it will proceed to locate some physical memory and update 
the address mappings so that the process's attempt to access will succeed.

Besides, it offers method to determined if swap area is good enough by comparing results of “swap –s” and “swap –l”. In our case,  amount of swap reserved (swap –s) exceeds the size of disk based swap (swap –l). Thus, increasing size of disk based swap solved the problem.

The method to compare is

$ swap -s
total: XXXXXXXXXk bytes allocated + YYYYYYYYk reserved = ZZZZZZZZZk used, AAAAAAAAAk available

The important value for purposes of this article is the reserved amount which is YYYYYYYYk

If that amount is larger than the total of your disk based swap, then the excess is reserved
by using physical memory which means that memory can never be used for any thing else.

If you use the swap -l command you can determine the size of your disk based swap:

$ swap -l
swapfile dev swaplo blocks free
/dev/zvol/dsk/swappool/swap xx,xx xx SSSSSSSSS ffffffffff

The important value here is the SSSSSSSS or the number of blocks, which from the swap man page:
blocks The swaplen value for the area in 512-byte blocks.

This can easily be converted to K which are 1024 by dividing by 2.

If the Amount of Swap Reserved (swap -s) exceeds the size of your disk based swap (swap -l)
then you are reserving physical memory which can not be used for other purposes and you may want
to consider adding additional disk based swap to the system.

Another good document is “How to Resolve “no swap space to grow stack for pid …” Messages (Doc ID 2022359.1)” PDF

Posted in My Reference, Oracle Case Study, Oracle Point | Tagged , , , , | 6 Comments

Scripts to find Blocking/Blocked Session in oracle database

Just found couple of good scripts to find blocking/blocked session in oracle database.

Script 1: find blocking session


Note: this script need to be run with sql*plus.

column "wait event" format a50 word_wrap
column "session" format a25
column "minutes" format 9999D9
column CHAIN_ID noprint
column N noprint
column l noprint
with w as (
 chain_id,rownum n,level l
 ,lpad(' ',level,' ')||(select instance_name from gv$instance where inst_id=w.instance)||' '''||w.sid||','||w.sess_serial#||'@'||w.instance||'''' "session"
 ,lpad(' ',level,' ')||w.wait_event_text ||
   when w.wait_event_text like 'enq: TM%' then
    ' mode '||decode(w.p1 ,1414332418,'Row-S' ,1414332419,'Row-X' ,1414332420,'Share' ,1414332421,'Share RX' ,1414332422,'eXclusive')
     ||( select ' on '||object_type||' "'||owner||'"."'||object_name||'" ' from all_objects where object_id=w.p2 )
   when w.wait_event_text like 'enq: TX%' then
     select ' on '||object_type||' "'||owner||'"."'||object_name||'" on rowid '
     from all_objects ,dba_data_files where object_id=w.row_wait_obj# and w.row_wait_file#=file_id
   end "wait event"
 , w.in_wait_secs/60 "minutes"
 , s.username , s.program
 from v$wait_chains w join gv$session s on (s.sid=w.sid and s.serial#=w.sess_serial# and s.inst_id=w.instance)
 connect by prior w.sid=w.blocker_sid and prior w.sess_serial#=w.blocker_sess_serial# and prior w.instance = w.blocker_instance
 start with w.blocker_sid is null
select * from w where chain_id in (select chain_id from w group by chain_id having max("minutes") >= 1 and max(l)>1 )
order by n

Script 2: find blocking session


select dbs.sid holding_sid, dbs.serial# holding_serial#, dbs.username holding_user, 
       (select count(sid) from v$session where blocking_session = dbs.sid) sessions_blocked,
       dbs.sql_id holding_sql_id, dbs.wait_class holding_class, dbs.event holding_event, dbs.seconds_in_wait holding_secs, 
       dws.sid waiting_sid, dws.serial# waiting_serial#, dws.username waiting_user, dws.sql_id waiting_sql_id, dws.wait_class waiting_class, dws.event waiting_event, dws.seconds_in_wait waiting_secs
from v$session dbs, v$session dws
where dws.blocking_session = dbs.sid
order by dbs.sid, dws.seconds_in_wait desc

Script 3: find recent blocking session/lock


set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct
       a.sql_id ,
       a.blocking_session blocker_ses,
       a.blocking_session_serial# blocker_ser,
      gv$sql s
where a.sql_id=s.sql_id
  and blocking_session is not null
  and a.user_id <> 0 --  exclude SYS user
  and a.sample_time > sysdate - 1

Script 4: find oracle blocking sessions and locked objects

  https://oradig.com/finding-oracle-blocking-sessions-and-locked-objects/ (PDF)

Script 5: Oracle Blocking Queries https://alokdba.wordpress.com/ (PDF)

  • detecting blocking sessions in RAC and non-RAC
  • Fully decoded locking
  • display SQL text from locked transactions
  • display locks and give the SID and serial# of session to kill
  • display user lock information

**** Script 6: Find a locking session https://oracleexamples.wordpress.com/2010/04/08/find-a-locking-session/ (PDF)

Posted in My Reference, Oracle Utilities | Tagged , | Leave a comment