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