Home » Other » Training & Certification » Sort activity (Oracle 9.2.0.8)
Sort activity [message #350841] Fri, 26 September 2008 12:41 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello, I am just trying to write the procedure which will run in the database to capture hit ratio,
library cache hit ratio, sort activity etc.


Here is the query i will be using. But when i google it, i see different queries in different links.
I just want to confirm this is the correct one to find out the memory sort.


SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 
       0,1,(a.VALUE + b.VALUE)),2) "Percent memory sorts"
FROM   v$sysstat a, 
       v$sysstat b
WHERE  a.name = 'sorts (disk)'
AND    b.name = 'sorts (memory)'
Re: Sort activity [message #350849 is a reply to message #350841] Fri, 26 September 2008 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is correct or not, it depends on what you want.
What are the other ones you saw?
If post them, we can tell what are the differences and then you can choose the one you want.

Regards
Michel
Re: Sort activity [message #350911 is a reply to message #350849] Sat, 27 September 2008 08:50 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello Michel, Thanks for getting back to me.

I wanted to know how much percentage of sorts are
happening on memory versus disk. I wanted to double check with some one before i implement this code.
I found two sql query and i am not sure which one is accurate. Please let me know which one i should use.

SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 
       0,1,(a.VALUE + b.VALUE)),2) "Percent memory sorts"
FROM   v$sysstat a, 
       v$sysstat b
WHERE  a.name = 'sorts (disk)'
AND    b.name = 'sorts (memory)'


I found another one.

select round((mem.value/(mem.value+dsk.value))*100,2)from v$sysstat mem, v$sysstat dsk
where mem.name='sorts (memory)'
and dsk.name='sorts (disk)'


Re: Sort activity [message #350924 is a reply to message #350911] Sat, 27 September 2008 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are same ones except that the second query does not handle the case when there was no sort at all.

Regards
Michel
Re: Sort activity [message #350929 is a reply to message #350924] Sat, 27 September 2008 14:26 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thanks Michel.
Previous Topic: Difference between Oracle 9i and Oracle 10g OCA developer's exam
Next Topic: Oracle Service Contracts Billing Schedule Tables
Goto Forum:
  


Current Time: Fri Mar 29 07:54:51 CDT 2024