Oracle等待事件 — direct path read - ORACLE MANAGING

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表，速度提高了很多。