select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null
SQL_TEXT SQL_FULLTEXT SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING FETCHES EXECUTIONS PX_SERVERS_EXECUTIONS END_OF_FETCH_COUNT USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS DIRECT_WRITES BUFFER_GETS APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_MODE OPTIMIZER_COST OPTIMIZER_ENV OPTIMIZER_ENV_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME KEPT_VERSIONS ADDRESS TYPE_CHK_HEAP HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE CHILD_NUMBER SERVICE SERVICE_HASH MODULE MODULE_HASH ACTION ACTION_HASH SERIALIZABLE_ABORTS OUTLINE_CATEGORY CPU_TIME ELAPSED_TIME OUTLINE_SID CHILD_ADDRESS SQLTYPE REMOTE OBJECT_STATUS LITERAL_HASH_VALUE LAST_LOAD_TIME IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE CHILD_LATCH SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PROGRAM_ID PROGRAM_LINE# EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE LAST_ACTIVE_TIME BIND_DATA TYPECHECK_MEM IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_INTERCONNECT_BYTES PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS LOCKED_TOTAL PINNED_TOTAL IO_CELL_UNCOMPRESSED_BYTES IO_CELL_OFFLOAD_RETURNED_BYTES CON_ID IS_REOPTIMIZABLE IS_RESOLVED_ADAPTIVE_PLAN IM_SCANS IM_SCAN_BYTES_UNCOMPRESSED IM_SCAN_BYTES_INMEMORY
1 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null <CLOB> b5s4sgqc6f9fc 32819 9392 8000 0 1 1 3 2511 2511 0 2511 0 10 2018-09-11/21:03:07 7 2511 874 0 13427 0 222321 878961 316525 0 0 2511 3 ALL_ROWS 8 E289FB891242B700DB011000AEF9C3E2CFFA331056414555519521105545551545545558591555449665851D5511058555544155515122555414A0EA0C5551454265405454449081566E001696C6A755441501025415404416FD157141551555550001540A16294545C1C21440A101001559554049C0144C5555455554FA0705949684D02D40019600008000000000040000400000000800800002F40100000000640000000840400000000E3E00010000000004000000328084D02D401919998000998C800C008000C08080824241810205800000007D000000998282820219FA0000001930E08000800000C0FF3F00002C960C1000100000FA000000C40900000008008082C70019320000020032C40900C00041A002A0252600FA0000C012D9FF3F00C0FF3F000000040000 3675012389 131 131 V7TLHX 0 0700011ABA2F6CD0 00 2556896716 3149753837 2918889830 3800854560 1 tlhxrac1 0 JDBC Thin Client -1748072636 0 0 303761 1940571 07000119D29CB690 6 N VALID 0 2018-09-12/09:25:37 N N N Y 0 0 0 1.29638007760582E19 1.29638007760582E19 2018/9/12 10:10:33 BEDA0C100200FFFFFC7C000203F0018003541553584B323031383039313230303030303030393735F0012003540439393939 0 0 7159808 874 7159808 0 0 0 1716 3001 0 0 0 N 0 0 0
2 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null <CLOB> b5s4sgqc6f9fc 36931 9296 7904 0 1 0 0 1 1 0 1 0 3 2018-09-11/21:03:07 2 2 2 0 9 0 0 0 551 0 0 1 3 ALL_ROWS 8 E289FB891242B700DB011D00AEF9C3E2CFFA331056414555519521105545551545545558591555449665851D5511058555544155515122555414A0EA0C5551454265405454449081566E001696C6A755441501025415404416FD157141551555550001540A16294545C1C21440A101001559554049C0144C5555455554FA07059484D02D809684D02D40019600008000000000040000400000000800800002F40100000000640000000840400000000E3E00010000000004000000328084D02D401919998000998C800C008000C08080824241810205800000007D000000998282820219FA0000001930E08000800000C0FF3F00002C960C1000100000FA000000C40900000008008082C70019320000020032C40900C00041A002A0252600FA0000C012D9FF3F00C0FF3F000000040000 66351156 131 131 V7TLHX 0 0700011ABA2F6CD0 00 2556896716 3149753837 2918889830 3800854560 2 tlhxrac1 0 JDBC Thin Client -1748072636 0 0 3363 7230 0700011C31977308 6 N INVALID_UNAUTH 0 2018-09-12/10:10:35 N N N N 0 coe_b5s4sgqc6f9fc_2918889830 0 0 1.29638007760582E19 1.29638007760582E19 2018/9/12 10:10:36 BEDA0C1002005B98759A000203F0012003541032303138303832373030303030393239F0012003540439393939 0 0 16384 2 16384 0 0 0 650 13272 0 0 0 N 0 0 0
3 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null <CLOB> b5s4sgqc6f9fc 36931 9296 7904 0 1 1 34 18567 18567 0 18567 0 1 2018-09-11/21:03:07 0 18567 9724 0 104237 0 0 7979420 5418448 0 0 18566 3 ALL_ROWS 8 E289FB891242B700DB011D00AEF9C3E2CFFA331056414555519521105545551545545558591555449665851D5511058555544155515122555414A0EA0C5551454265405454449081566E001696C6A755441501025415404416FD157141551555550001540A16294545C1C21440A101001559554049C0144C5555455554FA07059484D02D809684D02D40019600008000000000040000400000000800800002F40100000000640000000840400000000E3E00010000000004000000328084D02D401919998000998C800C008000C08080824241810205800000007D000000998282820219FA0000001930E08000800000C0FF3F00002C960C1000100000FA000000C40900000008008082C70019320000020032C40900C00041A002A0252600FA0000C012D9FF3F00C0FF3F000000040000 66351156 131 131 V7TLHX 0 0700011ABA2F6CD0 00 2556896716 3149753837 2918889830 3800854560 6 tlhxrac1 0 JDBC Thin Client -1748072636 0 0 2720926 18034274 0700011DC26ED6E8 6 N VALID 0 2018-09-12/10:20:22 N N N Y 0 coe_b5s4sgqc6f9fc_2918889830 0 0 1.29638007760582E19 1.29638007760582E19 2018/9/12 17:58:32 BEDA0C1002005B98E18A000203F0018003541553584B323031383038333130303030303038333930F0012003540439393939 0 0 79659008 9724 79659008 0 0 0 10506 18568 0 0 0 N 0 0 0
select a.sql_text,a.sql_id,a.PLAN_HASH_VALUE,a.hash_value,a.address,a.child_number,a.old_hash_value, a. child_address from v$sql a where a.sql_id='b5s4sgqc6f9fc'
SQL_TEXT SQL_ID PLAN_HASH_VALUE HASH_VALUE ADDRESS CHILD_NUMBER OLD_HASH_VALUE CHILD_ADDRESS
1 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null b5s4sgqc6f9fc 2918889830 2556896716 0700011ABA2F6CD0 1 3149753837 07000119D29CB690
2 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null b5s4sgqc6f9fc 2918889830 2556896716 0700011ABA2F6CD0 2 3149753837 0700011C31977308
3 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null b5s4sgqc6f9fc 2918889830 2556896716 0700011ABA2F6CD0 6 3149753837 0700011DC26ED6E8
select a.sql_text,a.VERSION_COUNT,a.HASH_VALUE,a.ADDRESS, a.LAST_ACTIVE_CHILD_ADDRESS, a.OLD_HASH_VALUE,a.PLAN_HASH_VALUE,a.LOADED_VERSIONS from v$sqlarea a where a.sql_id='b5s4sgqc6f9fc';
SQL_TEXT VERSION_COUNT HASH_VALUE ADDRESS LAST_ACTIVE_CHILD_ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE LOADED_VERSIONS
1 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh=:1 and farendma=:2 and trim(translate(mingxibh,'0123456789',' ')) is null 7 2556896716 0700011ABA2F6CD0 0700011DC26ED6E8 3149753837 2918889830 3
SQL> select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: b5s4sgqc6f9fc, child number: 0 cannot be found
SQL>
SQL> select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: b5s4sgqc6f9fc, child number: 0 cannot be found
SQL> select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5s4sgqc6f9fc, child number 1
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh=:1 and farendma=:2 and
trim(translate(mingxibh,'0123456789',' ')) is null
Plan hash value: 2918889830
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| KLNL_DKKHMX | 1 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 3 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"=:1 AND "FARENDMA"=:2)
23 rows selected
select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',2));
SQL> select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',2));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b5s4sgqc6f9fc, child number 2
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh=:1 and farendma=:2 and
trim(translate(mingxibh,'0123456789',' ')) is null
Plan hash value: 2918889830
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| KLNL_DKKHMX | 1 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 3 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"=:1 AND "FARENDMA"=:2)
Note
-----
- SQL profile coe_b5s4sgqc6f9fc_2918889830 used for this statement
27 rows selected
PLAN_TABLE_OUTPUT
1 SQL_ID b5s4sgqc6f9fc, child number 6
2 -------------------------------------
3 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
4 where dkjiejuh=:1 and farendma=:2 and
5 trim(translate(mingxibh,'0123456789',' ')) is null
6
7 Plan hash value: 2918889830
8
9 --------------------------------------------------------------------------------------------------------------------------------
10 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
11 --------------------------------------------------------------------------------------------------------------------------------
12 | 0 | SELECT STATEMENT | | | | 8 (100)| | | |
13 | 1 | SORT AGGREGATE | | 1 | 50 | | | | |
14 |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| KLNL_DKKHMX | 1 | 50 | 8 (0)| 00:00:01 | ROWID | ROWID |
15 |* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX3 | 3 | | 4 (0)| 00:00:01 | | |
16 --------------------------------------------------------------------------------------------------------------------------------
17
18 Predicate Information (identified by operation id):
19 ---------------------------------------------------
20
21 2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
22 3 - access("DKJIEJUH"=:1 AND "FARENDMA"=:2)
23
24 Note
25 -----
26 - SQL profile coe_b5s4sgqc6f9fc_2918889830 used for this statement
27
select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',3));
SQL>
SQL> select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',3));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: b5s4sgqc6f9fc, child number: 3 cannot be found
select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',4));
select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',5));
select * from table(dbms_xplan.display_cursor('b5s4sgqc6f9fc',6));