| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Weird OPTIMIZER behavior for SQL*Net queries
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 - 02:49:14 CDT
 |
 |