RE: /*+ NESTED_TABLE_GET_REFS +*/ hint

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Sat, 19 Jan 2002 05:31:44 -0800
Message-ID: <F001.003F4D7C.20020119050521@fatcity.com>

Thankyou Christian.

rgds
amar
http://amzone.netfirms.com

-----Original Message-----
From: Christian Trassens [mailto:ctrassens_at_yahoo.com] Sent: Friday, January 18, 2002 2:11 PM
To: Multiple recipients of list ORACLE-L Subject: Re: /*+ NESTED_TABLE_GET_REFS +*/ hint

Hi,

I don't know if sby answered you, pls don't mind if sby did it so.

It is a hint use it in exports, imports and loaders.

Here I enclose a note that maybe can clarify more about it.

Problem Description

---

You are querying the sql area and keeps seeing NESTED_TABLE_GET_REFS hints. This is a problem because your other sql statements continue to get flushed from the sql area in the shared pool. You don't know who is generating this hint and would like to know what it is used for and who is generating it.

Portion of the SQL AREA that you are seeing: 1,401 7 200.142857 2000-12-22/19:01:10 1,118 1 SELECT
/*+NESTED_TABLE_GET_REFS+*/"RCADMIN"."DELIVERY_ORDERS_TEMP".* FROM "RCADMIN"."DELIVERY_ORDERS_TEMP"
1,373 7 196.142857 2000-12-22/19:01:21 1,161 1 SELECT /*+NESTED_TABLE_GET_REFS+*/
"RCADMIN"."DELIVERY_ORDER_LINES".* FROM
"RCADMIN"."DELIVERY_ORDER_LINES"

1,361 7 194.4285712000-12-22/19:04:08 1,179 1 SELECT
/*+NESTED_TABLE_GET_REFS+*/"RCADMIN"."SHIPMENT_CONTAINERS".* FROM "RCADMIN"."SHIPMENT_CONTAINERS" Solution Description

---

Verify what is occuring on the system at the time you see the hints in the sql area. You notice that there are normal user processes at the time of the hint but there is also an export being done when the hints are loaded into the sql area.

Explanation

---

What this hint does is specify 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. Received on Sat Jan 19 2002 - 07:31:44 CST