But why the Oracle Manual indicates "alter system set sql_trace = TRUE" if this is not possible ?

Besides, what if there is a batch which logon upon execution, then there is no way to know its SID and SERIAL# in advance. Therefore, other than shutting down the intanace to set the global tracing, I don't see there is any other ways.

If you want to turn on sql_trace on system (instance) level, you need put sql_trace = true in the init.ora file and bounce the database.
Interestingly, when I query v$parameter, Oracle indicates that this parameter can not be changed at session or system level on fly. But everybody know that it can be changed at session level.

Follow up on my previous comment, you can set events 10046 at level 12 which is equivalent of sql trace at system level. However it only affects all new sessions started after you set the events. Also if you want to turn it off, you must bounce the database.

You probably may be able to turn of the 10046 tracing by

alter system set events '10046 trace name context off' ;

Thanks, Prince. It works.

Tom,

I like to turn the sql trace on Production *online* 
database using
dbms_system(<sid>,<serial#>,true/false),
dbms_system.set_ev or dbms_system.set-sql_trace_in_session.

Being a 3-tier system with tuxedo as the middle tier,  
it is was difficult to  choose a process that can be traced. Hence I tried "oradebug"
successfully by
            SQL>oradebug setospid
            SQL>oradebug event 10046 ...
   and trace the shared server process.
   (example ora_s005_<SID> process from v$process)
   The trace file was found in *bdump* directory and
   it had SQL and bind values

Your comments please and any suggestions for better
way to trace

Thanks


 

Tom,

Per URL
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1098633453913,%7Bdedicate%7D <code>

"
It is still 1 for each user process in MTS however in MTS a user process is shared by many users.
"

According to the statement, I inferred that 1 shared server
is dedicated to a user process (session) for all the
required processing of parse, bind, exec, and fetch.

Is this true. Is seems like the fetch is done by one shared
server and the bind is done by another shared server.
Example:
BIND #7, and
EXEC #7 are found in the trace file of "s003"

However the corresponding SQL statement is found
CURSOR #7 of another trace file "s004"

Is this possible or I am missing something

Thanks

Hi, Tom,

Here is the docu: Oracle 8i Desiging and Tuning for
Performance Release 2(8.1.6) A76992-01

Section 6: Using SQL Trace and TKPROF under step 2:
<Quote>
alter system set sql_trace=true/false
<QUOTE END>

Please file a bug on that,

Thanks

Tom,

<quote>
With a sql statement the parse can be done by server1, the execute by server2, the fetch of the first 10 rows by server3, the fetch of the next 10 rows by server4 and so on.
<quote>

What is the rational in switching the shared server in
midstream of fetching rows. Is there a time slice strategy or some other similar strategy involed. Could you explain, when context switching shared servers, will it lead to holding DML locks longer in case of DMLs, which
can cause resource contention problems

Thanks

Tom,

Thanks for your complete, comprehensive and outstanding
information.

Few follow up questions, that I like to clarify.

1. select <column_list> for update;
Performs parse and execute only and two shared servers
are utilized

2. Other queries like: select * from <object_name>;
Performs parse, execute and fetch and hence use >= 3.
During the fetch phase, switching of shared servers is
based upon the settings like, arraysize (in SQL) or
sort_area_size (if sort etc. is performed).... Is
this correct assumption.

Thanks very much

Tom

Thanks very much

BTW, by "sort_area_size", I actually meant the
sort buffer = sort_multiblock_read_count*db_block_buffer,
sort_multiblock_read_count having a dynamic scope for tuning

Hi Tom:

I created a logon trigger to start SQL tracing for a specific user. Here is the code for the trigger: (Note: I get the insufficient error even for system account)

CREATE OR REPLACE TRIGGER Trgr_LiveLink_Logon
After logon on database
Begin                            
            if ( user='LL92') then
            
                execute immediate 'alter session set sql_trace=true';
                
            End if;

            if ( user='SYSTEM') then
            
                execute immediate 'alter session set sql_trace=true';
                
            End if;


End;


The resulting trace file reports insufficient privs. 

Dump file d:\oracle\admin\o9ir2\udump\o9ir2_ora_2396.trc
Thu Sep 04 13:24:56 2003
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: o9ir2

Redo thread mounted by this instance: 1

Oracle process number: 18

Windows thread id: 2396, image: ORACLE.EXE


*** SESSION ID:(10.37) 2003-09-04 13:24:56.000
Skipped error 604 during the execution of SYSTEM.TRGR_LL_LOGON 
*** 2003-09-04 13:24:56.000
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 17



What privs beyond the following do I need ?

SQL>  select * from dba_sys_privs where grantee='LL92';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LL92                           CREATE VIEW                              NO
LL92                           CREATE TABLE                             NO
LL92                           ALTER SESSION                            NO
LL92                           CREATE SESSION                           NO
LL92                           CREATE TRIGGER                           NO
LL92                           CREATE SEQUENCE                          NO
LL92                           CREATE PROCEDURE                         NO
LL92                           UNLIMITED TABLESPACE                     NO

8 rows selected.

I'm sure it's something silly, that I've overlooked.

Thanks for being my second set of eyes.
 

I created the following trigger in the schema of the user that I want to trace and it works.

CREATE OR REPLACE TRIGGER LL92.Trgr_LL_Logon
After logon on database
Begin
if ( user='LL92') then
execute immediate 'alter session set sql_trace=true';
End if;
End;


I'll take this as a working solution.

Is there a better/different way of doing this ?

Thanks

Tom,

Any idea why DBMS_SYSTEM is not listed in the Oracle9i Supplied PL/SQL Packages and Types Reference ?

Very strange -- all other packages seem to be.

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/toc.htm <code>

Tom, why don't you sometimes answer questions, like above ?

I'm saaaaad :'(

Dear Tom,

<asktom>
Using SQL_TRACE with MTS is virtually useless. The trace data gets written all over the place.
</asktom>

What is the idle world scenario to address this issue?


How about connection pool in dedicated server env? Is there a better way to collect the trace for "user interested actions"?

Ours is a web-based environment where multiple connection pools and huge number of connections for each pool. We are trying to improve specific "User Targeted Actions".

If I turn on SQL_TRACE at instance level, it will generate vast amount of trace log and not easy to figure out the problem. Also I don't want to run the SQL statements separately. It should come from Application Server
so that it can simulate real time.

Anticipating your valuable commets

Dear Tom,
<AskTom>
ops$tkyte@8i> exec sys.dbms_system.set_sql_trace_in_session( 46, 13217, TRUE );
PL/SQL procedure successfully completed.
would enable tracing in that session.
</AskTom>

How do I enable EXTENDED TRACE (using dbms_system or some other way)?

One way I can use Logon Trigger and in the trigger body
"alter session set events '10046 trace name context forever, level 12';"

But the user is already connected to the database and business(end user) does not want to bring down the application and/or relogin?

Is there any other way to accomplish the same?

Your help/support is always appreciated.

Tom

you said

"so, unless the application is will to help, it is near impossible to trace in 9i and before. "

Does this imply there is something that will help in 10g, and if so, what is it ?

Regards

Nil

We use the dbms_support package that is supplied with 8i and it provides a trace with binds that you can easily read in the trace file.

If I want to trace a front end application, like reports or forms, I'll put a dbms_application_info.set_module() call in startup. Then in TOAD session monitor, I'll find the sid/serial# of that session (or via script), jump to toad sql editor and issue a start trace, run the form, and then stop trace. Tom's trace files script shows where it is at and what it is named.

For reports, since it is a secondary connection, I grab that session's info, and run the calls right inside the report. Generally, all I ever want is the EXACT sql statement that was run, and what the binds were, and this works well in 8i.

Also, we've added a trace option in our menu to allow the user to exec a trace on/off from inside the app.

Tom, one of my application starts a session and that session spawns multiple sessions. In this case, how do I trace? Thanks much for your time.

Hi tom,
I guess i can i ask this question here as it concerns sql tracing and auditing too. Well a bit of auditing. Sorry guys if i am a bit off the mark :)
Well as for SQL_trace part it is recommended to be used as and when required as it generates a lot of .trc files. Well the DBA before me unknowingly (i guess) or on purpose has set the SQL_TRACE = TRUE in the spfile. Now i am in a fix as to set it to false or let it be True. The system is taking it fine and i see on hiccups. So is it ok. Also MAX_DUMP_FILE_SIZE = UNLIMITED. It has generated 351 .trc files so far. Also I believe that Oracle uses these .trc files should the need arise. Am i right?
Also SQL_trace should exclusively be used for tuning right or can i use it for other purposes as well. Could you state a few other uses. Also i want to record what DML statements are being executed in a session or by a specific databse User. The user is just a normal user. So what should i use Auditing (be session or access) or FGA can be helpful here. Any giudance in this regard will be immensely helpful. Database is Oracle 9i Release 2.
Thanks a lot in advance.

Hi

I have been tracing our VB application connecting to Oracle using Oracle´s ODBC.
When I tkprof the trace file I see simple queries such as

SELECT medicalrecord,patientnumber,dateofdischarge,timeofdischarge,section,
patientype,icdadmissioncode,icdfreetext1,icpadmisioncode,icpfreetext,
icdadmissioncode2,icdfreetext12,icpadmisioncode2,icpfreetext2,activityid
FROM
activitypatient

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.02 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.02 0 0 0 0

Misses in library cache during parse: 3
Optimizer goal: CHOOSE
Parsing user id: 29

update activitypatient set medicalrecord = 113054,patientnumber = '04-246644',
dateofdischarge = to_date('05/07/2004', 'dd/mm/yyyy'),timeofdischarge =
to_date('01/01/1900 13:00:00', 'dd/mm/yyyy hh24:mi:ss'),section= 'OFT',
patientype = 'E',icdadmissioncode ='366.9',icdfreetext1 ='CATARATA NO
ESPECIFICADA',icpadmisioncode ='13.41',icpfreetext ='FACOEMULSIFICACION Y
ASPIRACION DE CATARATA',icdadmissioncode2 ='401.9',icdfreetext12 ='NO
ESPECIFICADA',icpadmisioncode2 ='13.71',icpfreetext2 ='INS.PROTESIS
CRIST.INTRAOC.MOMENTO EXTRAC.CATARATA'
where
activityid = 246784

etc.............

SELECT
medicalrecord,name,surname,surname2,sex,dateofbirth,deathindicator,
stateofbirth,ninumber,niletter,address,city,state,zipcode,telephonenumber,
mobilephone,foreignpatient,patsecuritylevel
FROM
patient


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.01 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 0 0 0 0

Misses in library cache during parse: 4
Optimizer goal: CHOOSE
Parsing user id: 29

update patient set medicalrecord =113054,name ='JULIO',surname ='VELARDO',
surname2 ='PIÑAS',sex ='M',dateofbirth = to_date('14/05/1942', 'dd/mm/yyyy')
, deathindicator = 0,stateofbirth ='10',ninumber=125937,niletter='N',
address ='CALLE FERROCARRIL 6 3º-A',city =280587,state ='28',zipcode =28944,
telephonenumber ='916906694',mobilephone ='649893255',foreignpatient =0,
patsecuritylevel=0
where
medicalrecord = 113054
etc......


This is puzzling because in the application there is no such queries because activitypatient has over 1 million of rows. Also this parses but never executes in what situation can cause this? I thought there was triggers but there arent any. I also thought about updating parent tables and FKs are not indexes so Oracle needs to check child table but there arent any FKs in this application neither.

I am not sure where do these queries come at all, why they are parsed but not executed?

Tom,

1. I used dbms_system.set_sql_trace_in_session yesterday and it created trace files. I let it run for a couple of hours before turning off. Interestingly, it won't work today. I tried 2-3 times and it is not creating any trace files. Any idea why?

2. In v$session, I can see a program with a process, appearing several times, each with a different sid and serial# s. Could you tell why this is so?

3. I used tkprof on a trc file that ran for 1 hour. This one is for a java application accessing from another machine (this particular application appears without a processid and it appears as a single session unlike the ones mentioned in Q 2).

There are several sql statements and some of them are select * from tablename statements. They have parse=1285 execute=0. Is it okay to have such statements?


4. One important sql that does the insert has Misses in Library cache during execute=4 and parse=1. What does that mean? It seems to be using bind variables well. It is doing an insert into 108 columns. eg: VALUES (:1,:2, .... :108)

5. Another major update statement used outerjoins and bind variables but has the following stats. However I see something like this in query in the sub query select clause. Is it ok? select x, y, z, :1 SourceID from

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5197 53.03 1923.12 0 0 10394 0
Execute 5197 44.60 1765.00 0 93618 41576 5197
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10394 97.63 3688.12 0 93618 51970 5197

Misses in library cache during parse: 2


Please clarify

Tom,

If we observe trace information for 1 hr, I assume it shows sqls in that timeframe. And from the given information for each sql, say parse/execute ratio, misses in cache etc .. is it enough to judge the quality of sql? Will there be a difference if we run a report traced for 4 hrs, 6 hrs?

Tom,

Great replies! Thanks for pointing this out, I disabled them soon after I realized a new trc file is not creating. I now understand that there is no way a new trc file be created unless the process is bounced and therefore I think there shouldn't be any filesystem growth whatsoever? Right?

<quote>
e) you enabled trace again but nothing *seems* to happen (but if you monitor
your file system, you'll see it having less and less free space as the trace
file grows, sure hope you TURNED OFF tracing!!!!)
</quote>

Tom,

This sql is using bind variables. It is just querying one table. Two key columns and four normal fields. However, parses=executes and it has one miss in library cache. Can you please comment?



SELECT "KEY1","KEY2","CCC","DDD",
"EEE","FFF"
FROM
"TAB1" "A3" WHERE :1="KEY1" AND :2="KEY2"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6086 3.13 4.86 0 0 0 0
Execute 6086 4.74 5.47 0 0 0 0
Fetch 6086 2.10 19.06 1239 24365 0 6086
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18258 9.97 29.39 1239 24365 0 6086

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 827 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TAB1
2 INDEX UNIQUE SCAN (object id 44781)

Tom,

Similar to one of questions above,

This sql is doing an insert into all the columns of one table. eg: VALUES (:1,:2, .... :108)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1285 0.65 2.13 0 0 0 0
Execute 1285 8.85 22.58 126 9319 25160 1285
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2570 9.50 24.71 126 9319 25160 1285

Misses in library cache during parse: 0
Misses in library cache during execute: 1

If it is using bind variables for all the columns it is inserting, then why is there a miss? Why will there be that many parses? Any ideas? Thanks in advance.

Tom,

Page # 458 in your book shows a query which has Parse=1, Execute=1. I have some sql statements with similar stats

The first 2 sqls have similar stats

select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
from
obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and
o.owner#=u.user# order by o.obj#


select baseobject,type#,update$,insert$,delete$,refnewname,refoldname, whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,
refprtname,rowid
from
trigger$ where obj# =:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.03 1 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.03 1 14 0 2

Misses in library cache during parse: 1


select owner#
from
obj$ o where obj# = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1

Misses in library cache during parse: 1

select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale,
c.precision#,c.charsetid,c.charsetform
from
triggercol$ tc,col$ c ,trigger$ tr where tc.obj#=:1 and c.obj#=:2 and
tc.intcol#=c.intcol# and tr.obj# = tc.obj# and (bitand(tr.property,32) !=
32 or bitand(tc.type#,20) = 20) union select type#,intcol#,position#,69,0,0,
0,0,0 from triggercol$ where obj#=:3 and intcol#=1001 union select tc.type#,
tc.intcol#,tc.position#,121,0,0,0,0,0 from triggercol$ tc,trigger$ tr where
tr.obj# = tc.obj# and bitand(tr.property,32) = 32 and tc.obj# = :4 and
bitand(tc.type#,20) != 20


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 29 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 7 0.00 0.02 1 359 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.01 0.04 1 388 0 6

Misses in library cache during parse: 1


Could you please tell if these statements are just run once per session? This is from tkprof report run using dbms_system.set_sql_trace_in_session. Does that mean that these sqls were run just once (while the trace file is updated) or is anything wrong with them (they parse everytime)? It will be a great help if you can take each one of the above sqls and comment something.

> Recursive SQL is always parsed like that -- it cannot be avoided.

Can setting (increasing) session_cached_cursors improve the performance of recursive SQL, or does rec. SQL bypass the "session cursor cache" ?

Just answering "Yes it improves" or "No it bypasses" is more than enough ;)

thanks in advance

Tom,

Thanks for your expert ideas. You said in a response above:
"
yes, the programmer is asking Oracle to parse the query each time the execute it
and Oracle is obeying their command. In other words -- oracle parses when a developer says "hey oracle -- PARSE this"

Sorry for my ignorance. Could you please explain it a little further as to what the coder should do to avoid parsing too much. Does that mean the coder should store his results somewhere? Is it something to do with pl/sql? Please clarify?

In a nutshell, they should code in this style:

static PreparedStatement pstmt_persistent;

public static void SQL_RightWay(Connection conn ) throws Exception
{
    if ( pstmt_persistent == null )
    {
        pstmt_persistent =
            conn.prepareStatement
            ("select object_name from all_objects where rownum <= ?");
    }

    pstmt_persistent.setInt( 1, 5000 );

    ResultSet rset = pstmt_persistent.executeQuery();
    while( rset.next() )
    {
        //System.out.println( rset.getString(1) );
    }
    rset.close();
}


Instead of:


public static void SQL_DoneSoWrong(Connection conn ) throws Exception
{
static PreparedStatement pstmt;

    pstmt =
            conn.prepareStatement
            ("select object_name from all_objects where rownum <= ?");

    pstmt.setInt( 1, 5000 );

    ResultSet rset = pstmt.executeQuery();
    while( rset.next() )
    {
        //System.out.println( rset.getString(1) );
    }
    rset.close();
    pstmt.close();
}



They are calling "parse (prepare)" and "close" over and over and over and over and over and over.....  or, they can:

Tom,

In the above question, I realized I shouldn't have said "coder should store results somewhere?". Thanks again.

Tom,

Earlier for one particular case, you discouraged the idea of doing count(*) . But there could be a compelling reason why someone needs to find the count. Maybe they need to perform actions depending on a count range

Could count(*) be avoided for such purposes also? This query has excessive fetch-query Any idea why this happens?
I read about bulkfetches in your book but I am not able to relate that here.

SELECT COUNT(*)
FROM
tablename


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 852 0.16 0.06 0 0 0 0
Fetch 852 20.26 24.07 256497 651786 3408 852
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1704 20.42 24.13 256497 651786 3408 852

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 483 (recursive depth: 1)
********************************************************************************

Thanks for the insight.

Being a SYSDBA, how can I find whether SQL_TRACE is enabled for a particular session? I can very well find if it is enabled at the instance level?

ls -l $ORACLE_HOME/admin/$ORACLE_SID/udump

if you see a trace file for their session.... they are tracing.

SQL> show parameter sql_trace 

to see if the instance is tracing. 

Tom,

Is it possible to trace group of session together or session from same username together?

Thanks for your time
Pravesh Karthik

Hi,
1) I have application server which connects to db(9.2).Now I get constant request from developer something like this ,"can you tell me what sql, application is sending to db" ??.It will be very hard to first find out sid and then start tracing using dbms pacakage..because by the time i find sid,application will pass many sql to db.
Is there any smart way(posibley trigger ??) of doing it in three tier architecture.Only info I can provide is name of the "db user" through which they connect to.Application is written in java.Any example will be helpful.Db box hosts many other application(s),so I can't do instance level tracing.

2)I am also looking for a way to generate a report(for management purpose) ,like number of transaction db is handling/handled(say weekly etc),resource used for the same etc.I am aware about statpack report(but I am looking for a report which management can unerstand).Any tool ,any idea will be appreciated.

Cheers

If "A" has access to TOAD, you can do this quite easily via the session browser. Although it does seem strange to me that the developers can't identify statements coming from their code? They can't do a text search for DML statements, anything?

Hi,
Is it possible to trace at the user level,because application server has to use datbase user(schema) for connection??.Just for tracebility ,I can start only one connection(say X),and trace whatever is going from X to db.Write some sort of trigger...(just a thought.),it will happen in very controlled environment where I can control number of people Using system.As always your expert comment is appreciated.
For Alex...It's totally different ball game altogether when you go in 3 tier envirionment.It's extremly difficult fo find text in 1000/5000 lines of code(with ejb,html,third part code etc) and the pattern of their execution,On the top of that If it is open source,it makes your life more difficult.I have seen over and over again people struggling to find out which sql is coming from where ...?? Now a day people write one piece of code and another they buy,they put them together and download another piece of code(open source) and again put them together.This is real world.No one has any clue what is happening in that piece of code.Yes this is real world(So called open source ...java...etc)
Yes you can find out if it is add/mod/del program for university project,but not in real time.Hope you understand :)

Cheers

Hi,
So you say,There is absolutely no way to trace if it is 3 tier architecture ??.I have a db user called "x" and application server connects(yes they have connection pool)
Using this user.Now I want to find out,what sql is being sent to db through that users.To pe precise say I have one db machine and one application server box,lets also assume only one client is connecting using IE via URL say "</code> http://app_server...".Now <code>I want to find out each sql is being sent to db engine.Here I know which database user they are going to connect with.So i want to pass that user name to a package like dbms_system,dbms_utility etc and write a trigger.Now I want to find out if this approach will work ,if not what is the other way ?.Any example will be helpful.

Cheers

It is a good explanation, Tom. But I have a question right now.

I cannot capture the SQL_Trace file for those SQLNet connection session. (I am using db version 8.1.7.4)

Here is an example, I use v$session to find out all the SID and SERIAL# that connect to my DB, and the results are: 

SID SERIAL# MACHINE TERMINAL 
PROGRAM 
--------- ---------- ------------------------- ------------------------- 
------------------------- 
1 1 QCPEPL UNKNOWN oracle@QCPEPL (PMON) 
2 1 QCPEPL UNKNOWN oracle@QCPEPL (DBW0) 
3 1 QCPEPL UNKNOWN oracle@QCPEPL (LGWR) 
4 1 QCPEPL UNKNOWN oracle@QCPEPL (CKPT) 
5 1 QCPEPL UNKNOWN oracle@QCPEPL (SMON) 
6 1 QCPEPL UNKNOWN oracle@QCPEPL (RECO) 
7 1 QCPEPL UNKNOWN oracle@QCPEPL (ARC0) 
8 148 RESOURCE_DM\EDWARDKFPANG EDWARDKFPANG sqlplusw.exe 
11 33681 QCPEPL pts/0 sqlplus@QCPEPL (TNS V1-V3) 

9 rows selected. 

And then I enable the SQL Trace of (SID, SERIAL#: 8,148) user, i.e Edwardkfpang 
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,148,TRUE); 
PL/SQL procedure successfully completed. 

But the outcome is, i cannot find the trace file for edwardkfpang. On the other hand, i can get the trace file for (11,33681) session. It seems that sql trace cannot captured those session that connect the db via SQLNet.

For your information, i have tried to enter an entry:
sql_trace=true in init.ora and re-start the oracle database.
But it still cannot trace the SQL of EDWARDKFPANG 

It is quite urgent, please help. Thanks 
 

Hi Tom, which is your position about the usage of ORADEBUG utility.
This is like hidden parameters: Don't use them, because there can be secondary effect..
Or is use it, but it's not documented.

Thanks Tom

Thanks Tom

Hi Tom, Oracle upgrade manual says
The following initialization parameters were deprecated in release 10.2:
SQL_TRACE

Which parameter replaced it, set events?

Thanks

Thanks Tom .
I understand too dbms_monitor.SESSION_TRACE_ENABLE
reeplaces 10046 event, or there is some difference if I use 10046 event instead of dbms_monitor?

Thanks

Hi Tom,

Is there any way we can find out which all sessions have sql_trace set to true?

Thanks and Regards
Amar

Hi Tom,

I meant, is there any way we could find sessions with trace enabled in Oracle9i or Oracle8i?

Thanks and Regards
Amar

Thanks Tom,

Yes looking in the udump and comparing the process (spid) in the v$process to sid is one of the way. Recently we faced a situation where all the trace files got deleted from the udump before disabling the the traces. The filesystem was getting filled up with invisible trace files. This is when we needed a view or table to find out which all sessions had trace enabled and had missing trace files.

Is there any way ?

Thanks
Amar

Tom,
On 8174, i have a query, which has the db link in it, like

in DB_A:
select *
from taba , tabb@DB_B, tabc@DB_B
where ....

i tried to use dbms_system.set_sqltrace_in_session from DB_B.

On DB_A, i did
select * from dual@DB_B;

ON DB_B, i captured the sid, series for the session coming from DB_A. and I issued bms_system.set_sqltrace_in_session(sid, series, true)

Then On the DB_A, i issued the query.

On DB_B, i noticed a newer session coming from DB_A, which was relevant to the query and was not I used in dbms_system.set_sqltrace_in_session. Thus the trace was not good.

How could sql_trace over db link?

Tom -

How would I enable extended tracing for a procudure that is executed via DBMS Job? Oracle 9.2.0.7 DB

Thanks,

Paul

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2,
action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

I'm trying to figure out what each of the parameter means in the API. My guess is that module_name and action_name map to v$session.module and v$session.action respectively. Is that correct?

What's the service_name (the first parameter of the API)?

I just noticed that there is a service_name column in v$session. Would this map to the service_name of the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE API?

BEGIN
dbms_application_info.set_module(
module_name => 'XYZ Module',
action_name => 'XYZ Action'
);
END;
/

SID SERIAL# STATUS MODULE ACTION SERVICE_NAME
---------- ---------- ---------- ------------ ------------ ------------
152 13143 ACTIVE XYZ Module XYZ Action SYS$USERS

There is another API that has instance_name as a parameter. I assume that's the instance you're connected to? Is there anyway to set the service_name?

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2 DEFAULT ANY_MODULE,
action_name IN VARCHAR2 DEFAULT ANY_ACTION,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL);

Sorry I missed a statement in the question above. The following SQL produces the result above.

SELECT sid, serial#, status, module, action, service_name
FROM v$session
WHERE module = 'XYZ Module';

SID SERIAL# STATUS MODULE ACTION SERVICE_NAME
---------- ---------- ---------- ------------ ------------ ------------
152 13143 ACTIVE XYZ Module XYZ Action SYS$USERS

Tom,

I have a procedure

CREATE OR REPLACE PROCEDURE p
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
a VARCHAR2 ( 100 );
b VARCHAR2 ( 100 );
c VARCHAR2 ( 100 );
BEGIN
SELECT SYS_CONTEXT ( 'userenv', 'session_user' )
, SYS_CONTEXT ( 'userenv', 'current_user' )
, SYS_CONTEXT ( 'userenv', 'os_user' )
INTO a
, b
, c
FROM DUAL;

DBMS_OUTPUT.put_line ( ' os user ' || c );
DBMS_OUTPUT.put_line ( ' curr user ' || b );
DBMS_OUTPUT.put_line ( ' sess user ' || a );
DBMS_OUTPUT.put_line ( ' executing P' );

BEGIN
EXECUTE IMMEDIATE 'alter session set timed_statistics=true';

EXECUTE IMMEDIATE 'alter session set max_dump_file_size=unlimited';

EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever ,level 12''';
END;

ROLLBACK;
END;
/


I can create the above procedure in my own schema and run it, works fine, but if I remove the authid current_user it says

z001698@DEVELOP > exec p
os user z001698
curr user Z001698
sess user Z001698
executing P
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "Z001698.P", line 27
ORA-06512: at line 1


Elapsed: 00:00:00.02
z001698@DEVELOP >


I cannot understand why?

1) What is the system privilege reqd to start a trace?
2) What happens in my case, even if it had been enabled thorugh a Role, if the current_user = sess_user, it should work for authid definer/current_user?

Thanks

Ravi

Ofcourse it is.
I could do the first two alter session statements from SQL plus (set role none) ie max_dump_size/timed_stats , but could not do the set events one.
Is there a system role just for 'alter session set events' which allows max_dump_Size?
It aborts with insufficient privs (1031)

Tom,

I am trying to enable tracing for sessions which are connected as hr user using dbms_monitor package on 10.2.0.2
but i am not able to see the trace files in user dump directory am i missing something ??

TIA.


Here is the example i used.

sql sesssion connected as sys
==============================
SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('hr',true, true);

PL/SQL procedure successfully completed.

/* Went to another session connected as hr and fired sql
statement see below*/
/* after executing select from hr came back to sys session
and fired this */

SQL> EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ('hr');

PL/SQL procedure successfully completed.

SQL> !ls -lrt
total 12
-rw-r--r--  1 oracle dba 11217 Apr  4 04:53 t1.txt

SQL> !pwd
/u01/app/oracle/admin/ORA10GR2/udump




sql session connect as hr
=========================
SQL> show user
USER is "HR"
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
......
..... lines ommited





 

Tom,

I am performing this on my desktop and i am the only one using it i cleared the trace files even before starting the database.

TIA.

oh, I see, you never turned on tracing for those sessions.  the client_id there is not a "userid", it is the client_id


ops$tkyte%ORA10GR2> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  2    from v$process a, v$session b, v$parameter c, v$instance d
  3   where a.addr = b.paddr
  4     and b.audsid = userenv('sessionid')
  5     and c.name = 'user_dump_dest'
  6  /

TRACE
-------------------------------------------------------------------------------
/home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_28245.trc

<b>so, if I were tracing, that would be my trace file name for my dedicated server...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !ls /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_28245.trc
ls: /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_28245.trc: No such file or directory

<b>it is not there.  Now, I identify my client_id as HR (different from my userid)</b>

ops$tkyte%ORA10GR2> exec dbms_session.set_identifier( 'HR' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> show user
USER is "OPS$TKYTE"

ops$tkyte%ORA10GR2> exec dbms_monitor.client_id_trace_enable( 'HR' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> !ls -l /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_28245.trc
-rw-rw----  1 ora10gr2 ora10gr2 2332 Sep 10 09:20 /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_28245.trc

<b>and I am tracing</b>


ops$tkyte%ORA10GR2> exec dbms_monitor.client_id_trace_disable( 'HR' );

PL/SQL procedure successfully completed.
 

Hello Tom,

Thanks for the wonderful site.

I have a small doubt. 

I start trace at instance level using 
 " alter system set events '10046 trace name context forever, level 12';" 

I noted the following 
a) there is no trace file generated for that particular session ( which turned ON the tracing)
b) even after I issue
" alter system set events '10046 trace name context off';"
The sessions that were started after the instance level tracing was ON , continues writing to the trace file .

SQL> !ls -l
total 0

SQL> alter system set events '10046 trace name context forever, level 12';

System altered.

SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 0

SQL> conn /as sysdba
Connected.
SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 14
-rw-r-----   1 oracle     dba           3830 Sep 12 07:45 shedv01_ora_21494.trc

SQL> alter system set events '10046 trace name context off;   ';

System altered.

SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 14
-rw-r-----   1 oracle     dba           5209 Sep 12 07:46 shedv01_ora_21494.trc

SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 14
-rw-r-----   1 oracle     dba           6060 Sep 12 07:46 shedv01_ora_21494.trc

SQL> conn /as sysdba
Connected.
SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 16
-rw-r-----   1 oracle     dba           7227 Sep 12 07:46 shedv01_ora_21494.trc

SQL> select sysdate from dual; 

SYSDATE
---------
12-SEP-06

SQL> !ls -l
total 16
-rw-r-----   1 oracle     dba           7227 Sep 12 07:46 shedv01_ora_21494.trc


Am I missing something ?? Or is this how it behaves ??

 

Tom,
We are trying to convert one of our applications from 9i to 10g. While running one of our stored procedures that works in 9i we notice that in 10g it consumes all of the CPU and seems to go off to la-la land. I traced the session as you described and found that it gets to a very simple SQL statement with one bind variable and appear to never quit trying to bind the variable. The trace file grows at the rate of about 500K per second (or as fast as it can write).

We shut the database down after a couple minutes but the last 15 million lines in the trace file are all the same. It is a repeat as follows:

BINDS #13:
kkscoacd
Bind#0
oacdty=01 mxl=32(18) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=01 siz=32 off=0
kxsbbbfp=ffffffff7ac25b50 bln=32 avl=18 flg=05
value="THIS IS MY TEST VL"

This is repeated over and over directly after:

=====================
PARSING IN CURSOR #13 len=76 dep=1 uid=26 oct=3 lid=26 tim=2673678511937 hv=632465996 ad='c5bb5bd0'
SELECT COLUMN1 FROM MYSCHEMA.TABLE1 WHERE COLUMN2 = :B1
END OF STMT
PARSE #13:c=0,e=415,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,tim=2673678511929

It is truly a very simple trace file. There are only 10 SQL statements that get executed, and it is sort of funny to see the tkprof output show 10 sql statements + 1 internal sql statement and then report that the trace file had 10+ million lines in it.

Have you ever seen anything like this. To make it even stranger, there is no data in the table it is trying to access. But it appears that it never even tries to fetch/execute so it doesn't matter. This obviously works in 9i, but kills the box in 10g.

Any clues?

Wanted to add one detail...

Even though it should make no difference that I can think of, t is interesting to note that the select statement that is being shown is:

a) a bulk collect
b) an explicit cursor with a parameter passed to the cursor and the value for the parameter when it is opened is coming from a structure (PL/SQL table).

This code seems to work ok when there is only one record in the PL/SQL table, but not when there is more than one. That's a little bit of speculation on my part as I just discovered this. I know that shouldn't matter but maybe it could be a bug related to passing and indexed entry into a PL/SQL table as a parameter to a cursor. I'm sure it's not that simple, but thought it would be worth posting...

Wow, after plenty of playin around I found the problem/bug. Thankfully I don't have to claim to be the author of the code that I didn't even think was valid, but evidently it is...

If you put the bulk collect in the cursor declaration (which I didn't know you could do and actually have it compile), the process will go off and consume the CPU and never come back.

Here is a simple script that demonstrates this in 10.2.0.2:

DECLARE
TYPE TestTab IS TABLE OF dual.dummy%TYPE;
vTab TestTab;

CURSOR c_cursor IS
SELECT dummy BULK COLLECT INTO vTab FROM dual;
BEGIN
OPEN c_cursor;
FETCH c_cursor BULK COLLECT INTO vTab;
CLOSE c_cursor;
END;

Tom,

Do you know how I can set tracing in another session and also get the bind values? Thanks.

No wonder dbms_system was not giving any hits in the docs.

I have a couple more questions. Is there anyway to get a trace file created with out having the session exit you are trying to trace? Or, as another alternative, are there any views I can join to get and statement that is running, and it's bind data?

&#9567;&#9792;^

Tom,

I found the following code in your book:

create or replace trigger logon_trigger

after logon on database

begin

if ( user = 'TKYTE' ) then

execute immediate

'alter session set events ''10046 trace name context forever, level 4''';

end if;

end;

/


My question is how can I enable this if the connection goes thought application server - so all user connection in v$session have the same user, but different OSUSER?

By the way: if I right a logon trigger without handling exceptions - is it possible that if I do something wrong nobody will be able to log on to the database?

Tom,

We have 9i. But let me rewrite the question:

The connections are pooled and all have the same database user, but as the user starts a new application module it is definitely that a new connection will be created at this moment - that is why I need a trigger and can not simple set tracing for existing session. The new connection has the same osuser in v$session as a "parent connection".

I need something like:

create or replace trigger trace_session
after logon on database
declare
os_user varchar2(30);
begin
select lower(sys_context( 'userenv', 'OS_USER' )) into os_user from dual;

if ( os_user = '&1' ) then
execute immediate
'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL &2''';
end if;
end;
/

The trigger is compiled without any errors, but as I try to trace a session I see the following trace file in udump and a session trace will not be created as expected:

*** SESSION ID:(53.7849) 2007-02-06 12:39:52.220
Skipped error 604 during the execution of ******.TRACE_SESSION
*** 2007-02-06 12:39:52.235
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 7

My questions are:

1. I created the trigger connected as sysdba. Should I provide some additional privs to users which I want to trace. If so can I rewrite the trigger so that I can avoid granting some additional privs to users - I want to have a simple trigger I can use on our prod system - and I can not change users on them...

2. As I select os_user I get something like domain_name\first_name.last_name (or eu\john.smith for windows). Is there any way to rewrite my select to omit the domain_name. The problem I can have domains like eu, na or eu.de etc and I want only to type user name by starting tracing

I am encountering an issue with regard to tracing sessions. My insert..select statement runs in parallel with upto 16 parallel slaves.

Before I issue the DML statement, I say alter session set events '10046 trace name context forever,level 8';

During the execution, I see bunch of wait lines associated to the cursor (insert..select) that says WAIT #1: nam='PX Deq: Execute Reply' ela= 1952967 p1=200 p2=3 p3=0

This line repeats almost till the end. I could not infer any thing from this wait event. However, if I trace one of the parallel slave I could see different wait events like "direct parth read, db file sequential read" which probably related to IO.

How can I get the information about the parallel slaves to figure out what exactly happened during my insert..select?

Do I need to trace each one of the parallel slave separately?

Thanks,

In 10g you mean if I use dbms_monitor.SESSION_TRACE_ENABLE, here I still have to specify sid and serial#.

Is there any other procedure under dbms_monitor package that can trace all the parallel slaves?

Thanks,

I'm trying to generate a consolidated trace file using trcsess, but I couldn't quite get it to work. Here's the sequence of events that I did.

Session #1 
------------------------------------------------------------
exec dbms_session.set_identifier('ClientTag');

BEGIN
   dbms_monitor.client_id_trace_enable('ClientTag');
END;
/

SELECT /* Session #1 */ * From dual;


Session #2
------------------------------------------------------------
exec dbms_session.set_identifier('ClientTag');

SELECT /* Session #2 */ * From dual;


Session #1
------------------------------------------------------------
BEGIN
   dbms_monitor.client_id_trace_disable('ClientTag');
END;
/

In the udump directory, I see two trace files generated. The trace files contain the two SQL statements that I executed in each session. Then I generated a consolidated trace file using trcsess utility.

trcsess output="Consolidated.trc" clientid="ClientTag"


Consolidated.trc got generated. However, it has nothing, 0 byte.

Am I doing something wrong?

[ora10gr2@desktop ~]$  trcsess output="Consolidated.trc" clientid="ClientTag"
[ora10gr2@desktop ~]$ ls -l Consolidated.trc<b>
-rw-rw-r--  1 ora10gr2 ora10gr2 0 May 11 08:30 Consolidated.trc</b>
[ora10gr2@desktop ~]$  trcsess output="Consolidated.trc" clientid="ClientTag" $ORACLE_HOME/admin/$ORACLE_SID/udump/*.trc
[ora10gr2@desktop ~]$ !ls
ls -l Consolidated.trc<b>
-rw-rw-r--  1 ora10gr2 ora10gr2 6399 May 11 08:30 Consolidated.trc</b>
[ora10gr2@desktop ~]$

The dump trace file has reached its max size limit as specified by max_dump_file_size paramater.I had to capture the events in that trace file.
So i had to set max_dump_file_size=unlimited and had run some commands for setting the events like alter system set events'--------------------';
but that trace dump file is not getting updated even after setting the parameter max_dump_file_size=unlimited.i tried
renaming/removing the previous trace file but new file was also not created.

Can you pls let me know how do i capture the events.I am capturing the events for ora-600 erros which happens abruptly and error cannot be reproduced at will.Please let me know..thanks

Tom,

our sql_trace does not show the bind variable values used. it shows ? only. which trace shows the queries with those values, could you please recommend?

Tom,
Iam trying to trace 2 queries in an environment, where these 2 queries perform quick in one database and slow in another.when we traced the session that is executing these queries and use tkprof over this file, i couldnt find row source operation details for one query. We use dbms_system to trace the session. Another strange thing is we are not getting row source operation for both the queries in another database.This is on oracle 9.2.0.8

Database 1:
query 1

SELECT A.DLQ_ST FROM
( SELECT NVL(DLQ_ST,0) DLQ_ST FROM DT010HB WHERE AC_NO = :B1 AND CHECKSUM =
99 ORDER BY DLQ_DT DESC ) A WHERE ROWNUM = 1

call count cpu elapsed disk query current rows

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

Parse 0 0.00 0.00 0 0 0 0

Execute 5601 1.14 2.44 0 0 0 0

Fetch 5601 884.69 2191.98 0 12977517 0 5601

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

total 11202 885.83 2194.43 0 12977517 0 5601



Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 22 (recursive depth: 1)

********************************************************************************

query 2
SELECT COUNT(1)

FROM

DT010HB WHERE AC_NO = :B1 AND CHECKSUM = 99





call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 5613 0.49 1.07 0 0 0 0

Fetch 5613 880.36 2170.04 0 13005321 0 5613

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

total 11227 880.85 2171.11 0 13005321 0 5613



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 22 (recursive depth: 1)



Rows Row Source Operation

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

3 SORT AGGREGATE

12 TABLE ACCESS FULL DT010HB

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

In database 2 for these queries i have only the parse/execute details and no RSO details. Also i could spot row source operation is available for only few sql stmts.

Can you help me please?

Tom,
Thanks a lot.By closing the cursor means cursor(s) of the program being traced.Am i right.Why iam asking is call to dbms_system package level cursor you mean?
If i want to troubleshoot a query within an application, where it resides in a procedure and uses information from the application level cursor, i will be missing the information if i trace that procedure alone(provided i know the time how long the application runs and when it executes out of the whole application).

I'm trying to get details on configuration of dbms_monitor for end-to-end tracing. I have read the Pl/SQL Pkgs manual and understand the basics of the different procedures and the parameters. However I am concerned about what load dbms_monitor might put on the database since we are already experiencing major response time issues. Can you direct me to a good document/article/metalink note that will detail the usage and impact of dbms_monitor?
Thank you.

Tom,
Earlier in this thread you said that oradebug should be used only under the supervision of Oracle support. Is this still true with new versions like 10g/11g? The reason I am asking is that I still cannot find any documentation on oradebug.

For clarification, would it be right to say that unless I have a SR open with Oracle and the support analyst has asked me to run oradebug, I should not run it in any shape or form against my database even if some Metalink article suggests doing it?

Thanks...

Tom,
I cannot find any documentation on dbms_system either. Is this package also expected to be used under guidance of Oracle support?

Thanks...

ops$tkyte@8i> desc sys.dbms_system
PROCEDURE DIST_TXN_SYNC
<b>[snipped a bunch of undocumented procedures...]</b>

how to increase the performance of reports????

Tom,
I found this article on Tanel Poder's blog about using oradebug to kill a badly hung session. My question: Is this safe to do so in a production database? It is advisable/recommended for production use?

The process is essentially:
oredebug set ospid <process id of hung process>
oradebug short_sack
oradebug even immediate crash

http://blog.tanelpoder.com/2008/06/19/killing-an-oracle-process-from-inside-oracle/

Thanks...

Tom:

I traced my parallel dml insert session with 10046 trace and then run 'trcsess' on the traces. But tkprof row source shows 0 rows as shown below. I tried several time on this but no difference still getting rowsource rowcount as 0.
could you please tell me what i am doing wrong.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 129 17.85 237.85 0 0 0 0
Execute 129 7422.52 231625.50 3249194 40663094 50660 3026574
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 258 7440.37 231863.35 3249194 40663094 50660 3026574

Misses in library cache during parse: 3
Optimizer mode: ALL_ROWS
Parsing user id: 84 (BALA) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
0 LOAD AS SELECT OF 'SL_FEED_TAB'
0 PX COORDINATOR
0 PX SEND (QC (RANDOM)) OF ':TQ20006' [:Q2006]
0 NESTED LOOPS (OUTER) [:Q2006]
0 VIEW [:Q2006]
0 SORT (GROUP BY) [:Q2006]
0 PX RECEIVE [:Q2006]
0 PX SEND (HASH) OF ':TQ20005' [:Q2005]
0 SORT (GROUP BY) [:Q2005]
0 HASH JOIN (OUTER) [:Q2005]
0 PX RECEIVE [:Q2005]
0 PX SEND (HASH) OF ':TQ20003' [:Q2003]
0 HASH JOIN (OUTER BUFFERED) [:Q2003]
0 BUFFER (SORT) [:Q2003]
0 PX RECEIVE [:Q2003]
0 PX SEND (HASH) OF ':TQ20000'
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 VIEW
0 PX COORDINATOR
0 PX SEND (QC
(RANDOM)) OF ':TQ10008' [:Q1008]
0 PX RECEIVE
[:Q1008]
0 PX SEND
(HASH) OF ':TQ10006' [:Q1006]
0 HASH
JOIN (OUTER BUFFERED) [:Q1006]
0 PX
RECEIVE [:Q1006]
0 PX
SEND (HASH) OF ':TQ10004' [:Q1004]

0
BUFFER (SORT) [:Q1004]
0
NESTED LOOPS (OUTER) [:Q1004]
0
NESTED LOOPS [:Q1004]
0
NESTED LOOPS [:Q1004]
0
HASH JOIN (RIGHT ANTI)
[:Q1004]
0
PX RECEIVE [:Q1004]
0
PX SEND (HASH) OF
':TQ10002' [:Q1002]
0
PX BLOCK (ITER
ATOR) [:Q1002]
0
INDEX MOD
E: ANALYZED (FAST FULL
SCAN) OF 'DOC_REJ_IDX'
(INDEX) [:Q1002]
0
PX RECEIVE [:Q1004]
0
PX SEND (HASH) OF
':TQ10003' [:Q1003]
0
BUFFER (SORT)
[:Q1003]
0
NESTED LOOP
S (OUTER) [:Q1003]
0
HASH JOI
N [:Q1003]
0
PX RE
CEIVE [:Q1003]
0
PX
SEND (HASH) OF ':TQ10
000' [:Q1000]
0

PX BLOCK (ITERATOR)
[:Q1000]
0

TABLE ACCESS
MODE: ANALYZED (FUL
L) OF 'THEADER_AR_F
EED' (TABLE) [:Q100
0]
0
PX RE
CEIVE [:Q1003]
0
PX
SEND (HASH) OF ':TQ10
001' [:Q1001]
0

PX BLOCK (ITERATOR)
[:Q1001]
0

TABLE ACCESS
MODE: ANALYZED (FUL
L) OF 'THEADER' (TA
BLE) [:Q1001]
0
INDEX
MODE: ANALYZED (UNIQUE
SCAN) OF 'P_CONSTR_135'
(INDEX (UNIQUE)) [:Q100
3]
0
TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TSUB_HEADER' (TABLE)
[:Q1004]
0
INDEX MODE: ANALYZ
ED (RANGE SCAN) OF 'PSUB_HE
ADER' (INDEX (UNIQUE))
[:Q1004]
0
TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TSUB_HEADER_FNCL' (TABLE)
[:Q1004]
0
INDEX MODE: ANALYZED
(RANGE SCAN) OF 'PSUB_HEADER
_FNCL' (INDEX (UNIQUE))
[:Q1004]
0
TABLE ACCESS MODE: ANALYZED
(BY INDEX ROWID) OF 'TPARTCPNT
_EXPLSN' (TABLE) [:Q1004]
0
INDEX MODE: ANALYZED
(RANGE SCAN) OF 'PPARTCPNT_EX
PLSN' (INDEX (UNIQUE))
[:Q1004]
0 PX
RECEIVE [:Q1006]
0 PX
SEND (HASH) OF ':TQ10005' [:Q1005]

0 PX
BLOCK (ITERATOR) [:Q1005]
0
TABLE ACCESS MODE: ANALYZED
(FULL) OF 'TCLA' (TABLE)
[:Q1005]
0 PX RECEIVE
[:Q1008]
0 PX SEND
(HASH) OF ':TQ10007' [:Q1007]
0 PX BLOCK
(ITERATOR) [:Q1007]
0 TABLE
ACCESS MODE: ANALYZED (FULL) OF
'TTRANSACTION_TYPE' (TABLE)
[:Q1007]
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TSUBLDGCO_MAP_SPDIV' (TABLE)
0 INDEX MODE:
ANALYZED (UNIQUE SCAN) OF 'ISLCMSD0'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TSUBLDGCO_MAP_SPGL' (TABLE)
0 INDEX MODE:
ANALYZED (UNIQUE SCAN) OF
'PSUBLDGCO_MAP_SPGL' (INDEX (UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TREFTAB_MIS780' (TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'TREF_MIS780_PK' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TREFTAB_MIS028' (TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'PREFTAB_MIS028' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE:
ANALYZED (BY INDEX ROWID) OF
'TSUBLDGCO_MAP_SPFCL' (TABLE)
0 INDEX MODE: ANALYZED
(UNIQUE SCAN) OF 'ISLCMSF0' (INDEX (UNIQUE))

0 TABLE ACCESS MODE: ANALYZED
(BY INDEX ROWID) OF 'TSUBLDGCO_MAP_ALL' (TABLE)

0 INDEX MODE: ANALYZED
(RANGE SCAN) OF 'PSUBLDGCO_MAP_ALL' (INDEX
(UNIQUE))
0 PX RECEIVE [:Q2003]
0 PX SEND (HASH) OF ':TQ20002' [:Q2002]

0 VIEW [:Q2002]
0 SORT (UNIQUE) [:Q2002]
0 PX RECEIVE [:Q2002]
0 PX SEND (HASH) OF ':TQ20001'
[:Q2001]
0 PX BLOCK (ITERATOR)
[:Q2001]


0 TABLE ACCESS MODE:
ANALYZED (FULL) OF 'TREINSURER_CO_BR'
(TABLE) [:Q2001]
0 PX RECEIVE [:Q2005]
0 PX SEND (HASH) OF ':TQ20004' [:Q2004]
0 PX BLOCK (ITERATOR) [:Q2004]
0 TABLE ACCESS MODE: ANALYZED (FULL)
OF 'TREINSURER_CO_BR' (TABLE) [:Q2004]
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TSUB_LEDGER_CO' (TABLE) [:Q2006]
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ISULECO0_LEDGER_CO' (INDEX (UNIQUE)) [:Q2006]


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 57343 1.96 101001.73
PX Deq Credit: send blkd 1837206 1.96 13652.14
PX Deq: Table Q Normal 2490737 1.96 106613.40
cursor: pin S wait on X 11725 0.02 217.97
PX Deq: Msg Fragment 88 0.00 0.00
cursor: mutex X 5 0.00 0.00
kksfbc child completion 29 0.05 1.59
db file sequential read 700053 0.40 1191.29
PX qref latch 4272 0.00 0.02
direct path read 92963 0.33 487.89
PX Deq Credit: need buffer 290320 0.42 114.46
read by other session 74885 0.35 151.29
latch: cache buffers chains 107 0.00 0.03
buffer busy waits 1533 0.11 6.18
direct path write temp 62102 0.05 8.78
direct path read temp 67819 0.28 25.16
control file sequential read 160 0.00 0.09
enq: TX - contention 41 0.00 0.08
enq: HV - contention 31050 0.17 54.51
direct path write 64 0.00 0.04
PX Deq: Join ACK 33 0.00 0.00
PX Deq: Parse Reply 106 1.94 3.79
PX Deq: Execute Reply 2530 1.96 986.27
PX Deq: Signal ACK 81 0.10 0.50
latch: session allocation 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

=== SQL and trace info ==
spool slfeed.log
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 32 ;
ALTER SESSION FORCE PARALLEL DML PARALLEL 32 ;

exec dbms_session.set_identifier('SLFEED');
alter session set tracefile_identifier = 'SLFEED';
exec dbms_monitor.client_id_trace_enable(client_id=>'SLFEED');
alter session set max_dump_file_size = UNLIMITED;
alter session set statistics_level = ALL;
alter session set timed_statistics = true;
alter session set events '10046 trace name context forever, level 12';

execute RPT.TRUNCATE_PARTITION('RPT','SL_FEED_TAB','SLFEED_A200811');

insert /*+append*/ into rpt.sl_feed_tab
....
;

exec dbms_monitor.client_id_trace_disable(client_id=>'SLFEED');
select 'close the cursor' from dual;
spool off;
exit;


trcsess script:

trcsess output="SLFEED_trace.trc" clientid="SLFEED" /oradump/logs/uw/RDPSRPTM/bdump/*.trc /oradump/logs/uw/RDPSRPTM/udump/*.trc
tkprof SLFEED_trace.trc SLFEED.tko explain=/ waits=yes

Tom:
max_dump_file_size value is already set to UNLIMITED.

SQL> show parameter max_dump_file_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      UNLIMITED


2) Also, trace file sizes look ok and they are in different sizes.

3) One more thing i noticed, i see 3026574 in total rows column in TKPROF but actual rows inserted was "1513287 rows created." from the INSERT into statement output. 

Any idea why i am getting double rows here in TKPROF ?

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      129     17.85     237.85          0          0          0           0
Execute    129   7422.52  231625.50    3249194   40663094      50660     3026574
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      258   7440.37  231863.35    3249194   40663094      50660     3026574


-rw-r-----   1 orasys   oradba   6695394 Jun 16 23:30 rdpsrptm_p074_13172.trc
-rw-r-----   1 orasys   oradba   6893722 Jun 16 23:30 rdpsrptm_p072_13162.trc
-rw-r-----   1 orasys   oradba   6802385 Jun 16 23:30 rdpsrptm_p079_13182.trc
-rw-r-----   1 orasys   oradba   6938866 Jun 16 23:30 rdpsrptm_p090_13204.trc
-rw-r-----   1 orasys   oradba   7519582 Jun 16 23:30 rdpsrptm_p073_13168.trc
-rw-r-----   1 orasys   oradba   45249213 Jun 16 23:30 rdpsrptm_p082_13188.trc
-rw-r-----   1 orasys   oradba   7104929 Jun 16 23:30 rdpsrptm_p064_13141.trc
-rw-r-----   1 orasys   oradba   6968866 Jun 16 23:30 rdpsrptm_p086_13196.trc
-rw-r-----   1 orasys   oradba   6936595 Jun 16 23:30 rdpsrptm_p071_13157.trc
-rw-r-----   1 orasys   oradba   7028156 Jun 16 23:30 rdpsrptm_p069_13153.trc
-rw-r-----   1 orasys   oradba   6896210 Jun 16 23:30 rdpsrptm_p080_13184.trc
-rw-r-----   1 orasys   oradba   12089331 Jun 16 23:30 rdpsrptm_p084_13192.trc
-rw-r-----   1 orasys   oradba   1282866 Jun 16 23:30 rdpsrptm_p109_13252.trc
-rw-r-----   1 orasys   oradba   6884795 Jun 16 23:30 rdpsrptm_p075_13174.trc
-rw-r-----   1 orasys   oradba   1163603 Jun 16 23:30 rdpsrptm_p124_13667.trc
-rw-r-----   1 orasys   oradba   6803252 Jun 16 23:30 rdpsrptm_p092_13208.trc
-rw-r-----   1 orasys   oradba   1293564 Jun 16 23:30 rdpsrptm_p108_13250.trc
-rw-r-----   1 orasys   oradba   1157740 Jun 16 23:30 rdpsrptm_p097_13223.trc
-rw-r-----   1 orasys   oradba   6593007 Jun 16 23:30 rdpsrptm_p081_13186.trc
-rw-r-----   1 orasys   oradba   1292003 Jun 16 23:30 rdpsrptm_p101_13236.trc
-rw-r-----   1 orasys   oradba   1139962 Jun 16 23:30 rdpsrptm_p099_13232.trc
-rw-r-----   1 orasys   oradba   1287232 Jun 16 23:30 rdpsrptm_p102_13238.trc
-rw-r-----   1 orasys   oradba   6872330 Jun 16 23:30 rdpsrptm_p088_13200.trc
-rw-r-----   1 orasys   oradba   6707210 Jun 16 23:30 rdpsrptm_p070_13155.trc
-rw-r-----   1 orasys   oradba   1270041 Jun 16 23:30 rdpsrptm_p103_13240.trc
-rw-r-----   1 orasys   oradba   6857580 Jun 16 23:30 rdpsrptm_p068_13151.trc
-rw-r-----   1 orasys   oradba   6727582 Jun 16 23:30 rdpsrptm_p085_13194.trc
-rw-r-----   1 orasys   oradba   1285652 Jun 16 23:30 rdpsrptm_p100_13234.trc
-rw-r-----   1 orasys   oradba   1162555 Jun 16 23:30 rdpsrptm_p098_13229.trc
-rw-r-----   1 orasys   oradba   1161344 Jun 16 23:30 rdpsrptm_p110_13254.trc
-rw-r-----   1 orasys   oradba   1173057 Jun 16 23:30 rdpsrptm_p122_13655.trc
-rw-r-----   1 orasys   oradba   125825803 Jun 16 23:30 rdpsrptm_p078_13180.trc
-rw-r-----   1 orasys   oradba   1290027 Jun 16 23:30 rdpsrptm_p096_13216.trc
-rw-r-----   1 orasys   oradba   1154106 Jun 16 23:30 rdpsrptm_p125_13669.trc
-rw-r-----   1 orasys   oradba   11182012 Jun 16 23:30 rdpsrptm_p095_13214.trc
-rw-r-----   1 orasys   oradba   6907041 Jun 16 23:30 rdpsrptm_p091_13206.trc
-rw-r-----   1 orasys   oradba   7482456 Jun 16 23:30 rdpsrptm_p093_13210.trc
-rw-r-----   1 orasys   oradba   1289775 Jun 16 23:30 rdpsrptm_p116_13454.trc
-rw-r-----   1 orasys   oradba   1278605 Jun 16 23:30 rdpsrptm_p127_13673.trc
-rw-r-----   1 orasys   oradba   6676544 Jun 16 23:30 rdpsrptm_p094_13212.trc
-rw-r-----   1 orasys   oradba   1171307 Jun 16 23:30 rdpsrptm_p115_13424.trc
-rw-r-----   1 orasys   oradba   12921710 Jun 16 23:30 rdpsrptm_p077_13178.trc
-rw-r-----   1 orasys   oradba   6892975 Jun 16 23:30 rdpsrptm_p083_13190.trc
-rw-r-----   1 orasys   oradba   34059349 Jun 16 23:30 rdpsrptm_p117_13472.trc
-rw-r-----   1 orasys   oradba   6565634 Jun 16 23:30 rdpsrptm_p066_13147.trc
-rw-r-----   1 orasys   oradba   6890261 Jun 16 23:30 rdpsrptm_p089_13202.trc
-rw-r-----   1 orasys   oradba   1300983 Jun 16 23:30 rdpsrptm_p113_13302.trc
-rw-r-----   1 orasys   oradba   1293656 Jun 16 23:30 rdpsrptm_p112_13264.trc
-rw-r-----   1 orasys   oradba   1153866 Jun 16 23:30 rdpsrptm_p119_13533.trc
-rw-r-----   1 orasys   oradba   1292453 Jun 16 23:30 rdpsrptm_p121_13643.trc
-rw-r-----   1 orasys   oradba   1291082 Jun 16 23:30 rdpsrptm_p107_13248.trc
-rw-r-----   1 orasys   oradba   11900482 Jun 16 23:30 rdpsrptm_p065_13145.trc
-rw-r-----   1 orasys   oradba   6760038 Jun 16 23:30 rdpsrptm_p067_13149.trc
-rw-r-----   1 orasys   oradba   1288344 Jun 16 23:30 rdpsrptm_p120_13584.trc
-rw-r-----   1 orasys   oradba   1269364 Jun 16 23:30 rdpsrptm_p111_13256.trc
-rw-r-----   1 orasys   oradba   6776886 Jun 16 23:30 rdpsrptm_p087_13198.trc
-rw-r-----   1 orasys   oradba   1278812 Jun 16 23:30 rdpsrptm_p114_13346.trc
-rw-r-----   1 orasys   oradba   1171080 Jun 16 23:30 rdpsrptm_p126_13671.trc
-rw-r-----   1 orasys   oradba   7028100 Jun 16 23:30 rdpsrptm_p076_13176.trc
-rw-r-----   1 orasys   oradba   1291900 Jun 16 23:30 rdpsrptm_p105_13244.trc
-rw-r-----   1 orasys   oradba   1150894 Jun 16 23:30 rdpsrptm_p104_13242.trc
-rw-r-----   1 orasys   oradba   1154231 Jun 16 23:30 rdpsrptm_p123_13665.trc
-rw-r-----   1 orasys   oradba   1294756 Jun 16 23:30 rdpsrptm_p106_13246.trc
-rw-r-----   1 orasys   oradba   1150070 Jun 16 23:30 rdpsrptm_p118_13496.trc
-rw-r-----   1 orasys   oradba   3499447 Jun 16 23:30 rdpsrptm_p005_12961.trc
-rw-r-----   1 orasys   oradba   1079068 Jun 16 23:30 rdpsrptm_p014_12997.trc
-rw-r-----   1 orasys   oradba   2211935 Jun 16 23:30 rdpsrptm_p006_12969.trc
-rw-r-----   1 orasys   oradba    794915 Jun 16 23:30 rdpsrptm_p003_12957.trc
-rw-r-----   1 orasys   oradba   3269015 Jun 16 23:30 rdpsrptm_p022_13013.trc
-rw-r-----   1 orasys   oradba    971479 Jun 16 23:30 rdpsrptm_p034_13039.trc
-rw-r-----   1 orasys   oradba   7054532 Jun 16 23:30 rdpsrptm_p045_13070.trc
-rw-r-----   1 orasys   oradba    649546 Jun 16 23:30 rdpsrptm_p032_13035.trc
-rw-r-----   1 orasys   oradba    989303 Jun 16 23:30 rdpsrptm_p058_13108.trc
-rw-r-----   1 orasys   oradba   2494657 Jun 16 23:30 rdpsrptm_p007_12979.trc
-rw-r-----   1 orasys   oradba    810800 Jun 16 23:30 rdpsrptm_p051_13093.trc
-rw-r-----   1 orasys   oradba   2334630 Jun 16 23:30 rdpsrptm_p027_13023.trc
-rw-r-----   1 orasys   oradba   3626822 Jun 16 23:30 rdpsrptm_p008_12985.trc
-rw-r-----   1 orasys   oradba   35812204 Jun 16 23:30 rdpsrptm_p049_13084.trc
-rw-r-----   1 orasys   oradba    536989 Jun 16 23:30 rdpsrptm_p055_13102.trc
-rw-r-----   1 orasys   oradba    677292 Jun 16 23:30 rdpsrptm_p000_12951.trc
-rw-r-----   1 orasys   oradba   1921237 Jun 16 23:30 rdpsrptm_p004_12959.trc
-rw-r-----   1 orasys   oradba   1650495 Jun 16 23:30 rdpsrptm_p029_13027.trc
-rw-r-----   1 orasys   oradba    535810 Jun 16 23:30 rdpsrptm_p050_13088.trc
-rw-r-----   1 orasys   oradba   1066885 Jun 16 23:30 rdpsrptm_p030_13029.trc
-rw-r-----   1 orasys   oradba    562877 Jun 16 23:30 rdpsrptm_p060_13112.trc
-rw-r-----   1 orasys   oradba   8973315 Jun 16 23:30 rdpsrptm_p009_12987.trc
-rw-r-----   1 orasys   oradba   2724486 Jun 16 23:30 rdpsrptm_p002_12955.trc
-rw-r-----   1 orasys   oradba    691123 Jun 16 23:30 rdpsrptm_p039_13049.trc
-rw-r-----   1 orasys   oradba   1368015 Jun 16 23:30 rdpsrptm_p020_13009.trc
-rw-r-----   1 orasys   oradba   2300336 Jun 16 23:30 rdpsrptm_p031_13031.trc
-rw-r-----   1 orasys   oradba   3764432 Jun 16 23:30 rdpsrptm_p015_12999.trc
-rw-r-----   1 orasys   oradba    557014 Jun 16 23:30 rdpsrptm_p037_13045.trc
-rw-r-----   1 orasys   oradba    734581 Jun 16 23:30 rdpsrptm_p040_13051.trc
-rw-r-----   1 orasys   oradba    536407 Jun 16 23:30 rdpsrptm_p057_13106.trc
-rw-r-----   1 orasys   oradba   1688873 Jun 16 23:30 rdpsrptm_p024_13017.trc
-rw-r-----   1 orasys   oradba   28189789 Jun 16 23:30 rdpsrptm_p063_13118.trc
-rw-r-----   1 orasys   oradba   6467869 Jun 16 23:30 rdpsrptm_p028_13025.trc
-rw-r-----   1 orasys   oradba   13443263 Jun 16 23:30 rdpsrptm_p052_13096.trc
-rw-r-----   1 orasys   oradba   5102458 Jun 16 23:30 rdpsrptm_p047_13076.trc
-rw-r-----   1 orasys   oradba   2170767 Jun 16 23:30 rdpsrptm_p038_13047.trc
-rw-r-----   1 orasys   oradba   10665567 Jun 16 23:30 rdpsrptm_p016_13001.trc
-rw-r-----   1 orasys   oradba   1467685 Jun 16 23:30 rdpsrptm_p018_13005.trc
-rw-r-----   1 orasys   oradba    628221 Jun 16 23:30 rdpsrptm_p026_13021.trc
-rw-r-----   1 orasys   oradba    574808 Jun 16 23:30 rdpsrptm_p033_13037.trc
-rw-r-----   1 orasys   oradba    550977 Jun 16 23:30 rdpsrptm_p061_13114.trc
-rw-r-----   1 orasys   oradba    546442 Jun 16 23:30 rdpsrptm_p041_13053.trc
-rw-r-----   1 orasys   oradba   2001374 Jun 16 23:30 rdpsrptm_p012_12993.trc
-rw-r-----   1 orasys   oradba    532216 Jun 16 23:30 rdpsrptm_p042_13055.trc
-rw-r-----   1 orasys   oradba   1027429 Jun 16 23:30 rdpsrptm_p001_12953.trc
-rw-r-----   1 orasys   oradba   1024995 Jun 16 23:30 rdpsrptm_p046_13072.trc
-rw-r-----   1 orasys   oradba   2131315 Jun 16 23:30 rdpsrptm_p019_13007.trc
-rw-r-----   1 orasys   oradba   7326200 Jun 16 23:30 rdpsrptm_p013_12995.trc
-rw-r-----   1 orasys   oradba   3387081 Jun 16 23:30 rdpsrptm_p017_13003.trc
-rw-r-----   1 orasys   oradba   5612030 Jun 16 23:30 rdpsrptm_p023_13015.trc
-rw-r-----   1 orasys   oradba    584007 Jun 16 23:30 rdpsrptm_p036_13043.trc
-rw-r-----   1 orasys   oradba   1326045 Jun 16 23:30 rdpsrptm_p011_12991.trc
-rw-r-----   1 orasys   oradba   2026163 Jun 16 23:30 rdpsrptm_p062_13116.trc
-rw-r-----   1 orasys   oradba    576522 Jun 16 23:30 rdpsrptm_p043_13060.trc
-rw-r-----   1 orasys   oradba    536772 Jun 16 23:30 rdpsrptm_p054_13100.trc
-rw-r-----   1 orasys   oradba    700762 Jun 16 23:30 rdpsrptm_p059_13110.trc
-rw-r-----   1 orasys   oradba   2958165 Jun 16 23:30 rdpsrptm_p044_13065.trc
-rw-r-----   1 orasys   oradba    728492 Jun 16 23:30 rdpsrptm_p048_13081.trc
-rw-r-----   1 orasys   oradba   1066883 Jun 16 23:30 rdpsrptm_p056_13104.trc
-rw-r-----   1 orasys   oradba   1578377 Jun 16 23:30 rdpsrptm_p053_13098.trc
-rw-r-----   1 orasys   oradba   6574402 Jun 16 23:30 rdpsrptm_p025_13019.trc
-rw-r-----   1 orasys   oradba   4436091 Jun 16 23:30 rdpsrptm_p010_12989.trc
-rw-r-----   1 orasys   oradba   2110843 Jun 16 23:30 rdpsrptm_p035_13041.trc
-rw-r-----   1 orasys   oradba    701146 Jun 16 23:30 rdpsrptm_p021_13011.trc
ftwldscidb202:RDPSRPTM:/oradump/logs/uw/RDPSRPTM/bdump>

Database version : 10.2.0.4

Tom:

Parse/execute is 128 because there were 128 parallel sessions executed this PARALLEL INSERT.

Is this the reason why 3026574 rows in total in TKPROF against actual "1513287 rows created" ?

1) I removed explain=/ option and now it does not even show execution plan in TKPROF.

2) In the script, i am closing the cursor and have 'exit' at the end. I do not know why i am not getting 'rowsource operation'. can you correct me if i missing anything.

===
..
exec dbms_monitor.client_id_trace_disable(client_id=>'SLFEED');
select 'close the cursor' from dual;
spool off;
exit;
===

Hi Tom

suppose that some sessions fire alter session set skip_unusable_indexes=true;

How can I find out which and how many session fire this command? is there any view for that?

Thanks!

Yes. Check V$SES_OPTIMIZER_ENV view.

-Thiru

Hi Tom
thanks, but I checked the view exists in 10g, not in 9i?

how to see the that information in 9i?

Tom:

<< run query, exit, login, disable trace (for future sessions)

I tried this way and but still no "row source operation" in tkprof.

I am on 10.2.0.4. Can you tell how to get 'row source operation' from AWR.

thanks

Tom:

When i ran trace parallel query, "i set statistics_level=ALL" at session level.

At oracle instance level:

SQL> show parameter statistics 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE

Tom

How to make parallel query sessions inherit the session settings ?

Before i run my parallel insert, i invoke the parallelism as shown below:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 32 ;
ALTER SESSION FORCE PARALLEL DML PARALLEL 32 ;

Hi Tom,

I have a JAVA application that uses connection pooling.
How can we take a 10046 or any other trace of such session?

Thanks.
lalu.

Hi Tom,

CREATE OR REPLACE TRIGGER logon_trace
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'WAS_USER'
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS=''10046 trace name context forever, level 8''';
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

I use this logon trigger but surprisingly there is no trace being generated in udump directory. What could be reason?

related parameter in db:


trace_enabled boolean TRUE
tracefile_identifier string prd03

Thanks,
Ani

Thanks Tom. I will clearly keep it in mind in future. As you said , I have given Alter session grant directly. Not thru any ROLE. Still no result.Am I missing something?

Regards,
Ani

Hi,Tom

Why I can not find the trace file after the below statements?

session #1 log in as sys user
SQL> exec dbms_system.set_ev(46,21,10046,12,'TEST');

PL/SQL procedure successfully completed.

session #2 log in as test user
SQL> select /*+ i_am_here */ count(*) from t;

  COUNT(*)
----------
     75301


session #1 
SQL> exec dbms_system.set_ev(46,21,10046,0,'TEST');

PL/SQL procedure successfully completed.


And,I can find the trace file as sone as I finish the below statements.


session #1
SQL> exec dbms_system.set_ev(46,21,10046,12,'');

PL/SQL procedure successfully completed.


session #2
SQL> select /*+ do_u_find_me */ count(*) from t;

  COUNT(*)
----------
     75301


session #1
SQL> exec dbms_system.set_ev(46,21,10046,0,'');

PL/SQL procedure successfully completed.


I don't know the difference between this two statements? "exec dbms_system.set_ev(46,21,10046,0,'')" 
and
"exec dbms_system.set_ev(46,21,10046,0,'TEST')"

Thanks for your time!

Hi Tom,

Is there any way to set tracefile identifier in another session using dbms_system, dbms_session, dbms_support, dbms_monitor etc?


Thanks a lot,
AL SWAMY