[20141213]11g ACS的一些问题3.txt

[20141213]11g ACS的一些问题3.txt

--11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题，前一段时间看了2篇blog

https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/
https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/

--我以前也写过一篇blog,链接如下：
http://blog.itpub.net/267265/viewspace-721817/

--如果绑定变量值出现倾斜，如果执行次数很大，很难改变执行计划，当时提到最好的方法是加入提示/*+ BIND_AWARE */ .
--比如查询status=:b1 ,status='N'的值很少，执行计划走索引更佳，而status='Y'的值很多，执行计划走全表扫描更佳。
--但是由于查询以status='N'次数居多，查询计划几乎很难改变。导致当status='Y'的执行计划依旧是走索引。

--作者最后给出二个结论或者是规律：

查询v$sql_cs_histogram视图，如果ROWS_PROCESSED存在某种规律：

规律1：

0    COUNT of BUCKET_ID  0 will be incremented
1000 COUNT of BUCKET_ID  1 will be incremented
       ROWS_PROCESSED > 1e6    --> COUNT of BUCKET_ID  2 will be incremented

规律2：
1.仅仅两个BUCKET_ID，count不等于0的情况下，BUCKET_ID贴近时count相等,下一次执行才会生成新的执行计划(子光标)。
2.仅仅两个BUCKET_ID，count不等于0的情况下，BUCKET_ID不贴近时, 大的count ceil(count/3)=小的count,下一次执行如果
  大的count ceil(count/3)小的count，才会生成新的执行计划(子光标)。

--我昨天在测试另外一些情况时，遇到一些问题，做一个记录：
--如果我使用PL/SQL执行遇到一些奇怪的问题。

1.建立测试环境:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t_acs(n1  number, vc2  varchar2(10));

BEGIN
     for j in 1..1200150 loop
      if j = 1 then
       insert into t_acs values (j, 'j1');
      elsif j>1 and j       insert into t_acs values(j, 'j100');
      elsif j>101 and j       insert into t_acs values (j, 'j1000');
      elsif j>10001 and j      insert into t_acs values(j,'j10000');
     else
      insert into t_acs values(j, 'j>million');
     end if;
    end loop;
   commit;
END;
/

create index t_acs_i1 on t_acs(vc2);

SCOTT@test> select vc2, count(1) from t_acs group by vc2 order by 2;
VC2          COUNT(1)
---------- ----------
j1                  1
j100              100
j1000            1000
j10000         100000
j>million     1099049

--可以发现vc2的数据分布情况。建立直方图：
BEGIN
       dbms_stats.gather_table_stats
                   (user
                   ,'t_acs'
                   ,method_opt       => 'for all columns size skewonly'
                   ,estimate_percent => null
                   ,cascade          => true
                   ,no_invalidate    => false
                   );
END;
/
--注：我做全统计，这样统计比较准确。
--带入不同的值，主要查询v$sql_cs_statistics,v$sql_cs_histogram,v$sql_cs_selectivity三个视图。

2.为了测试方便，编写一个PLSQL脚本：
-- test acs1
-- 参数1查询条件，参数2执行次数。
declare
m number :=0 ;
lvc varchar2(10) = '&1' ;
begin
    for j in 1..&2
    loop
        select count(n1) into m from t_acs where vc2 = lvc;
    end loop;
end;
/

@acs1 j10000 20
@acs1 j>million 20

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> @dpc 2488vrgvjz63p ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  2488vrgvjz63p, child number 0
-------------------------------------
SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1
Plan hash value: 3774726486
-----------------------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |   569 (100)|
|   1 |  SORT AGGREGATE              |          |      1 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ACS    |    100K|   569   (1)|
|*  3 |    INDEX RANGE SCAN          | T_ACS_I1 |    100K|   293   (1)|
-----------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (VARCHAR2(30), CSID=852): 'j10000'
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("VC2"=:B1)

--说明:sql_id='2488vrgvjz63p',注意plsql代码会格式化sql语句，实际执行的sql语句是SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
--可以发现执行计划走索引，而实际上查询vc2='j>million',走全表扫描更佳。继续测试：

SCOTT@test> @acs1 j10000 1
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

--从这里看count没有变化。而实际上已经产生了子光标。
SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           1

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20
--从这里看count没有变化。

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           2

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           3

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> @acs1 j>million 18
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          21

--可以发现执行计划依旧没有变化。

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          22

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

--可以发现执行计划依旧没有变化。

SCOTT@test> @acs1 j>million 20
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          42

--可以发现执行计划依旧没有变化。v$sql_cs_histogram的count也不变化。

SCOTT@test> @acs1 j>million 21

PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AF85E260 4146042997 2488vrgvjz63p            0          0          0
00000000AF85E260 4146042997 2488vrgvjz63p            0          1         20
00000000AF85E260 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS
------------- --------------- ------------------------------------------------------------ ----------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          63

--问题依旧。执行计划使用索引。

--当查询v$sql_cs_histogram的2个相近的BUCKET_ID如果count相等，如果查询条件导致oracle再分析，这个时候如果走索引，
--无论以后带入条件如何，都选择走索引，再次说明11g 目前的acs存在许多缺陷。而且奇怪的是v$sql_cs_histogram不会看到
--CHILD_NUMBER=1的情况，里面的count不再变化，视乎这个时候关闭了acs，oracle的bug吗？

--如果这样执行呢？

alter system flush SHARED_POOL;
@acs1 j10000 20
@acs1 j>million 20

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          0          0
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          1         20
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40            0

@acs1 j>million 1

SCOTT@test> @acs1 j>million 1
PL/SQL procedure successfully completed.

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          0          0
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          1         20
00000000BE5A05D8 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40            0
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           1            1

--执行计划发生了变化(看PLAN_HASH_VALUE)。选择的是全表扫描。如果这次带入j1000参数情况如何呢？

SCOTT@test> @acs1 j10000 1
PL/SQL procedure successfully completed.

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40            0
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           2            1

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000BE932468 4146042997 2488vrgvjz63p            0          0          0
00000000BE932468 4146042997 2488vrgvjz63p            0          1         20
00000000BE932468 4146042997 2488vrgvjz63p            0          2         20

--当查询v$sql_cs_histogram的2个相近的BUCKET_ID如果count相等，如果查询条件导致oracle再分析，这个时候如果全表扫描，
--无论以后带入条件如何，都选择全表扫描。而且奇怪的是v$sql_cs_histogram不会看到CHILD_NUMBER=1的情况，里面的count不再
--变化，视乎这个时候关闭了acs，oracle的bug吗？

难道使用PL/SQL下有什么不同吗？

才用执行sql语句方式测试。

$ cat acs2.sql
-- test acs2
var B1 varchar2(10);
exec :B1 := '&1';
SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;

SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;

alter system flush SHARED_POOL;
@acs1 j10000
@acs1 j>million

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFC2D388 4146042997 2488vrgvjz63p            0          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            0          1         20
00000000AFC2D388 4146042997 2488vrgvjz63p            0          2         20

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40            0

SCOTT@test> var B1 varchar2(10);
SCOTT@test> exec :B1 := 'j>million' ;

PL/SQL procedure successfully completed.

SCOTT@test> SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1 ;
COUNT(N1)
----------
   1099049

--分号前有空格。

SCOTT@test> select * from v$sql_cs_histogram where sql_id='2488vrgvjz63p';
ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
00000000AFC2D388 4146042997 2488vrgvjz63p            1          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            1          1          0
00000000AFC2D388 4146042997 2488vrgvjz63p            1          2          1
00000000AFC2D388 4146042997 2488vrgvjz63p            0          0          0
00000000AFC2D388 4146042997 2488vrgvjz63p            0          1         20
00000000AFC2D388 4146042997 2488vrgvjz63p            0          2         20

6 rows selected.

SCOTT@test> select sql_id,plan_hash_value,sql_text,executions,child_number from v$sql where sql_id='2488vrgvjz63p';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT                                                     EXECUTIONS CHILD_NUMBER
------------- --------------- ------------------------------------------------------------ ---------- ------------
2488vrgvjz63p      3774726486 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                          40            0
2488vrgvjz63p       535703726 SELECT COUNT(N1) FROM T_ACS WHERE VC2 = :B1                           1            1

--可以发现使用PLSQL测试与SQL语句的执行方式存在不同。v$sql_cs_histogram看到CHILD_NUMBER=1的情况，里面的count有变化，生成
--的新执行计划也发生了变化。

时间： 2025-01-28 05:00:21

[20141213]11g ACS的一些问题3.txt的相关文章

[20141213]11g ACS的一些问题4.txt

[20141213]11g ACS的一些问题4.txt --11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题,前一段时间看了2篇blog https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/ https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/ --我以前也写过一篇blog,链接如下: http://blog.itp

[20141116]11g ACS的一些问题1.txt

[20141116]11g ACS的一些问题1.txt --11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题,昨天看了2篇blog https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/ https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/ --我以前也写过一篇blog,链接如下: http://blog.itpub.

[20170824]11G备库启用DRCP连接.txt

[20170824]11G备库启用DRCP连接.txt --//参考链接: http://blog.itpub.net/267265/viewspace-2099397/ blogs.oracle.com/database4cn/adg%e5%a4%87%e5%ba%93%e7%9a%84drcp%e8%bf%9e%e6%8e%a5%e6%8a%a5%e9%94%99oci-21500%e8%a7%a3%e5%86%b3%e4%b8%80%e4%be%8b 1.测试环境: SYS@bookdg>

[20170726]11G 12c expand sql text 2.txt

[20170726]11G 12c expand sql text 2.txt --//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来. --//讨论链接:http://www.itpub.net/thread-2088981-1-1.html --//感谢solomon_007的指点,通过建立动态sql语句来实现.链接:http://blog.itpub.net/267265/viewspace-2141010/ --//qqjue给出建立建立类似c的宏来实现C

[20160517]11G HugePage的使用问题2.txt

[20160517]11G HugePage的使用问题2.txt --昨天测试了HUGEPAGE的使用问题,自己也看了一些文档,从11.2.0.2,加入了参数use_large_pages. SYS@book> @ &r/ver1 PORT_STRING         VERSION        BANNER ------------------- -------------- -------------------------------------------------------

[20150705]11G表统计信息与PUBLISH.txt

[20150705]11G表统计信息与PUBLISH.txt --11G表统计信息可以先不发布(在PUBLISH参数的控制下),等检测合适再发布. --确实参数optimizer_use_pending_statistics为false,可以在session级别打开为true,检测统计是否有用. SYS@test> @hide optimizer_use_pending_statistics NAME                              DESCRIPTION       

[20150910]11G ADG与延迟日志应用.txt

[20150910]11G ADG与延迟日志应用.txt --11G ADG是一个非常好的特性,它可以一边应用日志,一边提供查询,前一阵子跟别人讨论ADG 是否可以与延迟日志应用结合起来,既 --提供只读查询,又延迟日志应用,自己从来没有测试过,今天测试看看. --实际上一种可能就是在dg上打开flashback,这样在出现问题时闪回到出问题的时间点.但是这个是回滚,而我延迟应用是前进. 1.测试环境: SCOTT@test> @ver BANNER ----------------------

[20150710]11G谓词推入问题2.txt

[20150710]11G谓词推入问题2.txt --生产系统遇到一个sql语句的问题. --生产系统的sql语句比较复杂,做一个简化的例子来说明问题.来说明自己优化遇到的困惑. --昨天看来别人的回复,加提示 /*+ push_pred(v_tallx)*/,无效.实际上如果仔细看我的帖子 --http://blog.itpub.net/267265/viewspace-1724554/, 可以发现T2表的id是字符类型,存在隐式转换,虽然我定义了函数索引,视乎 --对于这种情况谓词推入存在问

[20131027]11G的内部视图X$DBGALERTEXT.txt

[20131027]11G的内部视图X$DBGALERTEXT.txt 链接:http://www.askmaclean.com/archives/11g%E6%96%B0%E7%89%B9%E6%80%A7xdbgalertext%E4%B8%80%E4%B8%AA%E5%BE%88%E9%85%B7%E7%9A%84%E5%86%85%E9%83%A8%E8%A7%86%E5%9B%BE.html 以前我们看alert*.log文件,一般直接进入目录,直接查看.当然也可以通过外部表来访问al