那些年，我們處理過的SQL問題

1、并行引起的災(zāi)禍

一大早，某網(wǎng)省兄弟告訴我，數(shù)據(jù)庫會(huì)話執(zhí)行的SQL開啟了并行，導(dǎo)致負(fù)載很高，會(huì)話也高，查了半天，沒找到具體原因，也不知道該如何解決？

對于他的問題，我直接回應(yīng)了：這還不清楚嗎？常見原因無非有以下兩個(gè)：

第一：對象開啟了并行（包括索引和表）

第二：SQL語句里面使用了PARALLEL的HINTS

現(xiàn)場兄弟說，都查了并沒有上面的情況，聽到他的回答，我首先對他查詢的方式持懷疑態(tài)度的，沒有設(shè)置并行度，也沒有加HINTS，執(zhí)行的SQL怎么會(huì)并行執(zhí)行呢？帶著這個(gè)疑問，我叫現(xiàn)場兄弟把查詢結(jié)果一一截圖給我，如下（文中案例都是事后補(bǔ)充）：

看到結(jié)果后我一時(shí)也有點(diǎn)摸不著頭腦，怎么回事？遇到問題我總是告訴自己要冷靜，不急。

2、層層推進(jìn)，分析問題

是不是什么參數(shù)控制了？

SQL> show parameter parallel

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fast_start_parallel_rollback         string      LOW

parallel_adaptive_multi_user         boolean     TRUE

parallel_automatic_tuning            boolean     FALSE

parallel_degree_limit                string      CPU

parallel_degree_policy               string      MANUAL

parallel_execution_message_size      integer     16384

parallel_force_local                 boolean     FALSE

parallel_instance_group              string     

parallel_io_cap_enabled              boolean     FALSE

parallel_max_servers                 integer     320

parallel_min_percent                 integer     0

parallel_min_servers                 integer     0

parallel_min_time_threshold          string      AUTO

parallel_server                      boolean     FALSE

parallel_server_instances            integer     1

parallel_servers_target              integer     128

parallel_threads_per_cpu             integer     2

recovery_parallelism                 integer     0

沒有發(fā)現(xiàn)可疑參數(shù)。

至此，表面排查的結(jié)果已經(jīng)解決不了這個(gè)問題了，于是我讓現(xiàn)場找了一條正在并行的SQL ，手動(dòng)執(zhí)行，并收集一個(gè)10053事件trace，看看是否能有新發(fā)現(xiàn)。腳本如下 

很快現(xiàn)場提供了TRACE FILE文件給我，我優(yōu)先看參數(shù)列表。

這時(shí)，我發(fā)現(xiàn)一個(gè)可疑的參數(shù)：parallel_query_default_dop  = 16

找到mos上關(guān)于該參數(shù)的相關(guān)信息，是一個(gè)默認(rèn)并行度的參數(shù)，該參數(shù)值的算法如下：

DEFAULT DOP = cpu_count * parallel_threads_per_cpu* cluster_database_instances

我立刻問現(xiàn)場同事，執(zhí)行的SQL在活動(dòng)會(huì)話中體現(xiàn)的是不是16個(gè)并行進(jìn)程?，F(xiàn)場同事答復(fù)我，觀察到的基本就是。至此問題明朗起來了，執(zhí)行的SQL使用了默認(rèn)并行度執(zhí)行，受參數(shù)parallel_query_default_dop控制。既然是默認(rèn)的并行度，那也應(yīng)該需要設(shè)置(如果不設(shè)置，默認(rèn)是1)。于是我把前期的查詢驗(yàn)證對象并行度是否開啟的SQL改造了下，具體如下（文中案例都是事后補(bǔ)充） 

 查詢結(jié)果截圖發(fā)出來，我就開心了，這里明顯有一個(gè)設(shè)置了并行度為DEFAULT（如果我們不設(shè)置就是1）的表和索引。然后確認(rèn)了他們正是正在運(yùn)行的sql中的對象。

3、問題解決

既然設(shè)置了默認(rèn)并行度，那么只需要取消默認(rèn)并行度即可，即執(zhí)行如下SQL

--針對表

alter table table_name noparalle;

--針對索引

Alter index index_name noparallel;

于是我叫現(xiàn)場把對象并行度修改為1，再次執(zhí)行該SQL，發(fā)現(xiàn)并行消失了，數(shù)據(jù)庫恢復(fù)了正常。

問題雖然解決了，但還有一個(gè)疑問沒有解開，什么情況下會(huì)設(shè)置的并行度為DEFUALT呢？正常創(chuàng)建索引和表都是1。

4、如何設(shè)置并行度為default

通過實(shí)踐發(fā)現(xiàn)如下2種方式可以實(shí)現(xiàn)并行度設(shè)置為DEFAULT。

1、創(chuàng)建表的時(shí)候指定:

    2、創(chuàng)建表之后可以修改

 小結(jié)：該問題解決第一個(gè)是思路 ,第二個(gè)是基本功要扎實(shí)。

1

背景如下：某網(wǎng)省采集中間庫從10.2.0.4升級到11.2.0.4（備注升級不是在老的機(jī)器上面直接升級，而是在新機(jī)器上面采用安裝遷移的方式）

升級完第二天現(xiàn)場找到我，說以前同步檔案數(shù)據(jù)的接口功能目前都運(yùn)行非常慢（數(shù)據(jù)接口同步的方式采用的DBLINK），有時(shí)甚至無法正常運(yùn)行完，影響檔案資料的同步，看來已經(jīng)很嚴(yán)重了。

關(guān)鍵字：DB升級從10G升級到11G
我以前遇到過相關(guān)案例，覺得可能是升級帶來的執(zhí)行計(jì)劃變化引起的。于是告知現(xiàn)場嘗試修改優(yōu)化器參數(shù)即optimizer_features_enable改成10.2.0.4，可以在線改，立刻生效，腳本如下：
alter system set optimizer_features_enable='10.2.0.4' scope=both;
修改完成后，重新在執(zhí)行同步檔案資料接口的任務(wù)看是否正常。
現(xiàn)場經(jīng)過一番測試之后，問題沒有解決，看來老的經(jīng)驗(yàn)無法解決該問題。
好，接下來我們做了以下模擬測試：
該SQL的文本如下：

INSERT INTO EPCT.C_CUST_ADDR@EPEXDB
(CUST_ID,
CUST_ADDR,
PROVINCE_CODE,
CITY_CODE,
COUNTY_CODE,
STREET_CODE,
VILLAGE_CODE,
ROAD_CODE,
COMMUNITY_CODE,
PLATE_NO,
TYPE_CODE,
POSTALCODE,
CA_ID,
APP_NO)
SELECT A2.CUST_ID,
A2.CUST_ADDR,
A2.PROVINCE_CODE,
A2.CITY_CODE,
A2.COUNTY_CODE,
A2.STREET_CODE,
A2.VILLAGE_CODE,
A2.ROAD_CODE,
A2.COMMUNITY_CODE,
A2.PLATE_NO,
A2.TYPE_CODE,
A2.POSTALCODE,
A2.CA_ID,
''
FROM SGPM.C_CUST_ADDR A2
WHERE A2.CUST_ID=ANY
(SELECTA3.CUST_ID
FROM SGPM.C_CONS A5,
SGPM.R_CP_CONS_RELA A4,
SGPM.C_CUST A3
 WHERE A4.CONS_ID=A5.CONS_ID
 AND A4.CP_NO=:B1
 ANDA5.CUST_ID=A3.CUST_ID);
可以看到是用到DBLINK從A數(shù)據(jù)庫到B數(shù)據(jù)庫的插入語句，這個(gè)SQL發(fā)起端在A數(shù)據(jù)庫，也就是程序部署在A數(shù)據(jù)庫中，而該SQL實(shí)際執(zhí)行端在B數(shù)據(jù)庫。雖然是往B數(shù)據(jù)庫插入數(shù)據(jù)，但是會(huì)派生一個(gè)查詢SQL到A數(shù)據(jù)庫取數(shù)。
針對INSERT INTO remote_table@dblink select * from local_table這種SQL執(zhí)行端都會(huì)在遠(yuǎn)端，不是本地，無法使用HINTS driving_site指定執(zhí)行端。
2、然后會(huì)在A數(shù)據(jù)庫確認(rèn)一下是否派生一個(gè)SQL，并且找到該SQLID
3、現(xiàn)場提供SQLID之后，我們可以獲取該sql執(zhí)行的相關(guān)信息： 

select *from table(dbms_xplan.display_cursor('1ar4us01aj0hu',null,'ADVANCED'));

紅框里出現(xiàn)的字樣引起了我的注意，眼尖的DBA應(yīng)該很快會(huì)發(fā)現(xiàn)其中的貓膩。

對的，這里調(diào)用了一個(gè)內(nèi)部函數(shù)。這個(gè)函數(shù)的說明如下：

The internal Oracle function SYS_OP_C2C performs conversion
from one character set to another character set C(haracterSet)2C(haracterSet).

字符集之間的轉(zhuǎn)換。OK ，看到這里我問現(xiàn)場，新舊兩套B數(shù)據(jù)庫中字符集是什么? A數(shù)據(jù)庫字符集又是什么?

現(xiàn)場答復(fù)如下：

老的B數(shù)據(jù)庫字符集是utf-8

新的B數(shù)據(jù)庫字符集是zhs16gbk

而A數(shù)據(jù)庫字符集是utf-8

這個(gè)也就說明了，遷移到新采集中間庫之后性能急劇下降的原因找到了。

那么解決方式有如下2種方式：

第一修改字符集，保證源目標(biāo)字符集一致。 

第二創(chuàng)建函數(shù)索引。

2