Optimization of pl/sql

3128872

Hi All ,

This is my query it takes 30 second to process . I need to optimize it pls provide me some solution. I also heard about EXPLAIN PLAN what is that?

                              Select  unique api_WEEKLY.country From api_WEEKLY INNER JOIN USERPROFILE

                                   ON api_WEEKLY.REGION  = NVL(USERPROFILE.REGION,api_WEEKLY.REGION)

                                   AND api_WEEKLY.Category = NVL(USERPROFILE.SECTOR,api_WEEKLY.Category)

                                   AND api_WEEKLY.MRP_DRP_CONTROLLER  = NVL(USERPROFILE.MRP_DRP_CONTROLLER,api_WEEKLY.MRP_DRP_CONTROLLER)

                                   AND api_WEEKLY.TDC_VAL = NVL(USERPROFILE.TDC_VAL,api_WEEKLY.TDC_VAL)

                                   AND api_WEEKLY.PLANT  = NVL(USERPROFILE.PLANT,api_WEEKLY.PLANT)

                                    AND api_WEEKLY.MATERIAL = NVL(USERPROFILE.MATERIAL,api_WEEKLY.MATERIAL)

                                   AND api_WEEKLY.SUBSECTOR  = NVL(USERPROFILE.SUBSECTOR,api_WEEKLY.SUBSECTOR)

                                   AND api_WEEKLY.COUNTRY = NVL(USERPROFILE.COUNTRY,api_WEEKLY.COUNTRY)

                                   WHERE USERPROFILE.USER_ID = 'sheikh.a' ;

and this is my User table

Chris Hunt

I also heard about EXPLAIN PLAN what is that?

Have you heard about Google? This was the first hit I got when I searched for "Explain Plan": Using EXPLAIN PLAN

Paulzip

Your query can't use any indexes because you are applying a function (NVL) to each of the where clause predicates.  So it will be doing a full table scan.

gaverill

What about...

alter session set optimizer_guess_op_meaning = true;

Select  unique api_WEEKLY.country From api_WEEKLY INNER JOIN USERPROFILE
                                  ON api_WEEKLY.REGION  = NVL(USERPROFILE.REGION,api_WEEKLY.REGION)
                                  AND api_WEEKLY.Category = NVL(USERPROFILE.SECTOR,api_WEEKLY.Category)
                                  AND api_WEEKLY.MRP_DRP_CONTROLLER  = NVL(USERPROFILE.MRP_DRP_CONTROLLER,api_WEEKLY.MRP_DRP_CONTROLLER)
                                  AND api_WEEKLY.TDC_VAL = NVL(USERPROFILE.TDC_VAL,api_WEEKLY.TDC_VAL)
                                  AND api_WEEKLY.PLANT  = NVL(USERPROFILE.PLANT,api_WEEKLY.PLANT)
                                    AND api_WEEKLY.MATERIAL = NVL(USERPROFILE.MATERIAL,api_WEEKLY.MATERIAL)
                                  AND api_WEEKLY.SUBSECTOR  = NVL(USERPROFILE.SUBSECTOR,api_WEEKLY.SUBSECTOR)
                                  AND api_WEEKLY.COUNTRY = NVL(USERPROFILE.COUNTRY,api_WEEKLY.COUNTRY)
                                  WHERE USERPROFILE.USER_ID = 'sheikh.a' ;

no rows selected.

Elapsed Time for Script Execution: 518 msecs.

At least it's faster...

Gerard

Billy Verreynne

What you have posted is akin to saying your car is slow, it is painted green, uses unleaded fuel, and runs on 4 tires.

In other words, nothing about WHAT is actually the problem. Going slow (in a car or a query) is a symptom at best - not a problem definition.

See - from the FAQ (link right at the top of this forum space's home page).

Jonathan Lewis

Since you have a row where all the columns (other than user_id) are null then the query is equivalent to:

select distinct

        wek.country

from

        api_weekly wek

where

        wek.region              is not null

and    wek.category            is not null

and    wek.mrp_drp_controller  is not null

and    wek.tdc_val            is not null

and    wek.plant              is not null

and    wek.material            is not null

and    wek.subsector          is not null

and    wek.country            is not null

;

Regards

Jonathan Lewis