How to add parallel hints in sql queries

I see the below query is the one which is taking long to finish and i have been asked to tune it.


There is an sql part like below which was taking longer. I tried to find out if there was any better plan available but did not find any.

select     distinct a19.STD_MOI_DESC  MOI,

                a16.VEH_KEY  VEH_KEY,

                a111.VEH_SOURCE_DESC  VEH_SOURCE_DESC,

                a110.LOC_STATE_CODE  LOC_STATE_CODE,

                a18.COND_DRIVEABLE_DESC  COND_DRIVEABLE_DESC,

                a18.COND_REPAIRABLE_DESC  COND_REPAIRABLE_CODE,

                a11.DL_USER_ACTV_FLG  DL_USER_ACTV_FLG,

                a15.COMPANY_CD  COMPANY_CD,

                a15.CUST_NM  CUST_NM,

                a12.ACTIVITY_TYPE_ID  ACTIVITY_TYPE_ID,

                a12.ACTIVITY_TYPE_NAME  ACTIVITY_TYPE_NAME,

Here is what we can do to speed up the process. We can add parallelism hint and the same query should let parallel sessions and increase processing power by 8 times.

select    /*+ parallel(8) */  distinct a19.STD_MOI_DESC  MOI,

                a16.VEH_KEY  VEH_KEY,

                a111.VEH_SOURCE_DESC  VEH_SOURCE_DESC,

                a110.LOC_STATE_CODE  LOC_STATE_CODE,

                a18.COND_DRIVEABLE_DESC  COND_DRIVEABLE_DESC,

                a18.COND_REPAIRABLE_DESC  COND_REPAIRABLE_CODE,

                a11.DL_USER_ACTV_FLG  DL_USER_ACTV_FLG,

                a15.COMPANY_CD  COMPANY_CD,

                a15.CUST_NM  CUST_NM,

                a12.ACTIVITY_TYPE_ID  ACTIVITY_TYPE_ID,

                a12.ACTIVITY_TYPE_NAME  ACTIVITY_TYPE_NAME,

Parallel sessions runs and use more resources so we have to be careful if this situation applies to you. Other options also include adding a profile which again gives parallelism and we have to be careful and monitor. Sometimes parallelism is fine but not all and we have to look for options like adding index or gathering stats.

Hope this helps.

Ahmad