OsmcQueryA

From NGDCWiki

Jump to: navigation, search

Contents

Query A

Summary

Query Purpose

  • This query returns the location of each drifting buoy when it last reported an observation of any parameter on September 19, 2007, if it reported SST at the time of that last observation, and if the location occurred within the area of the bounding box specified.
  • The query also returns the individual platform code, its platform type (or subtype), the country associated with the platform, and the time and location of the last observation reported for the time range specified.
  • The count returned in this case (which is obtained from the daily summary table) is the count of the number of times the platform reported an observation of any parameter on the day of its last reported observation.

Query A: returned values (select)

  • id (ds.platform_code)
  • NumberofObs (ds.count_observations)
  • type (ds.platform_type)
  • country (ds.country)
  • latitude (ds.last_report_latitude)
  • longitude (ds.last_report_longitude)
  • last_report (ds.last_report_date)

Query A: predicates (where)

  • Date range (9/18/07 00:00:00 - 23:59:59)
  • Platform type (DRIFTING BUOYS)
  • Bounding box (-60,224 -45,249)
  • Parameter (SST)

Comparison of return times

A caveat: Below are listed several different timing values for each of the two queries. It is difficult to give absolute measurements of timing for a particular query or for comparing return times, due to data block caching.

Attempting to "flush" the cache by issuing a "select * from osmc.daily_summary_mv" (returning ~3.5 million rows), before running the 'original' and 'optimized' Query A, results in perhaps more realistic return times.

  • Original Query A:
    • 00:05:24.41 (11/06)
    • 00:05:08.02 (11/07)
    • 00:13:19.73 (11/08 after flushing cache)
    • 00:06:15.82 (11/08)
  • Optimized Query A:
    • 00:00:00.99 (11/06)
    • 00:00:03.76 (11/07 after flushing cache)
    • 00:00:01.09 (11/07)
    • 00:00:01.68 (11/08 after flushing cache)

Autotrace statistics

If 'consistent gets' or 'physical reads' are high relative to the amount of data returned (i.e. 'rows processed'), then this is a sign that the query is expensive and needs to be reviewed for optimization.

Query A (original) after flushing cache

Statistics 
----------------------------------------------------------
      1116  recursive calls
         0  db block gets
   1095676  consistent gets
    814253  physical reads
   2435484  redo size
      1277  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
        76  sorts (memory)
         0  sorts (disk)
        11  rows processed

Query A (optimized) after flushing cache

Statistics for 
----------------------------------------------------------
        56  recursive calls
         0  db block gets
       507  consistent gets
        73  physical reads
      3764  redo size
      1277  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
        46  sorts (memory)
         0  sorts (disk)
        11  rows processed


Query A (original) after previous query runs

Elapsed: 00:06:15.82
Statistics
----------------------------------------------------------
        56  recursive calls
         0  db block gets
    816671  consistent gets
    763164  physical reads
       400  redo size
      1277  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
        46  sorts (memory)
         0  sorts (disk)
        11  rows processed
Elapsed: 00:13:31.86
Statistics
----------------------------------------------------------
        16  recursive calls
         0  db block gets
    981409  consistent gets
    761557  physical reads
   1416428  redo size
      1277  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
        46  sorts (memory)
         0  sorts (disk)
        11  rows processed

Query A (optimized) after previous query runs

Elapsed: 00:00:01.78
Statistics
----------------------------------------------------------
        16  recursive calls
         0  db block gets
       444  consistent gets
        71  physical reads
         0  redo size
      1277  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
        46  sorts (memory)
         0  sorts (disk)
        11  rows processed
Statistics 
----------------------------------------------------------
         3  recursive calls
         0  db block gets
       237  consistent gets
         0  physical reads
         0  redo size
      1267  bytes sent via SQL*Net to client
       512  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         7  sorts (memory)
         0  sorts (disk)
        11  rows processed

Query exploration

Optimization discussion by John LaRocque

We were able to optimize the query below and in so doing have reduced the amount of time the query ran within from 5.5 minutes down to under one second. The paragraphs below describe how we were able to accomplish this.

Two significant pieces of information can be gleaned from the explain plans shown below. First, look at the column titled Rows. This column is an estimate of the number of rows that the query will return for a given processing step. Second, look at the columns labeled PStart and PStop. These columns indicate the starting and ending partitions for the given processing step.

The Rows column in the first explain plan is single and double digits until the last few lines in that column, then it jumps to 156M (156,000,000). This is a big number. If you trace across the explain plan to the columns titled PStart and PStop you will notice that the numbers listed for those same processing steps are 1 and 16. There are 16 partitions in these two tables that we are accessing, so the Pstart and PStop numbers indicate that the query will read all partitions in the database.

With this information a user may ask themselves 'Well I've specified the ds.observation_day (which is the column on which the table daily_summary_mv is partitioned) why am I looking through all of the other partitions when I don't have to?'

In this case the issue is a result of the join between the tables daily_summary_mv and observation_value. You will notice in the first query we never refer to the partition key of the second table observation_value which happens to be observation_date even though these two tables are both partitioned in exactly the same way (they are both partitioned quarterly). By joining these two tables together on observation_day and observation_date we are telling Oracle that these are the same thing. When Oracle knows they are the same thing it can then make inferences regarding other optimizations that can be made. One of those inferences is partition-wise joining. What this means is that partition 1 in daily_summary_mv would be joined to partition 1 in observation_value. Because the first query only specified a date filter for daily_summary_mv a full scan was performed on observation_value. Consequently this is how we arrived with the explain plan for the first query.

If you look at the second query listed you'll notice the addition of the two components of logic to the query. Those two components are:

 24 ov.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 25 ov.observation_date <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
 26 ds.last_report_obs_id = ov.observation_id(+)  AND

These lines cover the missing components that were described above. Now if you look at the explain plan for the second query you'll notice that the column Rows have been reduced from 156 Million in one step to 5247. Also you'll notice that the PStart and PStop keys have been reduced from 1 - 16 to 15 - 15. These changes decrease the query execution time from over 5 minutes to under 1 second, a very significant performance improvement.

There is never a magic bullet or one fix for everything. Each situation needs to be evaluated and analyzed accordingly. Many cases are much more difficult, requiring the performance tuning analyst to evaluate physical data in the table in addition to substantially modifying the query to get it in to an optimal performance window.

Important highly applicable take-home message for the predicate clause in optimization (discussed above)

  • When joining to the observation_value (and/or observation table), specify both observation date and observation_id so that the partition key is used. e.g., when joining the OBSERVATION (O) table to the OBSERVATION_VALUE (OV) table, the following predicates serve this purpose:
    • O.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
    • O.observation_day < to_date('2007-09-19 00:00:00','yyyy-mm-dd hh24:mi:ss'))
    • OV.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
    • OV.observation_date < to_date('2007-09-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
    • O.observation_date = OV.observation_date
    • O.id = OV.observation_id

Caveats to this query

  • It seems like what is really expected from this query is the time and location that any drifting buoy last reported an SST observation, and the number of times the platform reported SST observations, in the time range and area specified
    • The query uses the daily_summary_mv rather than joining the base database tables, thus providing a count which probably is not what the user expects
  • It looks like there was an attempt to generalize the query by adding outer joins to a number of tables, and by adding the predicate for operational_status_id, both of which are not needed to return this particular result set.
    • If this was intended to also include platforms such as tide gauges that never report observations, it also needs to include the 'placeholder' date of '1970-01-01'
  • The observation_date field was converted from date type to character type
  • The query does not take advantage of Oracle spatial and a spatial query
  • The query uses the 'platform_types_related' function rather than the platform_type_view query
  • The 'distinct' clause may have been an attempt to override QC issues that are currently present (and being worked on) in the database, e.g. in the result set there are two 'last reported' observations for platform_code 55929 at different locations. As an aside, these QC issues also result in 'inflated' counts of observations.


ID                   NUMBEROFOBS TYPE              CO   LATITUDE  LONGITUDE LAST_REPORT         URL
-------------------- ----------- ----------------- -- ---------- ---------- ------------------- ---
55929                         45 DRIFTING BUOYS    US    -57.431    234.564 2007-09-18 23:10:00
55929                         45 DRIFTING BUOYS    US     -57.43     234.56 2007-09-18 23:10:00

Original Version of Query A

SQL for Query A (Original)

 SELECT
        DISTINCT ds.platform_code id,
        ds.count_observations NumberOfObs,
        ds.platform_type type,
        ds.country country,
        ds.last_report_latitude latitude,
        ds.last_report_longitude_390 longitude,
        to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
       plat.url URL
   FROM
       osmc.daily_summary_mv ds,
       osmc.platform plat,
       osmc.observation_value ov,
       osmc.parameter p
   WHERE
       (ds.operational_status_id = 2 OR
       ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
       ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
       (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
       (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
       ds.platform_code = plat.platform_code(+) AND
       ds.last_report_obs_id = ov.observation_id(+)  AND
       ov.parameter_id = p.id(+)
       AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
       AND p.name = 'SST'
   order by 1,7;

Explain plan for Query A (original)

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL> explain plan for
  2  SELECT
  3      DISTINCT ds.platform_code id,
  4      ds.count_observations NumberOfObs,
  5      ds.platform_type type,
  6      ds.country country,
  7      ds.last_report_latitude latitude,
  8      ds.last_report_longitude_390 longitude,
  9      to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
 10      plat.url URL
 11  FROM
 12      osmc.daily_summary_mv ds,
 13      osmc.platform plat,
 14      osmc.observation_value ov,
 15      osmc.parameter p
 16  WHERE
 17      (ds.operational_status_id = 2 OR
 18      ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 19      ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 20      (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 21      (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 22      ds.platform_code = plat.platform_code(+) AND
 23      ds.last_report_obs_id = ov.observation_id(+)  AND
 24      ov.parameter_id = p.id(+)
 25      AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 26      AND p.name = 'SST'
 27  order by 1,7;

Explained.

SQL> set linesize 5000;
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1645310607

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |     5 |   505 |   153K  (7)| 00:30:41 |       |       |
|   1 |  SORT UNIQUE                    |                          |     5 |   505 |   153K  (7)| 00:30:41 |       |       |
|   2 |   NESTED LOOPS                  |                          |     5 |   505 |   153K  (7)| 00:30:41 |       |       |
|*  3 |    HASH JOIN                    |                          |    52 |  4784 |   153K  (7)| 00:30:41 |       |       |
|   4 |     NESTED LOOPS OUTER          |                          |     4 |   332 |  8800   (5)| 00:01:46 |       |       |
|   5 |      PARTITION RANGE ALL        |                          |     4 |   256 |  8796   (5)| 00:01:46 |     1 |    16 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
|*  6 |       MAT_VIEW ACCESS FULL      | DAILY_SUMMARY_MV         |     4 |   256 |  8796   (5)| 00:01:46 |     1 |    16 |
|   7 |      TABLE ACCESS BY INDEX ROWID| PLATFORM                 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|*  8 |       INDEX UNIQUE SCAN         | PLATFORM_PLAFORM_CODE_UK |     1 |       |     0   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE ALL         |                          |   156M|  1343M|   141K  (6)| 00:28:16 |     1 |    16 |
|  10 |      TABLE ACCESS FULL          | OBSERVATION_VALUE        |   156M|  1343M|   141K  (6)| 00:28:16 |     1 |    16 |
|* 11 |    TABLE ACCESS BY INDEX ROWID  | PARAMETER                |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|* 12 |     INDEX UNIQUE SCAN           | NS_PARAMETER_PK          |     1 |       |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------

   3 - access("DS"."LAST_REPORT_OBS_ID"="OV"."OBSERVATION_ID")
   6 - filter("DS"."LAST_REPORT_LATITUDE"<=(-44.61119292862682) AND
              "DS"."LAST_REPORT_LONGITUDE_390">=223.71840468842848 AND ("DS"."OBSERVATION_DAY">=TO_DATE('2007-09-18 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "DS"."OBSERVATION_DAY"<=TO_DATE('2007-09-18 23:59:59', 'yyyy-mm-dd hh24:mi:ss') OR
              "DS"."OPERATIONAL_STATUS_ID"=2) AND "DS"."LAST_REPORT_LONGITUDE_390"<=248.9643297687431 AND
              ("DS"."PLATFORM_TYPE"='DRIFTING BUOYS' OR "OSMC"."PLATFORM_TYPES_RELATED"('DRIFTING BUOYS',"PLATFORM_TYPE")='Y')
              AND "DS"."LAST_REPORT_LATITUDE">=(-60.009277233033764))
   8 - access("DS"."PLATFORM_CODE"="PLAT"."PLATFORM_CODE"(+))
  11 - filter("P"."NAME"='SST')
  12 - access("OV"."PARAMETER_ID"="P"."ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

37 rows selected.

Results and timing for Query A (original)

SQL> set timing on;
SQL> SELECT
  2      DISTINCT ds.platform_code id,
  3      ds.count_observations NumberOfObs,
  4      ds.platform_type type,
  5      ds.country country,
  6      ds.last_report_latitude latitude,
  7      ds.last_report_longitude_390 longitude,
  8      to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
  9      plat.url URL
 10  FROM
 11      osmc.daily_summary_mv ds,
 12      osmc.platform plat,
 13      osmc.observation_value ov,
 14      osmc.parameter p
 15  WHERE
 16      (ds.operational_status_id = 2 OR
 17      ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 18      ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 19      (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 20      (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 21      ds.platform_code = plat.platform_code(+) AND
 22      ds.last_report_obs_id = ov.observation_id(+)  AND
 23      ov.parameter_id = p.id(+)
 24      AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 25      AND p.name = 'SST'
 26  order by 1,7;

ID                   NUMBEROFOBS TYPE              CO   LATITUDE  LONGITUDE LAST_REPORT         URL
-------------------- ----------- ----------------- -- ---------- ---------- ------------------- ---
16525                         44 DRIFTING BUOYS    US     -53.16  248.32001 2007-09-18 23:40:00
16562                         43 DRIFTING BUOYS    US     -53.33     227.58 2007-09-18 23:00:00
55609                         43 DRIFTING BUOYS    US     -51.45 234.240005 2007-09-18 23:30:00
55612                         46 DRIFTING BUOYS    US      -54.1     236.94 2007-09-18 23:00:00
55612                         46 DRIFTING BUOYS    US    -54.098    236.942 2007-09-18 23:00:00
55910                         57 DRIFTING BUOYS    US     -47.35     235.05 2007-09-18 23:51:00
55927                         45 DRIFTING BUOYS    US     -55.88 240.380005 2007-09-18 23:00:00
55927                         45 DRIFTING BUOYS    US    -55.875    240.378 2007-09-18 23:00:00
55929                         45 DRIFTING BUOYS    US    -57.431    234.564 2007-09-18 23:10:00
55929                         45 DRIFTING BUOYS    US     -57.43     234.56 2007-09-18 23:10:00
56513                         44 DRIFTING BUOYS    AU     -46.18     230.28 2007-09-18 23:40:00

11 rows selected.

Elapsed: 00:05:24.41

Autotrace Results for Query A (original)

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 8 08:08:06 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set timing on;
SQL> set autotrace trace;
SQL> SELECT
  2         DISTINCT ds.platform_code id,
  3         ds.count_observations NumberOfObs,
  4         ds.platform_type type,
  5         ds.country country,
  6         ds.last_report_latitude latitude,
  7         ds.last_report_longitude_390 longitude,
  8         to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
  9         plat.url URL
 10     FROM
 11         osmc.daily_summary_mv ds,
 12        osmc.platform plat,
 13         osmc.observation_value ov,
 14         osmc.parameter p
 15     WHERE
 16         (ds.operational_status_id = 2 OR
 17         ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 18         ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 19         (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 20         (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 21         ds.platform_code = plat.platform_code(+) AND
 22        ds.last_report_obs_id = ov.observation_id(+)  AND
 23         ov.parameter_id = p.id(+)
 24         AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 25         AND p.name = 'SST'
 26     order by 1,7;

11 rows selected.

Elapsed: 00:13:19.73

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=153388 Card=5 Bytes=505)
   1    0   SORT (UNIQUE) (Cost=153387 Card=5 Bytes=505)
   2    1     NESTED LOOPS (Cost=153386 Card=5 Bytes=505)
   3    2       HASH JOIN (Cost=153384 Card=52 Bytes=4784)
   4    3         NESTED LOOPS (OUTER) (Cost=8800 Card=4 Bytes=332)
   5    4           PARTITION RANGE (ALL) (Cost=8796 Card=4 Bytes=256)
   6    5             MAT_VIEW ACCESS (FULL) OF 'DAILY_SUMMARY_MV' (MAT_VIEW) (Cost=8796 Card=4 Bytes=256)
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM' (TABLE) (Cost=1 Card=1 Bytes=19)
   8    7             INDEX (UNIQUE SCAN) OF 'PLATFORM_PLAFORM_CODE_UK' (INDEX (UNIQUE)) (Cost=0 Card=1)
   9    3         PARTITION RANGE (ALL) (Cost=141329 Card=156544685 Bytes=1408902165)
  10    9           TABLE ACCESS (FULL) OF 'OBSERVATION_VALUE' (TABLE) (Cost=141329 Card=156544685 Bytes=1408902165)
  11    2       TABLE ACCESS (BY INDEX ROWID) OF 'PARAMETER' (TABLE) (Cost=1 Card=1 Bytes=9)
  12   11         INDEX (UNIQUE SCAN) OF 'NS_PARAMETER_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

Statistics
----------------------------------------------------------
       1116  recursive calls
          0  db block gets
    1095676  consistent gets
     814253  physical reads
    2435484  redo size
       1277  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         76  sorts (memory)
          0  sorts (disk)
         11  rows processed

Optimized Version of Query A

SQL for Query A (optimized)

SQL> SELECT
                 DISTINCT ds.platform_code id,
                 ds.count_observations NumberOfObs,
                 ds.platform_type type,
                 ds.country country,
                 ds.last_report_latitude latitude,
                 ds.last_report_longitude_390 longitude,
                 to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
                 plat.url URL
             FROM
                 osmc.daily_summary_mv ds,
                 osmc.platform plat,
                 osmc.observation_value ov,
                 osmc.parameter p
             WHERE
                 (ds.operational_status_id = 2 OR
                 ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
                 ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
                 ds.observation_day = ov.observation_date AND
                 (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
                 (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
                 ds.platform_code = plat.platform_code(+) AND
                 ov.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
                 ov.observation_date <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
                 ds.last_report_obs_id = ov.observation_id(+)  AND
                 ov.parameter_id = p.id(+)
                 AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
                 AND p.name = 'SST'
    order by 1, 7;

Explain plan for Query A (optimized)

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL> explain plan for
  2  SELECT
  3                DISTINCT ds.platform_code id,
  4                ds.count_observations NumberOfObs,
  5                ds.platform_type type,
  6                ds.country country,
  7                ds.last_report_latitude latitude,
  8                ds.last_report_longitude_390 longitude,
  9                to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
 10                plat.url URL
 11            FROM
 12                osmc.daily_summary_mv ds,
 13                osmc.platform plat,
 14                osmc.observation_value ov,
 15                osmc.parameter p
 16            WHERE
 17                (ds.operational_status_id = 2 OR
 18                ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 19                ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 20                ds.observation_day = ov.observation_date AND
 21                (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 22                (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 23                ds.platform_code = plat.platform_code(+) AND
 24                ov.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 25                ov.observation_date <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
 26                ds.last_report_obs_id = ov.observation_id(+)  AND
 27                ov.parameter_id = p.id(+)
 28                AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 29                AND p.name = 'SST'
 30   order by 1, 7;

Explained.

SQL> set linesize 5000;
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------              
Plan hash value: 1965315024

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                          |     1 |   103 |   141   (3)| 00:00:02 |       |       |
|   1 |  SORT UNIQUE                              |                          |     1 |   103 |   140   (2)| 00:00:02 |       |       |
|   2 |   NESTED LOOPS OUTER                      |                          |     1 |   103 |   139   (1)| 00:00:02 |       |       |
|   3 |    NESTED LOOPS                           |                          |     1 |    84 |   138   (1)| 00:00:02 |       |       |
|   4 |     NESTED LOOPS                          |                          |     1 |    75 |   137   (1)| 00:00:02 |       |       |
|   5 |      PARTITION RANGE SINGLE               |                          |     1 |    61 |   135   (1)| 00:00:02 |    15 |    15 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       MAT_VIEW ACCESS BY LOCAL INDEX ROWID| DAILY_SUMMARY_MV         |     1 |    61 |   135   (1)| 00:00:02 |    15 |    15 |
|*  7 |        INDEX RANGE SCAN                   | DAILY_SUMMARY_MV_IDX1    |  5247 |       |    44   (0)| 00:00:01 |    15 |    15 |
|   8 |      PARTITION RANGE SINGLE               |                          |     1 |    14 |     3   (0)| 00:00:01 |    15 |    15 |
|*  9 |       INDEX RANGE SCAN                    | NS_OBSERVATION_VALUES_PK |     1 |    14 |     3   (0)| 00:00:01 |    15 |    15 |
|* 10 |     TABLE ACCESS BY INDEX ROWID           | PARAMETER                |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|* 11 |      INDEX RANGE SCAN                     | PARAMETER_IDX1           |     1 |       |     0   (0)| 00:00:01 |       |       |
|  12 |    TABLE ACCESS BY INDEX ROWID            | PLATFORM                 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|* 13 |     INDEX UNIQUE SCAN                     | PLATFORM_PLAFORM_CODE_UK |     1 |       |     0   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------

   6 - filter("DS"."LAST_REPORT_LATITUDE"<=(-44.61119292862682) AND "DS"."LAST_REPORT_LONGITUDE_390">=223.71840468842848 AND
              ("DS"."OBSERVATION_DAY">=TO_DATE('2007-09-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "DS"."OBSERVATION_DAY"<=TO_DATE('2007-09-18 23:59:59', 'yyyy-mm-dd hh24:mi:ss') OR "DS"."OPERATIONAL_STATUS_ID"=2) AND
              "DS"."LAST_REPORT_LONGITUDE_390"<=248.9643297687431 AND ("DS"."PLATFORM_TYPE"='DRIFTING BUOYS' OR
              "OSMC"."PLATFORM_TYPES_RELATED"('DRIFTING BUOYS',"PLATFORM_TYPE")='Y') AND 
              "DS"."LAST_REPORT_LATITUDE">=(-60.009277233033764))
   7 - access("DS"."OBSERVATION_DAY">=TO_DATE('2007-09-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "DS"."OBSERVATION_DAY"<=TO_DATE('2007-09-18 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
   9 - access("DS"."OBSERVATION_DAY"="OV"."OBSERVATION_DATE" AND "DS"."LAST_REPORT_OBS_ID"="OV"."OBSERVATION_ID")
       filter("OV"."OBSERVATION_DATE">=TO_DATE('2007-09-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
  10 - filter("OV"."PARAMETER_ID"="P"."ID")
  11 - access("P"."NAME"='SST')
  13 - access("DS"."PLATFORM_CODE"="PLAT"."PLATFORM_CODE"(+))

Note
-----
   - 'PLAN_TABLE' is old version

41 rows selected.

Results and timing for Query A (optimized)

SQL> set timing on;
SQL> set linesize 5000;
SQL> SELECT
  2                DISTINCT ds.platform_code id,
  3                ds.count_observations NumberOfObs,
  4                ds.platform_type type,
  5                ds.country country,
  6                ds.last_report_latitude latitude,
  7                ds.last_report_longitude_390 longitude,
  8                to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
  9                plat.url URL
 10            FROM
 11                osmc.daily_summary_mv ds,
 12                osmc.platform plat,
 13                osmc.observation_value ov,
 14                osmc.parameter p
 15            WHERE
 16                (ds.operational_status_id = 2 OR
 17                ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 18                ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 19                ds.observation_day = ov.observation_date AND
 20                (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 21                (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 22                ds.platform_code = plat.platform_code(+) AND
 23                ov.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 24                ov.observation_date <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
 25                ds.last_report_obs_id = ov.observation_id(+)  AND
 26                ov.parameter_id = p.id(+)
 27                AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 28                AND p.name = 'SST'
 29   order by 1, 7;

ID                   NUMBEROFOBS TYPE                                     CO   LATITUDE  LONGITUDE LAST_REPORT         URL
-------------------- ----------- ---------------------------------------- -- ---------- ---------- ------------------- ---
16525                         44 DRIFTING BUOYS                           US     -53.16  248.32001 2007-09-18 23:40:00
16562                         43 DRIFTING BUOYS                           US     -53.33     227.58 2007-09-18 23:00:00
55609                         43 DRIFTING BUOYS                           US     -51.45 234.240005 2007-09-18 23:30:00
55612                         46 DRIFTING BUOYS                           US      -54.1     236.94 2007-09-18 23:00:00
55612                         46 DRIFTING BUOYS                           US    -54.098    236.942 2007-09-18 23:00:00
55910                         57 DRIFTING BUOYS                           US     -47.35     235.05 2007-09-18 23:51:00
55927                         45 DRIFTING BUOYS                           US     -55.88 240.380005 2007-09-18 23:00:00
55927                         45 DRIFTING BUOYS                           US    -55.875    240.378 2007-09-18 23:00:00
55929                         45 DRIFTING BUOYS                           US    -57.431    234.564 2007-09-18 23:10:00
55929                         45 DRIFTING BUOYS                           US     -57.43     234.56 2007-09-18 23:10:00
56513                         44 DRIFTING BUOYS                           AU     -46.18     230.28 2007-09-18 23:40:00

11 rows selected.

Elapsed: 00:00:00.99

Autotrace results for Query A (optimized)

[auerbach@cheetah auerbach]$ sqlplus las@devl.ndbc.noaa.gov

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 8 09:12:36 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

3510584 rows selected.

SQL> set timing on;
SQL> set autotrace trace;
SQL> set linesize 1000;
SQL> SELECT
  2                   DISTINCT ds.platform_code id,
  3                   ds.count_observations NumberOfObs,
  4                   ds.platform_type type,
  5                   ds.country country,
  6                   ds.last_report_latitude latitude,
  7                   ds.last_report_longitude_390 longitude,
  8                   to_char(ds.last_report_date, 'YYYY-MM-DD hh24:mi:ss') Last_Report,
  9                   plat.url URL
 10               FROM
 11                   osmc.daily_summary_mv ds,
 12                   osmc.platform plat,
 13                   osmc.observation_value ov,
 14                   osmc.parameter p
 15               WHERE
 16                   (ds.operational_status_id = 2 OR
 17                   ds.observation_day >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 18                   ds.observation_day <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss')) AND
 19                   ds.observation_day = ov.observation_date AND
 20                   (ds.last_report_longitude_390 >= 223.71840468842848 AND ds.last_report_longitude_390 <= 248.9643297687431 ) AND
 21                   (ds.last_report_latitude>=-60.009277233033764 AND ds.last_report_latitude<=-44.61119292862682) AND
 22                   ds.platform_code = plat.platform_code(+) AND
 23                   ov.observation_date >= to_date('2007-09-18 00:00:00','yyyy-mm-dd hh24:mi:ss') AND
 24                   ov.observation_date <= to_date('2007-09-18 23:59:59','yyyy-mm-dd hh24:mi:ss') AND
 25                   ds.last_report_obs_id = ov.observation_id(+)  AND
 26                   ov.parameter_id = p.id(+)
 27                   AND (ds.platform_type= 'DRIFTING BUOYS' OR osmc.platform_types_related('DRIFTING BUOYS', platform_type) = 'Y')
 28                   AND p.name = 'SST'
 29      order by 1, 7;

11 rows selected.

Elapsed: 00:00:01.68

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=141 Card=1 Bytes=103)
   1    0   SORT (UNIQUE) (Cost=140 Card=1 Bytes=103)
   2    1     NESTED LOOPS (OUTER) (Cost=139 Card=1 Bytes=103)
   3    2       NESTED LOOPS (Cost=138 Card=1 Bytes=84)
   4    3         NESTED LOOPS (Cost=137 Card=1 Bytes=75)
   5    4           PARTITION RANGE (SINGLE) (Cost=135 Card=1 Bytes=61)
   6    5             MAT_VIEW ACCESS (BY LOCAL INDEX ROWID) OF 'DAILY_SUMMARY_MV' (MAT_VIEW) (Cost=135 Card=1 Bytes=61)
   7    6               INDEX (RANGE SCAN) OF 'DAILY_SUMMARY_MV_IDX1' (INDEX) (Cost=44 Card=5247)
   8    4           PARTITION RANGE (SINGLE) (Cost=3 Card=1 Bytes=14)
   9    8             INDEX (RANGE SCAN) OF 'NS_OBSERVATION_VALUES_PK' (INDEX (UNIQUE)) (Cost=3 Card=1 Bytes=14)
  10    3         TABLE ACCESS (BY INDEX ROWID) OF 'PARAMETER' (TABLE) (Cost=1 Card=1 Bytes=9)
  11   10           INDEX (RANGE SCAN) OF 'PARAMETER_IDX1' (INDEX) (Cost=0 Card=1)
  12    2       TABLE ACCESS (BY INDEX ROWID) OF 'PLATFORM' (TABLE) (Cost=1 Card=1 Bytes=19)
  13   12         INDEX (UNIQUE SCAN) OF 'PLATFORM_PLAFORM_CODE_UK' (INDEX (UNIQUE)) (Cost=0 Card=1)

Statistics
----------------------------------------------------------
         56  recursive calls
          0  db block gets
        507  consistent gets
         73  physical reads
       3764  redo size
       1277  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         46  sorts (memory)
          0  sorts (disk)
         11  rows processed

Links

OSMC

Personal tools