Long full scan hasn’t returned but session is gone from V$SESSION

oracle

I have a query on a big table purposefully hinted with /*+ FULL() */ and uses partition pruning, and for a while the scans for each partition can be seen in V$SESSION_LONGOPS. After a while no more long ops are listed, the corresponding V$SESSION entry goes INACTIVE, and eventually disappears, but no rows have been returned.

SELECT /*+ full(t) */  Count(*) n
FROM t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'

Plan hash value: 3256883686

-----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |     1 |     8 |    14M  (1)| 57:04:42 |       |       |
|   1 |  SORT AGGREGATE           |         |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|         |   643 |  5144 |    14M  (1)| 57:04:42 |    49 |    79 |
|   3 |    PARTITION LIST ALL     |         |   643 |  5144 |    14M  (1)| 57:04:42 |     1 |     3 |
|*  4 |     TABLE ACCESS FULL     | T       |   643 |  5144 |    14M  (1)| 57:04:42 |   145 |   237 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / T@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_TIME"<=TO_DATE(' 2015-07-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

What's going on?

Best Answer

It seems you didn't reference the /*+ FULL(t) */ hint alias while calling target table. You created the table with the same name as the alias. I'm not sure if that might be the problem... But the docs example follow this structure:

SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e WHERE last_name LIKE :b1;

So, what happens if you instead try:

SELECT /*+ full(t) */ Count(1) n FROM t t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'

?

Related Question