Oracle 高 Version counts 问题说明
Oracle里的所有SQL 语句都是implicitly sharable的。 当用户提交了一个SQL语句,RDBMS 会对这个SQL 语句进行hash 运算,然后产生
一. version count 说明
MOS上有文档说明此问题,参考:[ID296377.1]
Oracle里的所有SQL 语句都是implicitly sharable的。 当用户提交了一个SQL语句,RDBMS 会对这个SQL 语句进行hash 运算,然后产生一个hash 值, 在拿这个hash 值与shared pool 里已经存在的SQL 进行hash 值比较,如果相同,就重用之前SQL 的cursor,如果没有,就需要进行硬解析。
在硬解析时,Oracle 至少会产生一个parent cursor 和 child cursor, parent cursor 用来保存hash value。 child cursor 用来保存SQL 语句的metadata。 parent cursor和child cursor都是shared cursor,它们都是以library cache object handle的方式存在library cache里。
注意:
parentcursor和child cursor都是library cache object,它们的结构是一摸一样的. 这个可以通过dump library cache 来确认。
看下library cache 的一个管理结构图,这几张图是从DSI 里找到的。
Oracle通过Hash buckets来存放相关的对象,如cursor,那么Oracle会把cursor 放到某个具体的buckets里,由Oracle自己的算法来操作。 在每个bucket里面,会有很多的object,Oracle 通过list 的方式将每个bucket里的对象连接起来。
每个Hash bucket 指向一个object Handle,handle 又指向其他的handle,每个Handle 又指向对应的Library Cache Object。 如下图所示。
LibraryCache Object是由一些独立的heap所组成,, Library cache handle指向Library cache Object,其实handle是指向第一个heap,这个heap 我们就称之为heap 0。Heap 0记录了指向其他heap的指针信息。
heap0 代表的是parentcursor,它保存的是sql 语句的hash value,同时heap 0 的child table里保存所有child cursor 的Data Heap地址。 在每个Child cursor 对应的handle 的Heap 0中的Data Blocks指向的heap 6保存了SQL 对应的执行计划。
当SQL 语句第一次执行时,在硬解析时,会创建parent cursor 和child cursor。 这2个是必须的。这2个过程是比较费资源的。也是硬解析里主要做的事。 当再次执行这个SQL时,Oracle 会先对SQL 语句进行hash 运算,产生一个hash 值,然后用这个HASH 值到buckets里去查找,hash value 存放在parent cursor里。 如果找到了,就去检查child cursor。 如果可以重用这个child cursor,那么就直接调用cursor里的执行计划。 如果不可重用,就会创建一个新的child cursor。 这个child cursor 的个数,就是version count。 不同parent cursor 对应的child cursor 越多,version count 就越高。
可以通过v$sqlarea 和 v$sql 视图来查新相关信息,v$sqlarea是父游标相关信息视图,v$sql是子游标的。
如:
select sql_id,version_countfromv$sqlarea order by 2 desc ;
select address,child_address,sql_textfrom v$sqlwheresql_id='70th7d08hqjf7';
v$sql中通过child_number,hash_value,address来确定一个子游标,而v$sqlarea通过address和hash_value就可以确定一个父游标;而从10g过后,通过sql_id就能确定一个游标。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

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

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

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

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.
