General
===========================
Checking the AWR report, the top foreground event is "cursor: pin S wait on X".
What does it mean?
===========================
Oracle Documentation
===========================
Per Oracle Tech Note Troubleshooting 'cursor: pin S wait on X' waits. (Doc ID 1349387.1)
What is a 'Cursor: pin S wait on X' wait?
A cursor wait is associated with parsing in some form.
A session may wait for this event when it is trying to get a mutex pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive.
Frequently, waits for 'Cursor: pin S wait on X' is a symptom and not the cause.
There may be underlying tuning requirements or known issues.
Apparently up to Oracle 11, there was a limit to child cursors, which was 1024.
In Oracle 11, this limit does not exists, which can lead to high number of child cursors.
==========================================
Look for a specific SQL that might be responsible for the wait.
==========================================
SELECT *
FROM DBA_HIST_SNAPSHOT;
To get the snap_id
SELECT sql_id, count(*)
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE snap_id = 31405
AND event = 'cursor: pin S wait on X'
GROUP BY sql_id
ORDER BY count (*) DESC
6jb4173jbysjp 3526
So one sql_id is responsible for all the 'cursor: pin S wait on X' waits
SELECT *
FROM V$SQL_TEXT
WHERE sql_id = '6jb4173jbysjp'
INSERT INTO DEBUG_GEN_W_SDR
(CDR_ID, PRODUCT_ID, PRODUCT_NAME, SYSTEM_ID, BILLING_TAG, IMSI, MSISDN, IMEI, EVENT_NAME, HOME_COUNTRY_ID, HOME_COUNTRY_NAME, VISITED_COUNTRY_ID, VISITED_COUNTRY_NAME, HOME_NETWORK_ID,HOME_NETWORK_NAME, HOME_PLMN_CODE, VISITED_NETWORK_ID, VISITED_NETWORK_NAME, CLI_IN, CLI_OUT, ORIG_DN, DN_IN, DN_OUT, DN_IN_NOA, REDIRECTION_NUMBER, CALL_TYPE, ROAMING_TYPE, SERVICE_KEY_IN, CURRENT_MSC_GT,SCCP_CALLED_GT, SCCP_CALLING_GT, CALL_END_CLEAR_CAUSE_ID, CALL_END_CLEAR_CAUSE_NAME, CALL_END_NETWORK_CLEAR_CAUSE, SESSION_START_TS, SESSION_END_TS, SESSION_DESC, SESSION_TS_ANSWER, SESSION_TS_DISCONNECT, SESSION_DUR_SEC, EXTERNAL_CALL_REF_NUM, PARAM1, PARAM2, PARAM3,PARAM4,PARAM5, PARAM6, PARAM7, PARAM8, PARAM9, PARAM10, PARAM11, PARAM12, PARAM13, PARAM14, PARAM15, PARAM16, PARAM17, PARAM18, PARAM19, PARAM20, PARAM21, PARAM22, PARAM23, PARAM24,PARAM25, PARAM26, PARAM27, PARAM28, PARAM29, PARAM30, PARAM31,PARAM32,PARAM33,PARAM34,PARAM35, PARAM36, PARAM37, PARAM38, PARAM39, PARAM40, PARAM41, PARAM42, PARAM43, PARAM44)
VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 ,:24 ,:25 ,:26 ,:27 ,:28 ,:29 ,:30 ,:31 ,:32 ,:33 ,:34 ,:35 ,:36 ,:37 ,:38 ,:39 ,:40 ,:41 ,:42 ,:43 ,:44 ,:45 ,:46 ,:47 ,:48 ,:49 ,:50 ,:51 ,:52 ,:53 ,:54 ,:55 ,:56 ,:57 ,:58 ,:59 ,:60 ,:61 ,:62 ,:63 ,:64 ,:65 ,:66 ,:67 ,:68 ,:69 ,:70 ,:71 ,:72 ,:73 ,:74 ,:75 ,:76 ,:77 ,:78 ,:79 ,:80 ,:81 ,:82 ,:83 ,:84 ,:85 )
This SQL is using bind variable, seems that there is nothing wrong with that...
SELECT version_count, executions, parse_calls, parsing_schema
FROM V$SQLAREA
WHERE sql_id = '6jb4173jbysjp'
version_count = 2717
executions = 5646
parse_calls = 1631
parsing_schema_name = MY_USER
So the issue is the big number of version_count.
Why did this happen?
===========================
High number of cusror version count
===========================
This is a very nice post
In short, when java application is doing UPDATE or INSERT with a column having NULL value, it does not grantee that the datatype for the NULL value parameter, would be the same in all the executions.
To grantee that, application must set parameter to a specific datatype, in addition to setting value to NULL.
In case there are many such NULL values columns, the combinations number would be high, leading to the high number of child cursors.
Changing the data type of a bind variable invalidates the child cursor (“bind mismatch”).
In detail...
jdbs driver by default is using setNull.NULL.
In this case the setter method will not use the data type of the field in the table.
Instead, the default is VARCHAR2 is used.
If in another execution a value is passed, the setter would use the data type of the field in the table.
In theory, setting over 30 numeric values to null and non-null in an alternating matter, creates 2^30 child cursors.
The solution would be set the bind variable to the correct datatype in addition to setting value to NULL.
===========================
V$SQL_SHARED_CURSOR
===========================
To see child cursors:
SELECT * FROM V$SQL_SHARED_CURSOR
WHERE address LIKE 'f3g84j69n0tjh%'
This table has many types, for which cursor could not be shared, and child cursors were created.
DESC V$SQL_SHARED_CURSOR
Name Type Nullable
----------------------------- ----------------- --------
SQL_ID VARCHAR2(13 BYTE) Y
ADDRESS RAW(8) Y
CHILD_ADDRESS RAW(8) Y
CHILD_NUMBER NUMBER Y
UNBOUND_CURSOR VARCHAR2(1 BYTE) Y
SQL_TYPE_MISMATCH VARCHAR2(1 BYTE) Y
OPTIMIZER_MISMATCH VARCHAR2(1 BYTE) Y
OUTLINE_MISMATCH VARCHAR2(1 BYTE) Y
STATS_ROW_MISMATCH VARCHAR2(1 BYTE) Y
LITERAL_MISMATCH VARCHAR2(1 BYTE) Y
FORCE_HARD_PARSE VARCHAR2(1 BYTE) Y
EXPLAIN_PLAN_CURSOR VARCHAR2(1 BYTE) Y
BUFFERED_DML_MISMATCH VARCHAR2(1 BYTE) Y
PDML_ENV_MISMATCH VARCHAR2(1 BYTE) Y
INST_DRTLD_MISMATCH VARCHAR2(1 BYTE) Y
SLAVE_QC_MISMATCH VARCHAR2(1 BYTE) Y
TYPECHECK_MISMATCH VARCHAR2(1 BYTE) Y
AUTH_CHECK_MISMATCH VARCHAR2(1 BYTE) Y
BIND_MISMATCH VARCHAR2(1 BYTE) Y
and much more...
===========================
Workaround
===========================
Flush the shared pool for the specific statement, using DBMS_SHARED_POOL.purge.
Execution of DBMS_SHARED_POOL.PURGE needs an EXECUTE permission from sysdba.
SQL> SELECT address, hash_value, version_count FROM V$SQLAREA WHERE sql_id LIKE 'f3g84j69n0tjh%';
ADDRESS HASH_VALUE VERSION_COUNT
---------------- ---------- -------------
000000009F8E4970 2470471216 102
SQL> exec DBMS_SHARED_POOL.PURGE ('000000009F8E4970, 2470471216', 'C');
PL/SQL procedure successfully completed.
SQL> SELECT address, hash_value, version_count FROM V$SQLAREA WHERE sql_id LIKE 'f3g84j69n0tjh%';
no rows selected
Or via PL/SQL block:
CREATE OR REPLACE PROCEDURE purge_sql_sp AS
v_sql_id V$SQLAREA.sql_id%TYPE := '';
v_sql_address V$SQLAREA.address%TYPE := '';
v_hash_value V$SQLAREA.hash_value%TYPE := '';
BEGIN
SELECT sql_id INTO v_sql_id
FROM V$SQLAREA
WHERE sql_text LIKE 'MERGE into SOME_TABLE%';
--EXECUTE IMMEDIATE 'SELECT address, hash_value FROM V$SQLAREA
WHERE sql_id = ''someSQLIDfoo''' INTO v_sql_address,v_hash_value;
EXECUTE IMMEDIATE 'SELECT address, hash_value FROM V$SQLAREA WHERE sql_id = '''||v_sql_id||'''' INTO v_sql_address,v_hash_value;
DBMS_SHARED_POOL.purge (v_sql_address||','||v_hash_value||','C');
END;
The SQL ID should remain the same, so this piece of code could be wrapped into a scheduled job, as needed.
Check status:
SELECT 'SPE7' as source_db,
TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi'),
sql_id,
executions_delta,
loaded_versions,
invalidations_delta,
parse_calls_delta,
disk_reads_delta
FROM DBA_HIST_SQLSTAT,
DBA_HIST_SNAPSHOT
WHERE sql_id = '71bmcyg2f5td0'
AND DBA_HIST_SQLSTAT.snap_id = DBA_HIST_SNAPSHOT.snap_id
ORDER BY 2 DESC;
