Hello,
Database version : Oracle 10.2.0.4 - Linux
I'm using a function to return a pivot query depending on an input "RUN_ID" value
For example, i consider two differents "RUN_ID" (e.g. 119 and 120) with exactly the same dataset
I have a performance issue when i run the result query with the "RUN_ID"=120.
Pivot:
SELECT MAX (a.plate_index), MAX (a.plate_name), MAX (a.int_well_id),
MAX (a.row_index_alpha), MAX (a.column_index), MAX (a.is_valid),
MAX (a.well_type_id), MAX (a.read_index), MAX (a.run_id),
MAX (DECODE (a.value_type || a.value_index,
'CALC190', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC304050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC306050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC30050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC3011050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC104050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC106050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC10050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC1011050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC204050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC206050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC20050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC2011050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC80050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'CALC70050301', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'RAW0', a.this_value,
NULL
)
),
MAX (DECODE (a.value_type || a.value_index,
'RAW5030', a.this_value,
NULL
)
),
MAX (a.dose), MAX (a.unit), MAX (a.int_plate_id), MAX (a.run_name)
FROM vw_well_data a
WHERE a.run_id = :app_run_id
GROUP BY a.int_well_id, a.read_index
Run the query :
SELECT Sql_FullText,(cpu_time/100000) "Cpu Time (s)",
(elapsed_time/1000000) "Elapsed time (s)",
fetches,buffer_gets,disk_reads,executions
FROM v$sqlarea
WHERE Parsing_Schema_Name ='SCHEMA';
With results :
SQL_FULLTEXT Cpu Time (s) Elapsed time (s) FETCHES BUFFER_GETS DISK_READS EXECUTIONS
query1 (RUN_ID=119) 22.15857 3.589822 1 2216 354 1
query2 (RUN_ID=120) 1885.16959 321.974332 3 7685410 368 3
Explain Plan for RUNID 119_
PLAN_TABLE_OUTPUT
Plan hash value: 3979963427
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 261 | 98397 | 434 (2)| 00:00:06 |
| 1 | HASH GROUP BY | | 261 | 98397 | 434 (2)| 00:00:06 |
| 2 | VIEW | VW_WELL_DATA | 261 | 98397 | 433 (2)| 00:00:06 |
| 3 | UNION-ALL | | | | | |
|* 4 | HASH JOIN | | 252 | 21168 | 312 (2)| 00:00:04 |
| 5 | NESTED LOOPS | | 249 | 15687 | 112 (2)| 00:00:02 |
|* 6 | HASH JOIN | | 249 | 14442 | 112 (2)| 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID | PLATE | 29 | 464 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_PLATE_RUN_ID | 29 | | 1 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 13286 | 544K| 109 (1)| 00:00:02 |
| 10 | TABLE ACCESS BY INDEX ROWID| RUN | 1 | 11 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_RUN | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| WELL | 13286 | 402K| 108 (1)| 00:00:02 |
|* 13 | INDEX RANGE SCAN | IDX_WELL_RUN_ID | 13286 | | 46 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_WELL_TYPE | 1 | 5 | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | WELL_RAW_DATA | 26361 | 540K| 199 (2)| 00:00:03 |
|* 16 | INDEX RANGE SCAN | IDX_WELL_RAW_RUN_ID | 26361 | | 92 (2)| 00:00:02 |
| 17 | NESTED LOOPS | | 9 | 891 | 121 (2)| 00:00:02 |
|* 18 | HASH JOIN | | 9 | 846 | 121 (2)| 00:00:02 |
|* 19 | HASH JOIN | | 249 | 14442 | 112 (2)| 00:00:02 |
| 20 | TABLE ACCESS BY INDEX ROWID | PLATE | 29 | 464 | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_PLATE_RUN_ID | 29 | | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 13286 | 544K| 109 (1)| 00:00:02 |
| 23 | TABLE ACCESS BY INDEX ROWID| RUN | 1 | 11 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_RUN | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| WELL | 13286 | 402K| 108 (1)| 00:00:02 |
|* 26 | INDEX RANGE SCAN | IDX_WELL_RUN_ID | 13286 | | 46 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | WELL_CALC_DATA | 490 | 17640 | 9 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_WELL_CALC_RUN_ID | 490 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_WELL_TYPE | 1 | 5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("WELL_RAW_DATA"."RUN_ID"="WELL"."RUN_ID" AND
"WELL"."INT_WELL_ID"="WELL_RAW_DATA"."INT_WELL_ID")
6 - access("PLATE"."RUN_ID"="WELL"."RUN_ID" AND "PLATE"."INT_PLATE_ID"="WELL"."INT_PLATE_ID")
8 - access("PLATE"."RUN_ID"=119)
11 - access("RUN"."RUN_ID"=119)
13 - access("WELL"."RUN_ID"=119)
14 - access("WELL"."WELL_TYPE_ID"="TSF_LAYOUT_WELL_TYPE"."WELL_TYPE_ID")
16 - access("WELL_RAW_DATA"."RUN_ID"=119)
18 - access("WELL"."RUN_ID"="WELL_CALC_DATA"."RUN_ID" AND
"WELL"."INT_WELL_ID"="WELL_CALC_DATA"."INT_WELL_ID")
19 - access("PLATE"."RUN_ID"="WELL"."RUN_ID" AND "PLATE"."INT_PLATE_ID"="WELL"."INT_PLATE_ID")
21 - access("PLATE"."RUN_ID"=119)
24 - access("RUN"."RUN_ID"=119)
26 - access("WELL"."RUN_ID"=119)
28 - access("WELL_CALC_DATA"."RUN_ID"=119)
29 - access("WELL"."WELL_TYPE_ID"="TSF_LAYOUT_WELL_TYPE"."WELL_TYPE_ID")
Explain Plan for RUNID 120_
PLAN_TABLE_OUTPUT
Plan hash value: 599334230
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 754 | 24 (5)| 00:00:01 |
| 1 | HASH GROUP BY | | 2 | 754 | 24 (5)| 00:00:01 |
| 2 | VIEW | VW_WELL_DATA | 2 | 754 | 23 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | WELL_RAW_DATA | 1 | 21 | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 84 | 9 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 63 | 6 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 58 | 6 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| RUN | 1 | 11 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_RUN | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| PLATE | 1 | 16 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_PLATE_RUN_ID | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WELL | 1 | 31 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_WELL_RUN_ID | 59 | | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_WELL_TYPE | 1 | 5 | 0 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_WELL_RAW_DATA_WELL_ID | 2 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | WELL_CALC_DATA | 1 | 36 | 8 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 99 | 14 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 63 | 6 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 58 | 6 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 27 | 3 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID| RUN | 1 | 11 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_RUN | 1 | | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| PLATE | 1 | 16 | 2 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX_PLATE_RUN_ID | 1 | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | WELL | 1 | 31 | 3 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | IDX_WELL_RUN_ID | 59 | | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_WELL_TYPE | 1 | 5 | 0 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_WELL_CALC_RUN_ID | 486 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("WELL_RAW_DATA"."RUN_ID"=120)
10 - access("RUN"."RUN_ID"=120)
12 - access("PLATE"."RUN_ID"=120)
13 - filter("PLATE"."INT_PLATE_ID"="WELL"."INT_PLATE_ID")
14 - access("WELL"."RUN_ID"=120)
15 - access("WELL"."WELL_TYPE_ID"="TSF_LAYOUT_WELL_TYPE"."WELL_TYPE_ID")
16 - access("WELL"."INT_WELL_ID"="WELL_RAW_DATA"."INT_WELL_ID")
17 - filter("WELL"."INT_WELL_ID"="WELL_CALC_DATA"."INT_WELL_ID")
23 - access("RUN"."RUN_ID"=120)
25 - access("PLATE"."RUN_ID"=120)
26 - filter("PLATE"."INT_PLATE_ID"="WELL"."INT_PLATE_ID")
27 - access("WELL"."RUN_ID"=120)
28 - access("WELL"."WELL_TYPE_ID"="TSF_LAYOUT_WELL_TYPE"."WELL_TYPE_ID")
29 - access("WELL_CALC_DATA"."RUN_ID"=120)
I need some advice to understand the issue and to improve the performance.
Thanks,
Grégory