5 Replies Latest reply on Mar 10, 2009 1:28 PM by 601585

    Different plans for a sql with the rule hint

    654603
      In a 9.2.0.8 64-bit database on Solaris 10 I have a query that accesses a single table with the RULE hint.
      SELECT   /*+ RULE */
               *
          FROM t
         WHERE t_pk >= NVL (RTRIM (:b1), ' '))
      ORDER BY t_pk ASC;
      This table has 30 million rows and its size is 4GB. The column t_pk is the primary key. A plsql package opens a cursor for this sql, fetches one row and closes the cursor. This is a legacy application migrated to Oracle from another DBMS. This logic is used to find if there is a row matching the parameter :b1.

      There are several child cursors for this sql. The strange thing is not all of them have the same execution plan. Here are the plans for two of them:
      Child number:4 
      ----------------------------------------------------------------------------
      | Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
      ----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |             |       |       |       |
      |   1 |  SORT ORDER BY               |             |       |       |       |
      |   2 |   TABLE ACCESS BY INDEX ROWID| T                  |       |       |       |
      |*  3 |    INDEX RANGE SCAN          | PK_T        |       |       |       |
      ----------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         3 - access("T"."T_PK">=NVL(RTRIM(:B1),' '))
       
      Note: rule based optimization
      
      
      Child number:0 
      ---------------------------------------------------------------------------
      | Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
      ---------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |             |       |       |       |
      |   1 |  TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
      |*  2 |   INDEX RANGE SCAN          | PK_T    |       |       |       |
      ---------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         2 - access("T"."T_PK">=NVL(RTRIM(:B1),' '))
       
      Note: rule based optimization
      Consider a :b1 value for which this sql finds 10 million rows. Fetching one row using child 0 with this value completes immediately in zero seconds since it gets the first row from the index and returns it. Using child 6 the same operation lasts for hours since it tries to sort millions of rows before returning the first row to the application.

      v$sql_shared_cursor shows the following for this sql:
      select s.child_number,s.first_load_time,s.last_load_time,c.optimizer_mismatch,auth_check_mismatch,language_mismatch
      from v$sql_shared_cursor c,v$sql s where kglhdpar='000000102E5FCD90'
      and s.child_address=c.address;
      
      CHILD_NUMBER FIRST_LOAD_TIME     LAST_LOAD_TIME      O A L
      ------------ ------------------- ------------------- - - -
                 0 2009-02-23/10:20:45 2009-02-23/10:20:45 N N N
                 1 2009-02-23/10:20:45 2009-02-23/10:21:53 N Y Y
                 2 2009-02-23/10:20:45 2009-02-23/11:09:14 Y N N
                 3 2009-02-23/10:20:45 2009-02-23/11:45:52 Y Y Y
                 4 2009-02-23/10:20:45 2009-02-24/09:51:38 Y Y Y
                 5 2009-02-23/10:20:45 2009-02-26/11:49:46 Y Y Y
                 6 2009-02-23/10:20:45 2009-02-27/13:57:00 Y Y Y
      My question is: why are there two different plans for this sql even if it is hinted to use RBO?

      By the way I know that RBO is an old man that needs to rest and that we need to use CBO and that this logic is not a good way to find if a rows exists but I want to find out what causes this behavior.
        • 1. Re: Different plans for a sql with the rule hint
          Randolf Geist
          YasinBaskan wrote:
          In a 9.2.0.8 64-bit database on Solaris 10 I have a query that accesses a single table with the RULE hint.

          This table has 30 million rows and its size is 4GB. The column t_pk is the primary key. A plsql package opens a cursor for this sql, fetches one row and closes the cursor. This is a legacy application migrated to Oracle from another DBMS. This logic is used to find if there is a row matching the parameter :b1.

          There are several child cursors for this sql. The strange thing is not all of them have the same execution plan. Here are the plans for two of them:

          Consider a :b1 value for which this sql finds 10 million rows. Fetching one row using child 0 with this value completes immediately in zero seconds since it gets the first row from the index and returns it. Using child 6 the same operation lasts for hours since it tries to sort millions of rows before returning the first row to the application.

          v$sql_shared_cursor shows the following for this sql:

          My question is: why are there two different plans for this sql even if it is hinted to use RBO?
          The most obvious explanation for the different behaviour encountered would be the following scenario:

          - T_PK is a CHAR based column (CHAR, VARCHAR, VARCHAR2, etc.)
          - Some of your clients use a NLS client setting that leads to a NLS_SORT session setting different than "binary"

          Therefore some of your clients can't use the index which uses "binary" sorts by default to return the result in the order requested due to the non-binary NLS_SORT session setting.

          The two columns "auth_check_mismatch" and "language_mismatch" show "Y" in V$SQL_SHARED_CURSOR in this case. Don't ask me why the AUTH_CHECK_MISMATCH is 'Y' but in my last test this was the case when the NLS_SORT settings were different. The crucial one is "language_mismatch" that refers to the NLS related session settings.

          If this applies you either need to make sure that your clients don't use different NLS settings, force a "NLS_SORT = binary" setting e.g. using a logon trigger, or find out what NLS_SORT settings are used and create additional language-specific function-based indexes using the NLSSORT function.

          But a lot of your child cursors also seem to have a different "optimizer" environment, as indicated by the "optimizer_mismatch" flag. There are a lot of settings that influence the optimizer environment, so I'm not sure why this is indicated. If it doesn't influence the execution plan then it probably doesn't matter in your particular case.

          Regards,
          Randolf

          Oracle related stuff blog:
          http://oracle-randolf.blogspot.com/

          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
          http://www.sqltools-plusplus.org:7676/
          http://sourceforge.net/projects/sqlt-pp/
          • 2. Re: Different plans for a sql with the rule hint
            654603
            Randolf, thank you very much. I found out that in one of the packages nls_language was changed affecting the sorting behavior. I did not think of that. Thanks.
            • 3. Re: Different plans for a sql with the rule hint
              601585
              Just to add one comment this thread.

              I found that in recent versions of Oracle(10.2.0.4 and 11.1.0.6 confirmed), Oracle does not have multiple child cursors for different nls settings.
              (I can't confirm it is always so. There should be other exceptions when nls setting must be applied)

              I think that Oracle tries to invalidate the cursor as less frequently as possible by enhanced dependency and invalidation mechanism.

              Simple demonstration.

              *10.2.0.1*
              UKJA@ukja102> select * from v$version;
              
              BANNER                                                                                                                  
              ----------------------------------------------------------------                                                        
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                                                        
              PL/SQL Release 10.2.0.1.0 - Production                                                                                  
              CORE     10.2.0.1.0     Production                                                                                              
              TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                                                                 
              NLSRTL Version 10.2.0.1.0 - Production                                                                                  
              
              UKJA@ukja102> create table t1(c1 int, c2 nvarchar2(100));
              
              Table created.
              
              UKJA@ukja102> alter system flush shared_pool;
              
              System altered.
              
              UKJA@ukja102> var b1 number;
              UKJA@ukja102> var b2 varchar2(10);
              UKJA@ukja102> exec :b1 := 1;
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja102> exec :b2 := '0';
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2;
              
              no rows selected
              
              UKJA@ukja102> @shared_cursor 'select /* share_test */%'
              SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2       
              SQL_ID                         = 31z11u1j41a4r                                                                          
              ADDRESS                        = 2B4ABD00                                                                               
              CHILD_ADDRESS                  = 29EB8F84                                                                               
              CHILD_NUMBER                   = 0                                                                                      
              --------------------------------------------------                                                                      
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja102> --Change NLS setting
              UKJA@ukja102> alter session set nls_sort = 'KOREAN_M';
              
              Session altered.
              
              UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2;
              
              no rows selected
              
              UKJA@ukja102> -- Mismatch in language mode and authentication
              UKJA@ukja102> @shared_cursor 'select /* share_test */%'
              SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2       
              SQL_ID                         = 31z11u1j41a4r                                                                          
              ADDRESS                        = 2B4ABD00                                                                               
              CHILD_ADDRESS                  = 29EB8F84                                                                               
              CHILD_NUMBER                   = 0                                                                                      
              --------------------------------------------------                                                                      
              SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2       
              SQL_ID                         = 31z11u1j41a4r                                                                          
              ADDRESS                        = 2B4ABD00                                                                               
              CHILD_ADDRESS                  = 29F9613C                                                                               
              CHILD_NUMBER                   = 1                                                                                      
              AUTH_CHECK_MISMATCH            = Y                                                                                      
              LANGUAGE_MISMATCH              = Y                                                                                      
              --------------------------------------------------                                                                      
              
              PL/SQL procedure successfully completed.
              *11.1.0.6*
              UKJA@ukja116> select * from v$version;
              
              BANNER                                                                                                                  
              --------------------------------------------------------------------------------                                        
              Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production                                                  
              PL/SQL Release 11.1.0.6.0 - Production                                                                                  
              CORE     11.1.0.6.0     Production                                                                                              
              TNS for 32-bit Windows: Version 11.1.0.6.0 - Production                                                                 
              NLSRTL Version 11.1.0.6.0 - Production                                                                                  
              
              UKJA@ukja116> drop table t1 purge;
              
              Table dropped.
              
              UKJA@ukja116> create table t1(c1 int, c2 nvarchar2(100));
              
              Table created.
              
              UKJA@ukja116> alter system flush shared_pool;
              
              System altered.
              
              UKJA@ukja116> var b1 number;
              UKJA@ukja116> var b2 varchar2(10);
              UKJA@ukja116> 
              UKJA@ukja116> exec :b1 := 1;
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja116> exec :b2 := '0';
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja116> select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2;
              
              no rows selected
              
              UKJA@ukja116> @shared_cursor 'select /* share_test */%'
              SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2       
              SQL_ID                         = 31z11u1j41a4r                                                                          
              ADDRESS                        = 4B45D5E8                                                                               
              CHILD_ADDRESS                  = 50C739E8                                                                               
              CHILD_NUMBER                   = 0                                                                                      
              --------------------------------------------------                                                                      
              
              PL/SQL procedure successfully completed.
              
              UKJA@ukja116> --Change NLS setting
              UKJA@ukja116> alter session set nls_sort = 'KOREAN_M';
              
              Session altered.
              
              UKJA@ukja116> select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2;
              
              no rows selected
              
              UKJA@ukja116> -- Mismatch in language mode and authentication
              UKJA@ukja116> @shared_cursor 'select /* share_test */%'
              SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = rtrim(:b2) order by c2       
              SQL_ID                         = 31z11u1j41a4r                                                                          
              ADDRESS                        = 4B45D5E8                                                                               
              CHILD_ADDRESS                  = 50C739E8                                                                               
              CHILD_NUMBER                   = 0                                                                                      
              --------------------------------------------------                                                                      
              
              PL/SQL procedure successfully completed.
              Visit http://dioncho.wordpress.com/2009/03/05/vsql_shared_cursor/ for more info.


              ================================
              Dion Cho - Oracle Performance Storyteller

              http://dioncho.wordpress.com (english)
              http://ukja.tistory.com (korean)
              ================================
              • 4. Re: Different plans for a sql with the rule hint
                Randolf Geist
                Dion_Cho wrote:
                Just to add one comment this thread.

                I found that in recent versions of Oracle(10.2.0.4 and 11.1.0.6 confirmed), Oracle does not have multiple child cursors for different nls settings.
                (I can't confirm it is always so. There should be other exceptions when nls setting must be applied)

                I think that Oracle tries to invalidate the cursor as less frequently as possible by enhanced dependency and invalidation mechanism.
                Dion,

                that's an interesting point. You'll however get different child cursors if the plans are different, obviously.

                If you change your test case slightly, then you ought to get different child cursors:
                create table t1_nls(c1 int, c2 nvarchar2(100));
                
                create index t1_nls_idx1 on t1_nls(c2, c1);
                
                var b1 number
                
                var b2 varchar2(10)
                
                exec :b1 := 1;
                
                exec :b2 := '0';
                
                alter session set nls_sort = binary;
                
                select /* share_test */ * from t1_nls where c1 = :b1 and c2 like rtrim(:b2) order by c2;
                
                @shared_cursor 'select /* share_test */%'
                
                alter session set nls_sort = german;
                
                select /* share_test */ * from t1_nls where c1 = :b1 and c2 like rtrim(:b2) order by c2;
                
                @shared_cursor 'select /* share_test */%'
                By using the LIKE operator and an index the database potentially needs to sort the data according to the NLS settings, so in one case the index can be used for both access and sort, and in the other case an additional SORT ORDER BY is required. If you use the equal operator, there is only a single value to sort, therefore the NLS settings don't matter for the plan.

                It's interesting to note that the same optimization doesn't apply to literals, at least in 11.1.0.7:
                create table t1_nls(c1 int, c2 nvarchar2(100));
                
                create index t1_nls_idx1 on t1_nls(c2, c1);
                
                alter session set nls_sort = binary;
                
                select /* share_test */ * from t1_nls where c1 = 1 and c2 = '0' order by c2;
                
                @shared_cursor 'select /* share_test */%'
                
                alter session set nls_sort = german;
                
                select /* share_test */ * from t1_nls where c1 = 1 and c2 = '0' order by c2;
                
                @shared_cursor 'select /* share_test */%'
                This gives me two child cursors with the same execution plan...

                Modifying NLS_COMP seems to be treated differently, too:
                create table t1_nls(c1 int, c2 nvarchar2(100));
                
                create index t1_nls_idx1 on t1_nls(c2, c1);
                
                var b1 number
                
                var b2 varchar2(10)
                
                exec :b1 := 1;
                
                exec :b2 := '0';
                
                alter session set nls_comp = binary;
                
                select /* share_test */ * from t1_nls where c1 = :b1 and c2 = rtrim(:b2) order by c2;
                
                @shared_cursor 'select /* share_test */%'
                
                alter session set nls_comp = linguistic;
                
                select /* share_test */ * from t1_nls where c1 = :b1 and c2 = rtrim(:b2) order by c2;
                
                @shared_cursor 'select /* share_test */%'
                Again gives me two child cursors with the same execution plan...

                Regards,
                Randolf

                Oracle related stuff blog:
                http://oracle-randolf.blogspot.com/

                SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                http://www.sqltools-plusplus.org:7676/
                http://sourceforge.net/projects/sqlt-pp/
                • 5. Re: Different plans for a sql with the rule hint
                  601585
                  Randolf.

                  Thanks for the better test cases than mine. :)



                  ================================
                  Dion Cho - Oracle Performance Storyteller

                  http://dioncho.wordpress.com (english)
                  http://ukja.tistory.com (korean)
                  ================================