Home » RDBMS Server » Performance Tuning » Help to alternate or rewrite the query (5 threads merged by bb) (oracle10.2.0.2,Unix)
Help to alternate or rewrite the query (5 threads merged by bb) [message #554028] |
Wed, 09 May 2012 23:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
krajasekhar.v
Messages: 36 Registered: May 2007 Location: bangalore
|
Member |
|
|
Hi,
when i'm trying to execute below procedure it's taking 5 min.could some one help to query re write / any other solution .
variable v_status VARCHAR2;
variable v_errortext VARCHAR2;
exec ecoursework.insert_exm_additional_samples('crx360',2009, 'MAY','026753','ENGLISH A1','ENGLISH A1','HL','ENGLISH','INTERNAL ASSESSMENT (ORAL)',:v_status,:v_errortext);
print v_status;
print v_errortext;
complete procedure
PROCEDURE insert_exm_additional_samples( v_candidate_id_list ibis.candidate_component_reg.candidate%type,
v_year ibis.candidate_component_reg.year%type,
v_month ibis.candidate_component_reg.month%type,
v_examiner_code ibis.examiner_moderator_allocation.examiner_code%type DEFAULT '',
v_subject ibis.candidate_component_reg.subject%type,
v_subject_option ibis.candidate_component_reg.subject_option%type DEFAULT '',
v_lvl ibis.candidate_component_reg.lvl%type DEFAULT '',
v_language ibis.candidate_component_reg.language%type DEFAULT '',
v_component ibis.candidate_component_reg.component%type DEFAULT '',
v_status OUT VARCHAR2,
v_errortext OUT VARCHAR2)
AS
BEGIN
v_status := 'SUCCESS';
v_errortext := 'SUCCESS';
INSERT INTO ibis.exam_sample_mark_additional(year,month,nolang_code,examiner_code,moderator_code,candidate,session_id)
SELECT
ccr.split_session_year as year,
ccr.split_session_month as month,
ep.no_lang_code,
ema.examiner_code,
ema.moderator_code,
ccr.candidate as candidate_code,
get_cand_session_number(ccr.split_session_year,ccr.split_session_month,ccr.candidate) as candidate_session_number
FROM
ibis.candidate_component_reg ccr
INNER JOIN ibis.candidate_examiner_allocation cea
ON ccr.split_session_year= cea.year
AND ccr.split_session_month=cea.month
AND ccr.paper_code= cea.paper_code
AND ccr.candidate = cea.candidate
AND ccr.assessment_school = cea.assessment_school
AND cea.examiner_code = v_examiner_code
INNER JOIN ibis.examiner_moderator_allocation ema
ON ccr.split_session_year =ema.year
AND ccr.split_session_month =ema.month
AND ema.examiner_code = cea.examiner_code
INNER JOIN ibis.examination_paper ep
ON ep.year= ccr.split_session_year
AND ep.month = ccr.split_session_month
AND ep.paper_code = ccr.paper_code
WHERE
ccr.split_session_year =v_year
AND ccr.split_session_month =v_month
AND ccr.subject=v_subject
AND ccr.subject_option=nvl(v_subject_option,ccr.subject_option)
AND ccr.language=nvl(v_language, ccr.language)
AND ccr.component=v_component
AND ccr.lvl=v_lvl
AND ccr.candidate IN (SELECT * FROM TABLE(SPLIT(v_candidate_id_list)));
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
v_status := 'FAILED';
v_errortext := 'No data found: '||sqlerrm;
WHEN OTHERS THEN
ROLLBACK;
v_status := 'FAILED';
v_errortext := 'Others: '||sqlerrm;
execution paln for select query
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3212494293
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 343 | 1961 (5)| 00:00:30 |
|* 1 | HASH JOIN SEMI | | 1 | 343 | 1961 (5)| 00:00:30 |
| 2 | NESTED LOOPS | | 1 | 341 | 1949 (5)| 00:00:30 |
| 3 | NESTED LOOPS | | 1 | 300 | 1947 (5)| 00:00:30 |
| 4 | NESTED LOOPS | | 1 | 72 | 1944 (5)| 00:00:30 |
| 5 | TABLE ACCESS BY INDEX ROWID | CANDIDATE_EXAMINER_ALLOCATION | 1 | 49 | 1942 (5)| 00:00:30 |
|* 6 | INDEX RANGE SCAN | RFL | 1 | | 1941 (5)| 00:00:30 |
|* 7 | INDEX RANGE SCAN | PK_EXAMINER_MODERATOR_ALLOC | 1 | 23 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | CANDIDATE_COMPONENT_REG | 1 | 228 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SPLIT_SESSION_CANDIDATE | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | EXAMINATION_PAPER | 1 | 41 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | EXAMINATION_PAPER_PK | 1 | | 1 (0)| 00:00:01 |
| 12 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CCR"."CANDIDATE"=VALUE(KOKBF$))
6 - access("CEA"."YEAR"=2009 AND "CEA"."MONTH"='MAY')
filter(TO_NUMBER("CEA"."EXAMINER_CODE")=026753)
7 - access("EMA"."YEAR"=2009 AND "EMA"."MONTH"='MAY' AND "EMA"."EXAMINER_CODE"="CEA"."EXAMINER_CODE")
8 - filter("CCR"."SUBJECT"='ENGLISH A1' AND "CCR"."COMPONENT"='INTERNAL ASSESSMENT (ORAL)' AND
"CCR"."LVL"='HL' AND "CCR"."SUBJECT_OPTION"=NVL('ENGLISH A1',"CCR"."SUBJECT_OPTION") AND
"CCR"."LANGUAGE"=NVL('ENGLISH',"CCR"."LANGUAGE") AND "CCR"."ASSESSMENT_SCHOOL"="CEA"."ASSESSMENT_SCHOOL")
9 - access("CCR"."CANDIDATE"="CEA"."CANDIDATE" AND "CCR"."PAPER_CODE"="CEA"."PAPER_CODE" AND
"CCR"."SPLIT_SESSION_YEAR"=2009 AND "CCR"."SPLIT_SESSION_MONTH"='MAY')
11 - access("EP"."YEAR"=2009 AND "EP"."MONTH"='MAY' AND "EP"."PAPER_CODE"="CCR"."PAPER_CODE")
33 rows selected
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:43:26 CDT 2024
|