1 2 Previous Next 18 Replies Latest reply: Mar 27, 2013 5:59 PM by rp0428 RSS

    Does plsql_optimize_level depend on differently each procedure or function

    973995
      Does plsql_optimize_level depend on differently each procedure or function?

      For example: when I run
      select * from user_plsql_object_settings;
      some of objects plsql_optimize_level is 2 and some of thme are 0. Aren't they supposed to be the same for a spesific user?

      I thought it was a global thing.

      Thanks in advance.
        • 1. Re: Does plsql_optimize_level depend on differently each procedure or function
          rp0428
          >
          Does plsql_optimize_level depend on differently each procedure or function?
          . . .
          some of objects plsql_optimize_level is 2 and some of thme are 0. Aren't they supposed to be the same for a spesific user?
          >
          Depends on who compiled them and what the setting was when they did that.

          See the last sentence of the doc
          http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams184.htm
          >
          The value of this parameter is stored persistently with the library unit.
          • 2. Re: Does plsql_optimize_level depend on differently each procedure or function
            973995
            I am trying to find out the for loop optimization in plsql_optimize_level = 2. Because it has been said that when optimization level is set to 2 and if you use for loop then, optimizer fetch the cursor bulk collect limit 100 impilicitly. However, my findings show that notting change, or I did wrong in somewhere???

            I did the following before I start
            alter system set plsql_optimize_level = 2;
            Edited by: 970992 on 26.Mar.2013 15:58
            • 3. Re: Does plsql_optimize_level depend on differently each procedure or function
              rp0428
              >
              I am trying to find out the for loop optimization in plsql_optimize_level = 2. Because it has been said that when optimization level is set to 2 and if you use for loop then, optimizer fetch the cursor bulk collect limit 100 impilicitly. However, my findings show that notting change, or I did wrong in somewhere???
              >
              What does any of that have to do with the question you first ask?

              'it has been said' has no meaning or context. What is 'it' and where was 'it' said?

              Your 'findings'? You didn't post any 'findings'.

              You also said 'optimizer fetch the cursor bulk collect limit 100 implicitly'. A BULK COLLECT is an EXPLICIT fetch, not an implicit one and it will FETCH everything there is or an amount based on the LIMIT you specify.

              Standard cursor FOR loops will now do an implicit fetch of 100 rows rather than just one. Perhaps that is what you are referring to.
              • 4. Re: Does plsql_optimize_level depend on differently each procedure or function
                973995
                First I created the following procedures in order to compare for loop and simple loop
                alter system set plsql_optimize_level = 2;
                
                create or replace
                procedure a1_p
                is
                  cursor my_c is 
                  select * from deneme3 order by 4;
                begin
                  for rec in my_c  
                  loop    
                    null;  
                  end loop;  
                end;
                
                
                create or replace
                procedure a2_p
                is
                  cursor my_c2 is 
                  select * from deneme3 order by 5;
                  rec my_c2%rowtype;
                begin
                
                  open my_c2;
                  loop      
                    fetch my_c2 into rec;      
                    exit when my_c2%notfound;
                  end loop;
                  close my_c2;   
                
                end;
                Then I execute them
                set serveroutput on;
                begin 
                a1_p;
                a2_p;
                end;
                After I check their fetches
                select SQL_TEXT, FETCHES from v$sql where lower(sql_text) like 'select%deneme3%order%';
                
                
                ------------------------------------------
                
                SELECT * FROM DENEME3 ORDER BY 4     299601
                SELECT * FROM DENEME3 ORDER BY 5     299601
                As you can see the findings are the same, I am trying to solve this. Because in the following article is is said that for loop used impilicit bulk collect

                >
                The default optimization level for an Oracle instance is 2. At this level of optimization, cursor for loops are optimized to run at a level of performance similar to a BULK COLLECT with a limit of 100. While the underlying implementation of this optimization is not specifically a BULK COLLECT, bulk fetching does occur, with 100 rows being retrieved with each fetch.

                Note: this optimizing feature is not well documented. You can read about it on Ask Tom. Steven also covers it in his "Best of Oracle PL/SQL" training available here. And while it is possible that Oracle will in some future version change this limit value of 100, it seems unlikely.

                >

                link: http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:2311493007214::NO:659:P659_COMP_EVENT_ID,P659_QUIZ_ID,P659_QUESTION_ID:124018,,3422&cs=1488975435170D71CE3D4B5D8E115D92F
                • 5. Re: Does plsql_optimize_level depend on differently each procedure or function
                  rp0428
                  works for me
                  SQL_TEXT     FETCHES
                  select SQL_TEXT, FETCHES from v$sql where lower(sql_text) like 'select%all_objects%order%'      2
                  SELECT * FROM ALL_OBJECTS ORDER BY 5     111276
                  SELECT * FROM ALL_OBJECTS ORDER BY 4     1114
                  • 6. Re: Does plsql_optimize_level depend on differently each procedure or function
                    973995
                    but, it doesn'T work for me and I am just wondering where did I do wrong? maybe there is something wrong with plsql_optimization_level? do you have any idea?

                    thanks
                    • 7. Re: Does plsql_optimize_level depend on differently each procedure or function
                      973995
                      How can I get the value of plsql_optimize_level?

                      select * from user_plsql_object_settings; ?
                      • 8. Re: Does plsql_optimize_level depend on differently each procedure or function
                        973995
                        Does anybody know? I think I have problem with plsql_optimize_level setting.
                        • 9. Re: Does plsql_optimize_level depend on differently each procedure or function
                          rp0428
                          Query it from V$PARAMETER
                          select * from V$parameter
                          where name = 'plsql_optimize_level'
                          You haven't posted your 4 digit Oracle version but '2' is the default and it worked for me in vanilla 11.2.0.1.0
                          • 10. Re: Does plsql_optimize_level depend on differently each procedure or function
                            973995
                            select * from v$version;
                            
                            ----------------------------------------------------------
                            
                            Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
                            PL/SQL Release 11.1.0.6.0 - Production
                            "CORE     11.1.0.6.0     Production"
                            TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
                            NLSRTL Version 11.1.0.6.0 - Production
                            select name, value from V$parameter where name = 'plsql_optimize_level';
                            
                            -----------------------------------
                            
                            plsql_optimize_level     0
                            So as it can be seen, it is 0, I did change though. I don't understand why? What should I do by the way?

                            Thanks in advance
                            • 11. Re: Does plsql_optimize_level depend on differently each procedure or function
                              rp0428
                              >
                              So as it can be seen, it is 0, I did change though. I don't understand why? What should I do by the way?
                              >
                              The default value is '2' so someone changed yours to '0'.

                              1. Change it back to '2'.
                              2. Confirm that is really is '2'
                              3. Rerun the test.
                              4. Post the results.
                              • 12. Re: Does plsql_optimize_level depend on differently each procedure or function
                                973995
                                >
                                The default value is '2' so someone changed yours to '0'.

                                1. Change it back to '2'.
                                2. Confirm that is really is '2'
                                3. Rerun the test.
                                4. Post the results.
                                >

                                I do understand you. However, I am confused due to reaults of user_plsql_object_settıngs table. Some of the objects plsl_optimize_level is set 2. However, some of them are equal 0. How come is that possible? Isn't is global? I though If I run the following than all database use the plsql_optimize_level = 2

                                alter system set plsql_optimize_level = 2;

                                Somebody tell me please?
                                • 13. Re: Does plsql_optimize_level depend on differently each procedure or function
                                  973995
                                  I did everything bu the result still same :(
                                  create or replace
                                  procedure a1_p
                                  is
                                    cursor my_c is 
                                    select * from deneme3 order by 4;
                                  begin
                                    for rec in my_c  
                                    loop    
                                      null;  
                                    end loop;  
                                  end;
                                  
                                  create or replace
                                  procedure a2_p
                                  is
                                    cursor my_c2 is 
                                    select * from deneme3 order by 5;
                                    rec my_c2%rowtype;
                                  begin
                                  
                                    open my_c2;
                                    loop      
                                      fetch my_c2 into rec;      
                                      exit when my_c2%notfound;
                                    end loop;
                                    close my_c2;   
                                  
                                  end;
                                  
                                  alter system set plsql_optimize_level = 2;
                                  
                                  alter procedure a1_p compile;
                                  
                                  alter procedure a2_p compile;
                                  
                                  set serveroutput on;
                                  begin
                                  a1_p;
                                  a2_p;
                                  end;
                                  
                                  
                                  select sql_text, fetches from v$sql where lower(sql_text) like 'select%deneme3%order%';
                                  
                                  -----------------------------
                                  SELECT * FROM DENEME3 ORDER BY 4     399468
                                  SELECT * FROM DENEME3 ORDER BY 5     399468
                                  As you can see the results stil same :((
                                  • 14. Re: Does plsql_optimize_level depend on differently each procedure or function
                                    rp0428
                                    >
                                    I did everything bu the result still same
                                    >
                                    No - you didn't do everything.

                                    What you posted shows you created the procedures BEFORE you set the LEVEL. And you didn't post the confirmation that level was really '2' for the system AND for those two procedures.

                                    1. drop the procedures
                                    2. set the level to '2'
                                    3. create the procedures
                                    4. rerun the test
                                    1 2 Previous Next