2019年8月 – OracleBlog

数据库序列的监控

2019-08-03 没有评论

需要监控数据库的序列，在达到最大值前，进行告警。特别是mysql，往往因为字段的定义和auto incremental的定义不同，导致各自的上限不同。 Oracle：

SELECT 
x.*,
CASE WHEN increment_by<0
 THEN round(last_number/min_value*100,4)
WHEN increment_by>0
  THEN round(last_number/max_value*100,4)
 ELSE 0
  END 
AS percent_usage
from DBA_SEQUENCES x  WHERE cycle_flag='N'
ORDER BY percent_usage DESC;

1

2

3

4

5

6

7

8

9

10

11

SELECT

x.*,

CASE WHEN increment_by<0

THEN round(last_number/min_value*100,4)

WHEN increment_by>0

THEN round(last_number/max_value*100,4)

ELSE 0

END

AS percent_usage

from DBA_SEQUENCES x WHERE cycle_flag='N'

ORDER BY percent_usage DESC;

SQL Server: [crayon-6844fa6152374 […]

..experience, Working case

隐式转换检查

2019-08-03 没有评论

数据库中是隐式转换往往是性能的杀手，下面2个语句分别可以在sql server和oracle查询到目前在内存中的，使用了隐式转换的SQL： sql server 隐式转换：

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

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)')

oracle隐式转换： [crayon-6844fa6153 […]

Study note

小记scn head room

2019-08-03 没有评论

小记一下前段时间看的scn head room的问题。 1. scn的最大值。scn的表示是SCN_WRAP.SCN_BASE，最大值是 ffff.ffffffff，即65535.4294967295，也就是每当scn_base到ffffffff（或者说4294967295）的时候，scn wrap […]