There are no quick steps available to understand the explain plan completely. Please go through the documentation for a day or two.
I've provided some points which can be a start up for you (Hope it helps)
Please refer below for some the terms(There are lotsss more :) ) that you might see in an explain plan. On seeing them decide whether you want that to happen or not. Based on that change your query.
- check for the data flow across Spool files (Look at their size mentioned in the plan and if its too large then try to reduce it by introducing filters or trying follow a sequence where start from small table and move towards a large table but in a logical way)
- Check if some joins result in a extremely large spool file (If so then try to reduce the number of records that qualify for the joins. Avoid residual conditions if possible - Though this might not be needed always)
- Look for the term "Redistribution" and "Duplication" (For ex: In a join duplicating a smaller table is better than redistributing a larger one. If this doesnt happen collect stats on the smaller table. This might change the plan.)
- See for terms which indicate that the plan is using an index(For UPI - 'using unique primary index, For NUPI - 'Using primary index' for USI - 'using unique index # 12, for NUSI - 'Using index #34. If none appears then see if you can add indexes in your query to make it efficient)
- Look for terms like All Amp, Group AMp etc
- Look for terms indicating confidence levels. ( High confidence, Low confidence and No confidence which directly indicates the level to which it has prior knowledge on the data it is using. Try collecting stats on columns where the confidence level is very low)
- Look if partition elimination happens for PPI Tables. If not present try to use the PPI column in you where clause.
- Look for BMSMS (Incase of several NUSI being used, try to use them with an AND operation instead of an OR)