Skip to Main Content
  • Questions

  • Execution Plan sharing across containers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kathryn Gamble.

Asked: March 06, 2025 - 1:26 pm UTC

Last updated: March 20, 2025 - 2:00 am UTC

Version: 19.26

Viewed 1000+ times

You Asked

If a SQL statement in pdb container A is exactly the same SQL statement as in pdb container B, will they share the same execution plan even though the owner of the tables and the user owning the session (including GTTs) will be different in each container?

and Connor said...

They may end up *using* the same plan, but they are still considered different cursors, eg

SQL> show con_id

CON_ID
------------------------------
3
SQL> select /*+ findme */ count(*) from scott.emp;

  COUNT(*)
----------
        14
        

SQL> show con_id

CON_ID
------------------------------
5
SQL> select /*+ findme */ count(*) from scott.emp;

  COUNT(*)
----------
        14
        
        
        
SQL> select sql_id, full_plan_hash_value, child_number, sql_text
  2  from v$sql
  3  where sql_text like '%findme%';

SQL_ID        FULL_PLAN_HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- -------------------- ------------ ----------------------------------------------------------------------
b4prmvh9nmx0n            749996338            0 select /*+ findme */ count(*) from scott.emp
b4prmvh9nmx0n            749996338            1 select /*+ findme */ count(*) from scott.emp


Rating

  (3 ratings)

Comments

Kathryn Gamble, March 09, 2025 - 8:46 am UTC

Thanks.
My scenario is

Container 1: connected as Scott:
T1_gtt And t2 contain approx 2000 rows each
Select * from t1_gtt a
Join T2 b on a.c1 = b.c1
Where a.c2= :1
;

Container 1: connected as tiger (i.e. different schemas)
T1_gtt and T2 contain millions of rows
Select * from t1_gtt a
Join T2 b on a.c1 = b.c1
Where a.c2= :1
;
I.e different schemas with same table structure.
If the data is different, is it possible that scenario 2 would use the execution plan generated for scenario 1. Which may not be optimal for scenario 2.

We are seeing huge differences (minutes to hours) in execution time for scenario 2 day to day even though data volumes are consistent in each scenario and gather_table_stats is done before the query in each scenario.

If we modify the query in scenario 2 to force a parse (different sql_id), the execution time is back to minutes again.

I had thought that because the queries are in different containers and schemas, the optimise would not reuse the execution plan from another container and schema.

If it does reuse it, would I need to force a reparse in scenario 2 in order to get the most optimal plan for the larger data volumes?




.




Connor McDonald
March 10, 2025 - 3:49 am UTC

If the data is different, is it possible that scenario 2 would use the execution plan generated for scenario 1. Which may not be optimal for scenario 2.

No. Notice the two different children cursors in my first response. This means they could not be shared. Each cursor points to its own plan

(In my example, the fact that they landed on the same plan was because everything was identical).

Kathryn Gamble, March 10, 2025 - 5:13 am UTC

Thanks very much for the clarification.


Connor McDonald
March 20, 2025 - 2:00 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions