Insert /*+ parallel */ hint execution
Hi,
I executed the below proc
CREATE OR REPLACE PROCEDURE bulk_collect
IS
TYPE sid IS TABLE OF NUMBER;
TYPE screated_date IS TABLE OF DATE;
TYPE slookup_id IS TABLE OF NUMBER;
TYPE sdata IS TABLE OF VARCHAR2(50);
l_sid sid;
l_screated_date screated_date;
l_slookup_id slookup_id;
l_sdata sdata;
l_start NUMBER;
BEGIN
l_start := DBMS_UTILITY.get_time;
SELECT id, created_date, lookup_id, data
BULK COLLECT INTO l_sid, l_screated_date, l_slookup_id,l_sdata
FROM big_table;
--dbms_output.put_line('After Bulk Collect: ' || systimestamp);
FORALL indx IN l_sid.FIRST..l_sid.LAST
INSERT /*+ parallel (big_table2,2) */ INTO big_table2 values (l_sid(indx), l_screated_date(indx), l_slookup_id(indx), l_sdata(indx));
--dbms_output.put_line('After FORALL: ' || systimestamp);
COMMIT;
DBMS_OUTPUT.put_line('Total Elapsed Time :- ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
SHOW ERRORS;
I want to confirm if the query was running in parallel. I checked the below tables to confirm if the insert statement was executed in parallel but none of them returned any rows.
select * from V$PX_SESSION where sid = 768
select * from V$PX_SESSTAT
select * from V$PX_PROCESS
select * from V$PX_PROCESS_SYSSTAT
select * from V$PQ_SESSTAT
Please can I know how to find out the parallel execution of /*+ parallel (table_name,2) */ hint
Thanks