【YashanDB知識庫】繫結引數，同一個sql多個執行計劃的問題

YashanDB發表於2024-07-26
SQL

問題現象

同一個sql有兩個執行計劃，是否合理？

它的EXECUTIONS，ELAPSED_TIME等統計資訊怎麼看，是獨立分開的還是統一計算的？

如下圖：

問題影響版本

tpcc測試：23.2.1.100

問題的風險及影響

影響EXECUTIONS等sql統計資訊的計算

問題發生原因

同一條sql，特別是繫結引數的sql，引數型別不同，會導致生成不同的執行計劃。

分析及處理過程

例子測試分析

復現例子：

drop table if exists tmp2;
create table tmp2(c1 int,c2 double,c3 int,c4 int,c5 int,c6 int,c7 int,c8 timestamp,c9 varchar(500),c10 int) ;

如上，第二個引數是int和double時，生成了兩個執行計劃，有兩個plan_hash_value，如下圖：

後跑幾次，發現其中executions、cpu_time這些值共享，統計到一起，所以兩條結果的這些欄位值一樣。

程式碼分析

select * from v$sql; 呼叫過程

v$sql是動態表，資料都存放在記憶體中。

anrDedicatedServiceNoLogin
anrResponse
anrDirectExecute
anlExecute
doAnlExecute
doExecute
fetchQuery
fetchPlanWithPreProj
anlFetchPlan
doAnlFetchPlan
fetchSelectPlan
anlFetchPlan
doAnlFetchPlan
fetchTableFullScan
ankFetch
ankFtFetch
{
ftSqlFetch //獲取一條v$sql資料
ankExecFilter //filter篩選
}

ftsqlFetch 資料：

  • 每一條sql都是一個anlcontext物件，存放在sqlpool中。

  • anlGetNextNewPlan(stmt, ctxCursor)，透過這個介面從sqlpool中，根據poolid和bucketid從sqlpool中取的anlcontext，根據anlcontext的plan取資料。

  • 一個plan一條資料，每條資料的plan_hash_value都不一樣。

  • 統計資料存放在anlcontext中的stats：iostat、timestat、runstat、gcstat

所以同一條sql不同的plan，stats資料其實相同。

anlcontext管理

sqlpool有兩個：
typedef enum EnAnlPoolType {
    ANL_MAIN_POOL = 0, -- 普通sql pool
    ANL_PL_POOL, -- pl sql pool
    __ANL_POOL_TYPE_COUNT__
} AnlPoolType;
 
//生成一個新sql的anlcontext，呼叫流程
doParseDML
anlHashSQL // 生成sql hash
anlTryReuseContext // 從 inst->sqlpool，buckets中比較已有的anlcontext，是否有同一個sql
anlCreateContext // 沒有從sqlpool中找到，從ANL_MAIN_POOL中申請新的anlcontext
anlPoolInsert // anlcontext插入pool中，anlLruInsert lru插入
 
//第二條執行計劃，呼叫流程
//sql的第二個plan，在anlexecute中生成。放在anlcontext的planContexts中
execExplain
replaceNewPlan
doReplaceNewPlan
anlCreateAndUseNewPlan
anlInsertPlan

pool記憶體池分配方式

SGA總記憶體分配介面：anrCreateGlobalArea

SGA總記憶體組成方式：data buff + vm buff + large pool + redo buff + shared pool + dbwr buff + audit buff + app pool + hot cache + pq pool +job pool

globalArea：anrStartInstance時一次性申請如下表空間資料，

typedef enum EnMzoneId {
    MZONE_DATA_BUFFER = 0, // 配置引數：DATA_BUFFER_SIZE attr->dataBufSize（64M）
    MZONE_TEMP_BUFFER, // 配置引數：VM_BUFFER_SIZE attr->tempBufSize (32M)
    MZONE_LARGE_POOL, // 配置引數：LARGE_POOL_SIZE attr->largePoolSize（16M）
    MZONE_LOG_BUFFER, // 配置引數：REDO_BUFFER_SIZE attr->logBufSize（8M）
    MZONE_HOT_CACHE, // 配置（隱藏）引數：_HOT_CACHE_SIZE attr->hotCacheSize（16M）
    MZONE_SHARE_POOL, // 配置引數：SHARE_POOL_SIZE attr->sharePoolSize（256M）
    MZONE_APP_POOL, // 配置引數：WORK_AREA_POOL_SIZE attr->appPoolSize（16M）
    MZONE_DBWR_BUFFER, // 配置引數：DBWR_BUFFER_SIZE attr->dbwrBufSize（4M）
    MZONE_JOB_POOL, // 預設4M
    MZONE_PQ_POOL, // 配置（隱藏）引數：PQ_POOL_SIZE attr->pqPoolSize（parallel execute buff 預設：16M）
    MZONE_AUDIT_BUFFER, // 配置引數：AUDIT_QUEUE_SIZE attr->auditQueueSize（16M）
    MZONE_COUNT,
} MzoneId;

shared pool 分配

分配介面：setShareBuffers

shared pool記憶體拆分：sql pool + dc pool + lock pool + cursor pool等

typedef enum EnSharePoolItemId {
    SHARE_SQL_POOL = 0, // 配置（隱藏）引數：SQL_POOL_SIZE attr->sqlPoolBuf  （sqlPoolSize：百分比預設50）
    SHARE_DC_POOL, //  配置（隱藏）引數：DICTIONARY_CACHE_SIZE profile->dictCache （dictCacheSize：百分比預設25）
    SHARE_LOCK_POOL, //  配置（隱藏）引數：LOCK_POOL_SIZE profile->lockPool（lockPoolSize：16M）
    SHARE_CURSOR_POOL, //  配置（隱藏）引數：CURSOR_POOL_SIZE profile->cursorPool（cursorPoolSize：32M）
    SHARE_DSTB_POOL, //  配置（隱藏）引數：DSTB_POOL_SIZE  （分散式，dstbPoolSize：預設百分比0 ）
    SHARE_GCS_RESOURCE, // 叢集（主備）才有  根據dataBufSize的block數計算出來
    SHARE_GLS_RESOURCE, // 叢集才有   根據dataBufSize的block數計算出來
    SHARE_GRC_REQUEST, // 叢集才有   根據lockPoolSize計算
    SHARE_GCS_PASTCOPY, // 叢集才有  根據 maxHandlers計算
    SHARE_COUNT, // 剩餘 profile->sharePool中
} SharePoolItemId;

sql pool buf分配：

呼叫介面：anlCreateSQLPool

sql pool組成：main pool buff + pl pool buff

ANL_MAIN_POOL： (9/10) * sqlpoolsize
   mainPoolSize = attr->sqlPoolSize - plPoolSize;
    AnlPool* mainPool = &inst->sqlPool[ANL_MAIN_POOL];
 
ANL_PL_POOL：(1/10) * sqlpoolsize
        #define ANL_PL_POOL_MEMORY_PCT (CodUint64)10
        #define ANL_PL_POOL_SIZE(totalSize) ((totalSize) * ANL_PL_POOL_MEMORY_PCT / 100)
        plPoolSize = ANL_PL_POOL_SIZE(attr->sqlPoolSize)

sql buff分配

static void setSQLBuffers()
{
    AnlAttr* attr = anlGetAttr(gInstance->sql);
    attr->appPoolBuf = g_MemoryZones[MZONE_APP_POOL].buffer;
    attr->jobPoolBuf = g_MemoryZones[MZONE_JOB_POOL].buffer;
    attr->jobPoolSize = g_MemoryZones[MZONE_JOB_POOL].size;
    attr->pqPoolBuf = g_MemoryZones[MZONE_PQ_POOL].buffer;
    attr->auditQueueBuf = g_MemoryZones[MZONE_AUDIT_BUFFER].buffer;
}

kernel buff分配

static void setKernelBuffers()
{
    KernelAttr* profile = ankGetKernelAttr(gInstance->kernel);
    profile->dataBuf = g_MemoryZones[MZONE_DATA_BUFFER].buffer;
    profile->logBuf = g_MemoryZones[MZONE_LOG_BUFFER].buffer;
    profile->tempBuf = g_MemoryZones[MZONE_TEMP_BUFFER].buffer;
    profile->hotCache = g_MemoryZones[MZONE_HOT_CACHE].buffer;
    profile->largePool = g_MemoryZones[MZONE_LARGE_POOL].buffer;
    profile->dbwrBuf = g_MemoryZones[MZONE_DBWR_BUFFER].buffer;
}

快取相關檢視

經驗分享

1、同一個sql，有多個執行計劃，是正常現象

2、v$sql中的executions、cpu_times等一些統計欄位，同一個sql都是同樣的值，不能做加減等操作。

相關文章