RE: Performance tuning of a system

I'm assuming your question is "HOW to trace for 24hours all queries done by user THIRDPARTYAPP1 and THIRDPARTYAPP2"?

Sounds like you want to trace and capture all SQL statements and trace them at the same time. It seems like you'll need to add a logon trigger to the database/schema for USERNAME=THIRDPARTYAPP1 or USERNAME=THIRDPARTYAPP2 and enable 10046 tracing for those sessions - in this case, each session will generate a specific trace file - not a great option for what you're trying to do, but you'll be able to capture ALL sql statements from those sessions over 24 hours and specific trace files.

Reference:
http://www.freelists.org/post/oracle-l/trace-via-logon-trigger,8 (H/T W. Breitling)

Also I like to add something like the following lines to the trigger:

execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER = "THIRDPARTYAPP1_TRC" ';
execute immediate ' ALTER SESSION SET TIMED_STATISTICS=TRUE ';
execute immediate ' ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED ';

HTH
Chris

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefano Cislaghi Sent: Monday, September 03, 2012 2:56 PM To: Oracle L
Subject: Performance tuning of a system

Hi all,

I'm facing with a nice (at least for me) exercise of performance and tuning. I have an OLTP system must process each transaction in no more than 2 seconds. A transaction is not a single query but consists of some query and store procedure run from an application server. Almost 98% of this complete in less than 2 second. The main concern is that the system is accessed also by third parties application, mainly for enquiries but also for updates. The DBA has never been involved in the evaluation of DML done by third parties applications and there is

Now I would like, as starting point, to trace for 24hrs all queries done by user THIRDPARTYAPP1 and THIRDPARTYAPP2 and its duration in order to understand if some query run by these application might in some case impact the performance of the database; also this may offer an opportunity of review cause there is no complete list of which queries are done by other external applications. Moreover any other hints is well accepted.

Database is 11.2.0.2 under Linux.

Thanks
Ste
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 04 2012 - 07:31:04 CDT