v$session.sql_id为NULL或不准确的BUG

v$SESSION是我们常用的动态性能视图之一,其SQL_ID字段常用来检测会话当前执行的SQL语句,但在少数版本中V$SESSION的SQL_ID列可能不正常地显示SQL_ID,一般是在Session Active或执行Pl/SQL的时候SQL_ID为NULL,以下是部分相关的部分BUG列表:

 

BUG# Affected Version Symptom Fix Version
Bug 13068790 – the value of v$session.sql_id of active session is null [ID 13068790.8] 11.2.0.2.4 Patch Set Update
11.2.0.2.3 Patch Set Update
11.2.0.2.1 Patch Set Update
11.2.0.2
11.2.0.1
active session’s sql_id, sql_address may be null or 0 out while a recursive call is executing. 11.2.0.3 (Server Patch Set)
Bug 14393463 : IN V$SESSION, SQL_ID IS NOT NULL BUT STATUS IS INACTIVE 11.2.0.3 Abstract: IN V$SESSION, SQL_ID IS NOT NULL BUT STATUS IS INACTIVE No Workaround Now
Bug 11670241 : NULL V$SESSION.SQL_ID WHEN RUNNING SLOW SQL IN PL/SQL 11.2.0.1 In 11.2 (maybe also in 10g), when running a slow sql in pl/sql, we found that
v$session.sql_id will be null.  This is very inconvenient for
troubleshooting, because we cannot tell what sql is running by a session.
The only workaround seems to turn on sql_trace.
No Workaround Now
Bug 13089859 : THE VALUE OF V$SESSION.SQL_ID OF ACTIVE SESSION IS NULL 11.2.0.2 In some cases customer needs to find out SQL statements which are
executed in active sessions and tune them. So they needs the values of SQL_ID
in v$session.But that column occasionally have NULL values and then it make a difficulty
in tuning problematic SQL statements.
The following columns of v$session have NULL or zero (0) values even though
the session status is ACTIVE: SQL_ID, SQL_ADDRESS, SQL_HASH_VALUE,
SQL_EXEC_START, SQL_EXEC_ID.
12.1

 

另附V$SESSION视图相关的BUG列表:

 

NB Bug Fixed Description
13545355 12.1.0.0 V$SESSION.FIXED_TABLE_SEQUENCE may show negative values if DB up for a long time
10299006 11.2.0.2.GIPSU03, 11.2.0.3, 12.1.0.0 Excess oraagent.bin sessions in the database (in V$SESSION) / ORA-20
9339310 11.2.0.2, 12.1.0.0 V$SESSION may not show correct SQL_ID
P 7214679 11.2.0.2, 12.1.0.0 OSUSER in V$SESSION is missing extended characters
13068790 11.2.0.3 the value of v$session.sql_id of active session is null
8655331 11.2.0.2 V$SESSION.COMMAND has wrong code for commands >= 128
6994490 10.2.0.5, 11.2.0.1 Multibyte characters garbled in V$SESSION client information
6993310 11.2.0.1 V$SESSION.CLIENT_INFO cannot be set to NULL with fix for bug 5915741
6661393 10.2.0.5, 11.1.0.7, 11.2.0.1 Setting CLIENT_INFO does not affect CLIENT_IDENTIFIER
8674660 10.2.0.5, 11.1.0.7 DIAG process gets PGA memory corruption or ORA-600[kjzhablar:idx] or dump in RAC env
5928612 10.2.0.4, 11.1.0.7 V$SESSION SQL_ADDRESS / SQL_HASH_VALUE not set for DBMS_JOBs
5915741 10.2.0.5, 11.1.0.6 ORA-29275 selecting from V$SESSION with multibyte DB
5246867 10.2.0.3, 11.1.0.6 V$SESSION.PROGRAM is not populated properly for JDBC Thin
5140631 10.2.0.4, 11.1.0.6 V$SESSION.sql_address not set by DBMS_SCHEDULER
5078627 10.2.0.4, 11.1.0.6 Audit sessionid is zero for jobs invoked by job scheduler
5010879 10.2.0.4, 11.1.0.6 V$SESSION slow and does not show any BLOCKING_SESSION column data
4507211 10.2.0.3, 11.1.0.6 Thin JDBC connection properties terminal not reflected in V$SESSION
4496189 9.2.0.8, 10.2.0.2, 11.1.0.6 V$SESSION.PROGRAM can contain partial MULTIBYTE characters
4493741 10.2.0.4, 11.1.0.6 Cannot see SQL_TEXT for procedure calls from EXECUTE IMMEDIATE
4383610 10.1.0.5, 10.2.0.2, 11.1.0.6 application info attributes are not translated correctly in utf16 environment
3735857 10.2.0.2, 11.1.0.6 V$SESSION.OSUSER not populated for JDBC clients
9322219 10.2.0.5.5 Session dump with stack memcpy <- kjzhgigblk in RAC env
5884519 10.2.0.4 V$SESSION is slow with fix for bug 5010879
5481650 10.2.0.4 GV$SESSION.blocking_session has incorrect value
4393134 10.2.0.1 OracleConnectionCachImpl does not set connection properties (eg V$SESSION.PROGRAM)
3258390 9.2.0.6, 10.1.0.4, 10.2.0.1 V$SESSION.SCHEMANAME may return wrong value in PLSQL of another user
2740805 9.2.0.5, 10.1.0.2 V$SESSION.OS_USER set incorrectly if client uses RADIUS
P 2661173 9.2.0.4, 10.1.0.2 Linux: V$SESSION.PROGRAM shows full path instead of executable name
P 2628258 9.2.0.3, 10.1.0.2 Win: Trailing “\0” added to MACHINE column of V$SESSION
2123156 9.0.1.4, 9.2.0.2, 10.1.0.2 FAILOVER: V$SESSION.FAILOVER_METHOD / FAILOVER _TYPE may be wrong for second connection
2106360 9.2.0.2, 10.1.0.2 MODULE (V$SESSION/V$SQL) should default to the PROGRAM name
P 2026123 8.1.7.3, 9.0.1.4, 9.2.0.1 V$SESSION.PROGRAM displays ‘?’ for OCI clients
1326191 9.2.0.1 V$SESSION.OSUSER is always ‘ORACLE’ using JDBC Thin
1249631 8.1.7.3, 9.0.1.3, 9.2.0.1 V$SESSION.SQL_ADDRESS is not cleared when a cursor is unmapped
1540012 9.0.1.0 V$SESSION and V$SESSTAT “IDLE TIME” may not increment
1500535 8.1.7.1.B, 9.0.1.0 V$SESSION.PROCESS not set by the THIN driver
1290469 8.1.6.3.J, 8.1.7.0 V$SESSION does not show useful information for JDBC thin clients
1237128 8.1.7.0 V$SESSION.PROGRAM may not contain a value
889678 8.1.7.0 V$SESSION had short sizes for TERMINAL and MACHINE fields
P 759086 7.3.4.4 SP2: Program column in V$SESSION shows up as ? (broken fix)

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号