Oracle 11 query runs fast on first 2 executions, slower on subsequent, no plan change

[adding edit as answer, as requested by Jon Heller]

This was caused by cardinality feedback. I thought I had tested this earlier and eliminated it, but clearly got it wrong.

Added this to query:

select --+ opt_param('_optimizer_use_feedback' 'false')

Now consistently fast.

Your query has a number of problems as written:

• You make very heavy use of COALESCE instead of just checking for NULL explicitly. This could negatively impact the use of indexes. Notice that these filters correspond to full table scans in the plan.
• You have a LEFT JOIN followed by several inner JOINs. It's not clear what your intention is (at least not without staring at it for 15 minutes). Should rows where the LEFT JOIN finds no match still be included with NULL for all JOINed table columns, be left out of the result set if there's no match in the following JOINed tables, or something else?
• You actually don't even need to JOIN some tables. You're filtering to a specific org_code and you never use the columns from tdb.organisation, so that join accomplishes nothing. tdb.loc_site_code is not referenced anywhere, so the join is just a simple filter to valid data values in that column at most. If the relating column is a DB enforced foreign key, then it's redundant.
• Your static filters are in your JOIN conditions instead of in the WHERE clause. This makes it more difficult to read and understand.

I'd advise rewriting the query completely and see how that performs. Try this:

select distinct    cc2.circuit_id as circuit_id,    cc2.circuit_component_id as component_circuit_idfrom bsdb.bs_instance sijoin bsdb.bs_location_schedule ls on ls.bs_instance_id = si.idjoin npc.npc_customer_service cs on cs.bs_location_schedule_id = ls.idjoin npc.npc_customer_service_circuit csc on csc.customer_service_id = cs.customer_service_idjoin npc.npc_circuit_component cc on cc.circuit_id = csc.circuit_idjoin npc.npc_circuit_hierarchy ch on ch.sub_circuit_id = cc.circuit_component_idjoin npc.npc_circuit_component cc2 on cc2.circuit_id = ch.master_circuit_id-- join tdb.loc_site_code lsc on lsc.id = ls.site_code_id -- Uncomment if ls.site_code_id isn't a foreign keyleft outer join scdb.brand br on br.id = si.brand_idwhere    coalesce(br.brand_org_code, si.client_org_code) = 2421    and cs.circuit_status_id in (1, 2, 6)    and (ls.terminated_date IS NULL OR ls.terminated_date >= sysdate)    and (csc.end_date IS NULL OR csc.end_date >= sysdate)    and (cc.end_date IS NULL OR cc.end_date >= sysdate)    and (cc2.end_date IS NULL OR cc2.end_date >= sysdate)    -- and ls.site_code_id IS NOT NULL -- Uncomment if site_code_id is a nullable foreign key;

Much easier to make sense of:

• Static filters all in the WHERE clause. (Note that this doesn't negatively impact performance. The DB is smart enough to figure out that it can do static filters before the JOINs.)
• Date columns have separate NULL checks, instead of using COALESCE that might prevent the planner from using indexes.
• JOINs are exclusively on simple table ID columns.
• The JOINs progress in a clear, straightforward, easy to follow order. Most of them reference an ID column in the table directly above it.
• LEFT JOIN located at the end of the query, making it clear which tables' columns can be all NULL or not.
• No joins to extraneous tables. All of them are used either for result columns or filtering.

If the organization code is a very selective filter (filters out a lot of rows), try replacing coalesce(br.brand_org_code, si.client_org_code) = 2421 with

((br.brand_org_code IS NULL and si.client_org_code = 2421) or br.brand_org_code = 2421)

Take careful note of the parentheses. The planner might have a better chance of realizing it can use indexes on this.

If you really do need tdb.organisation for some reason and just aren't referencing those columns in this particular query, turn the above into a subquery and join to it after all the other filtering and transformation:

select distinct    circuit_id,    component_circuit_idfrom (    select        cc2.circuit_id as circuit_id,        cc2.circuit_component_id as component_circuit_id,        coalesce(br.brand_org_code, si.client_org_code) as org_code    from bsdb.bs_instance si    join bsdb.bs_location_schedule ls on ls.bs_instance_id = si.id    join npc.npc_customer_service cs on cs.bs_location_schedule_id = ls.id    join npc.npc_customer_service_circuit csc on csc.customer_service_id = cs.customer_service_id    join npc.npc_circuit_component cc on cc.circuit_id = csc.circuit_id    join npc.npc_circuit_hierarchy ch on ch.sub_circuit_id = cc.circuit_component_id    join npc.npc_circuit_component cc2 on cc2.circuit_id = ch.master_circuit_id    -- join tdb.loc_site_code lsc on lsc.id = ls.site_code_id -- Uncomment if ls.site_code_id isn't a foreign key    left outer join scdb.brand br on br.id = si.brand_id    where        coalesce(br.brand_org_code, si.client_org_code) = 2421        and cs.circuit_status_id in (1, 2, 6)        and (ls.terminated_date IS NULL OR ls.terminated_date >= sysdate)        and (csc.end_date IS NULL OR csc.end_date >= sysdate)        and (cc.end_date IS NULL OR cc.end_date >= sysdate)        and (cc2.end_date IS NULL OR cc2.end_date >= sysdate)        -- and ls.site_code_id IS NOT NULL -- Uncomment if site_code_id is a nullable foreign key) active_circuit_componentsjoin tdb.organisation org on org.org_code = active_circuit_components.org_code;

I'd also recommend coming up with meaningful aliases. With this number of aliases and how similar they all look at a glance, it's hard to keep track of which table is which. Some examples:

• si: bs_instance
• ls: loc_sched
• cs: cust_serv
• csc: cust_serv_circuit

They're more verbose, but they'll make it vastly easier to make semantic sense of the query when you or another developer comes back to it. I know such short aliases are common practice, but for large queries, it really matters whether you have to look back at the list of tables just to check whether a column is coming from the table you think it is.

I'll take a stab at this.

Using the information you've provided I have the following observations:

1 - You can use Index Hints

When the SQL is slow it is not accessing the following tables in the same way: - npc_circuit_component - npc_circuit_hierarchy

The NPC_CIRCUIT_COMPONENT_I01 and NPC_CIRCUIT_HIERARCHY_I02 indexes are not being used when accessing these tables. Both these are also shown in your explain plan.

A solution here (without modifying your query) is to use Index Hints to force the use of this indexes all the time.

/*+ index(ch npc_circuit_hierarch_i02) *//*+ index(cc npc_circuit_component_i01) */

2 - Rebuild Indexes or Gather Stats

If you haven't done so already, stats may need to be gathered or rebuild the above indexes so they are updated before using the hint.

3 - The Query Itself

The need for a hint usually implies that there may be an issue with how the query is written. Whilst I can't quite get a grasp of what is in the database tables without completely understanding the data model this section could possibly be written better:

bsdb.bs_instance sileft outer join scdb.brand br    on br.id = si.brand_idjoin tdb.organisation o    on o.org_code = coalesce(br.brand_org_code, si.client_org_code)    and o.org_code = 2421

The org code is hardcoded here and may make the use of the organisation table redundant. You mentioned that the LEFT OUTER JOIN when removed will make it run faster. You can rewrite it with a WHERE:

WHERE CASE WHEN si.brand_id IS NULL THEN        si.client_org_code      ELSE        SELECT br.brand_org_code FROM brand br         WHERE br.id = si.brand_id      END = 2421 -- Ord Code

This is just going off the top of my head, but this avoids having to use the left outer join and also removes the use of coalesce which will make any index on the brand_org_code or client_org_code columns redundant.

Anyway...not sure if this will help. Hope it does. Good Luck!

Please correct my understanding if anything here is incorrect.