RE: odd error
- From: Jože Senegačnik <Joze.Senegacnik@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jan 2006 07:33:23 +0100
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
completed.
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Michael.Coll-Barth@xxxxxxxxxxxxxxxxxxx
Sent: Tuesday, January 24, 2006 9:21 PM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
