I have a query that is taking to long where it should takes less than 5 sec. How could I solve this problem? I think the u_final_result_user is the problem but i am not sure and don't know how I could fix this.
Explain
SQL Statement which produced this data:
select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 584 | 3522K| | 4484 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
|* 3 | FILTER | | | | | |
| 4 | COUNT | | | | | |
|* 5 | HASH JOIN | | 584 | 3522K| | 4484 |
| 6 | VIEW | | 74 | 80660 | | 428 |
| 7 | WINDOW SORT | | 74 | 740 | | 428 |
| 8 | SORT GROUP BY | | 74 | 740 | | 428 |
|* 9 | TABLE ACCESS FULL | U_FINALRESULT_USER | 74 | 740 | | 425 |
|* 10 | HASH JOIN OUTER | | 789 | 3918K| 3120K| 4038 |
| 11 | VIEW | | 789 | 3106K| | 3530 |
| 12 | FILTER | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TEST | 772K| 10M| | 3 |
| 14 | NESTED LOOPS | | 789 | 33927 | | 3530 |
| 15 | NESTED LOOPS | | 789 | 22881 | | 1163 |
| 16 | NESTED LOOPS | | 383 | 6894 | | 14 |
| 17 | NESTED LOOPS | | 1 | 10 | | 3 |
| 18 | TABLE ACCESS BY INDEX ROWID| SDG | 1 | 4 | | 2 |
|* 19 | INDEX UNIQUE SCAN | PK_SDG | 865 | | | 1 |
| 20 | TABLE ACCESS BY INDEX ROWID| SDG_USER | 1 | 6 | | 1 |
|* 21 | INDEX UNIQUE SCAN | PK_SDG_USER | 1 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | SAMPLE | 1 | 8 | | 11 |
|* 23 | INDEX RANGE SCAN | FK_SAMPLE_SDG | 383 | | | 2 |
|* 24 | TABLE ACCESS BY INDEX ROWID | ALIQUOT | 1 | 11 | | 3 |
|* 25 | INDEX RANGE SCAN | FK_ALIQUOT_SAMPLE | 2 | | | 2 |
|* 26 | INDEX RANGE SCAN | FK_TEST_ALIQUOT | 1 | | | 2 |
| 27 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 28 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 29 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 30 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 31 | VIEW | | 37 | 38998 | | 428 |
| 32 | SORT UNIQUE | | 37 | 555 | | 428 |
| 33 | WINDOW SORT | | 37 | 555 | | 428 |
|* 34 | TABLE ACCESS FULL | U_FINALRESULT_USER | 37 | 555 | | 425 |
| 35 | HASH JOIN | | | | | |
| 36 | CONNECT BY PUMP | | | | | |
| 37 | COUNT | | | | | |
|* 38 | HASH JOIN | | 584 | 3522K| | 4484 |
| 39 | VIEW | | 74 | 80660 | | 428 |
| 40 | WINDOW SORT | | 74 | 740 | | 428 |
| 41 | SORT GROUP BY | | 74 | 740 | | 428 |
|* 42 | TABLE ACCESS FULL | U_FINALRESULT_USER | 74 | 740 | | 425 |
|* 43 | HASH JOIN OUTER | | 789 | 3918K| 3120K| 4038 |
| 44 | VIEW | | 789 | 3106K| | 3530 |
| 45 | FILTER | | | | | |
| 46 | TABLE ACCESS BY INDEX ROWID | TEST | 772K| 10M| | 3 |
| 47 | NESTED LOOPS | | 789 | 33927 | | 3530 |
| 48 | NESTED LOOPS | | 789 | 22881 | | 1163 |
| 49 | NESTED LOOPS | | 383 | 6894 | | 14 |
| 50 | NESTED LOOPS | | 1 | 10 | | 3 |
| 51 | TABLE ACCESS BY INDEX ROWID| SDG | 1 | 4 | | 2 |
|* 52 | INDEX UNIQUE SCAN | PK_SDG | 865 | | | 1 |
| 53 | TABLE ACCESS BY INDEX ROWID| SDG_USER | 1 | 6 | | 1 |
|* 54 | INDEX UNIQUE SCAN | PK_SDG_USER | 1 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID | SAMPLE | 1 | 8 | | 11 |
|* 56 | INDEX RANGE SCAN | FK_SAMPLE_SDG | 383 | | | 2 |
|* 57 | TABLE ACCESS BY INDEX ROWID | ALIQUOT | 1 | 11 | | 3 |
|* 58 | INDEX RANGE SCAN | FK_ALIQUOT_SAMPLE | 2 | | | 2 |
|* 59 | INDEX RANGE SCAN | FK_TEST_ALIQUOT | 1 | | | 2 |
| 60 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 61 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 62 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 63 | INDEX UNIQUE SCAN | PK_OPERATOR_GROUP | 1 | 4 | | |
| 64 | VIEW | | 37 | 38998 | | 428 |
| 65 | SORT UNIQUE | | 37 | 555 | | 428 |
| 66 | WINDOW SORT | | 37 | 555 | | 428 |
|* 67 | TABLE ACCESS FULL | U_FINALRESULT_USER | 37 | 555 | | 425 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FR_PIVOT"."MAXLEVEL"=LEVEL)
2 - filter("FR_PIVOT"."RANK"=1)
3 - filter("FR_PIVOT"."RANK"=1)
5 - access("FR_PIVOT"."REF"=TO_CHAR("FR"."U_SDG_ID")||TO_CHAR("FR"."U_TEST_TEMPLATE_ID"))
9 - filter(NVL("U_FINALRESULT_USER"."U_OVERRULED_RESULT","U_FINALRESULT_USER"."U_CALCULATED_RESULT
")<>'X' AND "U_FINALRESULT_USER"."U_SDG_ID"=TO_NUMBER(:Z))
10 - access("SD"."SDG_ID"="FR"."U_SDG_ID"(+) AND "SD"."TEST_TEMPLATE_ID"="FR"."U_TEST_TEMPLATE_ID"(
+))
19 - access("SYS_ALIAS_4"."SDG_ID"=TO_NUMBER(:Z))
21 - access("SYS_ALIAS_4"."SDG_ID"="SDG_USER"."SDG_ID")
23 - access("SYS_ALIAS_4"."SDG_ID"="SYS_ALIAS_3"."SDG_ID")
24 - filter("SYS_ALIAS_2"."STATUS"='C' OR "SYS_ALIAS_2"."STATUS"='P' OR "SYS_ALIAS_2"."STATUS"='V')
25 - access("SYS_ALIAS_2"."SAMPLE_ID"="SYS_ALIAS_3"."SAMPLE_ID")
26 - access("SYS_ALIAS_1"."ALIQUOT_ID"="SYS_ALIAS_2"."ALIQUOT_ID")
34 - filter("U_FINALRESULT_USER"."U_REQUESTED"='T' AND NVL("U_FINALRESULT_USER"."U_OVERRULED_RESULT
","U_FINALRESULT_USER"."U_CALCULATED_RESULT")<>'X' AND "U_FINALRESULT_USER"."U_SDG_ID"=
TO_NUMBER(:Z))
38 - access("FR_PIVOT"."REF"=TO_CHAR("FR"."U_SDG_ID")||TO_CHAR("FR"."U_TEST_TEMPLATE_ID"))
42 - filter(NVL("U_FINALRESULT_USER"."U_OVERRULED_RESULT","U_FINALRESULT_USER"."U_CALCULATED_RESULT
")<>'X' AND "U_FINALRESULT_USER"."U_SDG_ID"=TO_NUMBER(:Z))
43 - access("SD"."SDG_ID"="FR"."U_SDG_ID"(+) AND "SD"."TEST_TEMPLATE_ID"="FR"."U_TEST_TEMPLATE_ID"(
+))
52 - access("SYS_ALIAS_4"."SDG_ID"=TO_NUMBER(:Z))
54 - access("SYS_ALIAS_4"."SDG_ID"="SDG_USER"."SDG_ID")
56 - access("SYS_ALIAS_4"."SDG_ID"="SYS_ALIAS_3"."SDG_ID")
57 - filter("SYS_ALIAS_2"."STATUS"='C' OR "SYS_ALIAS_2"."STATUS"='P' OR "SYS_ALIAS_2"."STATUS"='V')
58 - access("SYS_ALIAS_2"."SAMPLE_ID"="SYS_ALIAS_3"."SAMPLE_ID")
59 - access("SYS_ALIAS_1"."ALIQUOT_ID"="SYS_ALIAS_2"."ALIQUOT_ID")
67 - filter("U_FINALRESULT_USER"."U_REQUESTED"='T' AND NVL("U_FINALRESULT_USER"."U_OVERRULED_RESULT
","U_FINALRESULT_USER"."U_CALCULATED_RESULT")<>'X' AND "U_FINALRESULT_USER"."U_SDG_ID"=
TO_NUMBER(:Z))
Note: cpu costing is off
Tkprof
TKPROF: Release 9.2.0.1.0 - Production on Fri Jul 13 15:03:47 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:\oracle\admin\nautdev\udump\nautdev_ora_13020.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
********************************************************************************
alter session set sql_trace true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
********************************************************************************
select VALUE
from
nls_session_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS'
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 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 FIXED TABLE FULL X$NLS_PARAMETERS
********************************************************************************
select VALUE
from
nls_session_parameters where PARAMETER='NLS_DATE_FORMAT'
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 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 FIXED TABLE FULL X$NLS_PARAMETERS
********************************************************************************
select VALUE
from
nls_session_parameters where PARAMETER='NLS_CURRENCY'
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 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 FIXED TABLE FULL X$NLS_PARAMETERS
********************************************************************************
select to_char(9,'9C')
from
dual
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 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
SELECT sd.u_bas_stockseed_code,
sd.u_bas_storage_code,
sd.description as test,
case when fr.resultcount < 8
then null
else case when fr.resultdistinct > 1
then 'spl'
else fr.resultfinal
end
end as result,
case when level >=2
then substr(sys_connect_by_path(valcount,','),2)
end as spl
FROM
(
SELECT sd.sdg_id,
sa.sample_id,
t.test_template_id,
sdu.u_bas_stockseed_code,
sdu.u_bas_storage_code,
t.description
FROM lims_sys.sdg sd, lims_sys.sdg_user sdu, lims_sys.sample sa, lims_sys.aliquot a,lims_sys.test t
WHERE sd.sdg_id = sdu.sdg_id
AND sd.sdg_id = sa.sdg_id
AND a.sample_id = sa.sample_id
AND t.aliquot_id = a.aliquot_id
AND a.status IN ('V','P','C')
AND sd.sdg_id IN (:SDGID)
) sd,
(
SELECT distinct fr.u_sdg_id,
fr.u_sample_id,
fr.u_test_template_id,
nvl(fr.u_overruled_result, fr.u_calculated_result) as Resultfinal,
count(distinct nvl(fr.u_overruled_result, fr.u_calculated_result)) over (partition by concat(fr.u_sdg_id, fr.u_test_template_id)) as resultdistinct,
count(nvl(fr.u_overruled_result, fr.u_calculated_result)) over (partition by concat(fr.u_sdg_id, fr.u_test_template_id)) as resultcount
FROM lims_sys.u_finalresult_user fr
WHERE fr.u_requested = 'T'
AND nvl(fr.u_overruled_result,fr.u_calculated_result) != 'X'
AND fr.u_sdg_id IN (:SDGID)
) fr,
(
SELECT concat(fr.u_sdg_id, fr.u_test_template_id) as ref,
nvl( fr.u_overruled_result, fr.u_calculated_result),
to_char(count(*)) || 'x' || nvl(fr.u_overruled_result, fr.u_calculated_result) as valcount,
row_number() over (partition by concat(fr.u_sdg_id, fr.u_test_template_id) order by count(*) desc, nvl(fr.u_overruled_result, fr.u_calculated_result)) as rank,
count(*) over (partition by concat(fr.u_sdg_id, fr.u_test_template_id)) AS MaxLevel
FROM lims_sys.u_finalresult_user fr
WHERE nvl(fr.u_overruled_result,fr.u_calculated_result) != 'X'
AND fr.u_sdg_id IN (:SDGID)
GROUP BY concat(fr.u_sdg_id, fr.u_test_template_id), nvl(fr.u_overruled_result,fr.u_calculated_result)
) fr_pivot
WHERE sd.sdg_id = fr.u_sdg_id (+)
AND sd.test_template_id = fr.u_test_template_id (+)
AND fr_pivot.ref = concat(fr.u_sdg_id,fr.u_test_template_id)
AND level = maxlevel
start with rank = 1 connect by
prior fr.u_sdg_id = fr.u_sdg_id
and prior fr.u_test_template_id = fr.u_test_template_id
and prior rank = rank - 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.58 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 8344.424154501457.66 15955 140391 178371 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8344.454154501458.25 15955 140391 178371 500
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
500 FILTER
507 CONNECT BY WITH FILTERING
24731 FILTER
169667 COUNT
169667 HASH JOIN
34 VIEW
34 WINDOW SORT
34 SORT GROUP BY
312 TABLE ACCESS FULL U_FINALRESULT_USER
24731 HASH JOIN OUTER
546 VIEW
546 FILTER
546 TABLE ACCESS BY INDEX ROWID TEST
1093 NESTED LOOPS
546 NESTED LOOPS
123 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID SDG
1 INDEX UNIQUE SCAN PK_SDG (object id 54343)
1 TABLE ACCESS BY INDEX ROWID SDG_USER
1 INDEX UNIQUE SCAN PK_SDG_USER (object id 54368)
123 TABLE ACCESS BY INDEX ROWID SAMPLE
123 INDEX RANGE SCAN FK_SAMPLE_SDG (object id 54262)
546 TABLE ACCESS BY INDEX ROWID ALIQUOT
546 INDEX RANGE SCAN FK_ALIQUOT_SAMPLE (object id 53620)
546 INDEX RANGE SCAN FK_TEST_ALIQUOT (object id 54493)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
291 VIEW
291 SORT UNIQUE
291 WINDOW SORT
291 TABLE ACCESS FULL U_FINALRESULT_USER
1312330604 HASH JOIN
169667 CONNECT BY PUMP
2036004 COUNT
2036004 HASH JOIN
408 VIEW
408 WINDOW SORT
408 SORT GROUP BY
3744 TABLE ACCESS FULL U_FINALRESULT_USER
296772 HASH JOIN OUTER
6552 VIEW
6552 FILTER
6552 TABLE ACCESS BY INDEX ROWID TEST
13116 NESTED LOOPS
6552 NESTED LOOPS
1476 NESTED LOOPS
12 NESTED LOOPS
12 TABLE ACCESS BY INDEX ROWID SDG
12 INDEX UNIQUE SCAN PK_SDG (object id 54343)
12 TABLE ACCESS BY INDEX ROWID SDG_USER
12 INDEX UNIQUE SCAN PK_SDG_USER (object id 54368)
1476 TABLE ACCESS BY INDEX ROWID SAMPLE
1476 INDEX RANGE SCAN FK_SAMPLE_SDG (object id 54262)
6552 TABLE ACCESS BY INDEX ROWID ALIQUOT
6552 INDEX RANGE SCAN FK_ALIQUOT_SAMPLE (object id 53620)
6552 INDEX RANGE SCAN FK_TEST_ALIQUOT (object id 54493)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
0 INDEX UNIQUE SCAN PK_OPERATOR_GROUP (object id 54041)
3492 VIEW
3492 SORT UNIQUE
3492 WINDOW SORT
3492 TABLE ACCESS FULL U_FINALRESULT_USER
********************************************************************************
select 'x'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 6 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 44
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.03 0.59 0 0 0 0
Execute 11 0.00 0.00 0 0 0 2
Fetch 7 8344.424154501457.66 15955 140400 178371 506
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 8344.454154501458.25 15955 140400 178371 508
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 40 0.00 0.00 0 0 0 0
Execute 40 0.00 0.00 0 0 0 0
Fetch 40 0.00 0.00 0 81 0 40
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 120 0.00 0.00 0 81 0 40
Misses in library cache during parse: 0
10 user SQL statements in session.
40 internal SQL statements in session.
50 SQL statements in session.
********************************************************************************
Trace file: d:\oracle\admin\nautdev\udump\nautdev_ora_13020.trc
Trace file compatibility: 9.00.01
Sort options: default
1 session in tracefile.
10 user SQL statements in trace file.
40 internal SQL statements in trace file.
50 SQL statements in trace file.
10 unique SQL statements in trace file.
544 lines in trace file.