3 Replies Latest reply: May 20, 2014 12:13 AM by VJ4 RSS

    Load sql plan from node2 executing DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE from node1

    VJ4

      Hi All,

       

      I am using DB : 11.2.0.3 OS : Linux x86 RAC : 8 nodes.

       

      I am trying to build a script which will find the suitable plan in CURSOR CACHE and load it. As per the normal steps performed in RAC environment, we check respective plans(plan_hash_value) and in which instance it belongs. Then we login into that instance and execute :-

       

      exec :cnt:=dbms_spm.load_plans_from_cursor_cache(

        sql_id => 'djkqjd0kvgmb5',

        plan_hash_value => 3074207202, 

        sql_handle => 'SYS_SQL_bf5c9b08f72bde3e‘);

       

       

      I wanted to know is their any such technique that being on one instance I can load plan from other instance's also ?

       

      If in RAC we can kill sessions at different instances like "ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>,@<INST_ID>' IMMEDIATE;' then can someone guide me how this command works and will the logic help me to load plan from different instances.

       

      Let me know if I query in not clear.

       

      Thanks in advance.

        • 2. Re: Load sql plan from node2 executing DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE from node1
          J.A.

          Hi

           

          Check the sql ID from all instances:

           

          select

             sql_text,

             sql_id

          from 

             GV$SQL

          where

             sql_text like '%employee%';

           

          --Load the sql_id into the SQL Basesile

          declare

            v_sql_plan_id  pls_integer;

          begin

            v_sql_plan_id := dbms_spm.load_plans_from_cursor_cache(

              sql_id => '0gh1gn1mv7u0u');

          end;

          /

           

          --Check if the plan is being used and is accepted

          select

             sql_handle,

             plan_name,

             enabled,

             accepted,

             fixed

          from 

             dba_sql_plan_baselines

          where

             sql_text like '%employee%';

           

           

          I hope this help you

           

          Regards

          • 3. Re: Load sql plan from node2 executing DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE from node1
            VJ4

            Hi J.A. ,

             

            Thank you for your reply.

            GV$SQL - I will see the sqlid and the plan_hash_value for which it is giving best results. If I am executing this query on instance 1 and for ex: - I find that sql_id='abc1234' plan_hash_value='1234567' and inst_id='4' are performing best for my query I need to load the plan(plan_hash_value)  from the cursor cache(of instance 4). The method to load the plan is :- dbms_spm.load_plans_from_cursor_cache();.  To load the plan from instance 4 (inst_id='4') I need to execute this method from instance 4.

             

            My query is that being on instance 1 can I execute "dbms_spm.load_plans_from_cursor_cache();"  and load plan from instance 4 or any other instance?


            something like dbms_spm.load_plans_from_cursor_cache()@4;


            My observation is if we can kill session on remote instances( like @<inst_id>) then their might be some mechanism to get data from different instances.