7 Replies Latest reply: Aug 19, 2010 4:32 PM by Charles Hooper RSS

    Query execution is faster - as SYS user vs. application user in 11.1.07

    754844
      In +11.1.0.7+ database, we have noticed execution of an application process as SYS user is faster than the application user. To quantify this execution time was 2 hours as application user and same process when executed as SYS user completed in 1 hour.

      We ran tracing for this process and tkprof output showed follwoing:

      OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS:

      _call          count      cpu       elapsed disk query current rows_
      Execute 3705586 2440.49 *4294.54* 293602 21835223 23300886 3801288 <<< ==== SYS
      Execute 3703285 6356.69 *8576.50* 303175 21785419 23378086 3801291 <<< ==== Application user



      We have SR open with Oracle, but thought if some one knows about this issue would be great.

      Edited by: dhyani on Aug 17, 2010 3:16 PM
        • 1. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
          user503699
          dhyani wrote:
          In +11.1.0.7+ database, we have noticed execution of an application process as SYS user is faster than the application user. To quantify this execution time was 2 hours as application user and same process when executed as SYS user completed in 1 hour.
          Any reason why you are doing this comparison? If I am not mistaken, oracle strictly recommends NOT TO run any custom application code
          as SYS user. SYS is "special" user and is used solely by oracle.
          >
          We have SR open with Oracle, but thought if some one knows about this issue would be great.
          I am almost sure support will tell you not to bother yourself with execution statistics of SYS user. If you want to tune your process (executed as application user), you may want to post relevant details here and people will help.
          • 2. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
            754844
            Thanks for replying. Yes, we know that we should avoid running custom application code as SYS. BTW, this was an accidental finding on our part that our application code ran faster as "SYS" user.

            Yesterday we have provided full information to Oracle support to reproduce the testcase in house. Once done support engineer will file a new bug. So, my hope that some one might know about this bug is now hopeless.

            In any case I will keep this thread updated so some one else can benefit from it.
            • 3. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
              Charles Hooper
              Note that fine-grained access control, if enabled on the various tables, does not apply to the SYS user. It could very well be the case that the execution plan used by SYS is very different from that used by a normal user - take a look at a DBMS_XPLAN, specifically the predicate information section, for the SQL statement when executed by SYS and a normal user.

              There is also a complication that prevents the SYS user from being able to create a consistent export using the classic exp utility:
              http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm
              "SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users."
              The complication was something along the lines of not being able to obtain full table locks.

              There is also the potential issue of secure view merging, see slide 24:
              http://download.oracle.com/otndocs/technology/products/database/oracle11g/upgrade/upgrade11gr1_workshop_part2.pdf

              Charles Hooper
              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              • 4. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
                754844
                Charles,

                Thanks for your suggestion. We tested our application with this parameter - but no change.
                • 5. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
                  Charles Hooper
                  I might have been misunderstood - I was not suggesting that you change any parameters, I was merely listing why you might experience different performance for the SYS user than you would for normal users.

                  In the first paragraph I suggested looking at the execution plan for the SYS user and a normal user - using DBMS_XPLAN.DISPLAY_CURSOR. After executing the query for the SYS user, do the following:
                  SET LINESIZE 150
                  SET TRIMSPOOL ON
                  SET PAGESIZE 1000
                   
                  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +PEEKED_BINDS')); 
                  Repeat the above steps after executing the query using a normal database account. Compare the execution plans - pay close attention to the Predicate Information section of the execution plans.

                  If you intent to post the execution plans here, make certain that you use a { code } tag (without spaces) before and after that section of your post, like this:
                  { code }
                  SQL_ID  9n7zb5y85khvn, child number 0
                  -------------------------------------
                  SELECT SYSDATE FROM DUAL WHERE 1=1 CONNECT BY LEVEL<=5
                   
                  Plan hash value: 3573270583
                   
                  ------------------------------------------------------------------------------
                  | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
                  ------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT              |      |       |     2 (100)|          |
                  |*  1 |  FILTER                       |      |       |            |          |
                  |   2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
                  |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
                  ------------------------------------------------------------------------------
                   
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                     1 - filter(1=1)
                  { code }

                  Charles Hooper
                  Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                  http://hoopercharles.wordpress.com/
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.
                  • 6. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
                    754844
                    Charles,

                    I also misunderstood your response - surprisingly we didn't got different execution plan.

                    Surprisingly, execution plan remains exact same but execution time is not.
                    • 7. Re: Query execution is faster - as SYS user vs. application user in 11.1.07
                      Charles Hooper
                      What about the "Predicate Information" section of the execution plans - were those exactly the same? Did you use AUTOTRACE, EXPLAIN PLAN FOR, or the method that I showed to retrieve the execution plans? Where the SQL_ID and CHILD_NUMBERS displayed exactly the same? Is the STATISTIC_LEVEL set to TYPICAL for both sessions? You can check the setting in SQL*Plus like this:
                      SQL> SHOW PARAMETER STATISTICS_LEVEL
                       
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- -------
                      statistics_level                     string      typical
                      As a demonstration, I will change the value at the session level and then change the setting back:
                      SQL> ALTER SESSION SET STATISTICS_LEVEL='ALL';
                       
                      SQL> SHOW PARAMETER STATISTICS_LEVEL
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- -----
                      statistics_level                     string      ALL
                       
                      SQL> ALTER SESSION SET STATISTICS_LEVEL='TYPICAL';
                      If the above does not show a difference, you will need to capture a 10046 trace at either level 8 or level 12 for both sessions and compare the wait events and the STAT lines in the 10046 trace to see where the additional time is being spent. Reference these two blog articles for the 10046 trace:
                      http://hoopercharles.wordpress.com/2009/12/01/10046-extended-sql-trace-interpretation/
                      http://hoopercharles.wordpress.com/2010/01/26/10046-extended-sql-trace-interpretation-2/

                      Keep in mind that the goal is to not change anything until we understand the problem better. Maybe, just maybe, someone created a table (or view) or two in the SYS schema (or used a table/view name that matched a SYS owned object), in a normal user's schema, or there are synonyms pointing to different tables. So, it probably would be a good idea to verify that the results are exactly the same when the SYS user executes the SQL statement and a normal user executes the SQL statement.

                      Charles Hooper
                      Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                      http://hoopercharles.wordpress.com/
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.