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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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 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;
Since you are doing this within a session you do not need SID, SERIAL# just call
DBMS_SUPPORT.START_TRACE;
DBMS_SUPPORT.STOP_TRACE;
ASKER
Thanks,
I ran :
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
and got this:
VALUE
-------------------------- ---------- ----
/u00/oracle/admin/pone6/ud ump
1 row selected
How do I access that, and will it give me the query when I access it?
I ran :
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
and got this:
VALUE
--------------------------
/u00/oracle/admin/pone6/ud
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_sessio n ('||SID||','||SERIAL#||',T RUE);' from v$session where username='xxx';
spool off
spool trace_off.sql
select 'dbms_system.set_sql_trace _in_sessio n ('||SID||','||SERIAL#||',F ALSE);' 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
spool trace_on.sql
select 'dbms_system.set_sql_trace
spool off
spool trace_off.sql
select 'dbms_system.set_sql_trace
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/ud ump 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.
Then as Jacek stated use tkprof <infile> <outfile>.
The trace file should have all sql ran in that session.
ASKER
How do I go to the /u00/oracle/admin/pone6/ud ump file?
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