Expanding SQL views out to see full SQL statement



Sometimes you see a select statement and think that will run quick - it looks like a simple bit of SQL. Then you realise it's a view (or views of of multiple views) and suddenly the explain plan is horrendous and you can sometimes have quite a difficult problem to fix.

12c introduces a new utility that will expand the SQL you pass in in to what the actual full SQL statement will look like with all the views expanded - this can easily be demonstrated by choosing one of the built in views to make the point.

Select * from v$session would appear to be a direct select from some kind of 'memory table' - but in reality it's not. The simple use of the new expand_sql_text procedure within the DBMS_UTILITY package will show us what actually is being run.

SQL> var y clob
SQL> col y format a256
SQL> set pages 0 lines 1024 long 32000
SQL> begin
  2   dbms_utility.expand_sql_text('Select * from v$session',:y);
  3  end;
  4  /

PL/SQL procedure successfully completed.



 So we can see that we have fetched the results of that procedure call into a clob variable called y - lets see what the content of that is.

SQL> print y
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_VAL
UE" "SQL_HASH_VALUE","A1"."SQL_ID" "SQL_ID","A1"."SQL_CHILD_NUMBER" "SQL_CHILD_NUMBER","A1"."SQL_EXEC_START" "SQL_EXEC_START","A
1"."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_EX
EC_ID","A1"."PLSQL_ENTRY_OBJECT_ID" "PLSQL_ENTRY_OBJECT_ID","A1"."PLSQL_ENTRY_SUBPROGRAM_ID" "PLSQL_ENTRY_SUBPROGRAM_ID","A1"."P
LSQL_OBJECT_ID" "PLSQL_OBJECT_ID","A1"."PLSQL_SUBPROGRAM_ID" "PLSQL_SUBPROGRAM_ID","A1"."MODULE" "MODULE","A1"."MODULE_HASH" "MO
DULE_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_W
AIT_BLOCK#","A1"."ROW_WAIT_ROW#" "ROW_WAIT_ROW#","A1"."TOP_LEVEL_CALL#" "TOP_LEVEL_CALL#","A1"."LOGON_TIME" "LOGON_TIME","A1"."L
AST_CALL_ET" "LAST_CALL_ET","A1"."PDML_ENABLED" "PDML_ENABLED","A1"."FAILOVER_TYPE" "FAILOVER_TYPE","A1"."FAILOVER_METHOD" "FAIL
OVER_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" "BLO
CKING_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" "SECOND
S_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"."OWNERI
D" "OWNERID","A2"."TADDR" "TADDR","A2"."LOCKWAIT" "LOCKWAIT","A2"."STATUS" "STATUS","A2"."SERVER" "SERVER","A2"."SCHEMA#" "SCHEM
A#","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" "SQ
L_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_S
QL_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_OB
JECT_ID" "PLSQL_OBJECT_ID","A2"."PLSQL_SUBPROGRAM_ID" "PLSQL_SUBPROGRAM_ID","A2"."MODULE" "MODULE","A2"."MODULE_HASH" "MODULE_HA
SH","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_BLO
CK#","A2"."ROW_WAIT_ROW#" "ROW_WAIT_ROW#","A2"."TOP_LEVEL_CALL#" "TOP_LEVEL_CALL#","A2"."LOGON_TIME" "LOGON_TIME","A2"."LAST_CAL
L_ET" "LAST_CALL_ET","A2"."PDML_ENABLED" "PDML_ENABLED","A2"."FAILOVER_TYPE" "FAILOVER_TYPE","A2"."FAILOVER_METHOD" "FAILOVER_ME
THOD","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"."CLIE
NT_IDENTIFIER" "CLIENT_IDENTIFIER","A2"."BLOCKING_SESSION_STATUS" "BLOCKING_SESSION_STATUS","A2"."BLOCKING_INSTANCE" "BLOCKING_I
NSTANCE","A2"."BLOCKING_SESSION" "BLOCKING_SESSION","A2"."FINAL_BLOCKING_SESSION_STATUS" "FINAL_BLOCKING_SESSION_STATUS","A2"."F
INAL_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","A
2"."WAIT_CLASS#" "WAIT_CLASS#","A2"."WAIT_CLASS" "WAIT_CLASS","A2"."WAIT_TIME" "WAIT_TIME","A2"."SECONDS_IN_WAIT" "SECONDS_IN_WA
IT","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_TR
ACE_WAITS" "SQL_TRACE_WAITS","A2"."SQL_TRACE_BINDS" "SQL_TRACE_BINDS","A2"."SQL_TRACE_PLAN_STATS" "SQL_TRACE_PLAN_STATS","A2"."S
ESSION_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" "CO
N_ID","A2"."EXTERNAL_NAME" "EXTERNAL_NAME" FROM  (SELECT "A5"."INST_ID" "INST_ID","A5"."ADDR" "SADDR","A5"."INDX" "SID","A5"."KS
USESER" "SERIAL#","A5"."KSUUDSES" "AUDSID","A5"."KSUSEPRO" "PADDR","A5"."KSUUDLUI" "USER#","A5"."KSUUDLNA" "USERNAME","A5"."KSUU
DOCT" "COMMAND","A5"."KSUSESOW" "OWNERID",DECODE("A5"."KSUSETRN",HEXTORAW('00'),NULL,"A5"."KSUSETRN") "TADDR",DECODE("A5"."KSQPS
WAT",HEXTORAW('00'),NULL,"A5"."KSQPSWAT") "LOCKWAIT",DECODE(BITAND("A5"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("A5"."KSUSEFLG
",4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED','KILLED') "STATUS",DECODE("A5"."KSSPATYP",1,'DEDICATED',2,'SHARED',3,'PSEUD
O',4,'POOLED','NONE') "SERVER","A5"."KSUUDSID" "SCHEMA#","A5"."KSUUDSNA" "SCHEMANAME","A5"."KSUSEUNM" "OSUSER","A5"."KSUSEPID" "
PROCESS","A5"."KSUSEMNM" "MACHINE","A5"."KSUSEMNP" "PORT","A5"."KSUSETID" "TERMINAL","A5"."KSUSEPNM" "PROGRAM",DECODE(BITAND("A5
"."KSUSEFLG",19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') "TYPE","A5"."KSUSESQL" "SQL_ADDRESS","A5"."KSUSESQH" "SQL_HASH_VALU
E","A5"."KSUSESQI" "SQL_ID",DECODE("A5"."KSUSESCH",65535,TO_NUMBER(NULL),"A5"."KSUSESCH") "SQL_CHILD_NUMBER","A5"."KSUSESESTA" "
SQL_EXEC_START",DECODE("A5"."KSUSESEID",0,TO_NUMBER(NULL),"A5"."KSUSESEID") "SQL_EXEC_ID","A5"."KSUSEPSQ" "PREV_SQL_ADDR","A5"."
KSUSEPHA" "PREV_HASH_VALUE","A5"."KSUSEPSI" "PREV_SQL_ID",DECODE("A5"."KSUSEPCH",65535,TO_NUMBER(NULL),"A5"."KSUSEPCH") "PREV_CH
ILD_NUMBER","A5"."KSUSEPESTA" "PREV_EXEC_START",DECODE("A5"."KSUSEPEID",0,TO_NUMBER(NULL),"A5"."KSUSEPEID") "PREV_EXEC_ID",DECOD
E("A5"."KSUSEPEO",0,TO_NUMBER(NULL),"A5"."KSUSEPEO") "PLSQL_ENTRY_OBJECT_ID",DECODE("A5"."KSUSEPEO",0,TO_NUMBER(NULL),"A5"."KSUS
EPES") "PLSQL_ENTRY_SUBPROGRAM_ID",DECODE("A5"."KSUSEPCO",0,TO_NUMBER(NULL),DECODE(BITAND("A5"."KSUSSTMBV",POWER(2,11)),POWER(2,
11),"A5"."KSUSEPCO",TO_NUMBER(NULL))) "PLSQL_OBJECT_ID",DECODE("A5"."KSUSEPCS",0,TO_NUMBER(NULL),DECODE(BITAND("A5"."KSUSSTMBV",
POWER(2,11)),POWER(2,11),"A5"."KSUSEPCS",TO_NUMBER(NULL))) "PLSQL_SUBPROGRAM_ID","A5"."KSUSEAPP" "MODULE","A5"."KSUSEAPH" "MODUL
E_HASH","A5"."KSUSEACT" "ACTION","A5"."KSUSEACH" "ACTION_HASH","A5"."KSUSECLI" "CLIENT_INFO","A5"."KSUSEFIX" "FIXED_TABLE_SEQUEN
CE","A5"."KSUSEOBJ" "ROW_WAIT_OBJ#","A5"."KSUSEFIL" "ROW_WAIT_FILE#","A5"."KSUSEBLK" "ROW_WAIT_BLOCK#","A5"."KSUSESLT" "ROW_WAIT
_ROW#","A5"."KSUSEORAFN" "TOP_LEVEL_CALL#","A5"."KSUSELTM" "LOGON_TIME","A5"."KSUSECTM" "LAST_CALL_ET",DECODE(BITAND("A5"."KSUSE
PXOPT",12),0,'NO','YES') "PDML_ENABLED",DECODE("A5"."KSUSEFT",2,'SESSION',4,'SELECT',8,'TRANSACTIONAL','NONE') "FAILOVER_TYPE",D
ECODE("A5"."KSUSEFM",1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE') "FAILOVER_METHOD",DECODE("A5"."KSUSEFS",1,'YES','NO') "FAILED
_OVER","A5"."KSUSEGRP" "RESOURCE_CONSUMER_GROUP",DECODE(BITAND("A5"."KSUSEPXOPT",4),4,'ENABLED',DECODE(BITAND("A5"."KSUSEPXOPT",
8),8,'FORCED','DISABLED')) "PDML_STATUS",DECODE(BITAND("A5"."KSUSEPXOPT",2),2,'FORCED',DECODE(BITAND("A5"."KSUSEPXOPT",1),1,'DIS
ABLED','ENABLED')) "PDDL_STATUS",DECODE(BITAND("A5"."KSUSEPXOPT",32),32,'FORCED',DECODE(BITAND("A5"."KSUSEPXOPT",16),16,'DISABLE
D','ENABLED')) "PQ_STATUS","A5"."KSUSECQD" "CURRENT_QUEUE_DURATION","A5"."KSUSECLID" "CLIENT_IDENTIFIER",DECODE("A5"."KSUSEBLOCK
ER",4294967295,'UNKNOWN',4294967294,'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',4294967291,'NOT IN WAIT','VALID') "BLO
CKING_SESSION_STATUS",DECODE("A5"."KSUSEBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL
),4294967292,TO_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("A5"."KSUSEBLOCKER",2147418112)/65536) "BLOCKING_INSTANCE",DECODE
("A5"."KSUSEBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL)
,4294967291,TO_NUMBER(NULL),BITAND("A5"."KSUSEBLOCKER",65535)) "BLOCKING_SESSION",DECODE("A5"."KSUSEFBLOCKER",4294967295,'UNKNOW
N',4294967294,'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER',4294967291,'NOT IN WAIT','VALID') "FINAL_BLOCKING_SESSION_ST
ATUS",DECODE("A5"."KSUSEFBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO
_NUMBER(NULL),4294967291,TO_NUMBER(NULL),BITAND("A5"."KSUSEFBLOCKER",2147418112)/65536) "FINAL_BLOCKING_INSTANCE",DECODE("A5"."K
SUSEFBLOCKER",4294967295,TO_NUMBER(NULL),4294967294,TO_NUMBER(NULL),4294967293,TO_NUMBER(NULL),4294967292,TO_NUMBER(NULL),429496
7291,TO_NUMBER(NULL),BITAND("A5"."KSUSEFBLOCKER",65535)) "FINAL_BLOCKING_SESSION","A3"."KSLWTSEQ" "SEQ#","A3"."KSLWTEVT" "EVENT#
","A4"."KSLEDNAM" "EVENT","A4"."KSLEDP1" "P1TEXT","A3"."KSLWTP1" "P1","A3"."KSLWTP1R" "P1RAW","A4"."KSLEDP2" "P2TEXT","A3"."KSLW
TP2" "P2","A3"."KSLWTP2R" "P2RAW","A4"."KSLEDP3" "P3TEXT","A3"."KSLWTP3" "P3","A3"."KSLWTP3R" "P3RAW","A4"."KSLEDCLASSID" "WAIT_
CLASS_ID","A4"."KSLEDCLASS#" "WAIT_CLASS#","A4"."KSLEDCLASS" "WAIT_CLASS",DECODE("A3"."KSLWTINWAIT",0,DECODE(BITAND("A3"."KSLWTF
LAGS",256),0,(-2),DECODE(ROUND("A3"."KSLWTSTIME"/10000),0,(-1),ROUND("A3"."KSLWTSTIME"/10000))),0) "WAIT_TIME",DECODE("A3"."KSLW
TINWAIT",0,ROUND(("A3"."KSLWTSTIME"+"A3"."KSLWTLTIME")/1000000),ROUND("A3"."KSLWTSTIME"/1000000)) "SECONDS_IN_WAIT",DECODE("A3".
"KSLWTINWAIT",1,'WAITING',DECODE(BITAND("A3"."KSLWTFLAGS",256),0,'WAITED UNKNOWN TIME',DECODE(ROUND("A3"."KSLWTSTIME"/10000),0,'
WAITED SHORT TIME','WAITED KNOWN TIME'))) "STATE","A3"."KSLWTSTIME" "WAIT_TIME_MICRO",DECODE("A3"."KSLWTINWAIT",0,TO_NUMBER(NULL
),DECODE(BITAND("A3"."KSLWTFLAGS",64),64,0,"A3"."KSLWTTREM")) "TIME_REMAINING_MICRO","A3"."KSLWTLTIME" "TIME_SINCE_LAST_WAIT_MIC
RO","A5"."KSUSESVC" "SERVICE_NAME",DECODE(BITAND("A5"."KSUSEFLG2",32),32,'ENABLED','DISABLED') "SQL_TRACE",DECODE(BITAND("A5"."K
SUSEFLG2",64),64,'TRUE','FALSE') "SQL_TRACE_WAITS",DECODE(BITAND("A5"."KSUSEFLG2",128),128,'TRUE','FALSE') "SQL_TRACE_BINDS",DEC
ODE(BITAND("A5"."KSUSEFLG2",65536)+BITAND("A5"."KSUSEFLG2",131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC') "SQL_TRACE_PL
AN_STATS","A5"."KSUUDSAE" "SESSION_EDITION_ID","A5"."KSUSECRE" "CREATOR_ADDR","A5"."KSUSECSN" "CREATOR_SERIAL#","A5"."KSUSEECID"
 "ECID","A5"."KSUUDTXLP" "SQL_TRANSLATION_PROFILE_ID",("A5"."KSUSEPGAMAN"+"A5"."KSUSEPGAAUTO")*1024 "PGA_TUNABLE_MEM","A5"."CON_
ID" "CON_ID","A5"."KSUSEXNM" "EXTERNAL_NAME" FROM SYS."X$KSUSE" "A5",SYS."X$KSLED" "A4",SYS."X$KSLWT" "A3" WHERE BITAND("A5"."KS
SPAFLG",1)<>0 AND BITAND("A5"."KSUSEFLG",1)<>0 AND "A5"."INDX"="A3"."KSLWTSID" AND "A3"."KSLWTEVT"="A4"."INDX") "A2" WHERE "A2".
"INST_ID"=USERENV('INSTANCE')) "A1"


So v$session is not what it seems. This could of course be found easily in other ways - this just shows an example. I can see it's of much more use with application views where there are multiple layers of views involved.

The formatting is also not great - now we just need dbms_utility.format_sql...........

0 comments:

Post a Comment