search
HomeDatabaseMysql Tutorial会话后台运行引起并行进程释放问题

会话后台运行引起并行进程释放问题

Jun 07, 2016 pm 04:36 PM
sessionBackstageparallelcauserunprocessfreedquestion

有一套rac数据库,一个业务报表逻辑非常复杂,sql语句达到了600多行,表关联有20多个,采取了并行查询的方式去优化,而这个并行查询虽然速度上得到了解决,但是经常出现几个月之前的进程都不释放的状态,而且这些遗留的并行进程拖慢了系统的运行,最后经常不

有一套rac数据库,一个业务报表逻辑非常复杂,sql语句达到了600多行,表关联有20多个,采取了并行查询的方式去优化,而这个并行查询虽然速度上得到了解决,但是经常出现几个月之前的进程都不释放的状态,而且这些遗留的并行进程拖慢了系统的运行,最后经常不得不采取手动kill掉这些并行进程来释放压力。

查看了很多的文档并没有发现一个合适的说法,而这些并行进程都是在进行PX Deq:Execution Msg或者PX Deq Credit: send blkd等待,做hanganalyze也找不到问题的原因,mos上也没发现疑似的bug。

看见mos这篇文章
PX Deq Credit: Send Blkd Waits Seen after CTRL-C on a Session and Parallel Query Slaves Not Released (文档 ID 1545069.1)

Cause:
This is not a bug per Bug 1837760: PARALLEL QUERY SLAVE DOES NOT GET RELEASED AFTER CTRL-C TO CANCEL THE QUERY, which was closed as an enhancement request. Slaves will be considered "busy" until a SQL statement completes and releases the cursor. If a session is CTRL-C'd, the cursor will remain open until either (1) another SQL statement is issued in the session, or (2) the session is exited. This behavior continues through 11g and higher

Solution:
If you CTRL-C a session running in parallel, either issue another SQL statement to release the slaves, or exit the session.

这篇文章大概说的就是一个并行查询如果被终止了,这些并行进程依然还会存在,并进行一些PX Deq的等待,而解决办法是需要在当前这个session中执行另一个查询或者exited退出这个会话。

根据上面的文章小鱼手动做了这个复杂模块的查询,然后又叉掉了这个模块,过了很长的时间这个对应的并行进程并不释放,而查询这些并行进程发现对应的sql语句正是这个复杂的sql语句。

这个600多行的sql语句不采用并行执行是相当的慢,调优由于网络等因素都不方便,所以建议做的是并行,但是由于是客户操作某个模块触发的这个复杂的sql,而且有时候如果半天不响应就直接叉掉了那个模块重来,而叉掉这个模块的动作并不会清除当前这个会话,正是这个会话后台运行导致了oracle的pmon进程不会去释放一个运行的好好的进程,从而引起了太多的看似没有释放的并行进程,其实在oracle看来这些并行进程还在正常运行,根本就不需要释放,其实也就是会话后台运行引起的并行进程不释放,而为什么叉掉这个模块后,会话依然还在后台运行,这个可能跟中间件等相关,而这个中间件用的weblogic。

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: BLOB and other no-sql storage, what are the differences?MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialAdding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Dreamweaver Mac version

Dreamweaver Mac version

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.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment