The rownum
pseudocolumn is assigned as rows are processed. The predicate rownum = <<x>>
will evaluate to false
if <<x>>
is anything other than 1.
Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like
- Read a row from
table1
- Evaluate the
col2 = 'val'
expression
- If
col2 = 'val'
, evaluate the extract
- Assign the
rownum
to the row that has been returned. Since no other rows have satisfied all the criteria yet, the rownum
will be 1
- Evaluate the
rownum = <<x>>
predicate. If <<x>>
is anything other than 1, the row is
rejected
- Go back to step 1 and read the next row
In this loop, every row will be read from table1
. And every row will be rejected (assuming <<x>>
is something other than 1) because of the rownum = <<x>>
predicate. So you'll incur the cost of reading every row from the table, evaluating the extract
for every row, and you'll never return any data.
Your first query appears to be working because it is filtering out <<x>> + 1
rows in the subquery. Of course, since there is no ORDER BY
, your first query always returns an arbitrary row. It would be entirely legal for Oracle to return the same row for every value of <<x>>
in your first query. It probably won't, of course, but it is entirely possible that you'd get different rows over time, that some rows would never be returned, that other rows would be returned multiple times, etc. If you want a deterministic result (prior to 12.1 which has some simpler syntax options), you'd need to do two levels of nesting
SELECT *
FROM (SELECT b.*,
rownum rn
FROM (SELECT a.*
FROM some_table a
ORDER BY some_column) b
WHERE rownum <= <<upper_limit>>) c
WHERE rn >= <<lower_limit>>
For a pretty thorough discussion of the various issues, take a look through this askTom thread.
I think this does what you need.
USE 'yourDB'
GO
SELECT
OBJECT_NAME(p.[object_id]) BlockedObject
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
INNER JOIN sys.partitions p ON SUBSTRING(resource_description,
PATINDEX('%associatedObjectId%', resource_description) + 19,
LEN(resource_description)) = p.partition_id
Best Answer
What makes you think your session is waiting?
When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting.
V$SESSION
You should query
WAIT_TIME
as well:Another method to confirm that your session is
ON CPU
would be querying ASH.V$ACTIVE_SESSION_HISTORY
In ASH, the
SESSION_STATE
column has 2 different possible values:Another method would be to query
V$SESSTAT
(joined withV$STATNAME
) an check whether the statisticCPU time
increases.You can run any CPU-bound query to test this, for example the below query uses nothing else but CPU. Start it, and you will we see
SECONDS_IN_WAIT
increasing, while your session is not waiting, but using CPU.