Home » RDBMS Server » Performance Tuning » PL/SQL help (performance) (Win Xp, Oracle 10g)
| PL/SQL help (performance) [message #527673] |
Wed, 19 October 2011 13:07  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
I have a performance issue (mostly) here...Please check the code & let me know what would be the better code to do this...
1) The table CLOB_CLOBJECT_CDA has the columns described below...
Explaining only those fields which are important in this context
-- CDA_STEP_ID : Basically a Sequence
-- CLOBJECT_SOURCE1_ID : Every id has got a set of records
-- CLOBJECT_SOURCE2_ID : Every id has got a set of records
-- LVL : There are total 8 levels..
This is the main aim :
1) There are total 16 million rows..(limited to 10 rows here)
2) We need to go through level by level (LVL column) & insert the intersection records (CLOBJECT_SOURCE1_ID intersect CLOBJECT_SOURCE2_ID)
into another table...but this is how it goes..
Level (LVL column) 3's basically have CLOBJECT_SOURCE1_ID as level (LVL column) 2 CDA_STEP_ID's..
(consider the statement --** where CLOBJECT_SOURCE1_ID = 285 which is same as 1st insert statement step id)..
The above process goes for next levels until 8..
So for ex :
We go through the first insert statement and insert the insertion records only when both CLOBJECT_SOURCE1_ID & CLOBJECT_SOURCE2_ID has got records ..
If we don't find any records for both of them we should skip the corresponding step id when we go to the next levels...
Let's go through the 1st insert statement...
-- We have CDA_STEP_ID = 285 & two sources CLOBJECT_SOURCE1_ID as 19 & CLOBJECT_SOURCE2_ID as 74...
-- We see the table CLOBJECT_COUNTS & check whether we have counts for both 19 & 74 ..(In fact we insert counts into this table only if they have records)
-- If so, we insert the intersection records into CDA_MRN_RESULTS ( we do have counts for both of them..) with CDA_STEP_ID 285...
-- Then we insert the step id which is 285 along with the count into CLOBJECT_COUNTS..
Let's go through another insert statement...
-- Consider CDA_STEP_ID = 288 which has two sources CLOBJECT_SOURCE1_ID as 19 & CLOBJECT_SOURCE2_ID as 92...
-- We see the table CLOBJECT_COUNTS & check whether we have counts for both 19 & 92 ..(we have records for 19 but not for 92)
-- So we should not proceed with this..& also skip all those records (future records with increasing levels..basically level 3's) which have got 288 as CLOBJECT_SOURCE1_ID..
(As said earlier that the present CDA_STEP_ID will always be CLOBJECT_SOURCE1_ID in the next level)...
I wrote the following code which is after the statement...
Let me have the create & insert statements here..
create table CLOB_CLOBJECT_CDA
(
CDA_STEP_ID NUMBER,
CDA_ID NUMBER,
CDA_SEQ_NUMBER NUMBER,
CLOBJECT_SOURCE1_TYPE VARCHAR2(3000),
CLOBJECT_SOURCE1_ID NUMBER,
CLOBJECT_OPERATOR VARCHAR2(3000),
CLOBJECT_SOURCE2_TYPE VARCHAR2(3000),
CLOBJECT_SOURCE2_ID NUMBER,
LVL NUMBER
);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (285, 285, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 74, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (286, 286, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 75, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (287, 287, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 91, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (288, 288, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 92, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4869, 4869, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 91, 3); -- **
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4870, 4870, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 92, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4871, 4871, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 93, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4880, 4880, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 91, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4881, 4881, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 92, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4882, 4882, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 93, 3);
create table CDA_MRN_RESULTS
(
CLOBJECT_ID NUMBER,
CDA_STEP_ID NUMBER,
MRN NUMBER,
INSERT_DATE_TIME DATE
);
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 3, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 4, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 6, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (91, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (91, 3, to_date('19-10-2011', 'dd-mm-yyyy'));
create table CLOBJECT_COUNTS
(
CDA_STEP_ID NUMBER,
CLOBJECT_COUNT NUMBER,
DATE_TIME DATE
);
Insert into CLOBJECT_COUNTS values (19,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (74,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (75,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (91,2, to_date('19-10-2011', 'dd-mm-yyyy'));
The code which I wrote :
declare
cursor c1 (p_level varchar2 ) is
Select * from clob_clobject_cda
where lvl = p_level ;
TYPE V_TT IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
L_TT V_TT;
v1 number;
v2 number;
v_step_id number;
v_operator varchar2(100) := '';
begin
for i in 2..8 loop
open c1(i);
LOOP
FETCH C1 BULK COLLECT INTO L_TT LIMIT 500;
FOR indx IN 1 .. L_TT.COUNT
LOOP
v1 := L_TT(indx).clobject_source1_id;
v2 := L_TT(indx).clobject_source2_id;
v_step_id := L_TT(indx).cda_step_id;
v_operator := L_TT(indx).clobject_operator;
Execute Immediate ('Insert into cda_mrn_results Select --+ parallel (cm 128)
distinct ' || v_step_id || ', mrn, trunc(sysdate) dt from cda_mrn_results cm
where cda_step_id = ' || v1 || '
and cda_step_id in (Select cda_step_id from clobject_counts) ' ||
v_operator ||
' Select --+ parallel (cm 128)
distinct ' || v_step_id || ', mrn, trunc(sysdate) dt from cda_mrn_results cm
where cda_step_id = ' || v2 || '
and cda_step_id in (Select cda_step_id from clobject_counts) ' );
Insert --+ Append
into clobject_counts Select cda_step_id, count(distinct mrn),
insert_date_time dt from cda_mrn_results where cda_step_id = v_step_id group by cda_step_id,insert_date_time;
COMMIT;
END LOOP;
EXIT WHEN L_TT.COUNT = 0;
END LOOP;
CLOSE C1;
End Loop;
Commit;
End;
|
|
|
|
|
|
| Re: PL/SQL help (performance) [message #527681 is a reply to message #527679] |
Wed, 19 October 2011 13:46  |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Hi,
Actually I was looking for another code..as my lead is not interested in the code which I wrote..Just ignore the code & let me know another way of writing the code for the above..
|
|
|
|
| Re: PL/SQL help (performance) [message #527683 is a reply to message #527679] |
Wed, 19 October 2011 13:59 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
|
He denied the code..No reason why he said like that..May be the approach is wrong as the code which I wrote kept on running for a day (he mentioned) but never stopped..I need to skip the future steps which involves zero rows for the step in previous level..Is there a better way of writing the code for the above scenario...I don't have permission for sql trace/tkprof..
|
|
|
|
|
|
|
|
|
|
| Re: PL/SQL help (performance) [message #527717 is a reply to message #527714] |
Wed, 19 October 2011 19:59 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Yes that's a small issue which I forgot to take it out..Sorry about that..Actually lot of looping is involved..Needed a better way than this approach...Don't have privileges to view explain plan/trace/tkprof...
Hi,
I have a performance issue (mostly) here...Please check the code & let me know what would be the better code to do this...
1) The table CLOB_CLOBJECT_CDA has the columns described below...
Explaining only those fields which are important in this context
-- CDA_STEP_ID : Basically a Sequence
-- CLOBJECT_SOURCE1_ID : Every id has got a set of records
-- CLOBJECT_SOURCE2_ID : Every id has got a set of records
-- LVL : There are total 8 levels..
This is the main aim :
1) There are total 16 million rows..(limited to 10 rows here)
2) We need to go through level by level (LVL column) & insert the intersection records (CLOBJECT_SOURCE1_ID intersect CLOBJECT_SOURCE2_ID)
into another table...but this is how it goes..
Level (LVL column) 3's basically have CLOBJECT_SOURCE1_ID as level (LVL column) 2 CDA_STEP_ID's..
(consider the statement --** where CLOBJECT_SOURCE1_ID = 285 which is same as 1st insert statement step id)..
The above process goes for next levels until 8..
So for ex :
We go through the first insert statement and insert the insertion records only when both CLOBJECT_SOURCE1_ID & CLOBJECT_SOURCE2_ID has got records ..
If we don't find any records for both of them we should skip the corresponding step id when we go to the next levels...
Let's go through the 1st insert statement...
-- We have CDA_STEP_ID = 285 & two sources CLOBJECT_SOURCE1_ID as 19 & CLOBJECT_SOURCE2_ID as 74...
-- We see the table CLOBJECT_COUNTS & check whether we have counts for both 19 & 74 ..(In fact we insert counts into this table only if they have records)
-- If so, we insert the intersection records into CDA_MRN_RESULTS ( we do have counts for both of them..) with CDA_STEP_ID 285...
-- Then we insert the step id which is 285 along with the count into CLOBJECT_COUNTS..
Let's go through another insert statement...
-- Consider CDA_STEP_ID = 288 which has two sources CLOBJECT_SOURCE1_ID as 19 & CLOBJECT_SOURCE2_ID as 92...
-- We see the table CLOBJECT_COUNTS & check whether we have counts for both 19 & 92 ..(we have records for 19 but not for 92)
-- So we should not proceed with this..& also skip all those records (future records with increasing levels..basically level 3's) which have got 288 as CLOBJECT_SOURCE1_ID..
(As said earlier that the present CDA_STEP_ID will always be CLOBJECT_SOURCE1_ID in the next level)...
I wrote the following code which is after the statement...
Let me have the create & insert statements here..
create table CLOB_CLOBJECT_CDA
(
CDA_STEP_ID NUMBER,
CDA_ID NUMBER,
CDA_SEQ_NUMBER NUMBER,
CLOBJECT_SOURCE1_TYPE VARCHAR2(3000),
CLOBJECT_SOURCE1_ID NUMBER,
CLOBJECT_OPERATOR VARCHAR2(3000),
CLOBJECT_SOURCE2_TYPE VARCHAR2(3000),
CLOBJECT_SOURCE2_ID NUMBER,
LVL NUMBER
);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (285, 285, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 74, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (286, 286, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 75, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (287, 287, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 91, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (288, 288, 1, 'CLOBJECT', 19, 'INTERSECT', 'CLOBJECT', 92, 2);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4869, 4869, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 91, 3); -- **
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4870, 4870, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 92, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4871, 4871, 1, 'CDA_STEP', 285, 'INTERSECT', 'CLOBJECT', 93, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4880, 4880, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 91, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4881, 4881, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 92, 3);
insert into clob_clobject_cda (CDA_STEP_ID, CDA_ID, CDA_SEQ_NUMBER, CLOBJECT_SOURCE1_TYPE, CLOBJECT_SOURCE1_ID, CLOBJECT_OPERATOR, CLOBJECT_SOURCE2_TYPE, CLOBJECT_SOURCE2_ID, LVL)
values (4882, 4882, 1, 'CDA_STEP', 286, 'INTERSECT', 'CLOBJECT', 93, 3);
create table CDA_MRN_RESULTS
(
CDA_STEP_ID NUMBER,
MRN NUMBER,
INSERT_DATE_TIME DATE
);
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (19, 3, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (74, 4, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 1, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (75, 6, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (91, 2, to_date('19-10-2011', 'dd-mm-yyyy'));
insert into cda_mrn_results (CDA_STEP_ID, MRN, INSERT_DATE_TIME)
values (91, 3, to_date('19-10-2011', 'dd-mm-yyyy'));
create table CLOBJECT_COUNTS
(
CDA_STEP_ID NUMBER,
CLOBJECT_COUNT NUMBER,
DATE_TIME DATE
);
Insert into CLOBJECT_COUNTS values (19,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (74,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (75,3, to_date('19-10-2011', 'dd-mm-yyyy'));
Insert into CLOBJECT_COUNTS values (91,2, to_date('19-10-2011', 'dd-mm-yyyy'));
The code which I wrote :
declare
cursor c1 (p_level varchar2 ) is
Select * from clob_clobject_cda
where lvl = p_level ;
TYPE V_TT IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
L_TT V_TT;
v1 number;
v2 number;
v_step_id number;
v_operator varchar2(100) := '';
begin
for i in 2..8 loop
open c1(i);
LOOP
FETCH C1 BULK COLLECT INTO L_TT LIMIT 500;
FOR indx IN 1 .. L_TT.COUNT
LOOP
v1 := L_TT(indx).clobject_source1_id;
v2 := L_TT(indx).clobject_source2_id;
v_step_id := L_TT(indx).cda_step_id;
v_operator := L_TT(indx).clobject_operator;
Execute Immediate ('Insert into cda_mrn_results Select --+ parallel (cm 128)
distinct ' || v_step_id || ', mrn, trunc(sysdate) dt from cda_mrn_results cm
where cda_step_id = ' || v1 || '
and cda_step_id in (Select cda_step_id from clobject_counts) ' ||
v_operator ||
' Select --+ parallel (cm 128)
distinct ' || v_step_id || ', mrn, trunc(sysdate) dt from cda_mrn_results cm
where cda_step_id = ' || v2 || '
and cda_step_id in (Select cda_step_id from clobject_counts) ' );
Insert --+ Append
into clobject_counts Select cda_step_id, count(distinct mrn),
insert_date_time dt from cda_mrn_results where cda_step_id = v_step_id group by cda_step_id,insert_date_time;
COMMIT;
END LOOP;
EXIT WHEN L_TT.COUNT = 0;
END LOOP;
CLOSE C1;
End Loop;
Commit;
End;
|
|
|
|
| Re: PL/SQL help (performance) [message #527727 is a reply to message #527717] |
Wed, 19 October 2011 22:03 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- starting data:
SCOTT@orcl_11gR2> select cda_step_id,
2 clobject_source1_id,
3 clobject_source2_id
4 from clob_clobject_cda
5 /
CDA_STEP_ID CLOBJECT_SOURCE1_ID CLOBJECT_SOURCE2_ID
----------- ------------------- -------------------
285 19 74
286 19 75
287 19 91
288 19 92
4870 285 92
4871 285 93
4880 286 91
4881 286 92
4882 286 93
9 rows selected.
SCOTT@orcl_11gR2> select * from cda_mrn_results
2 /
CDA_STEP_ID MRN INSERT_DA
----------- ---------- ---------
19 1 19-OCT-11
19 2 19-OCT-11
19 3 19-OCT-11
74 1 19-OCT-11
74 2 19-OCT-11
74 4 19-OCT-11
75 1 19-OCT-11
75 2 19-OCT-11
75 6 19-OCT-11
91 2 19-OCT-11
91 3 19-OCT-11
11 rows selected.
SCOTT@orcl_11gR2> select * from clobject_counts
2 /
CDA_STEP_ID CLOBJECT_COUNT DATE_TIME
----------- -------------- ---------
19 3 19-OCT-11
74 3 19-OCT-11
75 3 19-OCT-11
91 2 19-OCT-11
4 rows selected.
-- indexes and statistics:
SCOTT@orcl_11gR2> create index ccc_source1_id_step_id
2 on clob_clobject_cda (clobject_source1_id, cda_step_id)
3 /
Index created.
SCOTT@orcl_11gR2> create index ccc_source2_id_step_id
2 on clob_clobject_cda (clobject_source2_id, cda_step_id)
3 /
Index created.
SCOTT@orcl_11gR2> create index cmr_cda_step_id_mrn
2 on cda_mrn_results (cda_step_id, mrn)
3 /
Index created.
SCOTT@orcl_11gR2> create index cc_cda_step_id
2 on clobject_counts (cda_step_id)
3 /
Index created.
SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'CLOB_CLOBJECT_CDA')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'CDA_MRN_RESULTS')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (user, 'CLOBJECT_COUNTS')
PL/SQL procedure successfully completed.
-- inserts:
SCOTT@orcl_11gR2> Insert into cda_mrn_results
2 (cda_step_id, mrn, insert_date_time)
3 select ccc.cda_step_id, cm1.mrn, trunc (sysdate) dt
4 from clob_clobject_cda ccc,
5 cda_mrn_results cm1,
6 clobject_counts cc
7 where ccc.clobject_source1_id = cm1.cda_step_id
8 and cm1.cda_step_id = cc.cda_step_id
9 intersect
10 select ccc.cda_step_id, cm2.mrn, trunc (sysdate) dt
11 from clob_clobject_cda ccc,
12 cda_mrn_results cm2,
13 clobject_counts cc
14 where ccc.clobject_source2_id = cm2.cda_step_id
15 and cm2.cda_step_id = cc.cda_step_id
16 /
6 rows created.
SCOTT@orcl_11gR2> Insert into clobject_counts
2 (cda_step_id, clobject_count, date_time)
3 Select cmr.cda_step_id,
4 count (distinct cmr.mrn),
5 cmr.insert_date_time
6 from cda_mrn_results cmr,
7 clob_clobject_cda ccc
8 where cmr.cda_step_id = ccc.cda_step_id
9 group by cmr.cda_step_id, cmr.insert_date_time
10 /
3 rows created.
-- results:
SCOTT@orcl_11gR2> select * from cda_mrn_results
2 /
CDA_STEP_ID MRN INSERT_DA
----------- ---------- ---------
19 1 19-OCT-11
19 2 19-OCT-11
19 3 19-OCT-11
74 1 19-OCT-11
74 2 19-OCT-11
74 4 19-OCT-11
75 1 19-OCT-11
75 2 19-OCT-11
75 6 19-OCT-11
91 2 19-OCT-11
91 3 19-OCT-11
285 1 19-OCT-11
285 2 19-OCT-11
286 1 19-OCT-11
286 2 19-OCT-11
287 2 19-OCT-11
287 3 19-OCT-11
17 rows selected.
SCOTT@orcl_11gR2> select * from clobject_counts
2 /
CDA_STEP_ID CLOBJECT_COUNT DATE_TIME
----------- -------------- ---------
19 3 19-OCT-11
74 3 19-OCT-11
75 3 19-OCT-11
91 2 19-OCT-11
285 2 19-OCT-11
286 2 19-OCT-11
287 2 19-OCT-11
7 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: PL/SQL help (performance) [message #527884 is a reply to message #527727] |
Thu, 20 October 2011 11:33 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
|
I needed a PL/SQL code to this & my code has better performance...Only problem with my code is a lot of looping is involved & needed a better way to skip future levels where it involves a present step id which gives zero output ...by checking in clobject counts table..
|
|
|
|
|
|
| Re: PL/SQL help (performance) [message #527892 is a reply to message #527886] |
Thu, 20 October 2011 12:54 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
ORA 01031 : INSUFFICIENT PRIVILEGES
I have been saying this right from the start..
I just replaced my sql query insert statements with the insert statements shown by Barbara..& it took double the amount of time..
|
|
|
|
|
|
|
|
| Re: PL/SQL help (performance) [message #527911 is a reply to message #527907] |
Thu, 20 October 2011 15:17 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
Actually I never asked for anyone to do my code..If so, I wouldn't have pasted the code which I wrote...I am a basic PL/SQL learner..SO I don't have any idea of tuning & even though I try to do it, there are no privileges to make me do it..I am absolutely thankful to her as she helped me several times...
Barbara, I didn't get it where exactly I was going to never ending..
I couldn't exactly run your code mainly because..
1) The code which u mentioned will stop at level 2
2) As I mentioned, as we go on to future levels, cda_step_id becomes source_1_id . So we need to skip the future rows which has zero counts for the corresponding step id by looking at clobject_counts table..how do we achieve this??
3) So I am hereby attaching the pseudo code..Let me know whether my code is exactly performing it or not..
4) The code which I wrote, I actually tested for level 2..it's done in 2 min..As we go on to the next levels..it's taking lot of time..
for(i in level 2:8){
for(j in step1:stepN in level i){
if(source 1 > 0 patients AND source 2 > 0 patients){#use table COUNTS to check this
patient_list = source1 INTERSECT source2
store patient_list for step j in Table CDA_MRN_RESULTS
store count of patient_list for step j in Table CLOBJECT_COUNTS
}
else { skip row;}
}
}
I do apologize for any inconvenience caused...bcoz as a starter, u have to think from my perspective..
|
|
|
|
| Re: PL/SQL help (performance) [message #527918 is a reply to message #527911] |
Thu, 20 October 2011 15:52 |
|
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
rajivn786 wrote on Thu, 20 October 2011 13:17
I didn't get it where exactly I was going to never ending..
In one loop, you insert into cda_mrn_results and clobject_counts. Then in the next loop, there are more rows to insert into cda_mrn_results as a result of the insert into clobject_counts and more rows to insert into clobject_counts as a result of the insert into cda_mrn and so on and so on and so on, for each loop.
rajivn786 wrote on Thu, 20 October 2011 13:17
I couldn't exactly run your code mainly because..
1) The code which u mentioned will stop at level 2
I don't understand. Certainly, you can run what I ran and see that it produces the desired results.
rajivn786 wrote on Thu, 20 October 2011 13:17
2) As I mentioned, as we go on to future levels, cda_step_id becomes source_1_id . So we need to skip the future rows which has zero counts for the corresponding step id by looking at clobject_counts table..how do we achieve this??
3) So I am hereby attaching the pseudo code..Let me know whether my code is exactly performing it or not..
4) The code which I wrote, I actually tested for level 2..it's done in 2 min..As we go on to the next levels..it's taking lot of time..
for(i in level 2:8){
for(j in step1:stepN in level i){
if(source 1 > 0 patients AND source 2 > 0 patients){#use table COUNTS to check this
patient_list = source1 INTERSECT source2
store patient_list for step j in Table CDA_MRN_RESULTS
store count of patient_list for step j in Table CLOBJECT_COUNTS
}
else { skip row;}
}
}
I don't understand at all. Maybe someone else can understand and help you.
|
|
|
|
| Re: PL/SQL help (performance) [message #528117 is a reply to message #527918] |
Fri, 21 October 2011 14:01 |
rajivn786
Messages: 161
Registered: January 2010
|
Senior Member |
|
|
I only solved the issue..It takes 1 hr to complete all 2 million rows..any suggestions welcome to reduce the time..
declare
cursor c1(p_level varchar2) is
Select *
from clob_clobject_cda
where lvl = p_level
--and cda_step_id = 8858
/*and cda_step_id in (96, 135, 789)*/;
TYPE V_TT IS TABLE OF C1%ROWTYPE INDEX BY PLS_INTEGER;
L_TT V_TT;
v1 number := 0;
v2 number := 0;
v_1 number := 0;
v_2 number := 0;
v_step_id number := 0;
v_found number := 0;
v_operator varchar2(100) := '';
V_SQL_CLOB varchar2(2000) := '';
V_SQL_STEP varchar2(2000) := '';
V_SQL_COUNT varchar2(2000) := '';
begin
for i in 2 .. 8 loop
open c1(i);
LOOP
FETCH C1 BULK COLLECT
INTO L_TT LIMIT 1000;
FOR indx IN 1 .. L_TT.COUNT LOOP
v1 := L_TT(indx).clobject_source1_id;
v2 := L_TT(indx).clobject_source2_id;
v_step_id := L_TT(indx).cda_step_id;
v_operator := L_TT(indx).clobject_operator;
V_SQL_CLOB := 'Insert
into cda_mrn_results Select --+ index(cm CDA_MRN_CLOB_STEP_IDX) index(cm CDA_MRN_CLOB_CLOB_IDX)
distinct null,' ||
v_step_id ||
',mrn,trunc(sysdate) dt from cda_mrn_results cm
where clobject_id = ' || v1 || ' ' ||
v_operator || ' Select --+ index(cm CDA_MRN_CLOB_STEP_IDX) index(cm CDA_MRN_CLOB_CLOB_IDX)
distinct null,' || v_step_id ||
',mrn,trunc(sysdate) dt from cda_mrn_results cm
where clobject_id = ' || v2;
V_SQL_STEP := 'Insert
into cda_mrn_results Select --+ index(cm CDA_MRN_CLOB_STEP_IDX)
distinct null,' ||
v_step_id ||
',mrn,trunc(sysdate) dt from cda_mrn_results cm
where cda_step_id = ' || v1 || ' ' ||
v_operator || ' Select --+index(cm CDA_MRN_CLOB_CLOB_IDX)
distinct null,' || v_step_id ||
',mrn,trunc(sysdate) dt from cda_mrn_results cm
where clobject_id = ' || v2;
V_SQL_COUNT := 'Insert --+ Append
into clobject_counts Select --+ index(cm CDA_MRN_CLOB_STEP_IDX)
null,cda_step_id, count(distinct mrn),
insert_date_time dt from cda_mrn_results cmr
where cda_step_id = ' || v_step_id || ' group by cda_step_id,insert_date_time' ;
begin
If i = 2
then
Select 1 into v_1
from dual
where v1 in (Select clobject_id from clobject_counts)
and v2 in (Select clobject_id from clobject_counts);
--dbms_output.put_line (v_1);
if v_1 = 1
then
Execute Immediate (V_SQL_CLOB);
--dbms_output.put_line (V_SQL_CLOB);
end if;
elsif i > 2
then
Select 1 into v_2
from dual
where v1 in (Select cda_step_id from clobject_counts)
and v2 in (Select clobject_id from clobject_counts);
--dbms_output.put_line (v_2);
if v_2 = 1
then
Execute Immediate (V_SQL_STEP);
--dbms_output.put_line (V_SQL_STEP);
end if;
End if;
/*exception
when no_data_found then null;
end; */
--dbms_output.put_line (V_SQL_COUNT);
Execute Immediate (V_SQL_COUNT);
COMMIT;
exception
when no_data_found then null;
end;
V_1 := 0;
V_2 := 0;
V1 := 0;
V2 := 0;
v_step_id := 0;
END LOOP;
EXIT WHEN L_TT.COUNT = 0;
END LOOP;
CLOSE C1;
End Loop;
End;
|
|
|
|
| Re: PL/SQL help (performance) [message #528190 is a reply to message #528117] |
Sat, 22 October 2011 16:04 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
|
The idea of Barbara with sql instead of plsql is very good. If her sql's are correct (you shoud check it), then you can tune them. You need privileges to work with dbms_xplan.display_cursor for that (select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL). Ask your DBA, if you can get these privileges. This way ist much more promising, than improving of your code.
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 11:16:18 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
