SqlServer & Oracle 查询执行计划中有隐式转换的sql

最新推荐文章于 2023-04-24 21:13:31 发布

Hehuyi_In

最新推荐文章于 2023-04-24 21:13:31 发布

阅读量1.7k

点赞数

分类专栏： Oracle SQLServer 执行计划

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/93192192

版权

Oracle 同时被 3 个专栏收录

189 篇文章 33 订阅

订阅专栏

SQLServer

142 篇文章 25 订阅

订阅专栏

执行计划

10 篇文章 1 订阅

订阅专栏

一、 SqlServer

查看执行计划中含有隐式转换的sql（在对应数据库执行）

注意以下sql都较复杂，在数据库中执行时间可能较长

-- 查询有隐式转换的语句，转换字段源及目标类型

DECLARE @dbname SYSNAME  
SET @dbname = QUOTENAME(DB_NAME());  
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,  
         ic.DATA_TYPE AS ConvertFrom ,  
         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,  
         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,  
         t.value('(@Length)[1]', 'int') AS ConvertToLength ,  
         query_plan  
FROM sys.dm_exec_cached_plans AS cp  
--FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp  
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )  
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )  
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')  
    AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')  
    AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')  
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 
and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')

-- 按各种消耗排序

DECLARE @dbname SYSNAME  
SET @dbname = QUOTENAME(DB_NAME());  
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  
SELECT top 100 stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,  
         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,  
         ic.DATA_TYPE AS ConvertFrom ,  
         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,  
         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,  
         t.value('(@Length)[1]', 'int') AS ConvertToLength ,  
         query_plan,
         qs.sql_handle,
       qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec
--FROM sys.dm_exec_cached_plans AS cp  
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp  
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )  
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )  
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')  
    AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')  
    AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')  
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 
and ic.DATA_TYPE != t.value('(@DataType)[1]', 'varchar(128)')
order by qs.total_worker_time desc

查到的另一个版本

-- 找到含有隐式转换且会导致index seek变为index scan的语句
–find all the plans and query which are being compared with wrong data type in queries and thus casuing the conversion implicit or explicit on column which has index and this in turn is causing the index scan instead of index seek

declare @databasename sysname
set @databasename= quotename(db_name())
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Cnvrt.value('(@DataType)[1]','sysname') as ConvertedDataType
,ty.name as ColumnDataType
--,Cnvrt.value('(@Implicit)[1]','sysname') as IsImplicit
--,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
--,ps.total_row_count
--,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
--,ps.used_page_count
--,PhysicalOperator.value('(.//Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
--,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
qs.creation_time,
qs.last_execution_time,
qs.total_worker_time,
qs.total_elapsed_time
  /*     qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec */
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Convert') as Conversion(Cnvrt)
cross apply Cnvrt.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
inner join sys.all_columns ac on t.object_id = ac.object_id and ac.name = ltrim(rtrim(ColDetails.value('(@Column)[1]','sysname')))
left join sys.types ty on ac.user_type_id = ty.user_type_id
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
--left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id)    as ps on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
Cnvrt.value('(@Implicit)[1]','sysname')=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc

找出列上带有函数转换且会导致index seek变为index scan的语句

-- 找出列上带有函数转换且会导致index seek变为index scan的语句
-- find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek


declare @databasename sysname
set @databasename= quotename(db_name())
--find all the plans and query which are using the function call on the column which has index and this in turn is causing the index scan instead of index seek
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 100
ColDetails.value('(@Schema)[1]','sysname') as SchemaName
,ColDetails.value('(@Table)[1]','sysname') as TableName
,ColDetails.value('(@Column)[1]','sysname') as ColumnName
,ColDetails.value('(@Alias)[1]','sysname') as TableAliasInQuery
,PhysicalOperator.value('(.//Object/@Index)[1]','sysname') as IndexName
,Intrinsic.value('(@FunctionName)[1]','varchar(200)') as FunctionNameUsed
,PhysicalOperator.value('(.//IndexScan/Predicate/ScalarOperator/@ScalarString)[1]','nvarchar(max)') as PredicateHavingFunction
,qs.execution_count * ps.total_row_count as MostImportantFactor_total_row_processed
,ps.total_row_count
,qs.execution_count * used_page_count as MostImportantFactor_possible_number_of_logical_reads
,ps.used_page_count
,cast(substring(cast(st.text as nvarchar(max)),qs.statement_start_offset/2 + 1,(case when qs.statement_end_offset = -1 then datalength(cast(st.text as nvarchar(max))) else qs.statement_end_offset end - qs.statement_start_offset)/2) as nvarchar(max)) as QueryText
,datalength(st.text) SQLtextLength
,st.text FullSqltext
,qp.query_plan as QueryPlan
,cp.usecounts,cp.refcounts,cp.objtype
,qs.sql_handle,
       qs.statement_start_offset,
       qs.statement_end_offset,
       qs.plan_handle,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.query_hash,
       qs.query_plan_hash,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.total_rows,
       qs.total_worker_time/ qs.execution_count as worktime_per_exec,
       qs.total_elapsed_time / qs.execution_count as elasp_per_exec,
       qs.total_logical_reads / qs.execution_count as logiread_per_exec,
       qs.total_physical_reads / qs.execution_count as phyread_per_exec,
       qs.total_rows / qs.execution_count as rows_per_exec
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
cross apply query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as Statements(IndividualStatement)
cross apply IndividualStatement.nodes('.//RelOp[@PhysicalOp="Index Scan" or @PhysicalOp="Clustered Index Scan"]') as RelOp(PhysicalOperator)
cross apply PhysicalOperator.nodes('.//ScalarOperator/Intrinsic') as Conversion(Intrinsic)
cross apply Intrinsic.nodes('./ScalarOperator/Identifier/ColumnReference') as Cols(ColDetails)
inner join sys.tables t on quotename(t.name) = ltrim(rtrim(ColDetails.value('(@Table)[1]','sysname'))) and t.type = 'U'
inner join sys.indexes id on t.object_id = id.object_id
and quotename(id.name) = ltrim(rtrim(PhysicalOperator.value('(.//Object/@Index)[1]','sysname')))
left join sys.dm_exec_query_stats qs on cp.plan_handle= qs.plan_handle
left join (select object_id,index_id,sum(used_page_count) as used_page_count, sum(row_count) as total_row_count from sys.dm_db_partition_stats group by object_id,Index_id)    as ps
on id.object_id = ps.object_id and id.index_id = ps.index_id
outer apply sys.dm_exec_sql_text(cp.plan_handle) st
where
1=1
and ColDetails.exist('.[@Database=sql:variable("@databasename")]') =1
and cp.cacheobjtype = 'Compiled Plan'
order by qs.total_worker_time desc

二、 Oracle

Oracle中找到隐式转换的方法

SELECT sql_id,plan_hash_value FROM v$sql_plan x WHERE  x.FILTER_PREDICATES LIKE '%INTERNAL_FUNCTION%'
GROUP BY sql_id,plan_hash_value

oracle的执行计划中，如果带上INTERNAL_FUNCTION，表示可能有隐式转换

参考

https://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs
https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://www.cnblogs.com/wuxiaoqian726/articles/2015519.html
https://blogs.msdn.microsoft.com/apgcdsd/2012/08/01/sql-serverscan-seek/
http://www.cnblogs.com/kerrycode/p/4806236.html
https://blog.csdn.net/kk185800961/article/details/42708311
http://www.cnblogs.com/CareySon/p/4255449.html

Hehuyi_In

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SqlServer & Oracle 查询执行计划中有隐式转换的sql

查看执行计划中含有隐式转换的sql（在对应数据库执行）注意以下sql都较复杂，在数据库中执行时间可能较长-- 查询有隐式转换的语句，转换字段源及目标类型DECLARE @dbname SYSNAME SET @dbname = QUOTENAME(DB_NAME()); WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft....
复制链接

扫一扫