有朋友聊到了关于kill session时会话长时间没有释放,而这个小鱼一般是通过os级别的kill spid来释放session和process的,如果用oracle的kill session可能会遇见进程和会话长时间不释放,下面来做一点简单的剖析。 Session A环境: SQL> conn test/test Conn
有朋友聊到了关于kill session时会话长时间没有释放,而这个小鱼一般是通过os级别的kill spid来释放session和process的,如果用oracle的kill session可能会遇见进程和会话长时间不释放,下面来做一点简单的剖析。
Session A环境:
SQL> conn test/test
Connected.
SQL> select sid,serial#,paddr,status from v$session where sid=userenv('sid');
SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
1147 6874 000000021A0D0A70 ACTIVE
Session B环境:
SQL> alter system kill session '1147,6874';
System altered.
SQL> select sid,serial#,paddr,status from v$session where sid=1147;
SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
1147 6874 000000021A6A9020 KILLED
此时我们查看这个被killed的session只是将session的状态标记为killed,然后将paddr(session对应的服务器进程的地址)标记为一个虚拟的地址。
过了一段时间,我们还是在Session B环境查询被kill掉的会话,发觉这个会话依然是存在的。
SQL> select sid,serial#,paddr,status from v$session where sid=1147;
SID SERIAL# PADDR STATUS
---------- ---------- ---------------- --------
1147 6874 000000021A6A9020 KILLED
而如果我们重新在原来的session A环境做一些命令查询,此时会报出session被killed的提示
SQL> select sid,serial#,paddr,status from v$session where sid=userenv('sid');
select sid,serial#,paddr,status from v$session where sid=userenv('sid')
*
ERROR at line 1:
ORA-00028: your session has been killed
再次回到session B环境查询这个被killed的会话
SQL> select sid,serial#,paddr,status from v$session where sid=1147;
no rows selected
发现已经没有任何数据了,此时这个session对应的process也已经被pmon进程释放了。
记得小鱼处理过一个win的库,有好几百的session在报出相应的热点块现象,通过kill session后,过了很长时间所有的被killed掉session都没有释放掉,而这个可能出现的原因数据库负载太高了,pmon进程并没有去释放掉这些session,再就是在pmon进程释放之前,需要touch该session才会被pmon进程清除(这个touch的含义是必须有对该session做查询等请求递交给server端),一般建议还是通过os级别的kill来释放session和process。
那么如果我们已经在用kill session的方式killed session,但是进程依然不释放,此时我们想通过kill spid的方式来释放session和process。
SQL> select program,spid from v$process where addr in
2 (select p.addr from v$process p where p.pid
1
3 minus
4 select s.paddr from v$session s);
PROGRAM SPID
------------------------------------------------ ------------------------
oracle@dbserver (D000) 26520
oracle@dbserver (S000) 26522
oracle@dbserver (TNS V1-V3) 27932
可以通过上面的查询先查出两个视图中地址不一致的进程,这里需要注意不要kill掉系统的进程,比如这里我们就可以通过上面的查询手动kill -9 27932来释放这个session和对应的process。
SQL> !kill -9 27932
SQL> select sid,serial#,paddr,status from v$session where sid=1147;
no rows selected
此时即使我们不touch原来那个session,通过os kill的方式,sesison和process都已经马上释放了,个人也建议在维护生产环境中尽量通过os的方式来kill掉需要的session和process。
原文地址:关于kill session的分析, 感谢原作者分享。

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

MySQL is suitable for beginners because: 1) easy to install and configure, 2) rich learning resources, 3) intuitive SQL syntax, 4) powerful tool support. Nevertheless, beginners need to overcome challenges such as database design, query optimization, security management, and data backup.

Yes,SQLisaprogramminglanguagespecializedfordatamanagement.1)It'sdeclarative,focusingonwhattoachieveratherthanhow.2)SQLisessentialforquerying,inserting,updating,anddeletingdatainrelationaldatabases.3)Whileuser-friendly,itrequiresoptimizationtoavoidper

ACID attributes include atomicity, consistency, isolation and durability, and are the cornerstone of database design. 1. Atomicity ensures that the transaction is either completely successful or completely failed. 2. Consistency ensures that the database remains consistent before and after a transaction. 3. Isolation ensures that transactions do not interfere with each other. 4. Persistence ensures that data is permanently saved after transaction submission.

MySQL is not only a database management system (DBMS) but also closely related to programming languages. 1) As a DBMS, MySQL is used to store, organize and retrieve data, and optimizing indexes can improve query performance. 2) Combining SQL with programming languages, embedded in Python, using ORM tools such as SQLAlchemy can simplify operations. 3) Performance optimization includes indexing, querying, caching, library and table division and transaction management.

MySQL uses SQL commands to manage data. 1. Basic commands include SELECT, INSERT, UPDATE and DELETE. 2. Advanced usage involves JOIN, subquery and aggregate functions. 3. Common errors include syntax, logic and performance issues. 4. Optimization tips include using indexes, avoiding SELECT* and using LIMIT.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Zend Studio 13.0.1
Powerful PHP integrated development environment

SublimeText3 English version
Recommended: Win version, supports code prompts!

Dreamweaver CS6
Visual web development tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft