Collecting Query Metrics

Database
Enthusiast

Collecting Query Metrics

Hello !

In order to tune queries, What metrics can be collected from Explain Plan

Vs

metrics can be collected only after running the query (from DBQL).

In other words, what query info can be collected without running the query Vs after running the query.

6 REPLIES
Enthusiast

Re: Collecting Query Metrics

from explain :

1. you should see any product joins happening

2. any big redistrubution/duplication

3. total time taken by query to run (this is not the wall clock time) and an approx one

4. do a diagnostic helpstats on the query anc check if any stats are being suggested with "high confidence"

from bbql:

1. ampcpu

2. elapsed time 

3. I/O

4. Impact CPU

Enthusiast

Re: Collecting Query Metrics

DBQL details can be collected only after running the query. Right?

I don't think there is way to get the same w/t running the query. Possible?

Senior Apprentice

Re: Collecting Query Metrics

Of course you can't get the CPU, IO, etc. metrics without actually running the query.

Some bad plans can easily be recognized by looking at Explain, but a good looking explain might have totally wrong estimates which lead to really bad steps.

Without DBQL there's no easy way to find out, QueryMonitor/QuerySpotlight in Viewpoint are also helpful, but QryLogStepsV shws much more details.

For a bad erforming query 

Enthusiast

Re: Collecting Query Metrics

Thanks Dieter for the valueable info !

Though i can see some keywords showing bad indicators, but really having hard time to narrow down to where in query to fix..

For Ex: i see a product join but where exactly i need to fix:

Sample two steps from same query explain plan...

27) We execute the following steps in parallel.

      1) We do an all-AMPs JOIN step from Spool 44 (Last Use) by way of

         an ALL-ROWS scan, which IS joined TO MYDB.ABC. BY way OF an

         all-rows scan with a condition of (

         "MYDB.ABC.DV_BK_FISCAL_YEAR_MONTH_INT >= 201404") LOCKING

         MYDB.ABC. for access.  Spool 44 and MYDB.ABC. are joined

         using a product join, with a join condition of (

         "MYDB.ABC.DV_BK_FISCAL_YEAR_MONTH_INT =

         FISCAL_YEAR_MONTH_INT").  The result goes into Spool 47

         (all_amps) (compressed columns allowed) fanned out into 3 hash

         join partitions, which is built locally on the AMPs.  The size

         of Spool 47 is estimated with low confidence to be 117,293,351

         rows (9,383,468,080 bytes).  The estimated time for this step

         is 19.41 seconds.

      2) We do an all-AMPs RETRIEVE step from 9 partitions of

         MYDB.MX. WITH a CONDITION OF (

         "(MYDB.MX..FISCAL_YEAR_QUARTER_NUMBER_INT >= 20131) AND

         ((MYDB.MX..FISCAL_YEAR_QUARTER_NUMBER_INT <= 20151) AND

         (MYDB.MX..BK_BUSINESS_ENTITY_TYPE_CD = 'I'))") locking for

         access into Spool 48 (all_amps) (compressed columns allowed)

         fanned out into 6 hash join partitions, which is redistributed

         by the hash code of (MYDB.MX..ITEM_KEY,

         MYDB.MX..FISCAL_YEAR_QUARTER_NUMBER_INT) to all AMPs.  The

         size of Spool 48 is estimated with low confidence to be

         6,352,666 rows (883,020,574 bytes).  The estimated time for

         this step IS 1.00 seconds.

-----------------------------------

38) We execute the following steps in parallel.

      1) We do an all-AMPs JOIN step from

         MYDB.S_FISCAL_MONTH_TO_YEAR in view

         FINVDB.BV_FISCAL_MONTH_TO_YEAR by way of an all-rows scan

         with no residual conditions, which is joined to Spool 63 (Last

         Use) by way of an all-rows scan locking

         MYDB.S_FISCAL_MONTH_TO_YEAR for access.

         MYDB.S_FISCAL_MONTH_TO_YEAR AND SPOOL 63 are joined USING

         a product join, with a join condition of ("(1=1)").  The

         result goes into Spool 64 (all_amps) (compressed columns

         allowed), which is duplicated on all AMPs.  The size of Spool

         64 is estimated with high confidence to be 1,853,712 rows (

         131,613,552 bytes).  The estimated time for this step is 0.06

         seconds.

      2) We do an all-AMPs JOIN step from COMDB.RSH in view

         FINVDB.BV_SLS_HRCHY by way of a RowHash match scan

         with a condition of ("(COMDB.RSH in view

         FINVDB.BV_SLS_HRCHY.SLS_TRY_TYPE_CODE =

         'UNKNOWN') OR (COMDB.RSH in view

         FINVDB.BV_SLS_HRCHY.SLS_TRY_TYPE_CODE =

         'CORP. REVENUE')"), which is joined to

         FINGDB.MT_FIN_SALES_THR_HR in view

         FINVDB.BV_FIN_SALES_THR_HR BY way OF a RowHash match

         scan with no residual conditions.  COMDB.RSH and

         FINGDB.MT_FIN_SALES_THR_HR are joined using a merge

         join, with a join condition of (

         "COMDB.RSH.SLS_TRY_KEY =

         FINGDB.MT_FIN_SALES_THR_HR.SLS_TRY_KEY").  The

         result goes into Spool 65 (all_amps) (compressed columns

         allowed), which is duplicated on all AMPs.  Then we do a SORT

         to order Spool 65 by the hash code of (

         COMDB.RSH.SLS_TRY_KEY,

         FINGDB.MT_FIN_SALES_THR_HR.SLS_TRY_KEY).  The

         size of Spool 65 is estimated with low confidence to be

         26,162,976 rows (21,035,032,704 bytes).  The estimated time

         for this step is 12.71 seconds.

What would be your suggestions to fix this product join?

Enthusiast

Re: Collecting Query Metrics

Product joins are not always bad. Sometime, optimier chooses this option and that is the correct one. for e.g, if there is a small table say 1 row and it is duplicated to all amps. Now, if you join another table table on the same column, the product join will be used and it is correct. another example is when you have non equality condition in the join condition e,g date1 >= date2, he PJ will happen. so, just check the join conditions and it should not be non-equality condition in the join.

Few things that you can check for PJ in your query is :

1. If aliasing is done correctly, if used

2. No join conditions are missing

3. Stats are there on the joining columns

4. do a diagnostic helpstats on for session and check what stats are being suggested by optimizer

5.  non equality join condition

But as a developer, you should have full understanding of the data in the underlying tables and then you can choose right columns to join.

Enthusiast

Re: Collecting Query Metrics

Thanks !

Any suggestions how to avoid non equality condition in the join condition like

 '>=' , '<=' etc