|
|
query block的名字
这段的实际应用意义不大，研究这个主要为了好玩。
前面提到我们可以用qb_name这个hint来定义SQL里面的query block的名字，实际上如果我们多看看
dbms_xplan.display_cursor的advanced模式的输出，或者看看10053 trace，或者看看10132 trace，就能发现其实oracle的CBO会
自己定义query block的名字，而且你会发现对于固定的SQL，这些query block的名字都是固定的。
这里打算介绍一下oracle是如何确定这些query block名字的，这已经超出了hint的范畴，但是如果你看到oracle给你列出这样的一条Hint，
了解query block的命名的由来对你读懂这个hint是有帮助的：
LEADING(@"SEL$F5BB74E1" "D"@"SEL$1" "EMPLOYEES"@"SEL$2")
--如果SQL多次出现employees这张表，你知道这个Hint是指哪个employees么？
其实一般来说，oracle对query block的命名还是很直观的，
如果是一个select，那么就命名为SEL$n，这里的n是1，2，3...
如果是delete，就命名为DEL$n，是update，就命名为UPD$n，是insert，就叫INS$n, 是merge，就叫MISC$n...
下面我举出两个例子，一个select, 一个delete，其他的大家可以自己去试。
explain plan for
select * from employees where salary = (select max(salary) from employees);
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
2 - SEL$2
3 - SEL$2 / EMPLOYEES@SEL$2
explain plan for
delete from employees;
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / EMPLOYEES@DEL$1
那为什么会出现类似于SEL$F5BB74E1这样的query block呢？这是因为oracle做了sql转化，我们知道oracle处于性能考虑会
把一些子查询转化为表与表连接的模式，SQL转化是个比较有意思的话题，包含的东西也很多（不仅仅针对子查询），
对于子查询而言，就是当你的SQL包含了子查询或者视图，oracle会对你的SQL进行转化，当然这种转化在10g里是基于cost的，也就是说如果转化后发现cost太高，这个转化就不会被采用。
对于oracle的SQL转化主要是做：
1） 转化SPJ (select + project + join) view
2） unnest Subquery
3） merge Complex View （mergable complex view的定义可见于oracle performance tuning guide)
4） Predicate Pushing， or expense, 物化视图重写，等。。。
那么当转化发生后，SQL的结构就变了，所以oracle会为转化后的SQL生成新的query block，为了和没有发生转化的SQL有所区别，
oracle在这里采用比较特殊的格式。
转化后oracle会把原来的一个外围的query block（假如名字是a), 和一个内部的query block(假如名字是b), 转化生成的新的query block的名字应该是a 和 b 的hash函数。
看看下面的例子：
SQL1，这是一个多层嵌套，每个嵌套的子查询都可以被转化，我们能看到oracle在做完转化后生成的新的query block为SEL$9C113579。
explain plan for
select /*+ qb_name(qb1) */ * from
(select /*+ qb_name(qb2) */ * from
(select /*+ qb_name(qb3) */ * from employees));
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9C113579 / EMPLOYEES@QB3
SQL2，是SQL1的第二层嵌套的查询，可以看到转化后生成的query block名字是SEL$1B0F72FE
explain plan for
select /*+ qb_name(qb2) */ * from
(select /*+ qb_name(qb3) */ * from employees);
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1B0F72FE / EMPLOYEES@QB3
SQL3，最外面得查询还是叫qb1，在里面随便套了一个子查询，但是我们把第二层查询的名字定义为SEL$1B0F72FE。
explain plan for
select /*+ qb_name(qb1) */ * from
(select /*+ qb_name(SEL$1B0F72FE) */ * from departments);
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE
可以看到转化后的query block的名字是SEL$9C113579，和我们的第一个SQL转化后得到的query block名字一致，这就可以证明，当oracle对SQL的子查询进行了转化后，
会生成新的query block的名字，这个名字是由原来的外围query block名字和原来的内层的query block名字决定的，估计是一个hash 函数。
如果我们把外面的query block名字改掉，得到的新的转化后的query block名字就会变了：
explain plan for
select /*+ qb_name(qbnew) */ * from
(select /*+ qb_name(SEL$1B0F72FE) */ * from departments);
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$90C23CFB / DEPARTMENTS@SEL$1B0F72FE --变了
下面这个例子可以看出，我把第二层的子查询做了一些改动，又套上了一些其他的子查询，但是使用no_merge hint来避免里面的子查询被oracle的CBO转化掉：
explain plan for
select /*+ qb_name(qb1) */ * from
(select /*+ qb_name(SEL$1B0F72FE) */ * from employees where department_id in
(select /*+ no_merge */ max(department_id) from departments where location_id in
(select /*+ no_merge */ max(location_id) from locations)));
select * from table(dbms_xplan.display(null,null,'alias'));
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE
2 - SEL$9C113579 / EMPLOYEES@SEL$1B0F72FE
3 - SEL$1
4 - SEL$1 / DEPARTMENTS@SEL$1
5 - SEL$1 / DEPARTMENTS@SEL$1
6 - SEL$2
7 - SEL$2 / LOCATIONS@SEL$2
可以看到，即使把内层的SQL做了这么多改变，最终转化出来的query block的名字还叫SEL$9C113579。
最后看一个特殊的例子：
这里是一个简单的2个表连接，没有子查询，但是我们可以看到在query block的名字是SEL$58A6D7F6，和我前面说的规则并不一样，为什么会这样？
explain plan for
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing';
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 76 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 4 | 28 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 4 | 76 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 12 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6 / D@SEL$1
3 - SEL$58A6D7F6 / L@SEL$1
4 - SEL$58A6D7F6 / L@SEL$1
5 - SEL$58A6D7F6 / D@SEL$1
原因在于这个SQL使用了ANSI的表连接语法，oracle在解析“join locations l on”这样的条件的时候会把它看做是一个子查询，于是乎会做转化，于是乎生成SEL$58A6D7F6这样的query block名字。
在10053 trace里面会看到(VIEW MERGE SEL$2; SEL$1)这样的字样， 然后query被转化为：
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing'
10053 trace的部分内容：
SQL:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM "HR"."DEPARTMENTS" "D","HR"."LOCATIONS" "L" WHERE "L"."CITY"='Beijing' AND "D"."LOCATION_ID"="L"."LOCATION_ID"
Query block (04B6A5E0) unchanged
Registered qb: SEL$58A6D7F6 0x4b6a5e0 (VIEW MERGE SEL$2; SEL$1)
signature (): qb_name=SEL$58A6D7F6 nbfros=2 flg=0
fro(0): flg=0 objn=51905 hint_alias="D"@"SEL$1"
fro(1): flg=0 objn=51900 hint_alias="L"@"SEL$1"
.........................................................
.........................................................
****************
QUERY BLOCK TEXT
****************
select department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing'
这对我们有什么影响吗？ 其实影响不大，我前面也说这一段不是为了使用，仅仅为了好玩，不过假如上面的SQL作为我们下面语句的子查询，并且我想让表locations走全表扫描，我按理应该可以这样写hint：
explain plan for
select /*+ full(@inner l) */ * from employees where department_id in
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 3159 | 6 (17)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 10 | 680 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 39 | 3159 | 6 (17)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 4 | 52 | 3 (0)| 00:00:01 |
| 4 | HASH UNIQUE | | 4 | 76 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 4 | 28 | 1 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 4 | 76 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 12 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$78C29F24 / EMPLOYEES@SEL$2
3 - SEL$49A70C8F / VW_NSO_1@SEL$78C29F24
4 - SEL$49A70C8F
5 - SEL$49A70C8F / D@SEL$1
7 - SEL$49A70C8F / L@SEL$1
8 - SEL$49A70C8F / L@SEL$1
9 - SEL$49A70C8F / D@SEL$1
10 - SEL$78C29F24 / EMPLOYEES@SEL$2
但如你所见，hint似乎没有生效，这个原因就是尽管我们给
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing')
这个子查询定义了query block的名字，但是因为这个是ANSI语法的连接，oracle会做一次转换，这次转化会给子查询生成一个新的query block(SEL$49A70C8F),
这进一步导致/*+ full(@inner l) */这个针对inner的Hint失效。
要想达到目的的方法也很简单， 比如hint可以这么写：
select * from employees where department_id in
(select /*+ full(l) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');
或者既然已经知道了新的query block的名字，我们就可以这么写（当然这种写法很奇怪，一般也不应该怎么写就是了）：
select /*+ full(@SEL$49A70C8F l) */ * from employees where department_id in
(select /*+ qb_name(inner) */ department_id from departments d join locations l on d.location_id=l.location_id where l.city='Beijing');
--如前所述，这里/*+ qb_name(inner) */还是需要的，否则转化后的query block名字又会不一样。
如果说能从这里例子总结点什么出来的话，就是如果你在oracle里写SQL，并估计以后可能需要给它加Hint，为了使用Hint方便，可以考虑尽量使用oracle的语法.
[ 本帖最后由 fusnow 于 2010-2-5 09:55 编辑 ] |
|
楼主: fusnow
IP卡
狗仔卡
发表于 2010-1-31 12:50
显身卡
发表于 2010-1-31 13:59
楼主
