Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: Performance Tuning..

    Hello Oracle DBAs,
    We are working on HPUX11.0.Currently,the system becomes very slow when certain oracle processes are running.(oracle 8.1.6)
    When the database server settings are checked throughly they are fine.
    When I check the snapshots of statspack at different time periods found that the problem is at the application end.So,we tracked the SQL statements which needs fine tuning.(From statspack report and other scripts)
    But..
    How to identify to which program(or file) these SQL statements belongs to.
    How to replace the current sql statment with the fine tuned version sql statment.
    Could Anybody please helpme to resolve this.
    thanks in advance.
    sridhar

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Finding the "bad" SQL is a BIG plus ... Now, you can rewrite it (changing the driving table, or proper joins, or add/remove indexes, etc...) ... Then ask which idiot programmer wrote it ... If you can find this code actually running, v$session will have the program that is currently issuing the statement ...

    HTH
    Gregg

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Also v$sqlarea has the parsing_user_id which should tell you which user executed the sql. Hopefully all your apps dont login using the same username : )

    Alan

  4. #4
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Thanks..

    Quote Originally Posted by gbrabham
    Finding the "bad" SQL is a BIG plus ... Now, you can rewrite it (changing the driving table, or proper joins, or add/remove indexes, etc...) ... Then ask which idiot programmer wrote it ... If you can find this code actually running, v$session will have the program that is currently issuing the statement ...

    HTH
    Gregg
    I found the sql statements using the statspack and few other scripts.but those sql statments are given from the processes currently running.so..
    1)Is there any way that I can find all(that are active and inactive) the sql statments that are causing poor performance.
    2)there is no chance left to take the help of devlopers who develped it.,will the v$session gives the program name? like if the sql statment is inside a jsp program will it give the jsp filename.

    Please help me resolving this.
    Thanking you.
    sri
    3)

  5. #5
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Thanks..

    Quote Originally Posted by AlanP
    Also v$sqlarea has the parsing_user_id which should tell you which user executed the sql. Hopefully all your apps dont login using the same username : )

    Alan
    Unfortunately one user name will be used by multiple users.
    I need to find out the program(or the file name) which contains the sql statments which Iam trying to tune.
    Please reply me.
    Thanking you.
    sri

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Well, you are the one working there, not us.
    If you can't figure out where the statements are coming from then
    you are up the creek.

    v$session can tell you a lot of information

    also, if you match the statspack hash value to v$sqlarea you might be able to gain
    more information.

    anyways, how many applications are connecting to the db??????
    can't you do a search through the build to find the queries???
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Feb 2004
    Posts
    93

    Red face Script used to run the statspack?

    I am using the spcreate.sql which is sitting in the oracle/ora81/database/ directory. I am logging in as sysdba but receiving the following;

    SQL> set echo off;
    Creating Package STATSPACK...
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0641: "SHOW ERRORS" requires connection to server
    Creating Package Body STATSPACK...
    SP2-0640: Not connected
    SP2-0640: Not connected
    SP2-0641: "SHOW ERRORS" requires connection to server

    any ideas
    ?
    cheers
    E
    Cheers
    Etravels

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •