Re: Monster SQL

Thanks for ther feedback...

Unfortunately the 10046 (level 8) did not give me much feedback because the session appeared to hang after running this statement.

The trace file had an entry in it for this SQL and then it stopped on "SQL Net message from client".

This was also the wait event listed for the session in v$session_wait.  It appeared as if the session was performing no work. I've never seen anything like it before.

I will investigate the 4-way out join alternative..

Thanks again.

Matt

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c1q0em$mbm$1_at_hercules.btinternet.com>...
> You've discovered one of the major defects
> in scalar subqueries. If you use a correlated
> scalar subquery in the select list, Oracle doesn't
> have ANY visible information about the execution
> of the subquery until Oracle 9 - and then it tends
> to report the execution plan for each subquery as
> if it were an independent query, with no indication
> of frequency of execution.
>
> If you have very precise indexes that suit the three
> subqueries, then the execution path is probably
> equivalent to a 'for each row' nested loop access into
> the three copies of the table.
>
> You may get some confirmation by running with
> event 10046 set to level 8, and checking the trace
> file for file reads. You might get a clue from v$sesstat
> by checking the 'rows fetched by rowid' compared to
> 'rows fetched by tablescan' as the query runs - do array
> fetches with pauses. You could get some clues by taking
> snapshots of the cache buffers chains latches and cross
> checking latches with buffered blocks.
>
> For this example, I think I'd look carefully at a four
> way (outer) join. I dislike any Oracle feature that isn't
> instrumented to show me how much work it is doing,
> or expects to to.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearances:
> March 2004 Hotsos Symposium - The Burden of Proof
> DYnamic Sampling - an investigation
> March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
> ____UK___June
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Matt" <mccmx_at_hotmail.com> wrote in message
> news:cfee5bcf.0402280316.607847f0_at_posting.google.com...
> > Does anyone know how Oracle would resolve this piece of SQL....
> >
> > SELECT DISTINCT
> > A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, 0, 0, 0,
> > 0, A.TRC,
> > NVL(
> > (
> > select B.TL_QUANTITY from PS_TL_IPT14 b
> > where A.PROCESS_INSTANCE = B.PROCESS_INSTANCE
> > AND A.EMPLID = B.EMPLID
> > AND A.SEQ_NBR = B.SEQ_NBR
> > AND A.DUR = B.DUR AND
> > B.TRC= 'C1'
> > )
> > ,0),
> > NVL(
> > (
> > select C.TL_QUANTITY from PS_TL_IPT14 C
> > where A.PROCESS_INSTANCE = C.PROCESS_INSTANCE
> > AND A.EMPLID = C.EMPLID
> > AND A.SEQ_NBR = C.SEQ_NBR
> > AND A.DUR = C.DUR
> > AND C.TRC= 'C3DMY'
> > )
> > ,0),
> > NVL(
> > (
> > select D.TL_QUANTITY from PS_TL_IPT14 D
> > where A.PROCESS_INSTANCE = D.PROCESS_INSTANCE
> > AND A.EMPLID = D.EMPLID
> > AND A.SEQ_NBR = D.SEQ_NBR
> > AND A.DUR = D.DUR
> > AND D.TRC= 'C8'
> > )
> > ,0),
> > 123, 456
> > FROM
> > PS_TL_IPT14 A
> > WHERE
> > A.PROCESS_INSTANCE = :1
> > AND
> > TRC IN ('C1', 'C3DMY', 'C8')
> >
> >
> >
> > Running it through explain plan just returns:
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 SORT (UNIQUE)
> > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_TL_IPT14'
> > 3 2 INDEX (RANGE SCAN) OF 'PSBTL_IPT14' (NON-UNIQUE)
> >
> > Since there is no join listed in the explain plan, how does Oracle
> > perform the self join of A, B, C, and D (which are all PS_TL_IPT14).
> >
> > NB: the bind variable :1 is always every single row in the
> > table.....!
> >
> > I'm sure that this logic can be coded procedurally or even with
> > analytical SQL but I'm curious as to how Oracle would resolve it
> > because the explain is not explicit.
> >
> > Version 8.1.7.4.1 EE.
> >
> > Matt
Received on Mon Mar 01 2004 - 02:37:59 CST