11 Replies Latest reply on Mar 19, 2014 9:04 AM by user6433292

    How sga and open curors affect use of indexes?

    user6433292

      Hi,

      I know using a oracle function on a column may cause oracle not to used index on the column.

       

      here is my example

       

          create table t1 (c1 varchar2(30),..., cn varchar2(200) primary key t1p1 (c1));

          select * from t1 where ltrim(c1) = '123456'

       

      Index t1p1 is not used in the above select and it is slow. We don't want to create function based index and we don't want to change the statement either due to the complication of the business requirement.

       

      We then do the following

       

      - increased sga_max_size and and sga_target from 8GB to 32 GB.

      - increased open cursors from 1200 to 5000.

       

      The same select then became fast.

       

      My question is: which of the two actions caused the index t1p1 to be used and why?

        • 1. Re: How sga and open curors affect use of indexes?
          Karthick2003

          Did you check the execution plan? I am 100% sure that INDEX will not be used. I would suggest you get SQL Trace before and after changing the SGA and open cursor size and compare the results.

           

          One possibility is that as you have increased the SGA size the Data buffer size would be increased as well. So the number of Physical I/O could be reduced and that could have caused the performance improvement.

           

          But increasing the size of SGA 4 times just to improve the performance of a SQL does not look good. Did your DBA approve it?

          1 person found this helpful
          • 2. Re: How sga and open curors affect use of indexes?
            Sergey Krasnoslobodtsev

            Hi.

            Having increased memory size, you increased the volume of structures of SGA supporting dynamic control.

             

            The increase in SGA in your case hardly is required.At rather bigger volume of buffer_cache too there can be problems.


            Look at the plan, hardly you there will see one of indexed access methods

             

            If you can't make the correction to application for support of purity of data, then you can use the elementary trigger or create the virtual column (trim (c1)) and on it to create an index

            1 person found this helpful
            • 3. Re: How sga and open curors affect use of indexes?
              user6433292

              That is our client production database which is outsourced to a third party for DBA. I can only make recommendations and after the change has been made the performance issue disappeared and they are busy moving on to the more important things to them. I have requested my client for the trace (I don't have control on the the database).

               

              I agree that it may be due to the buffer cache. If so it may not be good.

               

              Just wondering in general if the number of open cursors can influence the use of index, either directly or indirectly?

              1 person found this helpful
              • 4. Re: How sga and open curors affect use of indexes?
                michaelrozar17

                user6433292 wrote:

                 

                Index t1p1 is not used in the above select and it is slow. We don't want to create function based index and we don't want to change the statement either due to the complication of the business requirement.

                 

                If you are using Oracle 11g, then creating a virtual column will be an ideal solution for such situations. So you do not require to increase your memory and could avoid unnecessary bouncing of database.

                See the below test case:

                 

                Connected to:

                Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

                With the Partitioning, Automatic Storage Management, OLAP, Data Mining

                and Real Application Testing options

                 

                SQL> create table t1 (c1 varchar2(30) primary key , cn varchar2(200));

                 

                Table created.

                 

                SQL> insert into t1 values('  123','A')

                  2  /

                 

                1 row created.

                 

                SQL> insert into t1 values(' 5656','B')

                  2  /

                 

                1 row created.

                 

                SQL> insert into t1 values('    4545','C')

                  2  /

                 

                1 row created.

                 

                SQL> COMMIT;

                 

                Commit complete.

                 

                SQL> set autotrace traceonly explain

                SQL> select * from t1 where ltrim(c1) = '123'

                  2  /

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 3617692013

                 

                --------------------------------------------------------------------------

                | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                --------------------------------------------------------------------------

                |   0 | SELECT STATEMENT  |      |     1 |   119 |     3   (0)| 00:00:01 |

                |*  1 |  TABLE ACCESS FULL| T1   |     1 |   119 |     3   (0)| 00:00:01 |

                --------------------------------------------------------------------------

                 

                Predicate Information (identified by operation id):

                ---------------------------------------------------

                 

                   1 - filter(LTRIM("C1")='123')

                 

                Note

                -----

                   - dynamic sampling used for this statement (level=2)

                 

                SQL> --alter table to create virtual column

                SQL> alter table t1 add colvc generated always as(ltrim(c1))

                  2  /

                 

                Table altered.

                 

                SQL> --create an index on the virtual column

                SQL> create index colvc_idx on t1(colvc)

                  2  /

                 

                Index created.

                 

                SQL> select * from t1 where ltrim(c1) = '123'

                  2  /

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 2606357241

                 

                --------------------------------------------------------------------------------

                ---------

                 

                | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

                ime     |

                 

                --------------------------------------------------------------------------------

                ---------

                 

                |   0 | SELECT STATEMENT            |           |     1 |   136 |     2   (0)| 0

                0:00:01 |

                 

                |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 0

                0:00:01 |

                 

                |*  2 |   INDEX RANGE SCAN          | COLVC_IDX |     1 |       |     1   (0)| 0

                0:00:01 |

                 

                -------------------------------------------------------------------------------

                ---------

                 

                Predicate Information (identified by operation id):

                ---------------------------------------------------

                 

                   2 - access("T1"."COLVC"='123')

                 

                Note

                -----

                   - dynamic sampling used for this statement (level=2)

                1 person found this helpful
                • 5. Re: How sga and open curors affect use of indexes?
                  Sergey Krasnoslobodtsev

                  the OPEN_CURSOR parameter defines how many can be open at the same time cursors.

                  • 6. Re: How sga and open curors affect use of indexes?
                    user6433292

                    Hi,

                    I like your virtual column idea. The example is detailed. I would have a try on this.

                     

                    My situation is that the

                    select ...from t1 where trim(col1) = 'bla'

                    is not limited to one table column. In reality the app search component is dynamic. User can choose any table/column combination to perform the search. I could end up to create too many virtue indexes.

                     

                    To complicated the situation a bit. the t1p1 is could be a PK/FK that is used for a join. In such a case do I need to create virtue column/index on both parent and child table?

                     

                    Any way to reduce the number of virtue column/index to be created?

                    • 7. Re: How sga and open curors affect use of indexes?
                      Karthick2003

                      Why create a virtual column and then a index on that, I would rather just create a function based index instead. OP's option of not creating function based index but saying "I like your virtual column idea. The example is detailed. I would have a try on this." does not makes sense to me.

                       

                      To OP, If you can create a virtual column + index why not just a function based index?

                      1 person found this helpful
                      • 8. Re: How sga and open curors affect use of indexes?
                        user6433292

                        You are right. I just realized that to use virtue column index I have to go through the same complication as function based index.

                        1. alter table add column which I have no control over.

                        2. create index with function/virtue column which we hesitate to do because it put a burden on customer to maintain the indexes.

                         

                         

                        More update.

                        I do have a smaller text box that I have control over.

                        On this box the sga is set to 4GB and the open_cursors is set to 4000. Running the same statement on the same amount of data as in production. the explain plan show a full table scan. But the performance is not bad at all.

                        The production server sga was set to 8GB before change. if what you guys say is true that increasing sga is not necessary then the only change that improved the performance was the increase of open cursors.

                         

                        Any idea as to why the number of open cursors can impact the performance positively in my scenario? 

                        • 9. Re: How sga and open curors affect use of indexes?
                          michaelrozar17

                          Karthick - OP said that he don't want to create Function Based Index, so I wanted to give him other available options. May be he must have said I can't create..

                          • 10. Re: How sga and open curors affect use of indexes?
                            Hemant K Chitale

                            No, increasing OPEN_CURSORS did not cause the performance improvement.  Increasing the SGA may well have caused the improvement by caching the table.

                            Note that the difference between the production box and your test box is that the production box may well have many other table and index buffers in the buffer cache, contending for space.

                             

                             

                            Hemant K Chitale

                            1 person found this helpful
                            • 11. Re: How sga and open curors affect use of indexes?
                              user6433292

                              Hermant/All,

                              First of all thank you to ya'll. This is a wonderful community. I got a lot of very helpful insight from you. Appreciate it.

                               

                              Sum it up.

                              1) Increasing number of open cursors does not help performance.

                                   - In my experience, low number of open cursors often cause application to slow down. Increase the number of open cursors to 4000 - 5000 can bring the app back to user-acceptable level. And I would like to know why.

                               

                              2) Increasing sga 4 folds helps the helps the performance due to the increase of buffer cache but may not worth it.

                                  - Can anyone tell me in detail any possible negative impact? the OS is left with another 64GB memory not assigned to Oracle.

                               

                              3) Function based/virtue column index should help.

                                   - My example is too simplified and may be misleading. In reality all tables have 1 to 10 indexes depneding on usability.

                              The search component allows user to pick any table or view and specify any condition they want including joins. If I create function based/virtue column index on one table alone it may not be pick up. For example

                               

                              t1 with PK (cust_code) -- this is parent

                              t2 with PK (cust_code,prod_code)  -- this is child

                              t3 with PK (custt_code,prod_code) -- 1-to-1 to t2

                               

                              The query could be

                              create or replace view my_view

                              select t2.cust_code,....

                                from t1 inner join t2 on t1,cust_code = t2.cust_code

                                           inner join t3 on t2.cust_code = t3.cust_code and t2.prod_code = t3.prod_code

                              /

                              --use of view

                              select * from my_view where ltrim(cust_code) = 'bla';

                               

                              In such a case, the optimizer will choose table scan. Granted.

                              Now creating function/ virtue column based index just on t2 won't help much. I have to create FBI on t1 and t3 accordingly for the join to use it. And this might as well lead to create number of different FBI for each original index with different string functions, e.g ltrim,rtime,trim,substr,instr.... To a large application that is a maintenance nightmare?

                               

                              What is the common/best practice for my scenario? Creating large number of FBIs everywhere?

                               

                              Thanks again.