Home » RDBMS Server » Performance Tuning » DB file sequential read (9 merged) (oracle 10g)
icon9.gif  DB file sequential read (9 merged) [message #559834] Fri, 06 July 2012 11:53 Go to next message
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, Cool
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 [message #559845 is a reply to message #559834] Fri, 06 July 2012 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 22520
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

THANKS! for narrowing the scope of the problem for us.


Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
Re: DB file sequential read (9 merged) [message #559847 is a reply to message #559834] Fri, 06 July 2012 12:32 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Man, it's impossible to read that much without proper formatting. Format your code, with a formatter such as this one,
http://www.dpriver.com/pp/sqlformat.htm
and when you paste in code or tkprof output ALWAYS enclose it within [code] tages to preserve the formatting, as described here How to use [code] tags and make your code easier to read

And by the, what is the problem?
Re: DB file sequential read (9 merged) [message #559853 is a reply to message #559847] Fri, 06 July 2012 15:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 22520
Registered: January 2009
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 Go to previous message
John Watson
Messages: 4400
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

Previous Topic: DB_BLOCK_SIZE
Next Topic: where fiter result rows save before join and grop by operation?
Goto Forum:
  


Current Time: Tue Jul 29 08:04:00 CDT 2014

Total time taken to generate the page: 0.09101 seconds