/*+NESTED_TABLE_GET_REFS+*/

无意中发现有一些TABLE SCAN的大表,执行的语句竟然类似SELECT /*+NESTED_TABLE_GET_REFS+*/
       "WM65"."ACTIVITYLOG".*
  FROM "WM65"."ACTIVITYLOG"

一般开发人员是不会写这样子的SQL的,再查看对应的module竟然是exp@lhb2bdb (TNS V1-V3),原来是一个export操作.

This hint specifies that nested tables should be retrieved as refs.It is used for exporting, importing and loading. It should never be issued by
user queries, and in fact is unlikely to make a difference if tried.

This is also consistent with seeing large amounts of queries with this hint, and that those queries are not reused. If this is a problem for you, try to export at another time of day. If the exports are really long-running or the system load is constant, try to find out what you are using exports for and suggest alternatives (eg. backup and recovery purposes, etc). If this is a DSS system, you probably have a smaller shared pool and use import/export heavily. If so, it might be a good idea to use a bigger shared pool when doing this activity.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10640532/viewspace-584599/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10640532/viewspace-584599/