Performance issue with oracle 10g [message #412776] |
Sat, 11 July 2009 15:04  |
ashokkr24
Messages: 8 Registered: January 2007 Location: Chennai
|
Junior Member |
|
|
Hi,
My scenario is this...seems simple but somewhat tedious...
I have to delete records with duplicate email address from a table using certain criteria.
1) Contact that does not exist in employee table should be deleted.
2) Even after the deletion of records, If duplicate records exists, it implies the existing contacts are all employees and another criteria should be taken into consideration which is last_upd_dt value.The lastest contact with respect to last_upd_dt should not be deleted. apart from this all the other records can be deleted.
I have a solution with poor performance...want to check if the performance can be improved using Global Temporary Tables because I use similar queries repeatedly.Also the tables get locked and I think that may be the reason for performance issue.
PROCEDURE dup_email_diff_contact
IS
/*********************************************************************************************************/
CURSOR c_dup_diff_id
IS
SELECT DISTINCT UPPER(electronic_address_string) email
FROM sls.person_contact_mechanism pcm
WHERE UPPER(fk_person_cntct_mech_type_desc) LIKE 'E-MAIL%'
GROUP BY UPPER(electronic_address_string)
HAVING COUNT(1) > 1;
TYPE t_invalid_email IS TABLE OF c_invalid_email%ROWTYPE;
TYPE t_invalid_type IS TABLE OF c_invalid_type%ROWTYPE;
TYPE t_invalid_pcma IS TABLE OF c_invalid_pcma%ROWTYPE;
TYPE t_dup_email IS TABLE OF c_dup_email%ROWTYPE;
TYPE t_dup_email_2 IS TABLE OF c_dup_email_2%ROWTYPE;
TYPE t_dup_diff_id IS TABLE OF c_dup_diff_id%ROWTYPE;
v_invalid_pcma t_invalid_pcma;
v_invalid_email t_invalid_email;
v_invalid_type t_invalid_type;
v_dup_email t_dup_email;
v_dup_email_2 t_dup_email_2;
v_dup_diff_id t_dup_diff_id;
v_type_count NUMBER:=0;
v_type2_count NUMBER:=0;
v_type3_count NUMBER:=0;
v_inval_count NUMBER:=0;
v_usa_count NUMBER:=0;
v_brac_count NUMBER:=0;
v_apos_count NUMBER:=0;
v_space_count NUMBER:=0;
v_comma_count NUMBER:=0;
v_null_count NUMBER:=0;
v_del_dupl NUMBER:=0;
v_dup_diff_id_count NUMBER:=0;
v_last_upd_empl_count NUMBER:=0;
v_location VARCHAR2(100):= MCKB_LOAD.GET_UTL_DIR();
v_filename VARCHAR2(30);
v_file_handle UTL_FILE.FILE_TYPE;
BEGIN
v_del_dupl:=0;
BEGIN
OPEN c_dup_diff_id;
LOOP
FETCH c_dup_diff_id BULK COLLECT INTO v_dup_diff_id LIMIT 1000;
IF v_dup_diff_id.COUNT > 0
THEN
FOR m IN v_dup_diff_id.FIRST .. v_dup_diff_id.LAST
LOOP
BEGIN
SELECT COUNT(1)
INTO v_dup_diff_id_count
FROM sls.person_contact_mechanism pcm
WHERE UPPER(electronic_address_string) = v_dup_diff_id(m).email;
SELECT COUNT(1)
INTO v_last_upd_empl_count
FROM sls.person_contact_mechanism pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
AND TRIM(per.last_upd_empl_id) IS NULL;
IF v_last_upd_empl_count = 0
THEN
v_dup_diff_id_count :=0;
INSERT INTO shadow_sls.person_contact_mechanism
SELECT *
FROM sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id IN (
SELECT per.id
FROM sls.persons per,sls.person_contact_mechanism pcm
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
AND TRIM(per.last_upd_empl_id) IS NOT NULL
AND per.last_upd_empl_id NOT IN(
SELECT num
FROM ent.employees
))
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
IF SQL%ROWCOUNT > 0
THEN
DELETE sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id IN (
SELECT per.id
FROM sls.persons per,sls.person_contact_mechanism pcm
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
AND TRIM(per.last_upd_empl_id) IS NOT NULL
AND per.last_upd_empl_id NOT IN(
SELECT num
FROM ent.employees
))
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
v_del_dupl:=v_del_dupl+SQL%ROWCOUNT;
SELECT COUNT(1)
INTO v_dup_diff_id_count
FROM sls.person_contact_mechanism pcm
WHERE UPPER(electronic_address_string) = v_dup_diff_id(m).email;
IF v_dup_diff_id_count > 1
THEN
INSERT INTO shadow_sls.person_contact_mechanism
SELECT *
FROM sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM ( SELECT per.id,PER.LAST_UPD_DT
FROM SLS.PERSON_CONTACT_MECHANISM pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY PER.LAST_UPD_DT DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
IF SQL%ROWCOUNT > 0
THEN
DELETE sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM (
SELECT per.id,per.last_upd_dt
FROM sls.person_contact_mechanism pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY PER.LAST_UPD_DT DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
v_del_dupl:=v_del_dupl+SQL%ROWCOUNT;
END IF;
END IF;
END IF;
ELSIF v_last_upd_empl_count < v_dup_diff_id_count
THEN
v_dup_diff_id_count :=0;
INSERT INTO shadow_sls.person_contact_mechanism
SELECT *
FROM sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id IN (
SELECT per.id
FROM sls.persons per,sls.person_contact_mechanism pcm
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
AND TRIM(per.last_upd_empl_id) IS NOT NULL
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND per.last_upd_empl_id NOT IN(
SELECT num
FROM ent.employees
))
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
IF SQL%ROWCOUNT > 0
THEN
DELETE sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id IN (
SELECT per.id
FROM sls.persons per,sls.person_contact_mechanism pcm
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
AND TRIM(per.last_upd_empl_id) IS NOT NULL
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND per.last_upd_empl_id NOT IN(
SELECT num
FROM ent.employees
))
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
v_del_dupl:=v_del_dupl+SQL%ROWCOUNT;
SELECT COUNT(1)
INTO v_dup_diff_id_count
FROM sls.person_contact_mechanism pcm
WHERE UPPER(electronic_address_string) = v_dup_diff_id(m).email
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%';
IF v_dup_diff_id_count > 1
THEN
INSERT INTO shadow_sls.person_contact_mechanism
SELECT *
FROM sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM (
SELECT per.id,PER.LAST_UPD_DT
FROM SLS.PERSON_CONTACT_MECHANISM pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY PER.LAST_UPD_DT DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
IF SQL%ROWCOUNT > 0
THEN
DELETE sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM (
SELECT per.id,per.last_upd_dt
FROM sls.person_contact_mechanism pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY PER.LAST_UPD_DT DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
v_del_dupl:=v_del_dupl+SQL%ROWCOUNT;
END IF;
END IF;
END IF;
ELSIF v_last_upd_empl_count = v_dup_diff_id_count
THEN
INSERT INTO shadow_sls.person_contact_mechanism
SELECT *
FROM sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM ( SELECT per.id,per.last_upd_dt
FROM sls.person_contact_mechanism pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY per.last_upd_dt DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
IF SQL%ROWCOUNT > 0
THEN
DELETE sls.person_contact_mechanism pcm
WHERE pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND pcm.fk_persons_id NOT IN (
SELECT id
FROM ( SELECT per.id,per.last_upd_dt
FROM sls.person_contact_mechanism pcm,sls.persons per
WHERE pcm.fk_persons_id = per.id
AND pcm.fk_person_cntct_mech_type_desc LIKE 'E-MAIL%'
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email
ORDER BY PER.LAST_UPD_DT DESC
)
WHERE ROWNUM =1
)
AND UPPER(pcm.electronic_address_string) = v_dup_diff_id(m).email;
v_del_dupl:=v_del_dupl+SQL%ROWCOUNT;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER ERROR OCCURED IN dup diff id'|| v_dup_diff_id(m).email|| SQLERRM ||' '||SQLCODE);
RAISE;
END;
END LOOP;
END IF;
COMMIT;
EXIT WHEN c_dup_diff_id%NOTFOUND;
END LOOP;
CLOSE c_dup_diff_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER ERROR OCCURED IN dup diff id'|| SQLERRM ||' '||SQLCODE);
RAISE;
END;
DBMS_OUTPUT.PUT_LINE('COUNT Of Duplicate records for different contacts '||v_del_dupl);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE ('Error encountered in mckb_load.email_cleanup.dup_email_diff_contact : ' || SUBSTR (SQLERRM, 1, 200));
-- Re-raise exception
RAISE;
END dup_email_diff_contact; /* procedure dup_email_diff_contact */
|
|
|
Re: Performance issue with oracle 10g [message #412779 is a reply to message #412776] |
Sat, 11 July 2009 15:13   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem code here
ALTER SESSION SET SQL_TRACE=FALSE;
now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>
post the contents of trace_results.txt back here
>I have a solution with poor performance.
I am not surprised.
row by row is slow by slow.
Why not just 1 or more DELETE FROM sls.person_contact_mechanism pcm WHERE?
Why (ab)use PL/SQL at all?
>INSERT INTO shadow_sls.person_contact_mechanism
Why doing INSERT when goal is to do DELETE?
>Also the tables get locked and I think that may be the reason for performance issue.
Provide proof.
[Updated on: Sat, 11 July 2009 15:37] Report message to a moderator
|
|
|
|
Re: Performance issue with oracle 10g [message #412791 is a reply to message #412776] |
Sat, 11 July 2009 21:39   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref1517
states:
Quote: |
EXPLAIN
Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN statement after connecting to Oracle with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges. TKPROF takes longer to process a large trace file if the EXPLAIN option is used.
|
and
Quote: |
This example runs TKPROF, accepts a trace file named dlsun12_jane_fg_sqlplus_007.trc, and writes a formatted output file named outputa.prf:
TKPROF dlsun12_jane_fg_sqlplus_007.trc OUTPUTA.PRF
EXPLAIN=scott/tiger TABLE=scott.temp_plan_table_a INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)
This example is likely to be longer than a single line on the screen, and you might need to use continuation characters, depending on the operating system.
Note the other parameters in this example:
*
The EXPLAIN value causes TKPROF to connect as the user scott and use the EXPLAIN PLAN statement to generate the execution plan for each traced SQL statement. You can use this to get access paths and row source counts.
Note:
If the cursor for a SQL statement is not closed, TKPROF output does not automatically include the actual execution plan of the SQL statement. In this situation, you can use the EXPLAIN option with TKPROF to generate an execution plan.
|
[Updated on: Sat, 11 July 2009 23:45] by Moderator Report message to a moderator
|
|
|
|
|
|