查看ORACLE指定SQL游标版本情况

郑全

比如看 916zfvvc93bz3 的游标版本情况：
select * from table(version_rpt('916zfvvc93bz3'));

结果：
Note:438755.1 Version Count Report Version 3.2.5 -- Today's Date 24-jul-24 13:36
RDBMS Version :19.0.0.0.0 Host: lisdb2 Instance 2 : HISDB2
==================================================================
Addr: 00000002B7567E68  Hash_Value: 3633426403  SQL_ID 916zfvvc93bz3
Sharable_Mem: 2162316593 bytes   Parses: 83802   Execs:81148
Stmt:
0 SELECT * FROM (   SELECT       PAI_VISIT.PAI_VISIT_ID, PAI_VISIT
1 .PATIENT_ID, VISIT_ID, PATIENT_NAME,PATHWAY_STATUS,   PATIENT_GE
2 NDER, BIRTHDATE,   IDENTITY_TYPE, WORKING_STATUS,   OCCUPATION,
3   MARITAL_STATUS, PATIENT_AGE_STRING, CURRENT_DEPT, CURRENT_WARD
4 ,   CURRENT_NURSING_UNIT,   PAI_VISIT.BED_ID, PAI_VISIT.BED_CODE
5 ,   NEW_IN_DEPT, NEW_IN_DEPT_DATE,   ADMISSION_DEPT,   ADMISSION
6 _WARD,   ADMISSION_DEPT_TYPE,   ADMISSION_CASE,   ADMISSION_PURP
7 OSE,   IN_REGISTER_DATE,   CREATER, DISCHARGE_DEPT,   DISCHARGE_
8 DEPT_DATE,   DISCHARGE_DISPOSITION, CLINIC_DIAGNOSIS,   CLINIC_D
9 IAGNOSIS_NAME,   HOSPITAL_AREA, IN_FLAG,   CHECK_DISCHARGE_BILL_
10 FLAG,   COST_TYPE,   CURRENT_STATUS, CURRENT_ACTION,   LAST_TRAN
11 SFER_DATE,   CANCEL_IN_FLAG,   SETTLE_STATUS, DISEASE_TYPE,   TO
12 DAY_EXIT_FLAG, FORECAST_DISCHARGE_DATE,   ADMISSION_DOCTOR,   AD
13 MISSION_DATE,   MRN, NURSING_LEVEL, CASE_STATUS,   DOCTOR_CHARGE
14 ,   DOCTOR_ATTENDING,   DOCTOR_CHIEF,   NURSE_CHARGE,   BONDSMAN
15 _NAME,   BONDSMAN_UNIT, BONDSMAN_PHONE,
16

Versions Summary
----------------
OPTIMIZER_MISMATCH :66
BIND_EQUIV_FAILURE :3472
ROLL_INVALID_MISMATCH :3261
PURGED_CURSOR :1
USE_FEEDBACK_STATS :4

Total Versions:3471

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = FORCE
_cursor_obsolete_threshold = 8192 (See Note:10187168.8)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
     4216614660 3017
     1939405483 1
      544388990 5
     3012748548 448
     3029274991 1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MISMATCH :

65 versions with cursor_sharing = force
39 versions with total_processor_group_count = 2
66 versions with parallel_max_degree = 192
66 versions with _pga_max_size = 2097152 KB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_EQUIV_FAILURE :

Consolidated details for BIND* columns:
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)
======== ======== =============== =============== ======== =============== =================
    3473        1              32             128        1     Yes          (,)
    3473        2              22              22        2     No           (,)
    3473        3              32             128        1     Yes          (,)
    3473        4              22              22        2     No           (,)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL
IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
=========== ================= ============= ============
          0              3473          3473          428
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for ROLL_INVALID_MISMATCH :

No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for PURGED_CURSOR :

No details available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for USE_FEEDBACK_STATS :

No details available
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 3633426403, level LLL';
To turn it off do use address 1, level 2147483648
================================================================