Re: Can't insert into partition

Btw, have you noticed that you have a dot instead of comma in your hash hint: SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ How big are your tables - why do you want to have hash join on all of them? Hash joins aren't fast if you got huge datasets and little hash_area_size...

RE: Can't insert into partition

Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO

RE: Can't insert into partition

Henry - I thought somebody would ask for it and I've been wanting to try tracing another session. Works great! Here is the level 8 trace. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Dump file /oracle8/admin/madmp/udump/ora_12544.trc Oracle8i Enterprise Edition

RE: Can't insert into partition

Dennis, I am not pretty sure but you can try to increase degree of your table/index to 1 .. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 07 Aug 2003 14:19:23 -0800 Thanks Wolfgang! And thanks to the others who have helped

RE: Can't insert into partition

Not certain that this is the case, but could it be that statistics are missing for one partition, thus occulting (as in 'undefined and something is undefined') statistics for the other partitions and the table ? Not necessarily for the table you are trying to insert into. Not sure that it is a

RE: Can't insert into partition

But then it's not the same sql anymore and the access plan can be wildly different. You need to use bind variables in your sqlplus session as well. Unfortunately, even then it is not guaranteed that you'll get the same plan as you get in the plsql proc. At 06:44 AM 8/7/2003 -0800, you wrote:

Re: Can't insert into partition

Hi! IIRC, 8.1.6 didn't write any execution plan stats to trace file, it's a feature from 8.1.7. I might remember wrong though. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 10:54 PM Dennis, Is the explain

RE: Can't insert into partition

You are welcome. Happens a lot. You see what should be there rather than what IS there and wonder why it's not working as designed. Someone else, uninvolved, comes along, takes one look at the thing, points out the error and leaves you (me) feeling like an idiot. At 12:54 PM 8/8/2003 -0800,

RE: Can't insert into partition

Dennis, Is the explain plan the same between this run and the 30 minute run? The trace is just showing a FTS (looks like multi_block_read_count is 8 p3=8) of a table in file_id=197 and blocks between 103581 and 104237. Don't know which table that is (you can find out from dba_extents). I also

RE: Can't insert into partition

Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 12:19 AM To: Multiple

RE: Can't insert into partition

Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce

RE: Can't insert into partition

Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? At 02:29 PM 8/6/2003 -0800, you wrote: Henry - Thanks. I feel like I'm getting an

RE: Can't insert into partition

More information: - Running the insert statement from SQL*Plus works fine. - Normally this is run by executing a stored procedure that is in a package. Specifically, a master procedure calls a series of procedures within the package. The first 5 work fine, then this one doesn't complete. - Next

RE: Can't insert into partition

No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment.

RE: Can't insert into partition

Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned

RE: Can't insert into partition

Henry - Thanks. I feel like I'm getting an education today on the Oracle Wait Interface today. Nothing like a live problem for everything to make sense. Thanks for pointing out that I could find the table. It is our WKLYJOBFACT table. Not one we suspected. We have been doing an EXPLAIN PLAN

RE: Can't insert into partition

Dennis, If I understand your question correctly, ... Your 10046 trace file will contain the execution plan that the PL/SQL procedure is seeing, if you let the process close the cursor before you shut off the trace. I think you can also get the plan information you're looking for from the 10053