SQL历史记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select logon_time,
       last_call_et "time inactive",
       nvl(s.username, 'ORACLE PROCESS') username,
       s.machine,
       s.program,
       s.sid session_id,
       s.status,
       sql_text,
       ss.value "CPU used",
       trunc(buffer_gets / (executions + 1)) "BUFF-EXEC",
       trunc(buffer_gets / (rows_processed + 1)) "BUFF-ROWS",
       first_load_time,
       executions,
       parse_calls,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$session s, v$sesstat ss, v$statname sn, v$sqlarea sa
where s.sid = ss.sid
   and ss.statistic# = sn.statistic#
   and sn.name = 'CPU used by this session'
   and s.sql_address = sa.address
   and s.sql_hash_value = sa.hash_value
   and last_call_et > 5000  --超过5秒不释放的sql
order by machine, status, program, last_call_et asc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select ss.value "CPU used",
       sa.SQL_FULLTEXT,
       sql_text,
       s.status,
       last_call_et "time inactive",
       nvl(s.username, 'ORACLE PROCESS') username,
       s.sid,
     s.serial#,
       logon_time,
       s.machine,
       s.program,
       s.sid session_id,
       trunc(buffer_gets / (executions + 1)) "BUFF-EXEC",
       trunc(buffer_gets / (rows_processed + 1)) "BUFF-ROWS",
       first_load_time,
       executions,
       parse_calls,
       disk_reads,
       buffer_gets,
       rows_processed
  from v$session s, v$sesstat ss, v$statname sn, v$sqlarea sa
 where s.sid = ss.sid
   and ss.statistic# = sn.statistic#
   and sn.name = 'CPU used by this session'
   and s.sql_address = sa.address
   and s.sql_hash_value = sa.hash_value
--   and status='ACTIVE'
--   and username=''
--   and last_call_et > 1000
 order by ss.value desc

bigint类型转换为datetime类型

假设 1164691264437 是 Java 里的“日期时间”：即：自1970-01-01 00:00:00以来的毫秒数

1
2
3
4
5
6
mysql> select from_unixtime(1164691264437/1000);
+-----------------------------------+
| from_unixtime(1164691264437/1000) |
+-----------------------------------+
| 2006-11-28 13:21:04               |
+-----------------------------------+

datetime类型转换为bigint类型

假设 “2011-05-31 23:59:59” 是 Java 里的“日期时间”：即：自1970-01-01 00:00:00以来的毫秒数

1
2
3
4
mysql> select UNIX_TIMESTAMP('2011-05-31 23:59:59');
+-----------------------------------+
| from_unixtime(1306857599/1000) |
+-----------------------------------+

MYSQL查询锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 查询是否锁表
show OPEN TABLES where In_use > 0;
# 查看所有进程
# MySQL:
show processlist;
# mariabd:
show full processlist;
# 杀掉指定mysql连接的进程号
kill $pid
# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查看innodb引擎的运行时信息
show engine innodb status\G;
# 查看造成死锁的sql语句，分析索引情况，然后优化sql语句；
# 查看服务器状态
show status like '%lock%';
# 查看超时时间：
show variables like '%timeout%';