How to locate important (resource-intensive) SQL "Go" in Oracle

Source: Internet
Author: User
Tags sorts

1. View questionable SQL

Selectsubstr (To_char (s.pct,'99.00'),2)||'%'load, S.executions executes, P.sql_text from(Selectaddress, disk_reads, executions, pct, rank () over (order by dis K_reads desc) Ranking from(Selectaddress, disk_reads, executions, -*Ratio_to_report (Disk_reads) over () pct fromSys.v_$sqlwherecommand_type!= -)        whereDisk_reads> -*executions) s, Sys.v_$sqltext pwheres.ranking<=5and p.address=S.addressorder by1, S.address, p.piece;

2. View SQL that consumes more memory

Select B.username,       A. Buffer_gets,       a.executions,       01, a.executions),       A.sql_text SQL  fromwhere a.parsing_user_id = b.user_id     10000 ORDER BY disk_reads Desc;

3. View logical read-more SQL

Select*from (select  buffer_gets, Sql_text         from  V$sqlarea         where buffer_gets>500000        ORDER BY buffer_gets Desc)where rownum<=;

4. See more executions of SQL

Select sql_text, executions    from (SelectfromwhereBayi;

5. See more SQL for hard disk read

Select Sql_text, Disk_reads  from (Select from v$sqlarea ORDER by disk_reads Desc) where rownum<;

6. View the SQL with many sorts

Select Sql_text, sorts  from (Select from v$sqlarea order by sorts Desc) where rownum<;

7, the analysis of too many times, the number of executions is too few, to use the method of binding variables to write SQL

Select 1  the " SQL " " Totexecs "   from  where51) having count (30   2;

How to locate important (resource-intensive) SQL "Go" in Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.