Oracle SQL Query Running Slow

Question

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.

`assemble_address` seems to be a function. Consider creating a function based index on it. You can also try changing the `AND` clauses like `AND TB.owner = NVL(p_param1,TB.owner)` and so on. — Anjan Biswas, Feb 05 '14 at 01:14
If you are selecting only from `tableA` and filtering only by `tableA` and `tableB` then why so many joins on the other tables? That too, you are performing `left join`s, which will not filter any rows from `tableA` or `tableB`. Moreover, if you are putting filtering conditions in your `where` clause, it is better to put an `inner join` with `tableB`. Have you tried running this query outside the procedure, with hardcoded parameters? It would be helpful for us if you do that and post the explain plan for that query. — Rachcha, Feb 05 '14 at 01:25
My bad, the select query will definitely need to get column from every tables that's been joined. — Nil Pun, Feb 05 '14 at 01:50

Jon Heller · Answer 1 · 2014-02-05T06:02:48.277

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.

I'm new to Oracle, does Oracle SQL Developer has Query Execution plan or similar? What is explain plan? — Nil Pun, Feb 05 '14 at 05:29
The explain plan shows the steps Oracle uses to retrieve the results. There are front-ends to the explain plans but they are usually worse than the text format. The simplest way to get an explain plan is to run this statement: `explain plan for SELECT TableA.*,...` to generate the plan and then run `select * from table(dbms_xplan.display);` to display the plan. Unfortunately in your case there are a lot of bind variables so generating the explain plan that way may not be accurate. Getting plan statistics is trickier but is helpful because it tells you *actual* numbers, not just estimates. — Jon Heller, Feb 05 '14 at 06:01

Oracle SQL Query Running Slow

1 Answers1