RE: odd error

Michael,

qksmm stands for memory management services for the SQL compiler. Which is the version of the database?. If you are on 10g then you can try to set _smm_trace=65535 and a trace file should be produced in user_dump_dest directory.

alter session set "_smm_trace"=65535;
run the statement
exit

The trace might look something like:
...
qesmmCRegisterWorkArea: cursor register wds=41A29B20 wdm=09FCE5A8 qksmmAdjustStatistics(enter): isize=64 osize=64 rlen=15 qksmmAdjustStatistics(exit): isize=64 osize=64 rlen=15 qksmmEstimMemoryRequirement(enter): wds=41A29B20, isize=64, osize=64, rlen=15 qksmmEstimMemoryRequirement(exit): memMin=72, mem1Pass=72, memCache=72 ...
The trace file might help you to understand what's going on. Here is a brief explanation of the above piece of trace:

The _smm_trace produces a trace for memory manager for automatic memory management of working areas (related to PGA_AGGREGATE_TARGET parameter). For each work area the qesmmCRegisterWorkArea function is called to register the work area in local memory manager. You need a working area because of the "unique" operator. Immediately after that the qksmmAdjustStatistics function is called to adjust the statistics in the appropriate SGA memory structures (like V$SQL_PLAN_STATISTICS which is based on X$QESRSTATALL fixed table). After that the memory requirements for this working area are calculated with qksmmEstimMemoryRequirement function. This function returns the estimated memory requirements for all possible types of execution: one pass execution (mem1Pass), optimal execution (memCache) as well as for the multipass execution which requires minimal memory (memMin). This value also means the minimum possible memory which should be available to this statement to be successfully c  ompleted.

Most likely you wouldn't get ORA-600 without the unique operator. What if you use "distinct" (here I am just guessing) or add an additional "order by" clause. What is the execution plan for the statement - if it is 10.2 then this area would be used for HASH UNIQUE operation. What is the value of statistics_level parameter?

Hope this will help you a little bit.

Regards, Joze

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael.Coll-Barth_at_VerizonWireless.com Sent: Tuesday, January 24, 2006 9:21 PM
To: oracle-l_at_freelists.org
Subject: odd error

All,

I am getting a strange error with the code below. Does anyone have an explanation?

thanks,
Michael

SQL> select unique market_cd, churn_dt
  2 from eisweb.eis_ret_cmbd_rpt
  3 where ( churn_dt = '01-jan-2004' or churn_dt = '31-dec-2004' )   4 -- and market_cd > chr(0)
  5 ;
from eisweb.eis_ret_cmbd_rpt

            *
ERROR at line 2:
ORA-00600: internal error code, arguments: [qksmmAdjustStatistics1], [], [], [], [], [], [], []

The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 25 2006 - 00:33:23 CST