Loading ...
Sorry, an error occurred while loading the content.

Kill Query process in DB Server

Expand Messages
  • Wong, Jane
    There are some long running Queries (via PSQRYSRV) which have already timed out in the Query server. But they are still running within Oracle and use up
    Message 1 of 5 , Apr 2, 2006
    • 0 Attachment
      Kill Query process in DB Server

      There are some long running Queries (via PSQRYSRV) which have already timed out in the Query server. But they are still running within Oracle and use up resources.

      I am implementing a script to catch all those timed out Queries within Oracle and then kill them, but b/c it spawns out another session, the second session wont be timed out till the next 20 mins (the timeout setting) , which means the script wont be able to catch it till another 20mins.

      Is there any way to end those processes within Oracle once they time out in the Query server ?

      Thanks

      Jane Wong

      Database Analyst, Data Infrastructure Management

      Infrastructure and Desktop Management, IT

      The City of Calgary   403.268.8159


      NOTICE -
      This communication is intended ONLY for the use of the person or entity named above and may contain information that is confidential or legally privileged. If you are not the intended recipient named above or a person responsible for delivering messages or communications to the intended recipient, YOU ARE HEREBY NOTIFIED that any use, distribution, or copying of this communication or any of the information contained in it is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and then destroy or delete this communication, or return it to us by mail if requested by us. The City of Calgary thanks you for your attention and cooperation.

    • the dragon
      How about not letting the users write or run long running queries??? peace, clark PSA: Salary Slavery. If you earn a salary, your employer is renting your
      Message 2 of 5 , Apr 4, 2006
      • 0 Attachment
        How about not letting the users write or run long running queries???

        peace,
        clark

        PSA: Salary <> Slavery. If you earn a salary, your employer is renting your
        services for 40 hours a week, not purchasing your soul. Your time is the
        only real finite asset that you have, and once used it can never be
        recovered, so don't waste it by giving it away.

        I work to live; I don't live to work.

        "Time is the coin of your life. It is the only coin you have, and only you
        can determine how it will be spent. Be careful lest you let other people
        spend it for you."

        Carl Sandburg
        (1878 - 1967)

        ----Original Message Follows----

        There are some long running Queries (via PSQRYSRV) which have already
        timed out in the Query server. But they are still running within Oracle
        and use up resources.

        I am implementing a script to catch all those timed out Queries within
        Oracle and then kill them, but b/c it spawns out another session, the
        second session won't be timed out till the next 20 mins (the timeout
        setting) , which means the script won't be able to catch it till another
        20mins.

        Is there any way to end those processes within Oracle once they time out
        in the Query server ?

        Thanks

        Jane Wong

        Database Analyst, Data Infrastructure Management

        Infrastructure and Desktop Management, IT

        The City of Calgary 403.268.8159

        _________________________________________________________________
        Is your PC infected? Get a FREE online computer virus scan from McAfee´┐Ż
        Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
      • John Hayes
        I have the same issues, can t these peoplesoft views be converted to the materialized views? Most of these long running queries are based on views, I think...
        Message 3 of 5 , Apr 5, 2006
        • 0 Attachment
          I have the same issues, can't these peoplesoft views
          be converted to the materialized views? Most of these
          long running queries are based on views, I think...

          --- "Wong, Jane" <jane.wong@...> wrote:

          > There are some long running Queries (via PSQRYSRV)
          > which have already
          > timed out in the Query server. But they are still
          > running within Oracle
          > and use up resources.
          >
          > I am implementing a script to catch all those timed
          > out Queries within
          > Oracle and then kill them, but b/c it spawns out
          > another session, the
          > second session won't be timed out till the next 20
          > mins (the timeout
          > setting) , which means the script won't be able to
          > catch it till another
          > 20mins.
          >
          > Is there any way to end those processes within
          > Oracle once they time out
          > in the Query server ?
          >
          > Thanks
          >
          > Jane Wong
          >
          > Database Analyst, Data Infrastructure Management
          >
          > Infrastructure and Desktop Management, IT
          >
          > The City of Calgary 403.268.8159
          >
          >
          > NOTICE -
          > This communication is intended ONLY for the use of
          > the person or entity
          > named above and may contain information that is
          > confidential or legally
          > privileged. If you are not the intended recipient
          > named above or a
          > person responsible for delivering messages or
          > communications to the
          > intended recipient, YOU ARE HEREBY NOTIFIED that any
          > use, distribution,
          > or copying of this communication or any of the
          > information contained in
          > it is strictly prohibited. If you have received this
          > communication in
          > error, please notify us immediately by telephone and
          > then destroy or
          > delete this communication, or return it to us by
          > mail if requested by
          > us. The City of Calgary thanks you for your
          > attention and cooperation.
          >
          >


          __________________________________________________
          Do You Yahoo!?
          Tired of spam? Yahoo! Mail has the best spam protection around
          http://mail.yahoo.com
        • Wong, Jane
          In this case, we can t... we are to resolve this particular Query by removing a col from being selected. By doing that, the Query responds reasonably well. But
          Message 4 of 5 , Apr 5, 2006
          • 0 Attachment
            In this case, we can't... we are to resolve this particular Query by removing a col from being selected. By doing that, the Query responds reasonably well.
             
            But the mv is a good suggestion... I actually tested that with one of our nVision reports (it's a AP drill).  But at the end , I didn't implement it in production - with the concern that it will affect peformance when the vendor and voucher info are editted.
             
            I would like to hear from people who has been using mv in their Psoft production OLTP system.
             
            thanks

            NOTICE -
            This communication is intended ONLY for the use of the person or entity named above and may contain information that is confidential or legally privileged. If you are not the intended recipient named above or a person responsible for delivering messages or communications to the intended recipient, YOU ARE HEREBY NOTIFIED that any use, distribution, or copying of this communication or any of the information contained in it is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and then destroy or delete this communication, or return it to us by mail if requested by us. The City of Calgary thanks you for your attention and cooperation.


            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of John Hayes
            Sent: 2006 April 05 12:11 PM
            To: psftdba@yahoogroups.com
            Subject: Re: PeopleSoft DBA Forum Kill Query process in DB Server

            I have the same issues, can't these peoplesoft views
            be converted to the materialized views?  Most of these
            long running queries are based on views, I think...

            --- "Wong, Jane" <jane.wong@...> wrote:

            > There are some long running
            Queries (via PSQRYSRV)
            > which have already
            > timed out in the Query
            server. But they are still
            > running within Oracle
            > and use up
            resources.
            >
            > I am implementing a script to catch all those
            timed
            > out Queries within
            > Oracle and then kill them, but b/c it
            spawns out
            > another session, the
            > second session won't be timed
            out till the next 20
            > mins (the timeout
            > setting) , which means
            the script won't be able to
            > catch it till another
            > 20mins.
            >
            > Is there any way to end those processes within
            > Oracle
            once they time out
            > in the Query server ?
            >
            >
            Thanks
            >
            > Jane Wong
            >
            > Database Analyst, Data
            Infrastructure Management
            >
            > Infrastructure and Desktop
            Management, IT
            >
            > The City of Calgary  
            403.268.8159
            >
            >
            > NOTICE -
            > This communication is
            intended ONLY for the use of
            > the person or entity
            > named above
            and may contain information that is
            > confidential or legally
            >
            privileged. If you are not the intended recipient
            > named above or
            a
            > person responsible for delivering messages or
            > communications
            to the
            > intended recipient, YOU ARE HEREBY NOTIFIED that any
            > use,
            distribution,
            > or copying of this communication or any of the
            >
            information contained in
            > it is strictly prohibited. If you have received
            this
            > communication in
            > error, please notify us immediately by
            telephone and
            > then destroy or
            > delete this communication, or
            return it to us by
            > mail if requested by
            > us. The City of Calgary
            thanks you for your
            > attention and cooperation.
            >
            >


            __________________________________________________
            Do You Yahoo!?
            Tired of spam?  Yahoo! Mail has the best spam protection around
            http://mail.yahoo.com
          • Tiratto, Neil
            Hello, You may try looking into the SQLNET.EXPIRE_TIME parameter in the SQLNET.ORA file on the database server. The expiration time represents an interval that
            Message 5 of 5 , Apr 13, 2006
            • 0 Attachment

              Hello,

              You may try looking into the SQLNET.EXPIRE_TIME parameter in the SQLNET.ORA file on the database server.

              The expiration time represents an interval that is specified in terms of minutes. At the specified interval, Oracle Net verifies that the

              Connection still exists. If the probe fails, the Net session is terminated.

              I haven’t used this for several years but as I recall it worked as advertised.

               

              Neil Tiratto
              Database Administration Team Lead
              Saint Barnabas Health Care System
              Information Technology & Services Division
              Primary Phone: 732-923-8746
              email: ntiratto@...
               

              -----Original Message-----
              From: Wong, Jane [mailto:jane.wong@...]
              Sent: Wednesday, April 05, 2006 6:57 PM
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Kill Query process in DB Server

               

              In this case, we can't... we are to resolve this particular Query by removing a col from being selected. By doing that, the Query responds reasonably well.

               

              But the mv is a good suggestion... I actually tested that with one of our nVision reports (it's a AP drill).  But at the end , I didn't implement it in production - with the concern that it will affect peformance when the vendor and voucher info are editted.

               

              I would like to hear from people who has been using mv in their Psoft production OLTP system.

               

              thanks

               

              NOTICE -
              This communication is intended ONLY for the use of the person or entity named above and may contain information that is confidential or legally privileged. If you are not the intended recipient named above or a person responsible for delivering messages or communications to the intended recipient, YOU ARE HEREBY NOTIFIED that any use, distribution, or copying of this communication or any of the information contained in it is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone and then destroy or delete this communication, or return it to us by mail if requested by us. The City of Calgary thanks you for your attention and cooperation.


              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of John Hayes
              Sent: 2006 April 05 12:11 PM
              To: psftdba@yahoogroups.com
              Subject: Re: PeopleSoft DBA Forum Kill Query process in DB Server

              I have the same issues, can't these peoplesoft views
              be converted to the materialized views?  Most of these
              long running queries are based on views, I think...

              --- "Wong, Jane" <jane.wong@...> wrote:

              > There are some long running Queries (via PSQRYSRV)
              > which have already
              > timed out in the Query server. But they are still
              > running within Oracle
              > and use up resources.
              >
              > I am implementing a script to catch all those timed
              > out Queries within
              > Oracle and then kill them, but b/c it spawns out
              > another session, the
              > second session won't be timed out till the next 20
              > mins (the timeout
              > setting) , which means the script won't be able to
              > catch it till another
              > 20mins.
              >
              > Is there any way to end those processes within
              > Oracle once they time out
              > in the Query server ?
              >
              > Thanks
              >
              > Jane Wong
              >
              > Database Analyst, Data Infrastructure Management
              >
              > Infrastructure and Desktop Management, IT
              >
              > The City of Calgary   403.268.8159
              >
              >
              > NOTICE -
              > This communication is intended ONLY for the use of
              > the person or entity
              > named above and may contain information that is
              > confidential or legally
              > privileged. If you are not the intended recipient
              > named above or a
              > person responsible for delivering messages or
              > communications to the
              > intended recipient, YOU ARE HEREBY NOTIFIED that any
              > use, distribution,
              > or copying of this communication or any of the
              > information contained in
              > it is strictly prohibited. If you have received this
              > communication in
              > error, please notify us immediately by telephone and
              > then destroy or
              > delete this communication, or return it to us by
              > mail if requested by
              > us. The City of Calgary thanks you for your
              > attention and cooperation.
              >
              >


              __________________________________________________
              Do You Yahoo!?
              Tired of spam?  Yahoo! Mail has the best spam protection around
              http://mail.yahoo.com

            Your message has been successfully submitted and would be delivered to recipients shortly.