重庆思庄Oracle、Redhat认证学习论坛

标题: 查看ORACLE指定SQL游标版本情况 [打印本页]

作者: 郑全 时间: 2024-7-24 19:37
标题: 查看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
================================================================

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)