Home » RDBMS Server » Performance Tuning » Monitor performance
Monitor performance [message #630178] Mon, 22 December 2014 02:57 Go to next message
Banurs
Messages: 5
Registered: December 2014
Junior Member
Hi,

Can you please suggest some tools to monitor the performance in oracle, like sql profiler in sql server.

Thank you,
Banu
Re: Monitor performance [message #630184 is a reply to message #630178] Mon, 22 December 2014 05:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I use statspack reports or (if the client has Enterprise Edition plus Diagnostic Pack licences) AWR reports.

[Updated on: Mon, 22 December 2014 05:40]

Report message to a moderator

Re: Monitor performance [message #630209 is a reply to message #630178] Tue, 23 December 2014 01:20 Go to previous messageGo to next message
pvsarat
Messages: 10
Registered: October 2012
Location: CHENNAI
Junior Member
You can use OEM as well for performance bottel neck
Re: Monitor performance [message #630267 is a reply to message #630209] Tue, 23 December 2014 21:35 Go to previous messageGo to next message
Banurs
Messages: 5
Registered: December 2014
Junior Member
Thank you I will look into it.
Re: Monitor performance [message #630268 is a reply to message #630267] Tue, 23 December 2014 21:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Thank you I will look into it.

Upon what criteria will you use to differentiate good SQL from bad SQL?
Re: Monitor performance [message #630309 is a reply to message #630268] Wed, 24 December 2014 05:39 Go to previous messageGo to next message
Banurs
Messages: 5
Registered: December 2014
Junior Member
I am fairly new to oracle I was given a task of analyzing the reason for slow running website with oracleDB. I am looking for a tool like sql profiler in sql server to monitor all the functions and stored procedures that is being called when users are using the website to identify when it is taking long cpu time. Can you pls give me some suggestions.
Re: Monitor performance [message #630311 is a reply to message #630309] Wed, 24 December 2014 05:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I suggest you speak to the developers and find out what sql it is which is causing the problem.
Re: Monitor performance [message #630327 is a reply to message #630309] Wed, 24 December 2014 08:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Banurs wrote on Wed, 24 December 2014 03:39
I am fairly new to oracle I was given a task of analyzing the reason for slow running website with oracleDB. I am looking for a tool like sql profiler in sql server to monitor all the functions and stored procedures that is being called when users are using the website to identify when it is taking long cpu time. Can you pls give me some suggestions.


John Watson wrote on Mon, 22 December 2014 03:40
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I use statspack reports or (if the client has Enterprise Edition plus Diagnostic Pack licences) AWR reports.

Re: Monitor performance [message #630333 is a reply to message #630327] Wed, 24 December 2014 10:52 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Before diving into the the morass of performance analyzing tools, I'd start by asking if the performance degradation was sudden or gradual. Stale/badly out-of-date stats are a regular cause of performance problems. Not trying to start a war on performance tuning approach, but understanding when the issue started, which users experience it and when can save a lot of time in tracing the cause... I agree that AWR reports are excellent if you know what you're looking at.

[Updated on: Wed, 24 December 2014 10:54]

Report message to a moderator

Re: Monitor performance [message #630393 is a reply to message #630333] Thu, 25 December 2014 21:56 Go to previous messageGo to next message
Banurs
Messages: 5
Registered: December 2014
Junior Member
Thank you all
Re: Monitor performance [message #630404 is a reply to message #630393] Fri, 26 December 2014 07:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
You are assuming that your problem is with Oracle. The description provided of the task was "analyzing the reason for slow running website with oracleDB". Your first step should be to determine what percentage of a round trip is spent in Oracle and how much outside. You should be using a typical or at least repeatable task with the website. Break down the transaction into pieces that can be monitored. Your people should have network monitoring tools that can tell you this percentage. Then you know if the problem you are fixing is Oracle or not. Once you know this you can spent time in the right place.

Given your description though it may be that someone has already done this. If so, ask for the timings on NON-ORACLE databases doing the same queries for proof.

Or your could just assume it is Oracle and look at the TOP-N SQL to see if any of it is part of your web transaction. Here is a link to the scripts provided by my new book on SQL Tuning called - "Oracle SQL Performance Tuning and Optimization: Its all about the Cardinalities". The scripts are free so share them. One of the scripts is my top-n script. It will show you most expensive SQL on each instance for the database you are connected to.

Also, if you do decide that SQL is your problem and you need to tune it, I have attached the first chapter of my new book on SQL tuning here. This is the promotional chapter but it is the full chapter and it too is free so share it please. It will teach you about the role of Cardinality in SQL Tuning and give you more tools to speed your tuning work. You do NOT need to buy the book to use the scripts and share the promotional chapter and scripts with others.

Kevin

added scripts file

[Updated on: Sat, 03 January 2015 09:32]

Report message to a moderator

Re: Monitor performance [message #630459 is a reply to message #630404] Sun, 28 December 2014 21:34 Go to previous message
Banurs
Messages: 5
Registered: December 2014
Junior Member
Thank you, I will check it out.
Previous Topic: Library cache (PIPES)
Next Topic: Optimisation of query sql
Goto Forum:
  


Current Time: Thu Mar 28 18:19:51 CDT 2024