Forum Stats

  • 3,853,803 Users
  • 2,264,276 Discussions
  • 7,905,450 Comments

Discussions

Pivot - Performance Issue with large dataset

mercierg
mercierg Member Posts: 98 Blue Ribbon
edited Aug 26, 2009 11:28AM in SQL & PL/SQL
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

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Are the statistics up to date?

    The major difference I noticed in the execution plans is that in the case with a RUN_ID of 120 the estimates are almost all single rows. I am assuming this is not what you expected since you said they are exactly the same data set.

    Are there any histograms on the various RUN_ID columns?

    Thanks!
  • mercierg
    mercierg Member Posts: 98 Blue Ribbon
    Hello,

    Thanks for your response.

    Stats are computed recently with DBMS_STATS package (case 2) and we have histogramm on 'RUN_ID' columns.
    I tried to use the deprecated "analyze" method (case 1) and obtained better results!
    DECLARE
       -- Get tables used in the view vw_well_data --
       CURSOR c1
       IS
          SELECT table_name, last_analyzed
            FROM user_tables
           WHERE table_name LIKE 'WELL%';
    BEGIN
       FOR r1 IN c1
       LOOP
          -- Case 1 : Analyze method : Perf is good --
          EXECUTE IMMEDIATE    'analyze table '
                              || r1.table_name
                              || ' compute statistics ';
          
          -- Case 2 : DBMS_STATS --
          DBMS_STATS.gather_table_stats ('SCHEMA', r1.table_name);
       END LOOP;
    END;
    The explain plans are the same as before
    Any explanations, suggestions ?

    Thanks,
    Gregory
This discussion has been closed.