I have a query with an inline view aliased as 'x'. When I run the
query, it takes hours. If I take the inline view out, throw it into a
temporary table and then run the query using that, it comes back in a
few minutes. My question is is there a hint I can give it to process
the inline view first? I tried using Ordered or Leading (x), but it
didn't change the plan. The plan is listed below also.



SELECT l1.link_pvid link_pvid_1, fc_1, l2.link_pvid link_pvid_2, fc_2,
qt_util.get_subregion(l1.left_admin_place_id) region, cm.value cond_mod
,NVL((SELECT 'Y' FROM nt_date_Time_modifier dtm WHERE rownum<2 AND
dtm.condition_id= x.condition_id),'N') date_modifiers
FROM
(SELECT l1.condition_id,l1.link_id l1_link_id, l2.link_id l2_link_id,
l1.functional_class fc_1,
l2.functional_class fc_2 FROM tmp_links l1, tmp_links l2 WHERE
l1.cnt=2
AND l2.functional_class <= l1.functional_class
AND l1.nav_strand_id=l2.nav_strand_id
and l1.sequence_number=0
and l2.sequence_number=1
and l1.functional_class<5
) x
, nt_link l1, nt_link l2, nt_condition_modifier cm
WHERE x.l1_link_id=l1.link_id and x.l2_link_id=l2.link_id
AND cm.condition_id(+)=x.condition_id and cm.type(+)=1


Plan:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 8

COUNT STOPKEY
INDEX RANGE SCAN EU_MAP_IW.PK_DATE_TIME_MODIFIER_CONDITIO 2 14
3
NESTED LOOPS 1 178 8
NESTED LOOPS 1 164 6
NESTED LOOPS OUTER 1 143 4
NESTED LOOPS 1 130 2
TABLE ACCESS BY INDEX ROWID EU_MAP_IW.TMP_LINKS 1 78 1

INDEX RANGE SCAN EU_MAP_IW.IDX_TMP_LINK2 1 2

TABLE ACCESS BY INDEX ROWID EU_MAP_IW.TMP_LINKS 1 52 1

INDEX RANGE SCAN EU_MAP_IW.IDX_TMP_LINK2 1 1

TABLE ACCESS BY INDEX ROWID EU_MAP_IW.NT_CONDITION_MODIFIER 1
13 2
INDEX UNIQUE SCAN EU_MAP_IW.PK_CONDITION_MODIFIER_CONDITIO 1
1
TABLE ACCESS BY INDEX ROWID EU_MAP_IW.NT_LINK 1 21 2

INDEX UNIQUE SCAN EU_MAP_IW.PK_LINK_LINKID 1 1

TABLE ACCESS BY INDEX ROWID EU_MAP_IW.NT_LINK 1 14 2

INDEX UNIQUE SCAN EU_MAP_IW.PK_LINK_LINKID 1 1

Mike C wrote:
I don't see anything really odd in the above explain plan, you might
try a DBMS_XPLAN to see if it helps - Daniel Morgan recently posted how
to work with this feature in another thread.

Looking at the SQL statement (reformatted so that I can follow it):
SELECT
L1.LINK_PVID LINK_PVID_1,
FC_1,
L2.LINK_PVID LINK_PVID_2,
FC_2,
QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION,
CM.VALUE COND_MOD,
NVL(
(SELECT
'Y'
FROM
NT_DATE_TIME_MODIFIER DTM
WHERE
ROWNUM<2
AND DTM.CONDITION_ID=X.CONDITION_ID),'N') DATE_MODIFIERS
FROM
(SELECT
L1.CONDITION_ID,
L1.LINK_ID L1_LINK_ID,
L2.LINK_ID L2_LINK_ID,
L1.FUNCTIONAL_CLASS FC_1,
L2.FUNCTIONAL_CLASS FC_2
FROM
TMP_LINKS L1,
TMP_LINKS L2
WHERE
L1.CNT=2
AND L2.FUNCTIONAL_CLASS <= L1.FUNCTIONAL_CLASS
AND L1.NAV_STRAND_ID=L2.NAV_STRAND_ID
AND L1.SEQUENCE_NUMBER=0
AND L2.SEQUENCE_NUMBER=1
AND L1.FUNCTIONAL_CLASS<5) X,
NT_LINK L1,
NT_LINK L2,
NT_CONDITION_MODIFIER CM
WHERE
X.L1_LINK_ID=L1.LINK_ID
AND X.L2_LINK_ID=L2.LINK_ID
AND CM.CONDITION_ID(+)=X.CONDITION_ID
AND CM.TYPE(+)=1;

QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION - this seems to
be a PL/SQL function, is it optimized?

NVL(
(SELECT
'Y'
FROM
NT_DATE_TIME_MODIFIER DTM
WHERE
ROWNUM<2
AND DTM.CONDITION_ID=X.CONDITION_ID),'N') DATE_MODIFIERS
- might be better to throw this into an inline view. I see that you
are stopping after the first row is retrieved, there may be better
ways.

FROM
TMP_LINKS L1,
TMP_LINKS L2
WHERE
L1.CNT=2
AND L2.FUNCTIONAL_CLASS <= L1.FUNCTIONAL_CLASS
AND L1.NAV_STRAND_ID=L2.NAV_STRAND_ID
AND L1.SEQUENCE_NUMBER=0
AND L2.SEQUENCE_NUMBER=1
AND L1.FUNCTIONAL_CLASS<5

Looks like an intentional Cartesian join. It might also help to
specify AND L2.FUNCTIONAL_CLASS<5. Maybe if you prejoin each of these
copies of TMP_LINKS with NT_LINK, and then join those results with each
other it would help.

With these changes, your SQL statement _might_ look similar to this:
SELECT
L1.LINK_PVID LINK_PVID_1,
FC_1,
L2.LINK_PVID LINK_PVID_2,
FC_2,
QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION,
CM.VALUE COND_MOD,
NVL2(DTM.MATCHES,'Y','N') DATE_MODIFIERS
FROM
(SELECT
L1.LINK_PVID_1,
L1.CONDITION_ID,
L1.FC_1,
L1.NAV_STRAND_ID,
L2.LINK_PVID_2,
L2.FC_2
FROM
(SELECT
L1.LINK_PVID LINK_PVID_1,
TL1.CONDITION_ID,
TL1.FUNCTIONAL_CLASS FC_1,
TL1.NAV_STRAND_ID
FROM
TMP_LINKS TL1,
NT_LINK L1
WHERE
TL1.SEQUENCE_NUMBER=0
AND TL1.CNT=2
AND TL1.FUNCTIONAL_CLASS<5
AND TL1.LINK_ID=L1.LINK_ID) L1,
(SELECT
L2.LINK_PVID LINK_PVID_2,
TL2.FUNCTIONAL_CLASS FC_2,
TL2.NAV_STRAND_ID
FROM
TMP_LINKS TL2,
NT_LINK L2
WHERE
TL2.SEQUENCE_NUMBER=1
AND TL2.FUNCTIONAL_CLASS<5
AND TL2.LINK_ID=L2.LINK_ID) L2
WHERE
L2.NAV_STRAND_ID=L1.NAV_STRAND_ID
AND L2.FC_2<=L1.FC_1) X,
(SELECT
CONDITION_ID,
COUNT(*) MATCHES
FROM
NT_DATE_TIME_MODIFIER
GROUP BY
CONDITION_ID) DTM,
NT_CONDITION_MODIFIER CM
WHERE
X.CONDITION_ID=DTM.CONDITION_ID(+)
AND X.CONDITION_ID=CM.CONDITION_ID(+)
AND CM.TYPE(+)=1;

Without understanding your data model, the above could execute faster,
the same, or slower than the SQL that you posted. I am not sure that
this exercise will help you resolve the problem, but it may be another
way to look at the SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
I appreciate the help. I did run the above query and it seemed to chug
along pretty slowly (no results after 40 min). Since this problem is
basically solved with using the temp table, I am wondering more in
general. I mean, yes I can create temp table(s) and break things up to
get them to execute faster. Maybe that is the route to go, maybe it
looks amateurish. I've noticed if you get beyond around 6 tables,
Oracle is more iffy with picking the best join order . That is why I
was wondering if there is a hint or more elegant way to tell Oracle "do
this first" without creating temp tables. On less than 6 tables, I can
throw in a hint and usually get it to do what I want and it isn't
usually an issue.

Mike C wrote:
Something does not look right in the plan - the cost is very low
considering the length of time. Can you post the results of these
queries?
SELECT
OBJECT_NAME,
OBJECT_TYPE
FROM
DBA_OBJECTS
WHERE
OBJECT_NAME IN
('TMP_LINKS','NT_DATE_TIME_MODIFIER','NT_LINK','NT _CONDITION_MODIFIER');

SELECT
TABLE_NAME,
NUM_ROWS,
BLOCKS,
LAST_ANALYZED
FROM
DBA_TABLES
WHERE
TABLE_NAME IN
('TMP_LINKS','NT_DATE_TIME_MODIFIER','NT_LINK','NT _CONDITION_MODIFIER');

SELECT
NAME,
VALUE
FROM
V$PARAMETER
WHERE
NAME IN
('sort_area_size','sort_area_retained_size','optim izer_features_enable',
'db_file_multiblock_read_count',
'pga_aggregate_target','optimizer_index_caching',
'optimizer_index_cost_adj');

A 10046 trace at level 8 would be very helpful.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
Here you go. Hopefully you are viewing with a font that makes it look
readable.


OBJECT_NAME
OBJECT_TYPE

--------------------------------------------------------------------------------------------------------------------------------
------------------
NT_CONDITION_MODIFIER
TABLE

NT_LINK
TABLE

NT_DATE_TIME_MODIFIER
TABLE


3 rows selected


TABLE_NAME NUM_ROWS BLOCKS
LAST_ANALYZED
------------------------------ ----------------------
---------------------- -------------------------
NT_CONDITION_MODIFIER 3161690 9616
12-SEP-06
NT_DATE_TIME_MODIFIER 90634 700
28-OCT-06
NT_LINK 27836040 310309
13-SEP-06

3 rows selected


NAME VALUE








----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

db_file_multiblock_read_count 128








optimizer_features_enable 9.2.0








sort_area_size 65536








sort_area_retained_size 0








optimizer_index_cost_adj 100








optimizer_index_caching 0








pga_aggregate_target
8589934592








7 rows selected

Comments inline:

Mike C wrote:
You were having problems when you included the inline view, which
contained a reference to TMP_LINKS, but that object is not listed
above. This may be a clue. Can you verify that the SQL statement that
you executed against DBA_OBJECTS looks for an object named TMP_LINKS (I
might have typed the name wrong)?

TMP_LINKS is missing from this list also. If these tables have had a
lot of inserts, updates, or deletes, you might want to analyze the
tables and indexes - that may correct the costs seen in the plan.

Your pga_aggregate_target is about 8.5GB - do you have enough physical
memory in the server to allow this - you could be doing some serious
swapping to the operating system's swap file? The
db_file_multiblock_read_count seems very high. Depending on the DB
block size, a value of 8,16, or even 32 may be more realistic. If you
execute the following before processing your query, does it help:
ALTER SESSION SET SORT_AREA_SIZE=20000000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=5000000;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=8;

You will not want to leave the above in effect permanently. Keep me
posted what you find.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
TMP_LINKS was a table I created as part of this process. It does have 2
indexes, however there was nothing showing in dba_tables as
last_analyzed. I updated the statistics on this table and the thing
ran really fast. I was under the impression that when you created an
index it compilied the statistics for that index. That is obviously not
the case. Thanks for your help.


Here it is now:
TMP_LINKS 920043 6922 07-NOV-06

Mike C wrote:
That helps. When you saw that you updated the statistics and it is
really fast, are you saying that the problem has been corrected - that
the full query is running fast?

Regarding the pga_aggregate_target value of roughly 8.5GB, was that
amount of server RAM allocation intended. The server may be swapping
to the operating system's swap file, in addition to your query possibly
writing to and reading from the temporary tablespace.

If you are still having problems, execute the three ALTER SESSION
commands above and see if that helps. Without a 10046 trace at level
8, which would include the wait events both for your SQL statement and
the recursive SQL that is executed in the PL/SQL functions
(QT_UTIL.GET_SUBREGION), about the best that you can do is search for
obvious problems: review the initialization parameters
(db_file_multiblock_read_count is one such case) to make certain that
the values make sense, run a DBMS_XPLAN, keep an eye on V$SESSION_WAIT
while the SQL statement is exectuting, watch the values in V$SESSTAT
(join with V$STATNAME), watch the wait events in V$SESSION_EVENT, run a
Statspack report, and hope that something substantial is found.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

On 07.11.2006 23:14, Mike C wrote:
IMHO this is version dependend. It seems that Ora 10 does it
automatically but not older versions:

http://download-uk.oracle.com/docs/c...htm#sthref6625

Kind regards

robert

Charles Hooper wrote:

Yes, after adding the statistics on that table, the whole query ran
lightning fast. Just for the hell of it, I will try the alter sessions
and see if that imrpoves things any more.