CODEWITHSUNDEEP
sqloracle-databasejoinquery-tuning
Nil Pun
Nil Pun

Reputation: 17373

Oracle SQL Query Running Slow

I've a oracle SQL Query below used for Search Functionality and it's wrapped in Stored Procedure.

It runs very slow.

OPEN p_cursor FOR SELECT TableA.*, TB_S.someColumn, TB_S.SomeColumn .. 

    FROM
        customer_site TableA
        LEFT JOIN TableB TB on TB.some_id = TableA.some_id
        LEFT JOIN TableC TB_S on TB_S.Bla_ID = TB.Bla_ID
        LEFT JOIN TableC TB_CS on TB_CS.Bla2_ID = TB.Bla2_ID
        LEFT JOIN TableC TB_1 on TB_1.Bla3_ID = TB.Bla3_ID
        LEFT JOIN TableC TB_2 on TB_DC.Bla4_ID = TB.Bla4_ID
        LEFT JOIN TableD SP on SP.SP_ID = TableA.SP_ID
        LEFT JOIN TableC TB_3 on TB_3.Bla5_ID = TB.Bla5_ID
        LEFT JOIN TableC TB_4 on TB_4.Bla6_ID = TB.Bla6_ID
        LEFT JOIN TableC TB_5 on TB_5.Bla7_ID = TB.Bla7_ID
    WHERE 
        (p_nmi IS NULL OR TableA.someid LIKE p_nmi)
        AND (p_last_name IS NULL OR TableA.last_name LIKE p_last_name)
        AND (p_full_address IS NULL OR
            UPPER(assemble_address(flat_number, street_number, street_name,
                street_suffix, apartment_number, building_name, suburb, state, postcode))
            LIKE p_full_address)
        AND (p_param1 IS NULL OR TB.owner = p_param1)
        AND (p_param2 IS NULL OR TB.status = p_param2)
        AND (p_param3 IS NULL OR TB.contact_stage = p_param3)
        AND (p_param4 IS NULL OR TB.no_access_code = p_param4)
        AND (p_param5 IS NULL OR TB.defect_code = p_param5)
        AND (p_param6 IS NULL OR TB.REFUSAL_RESOLUTION = p_param6)
        AND (p_param7 IS NULL OR TB.DEFECT_LEVEL = p_param7)
        AND (p_param8 IS NULL OR TB.AMI_CTR_STATUS = p_param8)
        AND (p_param9 IS NULL OR TableA.meter_route LIKE p_param9)
        AND (p_param10 IS NULL OR TableA.sp_id = p_param10)
        AND (p_inTBdent_date_from IS NULL OR TB.inTBdent_date >= p_inTBdent_date_from)
        AND (p_inTBdent_date_to IS NULL OR TB.inTBdent_date <= p_inTBdent_date_to)
        AND rownum < 1001
    ORDER BY
        TB.inTBdent_date;

Could someone DB expert help me how above query can be tuned?

Thank you.

Upvotes: 1

Views: 2314

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

As @Annjawn suggested, change all instances of (p_param IS NULL or col = p_param) to col = nvl(p_param, col). Oracle can optimize those conditions, as explained by this Jonathan Lewis article. This might enable an INDEX RANGE SCAN instead of a TABLE ACCESS FULL. However that change is only logically equivalent when the columns are NOT NULL, because null = null will not return true. (Personally I prefer the way you have written the conditions, but Oracle seems to hate ORs.)

If that doesn't help, post the explain plan to identify the real issue. First, modify the procedure and add the hint SELECT /*+ gather_plan_statistics */ TableA.*, .... Run the procedure and then find the relevant SQL_ID with a query like select * from v$sql where lower(sql_fulltext) like '%gather_plan_statistics%';. Finally, post the results of select * from table(dbms_xplan.display_cursor(sql_id => '<sql_id from previous step>', format => 'allstats last'));. That will tell us the execution plan and probably what poor decisions the optimizer is making.

Upvotes: 1

Related Questions