Oracle等待事件 — direct path read

SQL> select * from v$version;

 

BANNER

------------------------------------------------------

Oracle8i Release 8.1.6.0.0 - Production

PL/SQL Release 8.1.6.0.0 - Production

CORE    8.1.6.0.0       Production

TNS for 32-bit Windows: Version 8.1.6.0.0 - Production

NLSRTL Version 3.4.1.0.0 - Production

 

测试组发现一个查询功能执行很慢。经跟踪，发现该会话处于 direct path read 等待中：

 

SQL> select * from v$session_wait where sid = 7;

 

  SID   SEQ# EVENT               P1TEXT       P1

----- ------ ------------------- ------------ --

    7  11853 direct path read    file number   8

 

8号数据文件是临时表空间中的：

 

SQL> SELECT bytes/1024/1024 size_m, tablespace_name, file_name

  2    FROM dba_data_files where file_id = 8;

 

    SIZE_M TABLESPACE_NAME  FILE_NAME

---------- ---------------- ------------------------------------------

        61 T_TEMP           D:\ORACLE816\ORA81\ORADATA\XX\T_TEMP.ORA

 

SQL> select USERNAME, TEMPORARY_TABLESPACE

  2    from user_users where USERNAME = 'CENTER_XX';

 

USERNAME                       TEMPORARY_TABLESPACE

------------------------------ -----------------------

CENTER_XX                      T_TEMP

 

SQL> select file_name from dba_data_files

  2   where tablespace_name = 'T_TEMP';

 

FILE_NAME

------------------------------------------

D:\ORACLE816\ORA81\ORADATA\XX\T_TEMP.ORA

 

SQL>

 

61M的临时表空间对于这个操作实在是太小了。这个问题应该可以通过增大临时表空间来解决，不过主要原因应该还是在SQL上。

看一下此时所执行的SQL：

 

SQL> select sql_address from v$session where sid = 7;

 

SQL_ADDR

--------

158FD760

 

SQL> select sql_text from v$sqlarea where address = '158FD760';

 

SQL_TEXT

-------------------------

SELECT "AC01"."AAB001",
       "AC01"."AAC002",
       "KC01"."AKC020",
       "AC01"."AAC001",
       "AC01"."AAC003",
       "AC01"."AAC004",
       "KC01"."AKC021",
       "AC01"."AAC008",
       "AC01"."AAC006",
       "AC01"."ZKC056"
  FROM "AC01", "KC01"
 WHERE ("KC01"."AAC001" = "AC01"."AAC001") AND
       ((("AC01"."AAC003" LIKE '%的%')))

 

KC01和AC01这两个表的AAC001字段都建立了索引：

 

SQL> select index_name, column_name

  2    from user_ind_columns

  3   where table_name = 'AC01';

 

INDEX_NAME             COLUMN_NAME

------------------------------

PK_AC01              AAC001

 

SQL> ed

已写入文件 afiedt.buf

 

  1  select index_name, column_name

  2    from user_ind_columns

  3*  where table_name = 'KC01'

SQL> /

 

INDEX_NAME             COLUMN_NAME 

------------------------------

PK_KC01                            AAC001

 

SQL>

 

通过查看其执行计划，发现并没有使用到索引：

 

SQL> explain plan

  2  for

  3  SELECT "AC01"."AAB001",

  4           "AC01"."AAC002",

  5           "KC01"."AKC020",

  6           "AC01"."AAC001",

  7           "AC01"."AAC003",

  8           "AC01"."AAC004",

  9           "KC01"."AKC021",

 10           "AC01"."AAC008",

 11           "AC01"."AAC006",

 12           "AC01"."ZKC056"

 13      FROM "KC01", "AC01"

 14     WHERE ("KC01"."AAC001" = "AC01"."AAC001") AND

 15           ((("AC01"."AAC003" LIKE '%的%')));

 

已解释。

 

SQL> SELECT LPAD(' ',LEVEL*2)||'  '||OPERATION||' '||OBJECT_NAME||' '||OPTIONS AS "SELECT QUERY"

  2   FROM plan_table

  3   START WITH ID=0

  4   CONNECT BY PRIOR ID=PARENT_ID;

 

SELECT QUERY

-----------------------------------------------------------------------

    SELECT STATEMENT

      MERGE JOIN

        SORT  JOIN

          TABLE ACCESS AC01 FULL

        SORT  JOIN

          TABLE ACCESS KC01 FULL

 

已选择6行。

 

增加一条线索，将其改为基于cost的执行计划。测试一下，发现这样可以使用到KC01的索引：

 

SQL> delete plan_table;

 

已删除6行。

 

SQL> commit;

 

提交完成。

 

SQL> explain plan

  2  for

  3  SELECT /*+FIRST_ROWS*/

  4         "AC01"."AAB001",

  5           "AC01"."AAC002",

  6           "KC01"."AKC020",

  7           "AC01"."AAC001",

  8           "AC01"."AAC003",

  9           "AC01"."AAC004",

 10           "KC01"."AKC021",

 11           "AC01"."AAC008",

 12           "AC01"."AAC006",

 13           "AC01"."ZKC056"

 14      FROM "KC01", "AC01"

 15     WHERE ("KC01"."AAC001" = "AC01"."AAC001") AND

 16           ((("AC01"."AAC003" LIKE '%的%')));

 

已解释。

 

SQL> SELECT LPAD(' ',LEVEL*2)||'  '||OPERATION||' '||OBJECT_NAME||' '||OPTIONS AS "SELECT QUERY"

  2   FROM plan_table

  3   START WITH ID=0

  4   CONNECT BY PRIOR ID=PARENT_ID;

 

SELECT QUERY

----------------------------------------------------------------------

    SELECT STATEMENT

      NESTED LOOPS

        TABLE ACCESS AC01 FULL

        TABLE ACCESS KC01 BY INDEX ROWID

          INDEX PK_KC01 UNIQUE SCAN

 

SQL>

 

因为需要支持模糊查询，AC01只能使用全表扫描了，但对应查找KC01时，使用到了索引。

 

看一下这两张表的数据量：

 

SQL> select count(*) from ac01;

 

  COUNT(*)

----------

    284285

 

SQL> select count(*) from kc01;

 

  COUNT(*)

----------

    284285

 

SQL>

 

少扫描了KC01表，速度提高了很多。