Expand SQL queries using dbms_utility.expand_sql_text

Oracle: 12.2.0.1
Interesting procedure which can expand your query to a more complicated structure but closer to the CBO’s one, unfortunately not the same:)

DECLARE
  vc_out CLOB;
  vc_sql CLOB;
BEGIN
  vc_sql := 'SELECT * FROM v$session';

  sys.dbms_utility.expand_sql_text(input_sql_text  => vc_sql,
                                   output_sql_text => vc_out);
  dbms_output.put_line(vc_out);
END;
/

After execution and a little code formating we can see a beautiful query based on X$KSUSE,X$KSLED,X$KSLWT tables…:)

SELECT "A1"."SADDR"                         "SADDR",
       "A1"."SID"                           "SID",
       "A1"."SERIAL#"                       "SERIAL#",
       "A1"."AUDSID"                        "AUDSID",
       "A1"."PADDR"                         "PADDR",
       "A1"."USER#"                         "USER#",
       "A1"."USERNAME"                      "USERNAME",
       "A1"."COMMAND"                       "COMMAND",
       "A1"."OWNERID"                       "OWNERID",
       "A1"."TADDR"                         "TADDR",
       "A1"."LOCKWAIT"                      "LOCKWAIT",
       "A1"."STATUS"                        "STATUS",
       "A1"."SERVER"                        "SERVER",
       "A1"."SCHEMA#"                       "SCHEMA#",
       "A1"."SCHEMANAME"                    "SCHEMANAME",
       "A1"."OSUSER"                        "OSUSER",
       "A1"."PROCESS"                       "PROCESS",
       "A1"."MACHINE"                       "MACHINE",
       "A1"."PORT"                          "PORT",
       "A1"."TERMINAL"                      "TERMINAL",
       "A1"."PROGRAM"                       "PROGRAM",
       "A1"."TYPE"                          "TYPE",
       "A1"."SQL_ADDRESS"                   "SQL_ADDRESS",
       "A1"."SQL_HASH_VALUE"                "SQL_HASH_VALUE",
       "A1"."SQL_ID"                        "SQL_ID",
       "A1"."SQL_CHILD_NUMBER"              "SQL_CHILD_NUMBER",
       "A1"."SQL_EXEC_START"                "SQL_EXEC_START",
       "A1"."SQL_EXEC_ID"                   "SQL_EXEC_ID",
       "A1"."PREV_SQL_ADDR"                 "PREV_SQL_ADDR",
       "A1"."PREV_HASH_VALUE"               "PREV_HASH_VALUE",
       "A1"."PREV_SQL_ID"                   "PREV_SQL_ID",
       "A1"."PREV_CHILD_NUMBER"             "PREV_CHILD_NUMBER",
       "A1"."PREV_EXEC_START"               "PREV_EXEC_START",
       "A1"."PREV_EXEC_ID"                  "PREV_EXEC_ID",
       "A1"."PLSQL_ENTRY_OBJECT_ID"         "PLSQL_ENTRY_OBJECT_ID",
       "A1"."PLSQL_ENTRY_SUBPROGRAM_ID"     "PLSQL_ENTRY_SUBPROGRAM_ID",
       "A1"."PLSQL_OBJECT_ID"               "PLSQL_OBJECT_ID",
       "A1"."PLSQL_SUBPROGRAM_ID"           "PLSQL_SUBPROGRAM_ID",
       "A1"."MODULE"                        "MODULE",
       "A1"."MODULE_HASH"                   "MODULE_HASH",
       "A1"."ACTION"                        "ACTION",
       "A1"."ACTION_HASH"                   "ACTION_HASH",
       "A1"."CLIENT_INFO"                   "CLIENT_INFO",
       "A1"."FIXED_TABLE_SEQUENCE"          "FIXED_TABLE_SEQUENCE",
       "A1"."ROW_WAIT_OBJ#"                 "ROW_WAIT_OBJ#",
       "A1"."ROW_WAIT_FILE#"                "ROW_WAIT_FILE#",
       "A1"."ROW_WAIT_BLOCK#"               "ROW_WAIT_BLOCK#",
       "A1"."ROW_WAIT_ROW#"                 "ROW_WAIT_ROW#",
       "A1"."TOP_LEVEL_CALL#"               "TOP_LEVEL_CALL#",
       "A1"."LOGON_TIME"                    "LOGON_TIME",
       "A1"."LAST_CALL_ET"                  "LAST_CALL_ET",
       "A1"."PDML_ENABLED"                  "PDML_ENABLED",
       "A1"."FAILOVER_TYPE"                 "FAILOVER_TYPE",
       "A1"."FAILOVER_METHOD"               "FAILOVER_METHOD",
       "A1"."FAILED_OVER"                   "FAILED_OVER",
       "A1"."RESOURCE_CONSUMER_GROUP"       "RESOURCE_CONSUMER_GROUP",
       "A1"."PDML_STATUS"                   "PDML_STATUS",
       "A1"."PDDL_STATUS"                   "PDDL_STATUS",
       "A1"."PQ_STATUS"                     "PQ_STATUS",
       "A1"."CURRENT_QUEUE_DURATION"        "CURRENT_QUEUE_DURATION",
       "A1"."CLIENT_IDENTIFIER"             "CLIENT_IDENTIFIER",
       "A1"."BLOCKING_SESSION_STATUS"       "BLOCKING_SESSION_STATUS",
       "A1"."BLOCKING_INSTANCE"             "BLOCKING_INSTANCE",
       "A1"."BLOCKING_SESSION"              "BLOCKING_SESSION",
       "A1"."FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_SESSION_STATUS",
       "A1"."FINAL_BLOCKING_INSTANCE"       "FINAL_BLOCKING_INSTANCE",
       "A1"."FINAL_BLOCKING_SESSION"        "FINAL_BLOCKING_SESSION",
       "A1"."SEQ#"                          "SEQ#",
       "A1"."EVENT#"                        "EVENT#",
       "A1"."EVENT"                         "EVENT",
       "A1"."P1TEXT"                        "P1TEXT",
       "A1"."P1"                            "P1",
       "A1"."P1RAW"                         "P1RAW",
       "A1"."P2TEXT"                        "P2TEXT",
       "A1"."P2"                            "P2",
       "A1"."P2RAW"                         "P2RAW",
       "A1"."P3TEXT"                        "P3TEXT",
       "A1"."P3"                            "P3",
       "A1"."P3RAW"                         "P3RAW",
       "A1"."WAIT_CLASS_ID"                 "WAIT_CLASS_ID",
       "A1"."WAIT_CLASS#"                   "WAIT_CLASS#",
       "A1"."WAIT_CLASS"                    "WAIT_CLASS",
       "A1"."WAIT_TIME"                     "WAIT_TIME",
       "A1"."SECONDS_IN_WAIT"               "SECONDS_IN_WAIT",
       "A1"."STATE"                         "STATE",
       "A1"."WAIT_TIME_MICRO"               "WAIT_TIME_MICRO",
       "A1"."TIME_REMAINING_MICRO"          "TIME_REMAINING_MICRO",
       "A1"."TIME_SINCE_LAST_WAIT_MICRO"    "TIME_SINCE_LAST_WAIT_MICRO",
       "A1"."SERVICE_NAME"                  "SERVICE_NAME",
       "A1"."SQL_TRACE"                     "SQL_TRACE",
       "A1"."SQL_TRACE_WAITS"               "SQL_TRACE_WAITS",
       "A1"."SQL_TRACE_BINDS"               "SQL_TRACE_BINDS",
       "A1"."SQL_TRACE_PLAN_STATS"          "SQL_TRACE_PLAN_STATS",
       "A1"."SESSION_EDITION_ID"            "SESSION_EDITION_ID",
       "A1"."CREATOR_ADDR"                  "CREATOR_ADDR",
       "A1"."CREATOR_SERIAL#"               "CREATOR_SERIAL#",
       "A1"."ECID"                          "ECID",
       "A1"."SQL_TRANSLATION_PROFILE_ID"    "SQL_TRANSLATION_PROFILE_ID",
       "A1"."PGA_TUNABLE_MEM"               "PGA_TUNABLE_MEM",
       "A1"."CON_ID"                        "CON_ID",
       "A1"."EXTERNAL_NAME"                 "EXTERNAL_NAME"
  FROM (SELECT "A2"."SADDR"                         "SADDR",
               "A2"."SID"                           "SID",
               "A2"."SERIAL#"                       "SERIAL#",
               "A2"."AUDSID"                        "AUDSID",
               "A2"."PADDR"                         "PADDR",
               "A2"."USER#"                         "USER#",
               "A2"."USERNAME"                      "USERNAME",
               "A2"."COMMAND"                       "COMMAND",
               "A2"."OWNERID"                       "OWNERID",
               "A2"."TADDR"                         "TADDR",
               "A2"."LOCKWAIT"                      "LOCKWAIT",
               "A2"."STATUS"                        "STATUS",
               "A2"."SERVER"                        "SERVER",
               "A2"."SCHEMA#"                       "SCHEMA#",
               "A2"."SCHEMANAME"                    "SCHEMANAME",
               "A2"."OSUSER"                        "OSUSER",
               "A2"."PROCESS"                       "PROCESS",
               "A2"."MACHINE"                       "MACHINE",
               "A2"."PORT"                          "PORT",
               "A2"."TERMINAL"                      "TERMINAL",
               "A2"."PROGRAM"                       "PROGRAM",
               "A2"."TYPE"                          "TYPE",
               "A2"."SQL_ADDRESS"                   "SQL_ADDRESS",
               "A2"."SQL_HASH_VALUE"                "SQL_HASH_VALUE",
               "A2"."SQL_ID"                        "SQL_ID",
               "A2"."SQL_CHILD_NUMBER"              "SQL_CHILD_NUMBER",
               "A2"."SQL_EXEC_START"                "SQL_EXEC_START",
               "A2"."SQL_EXEC_ID"                   "SQL_EXEC_ID",
               "A2"."PREV_SQL_ADDR"                 "PREV_SQL_ADDR",
               "A2"."PREV_HASH_VALUE"               "PREV_HASH_VALUE",
               "A2"."PREV_SQL_ID"                   "PREV_SQL_ID",
               "A2"."PREV_CHILD_NUMBER"             "PREV_CHILD_NUMBER",
               "A2"."PREV_EXEC_START"               "PREV_EXEC_START",
               "A2"."PREV_EXEC_ID"                  "PREV_EXEC_ID",
               "A2"."PLSQL_ENTRY_OBJECT_ID"         "PLSQL_ENTRY_OBJECT_ID",
               "A2"."PLSQL_ENTRY_SUBPROGRAM_ID"     "PLSQL_ENTRY_SUBPROGRAM_ID",
               "A2"."PLSQL_OBJECT_ID"               "PLSQL_OBJECT_ID",
               "A2"."PLSQL_SUBPROGRAM_ID"           "PLSQL_SUBPROGRAM_ID",
               "A2"."MODULE"                        "MODULE",
               "A2"."MODULE_HASH"                   "MODULE_HASH",
               "A2"."ACTION"                        "ACTION",
               "A2"."ACTION_HASH"                   "ACTION_HASH",
               "A2"."CLIENT_INFO"                   "CLIENT_INFO",
               "A2"."FIXED_TABLE_SEQUENCE"          "FIXED_TABLE_SEQUENCE",
               "A2"."ROW_WAIT_OBJ#"                 "ROW_WAIT_OBJ#",
               "A2"."ROW_WAIT_FILE#"                "ROW_WAIT_FILE#",
               "A2"."ROW_WAIT_BLOCK#"               "ROW_WAIT_BLOCK#",
               "A2"."ROW_WAIT_ROW#"                 "ROW_WAIT_ROW#",
               "A2"."TOP_LEVEL_CALL#"               "TOP_LEVEL_CALL#",
               "A2"."LOGON_TIME"                    "LOGON_TIME",
               "A2"."LAST_CALL_ET"                  "LAST_CALL_ET",
               "A2"."PDML_ENABLED"                  "PDML_ENABLED",
               "A2"."FAILOVER_TYPE"                 "FAILOVER_TYPE",
               "A2"."FAILOVER_METHOD"               "FAILOVER_METHOD",
               "A2"."FAILED_OVER"                   "FAILED_OVER",
               "A2"."RESOURCE_CONSUMER_GROUP"       "RESOURCE_CONSUMER_GROUP",
               "A2"."PDML_STATUS"                   "PDML_STATUS",
               "A2"."PDDL_STATUS"                   "PDDL_STATUS",
               "A2"."PQ_STATUS"                     "PQ_STATUS",
               "A2"."CURRENT_QUEUE_DURATION"        "CURRENT_QUEUE_DURATION",
               "A2"."CLIENT_IDENTIFIER"             "CLIENT_IDENTIFIER",
               "A2"."BLOCKING_SESSION_STATUS"       "BLOCKING_SESSION_STATUS",
               "A2"."BLOCKING_INSTANCE"             "BLOCKING_INSTANCE",
               "A2"."BLOCKING_SESSION"              "BLOCKING_SESSION",
               "A2"."FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_SESSION_STATUS",
               "A2"."FINAL_BLOCKING_INSTANCE"       "FINAL_BLOCKING_INSTANCE",
               "A2"."FINAL_BLOCKING_SESSION"        "FINAL_BLOCKING_SESSION",
               "A2"."SEQ#"                          "SEQ#",
               "A2"."EVENT#"                        "EVENT#",
               "A2"."EVENT"                         "EVENT",
               "A2"."P1TEXT"                        "P1TEXT",
               "A2"."P1"                            "P1",
               "A2"."P1RAW"                         "P1RAW",
               "A2"."P2TEXT"                        "P2TEXT",
               "A2"."P2"                            "P2",
               "A2"."P2RAW"                         "P2RAW",
               "A2"."P3TEXT"                        "P3TEXT",
               "A2"."P3"                            "P3",
               "A2"."P3RAW"                         "P3RAW",
               "A2"."WAIT_CLASS_ID"                 "WAIT_CLASS_ID",
               "A2"."WAIT_CLASS#"                   "WAIT_CLASS#",
               "A2"."WAIT_CLASS"                    "WAIT_CLASS",
               "A2"."WAIT_TIME"                     "WAIT_TIME",
               "A2"."SECONDS_IN_WAIT"               "SECONDS_IN_WAIT",
               "A2"."STATE"                         "STATE",
               "A2"."WAIT_TIME_MICRO"               "WAIT_TIME_MICRO",
               "A2"."TIME_REMAINING_MICRO"          "TIME_REMAINING_MICRO",
               "A2"."TIME_SINCE_LAST_WAIT_MICRO"    "TIME_SINCE_LAST_WAIT_MICRO",
               "A2"."SERVICE_NAME"                  "SERVICE_NAME",
               "A2"."SQL_TRACE"                     "SQL_TRACE",
               "A2"."SQL_TRACE_WAITS"               "SQL_TRACE_WAITS",
               "A2"."SQL_TRACE_BINDS"               "SQL_TRACE_BINDS",
               "A2"."SQL_TRACE_PLAN_STATS"          "SQL_TRACE_PLAN_STATS",
               "A2"."SESSION_EDITION_ID"            "SESSION_EDITION_ID",
               "A2"."CREATOR_ADDR"                  "CREATOR_ADDR",
               "A2"."CREATOR_SERIAL#"               "CREATOR_SERIAL#",
               "A2"."ECID"                          "ECID",
               "A2"."SQL_TRANSLATION_PROFILE_ID"    "SQL_TRANSLATION_PROFILE_ID",
               "A2"."PGA_TUNABLE_MEM"               "PGA_TUNABLE_MEM",
               "A2"."CON_ID"                        "CON_ID",
               "A2"."EXTERNAL_NAME"                 "EXTERNAL_NAME"
          FROM (SELECT "A3"."SADDR"                         "SADDR",
                       "A3"."SID"                           "SID",
                       "A3"."SERIAL#"                       "SERIAL#",
                       "A3"."AUDSID"                        "AUDSID",
                       "A3"."PADDR"                         "PADDR",
                       "A3"."USER#"                         "USER#",
                       "A3"."USERNAME"                      "USERNAME",
                       "A3"."COMMAND"                       "COMMAND",
                       "A3"."OWNERID"                       "OWNERID",
                       "A3"."TADDR"                         "TADDR",
                       "A3"."LOCKWAIT"                      "LOCKWAIT",
                       "A3"."STATUS"                        "STATUS",
                       "A3"."SERVER"                        "SERVER",
                       "A3"."SCHEMA#"                       "SCHEMA#",
                       "A3"."SCHEMANAME"                    "SCHEMANAME",
                       "A3"."OSUSER"                        "OSUSER",
                       "A3"."PROCESS"                       "PROCESS",
                       "A3"."MACHINE"                       "MACHINE",
                       "A3"."PORT"                          "PORT",
                       "A3"."TERMINAL"                      "TERMINAL",
                       "A3"."PROGRAM"                       "PROGRAM",
                       "A3"."TYPE"                          "TYPE",
                       "A3"."SQL_ADDRESS"                   "SQL_ADDRESS",
                       "A3"."SQL_HASH_VALUE"                "SQL_HASH_VALUE",
                       "A3"."SQL_ID"                        "SQL_ID",
                       "A3"."SQL_CHILD_NUMBER"              "SQL_CHILD_NUMBER",
                       "A3"."SQL_EXEC_START"                "SQL_EXEC_START",
                       "A3"."SQL_EXEC_ID"                   "SQL_EXEC_ID",
                       "A3"."PREV_SQL_ADDR"                 "PREV_SQL_ADDR",
                       "A3"."PREV_HASH_VALUE"               "PREV_HASH_VALUE",
                       "A3"."PREV_SQL_ID"                   "PREV_SQL_ID",
                       "A3"."PREV_CHILD_NUMBER"             "PREV_CHILD_NUMBER",
                       "A3"."PREV_EXEC_START"               "PREV_EXEC_START",
                       "A3"."PREV_EXEC_ID"                  "PREV_EXEC_ID",
                       "A3"."PLSQL_ENTRY_OBJECT_ID"         "PLSQL_ENTRY_OBJECT_ID",
                       "A3"."PLSQL_ENTRY_SUBPROGRAM_ID"     "PLSQL_ENTRY_SUBPROGRAM_ID",
                       "A3"."PLSQL_OBJECT_ID"               "PLSQL_OBJECT_ID",
                       "A3"."PLSQL_SUBPROGRAM_ID"           "PLSQL_SUBPROGRAM_ID",
                       "A3"."MODULE"                        "MODULE",
                       "A3"."MODULE_HASH"                   "MODULE_HASH",
                       "A3"."ACTION"                        "ACTION",
                       "A3"."ACTION_HASH"                   "ACTION_HASH",
                       "A3"."CLIENT_INFO"                   "CLIENT_INFO",
                       "A3"."FIXED_TABLE_SEQUENCE"          "FIXED_TABLE_SEQUENCE",
                       "A3"."ROW_WAIT_OBJ#"                 "ROW_WAIT_OBJ#",
                       "A3"."ROW_WAIT_FILE#"                "ROW_WAIT_FILE#",
                       "A3"."ROW_WAIT_BLOCK#"               "ROW_WAIT_BLOCK#",
                       "A3"."ROW_WAIT_ROW#"                 "ROW_WAIT_ROW#",
                       "A3"."TOP_LEVEL_CALL#"               "TOP_LEVEL_CALL#",
                       "A3"."LOGON_TIME"                    "LOGON_TIME",
                       "A3"."LAST_CALL_ET"                  "LAST_CALL_ET",
                       "A3"."PDML_ENABLED"                  "PDML_ENABLED",
                       "A3"."FAILOVER_TYPE"                 "FAILOVER_TYPE",
                       "A3"."FAILOVER_METHOD"               "FAILOVER_METHOD",
                       "A3"."FAILED_OVER"                   "FAILED_OVER",
                       "A3"."RESOURCE_CONSUMER_GROUP"       "RESOURCE_CONSUMER_GROUP",
                       "A3"."PDML_STATUS"                   "PDML_STATUS",
                       "A3"."PDDL_STATUS"                   "PDDL_STATUS",
                       "A3"."PQ_STATUS"                     "PQ_STATUS",
                       "A3"."CURRENT_QUEUE_DURATION"        "CURRENT_QUEUE_DURATION",
                       "A3"."CLIENT_IDENTIFIER"             "CLIENT_IDENTIFIER",
                       "A3"."BLOCKING_SESSION_STATUS"       "BLOCKING_SESSION_STATUS",
                       "A3"."BLOCKING_INSTANCE"             "BLOCKING_INSTANCE",
                       "A3"."BLOCKING_SESSION"              "BLOCKING_SESSION",
                       "A3"."FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_SESSION_STATUS",
                       "A3"."FINAL_BLOCKING_INSTANCE"       "FINAL_BLOCKING_INSTANCE",
                       "A3"."FINAL_BLOCKING_SESSION"        "FINAL_BLOCKING_SESSION",
                       "A3"."SEQ#"                          "SEQ#",
                       "A3"."EVENT#"                        "EVENT#",
                       "A3"."EVENT"                         "EVENT",
                       "A3"."P1TEXT"                        "P1TEXT",
                       "A3"."P1"                            "P1",
                       "A3"."P1RAW"                         "P1RAW",
                       "A3"."P2TEXT"                        "P2TEXT",
                       "A3"."P2"                            "P2",
                       "A3"."P2RAW"                         "P2RAW",
                       "A3"."P3TEXT"                        "P3TEXT",
                       "A3"."P3"                            "P3",
                       "A3"."P3RAW"                         "P3RAW",
                       "A3"."WAIT_CLASS_ID"                 "WAIT_CLASS_ID",
                       "A3"."WAIT_CLASS#"                   "WAIT_CLASS#",
                       "A3"."WAIT_CLASS"                    "WAIT_CLASS",
                       "A3"."WAIT_TIME"                     "WAIT_TIME",
                       "A3"."SECONDS_IN_WAIT"               "SECONDS_IN_WAIT",
                       "A3"."STATE"                         "STATE",
                       "A3"."WAIT_TIME_MICRO"               "WAIT_TIME_MICRO",
                       "A3"."TIME_REMAINING_MICRO"          "TIME_REMAINING_MICRO",
                       "A3"."TIME_SINCE_LAST_WAIT_MICRO"    "TIME_SINCE_LAST_WAIT_MICRO",
                       "A3"."SERVICE_NAME"                  "SERVICE_NAME",
                       "A3"."SQL_TRACE"                     "SQL_TRACE",
                       "A3"."SQL_TRACE_WAITS"               "SQL_TRACE_WAITS",
                       "A3"."SQL_TRACE_BINDS"               "SQL_TRACE_BINDS",
                       "A3"."SQL_TRACE_PLAN_STATS"          "SQL_TRACE_PLAN_STATS",
                       "A3"."SESSION_EDITION_ID"            "SESSION_EDITION_ID",
                       "A3"."CREATOR_ADDR"                  "CREATOR_ADDR",
                       "A3"."CREATOR_SERIAL#"               "CREATOR_SERIAL#",
                       "A3"."ECID"                          "ECID",
                       "A3"."SQL_TRANSLATION_PROFILE_ID"    "SQL_TRANSLATION_PROFILE_ID",
                       "A3"."PGA_TUNABLE_MEM"               "PGA_TUNABLE_MEM",
                       "A3"."CON_ID"                        "CON_ID",
                       "A3"."EXTERNAL_NAME"                 "EXTERNAL_NAME"
                  FROM (SELECT "A6"."INST_ID" "INST_ID",
                               "A6"."ADDR" "SADDR",
                               "A6"."INDX" "SID",
                               "A6"."KSUSESER" "SERIAL#",
                               "A6"."KSUUDSES" "AUDSID",
                               "A6"."KSUSEPRO" "PADDR",
                               "A6"."KSUUDLUI" "USER#",
                               "A6"."KSUUDLNA" "USERNAME",
                               "A6"."KSUUDOCT" "COMMAND",
                               "A6"."KSUSESOW" "OWNERID",
                               decode("A6"."KSUSETRN",
                                      hextoraw('00'),
                                      NULL,
                                      "A6"."KSUSETRN") "TADDR",
                               decode("A6"."KSQPSWAT",
                                      hextoraw('00'),
                                      NULL,
                                      "A6"."KSQPSWAT") "LOCKWAIT",
                               decode(bitand("A6"."KSUSEIDL",
                                             11),
                                      1,
                                      'ACTIVE',
                                      0,
                                      decode(bitand("A6"."KSUSEFLG",
                                                    4096),
                                             0,
                                             'INACTIVE',
                                             'CACHED'),
                                      2,
                                      'SNIPED',
                                      3,
                                      'SNIPED',
                                      'KILLED') "STATUS",
                               decode("A6"."KSSPATYP",
                                      1,
                                      'DEDICATED',
                                      2,
                                      'SHARED',
                                      3,
                                      'PSEUDO',
                                      4,
                                      'POOLED',
                                      'NONE') "SERVER",
                               "A6"."KSUUDSID" "SCHEMA#",
                               "A6"."KSUUDSNA" "SCHEMANAME",
                               "A6"."KSUSEUNM" "OSUSER",
                               "A6"."KSUSEPID" "PROCESS",
                               "A6"."KSUSEMNM" "MACHINE",
                               "A6"."KSUSEMNP" "PORT",
                               "A6"."KSUSETID" "TERMINAL",
                               "A6"."KSUSEPNM" "PROGRAM",
                               decode(bitand("A6"."KSUSEFLG",
                                             19),
                                      17,
                                      'BACKGROUND',
                                      1,
                                      'USER',
                                      2,
                                      'RECURSIVE',
                                      '?') "TYPE",
                               "A6"."KSUSESQL" "SQL_ADDRESS",
                               "A6"."KSUSESQH" "SQL_HASH_VALUE",
                               "A6"."KSUSESQI" "SQL_ID",
                               decode("A6"."KSUSESCH",
                                      65535,
                                      to_number(NULL),
                                      "A6"."KSUSESCH") "SQL_CHILD_NUMBER",
                               "A6"."KSUSESESTA" "SQL_EXEC_START",
                               decode("A6"."KSUSESEID",
                                      0,
                                      to_number(NULL),
                                      "A6"."KSUSESEID") "SQL_EXEC_ID",
                               "A6"."KSUSEPSQ" "PREV_SQL_ADDR",
                               "A6"."KSUSEPHA" "PREV_HASH_VALUE",
                               "A6"."KSUSEPSI" "PREV_SQL_ID",
                               decode("A6"."KSUSEPCH",
                                      65535,
                                      to_number(NULL),
                                      "A6"."KSUSEPCH") "PREV_CHILD_NUMBER",
                               "A6"."KSUSEPESTA" "PREV_EXEC_START",
                               decode("A6"."KSUSEPEID",
                                      0,
                                      to_number(NULL),
                                      "A6"."KSUSEPEID") "PREV_EXEC_ID",
                               decode("A6"."KSUSEPEO",
                                      0,
                                      to_number(NULL),
                                      "A6"."KSUSEPEO") "PLSQL_ENTRY_OBJECT_ID",
                               decode("A6"."KSUSEPEO",
                                      0,
                                      to_number(NULL),
                                      "A6"."KSUSEPES") "PLSQL_ENTRY_SUBPROGRAM_ID",
                               decode("A6"."KSUSEPCO",
                                      0,
                                      to_number(NULL),
                                      decode(bitand("A6"."KSUSSTMBV",
                                                    power(2,
                                                          11)),
                                             power(2,
                                                   11),
                                             "A6"."KSUSEPCO",
                                             to_number(NULL))) "PLSQL_OBJECT_ID",
                               decode("A6"."KSUSEPCS",
                                      0,
                                      to_number(NULL),
                                      decode(bitand("A6"."KSUSSTMBV",
                                                    power(2,
                                                          11)),
                                             power(2,
                                                   11),
                                             "A6"."KSUSEPCS",
                                             to_number(NULL))) "PLSQL_SUBPROGRAM_ID",
                               "A6"."KSUSEAPP" "MODULE",
                               "A6"."KSUSEAPH" "MODULE_HASH",
                               "A6"."KSUSEACT" "ACTION",
                               "A6"."KSUSEACH" "ACTION_HASH",
                               "A6"."KSUSECLI" "CLIENT_INFO",
                               "A6"."KSUSEFIX" "FIXED_TABLE_SEQUENCE",
                               "A6"."KSUSEOBJ" "ROW_WAIT_OBJ#",
                               "A6"."KSUSEFIL" "ROW_WAIT_FILE#",
                               "A6"."KSUSEBLK" "ROW_WAIT_BLOCK#",
                               "A6"."KSUSESLT" "ROW_WAIT_ROW#",
                               "A6"."KSUSEORAFN" "TOP_LEVEL_CALL#",
                               "A6"."KSUSELTM" "LOGON_TIME",
                               "A6"."KSUSECTM" "LAST_CALL_ET",
                               decode(bitand("A6"."KSUSEPXOPT",
                                             12),
                                      0,
                                      'NO',
                                      'YES') "PDML_ENABLED",
                               decode("A6"."KSUSEFT",
                                      2,
                                      'SESSION',
                                      4,
                                      'SELECT',
                                      8,
                                      'TRANSACTIONAL',
                                      'NONE') "FAILOVER_TYPE",
                               decode("A6"."KSUSEFM",
                                      1,
                                      'BASIC',
                                      2,
                                      'PRECONNECT',
                                      4,
                                      'PREPARSE',
                                      'NONE') "FAILOVER_METHOD",
                               decode("A6"."KSUSEFS",
                                      1,
                                      'YES',
                                      'NO') "FAILED_OVER",
                               "A6"."KSUSEGRP" "RESOURCE_CONSUMER_GROUP",
                               decode(bitand("A6"."KSUSEPXOPT",
                                             4),
                                      4,
                                      'ENABLED',
                                      decode(bitand("A6"."KSUSEPXOPT",
                                                    8),
                                             8,
                                             'FORCED',
                                             'DISABLED')) "PDML_STATUS",
                               decode(bitand("A6"."KSUSEPXOPT",
                                             2),
                                      2,
                                      'FORCED',
                                      decode(bitand("A6"."KSUSEPXOPT",
                                                    1),
                                             1,
                                             'DISABLED',
                                             'ENABLED')) "PDDL_STATUS",
                               decode(bitand("A6"."KSUSEPXOPT",
                                             32),
                                      32,
                                      'FORCED',
                                      decode(bitand("A6"."KSUSEPXOPT",
                                                    16),
                                             16,
                                             'DISABLED',
                                             'ENABLED')) "PQ_STATUS",
                               "A6"."KSUSECQD" "CURRENT_QUEUE_DURATION",
                               "A6"."KSUSECLID" "CLIENT_IDENTIFIER",
                               decode("A6"."KSUSEBLOCKER",
                                      4294967295,
                                      'UNKNOWN',
                                      4294967294,
                                      'UNKNOWN',
                                      4294967293,
                                      'UNKNOWN',
                                      4294967292,
                                      'NO HOLDER',
                                      4294967291,
                                      'NOT IN WAIT',
                                      'VALID') "BLOCKING_SESSION_STATUS",
                               decode("A6"."KSUSEBLOCKER",
                                      4294967295,
                                      to_number(NULL),
                                      4294967294,
                                      to_number(NULL),
                                      4294967293,
                                      to_number(NULL),
                                      4294967292,
                                      to_number(NULL),
                                      4294967291,
                                      to_number(NULL),
                                      bitand("A6"."KSUSEBLOCKER",
                                             2147418112) / 65536) "BLOCKING_INSTANCE",
                               decode("A6"."KSUSEBLOCKER",
                                      4294967295,
                                      to_number(NULL),
                                      4294967294,
                                      to_number(NULL),
                                      4294967293,
                                      to_number(NULL),
                                      4294967292,
                                      to_number(NULL),
                                      4294967291,
                                      to_number(NULL),
                                      bitand("A6"."KSUSEBLOCKER",
                                             65535)) "BLOCKING_SESSION",
                               decode("A6"."KSUSEFBLOCKER",
                                      4294967295,
                                      'UNKNOWN',
                                      4294967294,
                                      'UNKNOWN',
                                      4294967293,
                                      'UNKNOWN',
                                      4294967292,
                                      'NO HOLDER',
                                      4294967291,
                                      'NOT IN WAIT',
                                      'VALID') "FINAL_BLOCKING_SESSION_STATUS",
                               decode("A6"."KSUSEFBLOCKER",
                                      4294967295,
                                      to_number(NULL),
                                      4294967294,
                                      to_number(NULL),
                                      4294967293,
                                      to_number(NULL),
                                      4294967292,
                                      to_number(NULL),
                                      4294967291,
                                      to_number(NULL),
                                      bitand("A6"."KSUSEFBLOCKER",
                                             2147418112) / 65536) "FINAL_BLOCKING_INSTANCE",
                               decode("A6"."KSUSEFBLOCKER",
                                      4294967295,
                                      to_number(NULL),
                                      4294967294,
                                      to_number(NULL),
                                      4294967293,
                                      to_number(NULL),
                                      4294967292,
                                      to_number(NULL),
                                      4294967291,
                                      to_number(NULL),
                                      bitand("A6"."KSUSEFBLOCKER",
                                             65535)) "FINAL_BLOCKING_SESSION",
                               "A4"."KSLWTSEQ" "SEQ#",
                               "A4"."KSLWTEVT" "EVENT#",
                               "A5"."KSLEDNAM" "EVENT",
                               "A5"."KSLEDP1" "P1TEXT",
                               "A4"."KSLWTP1" "P1",
                               "A4"."KSLWTP1R" "P1RAW",
                               "A5"."KSLEDP2" "P2TEXT",
                               "A4"."KSLWTP2" "P2",
                               "A4"."KSLWTP2R" "P2RAW",
                               "A5"."KSLEDP3" "P3TEXT",
                               "A4"."KSLWTP3" "P3",
                               "A4"."KSLWTP3R" "P3RAW",
                               "A5"."KSLEDCLASSID" "WAIT_CLASS_ID",
                               "A5"."KSLEDCLASS#" "WAIT_CLASS#",
                               "A5"."KSLEDCLASS" "WAIT_CLASS",
                               decode("A4"."KSLWTINWAIT",
                                      0,
                                      decode(bitand("A4"."KSLWTFLAGS",
                                                    256),
                                             0,
                                             (-2),
                                             decode(round("A4"."KSLWTSTIME" / 10000),
                                                    0,
                                                    (-1),
                                                    round("A4"."KSLWTSTIME" / 10000))),
                                      0) "WAIT_TIME",
                               decode("A4"."KSLWTINWAIT",
                                      0,
                                      round(("A4"."KSLWTSTIME" + "A4"."KSLWTLTIME") / 1000000),
                                      round("A4"."KSLWTSTIME" / 1000000)) "SECONDS_IN_WAIT",
                               decode("A4"."KSLWTINWAIT",
                                      1,
                                      'WAITING',
                                      decode(bitand("A4"."KSLWTFLAGS",
                                                    256),
                                             0,
                                             'WAITED UNKNOWN TIME',
                                             decode(round("A4"."KSLWTSTIME" / 10000),
                                                    0,
                                                    'WAITED SHORT TIME',
                                                    'WAITED KNOWN TIME'))) "STATE",
                               "A4"."KSLWTSTIME" "WAIT_TIME_MICRO",
                               decode("A4"."KSLWTINWAIT",
                                      0,
                                      to_number(NULL),
                                      decode(bitand("A4"."KSLWTFLAGS",
                                                    64),
                                             64,
                                             0,
                                             "A4"."KSLWTTREM")) "TIME_REMAINING_MICRO",
                               "A4"."KSLWTLTIME" "TIME_SINCE_LAST_WAIT_MICRO",
                               "A6"."KSUSESVC" "SERVICE_NAME",
                               decode(bitand("A6"."KSUSEFLG2",
                                             32),
                                      32,
                                      'ENABLED',
                                      'DISABLED') "SQL_TRACE",
                               decode(bitand("A6"."KSUSEFLG2",
                                             64),
                                      64,
                                      'TRUE',
                                      'FALSE') "SQL_TRACE_WAITS",
                               decode(bitand("A6"."KSUSEFLG2",
                                             128),
                                      128,
                                      'TRUE',
                                      'FALSE') "SQL_TRACE_BINDS",
                               decode(bitand("A6"."KSUSEFLG2",
                                             65536) + bitand("A6"."KSUSEFLG2",
                                                             131072),
                                      65536,
                                      'ALL EXEC',
                                      131072,
                                      'NEVER',
                                      0,
                                      'FIRST EXEC') "SQL_TRACE_PLAN_STATS",
                               "A6"."KSUUDSAE" "SESSION_EDITION_ID",
                               "A6"."KSUSECRE" "CREATOR_ADDR",
                               "A6"."KSUSECSN" "CREATOR_SERIAL#",
                               "A6"."KSUSEECID" "ECID",
                               "A6"."KSUUDTXLP" "SQL_TRANSLATION_PROFILE_ID",
                               ("A6"."KSUSEPGAMAN" + "A6"."KSUSEPGAAUTO") * 1024 "PGA_TUNABLE_MEM",
                               "A6"."CON_ID" "CON_ID",
                               "A6"."KSUSEXNM" "EXTERNAL_NAME"
                          FROM SYS."X$KSUSE" "A6",
                               SYS."X$KSLED" "A5",
                               SYS."X$KSLWT" "A4"
                         WHERE bitand("A6"."KSSPAFLG",
                                      1) <> 0
                           AND bitand("A6"."KSUSEFLG",
                                      1) <> 0
                           AND "A6"."INDX" = "A4"."KSLWTSID"
                           AND "A4"."KSLWTEVT" = "A5"."INDX") "A3"
                 WHERE "A3"."INST_ID" = userenv('INSTANCE')) "A2") "A1"

Leave a Reply

Be the First to Comment!

avatar
wpDiscuz