crosscheck backup ORA-19554

朋友在rman中执行crosscheck backup出现ORA-19554错误

RMAN> crosscheck backup;

using channel ORA_DISK_1
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 01/23/2015 17:23:19
ORA-19554: error allocating device, device type: STB, device name:
ORA-27001: unsupported device type
Additional information: 1

提示找不到STB,根本就没有这种设备,下面执行命令来显示所有配置信息

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE STB TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'STB' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE STB TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE STB TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u02/test_df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/10.2.0/db/dbs/snapcf_test.f'; # default

上述信息中有
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ BACKUP TYPE TO BACKUPSET PARALLELISM 1;
说明确实有人设置了SBT_TAPE类型的设备信息,下面将其设置还原为默认值:

RMAN> configure device type 'SBT_TAPE' clear;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1;
RMAN configuration parameters are successfully reset to default value

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'STB' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'STB' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE 'STB' TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'STB' TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u02/test_df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/10.2.0/db/dbs/snapcf_test.f';

再来执行crosscheck backup命令正常执行:

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISKcrosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/test_df869734336_s15_s1 recid=5 stamp=869734336
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u02/test_df869734461_s16_s1 recid=6 stamp=869734463
Crosschecked 2 objects

还原点和闪回数据库

闪回数据库和还原点是两种与数据恢复相关的功能,它们用来替代按时间点恢复来撤消对数据库的改变。闪回数据库能让你将整个数据库进行回滚,使用指定时间窗口来撤消对数据库的改变。这类似于数据库的按时间点恢复。

还原点提供了与闪回数据库相关的能力它是另一种恢复操作。受保护的还原点,在特定的情况下,提供了闪回数据库完整的功能,允许你选择一个SCN并强制要求闪回数据库对这个SCN是可用的。

还原点和闪回数据库可以单独使用也可以一起使用。在这两种情况下,RMAN的flashback database命令或者flashback database语句实际上将数据库还原到一个指定的SCN所对应的状态,例如:

flashback database to restore point ‘before_upgrade’;
flashback databasae to scn 202381;

闪回数据库
闪回数据库,可以通过RMAN的flashback database命令和SQL*Plus的flashback database语句来完成。能够将数据库快速的从逻辑数据错误或用户错误中进行恢复。

它类似于传统的按时间点恢复一样,能够将数据库还原到过去某个时间点的状态。闪回数据库比按时间点恢复来快很多,因为闪回数据库不需要从备份中还原数据文件和从归档重做日志文件中应用相应的改变。

闪回数据库能被用来撤消对数据库不需要的改变。而数据文件完好无损。这包括将数据库还原到之前数据库化身对应的状态。这将撤消open resetlogs操作。

闪回数据库使用它自己的日志机制,它会在闪回区中创建闪回日志。只有在闪回日志是可用时才能使用闪回数据库。因此如果要使用这个功能就必须对数据库进行设置让其创建相关的闪回日志。

为了启用闪回数据库,需要设置闪回区和设置闪回保留策略目标来指定能够将数据库还原到过去某个时间点。

从启用闪回数据库开始,在定期的时间间隔内,数据库将会把每个数据文件中发生改变的数据块复制到闪回日志中。这些数据块镜像以后可用来重构数据文件的内容。

当数据库使用闪回数据库将数据库还原到过去的某些目标时间点时,自从还原时间点开始发生改变的每个数据块将从闪回日志中复制目标时间点对应的数据块。然后应用自数据块被复制到闪回日志时间点开始的重做日志。

注意:在整个时间跨度对于闪回日志相应的重做日志必须都是可用的。

闪回数据库窗口
在一定范围SCN之间有足够的闪回日志数据来支持flashback database命令这就叫闪回数据库窗口。如果闪回区的空间太小,通过设置保留策略当为了给新生成的闪回日志文件分配空间可能会删除过期的闪回日志。根据闪回区的大小,必须被保留的其它备份和需要的闪回日志数据,这可能造成闪回数据库窗口比闪回保留目标的时间要短。

注意:闪回保留目标是一个目标,不是一个绝对受保护能执行闪回数据库的目标。

如果闪回区的大小不能存储为了满足保留策略所需要的闪回日志和其它文件,比如归档重做日志和其它备份。那么为了存储其它文件从最早的SCN开始的闪回日志可能因为空间问题而被删除。

闪回数据库窗口不能对可用闪回日志中的最早SCN进行向前的扩展。闪回日志不能备份到闪回区之外的目录。因此为了增加可用的闪回日志来满足闪回数据窗口,所以要给闪回区可用的最大存储空间。

这里也有一些操作比如删除表空间或收缩数据文件,这些是不能通过闪回数据进行撤消的。在这些操作执行之后,闪回窗口就会立即从这些操作之后重新计算。

如果因为闪回数据库窗口不满足flashback database的要求,按时间点恢复可以最大程度的完成相似的任务。

使用受保护的还原点是唯一能保证你使用闪回数据库能将数据库还原点指定时间点或闪回窗口的方法。

正常还原点
通过指定时间点或SCN来作为还原点的名称来创建还原点,在执行有restore point子句的命令时使用这种标签或别名来指定SCN。

如果可能对你执行的任何操作执行撤消,那么可以创建一个正常的还原点。还原点名称和SCN会被记录到控制文件中。如果后面需要使用闪回数据库,闪回表或按时间点恢复,可以使用还原点名称来指示这个目标时间来代替一个时间表达式或SCN。在可能被撤消的操作执行前定义一个正常的还原点来消除需要手工记录SCN的需要,或在执行闪回查询时查找正确的SCN的需要。

正常还原点是非常轻量级的。控制文件可以包含上千个正常还原点而不会影响数据库的性能。正常还原点最终从控制文件中移除,而不是手工删除,那么它们将不再需要进行维护。

支持还原点的命令
在下面的上下文中还原点用来指定目标SCN:
1.RMAN的recover database和flashback database命令

2.SQL*Plus中的flashback database语句

注意:通常来说,受保护的还原点可以在任何使用正常还原点的操作中作为SCN的别名来使用。除非另有说明,否则正常还原点的使用方法也适用于受保护的还原点。

受保护的还原点
与正常还原点一样,受保护的还原点在恢复操作中也能作为SCN的别名。然而,它们也对闪回数据库提供了特定的功能。

在一个特定的SCN创建一个受保护的还原点可以强制通过执行闪回数据库能将数据库还原到这个SCN所处的状态,即使数据库没有启用闪回日志也能保证。如果闪回日志被启用,创建受保护的还原点强制闪回日志保留策略能满足将数据库闪回到创建受保护还原点后的任意时间点。

受保护还原点能用于将整个数据库还原到一个已知的状态良好的时间点,前提条件是闪回区有足够的空间来存储闪回日志。使用闪回数据库,受nologging操作影响的直接路径插入也能使用受保护的还原点来进行还原操作。

受保护的还原点替代存储快照
在实践中,受保护的还原点能够有效地替代存储快照,它通常用来在一些危险操作之前来保护数据库,比如大范围的更新,应用程序打补丁,或者升级。与创建快照或复制数据库再进行这些操作的相比,可以创建一个受保护的还原点然后执行这些危险操作,只要确只保留必要的闪回日志就行。

闪回数据库和受保护还原点的日志
闪回数据库和受保护还原点的日志是基于在改变应用前被捕获的数据块镜像,因此这些镜像能用来在执行flashback database命令时将数据文件还原到之前时间点所对应的状态。

正常闪回日志和受保护还原点的日志的主要差别就是日志是否会因为闪回区可用空间的压力而被删除。这些差别影响日志可用空间的使用和数据库的性能。

是否对闪回数据库,受保护的还原点启用日志都依赖于你的恢复目标,以及对性能的影响和这些功能对空间的使用。

受保护还原点和闪回区的空间使用
当创建一个受保护的还原点,不管有没有启用完全闪回数据库日志,你都必须监控闪回区可用空间的状态。如果为了满足受保护的还原点而需要的文件是不会满足从闪回区删除的条件。因此,闪回日志的保留策略和为了满足受保护的还的所需要的其它文件,就是满足备份保留策略一样,可能会让闪回区完全被使用掉。

注意:当因为保留策略不能删除任何文件而又遇到空间被使用完的情况时,在许多情况下数据库会被hang住。

闪回日志禁用时受保护还原点日志
如果当闪回数据库日志禁用时创建了受保护的还原点,那么,在受保护还原点创建之后第一次数据文件的数据块更改,在更改之前数据块的镜像会被存储到闪回日志中。闪回日志因此能还原被修改的数据块回到受保护还原点创建时的状态。然而后续对相同数据块的修改不会被记录在日志中,除非在这些修改之前创建另外的受保护还原点。

这种方式的日志有以下重要的影响:
1.可用的块镜像可以在使用flashback database时将数据文件的内容还原到受保护还原点所对应的状态但不能使用flashback database将数据库还原到受保护还原点与当前时间之间的某个时间点。只有当对闪回数据库启用日志才行。如果你需要将数据库还原到一个中间时间点,你只能使用数据库按时间点恢复。

2.因为每个数据块的改变只会被记录一次,当闪回日志被禁用时受保护还原点的日志所使用的空间要比正常闪回日志所使用的空间小。可以按天或周来维护受保护的还原点而不必关注当闪回数据库启用后闪回日志的增长。记录受保护的还原点的日志所产生的性能在禁用闪回数据库日志的情况下影响是很小的。

如果你主要是将数据库还原到受保护还原点所创建的时间点,那么通常关闭闪回数据库日志只使用受保护还原点更有效。例如当正计划在生产数据库服务器上对应用进行升级,在升级开始之前创建一个受保护的还原点,如果升级过程最终失败,可以使用flashback database撤消改变,而不用从备份中进行还原。

使用受保护还原点的闪回数据库日志
如果闪回数据库被启用并且定义了一个或多个受保护的还原点,那么数据库执行正常闪回日志时,会对性能有些影响。根据数据库的工作模式闪回区可能会产生较大的空间压力。然而,不像正常的闪回数据库日志,闪回区总是要保留为了使用flashback database将数据库还原到最早的当前受保护还原点时所需要的闪回日志。闪回日志如果要求满足受保护还原点恢复就不会因为空间压力而被删除。

在这种情况下对于flashback database可以将数据库恢复到闪回窗口中的任意时间点和特定的受保护的还原点所对应的状态,但你必须监控制闪回区的空间使用。

使用正常和受保护还原点

使用受保护还原点的要求
为了支持使用受保护还原点,数据库必须满足下面的要求:
1.compatible参数必须设置为10.2或更高版本

2.数据库必须运行在归档模式下。flashback database操作将数据库还原到受保护的还原点时要求使用一直到还原点以来的归档重做日志。

3.必须设置闪回区.受保护的还原点使用了一种类似于闪回日志的机制,Oracle必须在闪回区存储所要使用的日志

4.如果没有启用闪回数据库,当创建第一个受保护的还原点心(或者如果所有之前创建的受保护的还原点已经被删除后)那么数据库必须处于mount状态,不能是open状态。

注意:在使用正常的还原点没有特殊的要求。

创建正常和受保护还原点
为了创建正常或受保护还原点,使用create restore point语句,并给还原点提供了一个名称并指定它是受保护还原点还是正常还原点(默认值)。

在创建还原点时数据库可以处于open或mount状态。如果是mount状态,那么必须已经完全关闭(物理备库除外)。

下面的命令用来创建一个正常还原点

SQL> create restore point before_truncate;

Restore point created.

下面的命令用来创建一个受保护还原点

SQL> create restore point before_truncate guarantee flashback database;
create restore point before_truncate guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'BEFORE_TRUNCATE'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.

根据错误提示可以看出当数据库没有启用flashback database时想要创建受保护的还原点只能在mount状态下创建。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> create restore point before_truncate guarantee flashback database;

Restore point created.


SQL> col name for a20
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
  3  FROM V$RESTORE_POINT;

NAME                        SCN TIME                                DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
-------------------- ---------- ----------------------------------- --------------------- ---------------------------- ------------
BEFORE_TRUNCATE         2849315 27-JAN-15 02.40.56.000000000 PM                         2 YES                          8192000

显示还原点
为了查看当前定义的还原点,可以使用v$restore_point控制文件视图:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

SQL> col name for a20
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
  3  FROM V$RESTORE_POINT;

NAME                        SCN TIME                                DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE STORAGE_SIZE
-------------------- ---------- ----------------------------------- --------------------- ---------------------------- ------------
BEFORE_TRUNCATE         2849315 27-JAN-15 02.40.56.000000000 PM                         2 YES                          8192000

可以看到每个还原点的名称,SCN,时间和还原点创建时的数据库对应化身的序列号,是正常还原点还是受保护还原点,以及为了提供足够的信息来支持闪回数据库操作该还原点在闪回区所使用的空间大小。

可以使用只查询受保护的还原点信息:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';


SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
  3  FROM V$RESTORE_POINT
  4  WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                        SCN TIME                                                                        DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE   STORAGE_SIZE
-------------------- ---------- --------------------------------------------------------------------------- --------------------- ------------------------------ ------------
BEFORE_TRUNCATE         2849315 27-JAN-15 02.40.56.000000000 PM                                                                 2 YES                                 8192000

对于正常还原点,storage_size为0。对于受保护的还原点storage_size指示了为了保证能对还原点执行flashback database操作所要保留日志所消耗的闪回区的空间大小。

下面的正常还原点的storage_size确实为0:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
  2  GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
  3  FROM V$RESTORE_POINT
  4  WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                        SCN TIME                                                                        DATABASE_INCARNATION# GUARANTEE_FLASHBACK_DATABASE   STORAGE_SIZE
-------------------- ---------- --------------------------------------------------------------------------- --------------------- ------------------------------ ------------
BEFORE_TRUNCATE         2849315 27-JAN-15 02.40.56.000000000 PM                                                                 2 YES                                 8192000



SQL> select count(*) from tt;

  COUNT(*)
----------
     51281

SQL> truncate table tt;

Table truncated.

SQL> select count(*) from tt;

  COUNT(*)
----------
         0


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL>  select  flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL> flashback database to restore point before_truncate;

Flashback complete.



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>  alter database open resetlogs;

Database altered.

SQL> select count(*) from tt;

  COUNT(*)
----------
     51281

删除还原点
当不再需要已经存在的还原点时或者想用已经存在的还原点名称来创建一个新的还原点时就可以删除该还原点,下面使用dorp restore point语句还删除还原点:

SQL> drop restore point before_truncate;

Restore point dropped.

drop restore point用于删除正常还原点受保护还原点。

注意:正常还原不管有没有被显式删除最终都会从控制文件中删除。控制文件中还原点保留规则如下:
1.在控制文件中总是会保留最近的2048个还原点
2.只有还原点比比control_file_record_keep_time值所保留的还原点更新,而不管定义还原点的个数

正常还原点只要不满足上述条件就会从控制文件中删除。
受保护还原点从不会因为自动地从控制文件中删除,除非显式删除它们。

监控受保护还原点的空间使用情况
当在数据库中定义受保护还原点后,应该监控为了满足还原点而被存储的相关文件在闪回区中的空间使用情况。可以使用下面的查询语句查看storage_size列来判断每个受保护还原点相关文件的大小。

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

为了查看闪回区的使用大小可以查询v$recovery_file_dest和v$flash_recovery_area_usage视图:

SQL> select * from v$recovery_file_dest;

NAME                                                                             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------------
/u01/app/oracle/flash_recovery_area                                               2147483648    9699328           9699328               1
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                    0                         0               0
BACKUPPIECE                   0                         0               0
IMAGECOPY                  0.45                      0.45               1
FLASHBACKLOG                  0                         0               0

Oracle闪回数据库的设置与维护

闪回数据库的限制
因为阀回数据库是用来撤消某一时刻对数据文件所做的改变,因此它有以下限制:
1.闪回数据库只能撤消对数据文件的改变。它不能用于修复介质故障或者对意外删除的数据文件进行恢复。

2.不能使用闪回数据库来撤消对数据文件的收缩操作

3.如果数据库的控制文件从备份中进行了还原或者重新创建了控制文件,那么之前所累积的闪回日志信息将会被废弃。不能使用flashback database将数据库还原到还原控制文件或重新创建控制文件之前的某个时间点。

4.使用闪回数据库还原到某个时间点时,如果在这个时间点数据库正在执行nologging操作,那么在闪回数据库完成之后,受nologging操作影响的对象和数据文件可能存在坏块。例如,如果在2015-01-27的9:00到9:15正在以nologging模式执行直接路径插入操作,而后来你需要使用闪回数据库将数据库还原到2015-01-27的9:07分所对应的状态,那么在闪回数据库操作完成之后,由直接路径插入操作相关的对象和数据文件可有坏块。

如果可能要避免闪回数据库将数据库还原有nologging操作的时间点。也可以在任何nologging操作之后为了确保能恢复到该操作之后某个时间点可以执行全备或增量备份。如果你期望使用闪回数据库将数据库还原到某个操作执行的时间点,比如直接路径插入,可以考虑在logging模式下执行这些操作。

启用闪回数据库的条件
启用闪回数据库需要满足以下条件:
1.数据库必须运行在archivelog模式下,因为在执行闪回数据库操作时需要归档重做日志

2.必须设置闪回区,因为闪回日志只能存储在闪回区中

3.对于RAC环境中的数据库,闪回区必须是可以共享访问的集簇文件系统或者ASM

启用闪回日志
为了对闪回数据库启用闪回日志,要设置db_flashback_retention_target初始化参数并执行alter database flashback on语句。下面是启用闪回日志的过程:
1.确保数据库处于mount状态而不是open状态。例如:

SQL> shutdown immediate

SQL> startup mount

2.可选操作,可以将db_flashback_retention_target参数的值设置为你所期待的闪回窗口以分钟为单位

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

db_flashback_retention_target的默认值是一天(1440分钟)

3.使用v$database.FLASHBACK_ON可以查看闪回日志是否已启用,现在是“NO”,表示尚未启用:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
4.对整个数据库启用闪回数据库功能:
SQL> ALTER DATABASE FLASHBACK ON;

从此,在快速恢复区的flashback子目录下将会出现扩展名为.flb的文件,它们就是闪回日志。

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

闪回数据库命令的语法很简单:在进入MOUNT状态后执行flashback database to scn xxx 或flashback database to timestamp xxx 。

一次闪回数据库操作可能由两种方式进行。比如,令当前时间点为T_C,数据库闪回的目标时间点为T_1(T_C大于T_1),使用的命令为“flashback database to timestamp T_1”,该命令会自动选择以下两种方式的其中一个进行闪回操作。

方式一:

(1)第一阶段:利用闪回日志将数据库从T_C回退至较T_1更旧的某一时刻T_2。
(2)第二阶段:利用重做日志将数据库从T_2前滚至T_1。

方式二:

(1)第一阶段:确定一个比T_1更旧的某一时刻T_2。
(2)第二阶段:对于闪回日志根本无法恢复的对象或数据,把当初此类对象第一次产生的时间为T_3,首先利用T_3到T_2的重做日志将其重新产生并恢复。
(3)第三阶段:利用闪回日志将数据库从T_C回退至T_2。
(4)第四阶段:利用重做日志将数据库从T_2前滚至T_1(途中经过T_2)。

究竟采用哪种方式执行取决于目标时间点T_1与当前时间点T_C之间执行过什么命令,如果是在闪回日志中有对应反向操作的命令(如insert、update、delete)就采用方式一;如果在闪回日志中找不到对应反向操作的命令(比如:truncate命令)则采用方式二。方式二较方式一可能会索要更多的重做日志才可以完成闪回。

比如,若有任何对象发生过截断操作(truncate table xxx),那么方式二中的T3就是当初创建被截断的表的时刻,这意味着整个闪回操作需要从T3至T1的所有的重做日志,否则闪回数据库不可能成功。这样,可能会发生这样的问题:即使只是在10秒前错误地截断了一张表,现需要回到10秒之前,但是闪回数据库时flashback database命令向管理员索要几年前的归档日志!

如果T_1和T_C之间只是发生过类似update那样的DML,若是想回到10秒前,那么会采用方式一,差不多只是需要10秒的闪回日志和归档日志,闪回会很快,这也是闪回的真谛。

无论哪种方式,都可能遇到重做日志不充分的问题,下面来模拟这种情况:

1.检查数据库是否启用了闪回数据库,从下面的查询结果可知当前数据库没有启用闪回数据库

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

2.创建一个测试表,该表共有51282条记录

SQL> create table jy as select * from dba_objects;

Table created.

SQL> select count(*) from jy;

  COUNT(*)
----------
     51282

3.查询一下当前数据库的SCN号

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2879920

4.重启数据库并启用闪回数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

5.向表jy中增加一倍的记录

SQL> insert into jy select * from jy;

51282 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from jy;

  COUNT(*)
----------
    102564

6.查询当前数据库的SCN号

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2880218

7.执行闪回数据库,将表jy的记录恢复到只有51282条记录

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.


SQL> flashback database to scn 2879920;
flashback database to scn 2879920
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 2879811 to SCN 2879920
ORA-38761: redo log sequence 19 in thread 1, incarnation 2 could not be accessed

也可能遇到闪回日志不够的问题,报错信息如下所示:

SQL> flashback database to scn 2879920;
flashback database to scn 2879920
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

这里之所以不能闪回到SCN为2879920的时间点,是因为那时数据库没有启用闪回。

8.查看一下v$flashback_database_log视图,检查最远可以回到哪里

SQL> select oldest_flashback_scn,
  2  to_char(oldest_flashback_time,'YYYY-MM-DD HH24:MI:SS')
  3  from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLAS
-------------------- -------------------
             2880101 2015-01-27 23:32:18

从查询结果可以看到数据库最远可以闪回到SCN号为2880101的时间点

9.在MOUNT状态发起闪回操作后,管理员可以反复地以任何顺序执行这3个命令:flashback database to xxx,recover database和alter database open read only

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.

SQL> flashback database to scn 2880210;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from jy;

  COUNT(*)
----------
    102564

从上述结果可以看到现在将数据库闪回到SCN为2880210的时间点,表jy的记录数为102564不是我们期待的51282

10.再次将数据库闪回到SCN号为2880150的时间点

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> flashback database to scn 2880150;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select count(*) from jy;

  COUNT(*)
----------
     51282

从上述结果可以看到将数据库闪回到SCN为2880150的时间点时表jy的记录为51282达到我们期待的结果。

11.完成闪回数据库的最后操作。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL>  select count(*) from jy;

  COUNT(*)
----------
     51282


默认情况下对于所有永久表空间都会生成闪回日志。你也可以通过对指定的表空间来禁用闪回日志来减少开销:
SQL> ALTER TABLESPACE users FLASHBACK OFF;

可以使用下面的命令对表空间重新启用闪回日志:
SQL>alter tablespace users flashback on;

注意如果对一个表空间禁用了闪回数据库,那么在执行flashback database之前要确保该表空间的数据文件处于脱机状态。

对整个数据库禁用闪回日志:
SQL>alter database flashback off;

RMAN的改变跟踪

RMAN的改变跟踪
RMAN的改变跟踪功能通过在改变跟踪文件中记录每一个数据文件发生改变的数据块来提高增量备份的性能。如果改变跟踪被启用,RMAN使用改变跟踪文件来标识自上次增量备份以来发生改变的数据块,这样就能避免扫描每个数据文件的所有数据块。

在启用改变跟踪后,第一次level 0级的增量备份仍然会扫描每个数据文件的所有数据块,这时改变跟踪文件不能反应数据块的状态。后续的增量备份将使用level 0级的增量备份作为父备份集这样就可以利用改变跟踪文件来进行增量备份。

使用改变跟踪不会改变执行增量备份的命令,改变跟踪本身在设置后通常需要较少的维护。

改变跟踪默认情况下是禁用的,因为它在数据库的正常操作期间会有一些小的性能开销。然而,为了在备份期间对数据文件执行完全扫描,且在两次备份期间只有少量数据块发生改变时,使用改变跟踪就是很有用的。如果你的备份策略使用增量备份,那么应该启用改变跟踪。

一旦对整个数据库创建了改变跟踪文件,默认情况下改变跟踪文件所生成的目录是由
db_create_file_dest参数来决定的。也可以在启用改变跟踪时指定改变跟踪的文件名和存储目录。

注意:在RAC环境下,为了让所有的节点都能使用改变跟踪文件应该将改变跟踪文件存储在共享存储中

Oracle保存足够的改变跟踪文件能让增量备份使用最近8个增量备份作为它的父备份。

虽然RMAN不支持对改变跟踪文件本身的备份和恢复,如果整修数据库或部分需要还原和恢复,那么恢复对改变跟踪没有影响。在还原和恢复之后,改变跟踪文件会被清除,并再次开始记录数据块的改变。在任何恢复之后下一次的增量备份都能够使用改变跟踪的数据。

启用或禁用块改变跟踪
存储块改变跟踪文件的目录是由db_create_file_dest参数来设置的,下面的语句用来启用块改变跟踪:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

还可以在启用块改变跟踪时指定块改变跟踪文件创建的目录

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;

REUSE选项告诉Oracle会覆盖已经存在的块跟踪文件

为了禁用块改变跟踪:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

如果块改变跟踪文件存储在数据库区域,当禁用改变跟踪时会删除。

SQL> alter database enable block change tracking using file '/u01/app/oracle/rman_change_trace.f' reuse;

Database altered.

检查是否生成了改变跟踪文件

[root@oracle11g oracle]# ls -lrt
total 11632
drwxr-xr-x  3 oracle oinstall     4096 Sep  5 08:47 10.2.0
drwxrwxr-x  3 oracle oinstall     4096 Sep  5 09:12 oradata
drwxr-x---  3 oracle oinstall     4096 Sep  5 09:12 admin
drwxrwxr-x  3 oracle oinstall     4096 Sep  5 09:13 flash_recovery_area
-rw-r--r--  1 oracle oinstall   226495 Nov 18 10:55 utlu112i_8.sql
-rw-r--r--  1 oracle oinstall    10373 Nov 18 11:02 upgrade_info.log
drwxr-xr-x  3 root   root         4096 Nov 18 21:00 11.2.0
drwxrwx---  7 oracle oinstall     4096 Nov 18 21:37 oraInventory
drwxrwxr-x 11 oracle oinstall     4096 Nov 18 21:37 diag
drwxr-xr-x  2 oracle oinstall     4096 Nov 18 21:39 checkpoints
drwxr-x---  5 oracle oinstall     4096 Nov 19 18:55 cfgtoollogs
-rw-r--r--  1 oracle oinstall     3261 Nov 23 21:24 upchk.log
-rw-r--r--  1 oracle oinstall     2237 Nov 26 11:55 downgrade.log
-rw-r-----  1 oracle oinstall 11600384 Jan 26 21:32 rman_change_trace.f

禁用块改变跟踪:

SQL> alter database disable block change tracking;

Database altered.

检查是否删除了生成的改变跟踪文件

[root@oracle11g oracle]# ls -lrt
total 284
drwxr-xr-x  3 oracle oinstall   4096 Sep  5 08:47 10.2.0
drwxrwxr-x  3 oracle oinstall   4096 Sep  5 09:12 oradata
drwxr-x---  3 oracle oinstall   4096 Sep  5 09:12 admin
drwxrwxr-x  3 oracle oinstall   4096 Sep  5 09:13 flash_recovery_area
-rw-r--r--  1 oracle oinstall 226495 Nov 18 10:55 utlu112i_8.sql
-rw-r--r--  1 oracle oinstall  10373 Nov 18 11:02 upgrade_info.log
drwxr-xr-x  3 root   root       4096 Nov 18 21:00 11.2.0
drwxrwx---  7 oracle oinstall   4096 Nov 18 21:37 oraInventory
drwxrwxr-x 11 oracle oinstall   4096 Nov 18 21:37 diag
drwxr-xr-x  2 oracle oinstall   4096 Nov 18 21:39 checkpoints
drwxr-x---  5 oracle oinstall   4096 Nov 19 18:55 cfgtoollogs
-rw-r--r--  1 oracle oinstall   3261 Nov 23 21:24 upchk.log
-rw-r--r--  1 oracle oinstall   2237 Nov 26 11:55 downgrade.log

SQL> select filename from v$block_change_tracking;

FILENAME
——————————————————————————–
/u01/app/oracle/rman_change_trace.f

2.关闭数据库.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

3.使用操作系统命令将块改变跟踪文件移动到新的位置

[root@oracle11g oracle]# mv rman_change_trace.f rman_change_trace_new.f

4.mount数据库,更改块改变跟踪文件

SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/rman_change_trace.f' to '/u01/app/oracle/rman_change_trace_new.f';

Database altered.

5.打开数据库

SQL> alter database open;

Database altered.

SQL> alter database disable block change tracking;

Database altered.

[root@oracle11g oracle]# ls -lrt
total 284
drwxr-xr-x  3 oracle oinstall   4096 Sep  5 08:47 10.2.0
drwxrwxr-x  3 oracle oinstall   4096 Sep  5 09:12 oradata
drwxr-x---  3 oracle oinstall   4096 Sep  5 09:12 admin
drwxrwxr-x  3 oracle oinstall   4096 Sep  5 09:13 flash_recovery_area
-rw-r--r--  1 oracle oinstall 226495 Nov 18 10:55 utlu112i_8.sql
-rw-r--r--  1 oracle oinstall  10373 Nov 18 11:02 upgrade_info.log
drwxr-xr-x  3 root   root       4096 Nov 18 21:00 11.2.0
drwxrwx---  7 oracle oinstall   4096 Nov 18 21:37 oraInventory
drwxrwxr-x 11 oracle oinstall   4096 Nov 18 21:37 diag
drwxr-xr-x  2 oracle oinstall   4096 Nov 18 21:39 checkpoints
drwxr-x---  5 oracle oinstall   4096 Nov 19 18:55 cfgtoollogs
-rw-r--r--  1 oracle oinstall   3261 Nov 23 21:24 upchk.log
-rw-r--r--  1 oracle oinstall   2237 Nov 26 11:55 downgrade.log

如果不能关闭数据库,那么你必须禁用改变跟踪,再在重新启用时指定新的目录。例如:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

如果你选择使用这种方法,将会丢失改变跟踪文件中的内容。直到你下次完成level 0级的增量备份之前,RMAN将不得不扫描整个文件

评估改变跟踪文件的大小
改变跟踪文件的大小与数据库的大小和重做日志线程数成正比。它的大小不与数据库的频繁更新相关。通常来说块改变跟踪所需要的空间大小是将被跟踪的数据块大小的1/30000。注意,然而下面的两种原因可能会让改变跟踪文件比评估的大小大很多:
1.为了避免随着数据库的增长而要给改变跟踪文件分配空间所产生的开销,改变跟踪文件一开始创建时就是10M,并且以10M的大小来增加。因此对于任何大小接近300G的数据库来说,改变跟踪文件的大小不小于10M,对于大小接近600G的数据库来说,改变跟踪文件的大小不小于20M,依此类推。

2.对于每个数据文件,不管改变跟踪文件有多大都会在改变跟踪文件中将分配320K的空间。因此,如果有大量的小数据文件,改变跟踪文件将会比包含同样数据量的少量的大数据文件对应的改变跟踪文件大

乱用Hint造成性能问题案例二

某医保系统在业务高峰期间相关模块使用不了查询不出结果,生成业务高峰期间上午10点到11点期间的AWR报告
1

2

从上面的top sql部分可以看到执行时间最长的已经达到19019秒,还有几个运行时间也要执行几千秒,其中SQLID为d7bv3q1camq5x的SQL逻辑读和物理读都上几千万甚至上亿次。而每次也就返回200多行记录。
3

4

其中SQL语句为:

select /*+ rule */
 a.stat_type,
 a.his_item_code,
 a.his_item_name,
 a.item_code,
 max((select count(*)
       from mt_fee_fin aa
      where a.hospital_id = aa.hospital_id
        and a.serial_no = aa.serial_no
        and a.item_code = aa.item_code)) as item_sn,
 a.item_name,
 a.medi_item_type,
 a.price,
 sum(a.dosage) as dosage,
 a.model,
 replace(a.standard, '  ', '') as standard,
 sum(a.money) as money,
 sum(nvl(d.audit_money, 0)) as audit_money,
 d.hosp_reason_staff as hosp_reason_staff,
 d.hosp_reason_date as hosp_reason_date,
 d.hosp_reason_staffid as hosp_reason_staffid,
 d.hosp_reason as hosp_reason,
 d.center_resualt as center_resualt,
 d.center_flag as center_flag,
 d.audit_reason_id as audit_reason_id,
 sum(nvl(b.all_cash, 0)) as all_cash,
 (case
   when a.medi_item_type = '0' then
    (SELECT bo_flag
       FROM bs_item
      WHERE bs_item.item_code = a.item_code
        AND ROWNUM < 2)
   else
    (SELECT bo_flag
       FROM bs_medi
      WHERE bs_medi.medi_code = a.item_code
        AND ROWNUM < 2)
 end) as bo_flag,
 sum(nvl(b.part_cash, 0)) as part_cash,
 decode(nvl(d.audit_reason_id, 0),
        0,
        d.audit_reason,
        '%%' || to_char(d.audit_reason_id) || '%%') as audit_reason
  from mt_fee_fin a,
       pm_account_biz c,
       pm_fee_audit d,
       (select hospital_id,
               serial_no,
               policy_item_code,
               serial_fee,
               fee_batch,
               SUM(decode(fund_id,
                          '999',
                          decode(b.label_flag, '101', real_pay, 0),
                          '003',
                          decode(label_flag, '101', real_pay, 0),
                          0)) AS all_cash,
               SUM(decode(fund_id,
                          '999',
                          decode(b.label_flag, '102', real_pay, 0),
                          '003',
                          decode(label_flag, '102', real_pay, 0),
                          0)) AS part_cash
          from mt_pay_record_fin b
         where b.hospital_id = '4307210003'
           and b.serial_no = '25735455'
           and serial_fee <> 0
           and valid_flag = '1'
         group by hospital_id,
                  serial_no,
                  policy_item_code,
                  serial_fee,
                  fee_batch) b
 where a.hospital_id = c.hospital_id
   and a.serial_no = c.serial_no
   and a.hospital_id = '4307210003'
   and a.serial_no = '25735455'
   and a.hospital_id = b.hospital_id(+)
   and a.serial_fee = b.serial_fee(+)
   and a.serial_no = b.serial_no(+)
   and a.fee_batch = b.fee_batch(+)
   and a.valid_flag = '1'
   and c.valid_flag = '1'
   and d.audit_staff_id(+) = 2103
   and d.AUDIT_PHASE(+) = '1'
   and d.serial_fee(+) <> 0
   and a.serial_fee = d.serial_fee(+)
   and d.account_id(+) = 16905170
   and c.account_id = 16905170
 group by a.stat_type,
          a.item_name,
          a.his_item_name,
          a.price,
          a.his_item_code,
          a.item_code,
          a.medi_item_type,
          a.model,
          a.standard,
          d.hosp_reason,
          d.center_resualt,
          d.center_flag,
          d.hosp_reason_staff,
          d.hosp_reason_date,
          d.hosp_reason_staffid,
          d.audit_reason_id,
          d.audit_reason
 Order By a.stat_type, a.item_name, a.his_item_name

在程序的SQL语句使用了/*+ rule */提示,就会让优化器使用RBO,不使用CBO。 这样使得优化器少了许多选择CBO高效执行计划的机会。当我们去掉/*+ rule */,使用CBO时其执行结果如下:

SQL> select 
  2   a.stat_type,
  3   a.his_item_code,
  4   a.his_item_name,
  5   a.item_code,
  6   max((select count(*)
  7         from mt_fee_fin aa
  8        where a.hospital_id = aa.hospital_id
  9          and a.serial_no = aa.serial_no
 10          and a.item_code = aa.item_code)) as item_sn,
 11   a.item_name,
 12   a.medi_item_type,
 13   a.price,
 14   sum(a.dosage) as dosage,
 15   a.model,
 16   replace(a.standard, '  ', '') as standard,
 17   sum(a.money) as money,
 18   sum(nvl(d.audit_money, 0)) as audit_money,
 19   d.hosp_reason_staff as hosp_reason_staff,
 20   d.hosp_reason_date as hosp_reason_date,
 21   d.hosp_reason_staffid as hosp_reason_staffid,
 22   d.hosp_reason as hosp_reason,
 23   d.center_resualt as center_resualt,
 24   d.center_flag as center_flag,
 25   d.audit_reason_id as audit_reason_id,
 26   sum(nvl(b.all_cash, 0)) as all_cash,
 27   (case
 28     when a.medi_item_type = '0' then
 29      (SELECT bo_flag
 30         FROM bs_item
 31        WHERE bs_item.item_code = a.item_code
 32          AND ROWNUM < 2)
 33     else
 34      (SELECT bo_flag
 35         FROM bs_medi
 36        WHERE bs_medi.medi_code = a.item_code
 37          AND ROWNUM < 2)
 38   end) as bo_flag,
 39   sum(nvl(b.part_cash, 0)) as part_cash,
 40   decode(nvl(d.audit_reason_id, 0),
 41          0,
 42          d.audit_reason,
 43          '%%' || to_char(d.audit_reason_id) || '%%') as audit_reason
 44    from mt_fee_fin a,
 45         pm_account_biz c,
 46         pm_fee_audit d,
 47         (select hospital_id,
 48                 serial_no,
 49                 policy_item_code,
 50                 serial_fee,
 51                 fee_batch,
 52                 SUM(decode(fund_id,
 53                            '999',
 54                            decode(b.label_flag, '101', real_pay, 0),
 55                            '003',
 56                            decode(label_flag, '101', real_pay, 0),
 57                            0)) AS all_cash,
 58                 SUM(decode(fund_id,
 59                            '999',
 60                            decode(b.label_flag, '102', real_pay, 0),
 61                            '003',
 62                            decode(label_flag, '102', real_pay, 0),
 63                            0)) AS part_cash
 64            from mt_pay_record_fin b
 65           where b.hospital_id = '4307210003'
 66             and b.serial_no = '25735455'
 67             and serial_fee <> 0
 68             and valid_flag = '1'
 69           group by hospital_id,
 70                    serial_no,
 71                    policy_item_code,
 72                    serial_fee,
 73                    fee_batch) b
 74   where a.hospital_id = c.hospital_id
 75     and a.serial_no = c.serial_no
 76     and a.hospital_id = '4307210003'
 77     and a.serial_no = '25735455'
 78     and a.hospital_id = b.hospital_id(+)
 79     and a.serial_fee = b.serial_fee(+)
 80     and a.serial_no = b.serial_no(+)
 81     and a.fee_batch = b.fee_batch(+)
 82     and a.valid_flag = '1'
 83     and c.valid_flag = '1'
 84     and d.audit_staff_id(+) = 2103
 85     and d.AUDIT_PHASE(+) = '1'
 86     and d.serial_fee(+) <> 0
 87     and a.serial_fee = d.serial_fee(+)
 88     and d.account_id(+) = 16905170
 89     and c.account_id = 16905170
 90   group by a.stat_type,
 91            a.item_name,
 92            a.his_item_name,
 93            a.price,
 94            a.his_item_code,
 95            a.item_code,
 96            a.medi_item_type,
 97            a.model,
 98            a.standard,
 99            d.hosp_reason,
100            d.center_resualt,
101            d.center_flag,
102            d.hosp_reason_staff,
103            d.hosp_reason_date,
104            d.hosp_reason_staffid,
105            d.audit_reason_id,
106            d.audit_reason
107   Order By a.stat_type, a.item_name, a.his_item_name
108  ;
....省略...

277 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1363767461

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |     1 |   338 |     7  (43)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |                         |     1 |    36 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | MT_FEE_FIN              |     1 |    36 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | PK_MT_FEE_FIN           |     1 |       |     1   (0)| 00:00:01 |
|*  4 |  COUNT STOPKEY                  |                         |       |       |            |          |
|   5 |   TABLE ACCESS BY INDEX ROWID   | BS_ITEM                 |    12 |   192 |     3   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN             | IDX_ITEM_CODE           |    12 |       |     1   (0)| 00:00:01 |
|*  7 |   COUNT STOPKEY                 |                         |       |       |            |          |
|   8 |    TABLE ACCESS BY INDEX ROWID  | BS_MEDI                 |    12 |   216 |     3   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN            | IDX_MEDI_CODE           |    12 |       |     1   (0)| 00:00:01 |
|  10 |  SORT GROUP BY                  |                         |     1 |   338 |     7  (43)| 00:00:01 |
|* 11 |   HASH JOIN OUTER               |                         |     1 |   338 |     6  (34)| 00:00:01 |
|  12 |    NESTED LOOPS OUTER           |                         |     1 |   283 |     3   (0)| 00:00:01 |
|  13 |     NESTED LOOPS                |                         |     1 |   139 |     2   (0)| 00:00:01 |
|* 14 |      TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ          |     1 |    29 |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN         | PK_PM_ACCOUNT_BIZ       |     1 |       |     1   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS BY INDEX ROWID| MT_FEE_FIN              |     1 |   110 |     1   (0)| 00:00:01 |
|* 17 |       INDEX RANGE SCAN          | PK_MT_FEE_FIN           |     1 |       |     1   (0)| 00:00:01 |
|  18 |     TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT            |     1 |   144 |     1   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN           | PK_PM_FEE_AUDIT         |     1 |       |     1   (0)| 00:00:01 |
|  20 |    VIEW                         |                         |     1 |    55 |     2  (50)| 00:00:01 |
|  21 |     HASH GROUP BY               |                         |     1 |    57 |     2  (50)| 00:00:01 |
|* 22 |      TABLE ACCESS BY INDEX ROWID| MT_PAY_RECORD_FIN       |     1 |    57 |     1   (0)| 00:00:01 |
|* 23 |       INDEX RANGE SCAN          | IDX_MT_PAY_RECORD_FIN_2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AA"."ITEM_CODE"=:B1)
   3 - access("AA"."HOSPITAL_ID"=:B1 AND "AA"."SERIAL_NO"=:B2)
   4 - filter(ROWNUM<2)
   6 - access("BS_ITEM"."ITEM_CODE"=:B1)
   7 - filter(ROWNUM<2)
   9 - access("BS_MEDI"."MEDI_CODE"=:B1)
  11 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID"(+) AND "A"."SERIAL_FEE"="B"."SERIAL_FEE"(+) AND
              "A"."SERIAL_NO"="B"."SERIAL_NO"(+) AND "A"."FEE_BATCH"="B"."FEE_BATCH"(+))
  14 - filter("C"."SERIAL_NO"='25735455' AND "C"."HOSPITAL_ID"='4307210003' AND
              "C"."VALID_FLAG"='1')
  15 - access("C"."ACCOUNT_ID"=16905170)
  16 - filter("A"."VALID_FLAG"='1')
  17 - access("A"."HOSPITAL_ID"='4307210003' AND "A"."SERIAL_NO"='25735455')
  19 - access("D"."ACCOUNT_ID"(+)=16905170 AND "D"."AUDIT_PHASE"(+)='1' AND
              "A"."SERIAL_FEE"="D"."SERIAL_FEE"(+))
       filter(TO_NUMBER("D"."AUDIT_STAFF_ID"(+))=2103 AND "D"."SERIAL_FEE"(+)<>0 AND
              "D"."AUDIT_PHASE"(+)='1' AND "A"."SERIAL_FEE"="D"."SERIAL_FEE"(+))
  22 - filter("SERIAL_FEE"<>0 AND "VALID_FLAG"='1')
  23 - access("B"."HOSPITAL_ID"='4307210003' AND "B"."SERIAL_NO"='25735455')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1247  consistent gets
         12  physical reads
          0  redo size
       9160  bytes sent via SQL*Net to client
       2220  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         227  rows processed

其中逻辑读为1247,物理读12,比起几千万上亿次,提高了上万倍。

后面经检查发现在这套系统中,有大理SQL使用了/*+ rule */ Hint,最好的处理方法是修改SQL代码,这里为了应急我使用了_optimizer_ignore_hints参数让优化器忽略所有的hint。

闪回与归档参数的设置

Oracle闪回技术提供了一组功能用来查看数据的过去状态并且支持数据按时间返回到之前的状态不用对数据库从备份中执行大量的还原操作或者执行按时间点恢复。在大部分情况下闪回功能非常有效破坏性比介质恢复小。

Oracle的大部分闪回功能是在逻辑层面进行操作,查看和维护数据库对象,比如:
Oracle 闪回查询:指定目标时间对数据库运行查询来查看在指定时间点的数据结果。为了从一个不想要生效的改变中对表进行恢复,用户可以选择错误之前的时间并运行查询来检索丢失或改变的记录。

Oracle闪回版本查询:能查看在指定时间间隔内一个或多个表中已经存在记录的所有版本。还可以检索行记录不同版本的元数据,包括开始时间,结束时间,操作和创建这个版本的事务ID。这个功能可以用来恢复丢失的数据和查看表的改变。

Oracle闪回事务查询:能查看由单个事务所产生的改变或者在一个时间周期内所有事务的改变。

Oracle闪回表:将一个表返回到之前某个时间点的状态。当数据库处于联机状态时也能还原表数据。只撤消特定表的改变。

Oracle闪回删除:是dorp table的反操作

闪回表,闪回查询,闪回事务查询和闪回版本查询都依赖于undo数据,记录对Oracle数据库每一个更新的影响。undo的主要作用主要是用于SQL查询的一致性读与事务的回滚,这些undo信息包括了重构过去时间点数据所需要的信息。

闪回删除是建立在回收站机制上的,Oracle会将删除的数据库对象保留到因为要分配新空间而不得不覆盖原来使用的空间为止。

注意:逻辑层面的闪回功能不依赖于RMAN。

在物理层面,Oracle闪回数据库提供了一种对数据库执行按时间点恢复的替代方法。如果数据文件已经包含了所不期待的改变,那么使用闪回数据库能使用当前的数据文件将它的内容还原到过去时间点所处的状态。而结果就是按时间点恢复一样,但其操作速度更快因为它不需要从备份中还原数据文件,比介质恢复应用的重做要少很多。

闪回数据库使用闪回日志来访问数据块的之前版本,它的一些信息与归档重做日志一样。闪回数据库要求你对数据库设置了闪回区,因为闪回日志只能存储在这里。闪回日志缺省情况下是没启用的。闪回日志所使用的空间由数据库自动管理,在闪回区会让空间使用保持平衡。

注意:闪回数据库被集成到RMAN中,在执行闪回数据库时可以自动从备份中检索的任何归档重做日志,它也能使用SQL*Plus来执行,但在这种情况下你必须保证它所要应用的归档日志都在磁盘上。

如果闪回区没有分配足够的空间,那么闪回日志因为备份和归档日志文件可能会被删除。数据库按时间点恢复可以达到与闪回数据库同样的效果,将数据库的内容返回到过去的时间点。

Oracle数据库有联机重做日志,这个日志是记录对数据库所做的修改,比如插入,删除,更新数据等,对这些操作都会记录在联机重做日志里。一般数据库至少要有2个联机重做日志组。当一个联机重做日志组被写满的时候,就会发生日志切换,这时联机重做日志组2成为当前使用的日志,当联机重做日志组2写满的时候,又会发生日志切换,去写联机重做日志组1,就这样反复进行。

如果数据库处于非归档模式,联机日志在切换时就会丢弃. 而在归档模式下,当发生日志切换的时候,被切换的日志会进行归档。比如,当前在使用联机重做日志1,当1写满的时候,发生日志切换,开始写联机重做日志2,这时联机重做日志1的内容会被拷贝到另外一个指定的目录下。这个目录叫做归档目录,拷贝的文件叫归档重做日志。

数据库使用归档方式运行时才可以进行灾难性恢复。
1.归档日志模式和非归档日志模式的区别
非归档模式只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.
归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.
用ARCHIVE LOG LIST 可以查看当前模式状态是归档模式还是非归档模式.

当前数据库没有启动归档

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     191
Current log sequence           193

从上面的信息可以知道归档目标为USE_DB_RECOVERY_FILE_DEST,这说归档日志将会存储到闪回区这里之所以归档目录为是因为当没有设置其它归档目录时log_archive_dest_10会隐式的使用USE_DB_RECOVERY_FILE_DEST来存储归档重做日志

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

从上面的信息可以看到当前数据库没有设置任何归档目录,下面来设置归档目录

SQL> alter system set log_archive_dest_1='location=/u02' scope=both;

System altered.

再来查看归档重做日志存储的目录

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02
Oldest online log sequence     191
Current log sequence           193

可以看到现在数据库显示的归档日志目录为我们所指定的/u02

下面来启用闪回

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02
Oldest online log sequence     191
Current log sequence           193

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G

SQL> alter database force logging;

Database altered.


SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

现在虽然我们设置了db_recovery_file_dest与db_recovery_file_dest_size,强制数据库记录日志,但数据库没有启用归档是不能雇用闪回的,下面启用归档。

SQL> alter database archivelog;

Database altered.

再次启用闪回

SQL> alter database flashback on;

Database altered.

当启用闪回就不能使用log_archive_dest与log_archive_duplex_dest参数来指定归档日志目录。而是必须使用log_archive_dest_n

SQL> alter system set log_archive_dest='location=/u01' scope=both;
alter system set log_archive_dest='location=/u01' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST


SQL> alter system set log_archive_duplex_dest='location=/u01' scope=both;
alter system set log_archive_duplex_dest='location=/u01' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DUPLEX_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

从上面的错误信息确实可以看到当启用闪回后确实不能使用log_archive_dest与log_archive_duplex_dest参数来指定归档日志目录

查询缓冲区缓存忙等待

set pages 9999;
column buffer_busy_wait format 999,999,999;
column mydate heading ‘yr. mo dy Hr.’

select
to_char(snap_time,’yyyy-mm-dd HH24′) mydate,
new.name,
new.buffer_busy_wait-old.buffer_busy_wait buffer_busy_wait
from
perfstat.stats$buffer_pool_statistics old,
perfstat.stats$buffer_pool_statistics new,
perfstat.stats$snapshot sn
where
new.name=old.name
and new.snap_id=sn.snap_id
and old.snap_id=sn.snap_id-1
and new.buffer_busy_wait-old.buffer_busy_wait>1
group by to_char(snap_time,’yyyy-mm-dd HH24′),
new.name,
new.buffer_busy_wait-old.buffer_busy_wait;

dblink ora-00600 ora-02072 ora-02063

某汽车集团数据库环境为linux5.8,oracle 11g rac 11.2.0.4。dblink所连接的数据库环境未知,业务系统在通过dblink进行数据更新时在出现如下错误:

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-02072: distributed database network protocol mismatch], [], [], [], [], [], [], [], [], [], [========= Dump for incident 264681 (ORA 600 [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-]) ========

检查相关的错误信息跟踪文件tsp1_ora_23612_i264681.trc有如下信息:

Dump continued from file: /u01/app/oracle/diag/rdbms/tsp/tsp1/trace/tsp1_ora_23612.trcORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-02072: distributed database network protocol mismatch], [], [], [], [], [], [], [], [], [], [========= Dump for incident 264681 (ORA 600 [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-]) ========

*** 2015-01-18 17:55:15.413dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)----- Current SQL Statement for this session (sql_id=43ws7jmatt2yf) -----
MERGE INTO xxx@DBLINK1 R USING (SELECT DISTINCT SUBSTR(A.VIN,LENGTH(VIN)-7,LENGTH(A.VIN)) AS VIN,A.OFFSET_LON,A.OFFSET_LAT,A.UTC,A.ADDRESS,A.MILEAGE, B.ORDERNO,C.STATUSNAME,C.STATUSFLAG FROM aaa@dblink2 A,b B,c C,d@dblink3 D WHERE LENGTH(VIN)>7 AND SUBSTR(A.VIN,LENGTH(VIN)-7,8)= B.CHASSISNUMBER AND B.CARSTATUS = C.STATUSFLAG AND A.VIN =D.LICENSEPLATE AND D.ISLOGOUT = 0 AND SUBSTR(B.ORDERNO,0,2) IN ('31','37','40') AND C.STATUSFLAG < 6 AND A.ADDRESS IS NOT NULL) D ON (R.ROW_ID='ssi'||D.VIN) WHEN MATCHED THEN UPDATE SET LONGITUDE=D.OFFSET_LON,DIMENSION=D.OFFSET_LAT,GPS_TIME = D.UTC,GPS_LOCATION=D.ADDRESS,GPS_LOCATION_SPLIT=D.ADDRESS,STATUS=D.STATUSNAME,GPS_MILEAGE=D.MILEAGE,GAUGE_MILEAGE='',CREATED_BY = 'ssi',LAST_UPD_BY='ssi',ORDER_NO=D.ORDERNO WHEN NOT MATCHED THEN INSERT (ROW_ID,CHASSIS_NO,LONGITUDE,DIMENSION,GPS_TIME,GPS_LOCATION,GPS_LOCATION_SPLIT,STATUS,GPS_MILEAGE,GAUGE_MILEAGE,CREATED_BY,LAST_UPD_BY,ORDER_NO) VALUES('ssi'||D.VIN,D.VIN,D.OFFSET_LON,D.OFFSET_LAT,D.UTC,D.ADDRESS,D.ADDRESS,D.STATUSNAME,D.MILEAGE,'','ssi','ssi',D.ORDERNO)----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name0x2d3112bc0        33  package body DFCP.DFVL_DBLINK10x2d92769c8         1  anonymous block

----- Call Stack Trace -----
calling              call     entry                argument values in hexlocation             type     point                (? means dubious value)-------------------- -------- -------------------- ----------------------------
skdstdst()+41        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
ksedst1()+103        call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
ksedst()+39          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
dbkedDefDump()+2746  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
ksedmp()+41          call     dbkedDefDump()       000000003 ? 000000002 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
ksfdmp()+69          call     ksedmp()             000000003 ? 000000002 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
dbgexPhaseII()+1764  call     ksfdmp()             000000003 ? 000000002 ?
                                                   7FFFD889F8E0 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
dbgexProcessError()  call     dbgexPhaseII()       2B585AE35730 ? 2B585AE3E7F0 ?
+2680                                              7FFFD88A8F38 ? 7FFFD889F9B8 ?
                                                   7FFFD88A4460 ? 000000002 ?
dbgeExecuteForError  call     dbgexProcessError()  2B585AE35730 ? 2B585AE3E7F0 ?
()+88                                              000000001 ? 000000000 ?
                                                   7FFFD88A4460 ? 000000002 ?
dbgePostErrorKGE()+  call     dbgeExecuteForError  2B585AE35730 ? 2B585AE3E7F0 ?
2136                          ()                   000000001 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   00C0CC9E0 ? 2B585B260040 ?
71                                                 000000258 ? 000000001 ?
                                                   000000000 ? 000000002 ?
kgesev()+280         call     dbkePostKGE_kgsf()   00C0CC9E0 ? 2B585B260040 ?
                                                   000000258 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksesec1()+170        call     kgesev()             00C0CC9E0 ? 2B585B260040 ?
                                                   000000258 ? 000000001 ?
                                                   7FFFD88A9AE0 ? 000000002 ?
npierr()+878         call     ksesec1()            00C0CC9E0 ? 000000001 ?
                                                   0000000AC ? 2B585B49DDE0 ?
                                                   7FFFD88A9040 ? 00000000A ?
kpnerr()+240         call     npierr()             2B585B49C570 ? 000000258 ?
                                                   000000000 ? 00000005E ?
                                                   7FFFD88A9040 ? 2B585B49EE90 ?
kpnpst()+276         call     kpnerr()             2B585B49C570 ? 000000258 ?
                                                   000000000 ? 00000005E ?
                                                   7FFFD88ACA68 ? 2B585B49EE90 ?
upirtrc()+1919       call     kpnpst()             7FFFD88ACBE0 ? 000000258 ?
                                                   000000023 ? 000000000 ?
                                                   7FFFD88ACA68 ? 2B585B49EE90 ?
kpurcsc()+98         call     upirtrc()            7FFFD88ACBE0 ? 000000258 ?
                                                   000000023 ? 000000000 ?
                                                   7FFFD88ACA68 ? 2B585B4F4B10 ?
kpuexec()+10790      call     kpurcsc()            7FFFD88ACBE0 ? 000000258 ?
                                                   000000023 ? 000000000 ?
                                                   7FFFD88ACA68 ? 2B585B4F4B10 ?
kpnexe()+1517        call     kpuexec()            7FFFD88ACBE0 ? 000000258 ?
                                                   7FFFD88ACA60 ? 000000000 ?
                                                   7FFFD88ACA68 ? 7FFFD88B07B8 ?
opiexe()+32658       call     kpnexe()             2B585B2AF000 ? 2B585B4F4B10 ?
                                                   2B585B507EB8 ? 2B585B19A950 ?
                                                   000000001 ? 1000000000 ?
opipls()+2164        call     opiexe()             000000004 ? 000000005 ?
                                                   2B585B507EB8 ? 2B585B19A950 ?
                                                   000000001 ? 1000000000 ?
opiodr()+917         call     opipls()             000000066 ? 000000005 ?
                                                   2B585B507EB8 ? 2B585B19A950 ?
                                                   000000001 ? 1000000000 ?
rpidrus()+211        call     opiodr()             000000066 ? 000000007 ?
                                                   7FFFD88B3960 ? 2B585B19A950 ?
                                                   000000001 ? 1000000000 ?
skgmstack()+148      call     rpidrus()            7FFFD88B3338 ? 000000007 ?
                                                   7FFFD88B3960 ? 2B585B19A950 ?
                                                   000000001 ? 1000000000 ?
rpiswu2()+690        call     skgmstack()          7FFFD88B3310 ? 00C0CC600 ?
                                                   00000F618 ? 009698A9C ?
                                                   7FFFD88B3338 ? 1000000000 ?
rpidrv()+1327        call     rpiswu2()            2C511BF60 ? 00000006D ?
                                                   2C511BFE4 ? 000000002 ?
                                                   7FFFD88B3338 ? 1000000000 ?
psddr0()+473         call     rpidrv()             00000000B ? 000000066 ?
                                                   7FFFD88B3960 ? 000000038 ?
                                                   7FFFD88B3338 ? 1000000000 ?
psdnal()+457         call     psddr0()             00000000B ? 000000066 ?
                                                   7FFFD88B3960 ? 000000030 ?
                                                   7FFFD88B3338 ? 1000000000 ?
pevm_EXECC()+314     call     psdnal()             7FFFD88B50E0 ? 7FFFD88B52F0 ?
                                                   7FFFD88B3960 ? 2B585B2A2AE8 ?
                                                   22EC69508 ? 1000000000 ?
pfrinstr_EXECC()+80  call     pevm_EXECC()         2B585B1D5CA0 ? 2B585B409640 ?
                                                   000000020 ? 2B585B2A2AE8 ?
                                                   22EC69508 ? 1000000000 ?
pfrrun_no_tool()+63  call     pfrinstr_EXECC()     2B585B2A7258 ? 21D5E8DB4 ?
                                                   2B585B2A72C8 ? 2B585B2A2AE8 ?
                                                   22EC69508 ? 2B5800000020 ?
pfrrun()+627         call     pfrrun_no_tool()     2B585B2A7258 ? 21D5E8DB4 ?
                                                   2B585B2A72C8 ? 2B585B2A2AE8 ?
                                                   22EC69508 ? 2B5800000020 ?
plsql_run()+649      call     pfrrun()             2B585B2A7258 ? 21D5E8DB4 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 2B5800000020 ?
peicnt()+302         call     plsql_run()          2B585B2A7258 ? 21D5E8DB4 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 2B5800000020 ?
kkxexe()+525         call     peicnt()             7FFFD88B50E0 ? 2B585B2A7258 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 000000000 ?
opiexe()+18001       call     kkxexe()             2B585B2ABF80 ? 2B585B2A7258 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 000000000 ?
kpoal8()+2118        call     opiexe()             000000049 ? 000000003 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 000000000 ?
opiodr()+917         call     kpoal8()             00000005E ? 000000003 ?
                                                   2B585B2A72C8 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 000000000 ?
ttcpip()+2183        call     opiodr()             00000005E ? 00000001C ?
                                                   7FFFD88B9910 ? 7FFFD88B50E0 ?
                                                   22EC69508 ? 000000000 ?
opitsk()+1710        call     ttcpip()             00C0EA630 ? 00999BA90 ?
                                                   7FFFD88B9910 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
opiino()+969         call     opitsk()             00C0EA638 ? 000000000 ?
                                                   7FFFD88B9910 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
opiodr()+917         call     opiino()             00000003C ? 000000004 ?
                                                   7FFFD88BB108 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
opidrv()+570         call     opiodr()             00000003C ? 000000004 ?
                                                   7FFFD88BB108 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
sou2o()+103          call     opidrv()             00000003C ? 000000004 ?
                                                   7FFFD88BB108 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
opimai_real()+133    call     sou2o()              7FFFD88BB0E0 ? 00000003C ?
                                                   000000004 ? 7FFFD88BB108 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
ssthrdmain()+265     call     opimai_real()        000000002 ? 7FFFD88BB2D0 ?
                                                   000000004 ? 7FFFD88BB108 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
main()+201           call     ssthrdmain()         000000002 ? 7FFFD88BB2D0 ?
                                                   000000001 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
__libc_start_main()  call     main()               000000002 ? 7FFFD88BB478 ?
+244                                               000000001 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?
_start()+41          call     __libc_start_main()  000A29108 ? 000000002 ?
                                                   7FFFD88BB468 ? 000000000 ?
                                                   7FFFD88B9368 ? 7FFFD88B990C ?


--------------------- Binary Stack Dump ---------------------

在执行如下语句时报错:

merge into xxx@dblink1 r

using (select distinct substr(a.vin, length(vin) - 7, length(a.vin)) as vin,

                       a.offset_lon,

                       a.offset_lat,

                       a.utc,

                       a.address,

                       a.mileage,

                       b.orderno,

                       c.statusname,

                       c.statusflag

         from aaaa@dblink2 a,

              b                 b,

              c          c,

              dddd@dblink3    d

        where length(vin) > 7

          and substr(a.vin, length(vin) - 7, 8) = b.chassisnumber

          and b.carstatus = c.statusflag

          and a.vin = d.licenseplate

          and d.islogout = 0

          and substr(b.orderno, 0, 2) in ('31', '37', '40')

          and c.statusflag < 6

          and a.address is not null) d

on (r.row_id = 'ssi' || d.VIN)

when MATCHED then

  update

     set LONGITUDE          = d.offset_lon,

         DIMENSION          = d.offset_lat,

         gps_time           = d.utc,

         GPS_LOCATION       = d.address,

         GPS_LOCATION_SPLIT = d.address,

         STATUS             = d.statusname,

         GPS_MILEAGE        = d.mileage,

         GAUGE_MILEAGE      = '',

         created_by         = 'ssi',

         last_upd_by        = 'ssi',

         order_no           = d.orderno

when NOT MATCHED then

  insert

    (row_id,

     chassis_no,

     LONGITUDE,

     DIMENSION,

     GPS_TIME,

     GPS_LOCATION,

     GPS_LOCATION_SPLIT,

     STATUS,

     GPS_MILEAGE,

     GAUGE_MILEAGE,

     created_by,

     last_upd_by,

     order_no)

  VALUES

    ('ssi' || d.vin,

     d.vin,

     d.offset_lon,

     d.offset_lat,

     d.utc,

     d.address,

     d.address,

     d.statusname,

     d.mileage,

     '',

     'ssi',

     'ssi',

     d.orderno)

后面经询问了解到通过dblink所连接的远端数据库版本要低,而生产数据库升级前的版本为11.2.0.1,通过dblink进行数据更新是没有问题。这种通过dblink,且数据库版本不同的情况下经常遇到这种问题。在这种情况下,一般的处理方法就是通过dblink执行最简单的select,insert,update,delete语句看是否报错,如果没有出现故障,我们就可以将使用merge语法的SQL修改成最简单的select,insert,update,delete语句来达到相同的目的。也就像有多条路到达一个目的地,其中一条路因为某些原因不能通行,就改走另一路。

通过脚本来查看alert.log

create or replace directory data_dir as ‘/oracle/diag/rdbms/develop/develop/trace’;

CREATE TABLE alert_log
(
text_line varchar2(2000)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
records delimited by newline
fields
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
‘alert_develop.log’
)
)
REJECT LIMIT unlimited;

select to_char(last_time,’dd-mon-yyyy hh24:mi’) shutdown,
to_char(start_time,’dd-mon-yyyy hh24:mi’) startup,
round((start_time-last_time)*24*60,2) mins_down,
round((last_time-lag(start_time) over (order by r)),2) days_up,
case when (lead(r) over (order by r) is null )
then round((sysdate-start_time),2)
end days_still_up
from (
select r,
to_date(last_time, ‘Dy Mon DD HH24:MI:SS YYYY’) last_time,
to_date(start_time,’Dy Mon DD HH24:MI:SS YYYY’) start_time
from (
select r,
text_line,
lag(text_line,1) over (order by r) start_time,
lag(text_line,2) over (order by r) last_time
from (
select rownum r, text_line
from alert_log
where text_line like ‘___ ___ __ __:__:__ 20__’
or text_line like ‘Starting ORACLE instance %’
)
)
where text_line like ‘Starting ORACLE instance %’
and start_time not like ‘Starting ORACLE instance %’ and last_time not like ‘Starting ORACLE instance %’

)

select to_char(‘Fri Mar 09 11:20:21 2012′,’dd-mon-yyyy hh24:mi’) from dual

创建合理的索引避免order by操作提升SQL性能的案例一

某医保系统的查询一年内一个中心所有医疗费登账记录的汇总情况执行3000多秒才能出结果,下面TOP SQL中执行时间最长的SQL语句它的功能是查询一年内一个中心所有医疗费登账记录的汇总情况,执行时间3286秒,则且这条SQL就是我们要找的SQL语句。

Elapsed Time (s)      CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	 SQL Id          SQL Module	        SQL Text
3,286	              2,015	        1	        3,286		        18.59	         8aw9tm6w83usm	 JDBC Thin Client	select t1.account_id, ...
622	              316	        17	        36.60	                3.52	         ggrctzgtcg14s	 JDBC Thin Client	select t.pay_type as pay_...
222	              4	                1	        221.64	                1.25	         fvy1hnauapb8s	 JDBC Thin Client	select (select center_name fro...
204	              5	                1	        203.60	                1.15	         fcm5wrxny5b2u	 JDBC Thin Client	select count(1) cs from bs_ho...
198	              125	        31	        6.38	                1.12	         cfuhdxk446uzv	 JDBC Thin Client	select * from ( select a.hosp...
177	              20	        1	        177.00	                1.00	         42zd8jamnw4b4	 PL/SQL Developer	begin dbms_sqltune.execute_tu...
158	              88	        1	        158.21	                0.89	         17t35rynaz40v	 JDBC Thin Client	select u.center_name, x.hospit...
142	              89	        97	        1.46	                0.80	         a7dkwg8uhrwkj	 JDBC Thin Client	select * from ( select a.hosp...
135	              48	        1,417	        0.10	                0.76	         aay99a855zv3u	 JDBC Thin Client	SELECT distinct c.NAME, ...
116	              73	        303	        0.38	                0.66	         31tf0tfn8nv6f	 JDBC Thin Client	select a.pay_batch_no, a.pay_b...

其SQL如下所示:

select  t1.account_id,
       t1.hospital_id,
       (select hosp_level
          from bs_hospital a
         where t1.hospital_id = a.hospital_id
           and a.valid_flag = '1') as hosp_level,
       t1.serial_no,
       t1.icd,
       (select t4.disease
          from bs_disease t4
         where t1.icd = t4.icd(+)
           and t4.center_id = nvl(center.catalog_center, center.center_id)) as disease,
       t1.biz_type,
       t1.treatment_type,
       t1.disease_type,
       t1.reimburse_flag,
       t1.corp_id,
       t1.corp_type_code,
       t2.corp_name,
       t1.indi_id,
       t1.pers_type,
       t2.center_id,
       t2.district_code,
       decode(t2.center_id, t2.district_code, 0, 1) as out_type,
       t1.biz_flag,
       t1.biz_flag_old,
       t2.name,
       t2.idcard,
       t2.sex,
       t2.begin_date,
       t2.end_date,
       t1.fin_date,
       t2.reg_date,
       t1.disease_fee,
       t1.violate_flag,
       t1.patient_id,
       t1.in_days,
       decode(t1.allow_treat, '0', 0, 1) As allow_treat,
       Decode(T1.Treatment_Type_Last,
              '',
              T1.Treatment_Type,
              Decode(T1.Treatment_Type,
                     '120',
                     '120',
                     Decode(T1.Biz_Type,
                            '41',
                            '扣减已记帐金额',
                            '42',
                            '扣减已记帐金额',
                            T1.Treatment_Type_Last))) As Treatment_Type_Last,
       t1.violate,
       t1.pay_money,
       t1.pay_fund,
       t1.audit_money,
       '0' as finish_flag,
       t1.MONTH_DECL_SN,
       t1.deal_flag,
       t1.audit_deal_flag,
       t1.declare_icd_guide,
       t1.declare_audit_fee,
       t1.declare_audit_fee_cl,
       t1.first_icd_guide,
       t1.first_audit_fee,
       t1.first_audit_fee_cl,
       t1.second_icd_guide,
       t1.second_audit_fee,
       t1.second_audit_fee_cl,
       t1.PAY_otherfee,
       t1.PAY_FUND003,
       t1.PAY_FUND001,
       t1.PAY_FUND201,
       t1.PAY_FUND301,
       t1.PAY_FUND999,
       t1.PAY_FUND996,
       t1.PAY_begin
  from (select a.account_id,
               a.hospital_id,
               a.serial_no,
               a.center_id,
               a.icd,
               a.biz_type,
               a.treatment_type,
               a.reimburse_flag,
               a.corp_id,
               a.corp_type_code,
               a.indi_id,
               a.pers_type,
               a.biz_flag,
               a.biz_flag_old,
               a.fin_date,
               a.violate_flag,
               (select mbf.disease_type
                  from mt_biz_fin mbf
                 where mbf.valid_flag = '1'
                   and mbf.hospital_id = a.hospital_id
                   and mbf.serial_no = a.serial_no) disease_type,
               (Select aa1.allow_treat
                  From pm_account_scene aa1
                 where aa1.hospital_id = a.hospital_id
                   and aa1.serial_no = a.serial_no
                   and aa1.valid_flag = '1') as allow_treat,
               (select distinct bb1.patient_id
                  from mt_biz_fin bb1
                 where bb1.hospital_id = a.hospital_id
                   and bb1.serial_no = a.serial_no
                   and bb1.valid_flag = '1') as patient_id,
               (select distinct bb1.in_days
                  from mt_biz_fin bb1
                 where bb1.hospital_id = a.hospital_id
                   and bb1.serial_no = a.serial_no
                   and bb1.valid_flag = '1') as in_days,
               (select Max(audit_treat_value)
                  from pm_treat_audit aa
                 where aa.account_id = a.account_id
                   and aa.audit_staff_id = '2208'
                   and aa.audit_phase = '1'
                   and aa.valid_flag = '1') as treatment_type_last,
               a.violate,
               nvl((SELECT sum(pm_account_fund.pay_money)
                     FROM pm_account_fund
                    WHERE a.account_id = pm_account_fund.account_id
                      AND pm_account_fund.valid_flag = '1'),
                   0) as pay_money,
               nvl((SELECT sum(pm_account_fund.pay_money)
                     FROM pm_account_fund
                    WHERE a.account_id = pm_account_fund.account_id
                      AND pm_account_fund.valid_flag = '1'
                      AND pm_account_fund.fund_id not in
                          ('003', '999', '996', '997')),
                   0) as pay_fund,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '003'),
                   0) AS PAY_FUND003,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
                   0) AS PAY_FUND001,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '201'),
                   0) AS PAY_FUND201,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '301'),
                   0) AS PAY_FUND301,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '999'),
                   0) AS PAY_FUND999,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '996'),
                   0) AS PAY_FUND996,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID not in
                          ('801', '001', '003', '201', '999')),
                   0) AS PAY_otherfee,
               NVL((SELECT SUM(mprf.real_pay)
                     FROM mt_pay_record_fin mprf
                    WHERE A.hospital_id = mprf.hospital_id
                      AND A.serial_no = mprf.serial_no
                      AND mprf.policy_item_code = 'S01'),
                   0) AS PAY_begin,
               (select nvl(sum(b.AUDIT_MONEY), 0)
                  from pm_fee_audit b
                 where a.account_id = b.account_id
                   and b.audit_staff_id = '2208'
                   and b.AUDIT_TYPE = '1'
                   and b.AUDIT_PHASE = '1'
                   and b.valid_flag = '1') as audit_money,
               a.MONTH_DECL_SN,
               a.deal_flag,
               decode(nvl(a.disease_fee, 0),
                      0,
                      nvl((select nvl(max(ration), 0)
                            from pm_ration pr
                           where pr.center_id = a.center_id
                             and pr.hospital_id = a.hospital_id
                             and pr.treatment_type = a.treatment_type
                             and pr.year = to_char(a.fin_date, 'yyyy')
                             and (decode(a.pers_type,
                                         '21',
                                         '21',
                                         '22',
                                         '21',
                                         '23',
                                         '21',
                                         '24',
                                         '21',
                                         '41',
                                         '21',
                                         '3',
                                         '12',
                                         '4',
                                         '12',
                                         '5',
                                         '12',
                                         '7',
                                         '12',
                                         '2') = pr.insr_no or
                                 pr.insr_no = '0')
                             and rownum < 2
                             and pr.biz_flag = a.biz_flag),
                          0),
                      a.disease_fee) as disease_fee,
               a.audit_flag as audit_deal_flag,
               a.declare_icd_guide as declare_icd_guide,
               a.declare_audit_fee as declare_audit_fee,
               a.declare_audit_fee_cl as declare_audit_fee_cl,
               a.first_icd_guide as first_icd_guide,
               a.first_audit_fee as first_audit_fee,
               a.first_audit_fee_cl as first_audit_fee_cl,
               decode(a.biz_type,
                      '12',
                      decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
                      a.second_icd_guide) second_icd_guide,
               a.second_audit_fee as second_audit_fee,
               a.second_audit_fee_cl as second_audit_fee_cl
          from pm_account_biz a
         where 1 = 1
           and ('0' = 'A' or a.reimburse_flag ='0')
           and ('00' = '00' or a.biz_type ='00')
           and a.center_id in ('430722')
           and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
               to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
           and a.valid_flag = '1'
           and a.deal_flag in ('0', '1', '2')
           and (nvl(a.staff1_finish_flag, '0') ='0' and
               nvl(a.staff2_finish_flag, '0') = '0' and
               nvl(a.staff3_finish_flag, '0') = '0')
         group by a.account_id,
                  a.hospital_id,
                  a.serial_no,
                  a.center_id,
                  a.icd,
                  a.biz_type,
                  a.treatment_type,
                  a.reimburse_flag,
                  a.corp_id,
                  a.corp_type_code,
                  a.indi_id,
                  a.pers_type,
                  a.biz_flag,
                  a.biz_flag_old,
                  a.disease_fee,
                  a.fin_date,
                  a.violate_flag,
                  a.violate,
                  a.MONTH_DECL_SN,
                  a.deal_flag,
                  a.audit_flag,
                  a.declare_icd_guide,
                  a.declare_audit_fee,
                  a.declare_audit_fee_cl,
                  a.first_icd_guide,
                  a.first_audit_fee,
                  a.first_audit_fee_cl,
                  a.second_icd_guide,
                  a.second_audit_fee,
                  a.second_audit_fee_cl) t1,
       mt_biz_fin t2,
       bs_center center
 where t1.serial_no = t2.serial_no
   AND t1.hospital_id = t2.hospital_id
   AND t2.valid_flag = '1'
   and t1.center_id = center.center_id(+)
 order by t1.hospital_id, t1.serial_no

上面SQL的执行计划如下:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Order | Operation                         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------
|   0 |    67 | SELECT STATEMENT                  |                                |       |       | 56871 (100)|          |
|   1 |     2 |  TABLE ACCESS BY INDEX ROWID      | BS_HOSPITAL                    |     1 |    16 |     1   (0)| 00:00:01 |
|   2 |     1 |   INDEX UNIQUE SCAN               | PK_BS_HOSPITAL                 |     1 |       |     1   (0)| 00:00:01 |
|   3 |     4 |  TABLE ACCESS BY INDEX ROWID      | BS_DISEASE                     |     1 |    33 |     1   (0)| 00:00:01 |
|   4 |     3 |   INDEX RANGE SCAN                | INX_BS_DISEASE_01              |     1 |       |     1   (0)| 00:00:01 |
|   5 |     6 |  TABLE ACCESS BY INDEX ROWID      | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|   6 |     5 |   INDEX RANGE SCAN                | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|   7 |    10 |  SORT AGGREGATE                   |                                |     1 |    33 |            |          |
|   8 |     9 |   COUNT STOPKEY                   |                                |       |       |            |          |
|   9 |     8 |    TABLE ACCESS BY INDEX ROWID    | PM_RATION                      |     1 |    33 |     1   (0)| 00:00:01 |
|  10 |     7 |     INDEX RANGE SCAN              | IDX_PM_RATION_1                |     1 |       |     1   (0)| 00:00:01 |
|  11 |    13 |  HASH UNIQUE                      |                                |     1 |    26 |     2  (50)| 00:00:01 |
|  12 |    12 |   TABLE ACCESS BY INDEX ROWID     | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|  13 |    11 |    INDEX RANGE SCAN               | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|  14 |    16 |  HASH UNIQUE                      |                                |     1 |    26 |     2  (50)| 00:00:01 |
|  15 |    15 |   TABLE ACCESS BY INDEX ROWID     | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|  16 |    14 |    INDEX RANGE SCAN               | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|  17 |    18 |  TABLE ACCESS BY INDEX ROWID      | PM_ACCOUNT_SCENE               |     1 |    28 |     1   (0)| 00:00:01 |
|  18 |    17 |   INDEX RANGE SCAN                | IDX_PM_ACCOUNT_SCENE_1         |     1 |       |     1   (0)| 00:00:01 |
|  19 |    21 |  SORT AGGREGATE                   |                                |     1 |    37 |            |          |
|  20 |    20 |   TABLE ACCESS BY INDEX ROWID     | PM_TREAT_AUDIT                 |     1 |    37 |     1   (0)| 00:00:01 |
|  21 |    19 |    INDEX RANGE SCAN               | PK_PM_TREAT_AUDIT              |     1 |       |     1   (0)| 00:00:01 |
|  22 |    24 |  SORT AGGREGATE                   |                                |     1 |    12 |            |          |
|  23 |    23 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    12 |     1   (0)| 00:00:01 |
|  24 |    22 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  25 |    27 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  26 |    26 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  27 |    25 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  28 |    30 |  SORT AGGREGATE                   |                                |     1 |    21 |            |          |
|  29 |    29 |   TABLE ACCESS BY INDEX ROWID     | PM_FEE_AUDIT                   |     1 |    21 |     1   (0)| 00:00:01 |
|  30 |    28 |    INDEX RANGE SCAN               | PK_PM_FEE_AUDIT                |     1 |       |     1   (0)| 00:00:01 |
|  31 |    33 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  32 |    32 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  33 |    31 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  34 |    36 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  35 |    35 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  36 |    34 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  37 |    40 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  38 |    39 |   INLIST ITERATOR                 |                                |       |       |            |          |
|  39 |    38 |    TABLE ACCESS BY INDEX ROWID    | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  40 |    37 |     INDEX RANGE SCAN              | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  41 |    43 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  42 |    42 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  43 |    41 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  44 |    46 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  45 |    45 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  46 |    44 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  47 |    49 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  48 |    48 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  49 |    47 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  50 |    52 |  SORT AGGREGATE                   |                                |     1 |    16 |            |          |
|  51 |    51 |   TABLE ACCESS BY INDEX ROWID     | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|  52 |    50 |    INDEX RANGE SCAN               | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  53 |    55 |  SORT AGGREGATE                   |                                |     1 |    39 |            |          |
|  54 |    54 |   TABLE ACCESS BY INDEX ROWID     | MT_PAY_RECORD_FIN              |     1 |    39 |     1   (0)| 00:00:01 |
|  55 |    53 |    INDEX RANGE SCAN               | IDX_MT_PAY_RECORD_FIN_2        |     1 |       |     1   (0)| 00:00:01 |
|  56 |    66 |  FILTER                           |                                |       |       |            |          |
|  57 |    65 |   SORT GROUP BY                   |                                |     1 |   309 | 56871   (1)| 00:11:23 |
|  58 |    64 |    TABLE ACCESS BY INDEX ROWID    | MT_BIZ_FIN                     |     1 |   119 |     1   (0)| 00:00:01 |
|  59 |    63 |     NESTED LOOPS                  |                                |     1 |   309 | 56869   (1)| 00:11:23 |
|  60 |    61 |      MERGE JOIN OUTER             |                                |     1 |   190 | 56868   (1)| 00:11:23 |
|  61 |    57 |       TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_BIZ                 |     1 |   164 | 56867   (1)| 00:11:23 |
|  62 |    56 |        INDEX RANGE SCAN           | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE |   285K|       |   209   (1)| 00:00:03 |
|  63 |    60 |       BUFFER SORT                 |                                |     1 |    26 |     1   (0)| 00:00:01 |
|  64 |    59 |        TABLE ACCESS BY INDEX ROWID| BS_CENTER                      |     1 |    26 |     1   (0)| 00:00:01 |
|  65 |    58 |         INDEX UNIQUE SCAN         | PK_BS_CENTER                   |     1 |       |     1   (0)| 00:00:01 |
|  66 |    62 |      INDEX RANGE SCAN             | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------

从其执行计划中可以看到最消耗成本的操作就是对IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引执行索引范围扫描返回285K条记录再回表查询记录的成本是56867,而整个SQL语句的成本是56871。再与BS_CENTER表执行排序合并连接,再与MT_BIZ_FIN执行嵌套循环连接,再执行group by操作。因为表BS_CENTER表只有10几条记录,且表中的center_id是主键,表PM_ACCOUNT_BIZ中center_id与其它列存在复合索引,理想的执行计划应该是先访问表BS_CENTER再与PM_ACCOUNT_BIZ表执行嵌套循环连接,再与MT_BIZ_FIN连接。而且上述SQL中的group by子句是可以去掉了,这里group by真正的作用只起到了去掉重复记录的作用,而PM_ACCOUNT_BIZ表的主键是account_id,在查询列中,所以可以去掉这个group by 子句。

上面的SQL语句简化如下形式:

select ...省略
from pm_account_biz a
         where 1 = 1
           and ('0' = 'A' or a.reimburse_flag ='0')
           and ('00' = '00' or a.biz_type ='00')
           and a.center_id in ('430722')
           and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
               to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
           and a.valid_flag = '1'
           and a.deal_flag in ('0', '1', '2')
           and (nvl(a.staff1_finish_flag, '0') ='0' and
               nvl(a.staff2_finish_flag, '0') = '0' and
               nvl(a.staff3_finish_flag, '0') = '0')
         group by a.account_id,
                  a.hospital_id,
                  a.serial_no,
                  a.center_id,
                  a.icd,
                  a.biz_type,
                  a.treatment_type,
                  a.reimburse_flag,
                  a.corp_id,
                  a.corp_type_code,
                  a.indi_id,
                  a.pers_type,
                  a.biz_flag,
                  a.biz_flag_old,
                  a.disease_fee,
                  a.fin_date,
                  a.violate_flag,
                  a.violate,
                  a.MONTH_DECL_SN,
                  a.deal_flag,
                  a.audit_flag,
                  a.declare_icd_guide,
                  a.declare_audit_fee,
                  a.declare_audit_fee_cl,
                  a.first_icd_guide,
                  a.first_audit_fee,
                  a.first_audit_fee_cl,
                  a.second_icd_guide,
                  a.second_audit_fee,
                  a.second_audit_fee_cl) t1,
       mt_biz_fin t2,
       bs_center center
 where t1.serial_no = t2.serial_no
   AND t1.hospital_id = t2.hospital_id
   AND t2.valid_flag = '1'
   and t1.center_id = center.center_id(+)
 order by t1.hospital_id, t1.serial_no

可以看到表mt_biz_fin,与bs_center可以合并到内嵌视图t1中直接与表pm_account_biz进行表连接,前面已经说过了可以去掉group by子句,进行改写后的SQL如下:

select  t1.account_id,
       t1.hospital_id,
       (select hosp_level
          from bs_hospital a
         where t1.hospital_id = a.hospital_id
           and a.valid_flag = '1') as hosp_level,
       t1.serial_no,
       t1.icd,
       (select t4.disease
          from bs_disease t4
         where t1.icd = t4.icd(+)
           and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
       t1.biz_type,
       t1.treatment_type,
       t1.disease_type,
       t1.reimburse_flag,
       t1.corp_id,
       t1.corp_type_code,
       t1.corp_name,
       t1.indi_id,
       t1.pers_type,
       t1.center_id,
       t1.district_code,
       t1.out_type,
       t1.biz_flag,
       t1.biz_flag_old,
       t1.name,
       t1.idcard,
       t1.sex,
       t1.begin_date,
       t1.end_date,
       t1.fin_date,
       t1.reg_date,
       t1.disease_fee,
       t1.violate_flag,
       t1.patient_id,
       t1.in_days,
       decode(t1.allow_treat, '0', 0, 1) As allow_treat,
       Decode(T1.Treatment_Type_Last,
              '',
              T1.Treatment_Type,
              Decode(T1.Treatment_Type,
                     '120',
                     '120',
                     Decode(T1.Biz_Type,
                            '41',
                            '扣减已记帐金额',
                            '42',
                            '扣减已记帐金额',
                            T1.Treatment_Type_Last))) As Treatment_Type_Last,
       t1.violate,
       t1.pay_money,
       t1.pay_fund,
       t1.audit_money,
       '0' as finish_flag,
       t1.MONTH_DECL_SN,
       t1.deal_flag,
       t1.audit_deal_flag,
       t1.declare_icd_guide,
       t1.declare_audit_fee,
       t1.declare_audit_fee_cl,
       t1.first_icd_guide,
       t1.first_audit_fee,
       t1.first_audit_fee_cl,
       t1.second_icd_guide,
       t1.second_audit_fee,
       t1.second_audit_fee_cl,
       t1.PAY_otherfee,
       t1.PAY_FUND003,
       t1.PAY_FUND001,
       t1.PAY_FUND201,
       t1.PAY_FUND301,
       t1.PAY_FUND999,
       t1.PAY_FUND996,
       t1.PAY_begin
  from (select a.account_id,
               a.hospital_id,
               a.serial_no,
               a.center_id,
               a.icd,
               a.biz_type,
               a.treatment_type,
               a.reimburse_flag,
               a.corp_id,
               a.corp_type_code,
               t2.corp_name,
               a.indi_id,
               a.pers_type,
               center.catalog_center,
               t2.district_code,
               decode(t2.center_id, t2.district_code, 0, 1) as out_type,
               a.biz_flag,
               a.biz_flag_old,
               t2.name,
               t2.idcard,
               t2.sex,
               t2.begin_date,
               t2.end_date,
               a.fin_date,
               t2.reg_date,
               a.violate_flag,
               (select mbf.disease_type
                  from mt_biz_fin mbf
                 where mbf.valid_flag = '1'
                   and mbf.hospital_id = a.hospital_id
                   and mbf.serial_no = a.serial_no) disease_type,
               (Select aa1.allow_treat
                  From pm_account_scene aa1
                 where aa1.hospital_id = a.hospital_id
                   and aa1.serial_no = a.serial_no
                   and aa1.valid_flag = '1') as allow_treat,
               (select distinct bb1.patient_id
                  from mt_biz_fin bb1
                 where bb1.hospital_id = a.hospital_id
                   and bb1.serial_no = a.serial_no
                   and bb1.valid_flag = '1') as patient_id,
               (select distinct bb1.in_days
                  from mt_biz_fin bb1
                 where bb1.hospital_id = a.hospital_id
                   and bb1.serial_no = a.serial_no
                   and bb1.valid_flag = '1') as in_days,
               (select Max(audit_treat_value)
                  from pm_treat_audit aa
                 where aa.account_id = a.account_id
                   and aa.audit_staff_id = '2208'
                   and aa.audit_phase = '1'
                   and aa.valid_flag = '1') as treatment_type_last,
               a.violate,
               nvl((SELECT sum(pm_account_fund.pay_money)
                     FROM pm_account_fund
                    WHERE a.account_id = pm_account_fund.account_id
                      AND pm_account_fund.valid_flag = '1'),
                   0) as pay_money,
               nvl((SELECT sum(pm_account_fund.pay_money)
                     FROM pm_account_fund
                    WHERE a.account_id = pm_account_fund.account_id
                      AND pm_account_fund.valid_flag = '1'
                      AND pm_account_fund.fund_id not in
                          ('003', '999', '996', '997')),
                   0) as pay_fund,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '003'),
                   0) AS PAY_FUND003,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
                   0) AS PAY_FUND001,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '201'),
                   0) AS PAY_FUND201,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '301'),
                   0) AS PAY_FUND301,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '999'),
                   0) AS PAY_FUND999,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID = '996'),
                   0) AS PAY_FUND996,
               NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
                     FROM PM_ACCOUNT_FUND
                    WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
                      AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
                      AND PM_ACCOUNT_FUND.FUND_ID not in
                          ('801', '001', '003', '201', '999')),
                   0) AS PAY_otherfee,
               NVL((SELECT SUM(mprf.real_pay)
                     FROM mt_pay_record_fin mprf
                    WHERE A.hospital_id = mprf.hospital_id
                      AND A.serial_no = mprf.serial_no
                      AND mprf.policy_item_code = 'S01'),
                   0) AS PAY_begin,
               (select nvl(sum(b.AUDIT_MONEY), 0)
                  from pm_fee_audit b
                 where a.account_id = b.account_id
                   and b.audit_staff_id = '2208'
                   and b.AUDIT_TYPE = '1'
                   and b.AUDIT_PHASE = '1'
                   and b.valid_flag = '1') as audit_money,
               a.MONTH_DECL_SN,
               a.deal_flag,
               decode(nvl(a.disease_fee, 0),
                      0,
                      nvl((select nvl(max(ration), 0)
                            from pm_ration pr
                           where pr.center_id = a.center_id
                             and pr.hospital_id = a.hospital_id
                             and pr.treatment_type = a.treatment_type
                             and pr.year = to_char(a.fin_date, 'yyyy')
                             and (decode(a.pers_type,
                                         '21',
                                         '21',
                                         '22',
                                         '21',
                                         '23',
                                         '21',
                                         '24',
                                         '21',
                                         '41',
                                         '21',
                                         '3',
                                         '12',
                                         '4',
                                         '12',
                                         '5',
                                         '12',
                                         '7',
                                         '12',
                                         '2') = pr.insr_no or
                                 pr.insr_no = '0')
                             and rownum < 2
                             and pr.biz_flag = a.biz_flag),
                          0),
                      a.disease_fee) as disease_fee,
               a.audit_flag as audit_deal_flag,
               a.declare_icd_guide as declare_icd_guide,
               a.declare_audit_fee as declare_audit_fee,
               a.declare_audit_fee_cl as declare_audit_fee_cl,
               a.first_icd_guide as first_icd_guide,
               a.first_audit_fee as first_audit_fee,
               a.first_audit_fee_cl as first_audit_fee_cl,
               decode(a.biz_type,
                      '12',
                      decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
                      a.second_icd_guide) second_icd_guide,
               a.second_audit_fee as second_audit_fee,
               a.second_audit_fee_cl as second_audit_fee_cl
          from bs_center center,pm_account_biz a,mt_biz_fin t2
         where 1 = 1
           and ('0' = 'A' or a.reimburse_flag ='0')
           and ('00' = '00' or a.biz_type ='00')
           and a.center_id in ('430701')
           and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
               to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
           and a.valid_flag = '1'
           and a.deal_flag in ('0', '1', '2')
           and (nvl(a.staff1_finish_flag, '0') ='0' and
               nvl(a.staff2_finish_flag, '0') = '0' and
               nvl(a.staff3_finish_flag, '0') = '0')
           and a.serial_no = t2.serial_no
           AND a.hospital_id = t2.hospital_id
           AND t2.valid_flag = '1'
           and a.center_id = center.center_id(+)
           and center.center_id in ('430701')          
           order by a.hospital_id, a.serial_no
           )t1

来测试执行一次,但是执行了10分钟还是没有执行完,其执行计划如下:

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                |     3 |   855 | 24453   (1)| 00:04:54 |
|*  1 |  TABLE ACCESS BY INDEX ROWID    | BS_HOSPITAL                    |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN             | PK_BS_HOSPITAL                 |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID    | BS_DISEASE                     |     1 |    33 |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN              | INX_BS_DISEASE_01              |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID    | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN              | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|   7 |  SORT AGGREGATE                 |                                |     1 |    34 |            |          |
|*  8 |   COUNT STOPKEY                 |                                |       |       |            |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID  | PM_RATION                      |     1 |    34 |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN            | IDX_PM_RATION_1                |     1 |       |     1   (0)| 00:00:01 |
|  11 |  HASH UNIQUE                    |                                |     1 |    26 |     2  (50)| 00:00:01 |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|* 13 |    INDEX RANGE SCAN             | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|  14 |  HASH UNIQUE                    |                                |     1 |    26 |     2  (50)| 00:00:01 |
|* 15 |   TABLE ACCESS BY INDEX ROWID   | MT_BIZ_FIN                     |     1 |    26 |     1   (0)| 00:00:01 |
|* 16 |    INDEX RANGE SCAN             | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
|* 17 |  TABLE ACCESS BY INDEX ROWID    | PM_ACCOUNT_SCENE               |     1 |    28 |     1   (0)| 00:00:01 |
|* 18 |   INDEX RANGE SCAN              | IDX_PM_ACCOUNT_SCENE_1         |     1 |       |     1   (0)| 00:00:01 |
|  19 |  SORT AGGREGATE                 |                                |     1 |    37 |            |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID   | PM_TREAT_AUDIT                 |     1 |    37 |     1   (0)| 00:00:01 |
|* 21 |    INDEX RANGE SCAN             | PK_PM_TREAT_AUDIT              |     1 |       |     1   (0)| 00:00:01 |
|  22 |    SORT AGGREGATE               |                                |     1 |    37 |            |          |
|* 23 |     TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT                 |     1 |    37 |     1   (0)| 00:00:01 |
|* 24 |      INDEX RANGE SCAN           | PK_PM_TREAT_AUDIT              |     1 |       |     1   (0)| 00:00:01 |
|  25 |  SORT AGGREGATE                 |                                |     1 |    12 |            |          |
|* 26 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    12 |     1   (0)| 00:00:01 |
|* 27 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND1            |     1 |       |     1   (0)| 00:00:01 |
|  28 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 29 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 30 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  31 |  SORT AGGREGATE                 |                                |     1 |    21 |            |          |
|* 32 |   TABLE ACCESS BY INDEX ROWID   | PM_FEE_AUDIT                   |     1 |    21 |     1   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN             | PK_PM_FEE_AUDIT                |     1 |       |     1   (0)| 00:00:01 |
|  34 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 35 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  37 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 38 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 39 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  40 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|  41 |   INLIST ITERATOR               |                                |       |       |            |          |
|* 42 |    TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 43 |     INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  44 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 45 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 46 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  47 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 48 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 49 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  50 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 51 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 52 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  53 |  SORT AGGREGATE                 |                                |     1 |    16 |            |          |
|* 54 |   TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_FUND                |     1 |    16 |     1   (0)| 00:00:01 |
|* 55 |    INDEX RANGE SCAN             | PK_PM_ACCOUNT_FUND             |     1 |       |     1   (0)| 00:00:01 |
|  56 |  SORT AGGREGATE                 |                                |     1 |    39 |            |          |
|  57 |   TABLE ACCESS BY INDEX ROWID   | MT_PAY_RECORD_FIN              |     1 |    39 |     1   (0)| 00:00:01 |
|* 58 |    INDEX RANGE SCAN             | IDX_MT_PAY_RECORD_FIN_2        |     1 |       |     1   (0)| 00:00:01 |
|  59 |  SORT ORDER BY                  |                                |     3 |   855 | 24453   (1)| 00:04:54 |
|* 60 |   TABLE ACCESS BY INDEX ROWID   | MT_BIZ_FIN                     |     1 |   107 |     1   (0)| 00:00:01 |
|  61 |    NESTED LOOPS                 |                                |     3 |   855 | 24452   (1)| 00:04:54 |
|  62 |     NESTED LOOPS                |                                |     3 |   534 | 24451   (1)| 00:04:54 |
|  63 |      TABLE ACCESS BY INDEX ROWID| BS_CENTER                      |     1 |    14 |     1   (0)| 00:00:01 |
|* 64 |       INDEX UNIQUE SCAN         | PK_BS_CENTER                   |     1 |       |     1   (0)| 00:00:01 |
|* 65 |      TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ                 |     3 |   492 | 24450   (1)| 00:04:54 |
|* 66 |       INDEX RANGE SCAN          | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE |   118K|       |    29   (7)| 00:00:01 |
|* 67 |     INDEX RANGE SCAN            | PK_MT_BIZ_FIN                  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

其执行计划中现在是选访问表BS_CENTER后再与PM_ACCOUNT_BIZ执行嵌套循环连接,与我们设想的一样,这里慢的主要原因是因为要对表连接之后的记录执行order by a.hospital_id, a.serial_no操作。PM_ACCOUNT_BIZ表的查询条件中有and a.center_id in (‘430701’) and (a.fin_date between to_date(‘2014-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and
to_date(‘2014-12-31 23:59:59’, ‘yyyy-mm-dd hh24:mi:ss’)),而在CENTER_ID与FIN_DATE列上有存在复合索引。索引信息如下所示,在创建索引时,索引列的记录默认是按升序来存储的,而查询中要进行升序操作的列就是hospital_id,serial_no,而这两列已经存在复合索引IDX_PM_ACCOUNT_BIZ_SERIAL_NO中,而该索引中首列是CENTER_ID,我们只要把fin_date列加入这个复合索引中就可以让CBO在执行查询时使用该索引且能避免排序操作,而在IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引的有两列分别是CENTER_ID与FIN_DATE所以可以将这两个索引合并成一个新的索引(create index idx_pm_account_biz_center_id on pm_account_biz(center_id,hospital_id,serial_no,fin_date),并删除原先的这两个索引。

SQL> select a.index_name,a.column_name,a.column_position
  2   from user_ind_columns a  where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_NO';
 
INDEX_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
IDX_PM_ACCOUNT_BIZ_SERIAL_NO   CENTER_ID                                                                                      1
IDX_PM_ACCOUNT_BIZ_SERIAL_NO   HOSPITAL_ID                                                                                    2
IDX_PM_ACCOUNT_BIZ_SERIAL_NO   SERIAL_NO                                                                                      3
SQL>  select a.index_name,a.column_name,a.column_position
  2   from user_ind_columns a  where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_DATE';
 
INDEX_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION
------------------------------ -------------------------------------------------------------------------------- ---------------
IDX_PM_ACCOUNT_BIZ_SERIAL_DATE CENTER_ID                                                                                      1
IDX_PM_ACCOUNT_BIZ_SERIAL_DATE FIN_DATE      

我们在创建新索引后,执行修改后的SQL语句:

SQL> set timing on
SQL> set autotrace traceonly  
SQL> select  t1.account_id,
  2         t1.hospital_id,
  3         (select hosp_level
  4            from bs_hospital a
  5           where t1.hospital_id = a.hospital_id
  6             and a.valid_flag = '1') as hosp_level,
  7         t1.serial_no,
  8         t1.icd,
  9         (select t4.disease
 10            from bs_disease t4
 11           where t1.icd = t4.icd(+)
 12             and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
 13         t1.biz_type,
 14         t1.treatment_type,
 15         t1.disease_type,
 16         t1.reimburse_flag,
 17         t1.corp_id,
 18         t1.corp_type_code,
 19         t1.corp_name,
 20         t1.indi_id,
 21         t1.pers_type,
 22         t1.center_id,
 23         t1.district_code,
 24         t1.out_type,
 25         t1.biz_flag,
 26         t1.biz_flag_old,
 27         t1.name,
 28         t1.idcard,
 29         t1.sex,
 30         t1.begin_date,
 31         t1.end_date,
 32         t1.fin_date,
 33         t1.reg_date,
 34         t1.disease_fee,
 35         t1.violate_flag,
 36         t1.patient_id,
 37         t1.in_days,
 38         decode(t1.allow_treat, '0', 0, 1) As allow_treat,
 39         Decode(T1.Treatment_Type_Last,
 40                '',
 41                T1.Treatment_Type,
 42                Decode(T1.Treatment_Type,
 43                       '120',
 44                       '120',
 45                       Decode(T1.Biz_Type,
 46                              '41',
 47                              '扣减已记帐金额',
 48                              '42',
 49                              '扣减已记帐金额',
 50                              T1.Treatment_Type_Last))) As Treatment_Type_Last,
 51         t1.violate,
 52         t1.pay_money,
 53         t1.pay_fund,
 54         t1.audit_money,
 55         '0' as finish_flag,
 56         t1.MONTH_DECL_SN,
 57         t1.deal_flag,
 58         t1.audit_deal_flag,
 59         t1.declare_icd_guide,
 60         t1.declare_audit_fee,
 61         t1.declare_audit_fee_cl,
 62         t1.first_icd_guide,
 63         t1.first_audit_fee,
 64         t1.first_audit_fee_cl,
 65         t1.second_icd_guide,
 66         t1.second_audit_fee,
 67         t1.second_audit_fee_cl,
 68         t1.PAY_otherfee,
 69         t1.PAY_FUND003,
 70         t1.PAY_FUND001,
 71         t1.PAY_FUND201,
 72         t1.PAY_FUND301,
 73         t1.PAY_FUND999,
 74         t1.PAY_FUND996,
 75         t1.PAY_begin
 76    from (select a.account_id,
 77                 a.hospital_id,
 78                 a.serial_no,
 79                 a.center_id,
 80                 a.icd,
 81                 a.biz_type,
 82                 a.treatment_type,
 83                 a.reimburse_flag,
 84                 a.corp_id,
 85                 a.corp_type_code,
 86                 t2.corp_name,
 87                 a.indi_id,
 88                 a.pers_type,
 89                 center.catalog_center,
 90                 t2.district_code,
 91                 decode(t2.center_id, t2.district_code, 0, 1) as out_type,
 92                 a.biz_flag,
 93                 a.biz_flag_old,
 94                 t2.name,
 95                 t2.idcard,
 96                 t2.sex,
 97                 t2.begin_date,
 98                 t2.end_date,
 99                 a.fin_date,
100                 t2.reg_date,
101                 a.violate_flag,
102                 (select mbf.disease_type
103                    from mt_biz_fin mbf
104                   where mbf.valid_flag = '1'
105                     and mbf.hospital_id = a.hospital_id
106                     and mbf.serial_no = a.serial_no) disease_type,
107                 (Select aa1.allow_treat
108                    From pm_account_scene aa1
109                   where aa1.hospital_id = a.hospital_id
110                     and aa1.serial_no = a.serial_no
111                     and aa1.valid_flag = '1') as allow_treat,
112                 (select distinct bb1.patient_id
113                    from mt_biz_fin bb1
114                   where bb1.hospital_id = a.hospital_id
115                     and bb1.serial_no = a.serial_no
116                     and bb1.valid_flag = '1') as patient_id,
117                 (select distinct bb1.in_days
118                    from mt_biz_fin bb1
119                   where bb1.hospital_id = a.hospital_id
120                     and bb1.serial_no = a.serial_no
121                     and bb1.valid_flag = '1') as in_days,
122                 (select Max(audit_treat_value)
123                    from pm_treat_audit aa
124                   where aa.account_id = a.account_id
125                     and aa.audit_staff_id = '2208'
126                     and aa.audit_phase = '1'
127                     and aa.valid_flag = '1') as treatment_type_last,
128                 a.violate,
129                 nvl((SELECT sum(pm_account_fund.pay_money)
130                       FROM pm_account_fund
131                      WHERE a.account_id = pm_account_fund.account_id
132                        AND pm_account_fund.valid_flag = '1'),
133                     0) as pay_money,
134                 nvl((SELECT sum(pm_account_fund.pay_money)
135                       FROM pm_account_fund
136                      WHERE a.account_id = pm_account_fund.account_id
137                        AND pm_account_fund.valid_flag = '1'
138                        AND pm_account_fund.fund_id not in
139                            ('003', '999', '996', '997')),
140                     0) as pay_fund,
141                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
142                       FROM PM_ACCOUNT_FUND
143                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
144                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
145                        AND PM_ACCOUNT_FUND.FUND_ID = '003'),
146                     0) AS PAY_FUND003,
147                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
148                       FROM PM_ACCOUNT_FUND
149                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
150                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
151                        AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
152                     0) AS PAY_FUND001,
153                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
154                       FROM PM_ACCOUNT_FUND
155                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
156                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
157                        AND PM_ACCOUNT_FUND.FUND_ID = '201'),
158                     0) AS PAY_FUND201,
159                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
160                       FROM PM_ACCOUNT_FUND
161                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
162                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
163                        AND PM_ACCOUNT_FUND.FUND_ID = '301'),
164                     0) AS PAY_FUND301,
165                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
166                       FROM PM_ACCOUNT_FUND
167                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
168                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
169                        AND PM_ACCOUNT_FUND.FUND_ID = '999'),
170                     0) AS PAY_FUND999,
171                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
172                       FROM PM_ACCOUNT_FUND
173                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
174                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
175                        AND PM_ACCOUNT_FUND.FUND_ID = '996'),
176                     0) AS PAY_FUND996,
177                 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
178                       FROM PM_ACCOUNT_FUND
179                      WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
180                        AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
181                        AND PM_ACCOUNT_FUND.FUND_ID not in
182                            ('801', '001', '003', '201', '999')),
183                     0) AS PAY_otherfee,
184                 NVL((SELECT SUM(mprf.real_pay)
185                       FROM mt_pay_record_fin mprf
186                      WHERE A.hospital_id = mprf.hospital_id
187                        AND A.serial_no = mprf.serial_no
188                        AND mprf.policy_item_code = 'S01'),
189                     0) AS PAY_begin,
190                 (select nvl(sum(b.AUDIT_MONEY), 0)
191                    from pm_fee_audit b
192                   where a.account_id = b.account_id
193                     and b.audit_staff_id = '2208'
194                     and b.AUDIT_TYPE = '1'
195                     and b.AUDIT_PHASE = '1'
196                     and b.valid_flag = '1') as audit_money,
197                 a.MONTH_DECL_SN,
198                 a.deal_flag,
199                 decode(nvl(a.disease_fee, 0),
200                        0,
201                        nvl((select nvl(max(ration), 0)
202                              from pm_ration pr
203                             where pr.center_id = a.center_id
204                               and pr.hospital_id = a.hospital_id
205                               and pr.treatment_type = a.treatment_type
206                               and pr.year = to_char(a.fin_date, 'yyyy')
207                               and (decode(a.pers_type,
208                                           '21',
209                                           '21',
210                                           '22',
211                                           '21',
212                                           '23',
213                                           '21',
214                                           '24',
215                                           '21',
216                                           '41',
217                                           '21',
218                                           '3',
219                                           '12',
220                                           '4',
221                                           '12',
222                                           '5',
223                                           '12',
224                                           '7',
225                                           '12',
226                                           '2') = pr.insr_no or
227                                   pr.insr_no = '0')
228                               and rownum < 2
229                               and pr.biz_flag = a.biz_flag),
230                            0),
231                        a.disease_fee) as disease_fee,
232                 a.audit_flag as audit_deal_flag,
233                 a.declare_icd_guide as declare_icd_guide,
234                 a.declare_audit_fee as declare_audit_fee,
235                 a.declare_audit_fee_cl as declare_audit_fee_cl,
236                 a.first_icd_guide as first_icd_guide,
237                 a.first_audit_fee as first_audit_fee,
238                 a.first_audit_fee_cl as first_audit_fee_cl,
239                 decode(a.biz_type,
240                        '12',
241                        decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
242                        a.second_icd_guide) second_icd_guide,
243                 a.second_audit_fee as second_audit_fee,
244                 a.second_audit_fee_cl as second_audit_fee_cl
245            from bs_center center,pm_account_biz a,mt_biz_fin t2
246           where 1 = 1
247             and ('0' = 'A' or a.reimburse_flag ='0')
248             and ('00' = '00' or a.biz_type ='00')
249             and a.center_id in ('430701')
250             and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
251                 to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
252             and a.valid_flag = '1'
253             and a.deal_flag in ('0', '1', '2')
254             and (nvl(a.staff1_finish_flag, '0') ='0' and
255                 nvl(a.staff2_finish_flag, '0') = '0' and
256                 nvl(a.staff3_finish_flag, '0') = '0')
257             and a.serial_no = t2.serial_no
258             AND a.hospital_id = t2.hospital_id
259             AND t2.valid_flag = '1'
260             and a.center_id = center.center_id(+)
261             and center.center_id in ('430701')          
262             order by a.hospital_id, a.serial_no
263             )t1
264  ;

189998 rows selected.

Elapsed: 00:01:37.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1778792342

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                              |     3 |   855 | 20157   (1)| 00:04:02 |
|*  1 |  TABLE ACCESS BY INDEX ROWID   | BS_HOSPITAL                  |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | PK_BS_HOSPITAL               |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID   | BS_DISEASE                   |     1 |    33 |     1   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN             | INX_BS_DISEASE_01            |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID   | MT_BIZ_FIN                   |     1 |    26 |     1   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN             | PK_MT_BIZ_FIN                |     1 |       |     1   (0)| 00:00:01 |
|   7 |  SORT AGGREGATE                |                              |     1 |    34 |            |          |
|*  8 |   COUNT STOPKEY                |                              |       |       |            |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID | PM_RATION                    |     1 |    34 |     1   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN           | IDX_PM_RATION_1              |     1 |       |     1   (0)| 00:00:01 |
|  11 |  HASH UNIQUE                   |                              |     1 |    26 |     2  (50)| 00:00:01 |
|* 12 |   TABLE ACCESS BY INDEX ROWID  | MT_BIZ_FIN                   |     1 |    26 |     1   (0)| 00:00:01 |
|* 13 |    INDEX RANGE SCAN            | PK_MT_BIZ_FIN                |     1 |       |     1   (0)| 00:00:01 |
|  14 |  HASH UNIQUE                   |                              |     1 |    26 |     2  (50)| 00:00:01 |
|* 15 |   TABLE ACCESS BY INDEX ROWID  | MT_BIZ_FIN                   |     1 |    26 |     1   (0)| 00:00:01 |
|* 16 |    INDEX RANGE SCAN            | PK_MT_BIZ_FIN                |     1 |       |     1   (0)| 00:00:01 |
|* 17 |  TABLE ACCESS BY INDEX ROWID   | PM_ACCOUNT_SCENE             |     1 |    28 |     1   (0)| 00:00:01 |
|* 18 |   INDEX RANGE SCAN             | IDX_PM_ACCOUNT_SCENE_1       |     1 |       |     1   (0)| 00:00:01 |
|  19 |  SORT AGGREGATE                |                              |     1 |    37 |            |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID  | PM_TREAT_AUDIT               |     1 |    37 |     1   (0)| 00:00:01 |
|* 21 |    INDEX RANGE SCAN            | PK_PM_TREAT_AUDIT            |     1 |       |     1   (0)| 00:00:01 |
|  22 |    SORT AGGREGATE              |                              |     1 |    37 |            |          |
|* 23 |     TABLE ACCESS BY INDEX ROWID| PM_TREAT_AUDIT               |     1 |    37 |     1   (0)| 00:00:01 |
|* 24 |      INDEX RANGE SCAN          | PK_PM_TREAT_AUDIT            |     1 |       |     1   (0)| 00:00:01 |
|  25 |  SORT AGGREGATE                |                              |     1 |    12 |            |          |
|* 26 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    12 |     1   (0)| 00:00:01 |
|* 27 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND1          |     1 |       |     1   (0)| 00:00:01 |
|  28 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 29 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 30 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  31 |  SORT AGGREGATE                |                              |     1 |    21 |            |          |
|* 32 |   TABLE ACCESS BY INDEX ROWID  | PM_FEE_AUDIT                 |     1 |    21 |     1   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN            | PK_PM_FEE_AUDIT              |     1 |       |     1   (0)| 00:00:01 |
|  34 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 35 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  37 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 38 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 39 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  40 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|  41 |   INLIST ITERATOR              |                              |       |       |            |          |
|* 42 |    TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 43 |     INDEX RANGE SCAN           | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  44 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 45 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 46 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  47 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 48 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 49 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  50 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 51 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 52 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  53 |  SORT AGGREGATE                |                              |     1 |    16 |            |          |
|* 54 |   TABLE ACCESS BY INDEX ROWID  | PM_ACCOUNT_FUND              |     1 |    16 |     1   (0)| 00:00:01 |
|* 55 |    INDEX RANGE SCAN            | PK_PM_ACCOUNT_FUND           |     1 |       |     1   (0)| 00:00:01 |
|  56 |  SORT AGGREGATE                |                              |     1 |    39 |            |          |
|  57 |   TABLE ACCESS BY INDEX ROWID  | MT_PAY_RECORD_FIN            |     1 |    39 |     1   (0)| 00:00:01 |
|* 58 |    INDEX RANGE SCAN            | IDX_MT_PAY_RECORD_FIN_2      |     1 |       |     1   (0)| 00:00:01 |
|* 59 |  TABLE ACCESS BY INDEX ROWID   | MT_BIZ_FIN                   |     1 |   107 |     1   (0)| 00:00:01 |
|  60 |   NESTED LOOPS                 |                              |     3 |   855 | 20157   (1)| 00:04:02 |
|  61 |    NESTED LOOPS                |                              |     3 |   534 | 20156   (1)| 00:04:02 |
|  62 |     TABLE ACCESS BY INDEX ROWID| BS_CENTER                    |     1 |    14 |     1   (0)| 00:00:01 |
|* 63 |      INDEX UNIQUE SCAN         | PK_BS_CENTER                 |     1 |       |     1   (0)| 00:00:01 |
|* 64 |     TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ               |     3 |   492 | 20155   (1)| 00:04:02 |
|* 65 |      INDEX RANGE SCAN          | IDX_PM_ACCOUNT_BIZ_CENTER_ID |   118K|       |   178   (4)| 00:00:03 |
|* 66 |    INDEX RANGE SCAN            | PK_MT_BIZ_FIN                |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."VALID_FLAG"='1')
   2 - access("A"."HOSPITAL_ID"=:B1)
   4 - access("T4"."CENTER_ID"=NVL(:B1,:B2) AND "T4"."ICD"=:B3)
   5 - filter("MBF"."VALID_FLAG"='1')
   6 - access("MBF"."HOSPITAL_ID"=:B1 AND "MBF"."SERIAL_NO"=:B2)
   8 - filter(ROWNUM<2)
   9 - filter(("PR"."INSR_NO"=TO_NUMBER(DECODE(:B1,'21','21','22','21','23','21','24','21','41','21','3
              ','12','4','12','5','12','7','12','2')) OR "PR"."INSR_NO"=0) AND TO_NUMBER("PR"."BIZ_FLAG")=:B2)
  10 - access("PR"."YEAR"=TO_NUMBER(TO_CHAR(:B1,'yyyy')) AND "PR"."HOSPITAL_ID"=:B2 AND
              "PR"."TREATMENT_TYPE"=:B3 AND "PR"."CENTER_ID"=:B4)
       filter("PR"."TREATMENT_TYPE"=:B1 AND "PR"."CENTER_ID"=:B2)
  12 - filter("BB1"."VALID_FLAG"='1')
  13 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
  15 - filter("BB1"."VALID_FLAG"='1')
  16 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
  17 - filter("AA1"."VALID_FLAG"='1')
  18 - access("AA1"."HOSPITAL_ID"=:B1 AND "AA1"."SERIAL_NO"=:B2)
  20 - filter("AA"."VALID_FLAG"='1')
  21 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
       filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
  23 - filter("AA"."VALID_FLAG"='1')
  24 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
       filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
  26 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  27 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
  29 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  30 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
       filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
              "PM_ACCOUNT_FUND"."FUND_ID"<>'996' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'997')
  32 - filter("B"."VALID_FLAG"='1')
  33 - access("B"."ACCOUNT_ID"=:B1 AND "B"."AUDIT_TYPE"='1' AND "B"."AUDIT_PHASE"='1' AND
              "B"."AUDIT_STAFF_ID"='2208')
       filter("B"."AUDIT_STAFF_ID"='2208')
  35 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  36 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
       filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
              "PM_ACCOUNT_FUND"."FUND_ID"<>'001' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'801' AND
              "PM_ACCOUNT_FUND"."FUND_ID"<>'201')
  38 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  39 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='003')
  42 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  43 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND ("PM_ACCOUNT_FUND"."FUND_ID"='001' OR
              "PM_ACCOUNT_FUND"."FUND_ID"='801'))
  45 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  46 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='201')
  48 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  49 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='301')
  51 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  52 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='999')
  54 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
  55 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='996')
  58 - access("MPRF"."HOSPITAL_ID"=:B1 AND "MPRF"."SERIAL_NO"=:B2 AND "MPRF"."POLICY_ITEM_CODE"='S01')
  59 - filter("T2"."VALID_FLAG"='1')
  63 - access("CENTER"."CENTER_ID"='430701')
  64 - filter(NVL("A"."STAFF2_FINISH_FLAG",'0')='0' AND NVL("A"."STAFF1_FINISH_FLAG",'0')='0' AND
              NVL("A"."STAFF3_FINISH_FLAG",'0')='0' AND "A"."REIMBURSE_FLAG"='0' AND "A"."VALID_FLAG"='1' AND
              ("A"."DEAL_FLAG"='0' OR "A"."DEAL_FLAG"='1' OR "A"."DEAL_FLAG"='2'))
  65 - access("A"."CENTER_ID"="CENTER"."CENTER_ID" AND "A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd               hh24:mi:ss'))        filter("A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "A"."CENTER_ID"='430701' AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
  66 - access("A"."HOSPITAL_ID"="T2"."HOSPITAL_ID" AND "A"."SERIAL_NO"="T2"."SERIAL_NO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2319157  consistent gets
       8301  physical reads
        576  redo size
    2809640  bytes sent via SQL*Net to client
      24709  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      189998  rows processed

从上面的实际执行情况可以看到返回了接近19万条记录花费的时间是1分37秒,也就是97秒与3286秒,已经是几十倍的提升了。

小结:这里优化的方法是通过修改SQL语句,改变表连接的方法,去掉不必要的分组(group by)操作,创建来合理的复合索引来避免排序(order by)来达到性能的提升。

乱用Hint造成性能问题案例一

某系统上午9点到11点的AWR报告中TOP SQL,其中消耗时间最长的花了9770秒,该SQL_ID为36cbabzyq13gy
这条SQL语句与SQL_ID为0frcad5600xdu,g1a0qu2b42j83所对应的SQL语句除了文本值不一样外,其它部分是相同的,这里没有使用绑定变量

SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	          SQL Id	         SQL Module	        SQL Text
9,770	            6,418	        1	          9769.94	            16.02	           36cbabzyq13gy	 w3wp.exe	        SELECT /*+ index(lt, PK_LV_U...
5,831	            3,851	        2	          2915.28	             9.56	           0frcad5600xdu	 w3wp.exe	        SELECT /*+ index(lt, PK_LV_U...
2,495	            1,643	        1	          2495.48	             4.09	           g1a0qu2b42j83	 w3wp.exe	        SELECT /*+ index(lt, PK_LV_U...
2,348	            1,482	        62	            37.86	             3.85	           a7dkwg8uhrwkj	 JDBC Thin  Client	select * from ( select a.hosp...
772	              341	        22	            35.10	             1.27	           2vpny9ut5dcm6	 JDBC Thin  Client	select t.pay_type as pay_...
670	              438	        29	            23.10	             1.10	           acj1640jvr3u5	 JDBC Thin  Client	select t.biz_flag, t1.name, t1...
618	              317	        60	            10.30	             1.01	           ggrctzgtcg14s	 JDBC Thin  Client	select t.pay_type as pay_...
617	              39	        2	           308.52	             1.01	           c5m1092x9vg2y	 JDBC Thin  Client	select w.hospital_id, t.hospit...
605	              398	        1	           604.51	             0.99	           3yy1wbuvsxm93	 w3wp.exe	        SELECT /*+ index(lt, PK_LV_U...
381	              55	        1	           380.52	             0.62	           6q1xuznmvsu5d	 w3wp.exe	        SELECT t_center.center_name, ...

从awrsqrpt报告中可以看到,该SQL的逻辑读为3亿多次

Stat Name                   Statement Total  Per Execution  % Snap Total
Elapsed Time (ms)           9,769,937        9,769,936.85    16.02
CPU Time (ms)               6,417,920        6,417,920.27    20.69
Executions                  1      
Buffer Gets                 361,831,845      361,831,845.00  15.35
Disk Reads                  23,989  23,      989.00          0.05
Parse Calls                 1                1.00            0.00
Rows                        0                0.00   
User I/O Wait Time (ms)     27,723      
Cluster Wait Time (ms)      0      
Application Wait Time (ms)  0      
Concurrency Wait Time (ms)  460      
Invalidations               0      
Version Count               2      
Sharable Mem(KB)            275     

SQL_ID为36cbabzyq13gy的SQL语句如下:

SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */
 bi.indi_id,
 bi.name,
 pt.pers_name,
 bs.sex_name,
 lt.pay_money,
 bi.idcard,
 bi.birthday,
 bf.headed_name,
 lt.fac_pay_date,
 lbb.audit_man,
 tab_hosp.hospital_name as hospital_name,
 to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm,
 bf.telephone,
 nvl((decode(lt.intensive_disability_flag,
             1,
             decode(lt.lowflag, 1, '重症伤残,', '重症伤残'),
             '') ||
     decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') ||
     decode(lt.nothing_flag, 1, '三无', '')),
     '标准') as subsidykide
  FROM lv_urban_topay_tmp lt,
       bs_insured bi,
       bs_sex bs,
       bs_person_type pt,
       bs_pres_insur bpi,
       bs_family bf,
       lv_busi_bill lbb,
       lv_busi_record lbr,
       lv_busi_assign lba,
       (select bh.hospital_name, bph.indi_id
          from bs_pers_hosp bph, bs_hospital bh
         where bph.hospital_id = bh.hospital_id
           and bph.first_flag = 1
           and bph.end_year = '2015') tab_hosp
 WHERE nvl(lt.busi_asg_no, 0) <> 0
   AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980)
   AND lt.fac_pay_date is not null
   AND bi.indi_id = lt.indi_id
   AND bs.sex = bi.sex
   AND bi.indi_id = tab_hosp.indi_id(+)
   AND lbr.busi_reco_no = lba.busi_reco_no
   AND lbr.busi_bill_sn = lbb.busi_bill_sn
   AND lt.center_id = lbb.center_id
   AND lt.busi_asg_no = lba.busi_asg_no
   AND lt.indi_id = bi.indi_id
   AND pt.pers_type = bi.pers_type
   AND bpi.indi_id = bi.indi_id
   AND lt.center_id = pt.center_id
   AND bf.family_id = bi.family_id
   AND bf.family_sta = 1
   AND bi.indi_sta = 1
   AND bpi.indi_join_sta = 1
   AND bf.center_id = lt.center_id
   AND bf.corp_id = lt.corp_id
   AND lt.policy_item_code like '%INDI_TOPAY'
   AND lt.corp_id = '19159'
   AND bpi.insr_detail_code = 21
   AND lt.center_id = '430726'
   AND lt.curr_year = '2015'
   AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00',
                                  'yyyy-MM-dd hh24:mi:ss')
   AND lt.fac_pay_date < =
       to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
   and exists (select 'X'
          FROM lv_busi_bill       lbb,
               lv_busi_record     lbr,
               lv_busi_assign     lba,
               lv_urban_topay_tmp lutt
         WHERE lbr.busi_reco_no = lba.busi_reco_no
           AND lbr.busi_bill_sn = lbb.busi_bill_sn
           AND lbb.center_id = '430726'
           AND lutt.corp_id = '19159'
           AND lutt.center_id = lbb.center_id
           AND lutt.busi_asg_no = lba.busi_asg_no
           and lba.busi_asg_no = lt.busi_asg_no
           and lutt.indi_id = bi.indi_id)
 order by lt.fac_pay_date, bi.indi_id, bi.name

通过执地xplan脚本来获得SQL_ID为36cbabzyq13gy的执行计划,其执行计划如下

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Order | Operation                               | Name                           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 |    45 | SELECT STATEMENT                        |                                |        |       | 20484 (100)|          |
|   1 |    44 |  SORT ORDER BY                          |                                |      1 |   290 | 20484   (3)| 00:04:06 |
|   2 |    43 |   NESTED LOOPS                          |                                |      1 |   290 | 20483   (3)| 00:04:06 |
|   3 |    40 |    NESTED LOOPS                         |                                |      1 |   265 | 20482   (3)| 00:04:06 |
|   4 |    37 |     NESTED LOOPS                        |                                |      1 |   254 | 20481   (3)| 00:04:06 |
|   5 |    34 |      NESTED LOOPS                       |                                |      1 |   242 | 20480   (3)| 00:04:06 |
|   6 |    32 |       NESTED LOOPS                      |                                |      1 |   230 | 20479   (3)| 00:04:06 |
|   7 |    29 |        HASH JOIN                        |                                |    168 | 27720 | 17063   (3)| 00:03:25 |
|   8 |    13 |         VIEW                            | VW_SQ_1                        |    168 |  2016 |  2441   (1)| 00:00:30 |
|   9 |    12 |          HASH UNIQUE                    |                                |    168 |  9408 |            |          |
|  10 |    11 |           NESTED LOOPS                  |                                |    168 |  9408 |  2441   (1)| 00:00:30 |
|  11 |     8 |            NESTED LOOPS                 |                                |   1718 | 75592 |  2097   (1)| 00:00:26 |
|  12 |     5 |             NESTED LOOPS                |                                |   1758 | 58014 |  1745   (1)| 00:00:21 |
|  13 |     2 |              TABLE ACCESS BY INDEX ROWID| LV_URBAN_TOPAY_TMP             |   1742 | 36582 |  1397   (1)| 00:00:17 |
|  14 |     1 |               INDEX RANGE SCAN          | IDX_LV_URBAN_TOPAY_TMP_CORP_ID |  18770 |       |    14   (0)| 00:00:01 |
|  15 |     4 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN                 |      1 |    12 |     1   (0)| 00:00:01 |
|  16 |     3 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_ASSIGN              |      1 |       |     1   (0)| 00:00:01 |
|  17 |     7 |             TABLE ACCESS BY INDEX ROWID | LV_BUSI_RECORD                 |      1 |    11 |     1   (0)| 00:00:01 |
|  18 |     6 |              INDEX UNIQUE SCAN          | PK_LV_BUSI_RECORD              |      1 |       |     1   (0)| 00:00:01 |
|  19 |    10 |            TABLE ACCESS BY INDEX ROWID  | LV_BUSI_BILL                   |      1 |    12 |     1   (0)| 00:00:01 |
|  20 |     9 |             INDEX UNIQUE SCAN           | PK_LV_BUSI_BILL                |      1 |       |     1   (0)| 00:00:01 |
|  21 |    28 |         NESTED LOOPS OUTER              |                                |    123 | 18819 | 14622   (3)| 00:02:56 |
|  22 |    21 |          HASH JOIN                      |                                |    123 | 13776 | 14375   (3)| 00:02:53 |
|  23 |    15 |           TABLE ACCESS BY INDEX ROWID   | BS_FAMILY                      |    102 |  3264 |    93   (0)| 00:00:02 |
|  24 |    14 |            INDEX RANGE SCAN             | IDX_BS_FAMILY_CORP_ID          |   1203 |       |     1   (0)| 00:00:01 |
|  25 |    20 |           HASH JOIN                     |                                |   1081K|    82M| 14272   (3)| 00:02:52 |
|  26 |    16 |            TABLE ACCESS FULL            | BS_PERSON_TYPE                 |     11 |   198 |     3   (0)| 00:00:01 |
|  27 |    19 |            HASH JOIN                    |                                |   1080K|    63M| 14258   (3)| 00:02:52 |
|  28 |    17 |             TABLE ACCESS FULL           | BS_SEX                         |      4 |    24 |     3   (0)| 00:00:01 |
|  29 |    18 |             TABLE ACCESS FULL           | BS_INSURED                     |   1080K|    57M| 14244   (3)| 00:02:51 |
|  30 |    27 |          VIEW PUSHED PREDICATE          |                                |      1 |    41 |     2   (0)| 00:00:01 |
|  31 |    26 |           NESTED LOOPS                  |                                |      1 |    57 |     2   (0)| 00:00:01 |
|  32 |    23 |            TABLE ACCESS BY INDEX ROWID  | BS_PERS_HOSP                   |      1 |    25 |     1   (0)| 00:00:01 |
|  33 |    22 |             INDEX RANGE SCAN            | PK_BS_PERS_HOSP                |      2 |       |     1   (0)| 00:00:01 |
|  34 |    25 |            TABLE ACCESS BY INDEX ROWID  | BS_HOSPITAL                    |      1 |    32 |     1   (0)| 00:00:01 |
|  35 |    24 |             INDEX UNIQUE SCAN           | PK_BS_HOSPITAL                 |      1 |       |     1   (0)| 00:00:01 |
|  36 |    31 |        TABLE ACCESS BY INDEX ROWID      | LV_URBAN_TOPAY_TMP             |      1 |    65 |  3416   (4)| 00:00:41 |
|  37 |    30 |         INDEX FULL SCAN                 | PK_LV_URBAN_TOPAY_TMP          |      1 |       |  3416   (4)| 00:00:41 |
|  38 |    33 |       INDEX UNIQUE SCAN                 | INDEX_BS_PRES_INSUR_UNIQUE     |      1 |    12 |     1   (0)| 00:00:01 |
|  39 |    36 |      TABLE ACCESS BY INDEX ROWID        | LV_BUSI_ASSIGN                 |      1 |    12 |     1   (0)| 00:00:01 |
|  40 |    35 |       INDEX UNIQUE SCAN                 | PK_LV_BUSI_ASSIGN              |      1 |       |     1   (0)| 00:00:01 |
|  41 |    39 |     TABLE ACCESS BY INDEX ROWID         | LV_BUSI_RECORD                 |      1 |    11 |     1   (0)| 00:00:01 |
|  42 |    38 |      INDEX UNIQUE SCAN                  | PK_LV_BUSI_RECORD              |      1 |       |     1   (0)| 00:00:01 |
|  43 |    42 |    TABLE ACCESS BY INDEX ROWID          | LV_BUSI_BILL                   |      1 |    25 |     1   (0)| 00:00:01 |
|  44 |    41 |     INDEX UNIQUE SCAN                   | PK_LV_BUSI_BILL                |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------


***********
Table Level
***********


Table                   Number                 Empty Average    Chain Average Global User           Sample Date
Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size MM-DD-YYYY
--------------- -------------- -------- ------------ ------- -------- ------- ------ ------ -------------- ----------
LV_URBAN_TOPAY_     22,991,252  580,702        8,018     904        5     175 YES    NO          5,747,813 12-08-2014
TMP


Column                    Column                       Distinct          Number     Number Global User           Sample Date
Name                      Details                        Values Density Buckets      Nulls Stats  Stats            Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ -------------- ----------
URBAN_TOPAY_SN            NUMBER(12,0) NOT NULL      17,882,394       0     254          0 YES    NO          5,747,813 12-08-2014
CORP_ID                   NUMBER(12,0) NOT NULL             848       0     254          0 YES    NO              5,935 12-08-2014
INDI_ID                   NUMBER(12,0) NOT NULL       1,934,475       0     254          0 YES    NO          5,747,813 12-08-2014
NAME                      VARCHAR2(20)                  380,042       0     254          0 YES    NO            580,870 12-08-2014
IDCARD                    VARCHAR2(20)                  857,905       0       1  4,611,866 YES    NO            464,507 12-08-2014
PERS_TYPE                 NUMBER(2,0)                         4       0       4          0 YES    NO              5,935 12-08-2014
POLICY_ITEM_CODE          VARCHAR2(50) NOT NULL               9       0       9          0 YES    NO              5,935 12-08-2014
POLICY_ITEM_NAME          VARCHAR2(50)                        9       0       9          0 YES    NO              5,935 12-08-2014
INTENSIVE_DISABILITY_FLAG NUMBER(1,0)                         2       1       1          0 YES    NO              5,935 12-08-2014
VETERAN_BENEFIT_FLAG      NUMBER(1,0)                         1       1       1          0 YES    NO              5,935 12-08-2014
STIPEND_FLAG              NUMBER(1,0)                         1       1       1          0 YES    NO              5,935 12-08-2014
LOANS_FLAG                NUMBER(1,0)                         2       1       1          0 YES    NO              5,935 12-08-2014
REGISTERED_NUMBER         VARCHAR2(20)                        0       0       0 ########## YES    NO                    12-08-2014
PAY_INFO_NO               NUMBER(12,0)                6,086,462       0       1          0 YES    NO          5,747,813 12-08-2014
MONEY_NO                  NUMBER(12,0)               17,398,621       0       1          0 YES    NO          5,747,813 12-08-2014
INDIPAYSER                NUMBER(12,0)               22,991,252       0       1          0 YES    NO            580,870 12-08-2014
CALC_PRD                  VARCHAR2(6)                        28       0      28          0 YES    NO              5,935 12-08-2014
SRC_TYPE                  NUMBER(2,0)                         4       0       4          0 YES    NO              5,935 12-08-2014
MONEY_ID                  NUMBER(3,0)                         8       0       8          0 YES    NO              5,935 12-08-2014
PAY_MONEY                 NUMBER(12,2)                       16       0      16          0 YES    NO              5,935 12-08-2014
DO_FLAG                   NUMBER(1,0)                         1       0       1          0 YES    NO              5,935 12-08-2014
CENTER_ID                 VARCHAR2(10)                       10       0      10          0 YES    NO              5,935 12-08-2014
LOWFLAG                   NUMBER(1,0)                         2       1       1          0 YES    NO              5,935 12-08-2014
NOTHING_FLAG              NUMBER(1,0)                         2       1       1          0 YES    NO              5,935 12-08-2014
FAMILY_ID                 NUMBER(12,0)                  660,682       0       1    714,868 YES    NO            562,833 12-08-2014
URBAN_TYPE                NUMBER(2,0)                         4       0       4          0 YES    NO              5,935 12-08-2014
URBAN_TYPE_NAME           VARCHAR2(50)                        4       0       1          0 YES    NO              5,935 12-08-2014
BUSI_ASG_NO               NUMBER(12,0)                    2,223       0     254  3,836,517 YES    NO              4,967 12-08-2014
FAC_PAY_DATE              DATE                              907       0     254  3,836,517 YES    NO              4,967 12-08-2014
CURR_YEAR                 VARCHAR2(4) NOT NULL                8       0       8          0 YES    NO              5,935 12-08-2014
MOD_TIMESTAMP             TIMESTAMP(6)(11)                    0       0       0 ########## YES    NO                    12-08-2014
IS_PRINT                  NUMBER(1,0)                         1       1       1          0 YES    NO              5,935 12-08-2014
CURR_YEAR_BEG_PRD         VARCHAR2(6)                         8       0       1          0 YES    NO              5,935 12-08-2014
CURR_YEAR_END_PRD         VARCHAR2(6)                         8       0       1          0 YES    NO              5,935 12-08-2014

                              B                                        Average     Average
Index                      Tree Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global User           Sample Date
Name            Unique    Level Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Stats            Size MM-DD-YYYY
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------ ------ ------ -------------- ----------
PK_LV_URBAN_TOP UNIQUE        2 ####     21,623,824     21,623,824           1           1    2,737,193 YES    NO            137,855 12-08-2014
AY_TMP

DX_LV_URBAN_TOP NONUNIQUE     2 ####      6,086,462     22,613,945           1           1   10,946,874 YES    NO            429,108 12-08-2014
AY_TMP_PAY

IDX_LV_URBAN_TO NONUNIQUE     2 ####          2,223     18,979,800          17         708    1,574,170 YES    NO            530,075 12-08-2014
PAY_TMP_BUS

IDX_LV_URBAN_TO NONUNIQUE     3 ####            223     22,474,402         393      25,642    5,718,207 YES    NO            284,917 12-08-2014
PAY_TMP_CENTER

IDX_LV_URBAN_TO NONUNIQUE     2 ####        660,682     21,796,771           1          17   11,256,787 YES    NO            407,563 12-08-2014
PAY_TMP_FAM

IDX_LV_URBAN_TO NONUNIQUE     2 ####      1,934,475     21,768,753           1           7   15,125,646 YES    NO            381,994 12-08-2014
PAY_TMP_INDI

IDX_LV_URBAN_TO NONUNIQUE     2 ####     17,398,621     21,880,953           1           1   11,499,589 YES    NO            423,560 12-08-2014
PAY_TMP_NO

IDX_LV_URBAN_TO NONUNIQUE     2 ####     22,485,115     22,485,115           1           1    2,820,116 YES    NO            121,303 12-08-2014
PAY_TMP_SOME

IDX_LV_URBAN_TO NONUNIQUE     3 ####            996     22,727,163          80       8,493    8,459,953 YES    NO            311,063 12-08-2014
PAY_TMP_CORP_ID

IDX_LV_URBAN_TO NONUNIQUE     2 ####              8     23,228,508       7,131     #######      828,346 YES    NO            465,750 12-08-2014
PAY_TMP_YEAR


Index           Column                     Col Column
Name            Name                       Pos Details
--------------- ------------------------- ---- ------------------------
DX_LV_URBAN_TOP PAY_INFO_NO                  1 NUMBER(12,0)
AY_TMP_PAY

                PAY_INFO_NO                  1 NUMBER(12,0)
IDX_LV_URBAN_TO BUSI_ASG_NO                  1 NUMBER(12,0)
PAY_TMP_BUS

                BUSI_ASG_NO                  1 NUMBER(12,0)
IDX_LV_URBAN_TO CENTER_ID                    1 VARCHAR2(10)
PAY_TMP_CENTER

                CENTER_ID                    1 VARCHAR2(10)
                CURR_YEAR                    2 VARCHAR2(4) NOT NULL
                CURR_YEAR                    2 VARCHAR2(4) NOT NULL
                PERS_TYPE                    3 NUMBER(2,0)
                PERS_TYPE                    3 NUMBER(2,0)
IDX_LV_URBAN_TO CORP_ID                      1 NUMBER(12,0) NOT NULL
PAY_TMP_CORP_ID

                CORP_ID                      1 NUMBER(12,0) NOT NULL
                CURR_YEAR                    2 VARCHAR2(4) NOT NULL
                CURR_YEAR                    2 VARCHAR2(4) NOT NULL
                PERS_TYPE                    3 NUMBER(2,0)
                PERS_TYPE                    3 NUMBER(2,0)
IDX_LV_URBAN_TO FAMILY_ID                    1 NUMBER(12,0)
PAY_TMP_FAM

                FAMILY_ID                    1 NUMBER(12,0)
IDX_LV_URBAN_TO INDI_ID                      1 NUMBER(12,0) NOT NULL
PAY_TMP_INDI

                INDI_ID                      1 NUMBER(12,0) NOT NULL
IDX_LV_URBAN_TO MONEY_NO                     1 NUMBER(12,0)
PAY_TMP_NO

                MONEY_NO                     1 NUMBER(12,0)
IDX_LV_URBAN_TO URBAN_TOPAY_SN               1 NUMBER(12,0) NOT NULL
PAY_TMP_SOME

                URBAN_TOPAY_SN               1 NUMBER(12,0) NOT NULL
                INDI_ID                      2 NUMBER(12,0) NOT NULL
                INDI_ID                      2 NUMBER(12,0) NOT NULL
                POLICY_ITEM_CODE             3 VARCHAR2(50) NOT NULL
                POLICY_ITEM_CODE             3 VARCHAR2(50) NOT NULL
                CORP_ID                      4 NUMBER(12,0) NOT NULL
                CORP_ID                      4 NUMBER(12,0) NOT NULL
                BUSI_ASG_NO                  5 NUMBER(12,0)
                BUSI_ASG_NO                  5 NUMBER(12,0)
                CURR_YEAR                    6 VARCHAR2(4) NOT NULL
                CURR_YEAR                    6 VARCHAR2(4) NOT NULL
IDX_LV_URBAN_TO CURR_YEAR                    1 VARCHAR2(4) NOT NULL
PAY_TMP_YEAR

                CURR_YEAR                    1 VARCHAR2(4) NOT NULL

PK_LV_URBAN_TOP URBAN_TOPAY_SN               1 NUMBER(12,0) NOT NULLAY_TMP
                INDI_ID                      2 NUMBER(12,0) NOT NULL
                POLICY_ITEM_CODE             3 VARCHAR2(50) NOT NULL
                CORP_ID                      4 NUMBER(12,0) NOT NULL
                CURR_YEAR                    5 VARCHAR2(4) NOT NULL


从上面的显示结果可以看到执行计划的第一步执行的是对索引IDX_LV_URBAN_TOPAY_TMP_CORP_ID执行索引范围扫描并没有使用Hint所指定的PK_LV_URBAN_TOPAY_TMP,而且从上面的显示的索引信息部分可以看到索引PK_LV_URBAN_TOPAY_TMP是由列URBAN_TOPAY_SN, INDI_ID, POLICY_ITEM_CODE, CORP_ID, CURR_YEAR组成的复合索引而查询条件没有URBAN_TOPAY_SN字段使用不了这个索引从上面的执行计划可以看到这一结果。上面执行计划中步骤28与步骤29执行哈希连接,都是全表扫描,其中步骤Id=29是对BS_INSURED执行100多万条记录执行全表扫并且将它们的结果与表BS_PERSON_TYPE执行哈希连接最后与BS_FAMILY执行哈希连接返回记录123条数据,其成本是14375,其中对表BS_INSURED执行全表扫描的成本就是14244。而执行计划执行的第一步就是访问LV_URBAN_TOPAY_TMP,而where条件中有 bi.indi_id = lt.indi_id AND bs.sex = bi.sex and pt.pers_type = bi.pers_type,且这条连接条件都存在索引,那么就不应该那对BS_INSURED,BS_SEX,BS_PERSON_TYPE,BS_FAMILY 这四个表之间进行哈希连接后再与这四个表(LV_URBAN_TOPAY_TMP,LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL)连接后的结果集之间执行哈希连接。而是应该
在这四个表(LV_URBAN_TOPAY_TMP,LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL)连接后与BS_INSURED执行嵌套循环连接,因为有where条件bi.indi_id = lt.indi_id 而且indi_id在表BS_INSURED中是主键,从上面的索引信息中可以看到IDX_LV_URBAN_TOPAY_TMP_CORP_ID索引的distinct key是996,而where条件能使用CBO选择使用该索引从执行计划的Order列为1的步骤可知首先执行的就是对索引IDX_LV_URBAN_TOPAY_TMP_CORP_ID的索引范围扫描。当LV_URBAN_TOPAY_TMP与BS_INSURED执行完嵌套循环连接后因为有where条件bs.sex = bi.sex and pt.pers_type = bi.pers_type所以对BS_SEX,BS_PERSON_TYPE,BS_FAMILY表都应该执行嵌套循环连接。

这里优化步骤是首先删除原来SQL语句中所使用的Hint”/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */”后并执行SQL语句:

SQL>SELECT 
 bi.indi_id,
 bi.name,
 pt.pers_name,
 bs.sex_name,
 lt.pay_money,
 bi.idcard,
 bi.birthday,
 bf.headed_name,
 lt.fac_pay_date,
 lbb.audit_man,
 tab_hosp.hospital_name as hospital_name,
 to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm,
 bf.telephone,
 nvl((decode(lt.intensive_disability_flag,
             1,
             decode(lt.lowflag, 1, '重症伤残,', '重症伤残'),
             '') ||
     decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') ||
     decode(lt.nothing_flag, 1, '三无', '')),
     '标准') as subsidykide
  FROM lv_urban_topay_tmp lt,
       bs_insured bi,
       bs_sex bs,
       bs_person_type pt,
       bs_pres_insur bpi,
       bs_family bf,
       lv_busi_bill lbb,
       lv_busi_record lbr,
       lv_busi_assign lba,
       (select bh.hospital_name, bph.indi_id
          from bs_pers_hosp bph, bs_hospital bh
         where bph.hospital_id = bh.hospital_id
           and bph.first_flag = 1
           and bph.end_year = '2015') tab_hosp
 WHERE nvl(lt.busi_asg_no, 0) <> 0
   AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980)
   AND lt.fac_pay_date is not null
   AND bi.indi_id = lt.indi_id
   AND bs.sex = bi.sex
   AND bi.indi_id = tab_hosp.indi_id(+)
   AND lbr.busi_reco_no = lba.busi_reco_no
   AND lbr.busi_bill_sn = lbb.busi_bill_sn
   AND lt.center_id = lbb.center_id
   AND lt.busi_asg_no = lba.busi_asg_no
   AND lt.indi_id = bi.indi_id
   AND pt.pers_type = bi.pers_type
   AND bpi.indi_id = bi.indi_id
   AND lt.center_id = pt.center_id
   AND bf.family_id = bi.family_id
   AND bf.family_sta = 1
   AND bi.indi_sta = 1
   AND bpi.indi_join_sta = 1
   AND bf.center_id = lt.center_id
   AND bf.corp_id = lt.corp_id
   AND lt.policy_item_code like '%INDI_TOPAY'
   AND lt.corp_id = '19159'
   AND bpi.insr_detail_code = 21
   AND lt.center_id = '430726'
   AND lt.curr_year = '2015'
   AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00',
                                  'yyyy-MM-dd hh24:mi:ss')
   AND lt.fac_pay_date < =
       to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
   and exists (select 'X'
          FROM lv_busi_bill       lbb,
               lv_busi_record     lbr,
               lv_busi_assign     lba,
               lv_urban_topay_tmp lutt
         WHERE lbr.busi_reco_no = lba.busi_reco_no
           AND lbr.busi_bill_sn = lbb.busi_bill_sn
           AND lbb.center_id = '430726'
           AND lutt.corp_id = '19159'
           AND lutt.center_id = lbb.center_id
           AND lutt.busi_asg_no = lba.busi_asg_no
           and lba.busi_asg_no = lt.busi_asg_no
           and lutt.indi_id = bi.indi_id)
 order by lt.fac_pay_date, bi.indi_id, bi.name
;
....省略输出结果
2304 rows selected.

Elapsed: 00:00:08.83

一共返回了2304条记录,使用时间是8.83秒,其执行计划如下,现在消除了对BS_INSURED的全表扫描,选择最优的执行计划

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                |     1 |   278 |    88   (2)| 00:00:02 |
|   1 |  SORT ORDER BY                          |                                |     1 |   278 |    88   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                          |                                |     1 |   278 |    82   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                   |                                |     1 |   272 |    81   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                                |     1 |   231 |    79   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                       |                                |     1 |   219 |    78   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                      |                                |     1 |   201 |    77   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                     |                                |     1 |   169 |    76   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                    |                                |     1 |   113 |    75   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                   |                                |     1 |    88 |    74   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                  |                                |     1 |    77 |    73   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |     1 |    65 |    72   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_CORP_ID |   954 |       |     1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID  | LV_BUSI_ASSIGN                 |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN           | PK_LV_BUSI_ASSIGN              |     1 |       |     1   (0)| 00:00:01 |
|  15 |           TABLE ACCESS BY INDEX ROWID   | LV_BUSI_RECORD                 |     1 |    11 |     1   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN            | PK_LV_BUSI_RECORD              |     1 |       |     1   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS BY INDEX ROWID    | LV_BUSI_BILL                   |     1 |    25 |     1   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN             | PK_LV_BUSI_BILL                |     1 |       |     1   (0)| 00:00:01 |
|* 19 |         TABLE ACCESS BY INDEX ROWID     | BS_INSURED                     |     1 |    56 |     1   (0)| 00:00:01 |
|* 20 |          INDEX UNIQUE SCAN              | PK_BS_INSURED                  |     1 |       |     1   (0)| 00:00:01 |
|  21 |           NESTED LOOPS                  |                                |     1 |    56 |     5   (0)| 00:00:01 |
|  22 |            NESTED LOOPS                 |                                |     1 |    35 |     3   (0)| 00:00:01 |
|  23 |             NESTED LOOPS                |                                |     1 |    23 |     2   (0)| 00:00:01 |
|  24 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN                 |     1 |    12 |     1   (0)| 00:00:01 |
|* 25 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_ASSIGN              |     1 |       |     1   (0)| 00:00:01 |
|  26 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD                 |  1679K|    17M|     1   (0)| 00:00:01 |
|* 27 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_RECORD              |     1 |       |     1   (0)| 00:00:01 |
|* 28 |             TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL                   | 83405 |   977K|     1   (0)| 00:00:01 |
|* 29 |              INDEX UNIQUE SCAN          | PK_LV_BUSI_BILL                |     1 |       |     1   (0)| 00:00:01 |
|* 30 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |     1 |    21 |     2   (0)| 00:00:01 |
|* 31 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_INDI    |    12 |       |     1   (0)| 00:00:01 |
|* 32 |        TABLE ACCESS BY INDEX ROWID      | BS_FAMILY                      |     1 |    32 |     1   (0)| 00:00:01 |
|* 33 |         INDEX UNIQUE SCAN               | PK_BS_FAMILY                   |     1 |       |     1   (0)| 00:00:01 |
|  34 |       TABLE ACCESS BY INDEX ROWID       | BS_PERSON_TYPE                 |     1 |    18 |     1   (0)| 00:00:01 |
|* 35 |        INDEX UNIQUE SCAN                | PK_BS_PERSON_TYPE              |     1 |       |     1   (0)| 00:00:01 |
|* 36 |      INDEX UNIQUE SCAN                  | INDEX_BS_PRES_INSUR_UNIQUE     |     1 |    12 |     1   (0)| 00:00:01 |
|  37 |     VIEW PUSHED PREDICATE               |                                |     1 |    41 |     2   (0)| 00:00:01 |
|  38 |      NESTED LOOPS                       |                                |     1 |    57 |     2   (0)| 00:00:01 |
|* 39 |       TABLE ACCESS BY INDEX ROWID       | BS_PERS_HOSP                   |     1 |    25 |     1   (0)| 00:00:01 |
|* 40 |        INDEX RANGE SCAN                 | PK_BS_PERS_HOSP                |     2 |       |     1   (0)| 00:00:01 |
|  41 |       TABLE ACCESS BY INDEX ROWID       | BS_HOSPITAL                    |     1 |    32 |     1   (0)| 00:00:01 |
|* 42 |        INDEX UNIQUE SCAN                | PK_BS_HOSPITAL                 |     1 |       |     1   (0)| 00:00:01 |
|  43 |    TABLE ACCESS BY INDEX ROWID          | BS_SEX                         |     1 |     6 |     1   (0)| 00:00:01 |
|* 44 |     INDEX UNIQUE SCAN                   | PK_BS_SEX                      |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

从上面的执行计划可以看到,当去掉Hint”/*+ index(lt, PK_LV_URBAN_TOPAY_TMP) */”后,CBO选择的执行计划果然是先访问IDX_LV_URBAN_TOPAY_TMP_CORP_ID
来访问表LV_URBAN_TOPAY_TMP,然后因为“lbr.busi_reco_no = lba.busi_reco_no AND lbr.busi_bill_sn = lbb.busi_bill_sn AND lt.center_id = lbb.center_id
AND lt.busi_asg_no = lba.busi_asg_no ”,表LV_BUSI_ASSIGN,LV_BUSI_RECORD,LV_BUSI_BILL与LV_URBAN_TOPAY_TMP连接的列都是各个表的主键,所以会分别与这三个执行嵌套循环连接。且存在where条件bi.indi_id = lt.indi_id ,而indi_id是表BS_INSURED表的主键所以也执行嵌套循环连接。而又因为与表BS_INSURED进行表连接的列也存在相关索引因此,依次类推整个执行计划都是执行嵌套循环连接。而且从下面的执行计划信息中可以看到,整个语句的逻辑读只有96762,而原来的逻辑读为3亿多次。这可是数据量级的减少。

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Order | Operation                               | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |    44 |  SORT ORDER BY                          |                                |      1 |      1 |   2304 |00:00:00.39 |   96762 |   407K|   407K|  361K (0)|
|   2 |    43 |   NESTED LOOPS                          |                                |      1 |      1 |   2304 |00:00:00.39 |   96762 |       |       |          |
|   3 |    40 |    NESTED LOOPS OUTER                   |                                |      1 |      1 |   2304 |00:00:00.37 |   94456 |       |       |          |
|   4 |    33 |     NESTED LOOPS                        |                                |      1 |      1 |   2304 |00:00:00.30 |   80900 |       |       |          |
|   5 |    31 |      NESTED LOOPS                       |                                |      1 |      1 |   2304 |00:00:00.29 |   76290 |       |       |          |
|   6 |    28 |       NESTED LOOPS                      |                                |      1 |      1 |   2304 |00:00:00.27 |   73984 |       |       |          |
|   7 |    25 |        NESTED LOOPS                     |                                |      1 |      1 |   2304 |00:00:00.25 |   67070 |       |       |          |
|   8 |    11 |         NESTED LOOPS                    |                                |      1 |      1 |   2304 |00:00:00.09 |   21695 |       |       |          |
|   9 |     8 |          NESTED LOOPS                   |                                |      1 |      1 |   2304 |00:00:00.07 |   14781 |       |       |          |
|  10 |     5 |           NESTED LOOPS                  |                                |      1 |      1 |   2304 |00:00:00.05 |    7867 |       |       |          |
|* 11 |     2 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |      1 |      1 |   2304 |00:00:00.02 |     953 |       |       |          |
|* 12 |     1 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_CORP_ID |      1 |    954 |   6986 |00:00:00.01 |      33 |       |       |          |
|  13 |     4 |            TABLE ACCESS BY INDEX ROWID  | LV_BUSI_ASSIGN                 |   2304 |      1 |   2304 |00:00:00.02 |    6914 |       |       |          |
|* 14 |     3 |             INDEX UNIQUE SCAN           | PK_LV_BUSI_ASSIGN              |   2304 |      1 |   2304 |00:00:00.01 |    4610 |       |       |          |
|  15 |     7 |           TABLE ACCESS BY INDEX ROWID   | LV_BUSI_RECORD                 |   2304 |      1 |   2304 |00:00:00.02 |    6914 |       |       |          |
|* 16 |     6 |            INDEX UNIQUE SCAN            | PK_LV_BUSI_RECORD              |   2304 |      1 |   2304 |00:00:00.01 |    4610 |       |       |          |
|* 17 |    10 |          TABLE ACCESS BY INDEX ROWID    | LV_BUSI_BILL                   |   2304 |      1 |   2304 |00:00:00.02 |    6914 |       |       |          |
|* 18 |     9 |           INDEX UNIQUE SCAN             | PK_LV_BUSI_BILL                |   2304 |      1 |   2304 |00:00:00.01 |    4610 |       |       |          |
|* 19 |    24 |         TABLE ACCESS BY INDEX ROWID     | BS_INSURED                     |   2304 |      1 |   2304 |00:00:00.15 |   45375 |       |       |          |
|* 20 |    23 |          INDEX UNIQUE SCAN              | PK_BS_INSURED                  |   2304 |      1 |   2304 |00:00:00.13 |   43071 |       |       |          |
|  21 |    22 |           NESTED LOOPS                  |                                |   2304 |      1 |   2304 |00:00:00.11 |   38461 |       |       |          |
|  22 |    19 |            NESTED LOOPS                 |                                |   2304 |      1 |   2304 |00:00:00.06 |   27648 |       |       |          |
|  23 |    16 |             NESTED LOOPS                |                                |   2304 |      1 |   2304 |00:00:00.04 |   18432 |       |       |          |
|  24 |    13 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN                 |   2304 |      1 |   2304 |00:00:00.02 |    9216 |       |       |          |
|* 25 |    12 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_ASSIGN              |   2304 |      1 |   2304 |00:00:00.01 |    6912 |       |       |          |
|  26 |    15 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD                 |   2304 |   1679K|   2304 |00:00:00.02 |    9216 |       |       |          |
|* 27 |    14 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_RECORD              |   2304 |      1 |   2304 |00:00:00.01 |    6912 |       |       |          |
|* 28 |    18 |             TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL                   |   2304 |  83405 |   2304 |00:00:00.02 |    9216 |       |       |          |
|* 29 |    17 |              INDEX UNIQUE SCAN          | PK_LV_BUSI_BILL                |   2304 |      1 |   2304 |00:00:00.01 |    6912 |       |       |          |
|* 30 |    21 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |   2304 |      1 |   2304 |00:00:00.04 |   10813 |       |       |          |
|* 31 |    20 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_INDI    |   2304 |     12 |   7564 |00:00:00.02 |    6920 |       |       |          |
|* 32 |    27 |        TABLE ACCESS BY INDEX ROWID      | BS_FAMILY                      |   2304 |      1 |   2304 |00:00:00.02 |    6914 |       |       |          |
|* 33 |    26 |         INDEX UNIQUE SCAN               | PK_BS_FAMILY                   |   2304 |      1 |   2304 |00:00:00.01 |    4610 |       |       |          |
|  34 |    30 |       TABLE ACCESS BY INDEX ROWID       | BS_PERSON_TYPE                 |   2304 |      1 |   2304 |00:00:00.01 |    2306 |       |       |          |
|* 35 |    29 |        INDEX UNIQUE SCAN                | PK_BS_PERSON_TYPE              |   2304 |      1 |   2304 |00:00:00.01 |       2 |       |       |          |
|* 36 |    32 |      INDEX UNIQUE SCAN                  | INDEX_BS_PRES_INSUR_UNIQUE     |   2304 |      1 |   2304 |00:00:00.01 |    4610 |       |       |          |
|  37 |    39 |     VIEW PUSHED PREDICATE               |                                |   2304 |      1 |   2297 |00:00:00.06 |   13556 |       |       |          |
|  38 |    38 |      NESTED LOOPS                       |                                |   2304 |      1 |   2297 |00:00:00.05 |   13556 |       |       |          |
|* 39 |    35 |       TABLE ACCESS BY INDEX ROWID       | BS_PERS_HOSP                   |   2304 |      1 |   2297 |00:00:00.04 |    8960 |       |       |          |
|* 40 |    34 |        INDEX RANGE SCAN                 | PK_BS_PERS_HOSP                |   2304 |      2 |   4327 |00:00:00.02 |    4633 |       |       |          |
|  41 |    37 |       TABLE ACCESS BY INDEX ROWID       | BS_HOSPITAL                    |   2297 |      1 |   2297 |00:00:00.01 |    4596 |       |       |          |
|* 42 |    36 |        INDEX UNIQUE SCAN                | PK_BS_HOSPITAL                 |   2297 |      1 |   2297 |00:00:00.01 |    2299 |       |       |          |
|  43 |    42 |    TABLE ACCESS BY INDEX ROWID          | BS_SEX                         |   2304 |      1 |   2304 |00:00:00.01 |    2306 |       |       |          |
|* 44 |    41 |     INDEX UNIQUE SCAN                   | PK_BS_SEX                      |   2304 |      1 |   2304 |00:00:00.01 |       2 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
-----------------------------------------------------------

  11 - filter(("LT"."FAC_PAY_DATE">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LT"."CENTER_ID"='430726' AND
              "LT"."POLICY_ITEM_CODE" LIKE '%INDI_TOPAY' AND "LT"."FAC_PAY_DATE" IS NOT NULL AND "LT"."BUSI_ASG_NO" IS NOT NULL AND NVL("LT"."BUSI_ASG_NO",0)<>0 AND
              NVL("LT"."BUSI_ASG_NO",0)<>(-980) AND NVL("LT"."BUSI_ASG_NO",0)<>(-981) AND NVL("LT"."BUSI_ASG_NO",0)<>(-997) AND NVL("LT"."BUSI_ASG_NO",0)<>(-998)
              AND NVL("LT"."BUSI_ASG_NO",0)<>(-999) AND "LT"."FAC_PAY_DATE"< =TO_DATE(' 2015-01-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss')))
  12 - access("LT"."CORP_ID"=19159 AND "LT"."CURR_YEAR"='2015')
  14 - access("LT"."BUSI_ASG_NO"="LBA"."BUSI_ASG_NO")
  16 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO")
  17 - filter("LBB"."CENTER_ID"='430726')
  18 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN")
  19 - filter(("BI"."FAMILY_ID" IS NOT NULL AND "BI"."INDI_STA"=1))
  20 - access("BI"."INDI_ID"="LT"."INDI_ID")
       filter( IS NOT NULL)
  25 - access("LBA"."BUSI_ASG_NO"=:B1)
  27 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO")
  28 - filter("LBB"."CENTER_ID"='430726')
  29 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN")
  30 - filter(("LUTT"."BUSI_ASG_NO"=:B1 AND "LUTT"."CORP_ID"=19159 AND "LUTT"."CENTER_ID"='430726'))
  31 - access("LUTT"."INDI_ID"=:B1)
  32 - filter(("BF"."CORP_ID"=19159 AND "BF"."CENTER_ID"='430726' AND "BF"."FAMILY_STA"=1))
  33 - access("BF"."FAMILY_ID"="BI"."FAMILY_ID")
  35 - access("PT"."PERS_TYPE"="BI"."PERS_TYPE" AND "PT"."CENTER_ID"='430726')
  36 - access("BPI"."INDI_ID"="BI"."INDI_ID" AND "BPI"."INSR_DETAIL_CODE"=21 AND "BPI"."INDI_JOIN_STA"=1)
  39 - filter("BPH"."END_YEAR"='2015')
  40 - access("BPH"."INDI_ID"="BI"."INDI_ID" AND "BPH"."FIRST_FLAG"=1)
       filter("BPH"."FIRST_FLAG"=1)
  42 - access("BPH"."HOSPITAL_ID"="BH"."HOSPITAL_ID")
  44 - access("BS"."SEX"=TO_NUMBER("BI"."SEX"))

现在由于没有办法修改代码,所以选择使用SQL Profile来固定该SQL的执行计划。生成SQL Profile有两种方法,这里使用手工生成SQL Profile。使用coe_xfr_sql_profile.sql脚本先对原SQL语句生成SQL Profile文件。

SQL> @/oracle/sqlt/utl/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 36cbabzyq13gy


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1849931106    6280.149

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1849931106

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "36cbabzyq13gy"
PLAN_HASH_VALUE: "1849931106"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql
on TARGET system in order to create a custom SQL Profile
with plan 1849931106 linked to adjusted sql_text.


对去掉Hint后的SQL生成SQL Profile

SQL>@E:\scripts\ch\coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

输入 1 的值:  46fc6316z15mh


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3748535674        1.24

Parameter 2:
PLAN_HASH_VALUE (required)

输入 2 的值:  3748535674

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "46fc6316z15mh"
PLAN_HASH_VALUE: "3748535674"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not
found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&pl
an_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).
');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_46fc6316z15mh_3748535674.sql
on TARGET system in order to create a custom SQL Profile
with plan 3748535674 linked to adjusted sql_text.

原SQL的SQL Profile文件coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql中有如下记录:

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 20)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$3")]',
q'[OUTLINE_LEAF(@"SEL$639F1A6F")]',
q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]',
q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]',
q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID"))]',
q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]',
q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]',
q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))]',
q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))]',
q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]',
q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]',
q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]',
q'[END_OUTLINE_DATA]');

用去掉Hint后SQL语句所对应的SQL Profile文件coe_xfr_sql_profile_46fc6316z15mh_3748535674.sql中的如下记录替换coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql文件中上述内容:

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
q'[OPT_PARAM('optimizer_index_cost_adj' 20)]',
q'[OPT_PARAM('optimizer_index_caching' 90)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$3")]',
q'[OUTLINE_LEAF(@"SEL$639F1A6F")]',
q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]',
q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]',
q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]',
q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID"))]',
q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]',
q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]',
q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))]',
q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))]',
q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]',
q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]',
q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]',
q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]',
q'[END_OUTLINE_DATA]');

替换后保存coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql文件,并执行该文件:

SQL>@C:\Users\Administrator\coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql 11.4.
3.5 2015/01/05 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM   carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM   coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM   This script is generated by coe_xfr_sql_profile.sql
SQL>REM   It contains the SQL*Plus commands to create a custom
SQL>REM   SQL Profile for SQL_ID 36cbabzyq13gy based on plan hash
SQL>REM   value 1849931106.
SQL>REM   The custom SQL Profile to be created by this script
SQL>REM   will affect plans for SQL commands with signature
SQL>REM   matching the one for SQL Text below.
SQL>REM   Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM   None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM   SQL> START coe_xfr_sql_profile_36cbabzyq13gy_1849931106.sql;
SQL>REM
SQL>REM NOTES
SQL>REM   1. Should be run as SYSTEM or SYSDBA.
SQL>REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM   3. SOURCE and TARGET systems can be the same or similar.
SQL>REM   4. To drop this custom SQL Profile after it has been created:
SQL>REM  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_36cbabzyq13gy_1849931106');
SQL>REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM  for the Oracle Tuning Pack.
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  SELECT /*+ index(lt,
  7  PK_LV_URBAN_TOPAY_TMP)
  8  */                 bi.indi_id,
  9  bi.name,
 10  pt.pers_name,
 11  bs.sex_name,
 12  lt.pay_money,
 13  bi.idcard,
 14  bi.birthday,
 15  bf.headed_name,
 16  lt.fac_pay_date,
 17  lbb.audit_man,
 18  tab_hosp.hospital_name as hospital_name,
 19  to_char(lbb.make_bill_tm,
 20  'yyyy-mm-dd')
 21  as make_bill_tm,
 22  bf.telephone,
 23  nvl((decode(lt.intensive_disability_flag,
 24  1,
 25  decode(lt.lowflag,
 26  1,
 27  '重症伤残,
 28  ',
 29  '重症伤残')
 30  ,
 31  '')
 32  ||                 decode(lt.lowflag,
 33  1,
 34  decode(lt.nothing_flag,
 35  1,
 36  '低保,
 37  ',
 38  '低保')
 39  ,
 40  '')
 41  ||                 decode(lt.nothing_flag,
 42  1,
 43  '三无',
 44  '')
 45  )
 46  ,
 47  '标准')
 48  as subsidykide           FROM lv_urban_topay_tmp lt,
 49  bs_insured         bi,
 50  bs_sex             bs,
 51  bs_person_type     pt,
 52  bs_pres_insur      bpi,
 53  bs_family          bf,
 54  lv_busi_bill       lbb,
 55  lv_busi_record     lbr,
 56  lv_busi_assign     lba,
 57  (select bh.hospital_name,
 58  bph.indi_id        from bs_pers_hosp bph,
 59  bs_hospital bh        where bph.hospital_id = bh.hospital_id        and bph
.first_flag = 1       and bph.end_year = '2015')
 60  tab_hosp           WHERE nvl(lt.busi_asg_no,
 61  0)
 62  <> 0             AND nvl(lt.busi_asg_no,
 63  0)
 64  not in (-999,
 65  -998,
 66  -997,
 67  -981,
 68  -980)
 69  AND lt.fac_pay_date is not null             AND bi.indi_id = lt.indi_id
         AND bs.sex = bi.sex             AND bi.indi_id = tab_hosp.indi_id(+)
 70  AND lbr.busi_reco_no = lba.busi_reco_no                 AND lbr.busi_bill_s
n = lbb.busi_bill_sn                 AND lt.center_id = lbb.center_id
    AND lt.busi_asg_no = lba.busi_asg_no                AND lt.indi_id =  bi.ind
i_id             AND pt.pers_type = bi.pers_type             AND bpi.indi_id = b
i.indi_id             AND lt.center_id = pt.center_id             AND bf.family_
id = bi.family_id            AND bf.family_sta = 1             AND bi.indi_sta =
 1             AND bpi.indi_join_sta = 1            AND bf.center_id = lt.center
_id             AND bf.corp_id = lt.corp_id             AND   lt.policy_item_cod
e like '%INDI_TOPAY'
 71  AND   lt.corp_id='19159'             AND   bpi.insr_detail_code=21
     AND   lt.center_id= '430726'              AND   lt.curr_year= '2015'
    AND  lt.fac_pay_date >= to_date('2014-12-01 00:00:00',
 72  'yyyy-MM-dd hh24:mi:ss')
 73  AND  lt.fac_pay_date < = to_date('2015-01-05 23:59:59',
 74  'yyyy-MM-dd hh24:mi:ss')
 75  and exists             (select 'X' FROM lv_busi_bill       lbb,
 76  lv_busi_record     lbr,
 77  lv_busi_assign     lba,
 78  lv_urban_topay_tmp lutt           WHERE lbr.busi_reco_no = lba.busi_reco_no
             AND lbr.busi_bill_sn = lbb.busi_bill_sn               AND   lbb.cen
ter_id= '430726'             AND   lutt.corp_id='19159'            AND lutt.cent
er_id = lbb.center_id             AND lutt.busi_asg_no = lba.busi_asg_no
      and  lba.busi_asg_no  =   lt.busi_asg_no              and lutt.indi_id =
bi.indi_id)
 79  order by lt.fac_pay_date,
 80  bi.indi_id,
 81  bi.name
 82  ]';
 83  h := SYS.SQLPROF_ATTR(
 84  q'[BEGIN_OUTLINE_DATA]',
 85  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 86  q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
 87  q'[OPT_PARAM('optimizer_index_cost_adj' 20)]',
 88  q'[OPT_PARAM('optimizer_index_caching' 90)]',
 89  q'[ALL_ROWS]',
 90  q'[OUTLINE_LEAF(@"SEL$3")]',
 91  q'[OUTLINE_LEAF(@"SEL$639F1A6F")]',
 92  q'[PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)]',
 93  q'[OUTLINE_LEAF(@"SEL$1")]',
 94  q'[OUTLINE(@"SEL$3")]',
 95  q'[OUTLINE(@"SEL$2")]',
 96  q'[OUTLINE(@"SEL$1")]',
 97  q'[INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_U
RBAN_TOPAY_TMP"."CURR_YEAR" "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))]',
 98  q'[INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',

 99  q'[INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]'
,
100  q'[INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
101  q'[INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))]',
102  q'[INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))]',
103  q'[INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PER
SON_TYPE"."CENTER_ID"))]',
104  q'[INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR".
"INSR_DETAIL_CODE" "BS_PRES_INSUR"."INDI_JOIN_STA"))]',
105  q'[NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
106  q'[INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))]',
107  q'[LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1"
"BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1" "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SE
L$1")]',
108  q'[USE_NL(@"SEL$1" "LBA"@"SEL$1")]',
109  q'[USE_NL(@"SEL$1" "LBR"@"SEL$1")]',
110  q'[USE_NL(@"SEL$1" "LBB"@"SEL$1")]',
111  q'[USE_NL(@"SEL$1" "BI"@"SEL$1")]',
112  q'[USE_NL(@"SEL$1" "BF"@"SEL$1")]',
113  q'[USE_NL(@"SEL$1" "PT"@"SEL$1")]',
114  q'[USE_NL(@"SEL$1" "BPI"@"SEL$1")]',
115  q'[USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")]',
116  q'[USE_NL(@"SEL$1" "BS"@"SEL$1")]',
117  q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS
_PERS_HOSP"."HOSPITAL_ID" "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR"
"BS_PERS_HOSP"."BIZ_TYPE"))]',
118  q'[INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
]',
119  q'[LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")]',
120  q'[USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")]',
121  q'[INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))]',

122  q'[INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))]'
,
123  q'[INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))]',
124  q'[INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))]'
,
125  q'[LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3
")]',
126  q'[USE_NL(@"SEL$3" "LBR"@"SEL$3")]',
127  q'[USE_NL(@"SEL$3" "LBB"@"SEL$3")]',
128  q'[USE_NL(@"SEL$3" "LUTT"@"SEL$3")]',
129  q'[END_OUTLINE_DATA]');
130  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
131  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
132  sql_text    => sql_txt,
133  profile     => h,
134  name        => 'coe_36cbabzyq13gy_1849931106',
135  description => 'coe 36cbabzyq13gy 1849931106 '||:signature||'',
136  category    => 'DEFAULT',
137  validate    => TRUE,
138  replace     => TRUE,
139  force_match => TRUE /* TRUE:FORCE (match even when different literals in SQ
L). FALSE:EXACT (similar to CURSOR_SHARING) */ );
140  END;
141  /

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
   718970022444771957


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_36cbabzyq13gy_1849931106 completed

现在我们再来执行原SQL语句来检测SQL Profile是否生效:

SQL>SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */
 bi.indi_id,
 bi.name,
 pt.pers_name,
 bs.sex_name,
 lt.pay_money,
 bi.idcard,
 bi.birthday,
 bf.headed_name,
 lt.fac_pay_date,
 lbb.audit_man,
 tab_hosp.hospital_name as hospital_name,
 to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm,
 bf.telephone,
 nvl((decode(lt.intensive_disability_flag,
             1,
             decode(lt.lowflag, 1, '重症伤残,', '重症伤残'),
             '') ||
     decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') ||
     decode(lt.nothing_flag, 1, '三无', '')),
     '标准') as subsidykide
  FROM lv_urban_topay_tmp lt,
       bs_insured bi,
       bs_sex bs,
       bs_person_type pt,
       bs_pres_insur bpi,
       bs_family bf,
       lv_busi_bill lbb,
       lv_busi_record lbr,
       lv_busi_assign lba,
       (select bh.hospital_name, bph.indi_id
          from bs_pers_hosp bph, bs_hospital bh
         where bph.hospital_id = bh.hospital_id
           and bph.first_flag = 1
           and bph.end_year = '2015') tab_hosp
 WHERE nvl(lt.busi_asg_no, 0) <> 0
   AND nvl(lt.busi_asg_no, 0) not in (-999, -998, -997, -981, -980)
   AND lt.fac_pay_date is not null
   AND bi.indi_id = lt.indi_id
   AND bs.sex = bi.sex
   AND bi.indi_id = tab_hosp.indi_id(+)
   AND lbr.busi_reco_no = lba.busi_reco_no
   AND lbr.busi_bill_sn = lbb.busi_bill_sn
   AND lt.center_id = lbb.center_id
   AND lt.busi_asg_no = lba.busi_asg_no
   AND lt.indi_id = bi.indi_id
   AND pt.pers_type = bi.pers_type
   AND bpi.indi_id = bi.indi_id
   AND lt.center_id = pt.center_id
   AND bf.family_id = bi.family_id
   AND bf.family_sta = 1
   AND bi.indi_sta = 1
   AND bpi.indi_join_sta = 1
   AND bf.center_id = lt.center_id
   AND bf.corp_id = lt.corp_id
   AND lt.policy_item_code like '%INDI_TOPAY'
   AND lt.corp_id = '19159'
   AND bpi.insr_detail_code = 21
   AND lt.center_id = '430726'
   AND lt.curr_year = '2015'
   AND lt.fac_pay_date >= to_date('2014-12-01 00:00:00',
                                  'yyyy-MM-dd hh24:mi:ss')
   AND lt.fac_pay_date < =
       to_date('2015-01-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
   and exists (select 'X'
          FROM lv_busi_bill       lbb,
               lv_busi_record     lbr,
               lv_busi_assign     lba,
               lv_urban_topay_tmp lutt
         WHERE lbr.busi_reco_no = lba.busi_reco_no
           AND lbr.busi_bill_sn = lbb.busi_bill_sn
           AND lbb.center_id = '430726'
           AND lutt.corp_id = '19159'
           AND lutt.center_id = lbb.center_id
           AND lutt.busi_asg_no = lba.busi_asg_no
           and lba.busi_asg_no = lt.busi_asg_no
           and lutt.indi_id = bi.indi_id)
 order by lt.fac_pay_date, bi.indi_id, bi.name
...省略输入结果
2304 rows selected.

Elapsed: 00:00:08.08

SQL> select * from table(dbms_xplan.display_cursor('3z8rmv9d64xyx',0,'advanced'));                                                                           

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3z8rmv9d64xyx, child number 0
-------------------------------------
SELECT /*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */  bi.indi_id,  bi.name,  pt.pers_name,  bs.sex_name,  lt.pay_money,
bi.idcard,  bi.birthday,  bf.headed_name,  lt.fac_pay_date,  lbb.audit_man,  tab_hosp.hospital_name as
hospital_name,  to_char(lbb.make_bill_tm, 'yyyy-mm-dd') as make_bill_tm,  bf.telephone,
nvl((decode(lt.intensive_disability_flag,              1,              decode(lt.lowflag, 1, '重症伤残,', '重症伤残'),
          '') ||      decode(lt.lowflag, 1, decode(lt.nothing_flag, 1, '低保,', '低保'), '') ||
decode(lt.nothing_flag, 1, '三无', '')),      '标准') as subsidykide   FROM lv_urban_topay_tmp lt,        bs_insured
bi,        bs_sex bs,        bs_person_type pt,        bs_pres_insur bpi,        bs_family bf,
lv_busi_bill lbb,        lv_busi_record lbr,        lv_busi_assign lba,        (select bh.hospital_name,
bph.indi_id           from bs_pers_hosp bph, bs_hospital bh          where bph.hospital_id = bh.hospital_id
     and bph.first_flag = 1

Plan hash value: 3748535674

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                                |       |       |    88 (100)|          |
|   1 |  SORT ORDER BY                          |                                |     1 |   278 |    88   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                          |                                |     1 |   278 |    82   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                   |                                |     1 |   272 |    81   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                                |     1 |   231 |    79   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                       |                                |     1 |   219 |    78   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                      |                                |     1 |   201 |    77   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                     |                                |     1 |   169 |    76   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                    |                                |     1 |   113 |    75   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                   |                                |     1 |    88 |    74   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                  |                                |     1 |    77 |    73   (0)| 00:00:01 |
|* 11 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |     1 |    65 |    72   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_CORP_ID |   954 |       |     1   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID  | LV_BUSI_ASSIGN                 |     1 |    12 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN           | PK_LV_BUSI_ASSIGN              |     1 |       |     1   (0)| 00:00:01 |
|  15 |           TABLE ACCESS BY INDEX ROWID   | LV_BUSI_RECORD                 |     1 |    11 |     1   (0)| 00:00:01 |
|* 16 |            INDEX UNIQUE SCAN            | PK_LV_BUSI_RECORD              |     1 |       |     1   (0)| 00:00:01 |
|* 17 |          TABLE ACCESS BY INDEX ROWID    | LV_BUSI_BILL                   |     1 |    25 |     1   (0)| 00:00:01 |
|* 18 |           INDEX UNIQUE SCAN             | PK_LV_BUSI_BILL                |     1 |       |     1   (0)| 00:00:01 |
|* 19 |         TABLE ACCESS BY INDEX ROWID     | BS_INSURED                     |     1 |    56 |     1   (0)| 00:00:01 |
|* 20 |          INDEX UNIQUE SCAN              | PK_BS_INSURED                  |     1 |       |     1   (0)| 00:00:01 |
|  21 |           NESTED LOOPS                  |                                |     1 |    56 |     5   (0)| 00:00:01 |
|  22 |            NESTED LOOPS                 |                                |     1 |    35 |     3   (0)| 00:00:01 |
|  23 |             NESTED LOOPS                |                                |     1 |    23 |     2   (0)| 00:00:01 |
|  24 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_ASSIGN                 |     1 |    12 |     1   (0)| 00:00:01 |
|* 25 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_ASSIGN              |     1 |       |     1   (0)| 00:00:01 |
|  26 |              TABLE ACCESS BY INDEX ROWID| LV_BUSI_RECORD                 |     1 |    11 |     1   (0)| 00:00:01 |
|* 27 |               INDEX UNIQUE SCAN         | PK_LV_BUSI_RECORD              |     1 |       |     1   (0)| 00:00:01 |
|* 28 |             TABLE ACCESS BY INDEX ROWID | LV_BUSI_BILL                   |     1 |    12 |     1   (0)| 00:00:01 |
|* 29 |              INDEX UNIQUE SCAN          | PK_LV_BUSI_BILL                |     1 |       |     1   (0)| 00:00:01 |
|* 30 |            TABLE ACCESS BY INDEX ROWID  | LV_URBAN_TOPAY_TMP             |     1 |    21 |     2   (0)| 00:00:01 |
|* 31 |             INDEX RANGE SCAN            | IDX_LV_URBAN_TOPAY_TMP_INDI    |    12 |       |     1   (0)| 00:00:01 |
|* 32 |        TABLE ACCESS BY INDEX ROWID      | BS_FAMILY                      |     1 |    32 |     1   (0)| 00:00:01 |
|* 33 |         INDEX UNIQUE SCAN               | PK_BS_FAMILY                   |     1 |       |     1   (0)| 00:00:01 |
|  34 |       TABLE ACCESS BY INDEX ROWID       | BS_PERSON_TYPE                 |     1 |    18 |     1   (0)| 00:00:01 |
|* 35 |        INDEX UNIQUE SCAN                | PK_BS_PERSON_TYPE              |     1 |       |     1   (0)| 00:00:01 |
|* 36 |      INDEX UNIQUE SCAN                  | INDEX_BS_PRES_INSUR_UNIQUE     |     1 |    12 |     1   (0)| 00:00:01 |
|  37 |     VIEW PUSHED PREDICATE               |                                |     1 |    41 |     2   (0)| 00:00:01 |
|  38 |      NESTED LOOPS                       |                                |     1 |    57 |     2   (0)| 00:00:01 |
|* 39 |       TABLE ACCESS BY INDEX ROWID       | BS_PERS_HOSP                   |     1 |    25 |     1   (0)| 00:00:01 |
|* 40 |        INDEX RANGE SCAN                 | PK_BS_PERS_HOSP                |     2 |       |     1   (0)| 00:00:01 |
|  41 |       TABLE ACCESS BY INDEX ROWID       | BS_HOSPITAL                    |     1 |    32 |     1   (0)| 00:00:01 |
|* 42 |        INDEX UNIQUE SCAN                | PK_BS_HOSPITAL                 |     1 |       |     1   (0)| 00:00:01 |
|  43 |    TABLE ACCESS BY INDEX ROWID          | BS_SEX                         |     1 |     6 |     1   (0)| 00:00:01 |
|* 44 |     INDEX UNIQUE SCAN                   | PK_BS_SEX                      |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
  11 - SEL$1        / LT@SEL$1
  12 - SEL$1        / LT@SEL$1
  13 - SEL$1        / LBA@SEL$1
  14 - SEL$1        / LBA@SEL$1
  15 - SEL$1        / LBR@SEL$1
  16 - SEL$1        / LBR@SEL$1
  17 - SEL$1        / LBB@SEL$1
  18 - SEL$1        / LBB@SEL$1
  19 - SEL$1        / BI@SEL$1
  20 - SEL$1        / BI@SEL$1
  21 - SEL$3
  24 - SEL$3        / LBA@SEL$3
  25 - SEL$3        / LBA@SEL$3
  26 - SEL$3        / LBR@SEL$3
  27 - SEL$3        / LBR@SEL$3
  28 - SEL$3        / LBB@SEL$3
  29 - SEL$3        / LBB@SEL$3
  30 - SEL$3        / LUTT@SEL$3
  31 - SEL$3        / LUTT@SEL$3
  32 - SEL$1        / BF@SEL$1
  33 - SEL$1        / BF@SEL$1
  34 - SEL$1        / PT@SEL$1
  35 - SEL$1        / PT@SEL$1
  36 - SEL$1        / BPI@SEL$1
  37 - SEL$639F1A6F / TAB_HOSP@SEL$1
  38 - SEL$639F1A6F
  39 - SEL$639F1A6F / BPH@SEL$2
  40 - SEL$639F1A6F / BPH@SEL$2
  41 - SEL$639F1A6F / BH@SEL$2
  42 - SEL$639F1A6F / BH@SEL$2
  43 - SEL$1        / BS@SEL$1
  44 - SEL$1        / BS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 20)
      OPT_PARAM('optimizer_index_caching' 90)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$639F1A6F")
      PUSH_PRED(@"SEL$1" "TAB_HOSP"@"SEL$1" 12)
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "LT"@"SEL$1" ("LV_URBAN_TOPAY_TMP"."CORP_ID" "LV_URBAN_TOPAY_TMP"."CURR_YEAR"
              "LV_URBAN_TOPAY_TMP"."PERS_TYPE"))
      INDEX_RS_ASC(@"SEL$1" "LBA"@"SEL$1" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))
      INDEX_RS_ASC(@"SEL$1" "LBR"@"SEL$1" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))
      INDEX_RS_ASC(@"SEL$1" "LBB"@"SEL$1" ("LV_BUSI_BILL"."BUSI_BILL_SN"))
      INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("BS_INSURED"."INDI_ID"))
      INDEX_RS_ASC(@"SEL$1" "BF"@"SEL$1" ("BS_FAMILY"."FAMILY_ID"))
      INDEX_RS_ASC(@"SEL$1" "PT"@"SEL$1" ("BS_PERSON_TYPE"."PERS_TYPE" "BS_PERSON_TYPE"."CENTER_ID"))
      INDEX(@"SEL$1" "BPI"@"SEL$1" ("BS_PRES_INSUR"."INDI_ID" "BS_PRES_INSUR"."INSR_DETAIL_CODE"
              "BS_PRES_INSUR"."INDI_JOIN_STA"))
      NO_ACCESS(@"SEL$1" "TAB_HOSP"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "BS"@"SEL$1" ("BS_SEX"."SEX"))
      LEADING(@"SEL$1" "LT"@"SEL$1" "LBA"@"SEL$1" "LBR"@"SEL$1" "LBB"@"SEL$1" "BI"@"SEL$1" "BF"@"SEL$1" "PT"@"SEL$1"
              "BPI"@"SEL$1" "TAB_HOSP"@"SEL$1" "BS"@"SEL$1")
      USE_NL(@"SEL$1" "LBA"@"SEL$1")
      USE_NL(@"SEL$1" "LBR"@"SEL$1")
      USE_NL(@"SEL$1" "LBB"@"SEL$1")
      USE_NL(@"SEL$1" "BI"@"SEL$1")
      USE_NL(@"SEL$1" "BF"@"SEL$1")
      USE_NL(@"SEL$1" "PT"@"SEL$1")
      USE_NL(@"SEL$1" "BPI"@"SEL$1")
      USE_NL(@"SEL$1" "TAB_HOSP"@"SEL$1")
      USE_NL(@"SEL$1" "BS"@"SEL$1")
      INDEX_RS_ASC(@"SEL$639F1A6F" "BPH"@"SEL$2" ("BS_PERS_HOSP"."INDI_ID" "BS_PERS_HOSP"."HOSPITAL_ID"
              "BS_PERS_HOSP"."FIRST_FLAG" "BS_PERS_HOSP"."BEG_YEAR" "BS_PERS_HOSP"."BIZ_TYPE"))
      INDEX_RS_ASC(@"SEL$639F1A6F" "BH"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
      LEADING(@"SEL$639F1A6F" "BPH"@"SEL$2" "BH"@"SEL$2")
      USE_NL(@"SEL$639F1A6F" "BH"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "LBA"@"SEL$3" ("LV_BUSI_ASSIGN"."BUSI_ASG_NO"))
      INDEX_RS_ASC(@"SEL$3" "LBR"@"SEL$3" ("LV_BUSI_RECORD"."BUSI_RECO_NO"))
      INDEX_RS_ASC(@"SEL$3" "LBB"@"SEL$3" ("LV_BUSI_BILL"."BUSI_BILL_SN"))
      INDEX_RS_ASC(@"SEL$3" "LUTT"@"SEL$3" ("LV_URBAN_TOPAY_TMP"."INDI_ID"))
      LEADING(@"SEL$3" "LBA"@"SEL$3" "LBR"@"SEL$3" "LBB"@"SEL$3" "LUTT"@"SEL$3")
      USE_NL(@"SEL$3" "LBR"@"SEL$3")
      USE_NL(@"SEL$3" "LBB"@"SEL$3")
      USE_NL(@"SEL$3" "LUTT"@"SEL$3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - filter(("LT"."FAC_PAY_DATE">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "LT"."CENTER_ID"='430726' AND "LT"."POLICY_ITEM_CODE" LIKE '%INDI_TOPAY' AND "LT"."FAC_PAY_DATE" IS NOT NULL AND
              "LT"."BUSI_ASG_NO" IS NOT NULL AND NVL("LT"."BUSI_ASG_NO",0)<>0 AND NVL("LT"."BUSI_ASG_NO",0)<>(-980) AND
              NVL("LT"."BUSI_ASG_NO",0)<>(-981) AND NVL("LT"."BUSI_ASG_NO",0)<>(-997) AND NVL("LT"."BUSI_ASG_NO",0)<>(-998) AND
              NVL("LT"."BUSI_ASG_NO",0)<>(-999) AND "LT"."FAC_PAY_DATE"< =TO_DATE(' 2015-01-05 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss')))
  12 - access("LT"."CORP_ID"=19159 AND "LT"."CURR_YEAR"='2015')
  14 - access("LT"."BUSI_ASG_NO"="LBA"."BUSI_ASG_NO")
  16 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO")
  17 - filter("LBB"."CENTER_ID"='430726')
  18 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN")
  19 - filter(("BI"."FAMILY_ID" IS NOT NULL AND "BI"."INDI_STA"=1))
  20 - access("BI"."INDI_ID"="LT"."INDI_ID")
       filter( IS NOT NULL)
  25 - access("LBA"."BUSI_ASG_NO"=:B1)
  27 - access("LBR"."BUSI_RECO_NO"="LBA"."BUSI_RECO_NO")
  28 - filter("LBB"."CENTER_ID"='430726')
  29 - access("LBR"."BUSI_BILL_SN"="LBB"."BUSI_BILL_SN")
  30 - filter(("LUTT"."BUSI_ASG_NO"=:B1 AND "LUTT"."CORP_ID"=19159 AND "LUTT"."CENTER_ID"='430726'))
  31 - access("LUTT"."INDI_ID"=:B1)
  32 - filter(("BF"."CORP_ID"=19159 AND "BF"."CENTER_ID"='430726' AND "BF"."FAMILY_STA"=1))
  33 - access("BF"."FAMILY_ID"="BI"."FAMILY_ID")
  35 - access("PT"."PERS_TYPE"="BI"."PERS_TYPE" AND "PT"."CENTER_ID"='430726')
  36 - access("BPI"."INDI_ID"="BI"."INDI_ID" AND "BPI"."INSR_DETAIL_CODE"=21 AND "BPI"."INDI_JOIN_STA"=1)
  39 - filter("BPH"."END_YEAR"='2015')
  40 - access("BPH"."INDI_ID"="BI"."INDI_ID" AND "BPH"."FIRST_FLAG"=1)
       filter("BPH"."FIRST_FLAG"=1)
  42 - access("BPH"."HOSPITAL_ID"="BH"."HOSPITAL_ID")
  44 - access("BS"."SEX"=TO_NUMBER("BI"."SEX"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=3) "LT"."FAC_PAY_DATE"[DATE,7], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20],
       "BF"."TELEPHONE"[VARCHAR2,20], TO_CHAR(INTERNAL_FUNCTION("LBB"."MAKE_BILL_TM"),'yyyy-mm-dd')[10],
       "PT"."PERS_NAME"[VARCHAR2,20], "BS"."SEX_NAME"[VARCHAR2,5], "LT"."PAY_MONEY"[NUMBER,22],
       "BI"."IDCARD"[VARCHAR2,21], "BI"."BIRTHDAY"[DATE,7], "BF"."HEADED_NAME"[VARCHAR2,50],
       NVL(DECODE("LT"."INTENSIVE_DISABILITY_FLAG",1,DECODE("LT"."LOWFLAG",1,'重症伤残,','重症伤残'),'')||DECODE("LT"."LOWFLAG",1
       ,DECODE("LT"."NOTHING_FLAG",1,'低保,','低保'),'')||DECODE("LT"."NOTHING_FLAG",1,'三无',''),'标准')[18],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70]
   2 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."BIRTHDAY"[DATE,7],
       "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20],
       "PT"."PERS_NAME"[VARCHAR2,20], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70], "BS"."SEX_NAME"[VARCHAR2,5]
   3 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1],
       "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50],
       "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20], "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70]
   4 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1],
       "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50],
       "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20]
   5 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1],
       "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50],
       "BF"."TELEPHONE"[VARCHAR2,20], "PT"."PERS_NAME"[VARCHAR2,20]
   6 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20],
       "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BF"."HEADED_NAME"[VARCHAR2,50],
       "BF"."TELEPHONE"[VARCHAR2,20]
   7 - "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22], "LT"."LOWFLAG"[NUMBER,22],
       "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7],
       "LBB"."AUDIT_MAN"[VARCHAR2,50], "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20],
       "BI"."SEX"[CHARACTER,1], "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BI"."FAMILY_ID"[NUMBER,22]
   8 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22],
       "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22],
       "LT"."FAC_PAY_DATE"[DATE,7], "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50]
   9 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22],
       "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22],
       "LT"."FAC_PAY_DATE"[DATE,7], "LBR"."BUSI_BILL_SN"[NUMBER,22]
  10 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22],
       "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22],
       "LT"."FAC_PAY_DATE"[DATE,7], "LBA"."BUSI_RECO_NO"[NUMBER,22]
  11 - "LT"."INDI_ID"[NUMBER,22], "LT"."INTENSIVE_DISABILITY_FLAG"[NUMBER,22], "LT"."PAY_MONEY"[NUMBER,22],
       "LT"."LOWFLAG"[NUMBER,22], "LT"."NOTHING_FLAG"[NUMBER,22], "LT"."BUSI_ASG_NO"[NUMBER,22],
       "LT"."FAC_PAY_DATE"[DATE,7]
  12 - "SYS_ALIAS_1".ROWID[ROWID,10]
  13 - "LBA"."BUSI_RECO_NO"[NUMBER,22]
  14 - "LBA".ROWID[ROWID,10]
  15 - "LBR"."BUSI_BILL_SN"[NUMBER,22]
  16 - "LBR".ROWID[ROWID,10]
  17 - "LBB"."MAKE_BILL_TM"[DATE,7], "LBB"."AUDIT_MAN"[VARCHAR2,50]
  18 - "LBB".ROWID[ROWID,10]
  19 - "BI"."INDI_ID"[NUMBER,22], "BI"."PERS_TYPE"[NUMBER,22], "BI"."NAME"[VARCHAR2,20], "BI"."SEX"[CHARACTER,1],
       "BI"."BIRTHDAY"[DATE,7], "BI"."IDCARD"[VARCHAR2,21], "BI"."FAMILY_ID"[NUMBER,22]
  20 - "SYS_ALIAS_2".ROWID[ROWID,10], "BI"."INDI_ID"[NUMBER,22]
  23 - "LBR"."BUSI_BILL_SN"[NUMBER,22]
  24 - "LBA"."BUSI_RECO_NO"[NUMBER,22]
  25 - "LBA".ROWID[ROWID,10]
  26 - "LBR"."BUSI_BILL_SN"[NUMBER,22]
  27 - "LBR".ROWID[ROWID,10]
  29 - "LBB".ROWID[ROWID,10]
  31 - "LUTT".ROWID[ROWID,10]
  32 - "BF"."HEADED_NAME"[VARCHAR2,50], "BF"."TELEPHONE"[VARCHAR2,20]
  33 - "BF".ROWID[ROWID,10]
  34 - "PT"."PERS_NAME"[VARCHAR2,20]
  35 - "PT".ROWID[ROWID,10]
  37 - "TAB_HOSP"."HOSPITAL_NAME"[VARCHAR2,70]
  38 - "BH"."HOSPITAL_NAME"[VARCHAR2,70]
  39 - "BPH"."HOSPITAL_ID"[VARCHAR2,20]
  40 - "BPH".ROWID[ROWID,10], "BPH"."HOSPITAL_ID"[VARCHAR2,20]
  41 - "BH"."HOSPITAL_NAME"[VARCHAR2,70]
  42 - "BH".ROWID[ROWID,10]
  43 - "BS"."SEX_NAME"[VARCHAR2,5]
  44 - "BS".ROWID[ROWID,10]

Note
-----
   - SQL profile "coe_36cbabzyq13gy_1849931106" used for this statement


276 rows selected.

从Note部分的SQL profile "coe_36cbabzyq13gy_1849931106" used for this statement可知SQL Profile对该SQL生效了,而且从执行计划中可知现在与去掉Hint“/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */”之后的SQL执行计划一样的。至此,对该SQL的优化也就完成。

这个SQL语句执行缓慢的原因就是因为使用了Hint"/*+ index(lt,PK_LV_URBAN_TOPAY_TMP) */"后,使CBO选择了错误的执行计划而造成的。