Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Unanswered: Oracle SQLTRACE and TKPROF????

    We are having a problem with one of our processes running for 40 minutes in production, when it was running in 3 minutes just 3 months ago. In a test database it runs alot longer. The process I am referring to is an Oracle 6i form that calls many package bodies (oracle 8i). I would like to have some sort of trace set up while I am running the process so that we can see if there is a process that is running for a long time. I think we could do this in TKPROF, but our DBA says we have to modify the form to add instructions to start the trace. Is this true, or can you start the trace on the database and specify which process you want to trace? If so, how do you do this? I don't know alot about TKPROF, and would appreciate any help I can get!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The process I am referring to is an Oracle 6i form that calls many package bodies (oracle 8i).
    Has 8i been supported any time this century?
    TKPROF is a profiler utility which processes a file created after
    ALTER SESSION SET SQL_TRACE=TRUE

    Listen to your DBA. He knows more than you.

    CBO or RBO?

    When was the last time statistics were gathered for all tables & indexes involved in this query?
    Last edited by anacedent; 02-04-08 at 20:56.
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just to add: you might want to read the following document; pay attention to section 3.1.1 - Forms and Reports Developer Specific Measurements.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could also attack it a different way say using the statspack (or Enterprise Manager) to identify long running sql (or sql being executed a lot).

    Alan

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Another option would be to identify the session and use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to set the SQL TRACE without modifying the FORM.

    Also, take a closer look to what anacedent is asking you.

  6. #6
    Join Date
    Jul 2006
    Posts
    8

    Oracle SQLTRACE and TKPROF????

    Thanks for your help! I will try tracing by session and see if that works. Also, in response to your question, we did analyze the tables used by this process, and there was very little change.

Posting Permissions

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