- 浏览: 1087452 次
- 性别:
- 来自: 南京
博客专栏
-
Oracle管理和开发
浏览量:349745
最新评论
-
Simon.Ezer:
请问对于“如果非主键字段值发生改变,则不会同步过去”这种情况, ...
创建增量同步Oracle物化视图问题 -
dahai639:
挺好的,支持一下
Oracle的pipelined函数实现高性能大数据处理 -
zealotpz:
不错,原来是用户oracle 的所属组的问题
以sysdba身份登录oracle报ORA-1031权限不足错误之完美分析 -
mikixiyou:
zhangyuslam 写道如果担心全局索引失效,可以使用如下 ...
Oracle分区表的分区交互技术实现数据快速转移 -
sea0108:
...
Oracle sql loader使用速成
Oracle Hash join 是一种非常高效的join 算法,主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接,我们将小表构建到内存中,称为Hash cluster,大表称为probe表。
当两个表做hash join时,oracle会选择一个表作为驱动表,先根据过滤条件排除不必要的数据,然后将结果集做成hash表,放入进程的hash area,接着扫描第二张表,将记录的join字段值做hash运算,到内存的hash表里面去探测,如果探测成功,就返回数据,否则这行就丢弃掉。
(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1709321 )
select /*+use_nl(a b)*/ a.*,b.* from dba_obj a,all_obj b where a.object_id=b.object_id and a.object_name like 'tt%' SELECT STATEMENT, GOAL = ALL_ROWS NESTED LOOPS TABLE ACCESS FULL SCOTT DBA_OBJ TABLE ACCESS BY INDEX ROWID SCOTT ALL_OBJ INDEX UNIQUE SCAN SCOTT PK_ALL_OBJ
执行计划解读
两个表使用了嵌套循环连接。首先访问dba_obj表,得到全部记录。然后按照此表记录依次去扫描all_obj表,扫描过程走索引快速得到all_obj的记录。
select /*+use_hash(a b)*/ a.*,b.* from dba_obj a,all_obj b where a.object_id=b.object_id and a.object_name like 'tt%' SELECT STATEMENT, GOAL = ALL_ROWS HASH JOIN TABLE ACCESS FULL SCOTT DBA_OBJ TABLE ACCESS FULL SCOTT ALL_OBJ
执行计划解读
两个表使用了哈希连接。首先访问dba_obj表,得到全部记录,进行hash运算,放到内存hash area中形成hash table,也称为hash cluster。
然后,再腾出手来,全面扫描all_obj表,每扫描到一条记录时,将join字段进行hash运算,然后到hash area中去找与dba_obj表匹配的记录。
这个行为称为probe,中文称探测。此表也称为probe表。
hash table表是保存在hash area内存区域中,而这个区域在oracle中是分配在pga中。
PGA 包括 进程内存、UGA、sort area,bitmap merge area和hash area。UGA包含session状态信息和private sql area。
使用这个10104 event可以分析hash area的内存分配情况。
alter system set events '10104 trace name context forever,level 2';
select count(*)
from (select /*+use_hash(i g) leading(i)*/
i.*, g.*
from tdividenddetail i, tproductinfo g
where i.c_fundcode = g.fundcode
and i.d_cdate > sysdate - 100);
使用use_hash提示强制让两个表采用hash join关联,然后使用leading提示强制让i表作为驱动表。
在hash area中,默认采用8个partition,每个partition保存若干个 hash table的记录。这些记录又以bucket逻辑结构存储。
分析trc文件内容如下所示:
*** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) *** Join Type: INNER join Original hash-area size: 3064559 Memory for slot table: 2826240 Calculated overhead for partitions and row/slot managers: 238319 Hash-join fanout: 8 Number of partitions: 8 Number of slots: 23 Multiblock IO: 15 Block size(KB): 8 Cluster (slot) size(KB): 120 Minimum number of bytes per block: 8160 Bit vector memory allocation(KB): 128 Per partition bit vector length(KB): 16 Maximum possible row length: 1708 Estimated build size (KB): 0 Estimated Build Row Length (includes overhead): 408 # Immutable Flags: Not BUFFER(execution) output of the join for PQ Evaluate Left Input Row Vector Evaluate Right Input Row Vector # Mutable Flags: IO sync kxhfSetPhase: phase=BUILD kxhfAddChunk: add chunk 0 (sz=32) to slot table kxhfAddChunk: chunk 0 (lbs=0x2b4b26b47b20, slotTab=0x2b4b26b47ce8) successfuly added kxhfSetPhase: phase=PROBE_1 qerhjFetch: max build row length (mbl=390) *** RowSrcId: 1 END OF BUILD (PHASE 1) *** Revised row length: 370 Revised build size: 9KB kxhfResize(enter): resize to 12 slots (numAlloc=7, max=23) kxhfResize(exit): resized to 12 slots (numAlloc=7, max=12) Slot table resized: old=23 wanted=12 got=12 unload=0 *** RowSrcId: 1 HASH JOIN RESIZE BUILD (PHASE 1) *** Total number of partitions: 8 Number of partitions which could fit in memory: 8 Number of partitions left in memory: 8 Total number of slots in in-memory partitions: 7 kxhfResize(enter): resize to 13 slots (numAlloc=7, max=12) kxhfResize(exit): resized to 13 slots (numAlloc=7, max=13) set work area size to: 1753K (13 slots) *** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) *** Total number of partitions: 8 Number of partitions left in memory: 8 Total number of rows in in-memory partitions: 27 (used as preliminary number of buckets in hash table) Estimated max # of build rows that can fit in avail memory: 8190 ### Partition Distribution ### Partition:0 rows:3 clusters:1 slots:1 kept=1 Partition:1 rows:5 clusters:1 slots:1 kept=1 Partition:2 rows:3 clusters:1 slots:1 kept=1 Partition:3 rows:4 clusters:1 slots:1 kept=1 Partition:4 rows:6 clusters:1 slots:1 kept=1 Partition:5 rows:3 clusters:1 slots:1 kept=1 Partition:6 rows:3 clusters:1 slots:1 kept=1 Partition:7 rows:0 clusters:0 slots:0 kept=1 Revised number of hash buckets (after flushing): 27 Allocating new hash table. Requested size of hash table: 8 Actual size of hash table: 8 Number of buckets: 64 Match bit vector allocated: FALSE Total number of rows (may have changed): 27 Number of in-memory partitions (may have changed): 8 Final number of hash buckets: 64 Size (in bytes) of hash table: 512 qerhjBuildHashTable(): done hash-table on partition=6, index=1 last_slot#=5 rows=3 total_rows=3 qerhjBuildHashTable(): done hash-table on partition=5, index=2 last_slot#=6 rows=3 total_rows=6 qerhjBuildHashTable(): done hash-table on partition=4, index=3 last_slot#=0 rows=6 total_rows=12 qerhjBuildHashTable(): done hash-table on partition=3, index=4 last_slot#=3 rows=4 total_rows=16 qerhjBuildHashTable(): done hash-table on partition=2, index=5 last_slot#=1 rows=3 total_rows=19 qerhjBuildHashTable(): done hash-table on partition=1, index=6 last_slot#=4 rows=5 total_rows=24 qerhjBuildHashTable(): done hash-table on partition=0, index=7 last_slot#=2 rows=3 total_rows=27 kxhfIterate(end_iterate): numAlloc=7, maxSlots=13 ### Hash table ### # NOTE: The calculated number of rows in non-empty buckets may be smaller # than the true number. Number of buckets with 0 rows: 42 Number of buckets with 1 rows: 18 Number of buckets with 2 rows: 4 Number of buckets with 3 rows: 0 Number of buckets with 4 rows: 0 Number of buckets with 5 rows: 0 Number of buckets with 6 rows: 0 Number of buckets with 7 rows: 0 Number of buckets with 8 rows: 0 Number of buckets with 9 rows: 0 Number of buckets with between 10 and 19 rows: 0 Number of buckets with between 20 and 29 rows: 0 Number of buckets with between 30 and 39 rows: 0 Number of buckets with between 40 and 49 rows: 0 Number of buckets with between 50 and 59 rows: 0 Number of buckets with between 60 and 69 rows: 0 Number of buckets with between 70 and 79 rows: 0 Number of buckets with between 80 and 89 rows: 0 Number of buckets with between 90 and 99 rows: 0 Number of buckets with 100 or more rows: 0 ### Hash table overall statistics ### Total buckets: 64 Empty buckets: 42 Non-empty buckets: 22 Total number of rows: 27 Maximum number of rows in a bucket: 2 Average number of rows in non-empty buckets: 1.227273 *** 2012-10-31 10:37:15.443 qerhjFetch: max probe row length (mpl=0) *** RowSrcId: 1, qerhjFreeSpace(): free hash-join memory kxhfRemoveChunk: remove chunk 0 from slot table
附加:Alibaba DBA Team 关于Oracle hash join 的文档
当做hash join时,oracle会选择一个表作为驱动表,先根据过滤条件排除不必要的数据,然后将结果集做成hash表,放入进程的hash area,接着扫描第二张表,将行的键值做hash运算,到内存的hash表里面去探测,如果探测成功,就返回数据,否则这行就丢弃掉这个是最基本的解释,实际情况中,考虑到单个进程PGA的大小,oracle不会让进程任意的消耗OS内存,hash area是有一定限制的,所以在oracle中,hash也有三种模式:
optimal,onepass,multipass
optimal:当驱动结果集生成的hash表全部可以放入PGA的hash area时,称为optimal,大致过程如下:
1.先根据驱动表,得到驱动结果集
2.在hash area生成hash bulket,并将若干bulket分成一组,成为一个partition,还会生成一个bitmap的列表,每个bulket在上面占一位
3.对结果集的join键做hash运算,将数据分散到相应partition的bulket中,当运算完成后,如果键值唯一性较高的话,bulket里的数据会比较均匀,也有可能有的桶里面数据会是空的,这样bitmap上对应的标志位就是0,有数据的桶,标志位会是1
4.开始扫描第二张表,对jion键做hash运算,确定应该到某个partition的某个bulket去探测,探测之前,会看这个bulket的bitmap是否会1,如果为0,表示没数据,这行就直接丢弃掉
5.如果bitmap为1,则在桶内做精确匹配,判断OK后,返回数据
这个是最优的hash join,他的成本基本是两张表的full table scan,在加微量的hash运算
onepass
如果进程的pga很小,或者驱动表结果集很大,超过了hash area的大小,会怎么办?当然会用到临时表空间,此时oracle的处理方式稍微复杂点需奥注意上面提到的有个partition的概念,可以这么理解,数据是经过两次hash运算的,先确定你的partition,再确定你的bulket,假设hash area小于整个hash table,但至少大于一个partition的size,这个时候走的就是onepass
当我们生成好hash表后,状况是部分partition留在内存中,其他的partition留在磁盘临时表空间中,当然也有可能某个partition一半在内存,一半在磁盘,剩下的步骤大致如下:
1.扫描第二张表,对join键做hash运算,确定好对应的partition和bulket
2.查看bitmap,确定bulket是否有数据,没有则直接丢弃
3.如果有数据,并且这个partition是在内存中的,就进入对应的桶去精确匹配,能匹配上,就返回这行数据,否则丢弃
4.如果partition是在磁盘上的,则将这行数据放入磁盘中暂存起来,保存的形式也是partition,bulket的方式
5.当第二张表被扫描完后,剩下的是驱动表和探测表生成的一大堆partition,保留在磁盘上
6.由于两边的数据都按照相同的hash算法做了partition和bulket,现在只要成对的比较两边partition数据即可,并且在比较的时候,oracle也做了优化处理,没有严格的驱动与被驱动关系,他会在partition对中选较小的一个作为驱动来进行,直到磁盘上所有的partition对都join完
可以发现,相比optimal,他多出的成本是对于无法放入内存的partition,重新读取了一次,所以称为onepass,只要你的内存保证能装下一个partition,oracle都会腾挪空间,每个磁盘partition做到onepass
multipass
这是最复杂,最糟糕的hash join,此时hash area小到连一个partition也容纳不下,当扫描好驱动表后,可能只有半个partition留在hash area中,另半个加其他的partition全在磁盘上,剩下的步骤和onepass比价类似,不同的是针对partition的处理
由于驱动表只有半个partition在内存中,探测表对应的partition数据做探测时,如果匹配不上,这行还不能直接丢弃,需要继续保留到磁盘,和驱动表剩下的半个partition再做join,这里举例的是内存可以装下半个partition,如果装的更少的话,反复join的次数将更多,当发生multipass时,partition物理读的次数会显著增加
发表评论
-
Oracle分区表的分区交互技术实现数据快速转移
2013-01-22 11:48 14678有一个需求,将某业务表的某个时间点之前的记录转移到它的历史 ... -
等待事件enq TX row lock contention分析
2013-01-17 17:16 33215在Oracle数据库性能报告AWRRPT分析时,发现top ... -
Oracle的UNDO表空间管理总结
2013-01-14 15:06 12892UNDO是Oracle中的一个很 ... -
Oracle在不同windows系统中的迁移
2013-01-09 15:41 3709在Windows操作系统环境下 ... -
Oracle 10.2.0.1在windows 2008上安装失败经历
2013-01-07 10:29 8631这两天遇到这个一个项目上的数据库迁移的问题。原来的环境是数据库 ... -
所有的物化视图刷新脚本
2012-12-28 13:59 0select 'execute dbms_mview.refr ... -
Oracle数据库的SQL性能问题分析
2012-12-27 15:31 5237在Oracle 10.2.0.4数据库中,有一个SQL执行缓慢 ... -
db block gets和consistent gets的分析
2012-12-26 18:09 0在Oracle的文档中有这样一段解释: db block g ... -
创建增量同步Oracle物化视图问题
2012-12-25 14:07 15185我们采用Oracle的物化视图增量刷新机制定时将数据库A上的某 ... -
查归档日志文件每小时生成量
2012-12-18 16:13 10731在O racle数据库中,通过v$archived_lo ... -
如何删掉临时表空间的文件
2012-12-18 15:40 0Unlike Oracle datafiles which m ... -
Oracle sql性能诊断暨event 10046和10053使用
2012-12-17 10:24 0早上em grid control监控显示数据库的负载增加,其 ... -
查LOB字段占用的空间大小
2012-12-13 16:00 4493查询Oracle表中LOB字段的占用空间大小。表中每一个LOB ... -
Oracle Data Guard的重做日志传输和应用状况监控
2012-12-04 14:05 4026这是一个用来监控Oracle Data Guard环境下从主库 ... -
Oracle sql loader使用速成
2012-11-30 14:14 4834Oracle SQL LOADER是Oracle的 ... -
Oracle 11g2的监听器配置
2012-11-29 14:34 4319Oracle的监听器服务注册 ... -
Deleting archivelog on physical standby with RMAN in Oracle 10g
2012-11-28 13:25 0Turns out to be quite easy ... -
to_char将number转成string的小技巧
2012-11-27 14:14 10573很多数据转换处理操作时,会遇到将0.007007040000转 ... -
to_date转成字符串时ORA-01843 not a valid month 问题分析
2012-11-26 16:38 29154(注,本文三度易稿) 在开发Oracle SQL或PL/SQL ... -
Data Guard 10g 的保护级别为RESYNCHRONIZATION问题
2012-11-25 11:01 1878Oracle的data guard创建完成 ...
相关推荐
Oracle有三种表连接技术,分别是嵌套连接、合并连接和哈希连接。以下就是对这三种表连接算法进行了详细的分析介绍,需要的朋友可以参考下
3.2.4 农场之表空间的分类 93 3.2.4.1 表空间与系统农场93 3.2.4.2 表空间与临时农场93 3.2.4.3 表空间与回滚农场94 3.2.5 逻辑结构之初次体会 94 3.2.5.1 逻辑结构之BLOCK 94 3.2.5.2 逻辑结构之TABLESPACE 95 3.2....
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
该接口可使“数据库操作中间件”连接当前主流的数据库,如Oracle、SQLServer、MySQL、Access等;参数要求:指示数据库类型,数据库相应的连接参数。 提供关闭数据库连接接口。该接口可关闭“数据库操作中间件”当前...
该接口可使“数据库操作中间件”连接当前主流的数据库,如Oracle、SQLServer、MySQL、Access 等;参数要求:指示数据库类型,数据库相应的连接参数。 提供关闭数据库连接接口。该接口可关闭“数据库操作中间件”当前...
Sql中的三种物理连接操作 嵌套循环连接(Nested Loop Join) 合并连接(Merge Join) 哈希匹配(Hash Join)
属性不依赖于其它非主属性,确保数据表中的每一列数据都和主键直接相关,而不能间接相关,即要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 1.5.3.2 举例 党员表 党员Id 党员姓名 组织Code 符合3NF ...
完全支持MySQL、Oracle、PostgreSQL、Microsoft SQL Server、Microsoft Access、IBM DB2、SQLite、Firebird、Sybase、SAP MaxDB、HSQLDB和Informix等多种数据库管理系统。 完全支持布尔型盲注、时间型盲注、基于错误...
1、完全支持MySQL、Oracle、PostgreSQL、MSSQL、Access、IBM DB2、SQLite、Firebird、Sybase、SAP MaxDB、HSQLDB和Informix等多种数据库管理系统。 2、完全支持布尔型盲注、时间型盲注、基于错误信息的注入、联合...
完全支持MySQL、Oracle、PostgreSQL、Microsoft SQL Server、Microsoft Access、IBM DB2、SQLite、Firebird、Sybase、SAP MaxDB、HSQLDB和Informix等多种数据库管理系统。 完全支持布尔型盲注、时间型盲注、基于错误...
完全支持MySQL、Oracle、PostgreSQL、Microsoft SQL Server、Microsoft Access、IBM DB2、SQLite、Firebird、Sybase、SAP MaxDB、HSQLDB和Informix等多种数据库管理系统。 完全支持布尔型盲注、时间型盲注、基于错误...
witnet-rust是用Rust编写的Witnet Decentralized Oracle Network协议的开源实现。组件witnet-rust实现了旨在在Witnet生态系统中工作的许多不同组件: :一个完全验证和存档的Witnet区块链节点。 :用于管理Witnet...
全面支持MySQL, Oracle, PostgreSQL, Microsoft SQL Server, Microsoft Access, IBM DB2, SQLite, Firebird, Sybase和SAP MaxDB数据库管理系统。 全面支持六种SQL注入技术:boolean-based盲注、time-based盲注、error...
使用Oracle企业管理器或手工方法创建基于STUDENT、COURSE和SCORE三表连接查询的一个视图。 4. 使用Oracle企业管理器或手工方法创建表STUDENT的一个同义词以及用来生成表STUDENT中主键SNO唯一值的一个序列。 5. 使用...
HashGenerator 哈希值生成校验工具,用于文件完整性检测. HashKracker 用来恢复和破解多个类型的哈希散列密码的哈希密码破解工具. HiddenFileFinder 快速查找文件库里的隐藏文件,定位隐藏文件,对隐藏文件进行操作. ...
《mysql管理之道:性能调优、高可用与监控》由资深mysql专家撰写,以最新的mysql版本为基础,以构建高性能mysql服务器为核心,从故障诊断、表设计、sql优化、性能参数调优、mydumper逻辑、xtrabackup热备份与恢复、...
它的设计初衷就是为了提高数据库连接池的性能,根据某些测试数据发现,BoneCP是最快的连接池。BoneCP很小,只有四十几K(运行时需要slf4j和guava的支持,这二者加起来就不小了),而相比之下 C3P0 要六百多K。 异步...
使用高级语言连接空间数据库,并实现查询功能,文件时java编程,包括数据库连接,GUI界面设计,调用oracle里面的过程,函数等,仅供参考
SQL注入是Internet上最危险、最有名的安全漏洞之一,本书是目前唯一一本专门致力于讲解SQL威胁的图书。本书作者均是专门研究SQL注入的安全专家,他们集众家之长,对应用程序的基本编码和升级维护进行全面跟踪,详细...