?
Solved

sql_trace

Posted on 2009-12-17
8
Medium Priority
?
1,073 Views
Last Modified: 2013-12-19
I am tryin to set sql trace for a procedure that runs slow first time it is called in the morning.

So i added

begin
execute immediate 'alter session set sql_trace = true;'

...code

execute immediate 'alter session set sql-trace=false';

end;

When i run it from the web using mod_plsql i see
ORA--01031 ORA-01031: insufficient privileges

When i run ti using sql*plus it runs but i see no trace file in the dump directory.

any ideas what to do to get a trace file for TKPROF for the slow request of the procedure.
0
Comment
Question by:sam15
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 26077069
0
 
LVL 18

Accepted Solution

by:
sventhan earned 1000 total points
ID: 26077084
like this below

In sql plus

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

then run code...

ALTER SESSION SET sql_trace = false;

Look for a file in udump.




http://www.orafaq.com/wiki/SQL_Trace
0
 
LVL 7

Expert Comment

by:jocave
ID: 26077174
What is the "it" that you are running?

Are you running the EXECUTE IMMEDIATE from SQL*Plus as part of a named PL/SQL block?  As part of an anonymous PL/SQL block?  Or are you just issuing the ALTER SESSION statement in SQL*Plus?

Presumably, mod_plsql is calling a stored procedure in which you've added the EXECUTE IMMEDIATE, right?  If so, the stored procedure will not see privileges granted to the owner via a role, only those privileges granted directly (assuming it is a definer's rights stored procedure, the default).  I'd wager that the ALTER SESSION privilege has been granted via a role.  An anonymous PL/SQL block in SQL*Plus or an explicit ALTER SESSION will use privileges granted through a role.

If my hunch is right, you just need to

GRANT ALTER SESSION TO <<user that owns the procedure mod_plsql calls>>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sam15
ID: 26077536
I want to do it using the stored procedure that is called via mod_plsql.

I schedule a perl script that calls a URL at 5:00 AM. So i want to create a trace for that and see why it takes a while to run.

yes i think the alter session is granted via a role. i will try direct grant.

But is that why also running the proedure in sql*plus did not create a trace file. Is the code correct in the procedure for turning trace on and off.
0
 
LVL 7

Expert Comment

by:jocave
ID: 26077577
The syntax looks correct, yes.  I'm assuming that you made an error copying and pasting here in the line that turns off tracing.  I assume you meant SQL_TRACE with an underscore rather than SQL-TRACE with a dash.  The code to enable tracing is correct.

You're looking in the USER_DUMP_DEST for the trace file?
0
 
LVL 11

Assisted Solution

by:Andytw
Andytw earned 1000 total points
ID: 26078823
Sam15:
1. In my experience I have found that sometimes the trace file isn't written, or doesn't contain everything in a session until the session disconnects - only then are all cursors closed by Oracle and hence written to the trace file.  In SQL*Plus try exiting the session then look for your tracefile in UDUMP.

2. Also, you can run your PL/SQL code directly.  Instead of invoking a URL:
http:// ... /<your_package>.<your_procedure>

You can invoke the target procedure which MOD_PLSQL runs from the initial URL:
DECLARE
   nm owa.vc_arr;
   vl owa.vc_arr;
BEGIN
   nm(1) := 'WEB_AUTHENT_PREFIX';
   vl(1) := 'WEB$';
   owa.init_cgi_env(nm.count, nm, vl);
END;
/
-- now call your procedure

This means you can now test using SQL*PLus, and wrap your procedure call with ALTER SESSION SET SQL_TRACE statements as below.  On a production system you can use this method to trace your application (providing that the procedure in question has no side-effects e.g. creating an order).



DECLARE
   nm owa.vc_arr;
   vl owa.vc_arr;
BEGIN
   nm(1) := 'WEB_AUTHENT_PREFIX';
   vl(1) := 'WEB$';
   owa.init_cgi_env(nm.count, nm, vl);
END;
/

ALTER SESSION SET SQL_TRACE = true;
EXEC <your_package>.<your_procedure>;
ALTER SESSION SET SQL_TRACE = false;

Open in new window

0
 

Author Comment

by:sam15
ID: 26082628
yes, but the point is that i am not here at 5:00 am to run it.
this is why i have a perl calling a URL and schedule windows scheduler.

I guess the same thing would be done using a dbms job that start at 5:00 am and trace the thing. right.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 26130474
Yes I'd run it using a database job. Remember, using DBMS_JOB you can only run PL/SQL (the code I posted above was a SQL*Plus command script). You'd have to convert that to a PL/SQL procedure. Or since you are using Oracle 10g you could use the scheduler, which allows you to run OS commands.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question