Home » Developer & Programmer » Forms » To Generate Trace File (Oracle 10g, Forms 10g)
| To Generate Trace File [message #362834] |
Thu, 04 December 2008 04:18  |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Hi,
We oftenly facing performance issues in our application. So I would like to generate trace file for the application.
For doing this does the form allow me to generate trace file for the application.
If this is possible kindly share your views.
Thanks in advance.
Regards,
Sathya
|
|
|
|
|
|
| Re: To Generate Trace File [message #363112 is a reply to message #363049] |
Fri, 05 December 2008 02:35  |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Hi,
Thanks for your suggestion. I have gone through tkprof and i have some doubts regarding this.
1. My requirement is i would like to generate a trace file for entire d2k application and i would like to have each form name in that trace file.
2. For example user 'A' has logged into the application, now i want to generate the trace file for user 'A', for the entire transactions done during user 'A' session including with queries.
3. How can i get user 'A' session information. to get session information i should query v$session but i don't have dba priviliges.
4. without dba priviliges can i generate trace files
Kindly give more explanation on this..
Thanks
Regards,
Sathya
|
|
|
|
|
|
| Re: To Generate Trace File [message #365359 is a reply to message #362834] |
Thu, 11 December 2008 13:15 |
subhra_88
Messages: 14
Registered: April 2007
Location: Bangalore
|
Junior Member |
|
|
I remember tracing a forms session using the following in the forms start up trigger.
forms_ddl('alter session set events ''10046 trace name context forever, level 8''');
this generates trace files in the database udump directory. But this was only for testing the performance. Hope it helps.
|
|
|
|
| Re: To Generate Trace File [message #366231 is a reply to message #365359] |
Fri, 12 December 2008 03:45 |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Well, I got few solutions.
I will explain in brief
To generate trace files for your own session
------------------------------------------------
alter session set sql_trace=True
/
alter session set timed_statistics=true
/
alter session set max_dump_file_size=unlimited
/
alter session set tracefile_identifier = 'My_File.Trc'
/
alter session set events '10046 trace name context forever, level 12'
/
Do some transactions like select insert anything
Go to command prompt
CMD>tkprof My_File.Trc My_File.Txt
It will generate tkprof files for the trace files.
alter session set sql_trace=false
/
To see where trace files are generated execute following statment.
Select Value from V$Parameter Where Upper(name ) = 'USER_DUMP_DEST'
/
This is to see the trace file name
------------------------------
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
/
Or you can also Use DBMS_SESSION to set trace in your own session
The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:
SQL> -- in your own session using dbms_session
SQL> exec dbms_session.set_sql_trace(true);
PL/SQL procedure successfully completed.
SQL> -- execut some code
SQL> exec dbms_session.set_sql_trace(false);
PL/SQL procedure successfully completed.
** Remember trace files or tkprof files will reside in the server
To generate trace files for other sessions--------------------------------------------
To get session information query v$session view. Following query displays all the sessions
Select V.SID, V.SERIAL#, V.USER#, V.USERNAME, V.PROGRAM
From V$Session V
Where V.USERNAME Is Not Null
/
If you are having oracle 9i as database then you have to use dbms_support package to generate trace files for other sessions.
Dbms_Support package by default it will not be available to get that you need to execute the script which will be available
in $ORACLE_HOME/rdbms/admin/dbmssupp.sql. The base install script is dbmssupp.sql script and is run as sysdba.
DBMS_SUPPORT package is preferred over DBMS_SYSTEM and is fully supported by Oracle.
To execute the Dbms_Support package in other schemas, should have the execute privileges on Dbms_Support.
Start Tracing -- equivalent to level 8 tracing, bind=>true would be equivalent to level 12 tracing
SQL> execute sys.dbms_support.start_trace_in_session(&&SID, &&SERIAL, waits=>true, binds=>false); .
Stop Tracing SQL> execute sys.dbms_support.stop_trace_in_session(&&SID, &&SERIAL);
For Example
----------
-- All versions, requires DBMS_SUPPORT package to be loaded.
SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;
SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);
New tracing methods in Oracle 10g - DBMS_MONITOR
Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.
The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.
let's now take a look at some of the features of this package.
Setting trace with DBMS_MONITOR using SID / SERIAL#
Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# - we will use SCOTT connected through SQL*Plus as our sample session:
SQL> select s.sid,s.serial#,s.username
2 from v$session s, v$process p
3 where s.paddr=p.addr
SQL> /
...
SID SERIAL# USERNAME
---------- ---------- ------------------------------
248 153 SCOTT
258 61 DBSNMP
251 418 SYSMAN
255 961 SYS
249 215
27 rows selected.
SQL>
OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:
SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);
PL/SQL procedure successfully completed.
SQL> -- execute some sql
SQL> -- in the other session
SQL> -- turn trace off
SQL> exec dbms_monitor.session_trace_disable(248,153);
PL/SQL procedure successfully completed.
SQL>
Setting trace at the session level using DBMS_MONITOR
The same procedures can be used to set trace for the session by omitting the serial#. This is demonstrated next:
SQL> exec dbms_monitor.session_trace_enable(248);
PL/SQL procedure successfully completed.
SQL> -- execute some sql in the other session
SQL> -- turn off trace
SQL> exec dbms_monitor.session_trace_disable(248);
PL/SQL procedure successfully completed.
SQL> -- or you can turn it on with
SQL> exec dbms_monitor.session_trace_enable(248,null);
PL/SQL procedure successfully completed.
SQL> -- turn off again with:
SQL> exec dbms_monitor.session_trace_disable(248,null);
PL/SQL procedure successfully completed.
SQL>
Setting trace for the current session using DBMS_MONITOR
Setting trace for the current user session is done by leaving out the SID and SERIAL# altogether by setting them to NULL. Here is an example:
SQL> -- trace the current session
SQL> exec dbms_monitor.session_trace_enable(null,null);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- turn it off again
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- to get waits and binds do
SQL> exec dbms_monitor.session_trace_enable(null,null,true,true);
PL/SQL procedure successfully completed.
SQL> -- execute some code
SQL> -- then turn off tracec
SQL> exec dbms_monitor.session_trace_disable(null,null);
PL/SQL procedure successfully completed.
SQL> -- or turn it on like this
SQL> exec dbms_monitor.session_trace_enable();
PL/SQL procedure successfully completed.
SQL> -- execute some SQL and then turn off trace
SQL> exec dbms_monitor.session_trace_disable();
PL/SQL procedure successfully completed.
SQL>
That completes some of the ways to use DBMS_MONITOR to set trace using SID, SERIAL#, or at the session level or for the current session.
Set trace using a client identifier
Tracing using the client identifier allows trace to be set across multiple sessions as many Oracle shadow processes can work on behalf of one client. Also trace is persistent across all instances and restarts. First we need to see how the client identifier is set. This can be done using the DBMS_SESSION package as follows:
SQL> exec dbms_session.set_identifier('pete id');
PL/SQL procedure successfully completed.
SQL>
We can check now for a specific identifier in the V$SESSION view with the client_identifier column.
SQL> select s.username,s.client_identifier
2 from v$session s,v$process p
3 where s.paddr=p.addr
4 and client_identifier is not null;
USERNAME
------------------------------
CLIENT_IDENTIFIER
----------------------------------------------------------------
SCOTT
pete id
OK, now we can use this information to set trace for this client identifier as follows:
SQL> exec dbms_monitor.client_id_trace_enable('pete id',true,false);
PL/SQL procedure successfully completed.
SQL> -- wait for the client session to do something
SQL> -- turn off trace as follows:
SQL> exec dbms_monitor.client_id_trace_disable('pete id');
PL/SQL procedure successfully completed.
SQL>
That was quite easy!. next let's look at setting trace at the service, module action levels.
Setting trace for service/module/action with DBMS_MONITOR
This method of setting trace acts hierarchically. The first level is that trace is set globally for the whole database (all instances) You can override this by setting an instance name in the call to turn on trace. For this example as I am on a single instance database I will leave this parameter at its default. There are three levels to the hierarchy. If we set ACTION to NULL then all actions for the module and service are traced. The next level, if we set MODULE to NULL then all actions for all modules for the specified service name are traced. The trace will be collected into multiple trace files and the new tool trcsess must be used to collate all the trace files into one usable file.
The service name can be set using the package DBMS_SERVICE and the procedure CREATE_SERVICE. Here is an example:
SQL> exec dbms_service.create_service('Test Service','test network');
PL/SQL procedure successfully completed.
SQL> -- it can be deleted with
SQL> exec dbms_service.delete_service('Test Service');
PL/SQL procedure successfully completed.
SQL>
The service name can quite often be set already by the tool. It could be used to group together a set of programs / modules that perform some business task. Next let's see how the module and actions can be set.
SQL> -- set action
SQL> exec dbms_application_info.set_action('PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- set the module
SQL> exec dbms_application_info.set_module('ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL>
To view the relevant service names, modules and actions for sessions in the database you can use the v$SESSION view as follows:
SQL> col service_name for a15 wrapped
SQL> col username for a15 wrapped
SQL> col module for a15 wrapped
SQL> col action for a15 wrapped
SQL> select s.username,s.service_name,s.module,s.action
2 from v$session s,v$process p
3 where s.paddr=p.addr;
...
USERNAME SERVICE_NAME MODULE ACTION
--------------- --------------- --------------- ---------------
SYSMAN SANS
SYSMAN SANS OEM.SystemPool
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)
SYS$USERS
SYS SANS ACCOUNTS PAYMENT
SCOTT SANS SQL*Plus
...
29 rows selected.
SQL>
As we deleted our sample service name set up with DBMS_SERVICE.CREATE_SERVICE we will just use the default value SANS inserted by Oracle in our test case. Let's test some of the methods of setting trace with this functionality.
SQL> -- set trace for all modules and actions for SANS service name
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS',DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS');
PL/SQL procedure successfully completed.
SQL> -- now trace all actions for service SANS and module accounts
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- now turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS');
PL/SQL procedure successfully completed.
SQL> -- finally test service SANS, module ACCOUNTS and action PAYMENT
SQL> exec dbms_monitor.serv_mod_act_trace_enable('SANS','ACCOUNTS','PAYMENT',TRUE,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> -- turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable('SANS','ACCOUNTS','PAYMENT');
PL/SQL procedure successfully completed.
SQL> -- you can turn on or off binds and waits as well or use the waits=>true
SQL> -- syntax instead.
SQL>
OK, that wraps up the new procedures in 10g that can be used to turn on trace in different ways to capture true end to end trace for multi-tier applications. You should also be aware that DBMS_MONITOR also provides procedures to enable statistic gathering at the same levels of client identifier and service name/module/action. These statistics are stored and can then be accessed by selecting from V$SERV_MOD_ACT_STATS and V$CLIENT_STATS views. I will not detail those procedures here as this short paper is concentrating on trace only.
|
|
|
|
|
|
| Re: To Generate Trace File [message #376318 is a reply to message #366283] |
Tue, 16 December 2008 23:08 |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Yes buddy, You are right the requirement is to trace d2k applications. And the coding has to reside in form only..
Consider a situation where you have more than one d2k application or else you want to trace your toad or plsql developer sessions and whatever..
To achieve all these let us have one generic form which displays all the sessions, so that you are giving a flexibility to the user and asking him to select the session which he would like to trace..
|
|
|
|
| Re: To Generate Trace File [message #376416 is a reply to message #366283] |
Wed, 17 December 2008 04:41 |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Hi,
I have few queries regarding trace files. when we trace a session related trace files will be stored in user_dump_dest directory.
Consider that we dont have access to server and working as a client to server. My queries are as follows
1. How to identify trace file names as there might be n number of trace files in user_dump_dest location and can we customized name to trace files which is generated for other sessions?
2. is it possible to change user_dump_dest direcory? If yes, is there any possible side effects?
3. How do we generate tkprof files Since this can be done at command prompt or at sql*plus? Can we generate tkprof files from server only or else we can also do the same by using client installation of database?
4. How do we read the trace files from server or tkprof files?
I know we can do this through creating a batch file but i am not getting the implementation.
Thanks in advance
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:51:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
