Forum Stats

  • 3,816,525 Users
  • 2,259,201 Discussions
  • 7,893,505 Comments

Discussions

Categories

Optimization of pl/sql

3128872
3128872 Member Posts: 7
edited Dec 3, 2015 1:42PM in SQL & PL/SQL

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

profile.PNG

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Nov 26, 2015 9:58AM 
    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

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,677 Blue Diamond
    edited Nov 26, 2015 10:02AM

    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.

    · Share on TwitterShare on Facebook
  • gaverill
    gaverill Member Posts: 390 Silver Badge
    edited Dec 1, 2015 3:24PM

    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

    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,804 Red Diamond
    edited Dec 2, 2015 1:22AM

    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).

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,953 Blue Diamond
    edited Dec 3, 2015 1:42PM

    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

    · Share on TwitterShare on Facebook
This discussion has been closed.