/***************** mysql相关SQL ************/

--查看进程，查看是有锁表；

SHOW PROCESSLIST;

--删除进程

kill id;--锁表进程ID

--查看表信息，CREATE_OPTIONS='partitioned'是分区表

SHOW TABLE STATUS;

--查询字符是否有效数字，0否 1是

SELECT replace('20000','.','') REGEXP '^[0-9]*$' ;

/***************** sql server相关SQL ************/

---查看锁表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName,*

from sys.dm_tran_locks where resource_type='OBJECT';

---删除锁表进程

declare @spid int --声明参数

Set @spid = 57 --锁表进程ID赋值给参数

declare @sql varchar(1000)

set @sql='kill '+cast(@spid as varchar)

exec(@sql);

--查询SQL语句执行情况

SELECT s2.dbid ,

DB_NAME(s2.dbid) AS [数据库名] ,

--s1.sql_handle ,

( SELECT TOP 1

SUBSTRING(s2.text, statement_start_offset / 2 + 1,

( ( CASE WHEN statement_end_offset = -1

THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))

* 2 )

ELSE statement_end_offset

END ) - statement_start_offset ) / 2 + 1)

) AS [语句] ,

execution_count AS [执行次数] ,

last_execution_time AS [上次开始执行计划的时间] ,

total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] ,

last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] ,

min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] ,

max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] ,

total_logical_reads AS [总逻辑读] ,

last_logical_reads AS [上次逻辑读] ,

min_logical_reads AS [最少逻辑读] ,

max_logical_reads AS [最大逻辑读] ,

total_logical_writes AS [总逻辑写] ,

last_logical_writes AS [上次逻辑写] ,

min_logical_writes AS [最小逻辑写] ,

max_logical_writes AS [最大逻辑写]

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

WHERE s2.objectid IS NULL

ORDER BY last_worker_time DESC;

/***************** oracle相关SQL ************/

---查询未提交事务

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id　= o.object_id

AND l.session_id = s.sid;

---杀进程

alter system kill session 'sid,serial#';

--查询死锁语句

SELECT a.* FROM v$sql a inner join v$session b on a.HASH_VALUE=b.SQL_HASH_VALUE

join v$locked_object c on b.SID=c.SESSION_ID;

--查询操作执行进度

select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork

from v$session_longops where sofar!=totalwork;

--查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,

2),

'990.99') "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 4 DESC;

----查询表某时刻的数据，找回被删数据

select * from

table_name as of timestamp to_timestamp('20150610 17:30:00','yyyymmdd hh24:mi:ss');

--恢复删除的表，recyclebin 回收站

--先查询表是否被删除在回收站中

select object_name,original_name,partition_name,type,ts_name,createtime,droptime,A.* from recyclebin A

where original_name='TABLE_NAME';

--恢复回收站中的表

flashback table TABLE_NAME to before drop;

--DML最后操作时间

select max(ora_rowscn),to_char(scn_to_timestamp(max(ora_rowscn)),'yyyy-mm-dd hh24:mi:ss') from TABLE_NAME;

--DDL最后操作时间

select TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS'),a.* FROM user_objects a where object_name='TABLE_NAME';

--查询CLOB对象占用空间，及属于哪个表

SELECT A.TABLE_NAME,

A.COLUMN_NAME,

B.SEGMENT_NAME,

B.SEGMENT_TYPE,

B.TABLESPACE_NAME,

B.BYTES / 1024 / 1024,

B.BLOCKS,

B.EXTENTS

FROM USER_LOBS A, USER_SEGMENTS B

WHERE A.SEGMENT_NAME = B.SEGMENT_NAME

ORDER BY B.BYTES DESC;

----并行设置

--查询表并行设置

select degree from user_tables where table_name='TB_NET_PRICE_DAY_FRONT';

--开启表并行

ALTER TABLE TB_NET_PRICE_DAY PARALLEL;

ALTER TABLE TB_NET_PRICE_DAY_FRONT PARALLEL;

--取消表并行

alter table TB_NET_PRICE_DAY noparallel;

--开启会话并行 DML语句

alter session enable parallel dml;

--取消会话并行

alter session disable parallel dml;

---查询会话是否并行

SELECT pq_status ,pdml_status, pddl_status

FROM v$session WHERE sid=sys_context('userenv','sid');

 

 

查找前十条性能差的sql.

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,

COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea

order BY disk_reads DESC )where ROWNUM<10 ;

查看占io较大的正在运行的session

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,

se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st.

p1text,si.physical_reads,

si.block_changes FROM v$session se,v$session_wait st,

v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.

sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.

wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

---正在执行的

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT

from v$session a, v$sqlarea b

where a.sql_address = b.address

---执行过的

select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT

from v$sqlarea b

where b.FIRST_LOAD_TIME between '2018-08-08 09:24:47' and

'2018-08-09 09:24:47' order by b.FIRST_LOAD_TIME