Capturing all the SQLs ran yesterday
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Capturing all the SQLs ran yesterday

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Hi,
    One of my customers is complaining that their Cold-Fusion application sometimes gets really really slow and they have some idea that it's because of the users running some long, exhaustive queries. He wants to know if I can capture the SQLs for him. The SQLs are generated dynamically so there is no way of knowing it at the application level.

    I know v$sql will show the SQLs in the Shared SQL area. Explain_Plan will also show similar information but this type of information is available only when the SQL is running not a few hours later when customer calls and complains about performance.

    Is there a way to log all the SQLs generated with the user information? This needs to be done at the database level ?
    I know this will generate a *lot* of information but I want to give it a shot.

    Thanks,
    Rajeev Suri

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    enable sql_trace=true in init.ora
    OR match OS pid of long running connection with Oracle sid and username and find the sql.
    thanks
    GP

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    A way to do it that might not generate such a large trace file :

    1) find the users's session in V$SESSION, get the SID and serial#

    2) execute the following :
    exec dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);

    3) to stop tracing - its the same command except with FALSE as the 3rd parameter.

    no matter which way you trace, you might want to set the MAX_DUMP_FILE_SIZE parameter. This parameter determines how big the dump file is allowed to grow...

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Thanks guys
    The problem with sql_trace is that --
    1. It does not capture the time when the sql ran and the user who ran it.
    2. It also generates too much information while my customer wants only the SQLs so that he can decipher the culprits.

    I heard that DBArtisan is a good tool for this so got an Evaluation copy and am still trying it. So far it seems that it captures the SQLs DBArtisan sends to Oracle to generate the actual SQLs and statistics but it does not Log the actual SQLs.

    For example when you select a process it shows you the actual SQL on the screen (select col1, col2 from customer where col1 > 5) but in the Log file it captures --

    SELECT T.SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE S.SID = 23 AND S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE

    I think that defeats the purpose of logging because what I wanted to log was the actual SQL.

    I will appreciate if anybody has more suggestions.

    Thanks,

    Rajeev Suri

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    When the system is running slow, run the following the query to capture the SQL statement and then do EXPLAIN PLAN.

    Replace SCOTT with your user name.

    select sql_text from sys.v$sqltext_with_newlines
    where (address, hash_value ) in
    ( select sql_address, sql_hash_value
    from v$session where username in ('SCOTT')
    )
    order by address, piece;


Posting Permissions

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



Click Here to Expand Forum to Full Width