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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
