Home » SQL & PL/SQL » SQL & PL/SQL » Unable to access Tkprof output... (oracle 10g (10.0.0.2))
Unable to access Tkprof output... [message #284222] Thu, 29 November 2007 03:29 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I tried seeing the output generated by tkprof.
I got the directory to go to, using this SQL.
SELECT value
FROM sys.v_$parameter
WHERE name = 'user_dump_dest'

It gave me this output, /opt/oracle/admin/TPDB01A/udump
but I don't think I have an access to this path. Is there any other way to view the contents of this file like any v$views (or some feature in SQL or TOAD).

Also please let me know what to look for in tkprof when we seek to fine tune our query.
Why does this best method is thought to give a better picture regarding the Performance ahead of explain plan ?
Re: Unable to access Tkprof output... [message #284238 is a reply to message #284222] Thu, 29 November 2007 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TKPROF generates its output in the directory you told it not in user_dump_dest.
user_dump_dest contains trace files that is input files for tkprof.

There is no v$ view to see trace files.
You can create an external table for that.

For your last question, have a look at:
How to Identify Performance Problem and Bottleneck

Regards
Michel
Re: Unable to access Tkprof output... [message #288945 is a reply to message #284222] Wed, 19 December 2007 02:03 Go to previous messageGo to next message
jaygopal
Messages: 1
Registered: December 2007
Junior Member
I just created a shell to execute from unix

#!/bin/sh
tkprof $1.trc $1.tkprof sort=exeela,fchela,prsela explain=uid/pwd

It produces the output in local directory and you have complete access to it
Re: Unable to access Tkprof output... [message #288946 is a reply to message #288945] Wed, 19 December 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must:
1/ already be in the trace directory (you use the $1 for input and output)
2/ if you change this, you stiil must be able to access the directory and file and this is not public by default.

Regards
Michel
Re: Unable to access Tkprof output... [message #289151 is a reply to message #288946] Wed, 19 December 2007 21:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I wrote a package a little while ago that was helpful. You will have to get your DBA to run it. It currently only works on Unix (I developed it on SuSe Linux) but could be adapted for Windows.

It creates some objects under the SYSTEM user. Your DBA will be understandably nervous about this. It is possible to create a dedicated Oracle user for the purpose, but I leave this as an exercise for you.
set echo on
conn sys as sysdba

grant select on dba_directories to system;
grant select on v_$session to system;
grant select on v_$process to system;
grant select on v_$parameter to system;

grant create job to system;
grant create external job to system;


conn system
set echo off

COLUMN  user_dump_dest NEW_VALUE user_dump_dest

SELECT  value as user_dump_dest
FROM    v$parameter
WHERE   name = 'user_dump_dest';

CREATE OR REPLACE DIRECTORY user_dump_dest AS '&user_dump_dest';
CREATE OR REPLACE DIRECTORY tkprof_dir AS '/tmp/tkprof';

GRANT READ ON DIRECTORY user_dump_dest TO public;
GRANT READ, WRITE ON DIRECTORY tkprof_dir TO public;



CREATE OR REPLACE TYPE tkprof_file_type AS TABLE OF VARCHAR2(4000);
/


CREATE OR REPLACE PACKAGE utl_tkprof AS

FUNCTION get_trace_file_name(
        pSPID IN INTEGER := NULL
,       pTFI IN VARCHAR2 := NULL
)        RETURN VARCHAR2;

PROCEDURE run_tkprof (
        pTraceFile IN VARCHAR2
,       pTkprofFile IN VARCHAR2
);


FUNCTION tkprof (
        pSPID IN INTEGER := NULL
,       pTFI IN VARCHAR2 := NULL
) RETURN tkprof_file_type PIPELINED;

END utl_tkprof;
/

show errors


--==========================================================================



CREATE OR REPLACE PACKAGE BODY utl_tkprof AS

FUNCTION get_dir_path(
                pName IN VARCHAR2
) RETURN VARCHAR2 IS
        lDir VARCHAR2(100);
BEGIN
        SELECT  directory_path
        INTO    lDir
        FROM    dba_directories
        WHERE   directory_name = pName;

        return(lDir);
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                raise_application_error(
                        -20000
                ,       'Oracle Directory Object ('
                        || pName
                        || ') does not exist.'
                );
END;

----------------------------------------------------------------------------

FUNCTION get_trace_file_name(
        pSPID IN INTEGER := NULL
,       pTFI IN VARCHAR2 := NULL
)        RETURN VARCHAR2 IS

        lSPID           INTEGER         := pSPID;
        lTFI            VARCHAR2(100)   := pTFI;
BEGIN
        IF lSPID IS NULL THEN
                SELECT  p.spid
                INTO    lSPID
                FROM    v$session s
                JOIN    v$process p ON (p.addr = s.paddr)
                WHERE   audsid = sys_context('USERENV','SESSIONID');
        END IF;

        IF lTFI IS NULL THEN
                SELECT  nvl2(value, '_' || value, NULL)
                INTO    lTFI
                FROM    v$parameter
                WHERE   name = 'tracefile_identifier';
        END IF;

        RETURN(
                lower(SYS_CONTEXT('USERENV','DB_NAME'))
                || '_ora_'
                || lSPID
                || lTFI
                || '.trc'
        );

END get_trace_file_name;

----------------------------------------------------------------------------

PROCEDURE run_tkprof (
        pTraceFile IN VARCHAR2
,       pTkprofFile IN VARCHAR2
) IS
        lJobName        VARCHAR2(20);

        sh_script               VARCHAR2(2000) := '
                /bin/echo hi > /tmp/tkprof.log
';
        sh_script               VARCHAR2(2000) := '
                exec 1>>/tmp/tkprof.log 2>%AMPER%1
                echo "=================%TRACEFILE%====================="
                date
                echo "===================================================="
                umask 000
                ORACLE_SID=%DBNAME%
                ORAENV_ASK=NO
                ORACLE_HOME=`grep ^%DBNAME% /etc/oratab | awk -F: ''{print $2}''`
                . $ORACLE_HOME/bin/oraenv
                exec $ORACLE_HOME/bin/tkprof %TRACEFILE% %TKPROFFILE%
';

BEGIN
        lJobName := dbms_scheduler.generate_job_name('TKPROF_');

        DECLARE
                not_a_job       EXCEPTION;
                must_be_a_job   EXCEPTION;
                PRAGMA exception_init(not_a_job, -27476);
                PRAGMA exception_init(must_be_a_job, -27475);
        BEGIN
                dbms_scheduler.drop_job(lJobName);
        EXCEPTION
                WHEN not_a_job THEN
                        NULL;
                WHEN must_be_a_job THEN
                        NULL;
        END;

        --
        -- Replace the placeholders in the shell script
        --
        sh_script := replace(sh_script, '%DBNAME%', SYS_CONTEXT('USERENV', 'DB_NAME'));
        sh_script := replace(sh_script, '%TRACEFILE%', pTraceFile);
        sh_script := replace(sh_script, '%TKPROFFILE%', pTkprofFile);
        sh_script := replace(sh_script, '%AMPER%', chr(38));


        dbms_scheduler.create_job(
                job_name => lJobName
        ,       job_type => 'executable'
        ,       job_action => '/bin/sh'
        ,       number_of_arguments => 2
        ,       start_date => systimestamp + 10000);
        dbms_scheduler.set_job_argument_value(lJobName,1, '-c');
        dbms_scheduler.set_job_argument_value(lJobName,2, sh_script);
        dbms_scheduler.enable(name => lJobName);
        dbms_scheduler.run_job(lJobName);
END run_tkprof;

----------------------------------------------------------------------------

FUNCTION tkprof (
        pSPID IN INTEGER := NULL
,       pTFI IN VARCHAR2 := NULL
) RETURN tkprof_file_type PIPELINED IS
        lFid            utl_file.file_type;
        lTraceFile      VARCHAR2(100);
        lTracePath      VARCHAR2(200);
        lTkProfPath     VARCHAR2(200);
        lLine           VARCHAR2(32767);

        lExists         BOOLEAN;
        lFileLength     NUMBER(10);
        lBlockSize      NUMBER(10);

        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        lTraceFile := get_trace_file_name(pSPID, pTFI);
        lTracePath := get_dir_path('USER_DUMP_DEST') || '/' || lTraceFile;
        lTkProfPath := get_dir_path('TKPROF_DIR') || '/tk_' || lTraceFile;


        run_tkprof(lTracePath, lTkProfPath);
        lFid := utl_file.fopen('TKPROF_DIR', lTkProfPath, 'r', 32767);

        BEGIN
                LOOP
                        utl_file.get_line(lFid, lLine);
                        PIPE ROW(lLine);
                END LOOP;
        EXCEPTION
                WHEN NO_DATA_FOUND THEN
                        NULL;
        END;
        utl_file.fclose(lFid);

        RETURN;
END tkprof;


END utl_tkprof;
/

show errors

CREATE OR REPLACE VIEW tkprof AS SELECT * FROM table(utl_tkprof.tkprof());

GRANT SELECT ON tkprof TO PUBLIC;
GRANT EXECUTE ON utl_tkprof TO PUBLIC;

CREATE PUBLIC SYNONYM tkprof FOR system.tkprof;
CREATE PUBLIC SYNONYM utl_tkprof FOR system.utl_tkprof;


It may require a little tweaking. For example, /bin/sh is actually /usr/bin/sh on some Unix boxes.

When it is installed, you can get TKPROF output in a SQL session as follows:
ALTER SESSION SET SQL_TRACE = TRUE;
<your SQLs here>
ALTER SESSION SET SQL_TRACE = FALSE;
SELECT * FROM TKPROF;


It was working at my last site, but I'm having troubles with external DBMS_SCHEDULER jobs on my current database and am unable to test it. I was fiddling with the code trying to find the problem, so I hope I didn't leave any nasties in there.

If anyone needs to adapt it to get it working, post an update.

You will probably also have to set the Oracle initialisation param
_trace_files_public = true


Ross Leishman
Previous Topic: Weighted sum in group by
Next Topic: Nested Queries
Goto Forum:
  


Current Time: Mon Dec 05 13:00:47 CST 2016

Total time taken to generate the page: 0.10651 seconds