search
HomeDatabaseMysql Tutorial关于kill session的分析

关于kill session的分析

Jun 07, 2016 pm 04:38 PM
kikillsessionaboutanalyzefriend

有朋友聊到了关于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。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL's Role: Databases in Web ApplicationsMySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AM

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.

MySQL: Building Your First DatabaseMySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AM

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: A Beginner-Friendly Approach to Data StorageMySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AM

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.

Is MySQL Beginner-Friendly? Assessing the Learning CurveIs MySQL Beginner-Friendly? Assessing the Learning CurveApr 17, 2025 am 12:19 AM

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.

Is SQL a Programming Language? Clarifying the TerminologyIs SQL a Programming Language? Clarifying the TerminologyApr 17, 2025 am 12:17 AM

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

Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Apr 16, 2025 am 12:20 AM

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: Database Management System vs. Programming LanguageMySQL: Database Management System vs. Programming LanguageApr 16, 2025 am 12:19 AM

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: Managing Data with SQL CommandsMySQL: Managing Data with SQL CommandsApr 16, 2025 am 12:19 AM

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.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Chat Commands and How to Use Them
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

MantisBT

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

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft