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
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 educatio
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 C
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, yo
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 expla
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:
Wo
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 don'
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 g
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 recipie
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 help
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 Releas
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
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...
especi
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.
We have a situation where a process can't insert into a partition of a
partitioned table. The process just keeps running.
- A stored procedure executes a SQL insert statement (listing below).
- It normally completes in 30 minutes, but now just runs for hours.
- Oracle 8.1.6 on Dec/Compaq/HP Alpha
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 d
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
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 ta
18 matches
Mail list logo