oracle之内存―鞭辟近里（三）(六)
46 6avfua5g1gkh2 2013/7/11 22:3 16777219 0000000071DD1FB0 HASH-JOIN 8 AUTO 155 2954647 2848768 2847744 1271808 1271808 0
SQL>
哈哈，分析一下：
1）sql_hash_value:当前执行的sql语句的hash值：该值为：1578617346
2）sql_id:当前执行的sql命令的id号：6avfua5g1gkh2
3)SQL_EXEC_START:从会话开始执行该条语句的时间：2013/7/11 22:3
4）sql_exec_id:该语句执行的标示符：16777219
5）该语句所在的workarea的地址，可以通过该地址与v$sql_workarea进行连接查看该区域的更加详细信息，在学习v$sql_workarea会对此进行详细说明，改地址是唯一的且是该视图的主键：0000000071DD1F48
6）operation_type:使用该区域进行的操作类型（包括，sort，hash join，group by，buffering，bitmap merge，bitmap create）：HASH-JOIN
可以看一下该语句的执行计划如下：
Plan hash value: 456374238
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65381 | 12M| 247 (3)| 00:00:03 |
| 1 | VIEW | DBA_OBJECTS | 65381 | 12M| 247 (3)| 00:00:03 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 30 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | HASH JOIN | | 69509 | 8281K| 244 (3)| 00:00:03 |
| 9 | TABLE ACCESS FULL | USER$ | 87 | 1479 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 69509 | 7127K| 240 (2)| 00:00:03 |
| 11 | INDEX FULL SCAN | I_USER2 | 87 | 2001 | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | OBJ$ | 69509 | 5566K| 239 (2)| 00:00:03 |
|* 13 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
|* 16 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ4 | 1 | 8 | 1 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 105 | 3 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | LINK$ | 1 | 88 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
可以看到其实数据库内部进行了多个表的join，我们在深入研究一下看一下dba_objects这个表的内部创建语句。
SQL> R
DBMS_METADATA.GET_DDL('VIEW','
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_OBJECTS" ("OWNER", "OBJECT_NAME", "SUB
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACK