Re: /*+ NESTED_TABLE_GET

From: Christian Trassens <ctrassens_at_yahoo.com>
Date: Fri, 18 Jan 2002 02:38:06 -0800
Message-ID: <F001.003F38B4.20020118021033@fatcity.com>

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.

Regards.
--- Amar Kumar Padhi <TS2017_at_emirates.com> wrote:
> Hi,
> Our application runs in RBO Mode. We are frequently
> getting the hint /*+
> NESTED_TABLE_GET_REFS +*/
> in the V$SQLAREA. We don't have nested tables in our
> our application either.
> What is the possible reason for this hint?
>
> Is oracle internally adding this hint. If so, why
> should it be so with our
> application queries?
>
> On
> Oracle: 8.1.7
> front-end :Forms 6i.
>
>
> rgds
> amar
>
>
>

ENG. Christian Trassens
Senior DBA
ctrassens_at_yahoo.com
christian_trassens_at_yahoo.es
Phone : +34-699240979
+34-649824704

Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: ctrassens_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 18 2002 - 04:38:06 CST

Re: /*+ NESTED_TABLE_GET_REFS +*/ hint

Re: /+ NESTED_TABLE_GET_REFS +/ hint