Home » RDBMS Server » Performance Tuning » DB file sequential read (9 merged) (oracle 10g)
DB file sequential read (9 merged) [message #559834] |
Fri, 06 July 2012 11:53 |
|
bhagyaraj.p
Messages: 5 Registered: May 2012 Location: Bangalore
|
Junior Member |
|
|
Dear All,
I am facing an issue in my production database, I have a procedure that does bulk fetch from a table1 and insert into another table table2.
The table1 is a partitioned table having two partitions, and the table 2 is also hash partitioned on serial number of 25.
Both of these tables are big in size. The table1 is with a size of 12GB and table2 is arround 350GB.
But the partition where from I am inserting the data is just 0.5GB.
I am using a cursor that is fetching records from table1 of a specific partition between a range of sequence numbers.
sequence number is a field on the table1 and it is indexed locally.
Inside the API its opens the above cursor and fetches 10000 records into a plsql array.
and this plsql array is used in a FORALL insert statement to insert the records.
The FORALL statement is going for a dbfile sequential read and its consuming a lot time to complete.
Also one thing that I noticed is the INSERT statement contains a sequence and the cache of that sequence is 0.
I shall look for a way to increase the size of this cache to minimum 10000.
Please find the below source code and trace log details which I could collect from the DBA team.
Please help me to figure out what is going wrong here and please suggest me a way out of this.
In the below trace TPS72_PHY_CARDS is the table1, and TPS01_CARDS is table2.
TKPROF: Release 10.2.0.3.0 - Production on Fri Jul 6 17:24:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: /oracle/admin/ppms3p/udump/ppms3p_ora_23448.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 307828 6.04 5.49 0 0 0 0
Execute 307828 269.75 262.62 0 313073 624076 307828
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 615656 275.79 268.11 0 313073 624076 307828
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=180 us)
1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=22 us)(object id 102)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer busy waits 2170 0.00 0.23
latch: cache buffers chains 6 0.00 0.00
log file switch completion 12 0.10 0.38
********************************************************************************
select file#
from
file$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 30 0.00 0.00 0 0 0 0
Execute 30 0.00 0.00 0 0 0 0
Fetch 690 0.01 0.00 0 1350 0 660
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 750 0.01 0.01 0 1350 0 660
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Rows Row Source Operation
------- ---------------------------------------------------
22 TABLE ACCESS BY INDEX ROWID FILE$ (cr=45 pr=0 pw=0 time=43 us)
22 INDEX RANGE SCAN I_FILE2 (cr=23 pr=0 pw=0 time=232 us)(object id 42)
********************************************************************************
INSERT INTO TPS01_CARDS (CPS01_SERIAL_NUMBER, CPS01_CARD_TYPE,
CPS01_DENOMINATION, CPS01_DATE_GENERATED, CPS01_LOG_PHY_IND, CPS01_CARD_ID,
CPS01_OUTLET_CODE, CPS01_LOGICAL_ORDER_NR, CPS01_CURRENT_STATUS,
CPS01_ACCESS_CODE, CPS01_DATE_MODIFIED)
VALUES
(:B1 , :B6 , :B5 , SYSDATE, 'PHY', CPS01_CARD_ID_SEQ.NEXTVAL, :B4 , :B3 ,
NULL, :B2 , SYSDATE)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 32 569.38 3785.26 307819 679092 6197751 320000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32 569.38 3785.26 307819 679092 6197751 320000
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 296162 0.75 3156.59
latch: library cache 1 0.00 0.00
latch: object queue header operation 3 0.00 0.00
log file switch completion 5 0.09 0.22
latch: cache buffers chains 3 0.00 0.00
********************************************************************************
UPDATE CPS72_MININUM_SEQUENCE SET LAST_SEQUENCE_N=:B1
WHERE
OPERATION_CODE_V='MOVE_TPS01'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 31 0.03 0.01 0 217 64 31
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.03 0.01 0 217 64 31
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
********************************************************************************
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 31 0.49 0.46 0 0 31 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.49 0.46 0 0 31 0
Misses in library cache during parse: 0
Parsing user id: 55 (recursive depth: 1)
********************************************************************************
SELECT CPS74_STATUS
FROM
TPS74_LOGICAL_ORDERS_DETAIL WHERE CPS74_ORDER_NUMBER=:B2 AND
CPS74_DENOMINATION = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 31 0.01 0.00 0 0 0 0
Fetch 31 0.00 0.00 0 93 0 31
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62 0.01 0.00 0 93 0 31
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
********************************************************************************
SELECT CPS72_SEQUENCE_NUMBER, CPS72_SERIAL_NUMBER, CPS72_PIN_NUMBER,
CPS72_ACCESS_CODE
FROM
TPS72_PHY_CARDS PARTITION (TPS72_PHY_CARDS_UMAP) ,
TPS74_LOGICAL_ORDERS_DETAIL WHERE CPS74_ORDER_NUMBER = :B1 AND
CPS72_SEQUENCE_NUMBER BETWEEN CPS74_START_SEQ_NR AND CPS74_END_SEQ_NR AND
CPS72_SEQUENCE_NUMBER >= ( SELECT LAST_SEQUENCE_N FROM
CPS72_MININUM_SEQUENCE WHERE OPERATION_CODE_V='MOVE_TPS01' ) ORDER BY
CPS72_SEQUENCE_NUMBER ASC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 31 0.73 0.73 0 0 0 310000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.73 0.73 0 0 0 310000
Misses in library cache during parse: 0
Parsing user id: 55 (recursive depth: 1)
********************************************************************************
SAVEPOINT BULK_INSERT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 31 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 31 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 55 (recursive depth: 1)
********************************************************************************
*** 2012-07-06 16:54:07.455
update seq$ set increment$=:2,minvalue=:3,
maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE SEQ$ (cr=1 pr=0 pw=0 time=115 us)
1 INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=8 us)(object id 102)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1728 0.06 17.51
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 307859 6.04 5.49 0 0 0 0
Execute 308015 839.66 4048.37 307819 992383 6821924 627860
Fetch 752 0.74 0.74 0 1443 0 310691
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 616626 846.44 4054.62 307819 993826 6821924 938551
Misses in library cache during parse: 1
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 294434 0.75 3139.07
buffer busy waits 2170 0.00 0.23
latch: cache buffers chains 9 0.00 0.00
latch: library cache 1 0.00 0.00
latch: object queue header operation 3 0.00 0.00
log file switch completion 17 0.10 0.60
6 user SQL statements in session.
307859 internal SQL statements in session.
307865 SQL statements in session.
********************************************************************************
Trace file: /oracle/admin/ppms3p/udump/ppms3p_ora_23448.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
6 user SQL statements in trace file.
307859 internal SQL statements in trace file.
307865 SQL statements in trace file.
9 unique SQL statements in trace file.
18771469 lines in trace file.
3698 elapsed seconds in trace file.
#########################################################################################################
BEGIN
DECLARE
p_job number := 4567;
tps73 tps73_logical_orders%ROWTYPE;
tps74 tps74_logical_orders_detail%ROWTYPE;
w_code tps79_job_controls.cps79_completion_code%TYPE;
w_file tps79_job_controls.cps79_in_file%TYPE;
w_sn tps72_phy_cards.cps72_serial_number%TYPE;
w_ac tps72_phy_cards.cps72_access_code%TYPE;
w_sq tps72_phy_cards.cps72_sequence_number%TYPE;
w_pn tps72_phy_cards.cps72_pin_number%TYPE;
w_denomination tps01_cards.cps01_denomination%TYPE;
w_denom tps08_denomination_types.cps08_denomination_desc%TYPE;
w_logical_order tps01_cards.cps01_logical_order_nr%TYPE;
w_card_type VARCHAR2 (2);
w_counter NUMBER;
w_error_detail VARCHAR2 (250);
w_email VARCHAR2 (1449);
w_confirm VARCHAR2 (2000);
w_return_msg VARCHAR2 (255);
l_status varchar2(3);
TYPE sq_tab IS TABLE OF NUMBER;
TYPE sn_tab IS TABLE OF VARCHAR2 (14);
TYPE pn_tab IS TABLE OF NUMBER (16);
TYPE ac_tab IS TABLE OF VARCHAR2 (16);
sq_ntab sq_tab := sq_tab ();
sn_ntab sn_tab := sn_tab ();
pn_ntab pn_tab := pn_tab ();
ac_ntab ac_tab := ac_tab ();
error_occured EXCEPTION;
CURSOR c1
IS
SELECT *
FROM tps73_logical_orders
WHERE cps73_status = 7
AND cps73_steps IN (7,
AND cps73_order_type = 'PHY'
ORDER BY CPS73_START_SEQ_NR;
CURSOR c2
IS
SELECT *
FROM tps74_logical_orders_detail
WHERE cps74_order_number = tps73.cps73_order_number
ORDER BY cps74_denomination;
CURSOR c3
IS
SELECT cps72_sequence_number,
cps72_serial_number,
cps72_pin_number,
cps72_access_code
FROM tps72_phy_cards partition (TPS72_PHY_CARDS_UMAP) , tps74_logical_orders_detail ---bhagyaraj 5
WHERE cps74_order_number = tps73.cps73_order_number
AND CPS72_SEQUENCE_NUMBER BETWEEN CPS74_START_SEQ_NR AND CPS74_END_SEQ_NR
AND CPS72_SEQUENCE_NUMBER > (
SELECT LAST_SEQUENCE_N FROM CPS72_MININUM_SEQUENCE
WHERE OPERATION_CODE_V='MOVE_TPS01'
)
ORDER BY cps72_sequence_number asc;
l_move_cnt number := 0;
BEGIN
dbms_output.put_line('Started');
w_file := 'upload_vouchers_' || TO_CHAR (SYSDATE, 'yyyymmdd_hh24:mi');
job_control_auto (p_job, w_file, w_code);
IF w_code >= 0
THEN
GOTO proc_exit;
END IF;
job_control_steps_auto (p_job,
w_file,
1,
w_code);
IF w_code >= 0
THEN
GOTO proc_exit;
END IF;
OPEN c1;
LOOP
FETCH c1 INTO tps73;
EXIT WHEN c1%NOTFOUND;
UPDATE tps73_logical_orders
SET cps73_steps = 8
WHERE cps73_order_number = tps73.cps73_order_number;
OPEN c2;
LOOP
FETCH c2 INTO tps74;
EXIT WHEN c2%NOTFOUND;
SELECT cps08_card_type_code, cps08_denomination_desc
INTO w_card_type, w_denom
FROM tps08_denomination_types
WHERE cps08_denomination_code = tps74.cps74_denomination;
w_counter := 0;
OPEN c3;
LOOP
FETCH c3 BULK COLLECT INTO sq_ntab, sn_ntab, pn_ntab, ac_ntab LIMIT 10000;
-- dbms_output.put_line('Total Records :'||SQL%ROWCOUNT||'---'||sq_ntab.COUNT||'-----'||sn_ntab.count);
-- dbms_output.put_line(' before exit when sql%rowcount=0..');
EXIT WHEN sq_ntab.COUNT=0;
BEGIN
savepoint bulk_insert;
FORALL i IN sn_ntab.first..sn_ntab.last
INSERT INTO tps01_cards (cps01_serial_number,
cps01_card_type,
cps01_denomination,
cps01_date_generated,
cps01_log_phy_ind,
cps01_card_id,
cps01_outlet_code,
cps01_logical_order_nr,
cps01_current_status,
cps01_access_code,
cps01_date_modified)
VALUES (sn_ntab (i),
w_card_type,
tps74.cps74_denomination,
SYSDATE,
'PHY',
cps01_card_id_seq.NEXTVAL,
tps73.cps73_outlet_code,
tps73.cps73_order_number,
NULL,
ac_ntab (i),
sysdate);
-- FORALL i IN sn_ntab.first..sn_ntab.last
-- DELETE /*+INDEX (tps72_phy_cards TPS72_SEQ_LCL)*/
-- tps72_phy_cards PARTITION (tps72_phy_cards_map)
-- WHERE cps72_sequence_number = sq_ntab (i);
l_move_cnt := sq_ntab(sq_ntab.last);
UPDATE CPS72_MININUM_SEQUENCE SET LAST_SEQUENCE_N=l_move_cnt
WHERE OPERATION_CODE_V='MOVE_TPS01'; --- bhagyaraj 8
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
rollback to bulk_insert;
job_control_message_auto (p_job, w_file, 'BHAGYARAJ'||SUBSTR (sqlerrm, 1, 110));
-- IF SQLCODE != -6502
-- THEN
-- w_error_detail := SQLERRM;
-- RAISE error_occured;
-- END IF;
FOR i in sn_ntab.first..sn_ntab.last LOOP
--FETCH c3 INTO w_sq, w_sn, w_pn, w_ac;
--EXIT WHEN c3%NOTFOUND;
--w_counter := NVL (w_counter, 0) + 1;
BEGIN
INSERT INTO tps01_cards (cps01_serial_number,
cps01_card_type,
cps01_denomination,
cps01_date_generated,
cps01_log_phy_ind,
cps01_card_id,
cps01_outlet_code,
cps01_logical_order_nr,
cps01_current_status,
cps01_access_code,
cps01_date_modified)
VALUES (sn_ntab (i),
w_card_type,
tps74.cps74_denomination,
SYSDATE,
'PHY',
cps01_card_id_seq.NEXTVAL,
tps73.cps73_outlet_code,
tps73.cps73_order_number,
NULL,
ac_ntab (i),
sysdate);
-- DELETE /*+INDEX (tps72_phy_cards TPS72_SEQ_LCL)*/
-- tps72_phy_cards PARTITION (tps72_phy_cards_map)
-- WHERE cps72_sequence_number = sq_ntab (i);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
BEGIN
SELECT cps01_denomination,
cps01_logical_order_nr
INTO w_denomination, w_logical_order
FROM tps01_cards
WHERE cps01_serial_number = sn_ntab (i);
IF w_denomination !=
tps74.cps74_denomination
AND w_logical_order !=
tps73.cps73_order_number
THEN
w_error_detail :=
'Duplicate serial number / pin '
|| sn_ntab (i)
|| ' on insert into TPS01_CARDS'
|| UTL_TCP.crlf
|| 'Uploading of vouchers for order number '
|| tps73.cps73_order_number
|| ' has stopped. Please investigate';
job_control_message_auto (
p_job,
w_file,
w_error_detail
);
RAISE error_occured;
END IF;
END;
WHEN OTHERS
THEN
job_control_message_auto (
p_job,
w_file,
sn_ntab (i)||'-'||sqlerrm
);
RAISE error_occured;
END;
-- IF MOD (w_counter, 2000) = 0
-- THEN
-- COMMIT;
-- END IF;
END LOOP;
l_move_cnt := sq_ntab(sq_ntab.last);
UPDATE CPS72_MININUM_SEQUENCE SET LAST_SEQUENCE_N=l_move_cnt
WHERE OPERATION_CODE_V='MOVE_TPS01'; --- bhagyaraj 9
COMMIT;
END;
-- dbms_output.put_line(' before exit when notfound..');
EXIT WHEN c3%NOTFOUND;
select CPS74_STATUS into l_status from tps74_logical_orders_detail where cps74_order_number=tps74.cps74_order_number
and CPS74_DENOMINATION = tps74.CPS74_DENOMINATION;
if l_status = 'CA' then
rollback;
goto proc_exit;
end if;
-- dbms_output.put_line(' after exit when notfound..');
END LOOP;
if c3%isopen then
CLOSE c3;
end if;
-- Update the detail extract completed
UPDATE tps74_logical_orders_detail
SET cps74_status = 'UL'
WHERE cps74_order_number = tps73.cps73_order_number
AND cps74_denomination = tps74.cps74_denomination;
-- IF LENGTH (w_confirm) > 1800
-- THEN
---- ppms_email.send_group ('SUPPORT',
---- 'ppms_vouchers.upload_vouchers',
---- w_confirm,
---- w_return_msg);
-- w_confirm := NULL;
-- END IF;
IF w_confirm IS NULL
THEN
w_confirm :=
'The following voucher purchase orders have been successfully uploaded into TPS01_CARDS'
|| UTL_TCP.crlf;
w_confirm :=
w_confirm
|| 'Order '
|| tps73.cps73_order_number
|| ', denomination '
|| w_denom
|| UTL_TCP.crlf; -- added by prem on 16 jan 2008
ELSE
w_confirm :=
w_confirm
|| 'Order '
|| tps73.cps73_order_number
|| ', denomination '
|| w_denom
|| UTL_TCP.crlf;
END IF;
END LOOP;
CLOSE c2;
UPDATE tps73_logical_orders
SET cps73_status = 8
WHERE cps73_order_number = tps73.cps73_order_number;
ppms_send_email( 'Upload Order '||tps73.cps73_order_number||' Finished',
'Dear All, The Order number '||tps73.cps73_order_number||' has been successfully loaded into TPS01_CARDS..',w_code);
COMMIT;
END LOOP;
CLOSE c1;
update_job_control_steps_auto (p_job, w_file, 1);
update_job_controls_auto (p_job, w_file);
-- Notify PPMS Administrator that the vouchers have been successfully uploaded
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
<<proc_exit>>
IF w_code >= 0
THEN
job_control_message_auto (
p_job,
w_file,
'Job control error has occured - completion code '
|| TO_CHAR (w_code)
);
END IF;
EXCEPTION
WHEN error_occured
THEN
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
job_control_message_auto (p_job,
w_file,
SUBSTR (w_error_detail, 1, 120));
w_email :=
'The following error occured while uploading physical vouchers - '
|| UTL_TCP.crlf
|| w_error_detail;
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_email,
-- w_return_msg);
WHEN OTHERS
THEN
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
job_control_message_auto (p_job, w_file, SUBSTR (SQLERRM, 1, 120));
w_email :=
'The following error occured while uploading physical vouchers - '
|| UTL_TCP.crlf
|| SQLERRM;
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_email,
-- w_return_msg);
END;
END;
/
####################################################################################################################
|
|
|
|
|
Re: DB file sequential read (9 merged) [message #559853 is a reply to message #559847] |
Fri, 06 July 2012 15:02 |
|
bhagyaraj.p
Messages: 5 Registered: May 2012 Location: Bangalore
|
Junior Member |
|
|
Thanks for the guidence. Below is the code part.
The issue is as per my above pasted trace, my INSERT statement is very slow due to db file sequential read.
I am not able to figure out why its having this much of db file sequential read, or how I can reduce it.
BEGIN
DECLARE
p_job NUMBER := 4567;
tps73 tps73_logical_orders%ROWTYPE;
tps74 tps74_logical_orders_detail%ROWTYPE;
w_code tps79_job_controls.cps79_completion_code%TYPE;
w_file tps79_job_controls.cps79_in_file%TYPE;
w_sn tps72_phy_cards.cps72_serial_number%TYPE;
w_ac tps72_phy_cards.cps72_access_code%TYPE;
w_sq tps72_phy_cards.cps72_sequence_number%TYPE;
w_pn tps72_phy_cards.cps72_pin_number%TYPE;
w_denomination tps01_cards.cps01_denomination%TYPE;
w_denom tps08_denomination_types.cps08_denomination_desc%TYPE;
w_logical_order tps01_cards.cps01_logical_order_nr%TYPE;
w_card_type VARCHAR2 (2);
w_counter NUMBER;
w_error_detail VARCHAR2 (250);
w_email VARCHAR2 (1449);
w_confirm VARCHAR2 (2000);
w_return_msg VARCHAR2 (255);
l_status VARCHAR2(3);
TYPE sq_tab
IS TABLE OF NUMBER;
TYPE sn_tab
IS TABLE OF VARCHAR2 (14);
TYPE pn_tab
IS TABLE OF NUMBER (16);
TYPE ac_tab
IS TABLE OF VARCHAR2 (16);
sq_ntab SQ_TAB := Sq_tab ();
sn_ntab SN_TAB := Sn_tab ();
pn_ntab PN_TAB := Pn_tab ();
ac_ntab AC_TAB := Ac_tab ();
error_occured EXCEPTION;
CURSOR c1 IS
SELECT *
FROM tps73_logical_orders
WHERE cps73_status = 7
AND cps73_steps IN ( 7, 8 )
AND cps73_order_type = 'PHY'
ORDER BY cps73_start_seq_nr;
CURSOR c2 IS
SELECT *
FROM tps74_logical_orders_detail
WHERE cps74_order_number = tps73.cps73_order_number
ORDER BY cps74_denomination;
CURSOR c3 IS
SELECT cps72_sequence_number,
cps72_serial_number,
cps72_pin_number,
cps72_access_code
FROM tps72_phy_cards PARTITION (tps72_phy_cards_umap),
tps74_logical_orders_detail ---bhagyaraj 5
WHERE cps74_order_number = tps73.cps73_order_number
AND cps72_sequence_number BETWEEN
cps74_start_seq_nr AND cps74_end_seq_nr
AND cps72_sequence_number > (SELECT last_sequence_n
FROM cps72_mininum_sequence
WHERE
operation_code_v = 'MOVE_TPS01'
)
ORDER BY cps72_sequence_number ASC;
l_move_cnt NUMBER := 0;
BEGIN
dbms_output.Put_line('Started');
w_file := 'upload_vouchers_'
|| To_char (SYSDATE, 'yyyymmdd_hh24:mi');
Job_control_auto (p_job, w_file, w_code);
IF w_code >= 0 THEN
GOTO proc_exit;
END IF;
Job_control_steps_auto (p_job, w_file, 1, w_code);
IF w_code >= 0 THEN
GOTO proc_exit;
END IF;
OPEN c1;
LOOP
FETCH c1 INTO tps73;
EXIT WHEN c1%NOTFOUND;
UPDATE tps73_logical_orders
SET cps73_steps = 8
WHERE cps73_order_number = tps73.cps73_order_number;
OPEN c2;
LOOP
FETCH c2 INTO tps74;
EXIT WHEN c2%NOTFOUND;
SELECT cps08_card_type_code,
cps08_denomination_desc
INTO w_card_type, w_denom
FROM tps08_denomination_types
WHERE cps08_denomination_code = tps74.cps74_denomination;
w_counter := 0;
OPEN c3;
LOOP
FETCH c3 bulk collect INTO sq_ntab, sn_ntab, pn_ntab,
ac_ntab
limit
10000;
-- dbms_output.put_line('Total Records :'||SQL%ROWCOUNT||'---'||sq_ntab.COUNT||'-----'||sn_ntab.count);
-- dbms_output.put_line(' before exit when sql%rowcount=0..');
EXIT WHEN sq_ntab.count = 0;
BEGIN
SAVEPOINT bulk_insert;
forall i IN sn_ntab.first..sn_ntab.last
INSERT INTO tps01_cards
(cps01_serial_number,
cps01_card_type,
cps01_denomination,
cps01_date_generated,
cps01_log_phy_ind,
cps01_card_id,
cps01_outlet_code,
cps01_logical_order_nr,
cps01_current_status,
cps01_access_code,
cps01_date_modified)
VALUES (Sn_ntab (i),
w_card_type,
tps74.cps74_denomination,
SYSDATE,
'PHY',
cps01_card_id_seq.NEXTVAL,
tps73.cps73_outlet_code,
tps73.cps73_order_number,
NULL,
Ac_ntab (i),
SYSDATE);
-- FORALL i IN sn_ntab.first..sn_ntab.last
-- DELETE /*+INDEX (tps72_phy_cards TPS72_SEQ_LCL)*/
-- tps72_phy_cards PARTITION (tps72_phy_cards_map)
-- WHERE cps72_sequence_number = sq_ntab (i);
l_move_cnt := Sq_ntab(sq_ntab.last);
UPDATE cps72_mininum_sequence
SET last_sequence_n = l_move_cnt
WHERE operation_code_v = 'MOVE_TPS01'; --- bhagyaraj 8
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO bulk_insert;
Job_control_message_auto (p_job, w_file, 'BHAGYARAJ'
||
Substr (SQLERRM, 1, 110));
-- IF SQLCODE != -6502
-- THEN
-- w_error_detail := SQLERRM;
-- RAISE error_occured;
-- END IF;
FOR i IN sn_ntab.first..sn_ntab.last LOOP
--FETCH c3 INTO w_sq, w_sn, w_pn, w_ac;
--EXIT WHEN c3%NOTFOUND;
--w_counter := NVL (w_counter, 0) + 1;
BEGIN
INSERT INTO tps01_cards
(cps01_serial_number,
cps01_card_type,
cps01_denomination,
cps01_date_generated,
cps01_log_phy_ind,
cps01_card_id,
cps01_outlet_code,
cps01_logical_order_nr,
cps01_current_status,
cps01_access_code,
cps01_date_modified)
VALUES (Sn_ntab (i),
w_card_type,
tps74.cps74_denomination,
SYSDATE,
'PHY',
cps01_card_id_seq.NEXTVAL,
tps73.cps73_outlet_code,
tps73.cps73_order_number,
NULL,
Ac_ntab (i),
SYSDATE);
-- DELETE /*+INDEX (tps72_phy_cards TPS72_SEQ_LCL)*/
-- tps72_phy_cards PARTITION (tps72_phy_cards_map)
-- WHERE cps72_sequence_number = sq_ntab (i);
EXCEPTION
WHEN dup_val_on_index THEN
BEGIN
SELECT cps01_denomination,
cps01_logical_order_nr
INTO w_denomination, w_logical_order
FROM tps01_cards
WHERE cps01_serial_number = Sn_ntab (i)
;
IF w_denomination !=
tps74.cps74_denomination
AND w_logical_order !=
tps73.cps73_order_number
THEN
w_error_detail :=
'Duplicate serial number / pin '
|| Sn_ntab (i)
|| ' on insert into TPS01_CARDS'
|| utl_tcp.crlf
||
'Uploading of vouchers for order number '
||
tps73.cps73_order_number
||
' has stopped. Please investigate';
Job_control_message_auto (p_job, w_file,
w_error_detail);
RAISE error_occured;
END IF;
END;
WHEN OTHERS THEN
Job_control_message_auto (p_job, w_file,
Sn_ntab
(i)
||
'-'
||
SQLERRM);
RAISE error_occured;
END;
-- IF MOD (w_counter, 2000) = 0
-- THEN
-- COMMIT;
-- END IF;
END LOOP;
l_move_cnt := Sq_ntab(sq_ntab.last);
UPDATE cps72_mininum_sequence
SET last_sequence_n = l_move_cnt
WHERE operation_code_v = 'MOVE_TPS01';
--- bhagyaraj 9
COMMIT;
END;
-- dbms_output.put_line(' before exit when notfound..');
EXIT WHEN c3%NOTFOUND;
SELECT cps74_status
INTO l_status
FROM tps74_logical_orders_detail
WHERE cps74_order_number = tps74.cps74_order_number
AND cps74_denomination = tps74.cps74_denomination;
IF l_status = 'CA' THEN
ROLLBACK;
GOTO proc_exit;
END IF;
-- dbms_output.put_line(' after exit when notfound..');
END LOOP;
IF c3%isopen THEN
CLOSE c3;
END IF;
-- Update the detail extract completed
UPDATE tps74_logical_orders_detail
SET cps74_status = 'UL'
WHERE cps74_order_number = tps73.cps73_order_number
AND cps74_denomination = tps74.cps74_denomination;
-- IF LENGTH (w_confirm) > 1800
-- THEN
---- ppms_email.send_group ('SUPPORT',
---- 'ppms_vouchers.upload_vouchers',
---- w_confirm,
---- w_return_msg);
-- w_confirm := NULL;
-- END IF;
IF w_confirm IS NULL THEN
w_confirm :=
'The following voucher purchase orders have been successfully uploaded into TPS01_CARDS'
|| utl_tcp.crlf;
w_confirm := w_confirm
|| 'Order '
|| tps73.cps73_order_number
|| ', denomination '
|| w_denom
|| utl_tcp.crlf; -- added by prem on 16 jan 2008
ELSE
w_confirm := w_confirm
|| 'Order '
|| tps73.cps73_order_number
|| ', denomination '
|| w_denom
|| utl_tcp.crlf;
END IF;
END LOOP;
CLOSE c2;
UPDATE tps73_logical_orders
SET cps73_status = 8
WHERE cps73_order_number = tps73.cps73_order_number;
Ppms_send_email('Upload Order '
||tps73.cps73_order_number
||' Finished',
'Dear All, The Order number '
||tps73.cps73_order_number
||' has been successfully loaded into TPS01_CARDS..', w_code);
COMMIT;
END LOOP;
CLOSE c1;
Update_job_control_steps_auto (p_job, w_file, 1);
Update_job_controls_auto (p_job, w_file);
-- Notify PPMS Administrator that the vouchers have been successfully uploaded
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
<<proc_exit>>
IF w_code >= 0 THEN
Job_control_message_auto (p_job, w_file,
'Job control error has occured - completion code '
|| To_char (w_code));
END IF;
EXCEPTION
WHEN error_occured THEN
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
Job_control_message_auto (p_job, w_file, Substr (w_error_detail, 1, 120));
w_email :=
'The following error occured while uploading physical vouchers - '
|| utl_tcp.crlf
|| w_error_detail;
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_email,
-- w_return_msg);
WHEN OTHERS THEN
-- IF w_confirm IS NOT NULL
-- THEN
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_confirm,
-- w_return_msg);
-- END IF;
Job_control_message_auto (p_job, w_file, Substr (SQLERRM, 1, 120));
w_email :=
'The following error occured while uploading physical vouchers - '
|| utl_tcp.crlf
|| SQLERRM;
-- ppms_email.send_group ('SUPPORT',
-- 'ppms_vouchers.upload_vouchers',
-- w_email,
-- w_return_msg);
END;
END;
--------------------------------------------------------------------------------------------------------------------
Below is the DDL statement of Destination table and Indexes.
CREATE TABLE "PREPAID"."TPS01_CARDS"
( "CPS01_SERIAL_NUMBER" VARCHAR2(14) NOT NULL ENABLE,
"CPS01_PIN_NUMBER" NUMBER(16,0),
"CPS01_CARD_TYPE" VARCHAR2(2) NOT NULL ENABLE,
"CPS01_DENOMINATION" NUMBER(5,0) NOT NULL ENABLE,
"CPS01_DATE_GENERATED" DATE NOT NULL ENABLE,
"CPS01_LOG_PHY_IND" VARCHAR2(3) NOT NULL ENABLE,
"CPS01_CARD_ID" NUMBER(12,0) NOT NULL ENABLE,
"CPS01_OUTLET_CODE" VARCHAR2(10) NOT NULL ENABLE,
"CPS01_MSISDN" VARCHAR2(13),
"CPS01_BATCH_NUMBER" VARCHAR2(11),
"CPS01_DATE_SOLD" DATE,
"CPS01_DIST_CHANNEL" VARCHAR2(20),
"CPS01_DATE_CEASED" DATE,
"CPS01_DATE_PRINTED" DATE,
"CPS01_DATE_RECHARGE" DATE,
"CPS01_LOGICAL_ORDER_NR" VARCHAR2(20),
"CPS01_DATE_AVAILABLE" DATE,
"CPS01_CURRENT_STATUS" NUMBER(2,0),
"CPS01_ACCESS_CODE" VARCHAR2(16) NOT NULL ENABLE,
"CPS01_DATE_MODIFIED" DATE NOT NULL ENABLE,
CONSTRAINT "TPS01_PKN_1" PRIMARY KEY ("CPS01_SERIAL_NUMBER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_06"
PARTITION BY HASH ("CPS01_SERIAL_NUMBER")
(PARTITION "SYS_P146"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P147"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P148"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P149"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P150"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P151"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P152"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P153"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P154"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P155"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P156"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P157"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P158"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P159"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P160"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P161"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P162"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P163"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P164"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P165"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P166"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P167"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P168"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P169"
TABLESPACE "PPMS_DATA_06",
PARTITION "SYS_P170"
TABLESPACE "PPMS_DATA_06")
CREATE INDEX "PREPAID"."TPS01_COMP_INDXN" ON "PREPAID"."TPS01_CARDS" ("CPS01_LOGICAL_ORDER_NR", "CPS01_DENOMINATION", "CPS01_CURRENT_STATUS", "CPS01_CARD_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04"
PARALLEL 4 ;
CREATE INDEX "PREPAID"."TPS01_DATE_CEASED_INDX" ON "PREPAID"."TPS01_CARDS" "CPS01_DATE_CEASED")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04"
PARALLEL 40
CREATE INDEX "PREPAID"."TPS01_DATE_SOLD_INDX" ON "PREPAID"."TPS01_CARDS" ("CPS01_DATE_SOLD")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04"
CREATE UNIQUE INDEX "PREPAID"."TPS01_PKN_1" ON "PREPAID"."TPS01_CARDS" ("CPS01_SERIAL_NUMBER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04"
PARALLEL 4
CREATE INDEX "PREPAID"."TPS01_STATUS_INDXN" ON "PREPAID"."TPS01_CARDS" ("CPS01_CURRENT_STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_04"
PARALLEL 4
Below is the DDL statements of the Source table( the table where from data is selected for insertion)
CREATE TABLE "PREPAID"."TPS72_PHY_CARDS"
( "CPS72_SEQUENCE_NUMBER" NUMBER,
"CPS72_SERIAL_NUMBER" VARCHAR2(14),
"CPS72_PIN_NUMBER" NUMBER(16,0),
"CPS72_ACCESS_CODE" VARCHAR2(16),
"CPS72_OUTLET_CODE" VARCHAR2(10),
"CPS72_ORDER_NUMBER" VARCHAR2(20),
"CPS72_DENOM" NUMBER(5,0)
) PCTFREE 20 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01"
PARTITION BY LIST ("CPS72_DENOM")
(PARTITION "TPS72_PHY_CARDS_UMAP" VALUES (NULL)
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" NOCOMPRESS ,
PARTITION "TPS72_PHY_CARDS_MAP" VALUES (DEFAULT)
PCTFREE 20 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" NOCOMPRESS )
PARALLEL 10 ENABLE ROW MOVEMENT
CREATE INDEX "PREPAID"."TPS02_SEQ_IND" ON "PREPAID"."TPS72_PHY_CARDS" ("CPS72_SEQUENCE_NUMBER")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "TPS72_PHY_CARDS_UMAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" ,
PARTITION "TPS72_PHY_CARDS_MAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" )
PARALLEL 2
CREATE INDEX "PREPAID"."TPS72_SER_LCL" ON "PREPAID"."TPS72_PHY_CARDS" ("CPS72_SERIAL_NUMBER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_02" LOCAL
(PARTITION "TPS72_PHY_CARDS_UMAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_02" ,
PARTITION "TPS72_PHY_CARDS_MAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_INDEX_02" )
PARALLEL 30
CREATE INDEX "PREPAID"."TPS72_ORD_COMB_IDX" ON "PREPAID"."TPS72_PHY_CARDS" ("CPS72_OUTLET_CODE", "CPS72_ORDER_NUMBER", "CPS72_DENOM")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT) LOCAL
(PARTITION "TPS72_PHY_CARDS_UMAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" ,
PARTITION "TPS72_PHY_CARDS_MAP"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PPMS_DATA_01" )
PARALLEL 2
Below is the execution plan of the cursor select statement.
1 SELECT cps72_sequence_number,
2 cps72_serial_number,
3 cps72_pin_number,
4 cps72_access_code
5 FROM tps72_phy_cards partition (TPS72_PHY_CARDS_UMAP) , tps74_logical_orders_detail
6 WHERE cps74_order_number = '32240A'
7 AND CPS72_SEQUENCE_NUMBER BETWEEN 45678221 AND 45672322
8 AND CPS72_SEQUENCE_NUMBER > (
9 SELECT LAST_SEQUENCE_N FROM CPS72_MININUM_SEQUENCE
10 WHERE OPERATION_CODE_V='MOVE_TPS01'
11 )
12* ORDER BY cps72_sequence_number asc
SQL> /
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 0 (0)| | |
|* 1 | TABLE ACCESS FULL | TPS777_PPMS_USER_CONFIGURATION | 1 | 17 | 3 (0)| | |
|* 2 | FILTER | | | | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 56 | 3 (0)| | |
| 4 | PARTITION LIST SINGLE | | 1 | 49 | 0 (0)| KEY | KEY |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TPS72_PHY_CARDS | 1 | 49 | 0 (0)| 1 | 1 |
|* 6 | INDEX RANGE SCAN | TPS02_SEQ_IND | 1 | | 0 (0)| 1 | 1 |
|* 7 | TABLE ACCESS FULL | CPS72_MININUM_SEQUENCE | 1 | 17 | 3 (0)| | |
| 8 | BUFFER SORT | | 3 | 21 | 4 (25)| | |
|* 9 | INDEX RANGE SCAN | TPS74_PK | 3 | 21 | 2 (0)| | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CPS777_ACTIVE_YN"='Y' AND "CPS777_OSUSER"||"CPS777_DBUSER"=SYS_CONTEXT('USERENV','OS_USER')||SYS_CONT
EXT('USERENV','Current_User'))
2 - filter(NULL IS NOT NULL)
6 - access("CPS72_SEQUENCE_NUMBER">=45678221 AND "CPS72_SEQUENCE_NUMBER"<=45672322)
filter("CPS72_SEQUENCE_NUMBER"> (SELECT /*+ */ "LAST_SEQUENCE_N" FROM "CPS72_MININUM_SEQUENCE"
"CPS72_MININUM_SEQUENCE" WHERE "OPERATION_CODE_V"='MOVE_TPS01'))
7 - filter("OPERATION_CODE_V"='MOVE_TPS01')
9 - access("CPS74_ORDER_NUMBER"='32240A')
Note
-----
- 'PLAN_TABLE' is old version
In case of FORALL Insert statement I got the below SQL Query from v$sql for the session. And I could able to get the execution plan of the same.
SQL> INSERT INTO TPS01_CARDS (CPS01_SERIAL_NUMBER, CPS01_CARD_TYPE, CPS01_DENOMINATION, CPS01_DATE_GENERATED, CPS01_LOG_PHY_IND, CPS01_CARD_ID,
2 CPS01_OUTLET_CODE, CPS01_LOGICAL_ORDER_NR, CPS01_CURRENT_STATUS, CPS01_ACCESS_CODE, CPS01_DATE_MODIFIED)
3 VALUES ( 'IR123456' , 'R4' , '900' , SYSDATE, 'PHY', CPS01_CARD_ID_SEQ.NEXTVAL, 'SUP-ARS' , '32246A' , NULL, '2312312323' , SYSDATE)
4 /
1 row created.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 115 | 1 (0)|
| 1 | SEQUENCE | CPS01_CARD_ID_SEQ | | | |
---------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
SQL> roll
Rollback complete.
|
|
|
Re: DB file sequential read (9 merged) [message #559858 is a reply to message #559853] |
Fri, 06 July 2012 20:00 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
INSERT INTO TPS01_CARDS (CPS01_SERIAL_NUMBER, CPS01_CARD_TYPE,
CPS01_DENOMINATION, CPS01_DATE_GENERATED, CPS01_LOG_PHY_IND, CPS01_CARD_ID,
CPS01_OUTLET_CODE, CPS01_LOGICAL_ORDER_NR, CPS01_CURRENT_STATUS,
CPS01_ACCESS_CODE, CPS01_DATE_MODIFIED)
VALUES
(:B1 , :B6 , :B5 , SYSDATE, 'PHY', CPS01_CARD_ID_SEQ.NEXTVAL, :B4 , :B3 ,
NULL, :B2 , SYSDATE)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 32 569.38 3785.26 307819 679092 6197751 320000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32 569.38 3785.26 307819 679092 6197751 320000
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 296162 0.75 3156.59
latch: library cache 1 0.00 0.00
latch: object queue header operation 3 0.00 0.00
log file switch completion 5 0.09 0.22
latch: cache buffers chains 3 0.00 0.00
320000 rows were INSERTED & session waited 296162 different times to update all the indexes on this table.
INSERT completed in 3785.26 seconds & was updating indexes for 3156.59 seconds; which is/was majority of total time.
during DML, INDEX maintenance is the price that must be paid to reduce elapsed time for SELECT statements.
You can have faster INSERT & slow SELECT or you can have slower INSERT & faster SELECT.
You can't have them both be fast; but you can choose which will be preferred & fast.
|
|
|
Re: DB file sequential read (9 merged) [message #559865 is a reply to message #559858] |
Sat, 07 July 2012 01:43 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It might be possible to improve things a bit. Are you allowed to adjust the partitioning strategy? If so:
First, it is very odd to have 25 hash partitions. The Oracle docs say that hash partitioning should always be a power of two. Why not use 32 partitions?
Second, your indexes are all global non-partitioned. You should certainly make your primary key index on CPS01_SERIAL_NUMBER local, as it is also the partitioning key. What about the other indexes? Why not make them global prefixed?
Third, parallelism. You have some erratic numbers for those indexes. What is the rational behind them?
What I'm getting at is that there doesn't seem to be all that much logic behind the partitioning strategy. I'm not sure that it is optimized either for query or for DML.
hth - You have all my sympathy, tuning this sort of thing is not easy.
[Updated on: Sat, 07 July 2012 01:44] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Wed Jul 03 20:55:04 CDT 2024
|