優化SQL集一

来源:https://www.cnblogs.com/oracle-ziyuhou/archive/2019/05/13/10858570.html
-Advertisement-
Play Games

-->尋找低效的SQL語句 ,下麵的語句主要適用於從視圖v$sqlarea中獲得當前運行下且耗用buffer_gets較多的SQL語句 SELECT executions , disk_reads , buffer_gets , ROUND( ( buffer_gets - disk_reads ) ...


-->尋找低效的SQL語句 ,下麵的語句主要適用於從視圖v$sqlarea中獲得當前運行下且耗用buffer_gets較多的SQL語句                   
SELECT executions                                                                     
     , disk_reads                                                                    
     , buffer_gets                                                                  
     , ROUND( ( buffer_gets         
               - disk_reads )       
             / buffer_gets, 2 )      
          hit_ratio                                      
     , ROUND( disk_reads / executions, 2 ) reads_per_run                   
     , sql_text                                       
FROM   v$sqlarea                                                               
WHERE      executions > 0                                                   
       AND buffer_gets > 0                                               
       AND ( buffer_gets                                                    
            - disk_reads )                                                  
           / buffer_gets < 0.80                                                        
ORDER BY 4 DESC;   



--查詢低效的sql

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,
ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,
   SQL_TEXT
FROM   V$SQLAREA
WHERE  EXECUTIONS>0
AND  BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;




 1.查看總消耗時間最多的前10條SQL語句
 select *
from (select v.sql_id,
v.child_number,
v.sql_text,
last_load_time,
v.PARSING_USER_ID,
ROUND(v.ELAPSED_TIME / 1000000 / (CASE
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
                1
               ELSE
                EXECUTIONS
             END),
             2) "執行時間'S'",
 v.SQL_FULLTEXT,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v  ) a
where elapsed_rank <= 100  and   last_load_time > to_char(sysdate - 1/1440, 'YYYY-MM-DD/HH:MI:SS')    order by "執行時間'S'" desc

查詢最近一分鐘內最慢的SQL:

select executions, cpu_time/1e6 as cpu_sec, elapsed_time/1e6 as elapsed_sec, round(elapsed_time/sqrt(executions)) as important, v.*
from v$sql v
where executions > 10 and last_load_time > to_char(sysdate - 1/1440, 'YYYY-MM-DD/HH:MI:SS')  
order by important desc

2.查看CPU消耗時間最多的前10條SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

3.查看消耗磁碟讀取最多的前10條SQL語句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;



一、查詢執行最慢的sql
    
select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "執行次數",
        round(sa.ELAPSED_TIME / 1000000, 2) "總執行時間",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均執行時間",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用戶ID",
        u.username "用戶名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

二、查詢次數最多的 sql
    
select *
 from(selects.SQL_TEXT,
        s.EXECUTIONS"執行次數",
        s.PARSING_USER_ID"用戶名",
        rank() over(orderbyEXECUTIONS desc) EXEC_RANK
     fromv$sql s
     leftjoinall_users u
      onu.USER_ID = s.PARSING_USER_ID) t
 whereexec_rank <= 100;



select * from v$sql_monitor where sql_text is not null;


select * from v$sql t
where t.LAST_ACTIVE_TIME>(sysdate - interval '1' MINUTE)  --執行1分鐘內的SQL語句
--      and t.PARSING_SCHEMA_NAME = 'LSBIKE' --資料庫
--      and (t.MODULE is null or t.MODULE not like '%PL/SQL%') --不是在某些終端里的執行
--      and lower(t.SQL_TEXT) like '%select%' --查詢某類SQL語句
order by t.LAST_ACTIVE_TIME desc;


--TOP SQL
select *  
  from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",  
              round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",  
               sqt.exec,  
               round(decode(sqt.exec,  
                      0,  
                      to_number(null),  
                      (sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",  
               round((100 *  
               (sqt.elap / (select sum(e.value) - sum(b.value)  
                               from dba_hist_sys_time_model b,  
                                    dba_hist_sys_time_model e  
                              where b.snap_id = &beg_snap and  
                                    e.snap_id = &end_snap and  
                                    b.dbid = &dbid and  
                                    e.dbid = &dbid and  
                                    b.instance_number = &inst_num and  
                                    e.instance_number = &inst_num and  
                                    e.stat_name = 'DB time' and  
                                    b.stat_name = 'DB time'))) ,2)norm_val,  
               sqt.sql_id,  
               decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,  
               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText  
          from (select sql_id,  
                       max(module) module,  
                       sum(elapsed_time_delta) elap,  
                       sum(cpu_time_delta) cput,  
                       sum(executions_delta) exec  
                  from dba_hist_sqlstat  
                 where dbid = &dbid and  
                       instance_number = &inst_num and  
                        snap_id > &beg_snap and  
                       snap_id <= &end_snap  
                 group by sql_id) sqt,  
               dba_hist_sqltext st  
         where st.sql_id(+) = sqt.sql_id and  
               st.dbid(+) = &dbid  
         order by nvl(sqt.elap, -1) desc,  
                  sqt.sql_id)  
 where rownum < 65 and  
       (rownum <= 10 or norm_val > 1);

--

select a.EVENT, a.SQL_ID, a.MACHINE, b.SQL_TEXT, b.SQL_FULLTEXT,b.FIRST_LOAD_TIME,b.LAST_LOAD_TIME,b.LAST_ACTIVE_TIME
  from v$session a, v$sql b
where a.SQL_ID = b.SQL_ID
   and a.USERNAME is not null
   and a.STATUS = 'ACTIVE';

 

--查看正在執行的sql執行計劃

--display_cursor     為第一步查出來的SQL_ID

select * from table(dbms_xplan.display_cursor('7k0dhtw1zudrw'))


--等待事件以及語句情況
select  event,sql_id, mi, count(mi)
  from (select substrb(event,1,30) event, sql_id, to_char(sample_time, 'yyyymmdd hh24mi') mi --,
        --session_id
          from dba_hist_active_sess_history
         where sql_id = '5s1x1tmt570pn'
           and sample_time > to_date('20190513 0940', 'yyyymmdd hh24mi')
           and sample_time < to_date('20190513 1910', 'yyyymmdd hh24mi'))
 group by  event,sql_id, mi
  order by mi ;



enq: SQ - contention
5s1x1tmt570pn    SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410299 ||  LPAD(SEQ_BKE010.NEXTVAL, 10, '0')  ) FROM DUAL
7pycct8f0sur2    SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410299 ||  LPAD(SEQ_AAZ217.NEXTVAL, 10, '0')  ) FROM DUAL
2bxcdvtcvykcv    SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410200 || LPAD(SEQ_BKZ522.NEXTVAL, 10, '0') ) FROM DUAL

--看等待事件的
SELECT t2.sid,
  t2.SERIAL#,
  t1.SPID OS_PID,
  t3.SQL_ID,
  t2.EVENT,
  t2.P1TEXT,
  t2.P1,
  t2.p2TEXT,
  t2.P2,
  t2.p3TEXT,
  t2.P3,
  t3.SQL_FULLTEXT
FROM v$process t1,
  v$session t2,
  v$sql t3
WHERE t1.ADDR = t2.PADDR
AND t2.STATUS = 'ACTIVE' -- 'INACTIVE'
and t2.SQL_ID = t3.SQL_ID
and t2.event not like 'SQL%';

select t2.sid, t2.SERIAL#, -- 庫級唯一定位一個session t1.SPID OS_PID, -- 操作系統的PID t3.SQL_ID, t2.EVENT,-- SQLID和等待事件 t2.P1TEXT, t2.P1, -- 等待事件的P1信息 t2.p2TEXT, t2.P2, -- 等待事件的P2信息 t2.p3TEXT, t2.P3, -- 等待事件的P3信息 t3.SQL_FULLTEXT -- 被執行的SQL完整內容 from v$process t1, v$session t2, v$sql t3 where t1.ADDR = t2.PADDR and t2.STATUS = 'ACTIVE' -- 表示當前正在執行SQL的會話
-- and t2.STATUS = 'INACTIVE'
-- 表示當前等待執行SQL的會話
-- and t2.STATUS = 'KILLED'
-- 表示當前會話正在被殺掉，未提交事務強制回滾
and t2.SQL_ID = t3.SQL_ID; -- t2.sid, t2.SERIAL#      可以用作庫級殺死會話：alter system kill session 't2.sid, t2.SERIAL#';
-- t1.SPID OS_PID          可以用作系統級殺死會話：kill -9 t1.SPID
-- t2.EVENT 和 P1、P2、P3  不同的等待事件的P1、P2和P3的信息是不盡相同的，具體要查官檔
-- t3.SQL_FULLTEXT         是一個CLOB類型的欄位


--1.從V$SQLAREA視圖中選出最糟糕的查詢
select b.username username,a.disk_reads reads,
       a.executions exec,a.disk_reads / decode (a.executions, 0, 1,a.executions) rds_exec_ratio,
       a.sql_text STATEMENT
from   v$sqlarea a, dba_users b
where  a.parsing_user_id = b.user_id
and    a.disk_reads > 10000
order by a.disk_reads desc;

--2.從V$SQL視圖中選出最糟糕的查詢
select *
from  (select address,  --address替換sql_text
       rank() over (order by buffer_gets desc ) as rank_bufgets,
       to_char(100 * ratio_to_report(buffer_gets) over (), '999.99') pct_buf
       from v$sql )
where rank_bufgets < 11;



declare
  tune_task varchar2(30);
  tune_sql clob;
begin
  tune_task := dbms_sqltune.create_tuning_task(
    sql_id    => '6v864r3vc9qbc',
    task_name   => 'tune_test2',
    description => 'Provide SQL ID'
  );
end;
/





--執行DBMS_SQLTUNE並查看建議
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');

set long 10000 longchunksize 10000 linesize 150 pagesize 200
select dbms_sqltune.report_tuning_task('tune_test2') from dual;

--查看和刪除調優任務
select owner,task_name,advisor_name,created from dba_advisor_tasks order by created;

exec dbms_sqltune.drop_tuning_task(task_name => '&&task_name');


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • iTerm2快速SSH連接並保存密碼
    背景 Mac自帶terminal，以及比較好用的iTerm2命令行工具，都缺乏一個功能，就是遠程SSH連接，無法保存密碼。一種方法是將本機的ssh_key放到遠程伺服器中實現無密碼登錄。這種方法在很多情況下無法實現，因為遠程伺服器大多是客戶的。本文介紹一個簡單、輕量級的命令行工具——sshpass， ...
  • ArchLinux 添加開機啟動腳本, 親測
    以下命令以 用戶執行 1.創建一個啟動service腳本 2.創建 /etc/rc.local 文件 3.添加執行許可權 4.添加/etc/rc.local.d文件夾 5.設置開機自啟 結尾: 把sh腳本放在 文件夾中就行 ...
  • LNMP安裝與配置之Nginx
    Nginx簡介 Nginx (engine x) 是一個高性能的HTTP和反向代理web伺服器，同時也提供了IMAP/POP3/SMTP服務。Nginx是由伊戈爾·賽索耶夫為俄羅斯訪問量第二的Rambler.ru站點（俄文：Рамблер）開發的，第一個公開版本0.1.0發佈於2004年10月4日。 ...
  • PostgreSQL 務實應用（二/5）插入衝突
    在項目中，有時會動態地按周期（如按月）封存統計數據，通常需要做這樣的處理： 以按月封存為例，當月數據到達時，先需要檢查該月是否有過記錄，有則以更新的方式累加統計數字，無則添加一條記錄。 假設我們創建以下月封存表 month_stat，欄位 month_name 表示月份，欄位 total_count ...
  • 事務的隔離級別（併發產生的錯誤）
    事務的特性： 原子性 一致性 持久性 隔離性 併發產生的錯誤： 五、事務的隔離級別 多個線程開啟各自事務操作資料庫中數據時，資料庫系統要負責隔離操作，以保證各個線程在獲取數據時的準確性。 5.1、事務不考慮隔離性可能會引發的問題 如果事務不考慮隔離性，可能會引發如下問題： 1、臟讀 臟讀指一個事務讀 ...
  • 計算字元串由幾個單片語成
    字元串由幾個單片語成，一般情況之下以空格分隔來計算即可。 實現方法，參考下麵自定義函數： SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Insus.NET -- Create date: 2019-05-13 -- Up ...
  • 字元串中去除多餘的空格保留一個(MS SQL Server)
    大約2年前，寫過一篇《字元串中去除多餘的空格保留一個(C#)》https://www.cnblogs.com/insus/p/7954151.html 今天，Insus.NET使用MS SQL Server來實現相同的功能。現Insus.NET已經把它寫成一個自定義函數。 SET ANSI_NULL ...
  • SQL-查詢
    1 查詢語句 查看賬戶下的所有表 1 select * from tab; 查看賬戶下的所有表的詳細信息 1 select * from user_tables; 1.1 select select 用於從數據看查詢數據。語法： 1 select field1,filed2,.. . 2 from ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類