Skip to Main Content
  • Questions
  • tracing all SQL queries which have executed when application fire an order

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, mradul.

Asked: October 12, 2015 - 9:41 am UTC

Last updated: October 12, 2015 - 1:49 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 50K+ times! This question is

You Asked

Hi,

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.

Database version is -

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

and Chris said...

There are numerous options for tracing the SQL. You can enable SQL trace for a given session, client, service or the whole database:

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

If you're licensed for the diagnostics pack, then frequently executed queries will appear in AWR:

https://oracle-base.com/articles/10g/automatic-workload-repository-10g

Neither of these approaches is guaranteed to capture all of the SQL against given tables. Critical statements may be executed during periods you haven't captured. Ensure that you're capturing normal operation and (infrequent) scheduled batch jobs (e.g. month end reporting).

However:

You should base your decisions to partition tables on application performance and maintenance:

http://docs.oracle.com/database/121/VLDBG/GUID-D01AB935-0567-42C5-B21E-FB36BA9C7BAD.htm#VLDBG00101

If you're planning on using it in your production environment purely to grow your knowledge, you're doing it for the wrong reasons.

Improving your skills is good. You should do this in your own sandpit however. This will enable you to identify whether partition is useful for you and make better recommendations to your boss (if appropriate).

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL tracing for application

mradul goyal, October 12, 2015 - 11:06 am UTC

Hi chris,

Thanks for reply.
I am not doing this to improve my skill but if i will not do this then i will not get paid and i want to say that if i do this exercise then it will improve my skills in database performance tuning as i do not have much exposure to database administration activity but have experience in PLSQL and SQL not much but of 1 years.
I will do this first on out test env. then afterwards if things will be okey move to production only.

However for alternate solution i discussed with my boss that i have figure out some tables using DBMS_ADVISOR which you help me out in my previous question but he says that it is little amount of information we not to dig more into out database so i come up with this IDEA !

So my basic need here is to trace all the SQL's used by an application for some amount of time say 1 hrs. For this we have a tool which automatically fire orders and can fire 100 orders in 5 minutes so if i can get all the SQL's for this 5 minutes then i can move forward woth my plan and can make a report like
>which filters are mostly used in the WHERE clause
>Which tables are frequently used for SELECT Statement
>Which tables are frequently used for UPDATE Statement
>Which tables are frequently used for DELETE Statement
like that.

So my basic requirement is to track all the SQL's used by the application.

Chris Saxon
October 12, 2015 - 1:49 pm UTC

You could also take a look at auditing as a way to capture the SQL executed:

https://oracle-base.com/articles/10g/auditing-10gr2

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.