Results 1 to 7 of 7

Thread: Profiler

  1. #1
    Join Date
    Sep 2003
    Location
    Sacramento, CA
    Posts
    28

    Unanswered: Profiler

    I am new to Oracle and trying to understand and support a new software application.
    I want to know if there is a tool that when I run the software application and different calls are being made to stored procedures that I can see real-time what queries and stored procedure calls are being made.

    For example I know on a certain screen in the application that a stored procedure is being called in order to load the contents of a drop-down box. But I don't know what stored procedure is being called. A tool like I described would help me narrow down what SP is being called.

    Any help would be much appreciated. Hopefully there is a tool and it won't cost me anything.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Not in realtime; but after the fact.
    enabling SQL_TRACE will reveal all the SQL statements in order of execution.
    Once you find the SQL which obtains the data to populate the drop down box,
    you can find in which procedure that statement resides.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2003
    Location
    Sacramento, CA
    Posts
    28
    Is there a quick way to enable SQL_TRACE? Once I enable it, where will I see the SQL Statement executing? In Toad?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALTER SESSION SET SQL_TRACE=TRUE;

    above produces a trace file which needs to be processed using tkprof

    Oracle Database Search Results: tkprof
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2003
    Location
    Sacramento, CA
    Posts
    28
    Okay I feel kinda stupid here. I went in Toad and executed the ALTER SESSION SET SQL_TRACE=TRUE.

    According to the tkprof documentation that alter session was suppose to create a trace file and that filename is an input parameter to the tkprof command. I don't know what the filename is that was created.

    Also, the ALTER SESSION command sounds like it is only tracing my session in Toad, not the session that the web application is using?

    I think I am way off from where I want to be. I think I have been spoiled for years using SQL Server's Profiler. All I had to do was open the profile application and specify a database instance and it would give me
    real time query and stored procedure executions. No commands to type just log in and thats it. I could also filter down to specific users.
    Last edited by VbMan; 09-08-14 at 13:00.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    For example I know on a certain screen in the application that a stored procedure is being called in order to load the contents of a drop-down box. But I don't know what stored procedure is being called. A tool like I described would help me narrow down what SP is being called.
    If you have the standard edition you can only query V$SQL to see which statements have been parsed and execute recently.
    Something like:
    Code:
    SELECT (elapsed_time / 1000000) "Total Seconds",
             executions "Execs",
             rows_processed as "Rows",
             case 
               when nvl(executions,0) = 0 then null
               else round((elapsed_time/1000)/executions)
             end as "ms/exec",
             s.sql_fulltext,
             s.last_load_time,
             s.last_active_time
      from v$sql s
        join all_users au on au.user_id = s.parsing_user_id 
      where au.username = 'YOUR_APP_USER'
      and last_active_time >= trunc(sysdate) -- only statements from today
      order by last_active_time desc;
    Running
    Code:
    alter system flush shared_pool;
    will "purge" the V$SQL view (but will also slow down your application because all statements will have to be hard-parsed again!). If you do that before starting your application you get a pretty complete picture on what you application does. This assumes that nothing else is going on in the database of course.


    If you have the Enterprise Edition (or to be more precise: the license for the "Oracle Diagnostics Pack") you can query the ASH views for the statements that have been executed during a specific interval. Something like:

    Code:
    select ash.sample_time, 
           sql.sql_text
    from v$active_session_history ash
      join all_users au on au.user_id = ash.user_id
      left join v$sql sql on sql.sql_id = ash.sql_id 
    where au.username = 'YOUR_APP_USER'
    and ash.sample_time >= current_timestamp - interval '1' hour
    order by ash.sql_exec_start desc;
    The drawback of that is that ASH information is sampled and statements that run really quick might not even show up there (you need to use V$SQL then as shown above)

    But again: this requires the diagnostic pack license (which means a lot of money!)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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