查性能,查SQL,查长对话SESSION,杀进程
SELECT * FROM dba_dml_locks --这里可以看到做长对话的表,和全表扫描的SQL,找到SQL ADDRESS
SELECT * FROM DBA_BLOCKERS ;
、
SELECT SID,SERIAL#
,sql_address,client_info,lockwait FROM V$SESSION
WHERE SID = 108
SELECT * FROM v$sqltext WHERE ADDRESS =
'8A6DECE4' --ADDRESS是SQL ADDRESS里的值
ALTER SYSTEM KILL SESSION '1099,
9';
SELECT COUNT(*) FROM
V$SESSION WHERE schemaname = 'GEO'
SELECT * FROM v$session_longops
-------------------------------------------
ORDER BY elapsed_seconds DESC
SELECT SID,SERIAL# ,sql_address,client_info ,sql_id FROM
V$SESSION WHERE sid = 927
##############################################################
Concurrency值是 “同时并发使用” 的值
这个值高可能有两种可能
1. 锁导致排队等待,使得Concurrency“同时并发使用”值加大。
2. 在此同时都在进行同时做大的持续时间长的业务
看到值大的时候检查下两个
1.在DB EM页面下运行ADDM ,看在做哪些SQL
2.在TOAD里已SYS用户登录查,db锁的等待情况。
SELECT * FROM dba_dml_locks
SELECT SID,SERIAL#
,sql_address,client_info,lockwait,stat FROM
V$SESSION WHERE SID =
ID>
SELECT SID,SERIAL#
,sql_address,client_info,lockwait,state FROM
V$SESSION
SELECT * FROM v$sqltext WHERE ADDRESS = '
值'
如果是死锁或长时间运行导致堵塞排队,根据实际运行SQL事务程序的判断后,可以杀掉相关进程
ALTER SYSTEM KILL SESSION
',
'
查看长对话进程
SELECT * FROM v$session_longops
############################################################################
select * from dba_dml_locks
列出数据库中所有的DML锁,和对一个DML锁的所存未决请求.
############################################################################
系统死锁问题:
已高新物流的用户为例:
查死锁
SELECT * FROM DBA_DML_LOCKS;
查所有死锁总和
select count(*) from DBA_DML_LOCKS;
--------------------------------------------
SELECT * FROM DBA_DML_LOCKS;
SELECT SID,SERIAL# FROM V$SESSION WHERE SID=975;
ALTER SYSTEM KILL SESSION '975,334';
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
用SYS用户连入数据库
查死锁的session
SELECT * FROM v$session WHERE USERNAME='WXGD' AND
sid=2249
ALTER SYSTEM KILL SESSION '2249,55483'
解释:SESSION '2249,55483' ----2249是SID号
;55483是serial号根据这两个号确定杀SESSION
SELECT * FROM v$LOCK
查锁住项
SELECT * FROM v$LOCKed_object
查锁住项目
找到SESSION_ID栏号1030
SELECT * FROM v$session WHERE USERNAME='WXGD' AND
SID='1030' -----SESSION_ID栏号就是session里的SID号
SELECT * FROM v$session WHERE USERNAME='WXGD' AND
STATUS='DEAD'
根据用户查锁住状态
-----------------------
例:单号死锁
SELECT * FROM v$LOCKed_object
查锁住项目
找到SESSION_ID号1030,用下行
SELECT * FROM v$session WHERE USERNAME='WXWHLB'
AND SID='1030' -----SESSION_ID号就是
v$session里的SID号,用
ALTER SYSTEM KILL SESSION '1030,36438' ----1030是sid号
;36438是serial号根据这两个号确定杀SESSION
############################################################################
用SYS连进数据库
select * from v$process where addr in (select
paddr from v$session where sid in
(select session_id from dba_dml_locks where blocking_others = 'Blocking'));
------------------------
select * from v$session
可看ORACLE系统内部运行查询相关模块及时间
############################################################################
改数据库里的SESSION和PROSESS值
show parameter session;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 20
session_max_open_files integer 10
sessions integer 1131
shared_server_sessions integer
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
aq_tm_processes integer 0
db_writer_processes integer 4
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 1024
SQL> alter system set process=3072 scope=BOTH;
alter system set process=3072 scope=BOTH
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system set processes=3072
scope=BOTH;
alter system set processes=3072 scope=BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be
modified
SQL> alter system set processes=4096
scope=spfile;
System altered.
SQL> alter system set job_queue_processes=15
scope=spfile;
System altered.
SQL> show parameter session;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
session_cached_cursors integer 20
session_max_open_files integer 10
sessions integer 1131
shared_server_sessions integer
SQL>
############################################################################