Home » SQL & PL/SQL » SQL & PL/SQL » How to find High impact sql query in terms of CPU utilization (Oracle 10g / windows-2000 service pack-2)
How to find High impact sql query in terms of CPU utilization [message #340959] Fri, 15 August 2008 04:53 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hello

How to find High impact sql query in terms of CPU utilization?

Questions
-------------
1)How to get PID of high impact sql query in windows operating system ?
(I tried with Ctrl+Alt+Del ---> got a PID but it does not match with any value of v$process-PID column)

2)How to relate this PID with v$process,v$session & v$sqlarea
to get sql_text part of query ?

Thanking in advance
sbmk_design

[Updated on: Fri, 15 August 2008 04:57]

Report message to a moderator

Re: How to find High impact sql query in terms of CPU utilization [message #340963 is a reply to message #340959] Fri, 15 August 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) If you search client PID it is process in v$session
2)
v$process.addr = v$session.paddr
v$session.sql_id = v$sqlarea.sql_id

Regards
Michel
Re: How to find High impact sql query in terms of CPU utilization [message #341048 is a reply to message #340959] Fri, 15 August 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
> How to find High impact sql query in terms of CPU utilization?
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Re: How to find High impact sql query in terms of CPU utilization [message #341375 is a reply to message #340959] Mon, 18 August 2008 10:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I wonder some times what I am missing by not using AWR.

If you are looking for an old school approach, look at these to start:

desc v$sqlarea
desc v$open_cursor
desc v$session
desc v$process


There will show you where the info you are looking for lives. You will have to do the proper joins, and you should understand what each of these contains before you use them, but a simple GOOGLE on these view names will give you this information.

Good luck, Kevin.
Re: How to find High impact sql query in terms of CPU utilization [message #341379 is a reply to message #340959] Mon, 18 August 2008 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I wonder some times what I am missing by not using AWR.
IMO, you are not missing much at all.
A different view of the elephant can be obtained via Top Activity screen using Oracle Enterprise Manager.
Re: How to find High impact sql query in terms of CPU utilization [message #341385 is a reply to message #341379] Mon, 18 August 2008 11:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Anacedent, it is always good to here opinions from those with experience in a matter. I appreciate your comment.

Kevin
Re: How to find High impact sql query in terms of CPU utilization [message #341785 is a reply to message #340959] Wed, 20 August 2008 05:46 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
But in the interviews no body is ready to accept answers using Oracle Enterprise Manager. They are asking full query (or) various steps involved in finding High impact query.

Regards
sbmk_design
Re: How to find High impact sql query in terms of CPU utilization [message #341790 is a reply to message #341785] Wed, 20 August 2008 06:00 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sbmk_design wrote on Wed, 20 August 2008 12:46
But in the interviews no body is ready to accept answers using Oracle Enterprise Manager. They are asking full query (or) various steps involved in finding High impact query.

Regards
sbmk_design

This is wrong. Some accept this answer.

Regards
Michel

[Updated on: Wed, 20 August 2008 06:00]

Report message to a moderator

Previous Topic: Storing Chinese Character
Next Topic: How to generate the spool (.LST) file using PL/SQL?
Goto Forum:
  


Current Time: Tue Dec 06 04:51:01 CST 2016

Total time taken to generate the page: 0.07815 seconds