Hello,
We are seeing elapsed time as 24:26:54.18 which I assume as 24hours 26min. Actually this is not true since the script executed in far less than 24hours. Unfortunately DBA was using old version of Oracle client so we lost the output from "SELECT SYSTIMESTAMP AS end_date FROM dual" statement.
Can someone tell me what is the actual execution time here? I just did a calculation between 16:11hrs to 6:51hrs and decided the actual execution time as 15hrs. Is this correct?
SQL> SET TERMOUT ON
SQL> SET TIMING ON
SQL> SET TIME ON
16:11:39 SQL> SET PAGESIZE 30000
16:11:39 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
Elapsed: 00:00:00.15
16:11:39 SQL> SELECT SYSTIMESTAMP AS start_date FROM dual;
SELECT SYSTIMESTAMP AS start_date FROM dual
*
ERROR at line 1:
ORA-03115: unsupported network datatype or representation
Elapsed: 00:00:00.13
16:11:39 SQL> --
16:11:39 SQL> PROMPT Executing Invoicedist_Total_by_Ledger_11
Executing Invoicedist_Total_by_Ledger_11
16:11:39 SQL> --
16:11:39 SQL> WHENEVER SQLERROR CONTINUE
16:11:39 SQL> DECLARE
16:11:39 2 l_cnt number :=0;
16:11:39 3 BEGIN
16:11:39 4 DBMS_OUTPUT.PUT_LINE('Inserting Into XXXT.Invoicedist_Total_by_Ledger_11');
16:11:39 5 FOR cur_org in ( select organization_id , name from apps.hr_all_organization_units )
16:11:39 6 loop
16:11:39 7 l_cnt := l_cnt +1 ;
16:11:39 8
16:11:39 9 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('ORG : ' ||cur_org.organization_id );
16:11:39 10 DBMS_APPLICATION_INFO.SET_CLIENT_INFO('ORG Number: ' ||l_cnt );
16:11:39 11
16:11:39 12 INSERT /*+ PARALLEL(itbl,DEFAULT) */ INTO XXXT.Invoicedist_Total_by_Ledger_11 itbl
16:11:39 13 (
16:11:39 14 NAME,SUM_AMOUNT,COUNT_OF_INVOICES )
16:11:39 15 select /*+ PARALLEL(aid, DEFAULT) */ cur_org.name, sum(amount) , count(10) from apps.ap_invoice_distributions_all aid
16:11:39 16 where aid.org_id = cur_org.organization_id
16:11:39 17 group by aid.org_id;
16:11:39 18 COMMIT;
16:11:39 19
16:11:39 20 end loop;
16:11:39 21 DBMS_OUTPUT.PUT_LINE('Completed Inserting Into XXXT.Invoicedist_Total_by_Ledger_11');
16:11:39 22 EXCEPTION
16:11:39 23 WHEN others THEN
16:11:39 24 DBMS_OUTPUT.PUT_LINE('Error while Inserting Data - ' || SQLERRM);
16:11:39 25 END;
16:11:39 26 /
Inserting Into XXXT.Invoicedist_Total_by_Ledger_11
Completed Inserting Into XXXT.Invoicedist_Total_by_Ledger_11
PL/SQL procedure successfully completed.
Elapsed: 24:26:54.18
06:51:57 SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
06:51:57 SQL> SELECT SYSTIMESTAMP AS end_date FROM dual;
SELECT SYSTIMESTAMP AS end_date FROM dual
*
ERROR at line 1:
ORA-03115: unsupported network datatype or representation
Elapsed: 00:00:00.00
06:51:57 SQL> SPOOL OFF;
1