Thankyou Christian.
rgds
amar
http://amzone.netfirms.com
-----Original Message-----
From: Christian Trassens [mailto:[EMAIL PROTECTED]]
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.
