In order to tune queries, What metrics can be collected from Explain Plan
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.
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"
2. elapsed time
4. Impact CPU
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?
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
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 (
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
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
'UNKNOWN') OR (COMDB.RSH in view
'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 (
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 (
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?
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.