Hi everybody !
I am trying to write a query that shows me what queries were executed on the server. I need to get the queries in the exact order they have been executed. It's a kind of profiler. To do it, I use the V$SQLAREA view.
The problem is that I am not able to sort the queries in the exact order they execute because I use the LAST_ACTIVE_TIME column in the ORDER BY. But the LAST_ACTIVE_TIME is a "date" type. Because of it, when queries are executed within the same second, they appear in the wrong order. Actually, If queries had a kind of counter, it would be good, but it's not the case.
Here is the code :
SELECT last_active_time,
S.SQL_FULLTEXT,
S.HASH_VALUE hash,
P.NAME,
P.VALUE_STRING,
s.parsing_schema_name,
NVL(v.module, s.module) module,
ROUND(S.ELAPSED_TIME/DECODE(executions,0,1,executions)/1000000,3) SEC_EXEC,
V.OSUSER ,
V.MACHINE ,
S.EXECUTIONS,
NVL(o.object_name,'') OBJECT_NAME
FROM (V$SQLAREA s
LEFT JOIN
(SELECT DISTINCT NAME,
VALUE_STRING,
HASH_VALUE,
ADDRESS,
last_captured
FROM V$SQL_BIND_CAPTURE
) P
ON S.HASH_VALUE = P.HASH_VALUE
AND S.ADDRESS = P.ADDRESS
AND last_captured >=s.last_active_time )
LEFT JOIN
(SELECT DISTINCT cu.sid,
su.module,
su.machine,
su.osuser,
cu.hash_value
FROM v$open_cursor cu,
v$session su
WHERE cu.saddr = su.saddr
UNION
SELECT sid,
module,
machine,
osuser,
sql_hash_value
FROM v$session
WHERE sql_hash_value <> 0
UNION
SELECT sid,
module,
machine,
osuser,
prev_hash_value
FROM v$session
WHERE prev_hash_value <> 0
) v
ON (s.HASH_VALUE = v.HASH_VALUE )
LEFT JOIN all_objects o
ON s.PROGRAM_ID = O.OBJECT_ID
AND s.parsing_schema_name = O.owner
WHERE s.last_active_time >='09.09.15'
AND s.parsing_schema_name != 'SYS'
AND s.parsing_schema_name != 'SYSTEM'
AND s.parsing_schema_name != 'SYSMAN'
ORDER BY s.last_active_time DESC,
s.PROGRAM_LINE# DESC,
S.HASH_VALUE,
P.NAME DESC
I haven't found any ORDER BY i could use to get queries in the right order.
Any idea of how I can do it ?
Thank you for your response.
Best regards.
MS