1

I need to collect all the SQL queries (SELECT, UPDATE, DELETE, INSERT) which have been used by the application when any order is processed through the application.

If I can get all SQL's for atleast 50 orders processed through the application then I can check that which SELECT, UPDATE, DELETE statements are frequently in use and which tables are being frequently used by the application after finding these information.

I can get to conclusion that on which table I can use partitioning as if I get the whole SQL's with the WHERE clause I can also get to know that which type of partitioning will be better for any particular table and the partitioning.

However it seems to be a hectic exercise as there could be lots of SQL's which the application use but it helps me understand the application and also after this exercise i will be having a scrutiny report of my application behavior with database which can be used by the later employees.

For this till now i have used the DBMS_adivsor package which gives me some tables of my database to be partitioned and when i check the EXPLAIN PLAN of SQL which i used in the DBMS_ADVISOR then it occur to me that tables which are being full table scan in EXPLAIN PLAN the DBMS_ADVISOR told me to partition them.

The thing is that i can not partition the tables based on this information as its a application level partitioning and also my manager will be not convinced by this little information. so i have come up with the ABOVE plan:(

I need to do this to find out the tables where i can perform table partitioning and other performance tuning things like creating index's as i can get the where clause with filter so its like a database tuning and i want to do this as it will help me grow my career in database development.

Please help me out with this scenario.

Will this query give me required information !

select st.command 
  from V$SQLTEXT_WITH_NEWLINES st, SYS.V_$SQL s 
 where st.hash_value = s.hash_value
   and parsing_schema_name = 'NETSERVICOS2CM'
   and s.module = 'JDBC THIN CLIENT';

Tracing for non-dba USER's ----

GRANT SELECT ON SYS.V_$SESSION TO USER;

GRANT SELECT ON SYS.V_$MYSTAT TO USER;

To get the SID and SERAIL#

SELECT sid, serial# FROM SYS.V_$SESSION

WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT);

Then on DBA user execute this --

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>3002,    serial#=>31833,sql_trace=> true);

OR

on no-dba user i am using --

ALTER SESSION SET SQL_TRACE = TRUE;

OR 

EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

Trigger to trace a session for a particular user ----

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN
AFTER LOGON ON DATABASE
WHEN ( USER = 'NETSERVICOS1CM' )
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "net1cm"';
  EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
  EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context     forever, level 12''';
 EXCEPTION
   WHEN OTHERS THEN
   NULL;
 END;

After that to stop trace i am using 

 ALTER SESSION SET EVENTS '10046 trace name context off';

 ALTER SYSTEM SET EVENTS '10046 trace name context off';

As suggested by Derek.

After this you may have multiple trace files to make a consolidate trace file we can use TRCSESS utility --

trcsess output=net1cm_trcsess.trc module="JDBC Thin Client" *net1cm.trc

It will create a single trace file net1cm_trcsess.trc for all trace file generated in my case (with trace file identifier net1cm).

Now we can use TKPROF utility to generate a report which is in human readable form using below command for example ---

tkprof net1cm_trcsess.trc OUTPUT=net1cm_trcsess.txt EXPLAIN=netservicos1cm/netservicos1 SYS=NO

Thanks

Improve this question
1
  • you can use SQL Profiler Commented Oct 12, 2015 at 6:57

2 Answers 2

Reset to default
1

So here is my advise.

You can use several different traces for application context actions, such as INSERT, DELETE, UPDATE, SELECT, or even all actions.

Say you have a PL/SQL program run by an application, or have an OCI call to the database. You would have this oracle code at the module/stored proc level:

dbms_application_info.set_module(<module_name>,'execute');

before you execute the entire code. (After the BEGIN in the code).

or

dbms_application_info.set_module(<module_name>,'UPDATE');

before you do an update SQL statement.

To turn off application context, you would use (before the END;):

dbms_application_info.set_module(NULL,NULL);

Then when you execute the module or run the update statement you would like to trace in the module you would make sure you did this prior to and after the module runs

execute DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( -
service_name => '<service_name>', -
module_name => '<module_name>', -
action_name => DBMS_MONITOR.ALL_ACTIONS, -
waits => TRUE, -
binds => TRUE);

All actions would be traced and you would know exactly where the statement ran and what action was executed.

To turn it off:

execute DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE( -
service_name => '<service_name>', -
module_name => '<module_name>', -
action_name => DBMS_MONITOR.ALL_ACTIONS);

To do this at the session level, you would do the following when 9 is the serial number and 100 is the Sid, for example. (check the syntax).

execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,190,TRUE);

To turn it off:

execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,190,FALSE);

At the database level, (You have to be very careful with this because it will generate a trace for the entire database and can fill up your diagnostic directory on your oracle database. Disclaimer: USE WITH CAUTION).

execute DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits=>TRUE, binds=>TRUE, instance_name=>'<Instance_name>');
execute DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name=>'<instance_name>');
Improve this answer
Sign up to request clarification or add additional context in comments.

21 Comments

Thanks Derek for reply but i have a question here that what if there is no dbms_application_info.set_module(<module_name>,'execute'); set in our PLSQL programs ! Do i need to put them in each of our procedures, functions and packages ? and what if there is a call to only pure SQL's like SELECT, UPDATE, DELETE etc.
@Derek-- I am using execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>3002, serial#=>31833,sql_trace=> true); to race the session but i am doing this FROM a DBA user but when i tried to use it on a NON_DBA user then it gives me error for DBMS_SYSTEM package when i omit this PAKCHGE and USE ALTER SESSION SET SQL_TRACE =TRUE after granting ALETR SESSION permission to user then it dis but i am not able to locate the name of the file trace file while i can do this on a DBA user any suggestion please.
You can use the dbms_application_info.set module at the beginning and end of your stored procedures and it will context (store application session information) in the database v$sql and v$sqlarea tables. If setting that in every stored procedure is too much work. the you can use the DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION.
Tracing requires the DBA permission to run. However, if you have a role such as the OEM_MONITOR and have the diagnostic pack installed on your database, you can collect the historical information from the Automatic Workload Repository with the objects DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT table objects. You still would need to use dbms_applicaton_info in order for you to get application context, but you don't have to use tracing to collect this information in the DBA_HIST* tables.
And as I mentioned above, in the absence of tracing (which pretty much requires a DBA), you can look at the v$sql, v$sqlarea (shared-pool queries) or the DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT tables (historical) to get all the queries that are running on the database. Without setting the dbms_application_info in the stored procedure, you just will not know what modules they are coming from. Anyways, let me know if you need examples from these tables. I'll post another solution. Regards
|
0

You can leverage v$sqltext_with_newlines ,V$SESSION and v$session_longops. You can google with these words and see if these can be useful for your requirements.

Improve this answer

1 Comment

will this query be of any use here ! please see edited question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.