您好,登錄后才能下訂單哦!
1、查看 alert_PROD.log
【錯誤信息】:ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1
查看臨時表空間基礎信息及其使用情況:
基礎信息查看:
select dtf.TABLESPACE_NAME, dtf.file_name, dtf.BYTES / 1024 / 1024 as "MB" from dba_temp_files dtf order by dtf.TABLESPACE_NAME;
使用情況查看:
SELECT d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.900') "Size (G)", TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024 / 1024, '99999999.999') "HWM (G)", TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % ", TO_CHAR(NVL(t.bytes / 1024 / 1024 / 1024, 0), '99999999.999') "Using (G)", TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
通過查看臨時表空間組中的表空間可以判斷,該表空間處于異常狀態(tài)。
2、啟動ORA-1652跟蹤錯誤
在session級別啟用數據庫的跟蹤:
ALTER SESSION SET EVENTS '1652 trace name errorstack';
在系統級別啟用數據庫的跟蹤:
ALTER SYSTEM SET EVENTS '1652 trace name errorstack';
把該參數寫入到spfile文件中:
ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE;
3、查看alert_PROD.log中錯誤日志
Errors in file /ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc: more /ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc
----- Error Stack Dump ----- ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1 ----- Current SQL Statement for this session (sql_id=cfrvgzfkpxp49) ----- 注:中間為需要優(yōu)化的代碼 ----- PL/SQL Stack -----
4、打印SQL執(zhí)行計劃
pl/sql Developer 工具按F5.
5、分析執(zhí)行計劃
全表掃描(Table access full)的子句。
通過SQL優(yōu)化的規(guī)則分析該字句。
6、對該SQL重要部分在Test系統中進行優(yōu)化測試。
(1)執(zhí)行優(yōu)化前:
(2)創(chuàng)建索引
create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS(nvl(CPMD.TRANSACTION_ID,0));
(3)修改后
7、檢查Test系統中進行優(yōu)化結果。
優(yōu)化前后對比
名稱 | 優(yōu)化前 | 優(yōu)化后 |
掃描方式 | TABLE ACCESS FULL | INDEX RANGE SCAN |
Cost | 12,735 | 844 |
IO cost | 12,681 | 842 |
CPU cost | 447,695,496 | 17,435,302 |
8、在Prod系統中進行部署
(1)備份 CUX_PA_MAT_A_DETS_COLLECT_PKG到本地
(2)創(chuàng)建索引
create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS(nvl(CPMD.TRANSACTION_ID,0));
(3)進行優(yōu)化,并添加注釋
A、WHERE CPMD.TRANSACTION_ID is not null 替換為 WHERE NVL(CPMD.TRANSACTION_ID,0) >0 B、 AND MTLN.LOT_NUMBER NOT LIKE '%|%' 替換為: AND INSTR(MTLN.LOT_NUMBER,'|') < 1 C、 AND T.ACCOUNT_NAME LIKE '%出庫%'; 替換為: AND INSTR(T.ACCOUNT_NAME,'出庫') > 0 ;
(4)進行代碼檢查
檢查項:a、檢查修改代碼是否正確
b、檢查源代碼是否已經注釋
C、檢查是否在修改代碼后添加注釋(包括時間、功能、修改人員)
(5)執(zhí)行代碼,重新提交請求
9、執(zhí)行查詢操作,查看結果
整體優(yōu)化前后對比:
名稱 | 優(yōu)化前 | 優(yōu)化后 |
掃描方式 | TABLE ACCESS FULL | INDEX RANGE SCAN |
Cost | 51,651 | 751 |
IO cost | 51,423 | 740 |
CPU cost | 1,885,854,949 | 94,466,486 |
9、檢查alert_PROD.log中是否出現ORA-1652錯誤
Beginning log switch checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707 Tue Mar 20 16:19:47 2018 Thread 1 advanced to log sequence 2249 (LGWR switch) Current log# 1 seq# 2249 mem# 0: /data/prod/proddata/log01a.dbf Current log# 1 seq# 2249 mem# 1: /data/prod/proddata/log01b.dbf Tue Mar 20 16:19:51 2018 Archived Log entry 2248 added for thread 1 sequence 2248 ID 0x1556bb36 dest 1: Tue Mar 20 16:24:52 2018 Completed checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707 Tue Mar 20 16:27:28 2018 Incremental checkpoint up to RBA [0x8c9.a420.0], current log tail at RBA [0x8c9.21670.0] Tue Mar 20 16:47:32 2018 Incremental checkpoint up to RBA [0x8c9.32675.0], current log tail at RBA [0x8c9.3d64f.0] Tue Mar 20 16:58:03 2018 Tue Mar 20 17:00:03 2018 Closing scheduler window Closing Resource Manager plan via scheduler window Clearing Resource Manager plan via parameter Tue Mar 20 17:07:35 2018 Incremental checkpoint up to RBA [0x8c9.547b9.0], current log tail at RBA [0x8c9.5979d.0] Tue Mar 20 17:27:37 2018 Incremental checkpoint up to RBA [0x8c9.5eece.0], current log tail at RBA [0x8c9.609cf.0]
10、關閉ORA-1652跟蹤事件。對應的關閉腳本如下:
ALTER SESSION SET EVENTS '1652 trace name context off'; ALTER SYSTEM SET EVENTS '1652 trace name context off'; ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID = '*';
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。