数据库中是隐式转换往往是性能的杀手，下面2个语句分别可以在sql server和oracle查询到目前在内存中的，使用了隐式转换的SQL：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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)') |
|
1 2 |
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 |