RE: Performance tuning of a system
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <s.cislaghi@xxxxxxxxx>
- Date: Tue, 4 Sep 2012 07:31:04 -0500
Ste,
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:
//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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
