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 to solve the problem of mysql cannot open shared libraryHow to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

Reduce the use of MySQL memory in DockerReduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewWhat is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Running multiple MySQL versions on MacOS: A step-by-step guideRunning multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

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

Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development 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.

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.

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor