A join query is running against my db which is logically partitioned.
The join involves a partitioned table (has around 300 Million rows)
which is spread across 7 partitions and a small non-partitioned table
(has around 2000 rows). Joining the table based on the partitioning key
The query has been running for the past 30 mins without fetching any
result set and usually I understand that it would run only for less
than 5 mins.
Statistics are upto date on both tables
Query:
SELECT distinct ltrim(rtrim(A.VEH_IDENT_NBR)),
ltrim(rtrim(A.VEH_IDENT_NBR)) AS VEH_IDENT_NBR_LKP
FROM CARD.GLOBAL_VEH A
join
ETL.STG_NA_GIF_VEH_DLVRY_CAN_EVNT_Vin B
on
A.VEH_IDENT_NBR = B.VEH_IDENT_NBR for read only
When I checked the appln.snapshot the query is in executing state on
co-ordinator partiton while on the rest of partition it is on "Waiting
to send on tablequeue" status
====
Part of Application Snapshot for co-ordinator partition
====
Total sort time = 0
Sort overflows = 0
Rows read = 1
Rows written = 53183604
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Blocking cursor = YES
Dynamic SQL statement text:
SELECT distinct ltrim(rtrim(A.VEH_IDENT_NBR)),
ltrim(rtrim(A.VEH_IDENT_NBR)) AS VEH_IDENT_NBR_LKP FROM CARD.GLOBAL_VEH
A joi
n ETL.STG_NA_GIF_VEH_DELIVERY_EVENT_Vin B on A.VEH_IDENT_NBR =
B.VEH_IDENT_NBR for read only
Subsection number = 0
Subsection database partition number = 0
Subsection status = Executing
Execution elapsed time (seconds) = 1825
Total user CPU time (sec.ms) = 0.000000
Total system CPU time (sec.ms) = 0.000000
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 0
Rows read = 0
Rows written = 0
Number of agents working on subsection = 0
========
Part of Application Snapshot from one of the other partitions
========
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Blocking cursor = NO
Dynamic SQL statement text:
SELECT distinct ltrim(rtrim(A.VEH_IDENT_NBR)),
ltrim(rtrim(A.VEH_IDENT_NBR)) AS VEH_IDENT_NBR_LKP FROM CARD.GLOBAL_VEH
A joi
n ETL.STG_NA_GIF_VEH_DELIVERY_EVENT_Vin B on A.VEH_IDENT_NBR =
B.VEH_IDENT_NBR for read only
Subsection number = 2
Subsection database partition number = 1
Subsection status = Waiting to send
on tablequeue
Node for which waiting on tablequeue = 0
Tablequeue ID on which agent is waiting = 2
Execution elapsed time (seconds) = 1826
Total user CPU time (sec.ms) = 11.971956
Total system CPU time (sec.ms) = 0.087213
Current number of tablequeue buffers overflowed = 0
Total number of tablequeue buffers overflowed = 0
Maximum number of tablequeue buffers overflowed = 0
Rows received on tablequeues = 0
Rows sent on tablequeues = 7598014
Rows read = 0
Rows written = 0
Number of agents working on subsection = 1
Agent process/thread ID = 2244632
Explain Plan is as below:
Rows
RETURN
( 1)
Cost
I/O
|
0
DTQ
( 2)
113.109
64
|
0
TBSCAN
( 3)
113.069
64
|
0
SORT
( 4)
113.068
64
|
0
NLJOIN
( 5)
113.067
64
/----+----\
0 1
TBSCAN TBSCAN
( 6) ( 7)
113.067 7.73186e+06
64 1.01032e+06
| |
0 5.88295e+07
TABLE: ETL TEMP
STG_NA_GIF_VEH_D ( 8)
6.13434e+06
I dont this this is waiting for any locks because
[1] IS lock on both tables
[2] Recursive app.snap shows incremental values for "Rows sent on
tablequeues" section.
Questions:
[1] How to troubleshoot further to identify the problem?
[2] Why does the optimizer broadcasts the data from big table instead
of broadcasting small table?
Any help is greatly appreciated.
Thanks,
Sam.
