Hi Friends,
I have a query which is taking hours to run , does not finish, i had to kill it.
Query:
SELECT AP_ID, MAX (TO_NUMBER (ap_version_id)) max_ap_version_id
FROM TABLE_A
WHERE state <> AM
AND end_date > SYSDATE
AND ctcv_conv_id IN ('CMR7865')
START WITH AP_ID in (select /*+ parallel(TABLE_B 4) */ MS FROM TABLE_B)
CONNECT BY AP_ID_DOMINANT = PRIOR AP_ID
GROUP BY AP_ID
/
Few statistics:
TABLE_A has 602006883 i.e. ~600million rows.
TABLE_B has 1.5 million rows.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3624992970
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 390 | 4 | | | |
| 1 | HASH GROUP BY | | 10 | 390 | 4 | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | TABLE_A | | | | | | |
| 5 | PX COORDINATOR | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ20000 | 3991K| 76M| 4210 | Q2,00 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 3991K| 76M| 4210 | Q2,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | | | | Q2,00 | PCWC | |
| 9 | TABLE ACCESS FULL | TABLE_B | 1489K| 14M| 73 | Q2,00 | PCWP | |
|* 10 | INDEX RANGE SCAN | TABLE_A_PK | 3 | 30 | 1 | Q2,00 | PCWP | |
| 11 | NESTED LOOPS | | | | | | | |
| 12 | BUFFER SORT | | | | | | | |
| 13 | CONNECT BY PUMP | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID| TABLE_A | 10 | 390 | 1 | | | |
|* 15 | INDEX RANGE SCAN | TABLE_A_3IX | 9 | | 1 | | | |
|* 16 | TABLE ACCESS FULL | TABLE_A | 10 | 390 | 1 | | | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STATE"<>AM AND "END_DATE">SYSDATE@! AND "CTCV_CONV_ID"='CMR7865')
3 - access("AP_ID_DOMINANT"=PRIOR "AP_ID")
10 - access("AP_ID"="MS")
15 - access("AP_ID_DOMINANT"=PRIOR "AP_ID")
16 - access("AP_ID_DOMINANT"=PRIOR "AP_ID")
When i ran this query i had to stop it after few hours as it had done *1.8 billion consistent gets!!*
Oracle version :
SQL> select *from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Below are the list of indexes and few statistics of the table TABLE_A:
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TABLE_A TABLE_A_1IX SERVICE_ID
TABLE_A_2IX MAIN_ITEM_ID
TABLE_A_3IX AP_ID_DOMINANT
TABLE_A_4IX INSTAL_ADDRESS_ID
ENTITY_TYPE
TABLE_A_6IX CUSTOMER_ID
START_DATE
TABLE_A_7IX ORDER_ACTION_ID
TABLE_A_8IX CUSTOMER_ID
MAIN_IND
STATE
END_DATE
TABLE_A_PK AP_ID
AP_VERSION_ID
14 rows selected.
OWNER_OBJ CREATED NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
-------------------- --------- ---------- ---------- ----------- ---------
TABLE_A 18-JUL-03 299223500 7662998 179 22-NOV-07
Few other details:
1. Sample output would be something like this:
I got this when put one value for AP_ID ( i.e. START WITH AP_ID='2627818885' instead of START WITH AP_ID in (select /*+ parallel(TABLE_B 4)*/ MS FROM TABLE_B) )
AP_ID AP_VERSION
---------- ----------
2627818885 0
2627818907 0
2627818910 0
2627818923 0
2. The developer need this data to fix a wrong data issue in PRODUCTION. I am testing this query in a production copy (which gets refreshed from production).
3. The stats are not updated , but they wont make a difference in this case as the NUM_ROWS is ~300m for TABLE_A, which i think is representative enough.
4. For a short duration i saw LATCH:CACHE BUFFER CHAINS (about 91 number of waits) OF nearly 127 seconds_in_wait for the session which i ran. There was nothing else running in the database at that time. At other times normal waits like db file sequential read, scattered reds, PX related etc were seen.
5. I tried using the
oldconnect_by_enabled underscore parameter. It just changes the plan a little bit , but doesnt help in making the query run faster.
Can you please suggest how can i make this query faster,when i am passing 1.5 million rows to the START WITH CLAUSE?
I searched on this site + google , no breakthrough :(
Regds,
Kunwar
Edited by: Kunwar on Sep 24, 2012 10:48 AM