Oracle Histograms Simplified: Part 1

Oracle Histogram is very nice feature to help CBO to make right decision while constructing execution plan. But many of DBA’s do not use it, assuming it is complex concept to use. In next few posts, I will try to simplify it, so that we can achieve maximum benefit from it.

What it Histogram & what is its purpose:

A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. In simple words, it is a feature in CBO and it helps optimizer to determine how data are distributed/skewed within column. Predicates with skewed data are good candidates for histogram creation.

Basically CBO assumes uniform data distribution of data inside column across the distinct values. For the columns that contain skewed data, histogram helps to generate more accurate execution plans.

Below example will explain you significance of histograms:


I have a table ADI.SALES_ORDER with 100000 rows with index SALES_ORDER_N1 on column GENERATED.

GENERATED column has 4 distinct values: A, B, Y, N.  CBO will assume uniform data distribution across these values. So any query with column GENERATED as predicate will go for FTS, as CBO will assume it has to select 25% data from table.

But in reality data under column generated in skewed as given below:

I gathered fresh statistics without histogram on table as shown below:

Now will compare execution plan with different values for predicate:

As expected this query is going for FTS, as it has to select approx. 80% of data.

But I will expect query to pick index in case of GENERATED=’N’ as it will just select less than 1% of rows from table.

But NO its still going for FTS 😦 & it is due to CBO’s default behavior.

Now here Histogram should come into picture:

Now I will once again compare plans:

As expected following query is going for FTS, as it has to select approx. 80% of data.

But following query has picked up index due to creation of histogram. 🙂

In this post, we focused on what is histogram & why it is used.

Hope so u will find this post very useful:-)

Stay tuned for Oracle Histograms Simplified: Part 2, in which I will explain what are different ways to create & drop histogram on particular column.

Cheers

Regards,
Adityanath

Advertisements

EXPDP/IMPDP has performance issue due to “Streams AQ: enqueue blocked on low memory”

In this post, we are discussing about performance issue which I faced with EXPDP schema backup.

We have a production database, for which we take daily EXPDP schema backup before EOD starts. The control-m job configuration has made in such a way that EOD will not start until schema level EXPDP backup gets complete. From last month, EXPDP backup started taking considerable time. Initially it used to take hardly 15 minutes, and all of sudden it started taking 90 minutes, in turn causing delays in start of EOD. Application team raised problem ticket with us for the same issue.

I did basic tuning, like increasing PGA or excluding statistics, but none of it helped me to minimize backup time. Then I thought of observing wait event for EXPDP master & slave processes. It was “Streams AQ: enqueue blocked on low memory”. Of course it was something to related to performance issue I was observing.

I did check for memory configuration of database & found memory is configured with automatic management:

2
If you see above output, due to sudden growth in shared pool size, other components like large pool, db cache & streams pool have shrunk.

I tried to dig in history backup logfiles & found, till 6th Jan 2017, backups were running fine. So lack of streams pool memory is causing EXPDP to run longer.

So as to resolve this issue, I switched from automatic memory to manual one & allocated 200 MB to streams pool. Post memory configuration changes, EXPDP performance was back to normal.

After searching on MOS for similar issues, I found EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

ORA-12801: error signaled in parallel query + ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”)

Yesterday I got request from one of the user saying his job failed giving following error:


Severity Timestamp Node Thread Message Code Message
ERROR 2014/10/24 02:59:32 PM node-uat-informatika READER_1_1_1 RR_4035 SQL Error [
ORA-12801: error signaled in parallel query server P178, instance rmb-uat-db-gik1:DBGIK1 (1)
ORA-12853: insufficient memory for PX buffers: current 4347776K, max needed 78766080K
ORA-04031: unable to allocate 65560 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Database driver error...
Function Name : Execute
SQL Stmt : SELECT APPS.XXXLA_TRX_NOT_IN_GL_V.COUNTRY_CODE, APPS.XXXLA_TRX_NOT_IN_GL_V.EVENT_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.PKEY, APPS.XXXLA_TRX_NOT_IN_GL_V.TDS_BATCH_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.TYPE FROM APPS.XXXLA_TRX_NOT_IN_GL_V
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT APPS.XXXLA_TRX_NOT_IN_GL_V.COUNTRY_CODE, APPS.XXXLA_TRX_NOT_IN_GL_V.EVENT_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.PKEY, APPS.XXXLA_TRX_NOT_IN_GL_V.TDS_BATCH_ID, APPS.XXXLA_TRX_NOT_IN_GL_V.TYPE FROM APPS.XXXLA_TRX_NOT_IN_GL_V
Oracle Fatal Error].

ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs.

Ofcourse in my case large pool was getting full. but WHY? I concerntrated on other error i.e. ORA-12801: error signaled in parallel query server P178.

After checking parameterd related to parallel processing, I found parallel_max_servers was set to very high value : 2560.

After checking v$sga_dynamic_compnents for large_pool size I got following stats:


COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
---------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------------------- ------------
large pool 2617245696 335544320 5100273664 0 4116 GROW IMMEDIATE 24-OCT-2014 15:03:47 67108864

So we can say large_pool was grown & given ORA-04031 once it was having insufficient memory to grow.

I decided to minimize parallel_max_servers.


15:06:11 SQL> alter system set parallel_max_servers=200;
system altered.

v$sga_dynamic_compnents statistics for large_pool post parameter change:


COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE
---------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------------------- ------------
large pool 536870912 335544320 5100273664 0 4118 SHRINK DEFERRED 24-OCT-2014 15:06:41 67108864

As soon as I minimized parallel_max_servers, current size for large_pool decreased from 2617MB to 536MB.

I suggested User to rerun his job. He confirmed about successful execution of job 🙂

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Multiple failed login attempts can cause high number of row cache lock wait events in 11g

From Oracle 11g, there is a new security enhancement that has potential of creating huge performance issues due to failed login attempts through multiple sessions.

Yesterday we faced this issue on production database, all application users were complaining about slow performance especially while making new connections with database.

After having quick check with v$session, I found there are huge number of sessions waiting on event : row cache lock.


 

select event,count(0) from v$session
where username is not null
and status='ACTIVE'
group by event
order by 2 desc;
EVENT COUNT(0)
---------------------------------------------------------------- ----------
row cache lock 137
class slave wait 4
direct path read 3
db file sequential read 2
db file scattered read 1
SQL*Net message to client 1

 

Row cache lock ???

After searching on metalink I found following note :

Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay (Doc ID 7715339.8).

As per this bug: In 11g there is an intentional delay between allowing failed logon attempts to retry. For some specific application types this can cause a problem as the row cache entry is locked for the duration of the delay . This can lead to excessive row cache lock waits for DC_USERS for specific users / schemas .

After 3 successive failures a sleep delay is introduced starting at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).

This can severely impact applications as any new connection would not be possible. All existing sessions will continie to work without issue.

In AUD$ we found that , one of the application schema was with high number of failed login attempts , all of which were done through database link from other database.

After troubleshooting issues with database link, database performance was back to normal.

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Resolving cursor: pin S wait on X using interesting column final_blocking_session in v$session

Recently I was having issue in our test environment, users were complaining that database is not responding for any queries, it appears to be in hanged state.

After checking database for possible causes for performance issues, I found following:


 

SQL> select event,count(0) from v$session where username is not null and status='ACTIVE' group by event;
event                      count(0)
-----------------------    -----------
db file sequential read    1 
library cache lock         4 
cursor: pin S wait on X    25 
SQL*Net message to client  1

 

Definitely something was wrong with the database. So firstly I concentrated on wait event library cache lock. I found some user was executing following DDL on one of the highly accessible table in the database:


 

SQL> @s1
Enter Session ID: 1409
======================================================================================================
SID/Serial : 1409,39547
Module : TOAD 10.6.1.3
SQL_ID : d489wrxp66a0m
Foreground : PID: 11016:1228 - Toad.exe
Shadow : PID: 12339 - oracle@test4.test.com
Terminal : XXGGG / UNKNOWN
OS User : C_GGG on TEST4\C_GGG2-L
Ora User : SADB
Status Flags: ACTIVE DEDICATED USER
Tran Active : 000000028CC470A0
Login Time : Thu 11:44:52
Last Call : Thu 11:48:59 - 199.7 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
 ALTER TABLE sadb.audit_log DROP UNUSED COLUMNS CHECKPOINT 1000
Previous SQL statement:
Session Waits:
 WAITING: db file sequential read
Connect Info:
 : TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
 : Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production
 : Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
 TYPE=AE H: S R: NONE - ID1=282717 ID2=0
 DML/DATA ENQ H: X R: NONE - AUDIT_LOG
====================================================================================================

 

So here I got culprit for wait eventlibrary cache lock. Killing this session would have resolved sessions waiting for this wait event.

But what causing cursor: pin S wait on X?

I searched on Google for possible causes of this wait event, I found lots of documentation each pointing to different possibilities.

Is same session with SID : 1409 was the culprit? After describing v$session view, I found very interesting column – ‘final_blocking_session’ – which is introduced in Oracle 11g.


 

SQL> select distinct blocking_session from v$session where event='cursor: pin S wait on X';
BLOCKING_SESSION
----------------
 1305
 1520
SQL> select sql_id from v$session where sid in (1305,1520);
SQL_ID
-------------
4vcd40bta41pb
0h4n33suqpqtv
SQL> select distinct final_blocking_session from v$session where event='cursor: pin S wait on X';
FINAL_BLOCKING_SESSION
----------------------
 1409

 

So I found the culprit :-). After killing session with SID : 1409, issue got resolved.

After searching on Metalink I found very useful note:

Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1), which says

In 11.2 you can add v$session.final_blocking_session to see the final blocker. The final blocker is the session/process at the top of the wait chain. This is the session/process that maybe causing the problem. Example of query with final_blocking_session info:


 

set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT * 
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)|| 
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'<none>',
 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 
 'Program: '||p.program image,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds,
 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,
'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '<none>',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
 gv$session s,
 gv$session bs,
 gv$instance i,
 gv$process p
WHERE wc.instance = i.instance_number (+)
AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 and wc.sess_serial# = s.serial# (+))
 AND (s.final_blocking_instance = bs.inst_id (+) 
and s.final_blocking_session = bs.sid (+))
 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
ORDER BY chain_id,
 num_waiters DESC)
WHERE ROWNUM < 101;

 

This sql will give u OS process ID for final blocker.


 

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath