Link to home
Start Free TrialLog in
Avatar of liltyga
liltyga

asked on

Analyze Queries in Oracle?

Hello,

I'm at a new job, and have been tasked with reverse engineering an application that was written in ASP , and convert it to JSP.  There are approximately 25 tables, and lots of data.  The main problem that I'm having is to reconstruct the query that was used in the ASP application, because we don't have the actual code.  I'm going thru each table, and trying to make sense of how to duplicate the application, and I only have the printoouts of the result screen to work with.

I know on a SQL database, you can use SQL Profiler to run the ASP code after connecting to the database, and see what query was performed.  Can I do the same kind of thing when connecting to an Oracle 9i database?

Thanks in advance for any suggestions you can offer,

lil
Avatar of Bigfam5
Bigfam5

You can try

select a.sid, a.serial#, a.terminal, b.sql_Text from v$session a, v$sqltext b
where a.sql_address = b.address
    and a.username <> 'SYS'  -- if you know the username change to a.username = 'USERNAME'
order by b.address, b.piece
Set sql trace on for your application sessions. Then run tkprof on produced trace files and you will have all the executed queries (plus their perfomance).
Thanks a great idea sjacek.  Also don't forgot to set timed_statistics on
ASKER CERTIFIED SOLUTION
Avatar of DrJekyll
DrJekyll

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of liltyga

ASKER

Ok, I'm a little new to this - can I set the sql trace within my JSP code,. or do I need to do this from Toad or SQLplus?
Avatar of liltyga

ASKER

You know,

I actually didn't mean to accept this answer quite yet - I only wanted to post another comment - I was able to successfully run that query in Oracle, but I still don't know how to decipher the query in the application.  Please help!
I do not know JSP but I would think you would call the dbms_support package just like you call any other.
Since you are doing this within a session you do not need SID, SERIAL# just call
DBMS_SUPPORT.START_TRACE;
DBMS_SUPPORT.STOP_TRACE;
Avatar of liltyga

ASKER

Thanks,

I ran :
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'

and got this:

VALUE                                  
----------------------------------------
/u00/oracle/admin/pone6/udump          
1 row selected


How do I access that, and will it give me the query when I access it?


To start sql trace for specific user (for your application) use following:

spool trace_on.sql
select 'dbms_system.set_sql_trace_in_session ('||SID||','||SERIAL#||',TRUE);' from v$session where username='xxx';
spool off
spool trace_off.sql
select 'dbms_system.set_sql_trace_in_session ('||SID||','||SERIAL#||',FALSE);' from v$session where username='xxx';
spool off

Then edit created spool files. You have to add 'begin' and 'end' to make it pl/sql and run trace_on.sql from system account via sqlplus. This will start sql trace for all the sessions for xxx Oracle user.
Do whatever you want need with your application (to execute relevant queries).
Then run trace_off.sql to stop sql trace.

This operation will create set of sql trace files in the 'user_dum_dest' directory (you have it from your previous post).

Use tkprof on those trace files to make it into readable format:
   tkprof <infile> <outfile>

All your queries are in created outfiles.

Cheers,

Jacek
You need to go to the /u00/oracle/admin/pone6/udump and find the correct .trc file for that session.
Then as Jacek stated use tkprof <infile> <outfile>.
The trace file should have all sql ran in that session.
Avatar of liltyga

ASKER

How do I go to the /u00/oracle/admin/pone6/udump file?