-
To search the most cost SQL statement , follow SQL is used
select sql_TEXT,
EXECUTIONS,
ROWS_PROCESSED,
BUFFER_GETS/100,
DISK_READS/100
FROM V$sql V,
(SELECT SUM(BUFFER_GETS) TOT_GETS FROM V$SQL) S
WHERE BUFFER_GETS > TOT_GETS * 0.1
ORDER BY BUFFER_GETS DESC
It shows the SQL statement which consume more than 10% of total buffer gets since db startup .
I have few question on the result ,
A. EXECUTIONS means what , the result show one query runs nearly 1000 times ( it's application code not oracle inner SQL) , But actually it should not be run this much of times . So what's the meaning of executions , Is it time elapsed or the number of times executed ?
B. Some SQL statement is truncated in the result . what's the problem ?
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
1.Executions means no of times the same statement was executed.
2.Your sql_text column is truncated because its has a max limit and if it execeds this limit the sql text would be truncated.
regards
anandkl
anandkl
-
I could under at technical point of view , but actually I am sure this sql won't be execute so many times as only few users
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
It's not lying to you. It is telling you the amount of executions made against the object since it was bought into the library cache.
How long has the database been up and how long has the object been in the library cache ?
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Around 4 hours and only 10 users access database . THat's why I feel very confused on the execution number ( It can not be 1000 times)
Any idea
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
as for the truncated sql, you can get the full sql statement from v$sqltext_with_newlines, according to the hash_value.
I think oracle will maybe show less executions than actual, it won't show you more.Less executions because maybe once it was ever aged out.
1000 executions is not something very much. You have 10 users and running 4 hours.If there is a loop, it is possible that 1000 execuitons can be done in 1 minutes. Of course it totally depend on the application.
www.cnoug.org
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|