6 Replies Latest reply: Aug 29, 2006 5:11 PM by Rob van Wijk RSS

    What causes this recursive SQL to be executed

    Rob van Wijk
      Hello all,

      In our database we see the SQL-statement below getting executed often. Until february this year we see this query getting executed about 5000 times a day, but from then on until now, it gets executed around 100,000 times a day. Since then it is in our top 10 of CPU time consumers.
      select privilege#,level
      from sysauth$
      connect by grantee#=prior privilege# and
             privilege#>0
      start with (grantee#=:1 or
             grantee#=1) and
             privilege#>0
      It looks like a query Oracle uses to find out if a user has the privilege to do something.
      Can anyone relate this query to an activity we should do less often ?

      All hints are welcome.

      Regards,
      Rob.
        • 1. Re: What causes this recursive SQL to be executed
          475664
          Hi, this is obviousely a lookup of privileges in the data dictionary.
          This must be done every time a statement is excuted, again and again, because the privileges can change any time.
          This is like you wanting to rent a car: yuo must show your driver's license every time you try to hire a car, it is not enough to show it once a month and get cars four times a month.
          You have to prove again and again that you have it!

          Is it possible that your application has changed in February?
          =;-)
          Lutz
          • 2. Re: What causes this recursive SQL to be executed
            277993
            Oracle uses sysauth$ in so many ways. For instance, to obtain information about which privileges and roles are available to a particular user.

            The increase in execution therefore may relate to increased user base, number of logon attempts and increase in the use of system and object privileges.

            It may be normal to have it at the 10 tp of CPU consumers, but the seriourness depends on how much CPU does it use. If you think it is in excess, I would suggest you check with Oracle Support.

            What has changed in the database from Feb 06 (Upgrades? Patches? Database Community Usage? etc)
            • 3. Re: What causes this recursive SQL to be executed
              Rob van Wijk
              Lutz,

              Thanks for your answer.

              The application changes every week a little bit and once per quarter a little bit more (weekpatches and releases). The database handles far more SQL-statements than the number of times this recursive SQL gets executed, so the analogy with the driver's license doesn't quite hold up. I'm looking for the feature that got introduced in february that causes this spectacular increase.

              Regards,
              Rob.
              • 4. Re: What causes this recursive SQL to be executed
                Rob van Wijk
                Ugonic,

                Thanks for your answer.
                The increase in execution therefore may relate to
                increased user base, number of logon attempts and
                increase in the use of system and object privileges.
                Our user base has indeed increased, but only by a small percentage and it was certainly not a "big bang" scenario. So I don't think the first two are applicable. The third is obviously true.
                It may be normal to have it at the 10 tp of CPU
                consumers, but the seriourness depends on how much
                CPU does it use. If you think it is in excess, I
                would suggest you check with Oracle Support.
                I will do that if this thread does not lead to an explanation.
                What has changed in the database from Feb 06
                (Upgrades? Patches? Database Community Usage? etc)
                As I described above and in answer to Lutz.

                Regards,
                Rob.
                • 5. Re: What causes this recursive SQL to be executed
                  John Spencer
                  Rob:

                  That statement is generally run when a user logs onto the database to gather all of the privileges that the user has. Have you changed something in your application to generate more connections. For example did you change something that went

                  connect
                  sql statement
                  sql statement
                  sql statement
                  disconnect

                  to something like:

                  connect
                  sql statement
                  disconnect
                  connect
                  sql statement
                  disconnect.

                  HTH
                  John
                  • 6. Re: What causes this recursive SQL to be executed
                    Rob van Wijk
                    Thanks John.

                    I will investigate the application with this in mind.

                    Regards,
                    Rob.