search
HomeDatabaseMysql TutorialOracle 执行计划更改导致数据加工未完成

使用use_hash hint或使用outlines稳固执行计划, 因为时间原因,最后采用了使用hint的方式。hash join 不仅解决了nested loops连

现象:job一直运行,抽取一直在运行,,通过过程中的日志记录定位到是因为抽取X系统数据一直卡着。但是查询正在执行的sql,没有任何结果,抽取这部分数据一直运行了一天,还未完。
 
因为定位不到是哪条sql在运行,于是停job,手动运行这部分的数据抽取,运行时间与正常抽取的时间相近,之前查看系统cpu,io均没有异常,怀疑是因为这部分sql的执行计划改变所致。
 调出系统中的执行计划与手动运行的sql的执行计划进行对比,一条sql的执行计划在月初由hash join 变为nested loops。
 --1.由于定位了sql,查找sql的sql_id
 select sql_id,address,plan_hash_value,hash_value,child_number,sql_text from v$sql                                         
 where sql_text like 'INSERT INTO XXX%'
 
--2.dba_hist_sql_plan视图显示了历史的执行计划,TIMESTAMP字段指该执行计划生成的时间
 select * from dba_hist_sql_plan h where sql_id='7sjn92j0k8csm';
 每个不同的plan_hash_value值代表一个不同的执行计划。这条对应两个执行计划,在月初的时候新生成了一个计划(连接方式是nested loops),Oracle使用了最新的执行计划。
 
 --3.显示所有详细的执行计划
 select plan_table_output from table(dbms_xplan.display_awr('7xh0wcw1qt6d7'));
 
解决方法:使用use_hash hint或使用outlines稳固执行计划,  因为时间原因,最后采用了使用hint的方式。hash join 不仅解决了nested loops连接中大量随机读取的问题,还解决了排序合并连接中排序代价过大的问题。hash join不同于nested loop按照随机读取的方式进行连接,它需要提前将连接对象集中到一起,然后在内存中按照哈希读取的方式进行连接。排序合并连接可以参考:
 
查看计划的两种方式:
 DBMS_XPLAN.DISPLAY_AWR(
    sql_id            IN      VARCHAR2,
    plan_hash_value  IN      NUMBER DEFAULT NULL,
    db_id            IN      NUMBER DEFAULT NULL,
    format            IN      VARCHAR2 DEFAULT TYPICAL);
 
sql_id
 Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
 
DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id        IN  VARCHAR2  DEFAULT  NULL,
    child_number  IN  NUMBER    DEFAULT  NULL,
    format        IN  VARCHAR2  DEFAULT  'TYPICAL');
 
sql_id
 Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.

linux

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
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

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 Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment