腳本之家服務器常用軟件

快捷導航

達夢數(shù)據(jù)庫查詢較慢的快速診斷及優(yōu)化

更新時間：2025年10月29日 08:32:50 作者：百代繁華一朝都-綺羅生

在達夢數(shù)據(jù)庫中,慢SQL查詢不僅會影響系統(tǒng)的性能,還會降低用戶體驗,這篇文章主要介紹了達夢數(shù)據(jù)庫查詢較慢的快速診斷及優(yōu)化的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

1 快速診斷：定位瓶頸所在

1.1 查看當前慢SQL

– 最慢的 20 條 SQL 統(tǒng)計

SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

–查詢慢 SQL 及阻塞

SELECT
        DS.SESS_ID "被阻塞的會話ID",
        DS.SQL_TEXT "被阻塞的SQL",
        DS.TRX_ID "被阻塞的事務ID",
        (CASE L.LTYPE WHEN 'OBJECT' THEN '對象鎖' WHEN 'TID' THEN '事務鎖' END CASE ) "被阻塞的鎖類型",
        DS.CREATE_TIME "開始阻塞時間",
        SS.SESS_ID "占用鎖的會話ID",
        SS.SQL_TEXT "占用鎖的SQL",
        SS.CLNT_IP "占用鎖的IP",
        L.TID "占用鎖的事務ID"
FROM
        V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
        DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
        SS.TRX_ID = L.TID
WHERE
        L.BLOCKED = 1;

1.2 檢查系統(tǒng)資源狀態(tài)

– 查看緩存命中率

數(shù)據(jù)緩沖區(qū)是 DMSERVER 在將數(shù)據(jù)頁寫入磁盤之前以及從磁盤上讀取數(shù)據(jù)頁之后，數(shù)據(jù)頁所存儲的地方。

數(shù)據(jù)緩沖區(qū)設定得太小，會導致緩沖頁命中率低，磁盤 IO 頻繁；將其設定得太大，又會導致操作系統(tǒng)內(nèi)存本身不夠用。

select
      name 緩沖池名稱,
      sum(page_size)*sf_get_page_size 緩沖池大小_G,
      sum(rat_hit) /count(*) 命中率
from
      v$bufferpool
group by name;

2 SQL語句優(yōu)化

2.1 分析執(zhí)行計劃

– 對慢SQL生成執(zhí)行計劃

EXPLAIN SELECT * FROM your_table WHERE your_conditions;

執(zhí)行計劃重點關注：

是否使用了合適的索引（INDEX SCAN）

避免全表掃描（CROSS INDEX SCAN）

注意排序操作（SORT）是否必要

2.2 常見SQL優(yōu)化技巧

– 避免 SELECT *，只取需要的字段

SELECT id, name FROM users WHERE status = 1;  -- 好
SELECT * FROM users WHERE status = 1;         -- 差

– 為查詢條件字段添加索引

CREATE INDEX idx_users_status ON users(status);

– 避免在WHERE條件中對字段進行函數(shù)操作

SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01';  -- 差
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01';  -- 好

– 使用EXISTS代替IN（大數(shù)據(jù)量時）

SELECT * FROM table1 WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table1.id = table2.id
);

3. 索引優(yōu)化策略

3.1 檢查缺失索引

– 查找可能需要索引的表

頻繁用于查詢條件的列所在的表：如果某個表的列經(jīng)常出現(xiàn)在 WHERE 子句中作為查詢條件，該表可能需要索引。
主鍵或唯一性約束的表：達夢數(shù)據(jù)庫會自動為這些列創(chuàng)建索引，但如果需要額外的查詢優(yōu)化，可以手動添加其他索引。
連接操作中使用的列所在的表：如果某個表經(jīng)常在 JOIN 操作中使用，可以為連接列創(chuàng)建索引以提高性能。
數(shù)據(jù)量較大的表：對于數(shù)據(jù)量較大的表，適當?shù)乃饕梢燥@著減少查詢時間。
排序或分組操作中使用的列所在的表：如果某個表的列經(jīng)常用于 ORDER BY 或 GROUP BY 操作，可以考慮創(chuàng)建索引。

3.2 創(chuàng)建合適的索引

在以下場景下可考慮創(chuàng)建索引：

僅當要通過索引訪問表中很少的一部分行（1%~20%）。
索引可覆蓋查詢所需的所有列，不需額外去訪問表。

存在下列情況將導致無法使用索引：

組合索引中，條件列中沒有組合索引的首列。
條件列帶有函數(shù)或計算。
索引排序是按照字段值進行排序的，字段值通過函數(shù)或計算后的值索引無法獲取。
索引過濾性能不好時。

4. 數(shù)據(jù)庫參數(shù)優(yōu)化

4.1 關鍵性能參數(shù)檢查

內(nèi)存相關
BUFFER = 10000 # 緩沖區(qū)大小(MB)，建議系統(tǒng)內(nèi)存的50-70%
MAX_BUFFER = 10000 # 最大緩沖區(qū)
SORT_BUF_SIZE = 200 # 排序緩沖區(qū)(MB)
HJ_BUF_SIZE = 200 # HASH連接緩沖區(qū)(MB)

#并行處理
PARALLEL_POLICY = 1 # 啟用并行
PARALLEL_THREAD_NUM = 4 # 并行線程數(shù)

#優(yōu)化器相關
OPTIMIZER_MODE = 1 # 優(yōu)化器模式

5. 統(tǒng)計信息維護

5.1 更新統(tǒng)計信息

– 更新表的統(tǒng)計信息

CALL SP_TAB_STAT_INIT('SCHEMA_NAME', 'TABLE_NAME');

– 更新整個模式的統(tǒng)計信息

CALL SP_DB_STAT_INIT('SCHEMA_NAME');

– 收集列統(tǒng)計信息

CALL SP_COL_STAT_INIT('SCHEMA_NAME', 'TABLE_NAME', 'COLUMN_NAME');

5.2 定期維護腳本

舉例：

– 更新模式 DB_TEST 下的統(tǒng)計信息

DBMS_STATS.GATHER_SCHEMA_STATS('DB_TEST', 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');

– 更新特定表的統(tǒng)計信息

DBMS_STATS.GATHER_TABLE_STATS('username', 'table_name', null, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO', cascade => true, degree => 8);

– 更新所有表和索引的統(tǒng)計信息并記錄日志

drop table if exists my_tab_stats;
create table my_tab_stats(table_name varchar2(100),owner varchar2(100),stat_flag varchar2(5),begin_time timestamp,end_time timestamp);
insert into my_tab_stats(table_name,owner) select table_name,owner from dba_tables where owner in ('OA');
update my_tab_stats set stat_flag = 0;
commit;
begin
    for tb in (select rowid,table_name,owner from my_tab_stats where stat_flag = 0 ) 
	loop
        update my_tab_stats set begin_time=sysdate where my_tab_stats.table_name=tb.table_name and my_tab_stats.owner=tb.owner;
        dbms_stats.gather_table_stats(tb.owner,tb.table_name,null,100,true,'FOR ALL COLUMNS SIZE AUTO',cascade => true, degree => 8);
        update my_tab_stats set stat_flag = 1,end_time=sysdate where my_tab_stats.table_name = tb.table_name and my_tab_stats.owner=tb.owner;
        commit;
    end loop;
end;

6. 架構和設計優(yōu)化

6.1 分區(qū)表優(yōu)化

– 對大表進行分區(qū)
CREATE TABLE sales (
sale_id INT,
sale_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE(sale_date)
(
PARTITION p2023 VALUES LESS THAN (‘2024-01-01'),
PARTITION p2024 VALUES LESS THAN (‘2025-01-01')
);

6.2 物化視圖

– 為復雜查詢創(chuàng)建物化視圖

CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS 
SELECT customer_id, SUM(amount) as total_amount,
       COUNT(*) as order_count
FROM sales 
GROUP BY customer_id;