Home Database Mysql Tutorial 10053事件分析案例一则

10053事件分析案例一则

Jun 07, 2016 pm 03:54 PM
consistent event analyze data Case test

测试库两张表,数据一致,(表有复合主键A+B),但同样执行DELETE TABLE FROM T1/T2 WHERE A=1 AND ROWNUM100;时,T1表删除时间非常长,T2表删除时间很快。在PLSQL中或sqlplus中查看执行计划都是一样的,表示都用到了索引范围扫描。 PLAN_TABLE_OUTPUT -------

测试库两张表,数据一致,(表有复合主键A+B),但同样执行DELETE TABLE FROM T1/T2 WHERE A='1' AND ROWNUM

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000 | 12000 | 3217 |
| 1 | DELETE | T1 | | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | INDEX RANGE SCAN | IDX_T1 | 420K| 4931K| 3217 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(ROWNUM 3 - access("T1"."A"='1')
Note: cpu costing is off

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1000 | 12000 | 2965 |
| 1 | DELETE | T2 | | | |
|* 2 | COUNT STOPKEY | | | | |
|* 3 | INDEX RANGE SCAN | IDX_T2 | 393K| 4607K| 2965 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(ROWNUM 3 - access("T2"."A"='1')
Note: cpu costing is off

显然感觉这两个表的实际操作和执行计划不太相符,这时10053事件就起到了作用。

10053介绍:

10053 事件是oracle 提供的用于跟踪sql 语句成本计算的内部事件,它能记载CBO 模式下oracle 优化器如何计算sql 成本,生成相应的执行计划。 用来描述oracle如何选择执行计划的过程,然后输出到trace文件里,因为我们经常看执行计划怎么执行的消耗了哪些资源,而不是常看执行计划怎么选择出来了的。

10053特点:

(1) 只可以了解oracle执行计划的选择过程

(2) 无法获知代价的计算公式,因为这是oracle内部的商业机密,而且每个oracle版本的优化器计算公式都不相同差距还是蛮大的,不同版本的同一个语句的代价也不一样,优化器现在还不是很成熟,还有待完善。

(3) 在这个里面我们重点要了解的是“代价”是如何计算出来的,然后我们才能了解执行计划是如何选择的。

(4) 在10053中可以了解哪些因素影响sql的执行代价

(5) oracle 8i cost等价IO资源消耗 9i以后cost等价IO+CPU+网络+等待事件+其他代价

T1表的10053事件信息:

***************************************

BASE STATISTICAL INFORMATION
***********************
Table stats Table: T1 Alias: T1 来自user_tables视图
TOTAL :: CDN: 2341358 NBLKS: 13921 AVG_ROW_LEN: 40
-- Index stats 来自user_indexes视图
INDEX NAME: IDX_STAROTHER COL#: 2 3
TOTAL :: LVLS: 2 #LB: 13609 #DK: 2156054 LB/K: 1 DB/K: 1 CLUF: 165252
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col#: 2 Table: T1 Alias: T1
NDV: 7 NULLS: 0 DENS: 1.4286e-01
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: STAROTHERPRF ORIG CDN: 2341358 ROUNDED CDN: 334480 CMPTD CDN: 334480
Access path: tsc Resc: 1340 Resp: 1340 全表扫描代价(1340),这里tsc我想应该是TableScan的缩写
Skip scan: ss-sel 0 andv 308008
ss cost 308008 索引跳跃扫描的代价(1945)
index io scan cost 1945
Access path: index (index-only) 索引(范围)扫描代价(1947)
Index: IDX_T1
TABLE: T1
RSC_CPU: 0 RSC_IO: 1947
IX_SEL: 1.4286e-01 TB_SEL: 1.4286e-01

BEST_CST: 1340.00 PATH: 2 Degree: 1 最佳代价是1340,即全表扫描

对应的执行计划:

***************************************
GENERAL PLANS
***********************
Join order[1]: STAROTHERPRF[STAROTHERPRF]#0
Best so far: TABLE#: 0 CST: 1340 CDN: 334480 BYTES: 4348240
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1340 CDN: 334480 RSC: 1340 RSP: 1340 BYTES: 4348240
IO-RSC: 1340 IO-RSP: 1340 CPU-RSC: 0 CPU-RSP: 0
QUERY
explain plan for delete from starotherprf WHERE AIRLINE_CODE = 'US' AND ROWNUM PLAN
Cost of plan: 1340
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT 0
DELETE STAROTHERPRF 1
COUNT STOPKEY 2 1
TABLE ACCESS T1 FULL 3 2
QUERY

显示用的就是全表扫描

T2表的10053事件信息:

***************************************

SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO Col#: 1 Table: T2 Alias: T2
NDV: 19 NULLS: 0 DENS: 5.2632e-02
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: CASTARPRF ORIG CDN: 6665065 ROUNDED CDN: 350793 CMPTD CDN: 350793
Access path: tsc Resc: 4275 Resp: 4275 全表扫描代价(4275)
Skip scan: ss-sel 0 andv 413617 索引跳跃扫描代价(413617)
ss cost 413617
index io scan cost 1973
Access path: index (index-only) 索引(范围)扫描代价(1975)
Index: IDX_T2
TABLE: T2
RSC_CPU: 0 RSC_IO: 1975
IX_SEL: 5.2632e-02 TB_SEL: 5.2632e-02
BEST_CST: 1975.00 PATH: 4 Degree: 1 最佳代价是1975,即索引扫描

对应的执行计划:

***************************************
GENERAL PLANS
***********************
Join order[1]: CASTARPRF[CASTARPRF]#0
Best so far: TABLE#: 0 CST: 1975 CDN: 350793 BYTES: 4911102
prefetching is on for IDX_CASTAR
Final - All Rows Plan:
JOIN ORDER: 1
CST: 1975 CDN: 350793 RSC: 1975 RSP: 1975 BYTES: 4911102
IO-RSC: 1975 IO-RSP: 1975 CPU-RSC: 0 CPU-RSP: 0
QUERY
explain plan for delete from castarprf WHERE AIRLINE_CODE = 'US' AND ROWNUM PLAN
Cost of plan: 1975
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT 0
DELETE CASTARPRF 1
COUNT STOPKEY 2 1
INDEX IDX_T2 RANGE SCAN 3 2
QUERY

显示用的就是索引扫描

现在就可以知道为什么这两张表删除时间不同了,原因就是T1表CBO选择了错误的执行计划,导致全表扫描,因此百万级的数据就会耗费更长的时间。

总结:当感觉SQL语句执行时走的是错误的执行计划,而又找不到原因时,这时请用10053来分析一下原因。这就是10053的适用场景。

Statement of this Website
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

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

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

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

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1655
14
PHP Tutorial
1255
29
C# Tutorial
1228
24
Open source! Beyond ZoeDepth! DepthFM: Fast and accurate monocular depth estimation! Open source! Beyond ZoeDepth! DepthFM: Fast and accurate monocular depth estimation! Apr 03, 2024 pm 12:04 PM

0.What does this article do? We propose DepthFM: a versatile and fast state-of-the-art generative monocular depth estimation model. In addition to traditional depth estimation tasks, DepthFM also demonstrates state-of-the-art capabilities in downstream tasks such as depth inpainting. DepthFM is efficient and can synthesize depth maps within a few inference steps. Let’s read about this work together ~ 1. Paper information title: DepthFM: FastMonocularDepthEstimationwithFlowMatching Author: MingGui, JohannesS.Fischer, UlrichPrestel, PingchuanMa, Dmytr

Use ddrescue to recover data on Linux Use ddrescue to recover data on Linux Mar 20, 2024 pm 01:37 PM

DDREASE is a tool for recovering data from file or block devices such as hard drives, SSDs, RAM disks, CDs, DVDs and USB storage devices. It copies data from one block device to another, leaving corrupted data blocks behind and moving only good data blocks. ddreasue is a powerful recovery tool that is fully automated as it does not require any interference during recovery operations. Additionally, thanks to the ddasue map file, it can be stopped and resumed at any time. Other key features of DDREASE are as follows: It does not overwrite recovered data but fills the gaps in case of iterative recovery. However, it can be truncated if the tool is instructed to do so explicitly. Recover data from multiple files or blocks to a single

Google is ecstatic: JAX performance surpasses Pytorch and TensorFlow! It may become the fastest choice for GPU inference training Google is ecstatic: JAX performance surpasses Pytorch and TensorFlow! It may become the fastest choice for GPU inference training Apr 01, 2024 pm 07:46 PM

The performance of JAX, promoted by Google, has surpassed that of Pytorch and TensorFlow in recent benchmark tests, ranking first in 7 indicators. And the test was not done on the TPU with the best JAX performance. Although among developers, Pytorch is still more popular than Tensorflow. But in the future, perhaps more large models will be trained and run based on the JAX platform. Models Recently, the Keras team benchmarked three backends (TensorFlow, JAX, PyTorch) with the native PyTorch implementation and Keras2 with TensorFlow. First, they select a set of mainstream

Slow Cellular Data Internet Speeds on iPhone: Fixes Slow Cellular Data Internet Speeds on iPhone: Fixes May 03, 2024 pm 09:01 PM

Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker

Tesla robots work in factories, Musk: The degree of freedom of hands will reach 22 this year! Tesla robots work in factories, Musk: The degree of freedom of hands will reach 22 this year! May 06, 2024 pm 04:13 PM

The latest video of Tesla's robot Optimus is released, and it can already work in the factory. At normal speed, it sorts batteries (Tesla's 4680 batteries) like this: The official also released what it looks like at 20x speed - on a small "workstation", picking and picking and picking: This time it is released One of the highlights of the video is that Optimus completes this work in the factory, completely autonomously, without human intervention throughout the process. And from the perspective of Optimus, it can also pick up and place the crooked battery, focusing on automatic error correction: Regarding Optimus's hand, NVIDIA scientist Jim Fan gave a high evaluation: Optimus's hand is the world's five-fingered robot. One of the most dexterous. Its hands are not only tactile

Alibaba 7B multi-modal document understanding large model wins new SOTA Alibaba 7B multi-modal document understanding large model wins new SOTA Apr 02, 2024 am 11:31 AM

New SOTA for multimodal document understanding capabilities! Alibaba's mPLUG team released the latest open source work mPLUG-DocOwl1.5, which proposed a series of solutions to address the four major challenges of high-resolution image text recognition, general document structure understanding, instruction following, and introduction of external knowledge. Without further ado, let’s look at the effects first. One-click recognition and conversion of charts with complex structures into Markdown format: Charts of different styles are available: More detailed text recognition and positioning can also be easily handled: Detailed explanations of document understanding can also be given: You know, "Document Understanding" is currently An important scenario for the implementation of large language models. There are many products on the market to assist document reading. Some of them mainly use OCR systems for text recognition and cooperate with LLM for text processing.

The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks Apr 29, 2024 pm 06:55 PM

I cry to death. The world is madly building big models. The data on the Internet is not enough. It is not enough at all. The training model looks like "The Hunger Games", and AI researchers around the world are worrying about how to feed these data voracious eaters. This problem is particularly prominent in multi-modal tasks. At a time when nothing could be done, a start-up team from the Department of Renmin University of China used its own new model to become the first in China to make "model-generated data feed itself" a reality. Moreover, it is a two-pronged approach on the understanding side and the generation side. Both sides can generate high-quality, multi-modal new data and provide data feedback to the model itself. What is a model? Awaker 1.0, a large multi-modal model that just appeared on the Zhongguancun Forum. Who is the team? Sophon engine. Founded by Gao Yizhao, a doctoral student at Renmin University’s Hillhouse School of Artificial Intelligence.

Single card running Llama 70B is faster than dual card, Microsoft forced FP6 into A100 | Open source Single card running Llama 70B is faster than dual card, Microsoft forced FP6 into A100 | Open source Apr 29, 2024 pm 04:55 PM

FP8 and lower floating point quantification precision are no longer the "patent" of H100! Lao Huang wanted everyone to use INT8/INT4, and the Microsoft DeepSpeed ​​team started running FP6 on A100 without official support from NVIDIA. Test results show that the new method TC-FPx's FP6 quantization on A100 is close to or occasionally faster than INT4, and has higher accuracy than the latter. On top of this, there is also end-to-end large model support, which has been open sourced and integrated into deep learning inference frameworks such as DeepSpeed. This result also has an immediate effect on accelerating large models - under this framework, using a single card to run Llama, the throughput is 2.65 times higher than that of dual cards. one

See all articles