Hi all
I had some sql code that was running slowly. After visiting this forum, I managed to speed the query time up by adding the driving site hint.
It seems I have a further issue now. The insert statement takes about 3 hours to run even if the select brings back no data.
What my query does is get all of the transactions that happened in the last 90 days and check if any of them have been set to status 'extended'
I need to check which transactions have already been sent by using the table LOGGED_TXNS.
The table called LOGGED_TXNS, all other tables exist at a remote location.
Here is my code:
INSERT /*+ APPEND */ INTO CLIENT_REPORTING(RECORD_TYPE,
NEW_TRANSACTION,
TRANSACTION_REASON,
TRANSACTION_STATUS,
ADVERTISER_ID,
SHOPPER_ID,
TRANSACTION_ID,
BASKET_LINE_ID,
CLICK_DATETIME,
COLLECTOR_ACTION_DATETIME,
STATUS_CHANGE_DATETIME,
AUTO_APPROVED,
REVERSAL_REASON,
EVENT_TYPE,
RETAILER_TRANSACTION_ID,
ACTION_ID,
PRODUCT_CODE,
VOUCHER_CODE,
LINK_ID,
SALE_AMOUNT,
SALE_CURRENCY,
VAT_CODE,
SALE_AMOUNT_VAT_VALUE,
OTHER_NON_COMMISSION_VALUE,
PUBLISHER_COMMISSION,
COMMISSION_CURRENCY,
COMMISSION_REPORTED,
EXTRA_ATTRIB_1,
EXTRA_ATTRIB_2,
EXTRA_ATTRIB_3,
EXTRA_ATTIRB_4,
POSTING_DATE
)
select /*+ DRIVING_SITE(a) */
'D' Record_Type_Indicator,
'U' New_Transaction,
CASE WHEN actiontrackertype IN('sim_lead','item_lead', 'item_sale', 'sim_sale') THEN 1
WHEN actiontrackertype = 'bonus' THEN 2 END Transaction_Reason,
DECODE(cat.status, 'new', 'Pend', 'extended', 'Pend', 'locked', 'Appr') Transaction_Status,
cat.adv_company_id Advertiser_ID,
cat.sid Shopper_ID,
cat.id Transaction_ID,
0 Basket_Line_ID,
to_char(new_time(cat.click_date, 'PST', 'GMT'), 'YYYYMMDDHH24MI') Click_Date_Time,
to_char(new_time(cat.event_date, 'PST', 'GMT'), 'YYYYMMDDHH24MI') Collection_Action_Date,
'' Status_Change_Date,
'' Auto_Approved,
'' Reversal_Reason,
CASE WHEN actiontrackertype IN('item_sale','sim_sale') THEN '01'
WHEN actiontrackertype IN('item_lead','sim_lead') THEN '02'
ELSE '04' END Event_Type,
cat.oid Retailer_Transaction_ID,
cat.actiontracker_id Action_ID,
cati.sku Product_Code,
'' Voucher_Code,
cat.ad_id Link_ID,
CASE WHEN cat.sale_amount_adv_curr > 0 THEN
REPLACE(REPLACE(TO_CHAR(CASE WHEN cati.sale_amount_pub_curr IS NULL THEN
cat.sale_amount_adv_curr ELSE cati.sale_amount_pub_curr END, '000000000.00'), '.',''), ' ', '')
WHEN cat.sale_amount_adv_curr < 0 THEN '1' ||
REPLACE(REPLACE(TO_CHAR(CASE WHEN cati.sale_amount_pub_curr IS NULL THEN
cat.sale_amount_adv_curr ELSE cati.sale_amount_pub_curr END, '00000000.00'), '.',''),'-','')
ELSE '00000000000' END Sale_Amount,
cat.orig_curr Sale_Currency,
'S' VAT_Code_Sale_Amount,
0 Sale_Amount_VAT_Value,
0 Other_Non_Commission_Value,
CASE WHEN cat.sale_amount_adv_curr > 0 THEN
REPLACE(REPLACE(TO_CHAR(CASE WHEN cati.pub_comm_pub_curr IS NULL THEN -(cat.adv_comm_adv_curr + cat.cj_fee_adv_curr)
ELSE cati.pub_comm_pub_curr END, '000000000.00'), '.',''), ' ', '')
WHEN cat.sale_amount_adv_curr < 0 THEN '1' ||
REPLACE(REPLACE(TO_CHAR(CASE WHEN cati.pub_comm_pub_curr IS NULL THEN (cat.adv_comm_adv_curr + cat.cj_fee_adv_curr)
ELSE cati.pub_comm_pub_curr END, '00000000.00'), '.',''),'-','')
ELSE '00000000000' END Publisher_Commission,
c.func_currency Commission_Currency,
'N' Commission_Reported,
CASE WHEN cati.quantity IS NULL THEN 0 ELSE cati.quantity END Extra_Attrib_1,
'' Extra_Attrib_2,
'' Extra_Attrib_3,
'' Extra_Attrib_4,
cat.posting_date
FROM cjo_action_txn@report_source cat,
action@report_source a,
website@report_source w,
company@report_source c,
cjo_action_txn_item@report_source cati,
(SELECT TRANSACTION_ID
FROM BIRPT.LOGGED_TXNS NLT
WHERE NLT.NEW = 1
AND NLT.EXTENDED = 0
AND NLT.CLOSED = 0) LOG_TXN
WHERE cat.pub_company_id = 1605598
AND cat.posting_date >= trunc(sysdate - 90)
AND cat.status = 'extended'
AND LOG_TXN.TRANSACTION_ID = cat.id
AND cat.id = cati.cjo_action_txn_id(+)
AND cat.actiontracker_id = a.id
AND cat.pub_company_id = c.id
AND w.id = cat.WEBSITE_ID;
Here is my explain plan output:
PLAN_TABLE_OUTPUT
Plan hash value: 3009991002
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 442 | 660 (1)| 00:00:08 | | |
| 1 | LOAD TABLE CONVENTIONAL | CLIENT_REPORTING | | | | | | |
|* 2 | HASH JOIN | | 1 | 442 | 660 (1)| 00:00:08 | | |
| 3 | NESTED LOOPS | | 1 | 428 | 656 (0)| 00:00:08 | | |
| 4 | NESTED LOOPS | | 1 | 415 | 655 (0)| 00:00:08 | | |
| 5 | NESTED LOOPS OUTER | | 1 | 402 | 655 (0)| 00:00:08 | | |
| 6 | NESTED LOOPS | | 1 | 355 | 651 (0)| 00:00:08 | | |
| 7 | REMOTE | COMPANY | 1 | 19 | 3 (0)| 00:00:01 | RPT1_~ | R->S |
| 8 | REMOTE | CJO_ACTION_TXN | 1 | 336 | 648 (0)| 00:00:08 | RPT1_~ | R->S |
| 9 | REMOTE | CJO_ACTION_TXN_ITEM | 1 | 47 | 4 (0)| 00:00:01 | RPT1_~ | R->S |
| 10 | REMOTE | ACTION | 1 | 13 | 0 (0)| 00:00:01 | RPT1_~ | R->S |
| 11 | REMOTE | WEBSITE | 1 | 13 | 1 (0)| 00:00:01 | RPT1_~ | R->S |
|* 12 | TABLE ACCESS FULL | LOGGED_TXNS | 919 | 12866 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TRANSACTION_ID"="CAT"."ID")
12 - filter("NLT"."NEW"=1 AND "NLT"."EXTENDED"=0 AND "NLT"."CLOSED"=0)
Remote SQL Information (identified by operation id):
----------------------------------------------------
7 - SELECT "ID","FUNC_CURRENCY" FROM "COMPANY" "C" WHERE "ID"=1605598 (accessing 'REPORT_SOURCE' )
8 - SELECT "ID","POSTING_DATE","EVENT_DATE","CLICK_DATE","ADV_COMPANY_ID","PUB_COMPANY_ID","AD_ID","W
EBSITE_ID","ACTIONTRACKER_ID","ACTIONTRACKERTYPE","OID","SID","CJ_FEE_ADV_CURR","ADV_COMM_ADV_CURR","ORI
G_CURR","SALE_AMOUNT_ADV_CURR","STATUS" FROM "CJO_ACTION_TXN" "CAT" WHERE "PUB_COMPANY_ID"=1605598 AND
"STATUS"='extended' AND "POSTING_DATE">=TRUNC(:1-90) (accessing 'REPORT_SOURCE' )
9 - SELECT "CJO_ACTION_TXN_ID","SKU","QUANTITY","SALE_AMOUNT_PUB_CURR","PUB_COMM_PUB_CURR" FROM
"CJO_ACTION_TXN_ITEM" "CATI" WHERE :1="CJO_ACTION_TXN_ID" (accessing 'REPORT_SOURCE' )
10 - SELECT /*+ */ "ID" FROM "ACTION" "A" WHERE :1="ID" (accessing 'REPORT_SOURCE' )
11 - SELECT "ID" FROM "WEBSITE" "W" WHERE "ID"=:1 (accessing 'REPORT_SOURCE' )
I could not get the AUTOTRACE to work (I am using Toad)
If this is needed and someone can tell me how to use it in Toad, I will post the results.
Thanks