Re: Weird OPTIMIZER behavior for SQL*Net queries

Hi Jerzy,

This is just guess work, but I noticed two strange issues: - there are duplicate conditions in your query, and it looks like the date conditions could have been formulated with a between. With RBO you were forced to do it that way, because otherwise it couldn't recognize it was a between and could be processed with a range scan - secondly and more important, and I don't say I can immediately resolve it. However, this problem is too interesting to let it rest. In your IPC session the optimizer chooses a hash join, in the 'remote' session for nested loops.
The behavior of hash_joins is dependent on several init<sid>.ora parameters, amongst them hash_area_size and all of them can be set using alter session commands. I know the remote connect many times runs some extra commands for you when connecting (setting nls_language, application_info and the like). I'm not aware it does set hash_area_size. You also would expect the hash_area_size is allocated in the background task, and is allocated in the uga.
Now I'm also not aware the optimizer knows the difference between sqlnet i/o and ipc i/o, that would be very smart.
What you could try in your 'remote' session is setting the hash_area_size non-zero and try if you can coerce this session to take the same path. Let me know any outcome, I would very much like to know what is going on too.
Hth,

--
Sybrand Bakker, Oracle DBA

Jerzy Tomasik <jerzy_at_tus.ssi1.com> wrote in message news:37D0CEF9.A796BBEF_at_tus.ssi1.com...
> I guess I've been looking at this for too long, to post an Oracle question
> without
> the version :-)
> Oracle 7.3.3.6, SQL*Plus 2.3.3.6 on Solaris 2.5.1
> By direct connection I mean an sqlplus IPC session on the server.
> The remote connection uses the tnsname of the server, again executing
> on the server. I don't have a login.sql, but at any rate, I'm using
exactly
> the same environment to start sqlplus. (Initially I discovered the
problem
> remotely, with JDBC, and finally isolated it to the difference that I've
> described).
>
> Here's the output from the analyzer:
> Local (fast) execution plan:
> SELECT STATEMENT Cost = 565
> SORT GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> HASH JOIN
> TABLE ACCESS BY ROWID SSI_WAFER
> INDEX RANGE SCAN SSI_WAFER_DEVICE_IDX
> NESTED LOOPS
> TABLE ACCESS BY ROWID SSI_DEVICE_CONFIG
> INDEX RANGE SCAN SYS_C001008
> TABLE ACCESS FULL DYM_STATISTIC
> TABLE ACCESS BY ROWID DYM_LAYER_INSPECTION
> INDEX RANGE SCAN LAYER_SCAN_INDEX1
> TABLE ACCESS BY ROWID DYM_DEFECT
> INDEX RANGE SCAN DEFECT_SCAN_INDEX1
>
> Remote (slow) execution plan:
> SELECT STATEMENT Cost = 778
> SORT GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY ROWID SSI_DEVICE_CONFIG
> INDEX RANGE SCAN SYS_C001008
> TABLE ACCESS BY ROWID SSI_WAFER
> INDEX RANGE SCAN SSI_WAFER_DEVICE_IDX
> TABLE ACCESS FULL DYM_STATISTIC
> TABLE ACCESS BY ROWID DYM_LAYER_INSPECTION
> INDEX RANGE SCAN LAYER_SCAN_INDEX1
> TABLE ACCESS BY ROWID DYM_DEFECT
> INDEX RANGE SCAN DEFECT_SCAN_INDEX1
>
> At the risk of boring anybody to death :-), here's the query, but I agree
with
> your
> assessment, that an optimizer is an optimizer and there must be a
difference on
> my
> side. I just wish I knew what it was.
>
>
> SELECT COUNT(*) FROM
> (
> SELECT
> STATS.TECHNOLOGY TECHNOLOGY,
> WAFER.DEVICE DEVICE,
> STATS.LAYER_STEP_ID LAYER_STEP,
> DEFECT.CLUSTER_ID CLUSTER_ID,
> WAFER.LOT_ID LOT_ID,
> WAFER.WAFER_ID WAFER_ID,
> STATS.FAB_ID FAB_ID,
> WAFER.TESTER_TYPE TESTER_TYPE,
> WAFER.TESTER_ID TESTER_ID,
> WAFER.OPERATOR_ID PROBE_OPERATOR,
> WAFER.PROGRAM PROBE_PROGRAM,
> WAFER.START_TIME PROBE_START,
> STATS.SCAN_DATE_TIME INSPECT_TIME,
> STATS.INSPECT_PROG_ID INSPECT_PROG_ID,
> STATS.INSPECT_EQUIP_ID INSPECT_EQUIP_ID,
> DEFECT.DTYPE1_CLASS CLASS,
> DEFECT.X_DIE_NUM XN,
> DEFECT.Y_DIE_NUM YN,
> WAFER.SGID SGID,
> DEVICE.X_DIE_SHIFT XCP,
> DEVICE.Y_DIE_SHIFT YCP,
> LAYER.X1_ALIGNMENT XCD,
> LAYER.Y1_ALIGNMENT YCD,
> LAYER.X_DIE_SIZE XSD,
> LAYER.Y_DIE_SIZE YSD,
> LAYER.X_DIE_STREET XTD,
> LAYER.Y_DIE_STREET YTD,
> DEVICE.X_PERIOD XSP,
> DEVICE.Y_PERIOD YSP,
> DEVICE.X_STREET XTP,
> DEVICE.Y_STREET YTP,
> STATS.SCAN_AREA AREA,
> COUNT(*) DEFECTS
> FROM
> DYM_STATISTIC STATS,
> SSI_WAFER WAFER,
> DYM_LAYER_INSPECTION LAYER,
> SSI_DEVICE_CONFIG DEVICE,
> DYM_DEFECT DEFECT
> WHERE WAFER.START_TIME >= SYSDATE-7
> AND WAFER.START_TIME < SYSDATE-1
> AND WAFER.DEVICE = '3168-1-10Z1-81'
> AND WAFER.START_TIME < SYSDATE-1
> AND WAFER.DEVICE = '3168-1-10Z1-81'
> AND SUBSTR(STATS.LOT_ID, 1, 6) = WAFER.LOT_ID
> AND SUBSTR(STATS.WAFER_ID, 2, 2) = WAFER.WAFER_ID
> AND DEFECT.LAYER_SCAN_INDEX = STATS.LAYER_SCAN_INDEX
> AND LAYER.LAYER_SCAN_INDEX = STATS.LAYER_SCAN_INDEX
> AND LAYER.LAYER_SCAN_INDEX = DEFECT.LAYER_SCAN_INDEX
> AND WAFER.DEVICE = DEVICE.DEVICE
> GROUP BY
> STATS.TECHNOLOGY,
> WAFER.DEVICE,
> STATS.LAYER_STEP_ID,
> DEFECT.CLUSTER_ID,
> WAFER.LOT_ID,
> WAFER.WAFER_ID,
> STATS.FAB_ID,
> WAFER.TESTER_TYPE,
> WAFER.TESTER_ID,
> WAFER.OPERATOR_ID,
> WAFER.PROGRAM,
> WAFER.START_TIME,
> STATS.SCAN_DATE_TIME,
> STATS.INSPECT_PROG_ID,
> STATS.INSPECT_EQUIP_ID,
> DEFECT.DTYPE1_CLASS,
> DEFECT.X_DIE_NUM,
> DEFECT.Y_DIE_NUM,
> WAFER.SGID,
> DEVICE.X_DIE_SHIFT,
> DEVICE.Y_DIE_SHIFT,
> LAYER.X1_ALIGNMENT,
> LAYER.Y1_ALIGNMENT,
> LAYER.X_DIE_SIZE,
> LAYER.Y_DIE_SIZE,
> LAYER.X_DIE_STREET,
> LAYER.Y_DIE_STREET,
> DEVICE.X_PERIOD,
> DEVICE.Y_PERIOD,
> DEVICE.X_STREET,
> DEVICE.Y_STREET,
> STATS.SCAN_AREA
> )
>
> -Jerzy
>
> Sybrand Bakker wrote:
>
> > Without extra information it is impossible to resolve this issue.
> > What is the version of Oracle?
> > What is the platform?
> > What do you mean by 'direct connection': a sql*plus session on the
server
> > itself by means of IPC or BEQ.
> > What is that query and what are the execution paths.
> > I'm asking this because the optimizer is the optimizer, there shouldn't
been
> > any difference, and there are no settings that take sqlnet into account.
> > There must exist some difference on your side, maybe the optimizer_goal
> > setting modified by login.sql, or something like that.
> >
> > Hth,
> >
> > --
> > Sybrand Bakker, Oracle DBA
> >
> > Jerzy Tomasik <jerzy_at_tus.ssi1.com> wrote in message
> > news:37D0A5B2.BC9E5FE6_at_tus.ssi1.com...
> > > Hi,
> > >
> > > I'm seeing a strange performance problem. The same query performs
very
> > > well when executed
> > > with a direct connection (e.g. sqlplus scott/tiger) and very poorly
when
> > > executed through SQL*Net
> > > (e.g. sqlplus scott/tiger_at_mydb). Running the analyzer shows that the
> > > execution path is different in
> > > both cases.
> > > BTW, the query generates virtually no network traffic (returns just a
> > > count).
> > >
> > > Is there any way I can control or force the optimizer to behave the
same
> > > way for a query
> > > coming through SQL*Net as it does for local queries?
> > > I've read the docs about the hints, etc, but I haven't seen anything
> > > that seems to be the answer.
> > >
> > > TIA,
> > >
> > > -Jerzy
> > >
>
Received on Sat Sep 04 1999 - 04:27:07 CDT