WORTH GOLD
JReinhart, March 30, 2003 - 6:32 pm UTC
Still some doubt
John, March 31, 2003 - 1:37 am UTC
Thanks, Tom. Looking at this example,
1. Do you think ORACLE had ignored the hint for a HASH JOIN?
2. What is the meaning of "Bytes=2132"? The amount of data returned?
ops$tkyte@ORA920> select /*+ use_nl(b) */ * from a, b where a.id = b.id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4264)
1 0 HASH JOIN (Cost=5 Card=82 Bytes=4264)
2 1 TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes=2132)
3 1 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=82 Bytes=2132)
March 31, 2003 - 8:13 am UTC
<quote>
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join, using the specified table as the inner table.
</quote>
No, I don't think so, you just said to process B with NL, not A to B. In short -- insufficient data passed to the optimizer to make it do anything. I think the example in the book is "misleading" because they used ordered + use_nl to get the NL behavior
bytes = 2132 is the amount of data in bytes the optimizer expects to spit out of that phase of the query plan.
useful event
Connor McDonald, March 31, 2003 - 9:12 am UTC
event 10053 is a nice way of seeing 'where and when' the hints are applied as the optimizer canvases the various alternatives.
Cheers
Connor
Will a hint always be taken?
John Spencer, March 31, 2003 - 1:16 pm UTC
Tom:
In your reponse, you said "A "valid", "well formed" hint should be taken.". My understanding, from your site and others, was that a hint merely changed the cost of particular operations used by the optimizer from their default values. In essence, hints make the hinted operation appear cheaper than it otherwise would.
Given this, should the optimizer always use the hinted plan even if a cheaper plan could be found, or, is the cost adjustment implied by the hint so large that it guarantees the cheapest (not neccessarily the best) plan?
March 31, 2003 - 1:23 pm UTC
it removes certain plans from consideration as well.
A hint "should be taken", if it is possible and sensible to take it
driving_site
Arun Gupta, September 04, 2003 - 3:41 pm UTC
Tom
I have a need to join to four remote tables and one local table in the same query. Oracle recommends that driving_site hint can offer benefits if majority of data is on the remote site. Since driving_site takes only one table name as argument, how do I use this hint with four remote tables? I have tried various combinations, but Oracle seems to be ignoring the hint. Can I use table name alias in the argument to the hint?
Thanks
September 05, 2003 - 3:16 pm UTC
if all four remote tables are on the same site, you just pick any of them.
how do you know "we are ignoring it". do you have an example with autotrace for us to look at that shows you this?
Arun Gupta, September 06, 2003 - 1:36 am UTC
The explain plan showed that the hint was being ignored. The queries in remote serial changed from remote database tables to local database tables when hint was used. The reason was a function call in the select list like:
select col1, col2, pkg_util.fn_get_suffix(col3)...
from remote1, remote2, remote3, local1
where ....
Since the function is on the local database only, the driving_site hint was being ignored. When I removed the function call, the driving_site hint worked well. With a bit of code rewrite, the function call was eliminated. The improvement was all rows in 2 seconds as opposed to all rows in 11 seconds!!
There is another observation. I had queries like:
Q1(with driving site hint, remote and local tables joined)
UNION
Q2 (all tables joined are local, no hint)
The query Q2 is also treated as remote query by the optimizer. Is there a way to overcome this behaviour?
The second question I have is about hints in general. We use bind variables everywhere possible and the data is highly unevenly distributed. The optimizer is making row estimates which are way off the mark and too low (NUM_ROWS/NDV). The result is a choice of NLJ where a HJ would provide greater benefits. How can I avoid using hints in such situation?
Sorry, I do not have the explain plans right now. The e-mail system at work is partially working because of viruses.
Thanks...
September 06, 2003 - 8:55 am UTC
the driving site hint was not ignore in as much as "not really practical"
driving site says "do optimization over there" -- it is all or nothing.
you can create a remote view that operated on the three tables, and join to that.
As for the last thing, bind variable peeking in 9i and up should resolve that -- i have to assume you are on 8i still. all_rows optimization will probably get you what you want right now.
Arun Gupta, September 06, 2003 - 11:48 pm UTC
I agree. The hint being "ignored" was just a manner of expression, which came from reading somewhere that if Oracle doesn't find a practical method of doing what the hint requires, it just ignores the hint. Sorry for the misexpression.
I did try creating an inline view on remote tables and join to the local table and then apply the function, but no success. Could it be because the argument to function was a column from the remote table? I did as:
select col1, col2, pkg_util.fn_get_suffix(col3)...from
(select col1, col2, col3... from remote1, remote2, remote3
where...) rem_view,
local1
where local1.col_x= rem_view.col_y
We are on Oracle 9i. Is bind variable peeking achieved with CURSOR_SHARING=FORCE ?
Thanks...
September 07, 2003 - 6:05 pm UTC
try adding "and rownum > 0" to the inline view or using the NO_MERGE hint.
peeking is achieved simply by using oracle9i
Sorry wrong code
Arun Gupta, September 07, 2003 - 6:56 am UTC
Please read the code as:
select col1, col2, pkg_util.fn_get_suffix(col3)...from
(select /*+ driving_site(remote1) */
col1, col2, col3... from remote1, remote2, remote3
where...) rem_view,
local1
where local1.col_x= rem_view.col_y
DRIVING_SITE hint disabled when insert is used?
Peter Staunton, November 13, 2003 - 12:08 pm UTC
I've been looking at some query plans and notice that the DRIVING_SITE optimiser hint only appears to work when performing a simple SELECT, but is ignored if INSERT SELECT or CREATE TABLE AS SELECT is used.
For example:
INSERT INTO local_table(a,b)
SELECT /*+DRIVING_SITE(big)*/
big.a
,big.b
FROM big_table@remotedb big
, small_table sml
WHERE big.c=sml.c
From my knowledge of data volumes, it is much better if the join happens remotely rather than locally, but DRIVING_SITE doesn't appear to work here.
Is this a bug or a feature??
November 13, 2003 - 2:22 pm UTC
what happens with insert /*+ driving_site(big) */ into ....
DRIVING_SITE hint on INSERT
Peter Staunton, November 17, 2003 - 11:29 am UTC
Thanks for the suggestion.
I tried the hint on the Insert statement instead, even tried it on both Insert and Select, but in both cases the DRIVING_SITE hint is ignored and the join still takes place locally rather than remotely.
This is on 8.1.7.4 by the way, if that's relevant. Don't know if this behaviouor has changed in 9i.
add hint without modifying query
saha, December 09, 2003 - 3:45 pm UTC
You have discussed somewhere(9i) how to add hint without modifying a query. Please let me know the link
December 10, 2003 - 2:39 am UTC
you might be talking about stored outlines.
In 9i, you can use OEM to "edit" these stored outlines and change the plan.
If you have my book "Expert one on one Oracle" -- you can read the chapter that describes stored outlines or search this site for stored outlines (as well as otn.oracle.com -> documentation of course)
Please explain
Bruce, March 29, 2004 - 2:57 am UTC
Hi Tom,
What may the following hints do?
1)HWM_BROKERED
2)BYPASS_UJVC
3)CIV_GB and CUBE_GB
4)PIV_GB and PIV_SSF
Do you have any doc.links?
Please do reply.
Bye!
March 29, 2004 - 10:02 am UTC
search for bypass_ujvc on this site.
for the others -- nothing you can use, not meaningful to us (self included -- really). they are internal PQ (parallel query) hints.
hint
Phil, May 23, 2004 - 9:51 pm UTC
I have added an "index" hint to a query and the response has changed a great deal ... above you mention not using hints, which I understand, but using them to identify and fix an issue .... can you please help me identify the issue and a possible solution/reason
no hint and uses index 3 which is (VALUE_DATE,ACCOUNT_FK) LOCAL
SELECT t.*
FROM (SELECT trans.pk AS pk,
trans.account_owner_ref
AS accountownerreference,
trans.servicer_org_fk
AS accountservicer,
trans.owner_org_fk AS accountowner,
trans.account_fk
AS account_pk, trans.amount AS tranamount,
trans.isocurrency_fk
AS currency,
trans.transactionstatustype_fk AS status,
trans.transactiontype_fk
AS transactiontype,
trans.statement_fk AS stmtref,
trans.lastinst_hub_arrival_date_time
AS datetime,
trans.value_date
FROM logicaltransaction trans, useraccount ua
WHERE ua.account_fk = trans.account_fk
AND ua.rtnuser_fk = :b11
AND ua.revoked = 0
AND trans.lastinst_hub_arrival_date_time >=
TO_DATE (:b10, 'dd-mm-yyyy hh24:MI')
AND trans.transactionstatustype_fk IN (:b9,:b8,:b7,:b6,:b5,:b4,:b3,:b2)
AND trans.value_date >= TO_DATE (:b1, 'YYYYMM')
AND trans.value_date < ADD_MONTHS (TO_DATE (:b1, 'YYYYMM'), 1)
ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC) t
WHERE ROWNUM < 5000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5000 2.98 2.91 0 327556 0 4999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 2.98 2.92 0 327556 0 4999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 84 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4999 COUNT STOPKEY
4999 VIEW
4999 SORT ORDER BY STOPKEY
11092 FILTER
11092 TABLE ACCESS BY INDEX ROWID USERACCOUNT
168825 NESTED LOOPS
157732 TABLE ACCESS BY GLOBAL INDEX ROWID LOGICALTRANSACTION PARTITION: ROW LOCATION ROW LOCATION
182752 INDEX RANGE SCAN LOGICALTRANSACTION_IX3 (object id 33927)
11092 INDEX RANGE SCAN USERACCOUNT_IX1 (object id 33725)
********************************************************************************
with my index 2 hint which is account only
SELECT t.*
FROM (SELECT /*+ index(trans LOGICALTRANSACTION_IX2)*/ trans.pk AS pk,
trans.account_owner_ref
AS accountownerreference,
trans.servicer_org_fk
AS accountservicer,
trans.owner_org_fk AS accountowner,
trans.account_fk
AS account_pk, trans.amount AS tranamount,
trans.isocurrency_fk
AS currency,
trans.transactionstatustype_fk AS status,
trans.transactiontype_fk
AS transactiontype,
trans.statement_fk AS stmtref,
trans.lastinst_hub_arrival_date_time
AS datetime,
trans.value_date
FROM logicaltransaction trans, useraccount ua
WHERE ua.account_fk = trans.account_fk
AND ua.rtnuser_fk = :b11
AND ua.revoked = 0
AND trans.lastinst_hub_arrival_date_time >=
TO_DATE (:b10, 'dd-mm-yyyy hh24:MI')
AND trans.transactionstatustype_fk IN (:b9,:b8,:b7,:b6,:b5,:b4,:b3,:b2)
AND trans.value_date >= TO_DATE (:b1, 'YYYYMM')
AND trans.value_date < ADD_MONTHS (TO_DATE (:b1, 'YYYYMM'), 1)
ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC) t
WHERE ROWNUM < 5000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 5000 0.28 0.24 0 884 0 4999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 0.29 0.25 0 884 0 4999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 84 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4999 COUNT STOPKEY
4999 VIEW
4999 SORT ORDER BY STOPKEY
11092 FILTER
11092 TABLE ACCESS BY LOCAL INDEX ROWID LOGICALTRANSACTION
12593 NESTED LOOPS
60 TABLE ACCESS BY INDEX ROWID USERACCOUNT
60 INDEX RANGE SCAN USERACCOUNT_IX1 (object id 33725)
12532 PARTITION RANGE ITERATOR PARTITION: KEY KEY
12532 INDEX RANGE SCAN LOGICALTRANSACTION_IX2 PARTITION: KEY KEY (object id 33928)
********************************************************************************
many thanks
phil
May 24, 2004 - 7:26 am UTC
would need autotraces against same, in order to see assumptions used by optimizer (autotrace) vs reality (tkprof).
look for differences in the row source estimates and see if you cannot see why they might be so different (eg: missing or stale stats for example)
autotrace
philresham-computing.com, May 24, 2004 - 6:53 pm UTC
here is the autotrace, I updated the stats and we now have a full table scan , although this still seems to be less efficient than the index2 ... your thoughts are appreciated.
thanks
phil
SQL> @tracetxn.sql
SQL> SELECT t.*
2 FROM (SELECT trans.pk AS pk,
3 trans.account_owner_ref
4 AS accountownerreference,
5 trans.servicer_org_fk
6 AS accountservicer,
7 trans.owner_org_fk AS accountowner,
8 trans.account_fk
9 AS account_pk, trans.amount AS tranamount,
10 trans.isocurrency_fk
11 AS currency,
12 trans.transactionstatustype_fk AS status,
13 trans.transactiontype_fk
14 AS transactiontype,
15 trans.statement_fk AS stmtref,
16 trans.lastinst_hub_arrival_date_time
17 AS datetime,
18 trans.value_date
19 FROM logicaltransaction trans, useraccount ua
20 WHERE ua.account_fk = trans.account_fk
21 AND ua.rtnuser_fk = 2385
22 AND ua.revoked = 0
23 AND trans.lastinst_hub_arrival_date_time >=
24 TO_DATE ('18-05-2004 09:12', '
dd-mm-yyyy hh24:MI')
25 AND trans.transactionstatustype_fk IN (100,200,300,400,500,600,9
00,1000)
26 AND trans.value_date >= TO_DATE ('200405', 'YYYYMM')
27 AND trans.value_date < ADD_MONTHS (TO_DATE ('200405', 'YYYYMM'),
1)
28 ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC)
t
29 WHERE ROWNUM < 5000;
4999 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1566 Card=4999 Bytes
=13298879)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=1566 Card=71117 Bytes=13298879)
3 2 SORT (ORDER BY STOPKEY) (Cost=1566 Card=71117 Bytes=56
89360)
4 3 HASH JOIN (Cost=669 Card=71117 Bytes=5689360)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'USERACCOUNT' (Co
st=115 Card=542 Bytes=5420)
6 5 INDEX (RANGE SCAN) OF 'USERACCOUNT_IX1' (NON-UNI
QUE) (Cost=5 Card=1)
7 4 TABLE ACCESS (FULL) OF 'LOGICALTRANSACTION' (Cost=
552 Card=183710 Bytes=12859700)
Statistics
----------------------------------------------------------
2871 recursive calls
0 db block gets
6530 consistent gets
0 physical reads
0 redo size
234144 bytes sent via SQL*Net to client
4318 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
4999 rows processed
SQL>
SQL> SELECT t.*
2 FROM (SELECT /*+ index(trans LOGICALTRANSACTION_IX2)*/ trans.pk AS pk
,
3 trans.account_owner_ref
4 AS accountownerreference,
5 trans.servicer_org_fk
6 AS accountservicer,
7 trans.owner_org_fk AS accountowner,
8 trans.account_fk
9 AS account_pk, trans.amount AS tranamount,
10 trans.isocurrency_fk
11 AS currency,
12 trans.transactionstatustype_fk AS status,
13 trans.transactiontype_fk
14 AS transactiontype,
15 trans.statement_fk AS stmtref,
16 trans.lastinst_hub_arrival_date_time
17 AS datetime,
18 trans.value_date
19 FROM logicaltransaction trans, useraccount ua
20 WHERE ua.account_fk = trans.account_fk
21 AND ua.rtnuser_fk = 2385
22 AND ua.revoked = 0
23 AND trans.lastinst_hub_arrival_date_time >=
24 TO_DATE ('18-05-2004 09:12',
'dd-mm-yyyy hh24:MI')
25 AND trans.transactionstatustype_fk IN (100,200,300,400,500,600,
900,1000)
26 AND trans.value_date >= TO_DATE ('200405', 'YYYYMM')
27 AND trans.value_date < ADD_MONTHS (TO_DATE ('200405', 'YYYYMM')
, 1)
28 ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC)
t
29 WHERE ROWNUM < 5000;
4999 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2150 Card=4999 Bytes
=13298879)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=2150 Card=71117 Bytes=13298879)
3 2 SORT (ORDER BY STOPKEY) (Cost=2150 Card=71117 Bytes=56
89360)
4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOGICALTRANS
ACTION' (Cost=3 Card=131 Bytes=9170)
5 4 NESTED LOOPS (Cost=1253 Card=71117 Bytes=5689360)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'USERACCOUNT' (
Cost=115 Card=542 Bytes=5420)
7 6 INDEX (RANGE SCAN) OF 'USERACCOUNT_IX1' (NON-U
NIQUE) (Cost=5 Card=542)
8 5 INDEX (RANGE SCAN) OF 'LOGICALTRANSACTION_IX2' (
NON-UNIQUE)
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
827 consistent gets
0 physical reads
0 redo size
234144 bytes sent via SQL*Net to client
4318 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4999 rows processed
May 24, 2004 - 7:14 pm UTC
arg, now I have apples and bananas to compare!
need the reality (tkprof)
need the assumption (autotrace traceonly explain)
phil, May 24, 2004 - 7:20 pm UTC
sorry tom,
the fist post was from a tkprof, the second from an autotrace traceonly ....
I must be missing something ... is this not what you required to assist ?
thanks
phil
tkprof
phil, May 24, 2004 - 7:24 pm UTC
"apples and bananas" , I ran stats ... doh !
here is the tkprof ... using index 3 ( not full table scan like the trace is telling me ) ... confused.
SELECT t.*
FROM (SELECT trans.pk AS pk,
trans.account_owner_ref
AS accountownerreference,
trans.servicer_org_fk
AS accountservicer,
trans.owner_org_fk AS accountowner,
trans.account_fk
AS account_pk, trans.amount AS tranamount,
trans.isocurrency_fk
AS currency,
trans.transactionstatustype_fk AS status,
trans.transactiontype_fk
AS transactiontype,
trans.statement_fk AS stmtref,
trans.lastinst_hub_arrival_date_time
AS datetime,
trans.value_date
FROM logicaltransaction trans, useraccount ua
WHERE ua.account_fk = trans.account_fk
AND ua.rtnuser_fk = :b11
AND ua.revoked = 0
AND trans.lastinst_hub_arrival_date_time >=
TO_DATE (:b10, 'dd-mm-yyyy hh24:MI')
AND trans.transactionstatustype_fk IN (:b9,:b8,:b7,:b6,:b5,:b4,:b3,:b2)
AND trans.value_date >= TO_DATE (:b1, 'YYYYMM')
AND trans.value_date < ADD_MONTHS (TO_DATE (:b1, 'YYYYMM'), 1)
ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC) t
WHERE ROWNUM < 5000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5000 3.11 3.01 0 327452 0 4999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 3.11 3.02 0 327452 0 4999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 84 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4999 COUNT STOPKEY
4999 VIEW
4999 SORT ORDER BY STOPKEY
11092 FILTER
11092 TABLE ACCESS BY INDEX ROWID USERACCOUNT
168825 NESTED LOOPS
157732 PARTITION RANGE ITERATOR PARTITION: KEY KEY
157732 TABLE ACCESS BY LOCAL INDEX ROWID LOGICALTRANSACTION PARTITION: KEY KEY
182752 INDEX RANGE SCAN LOGICALTRANSACTION_IX3 PARTITION: KEY KEY (object id 33939)
11092 INDEX RANGE SCAN USERACCOUNT_IX1 (object id 33725)
********************************************************************************
and with the index ...
SELECT t.*
FROM (SELECT /*+ index(trans LOGICALTRANSACTION_IX2)*/ trans.pk AS pk,
trans.account_owner_ref
AS accountownerreference,
trans.servicer_org_fk
AS accountservicer,
trans.owner_org_fk AS accountowner,
trans.account_fk
AS account_pk, trans.amount AS tranamount,
trans.isocurrency_fk
AS currency,
trans.transactionstatustype_fk AS status,
trans.transactiontype_fk
AS transactiontype,
trans.statement_fk AS stmtref,
trans.lastinst_hub_arrival_date_time
AS datetime,
trans.value_date
FROM logicaltransaction trans, useraccount ua
WHERE ua.account_fk = trans.account_fk
AND ua.rtnuser_fk = :b11
AND ua.revoked = 0
AND trans.lastinst_hub_arrival_date_time >=
TO_DATE (:b10, 'dd-mm-yyyy hh24:MI')
AND trans.transactionstatustype_fk IN (:b9,:b8,:b7,:b6,:b5,:b4,:b3,:b2)
AND trans.value_date >= TO_DATE (:b1, 'YYYYMM')
AND trans.value_date < ADD_MONTHS (TO_DATE (:b1, 'YYYYMM'), 1)
ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC) t
WHERE ROWNUM < 5000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 5000 0.25 0.24 0 884 0 4999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5002 0.27 0.26 0 884 0 4999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 84 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
4999 COUNT STOPKEY
4999 VIEW
4999 SORT ORDER BY STOPKEY
11092 FILTER
11092 TABLE ACCESS BY LOCAL INDEX ROWID LOGICALTRANSACTION
12593 NESTED LOOPS
60 TABLE ACCESS BY INDEX ROWID USERACCOUNT
60 INDEX RANGE SCAN USERACCOUNT_IX1 (object id 33725)
12532 PARTITION RANGE ITERATOR PARTITION: KEY KEY
12532 INDEX RANGE SCAN LOGICALTRANSACTION_IX2 PARTITION: KEY KEY (object id 33928)
thanks
phil
May 24, 2004 - 11:41 pm UTC
ahh, run AUTOTRACE exactly with the same query as tkprof.
BIND VARIABLES and all please. no binds = definitely different plan.
in tkprof, you bound, in autotrace you are not. lets see "both"
that'll lead us down the path -- also, run your query with a couple of DIFFERENT binds, you might find for "bind=55" query runs "really fast", for binds = "56" binds run really slow -- for both cases (maybe opposites, one runs fast/other slow and vice versa).
Trace with binds
phil, May 25, 2004 - 6:15 pm UTC
autotrace with binds ( in comparison to the tkprof above ).... your thoughts are greatly appreciated. Different binds I will try.
n_utils@NOSTRODB> SELECT t.*
2 FROM (SELECT trans.pk AS pk,
3 trans.account_owner_ref
4 AS accountownerreference,
5 trans.servicer_org_fk
6 AS accountservicer,
7 trans.owner_org_fk AS accountowner,
8 trans.account_fk
9 AS account_pk, trans.amount AS tranamount,
10 trans.isocurrency_fk
11 AS currency,
12 trans.transactionstatustype_fk AS status,
13 trans.transactiontype_fk
14 AS transactiontype,
15 trans.statement_fk AS stmtref,
16 trans.lastinst_hub_arrival_date_time
17 AS datetime,
18 trans.value_date
19 FROM logicaltransaction trans, useraccount ua
20 WHERE ua.account_fk = trans.account_fk
21 AND ua.rtnuser_fk = :a
22 AND ua.revoked = 0
23 AND trans.lastinst_hub_arrival_date_time >=
24 TO_DATE (:b, 'dd-mm-yyyy hh24:
MI')
25 AND trans.transactionstatustype_fk IN (:p1,:p2,:p3,:p4,:p5,:p6,:
p7,:p8)
26 AND trans.value_date >= TO_DATE (:c, 'YYYYMM')
27 AND trans.value_date < ADD_MONTHS (TO_DATE (:c, 'YYYYMM'), 1)
28 ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC)
t
29 WHERE ROWNUM < 5000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=26 Bytes=486
2)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=38 Card=26 Bytes=4862)
3 2 SORT (ORDER BY STOPKEY) (Cost=38 Card=26 Bytes=2080)
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'USERACCOUNT' (Co
st=2 Card=1 Bytes=10)
6 5 NESTED LOOPS (Cost=22 Card=26 Bytes=2080)
7 6 PARTITION RANGE (ITERATOR)
8 7 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOGI
CALTRANSACTION' (Cost=15 Card=23 Bytes=1610)
9 8 INDEX (RANGE SCAN) OF 'LOGICALTRANSACTION_
IX3' (NON-UNIQUE) (Cost=5 Card=824)
10 6 INDEX (RANGE SCAN) OF 'USERACCOUNT_IX1' (NON-U
NIQUE)
n_utils@NOSTRODB>
n_utils@NOSTRODB>
n_utils@NOSTRODB> SELECT t.*
2 FROM (SELECT /*+ index(trans LOGICALTRANSACTION_IX2)*/ trans.pk AS pk,
3 trans.account_owner_ref
4 AS accountownerreference,
5 trans.servicer_org_fk
6 AS accountservicer,
7 trans.owner_org_fk AS accountowner,
8 trans.account_fk
9 AS account_pk, trans.amount AS tranamount,
10 trans.isocurrency_fk
11 AS currency,
12 trans.transactionstatustype_fk AS status,
13 trans.transactiontype_fk
14 AS transactiontype,
15 trans.statement_fk AS stmtref,
16 trans.lastinst_hub_arrival_date_time
17 AS datetime,
18 trans.value_date
19 FROM logicaltransaction trans, useraccount ua
20 WHERE ua.account_fk = trans.account_fk
21 AND ua.rtnuser_fk = :a
22 AND ua.revoked = 0
23 AND trans.lastinst_hub_arrival_date_time >=
24 TO_DATE (:b, 'dd-mm-yyyy hh24:
MI')
25 AND trans.transactionstatustype_fk IN (:p1,:p2,:p3,:p4,:p5,:p6,:
p7,:p8)
26 AND trans.value_date >= TO_DATE (:c, 'YYYYMM')
27 AND trans.value_date < ADD_MONTHS (TO_DATE (:c, 'YYYYMM'), 1)
28 ORDER BY trans.lastinst_hub_arrival_date_time DESC, trans.pk DESC)
t
29 WHERE ROWNUM < 5000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2569 Card=26 Bytes=4
862)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=2569 Card=26 Bytes=4862)
3 2 SORT (ORDER BY STOPKEY) (Cost=2569 Card=26 Bytes=2080)
4 3 FILTER
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOGICALTRA
NSACTION' (Cost=5 Card=1 Bytes=70)
6 5 NESTED LOOPS (Cost=2554 Card=26 Bytes=2080)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'USERACCOUNT'
(Cost=115 Card=542 Bytes=5420)
8 7 INDEX (RANGE SCAN) OF 'USERACCOUNT_IX1' (NON
-UNIQUE) (Cost=5 Card=542)
9 6 PARTITION RANGE (ITERATOR)
10 9 INDEX (RANGE SCAN) OF 'LOGICALTRANSACTION_IX
2' (NON-UNIQUE) (Cost=8 Card=131)
May 25, 2004 - 7:00 pm UTC
right here is where, given your inputs, it got it "wrong:
NESTED LOOPS (Cost=22 Card=26 Bytes=2080)
PARTITION RANGE (ITERATOR)
TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOGICALTRANSACTION'
(Cost=15 Card=23 Bytes=1610)
INDEX (RANGE SCAN) OF 'LOGICALTRANSACTION_IX3'
(NON-UNIQUE) (Cost=5 Card=824)
it was thinking "about 26 rows out of this", it got
168825 NESTED LOOPS
157732 PARTITION RANGE ITERATOR PARTITION: KEY KEY
157732 TABLE ACCESS BY LOCAL INDEX ROWID LOGICALTRANSACTION PARTITION
quite a few more. therein is the "cause". is trans.transactionstatustype_fk "skewed"?
phil, May 26, 2004 - 8:34 pm UTC
no, the field is not "skewed". even if I remove it from the where clause, and run the query with and without the hint , the hint is so much quiker as above - same plans.
I am clearly missing something here.
btw: any plans to come to Sydney, Aus ?
May 27, 2004 - 8:44 am UTC
are you positive the field is not skewed? it seems it would have to be -- else the statistics would be more accurate in the query plan. could you run a query like this:
ops$tkyte@ORA9IR2> select *
2 from (
3 select transactionstatustype_fk,
4 cnt,
5 total_cnt,
6 dense_rank() over (order by cnt) small_to_large,
7 dense_rank() over (order by cnt desc) large_to_small
8 from (
9 select object_type transactionstatustype_fk,
10 count(*) cnt,
11 count(*) over () total_cnt
12 from all_objects t
13 group by object_type
14 )
15 )
16 where small_to_large <= 5 or large_to_small <= 5
17 /
TRANSACTIONSTATUST CNT TOTAL_CNT SMALL_TO_LARGE LARGE_TO_SMALL
------------------ ---------- ---------- -------------- --------------
SYNONYM 12695 27 26 1
JAVA CLASS 10259 27 25 2
VIEW 2649 27 24 3
INDEX 1392 27 23 4
TABLE 1157 27 22 5
EVALUATION CONTEXT 11 27 5 22
DIRECTORY 8 27 4 23
INDEXTYPE 8 27 4 23
CONTEXT 6 27 3 24
MATERIALIZED VIEW 3 27 2 25
CONSUMER GROUP 2 27 1 26
11 rows selected.
using your key/table of course.
you were right
Phil, May 27, 2004 - 6:19 pm UTC
I think you were right again, should I be using SIZE AUTO when updating stats ?
sys@NOSTRODB> select *
2 from (
3 select transactionstatustype_fk,
4 cnt,
5 total_cnt,
6 dense_rank() over (order by cnt) small_to_large,
7 dense_rank() over (order by cnt desc) large_to_small
8 from (
9 select transactionstatustype_fk,
10 count(*) cnt,
11 count(*) over () total_cnt
12 from n_sys.logicaltransaction t
13 group by transactionstatustype_fk
14 )
15 )
16 where small_to_large <= 5 or large_to_small <= 5;
TRANSACTIONSTATUSTYPE_FK CNT TOTAL_CNT SMALL_TO_LARGE LARGE_TO_SMALL
------------------------ ---------- ---------- -------------- --------------
100 45316 5 5 1
500 1614 5 4 2
800 1200 5 3 3
600 601 5 2 4
900 190 5 1 5
May 27, 2004 - 8:56 pm UTC
you have only 5 values?
25 AND trans.transactionstatustype_fk IN (:p1,:p2,:p3,:p4,:p5,:p6,:
p7,:p8)
you in on more than that?
how do you gather stats?
phil, May 27, 2004 - 9:16 pm UTC
there are 8 potential values ( ie the user has selected 8 ), however there is only data currently for 5, could be 3 tomorrow as the status changes real time as the data come in.
I gather stats as below .. too simple ?
BEGIN dbms_stats.gather_schema_stats (ownname=>'N_SYS' ,estimate_percent=>30 ,degree=>1 ,granularity=>'ALL' ,cascade=>TRUE); END;
Phil, May 30, 2004 - 6:09 pm UTC
sorry to add further ... impatience and under pressure I guess.
Is my stats gathering too "simple" , are there other options that I should be using ?
thanks
May 31, 2004 - 12:41 pm UTC
no, stats look OK, problem is the optimizer is changing driving tables when you do the index hint.
Query has binds, "generic plan" is choosen. All rows optimized for. Here you have two tables that could drive. The set of predicates (that can be used to make the "join smaller" is)
WHERE ua.account_fk = trans.account_fk
AND ua.rtnuser_fk = :b11
AND ua.revoked = 0
AND trans.lastinst_hub_arrival_date_time >= TO_DATE (:b10, 'dd-mm-yyyy hh24:MI')
AND trans.transactionstatustype_fk IN (:b9,:b8,:b7,:b6,:b5,:b4,:b3,:b2)
AND trans.value_date >= TO_DATE (:b1, 'YYYYMM')
AND trans.value_date < ADD_MONTHS (TO_DATE (:b1, 'YYYYMM'), 1)
so, perhaps I've been looking at the wrong table -- is rtnuser_fk *skewed* and (this is important) how frequently will this particular query be executed?
phil, May 31, 2004 - 6:33 pm UTC
the frequency is alot ... it is the "flagship" query for the application and is used as the visual benchmark. I know 3 seconds above is quick but <1 second is quicker. The frequency boils down to all users everyday/allday
skewed ... it looks that way ( too much to paste ! ) would this then warrant histograms?
thanks for responding
phil
June 01, 2004 - 8:07 am UTC
well, if the rtnuser_fk column is massively skewed, I'd like to see a query output where
a) rtnuser_fk has minimal cardinality (like the 60 row example you ran)
b) rtnuser_fk has MAXIMAL cardinality
you'll find the plan you are trying to get to is only the best plan *sometimes*
We might want to start looking at the query more intensely too -- if this is the flagship query, we need to get it performing "subsecond".
The problem will be in the mismatch between the order by and the predicate on trans. Having trans.pk in there sort of messes it up, we cannot use an index purely to avoid a sort -- so, you need to get EVERY row pretty much and then sort and then just retreive the first 5,000.
What is the relationship between TRANS and UA -- 1:1 or 1:M -- both say "_fk" so it is hard to say.
How many PK's might you anticipate for each lastinst_hub_arrival_date_time?
Is the rownum part really in there in production? or are you just using it to test with (if so, stop, it really affects the outcome and your measurements)
Why index hint is not picked by Oracle?
A reader, June 10, 2004 - 9:40 am UTC
Tom,
I have the a query listed below. It seems Oracle does not pick up the index even I use the hint in the select statement. Could you please tell me why?
Thanks in advance for your help.
1 select /*+ index(outlet_dstservices OUTLET_DSTSERVICES_IX) */
2 od.corp_id, od.house_id, od.cust_num, od.outlet, cs.service_id , count(*)
3 from corp_services cs, outlet_dstservices od
4 where cs.corp_id = od.corp_id and cs.rate_code = od.rate_code
5* group by od.corp_id, od.house_id, od.cust_num, od.outlet, cs.service_id
SQL> /
2519374 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17061 Card=2676664 B
ytes=85653248)
1 0 SORT (GROUP BY) (Cost=17061 Card=2676664 Bytes=85653248)
2 1 HASH JOIN (Cost=1236 Card=2676664 Bytes=85653248)
3 2 INDEX (FAST FULL SCAN) OF 'CORP_SERVICES_PK' (UNIQUE)
(Cost=3 Card=3590 Bytes=61030)
4 2 TABLE ACCESS (FULL) OF 'OUTLET_DSTSERVICES' (Cost=1144
Card=3444621 Bytes=51669315)
Statistics
----------------------------------------------------------
0 recursive calls
21 db block gets
11916 consistent gets
24680 physical reads
0 redo size
110901192 bytes sent via SQL*Net to client
1848041 bytes received via SQL*Net from client
167960 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
2519374 rows processed
June 10, 2004 - 4:59 pm UTC
thanks goodness it did not!!! imagine how *slow* that would be.
but you really don't tell me enough info, like how the tables where created, what columns the indexes are on.
so -- just be happy it found it could not use the index and count your blessings. it would be painfully slow to do 2,519,374 nested loops operations.
Re: Why index hint is not picked by Oracle?
A reader, June 14, 2004 - 12:18 pm UTC
Tom,
Here is more info for that table:
create table OUTLET_DSTSERVICES (
CORP_ID NUMBER(10) not null,
HOUSE_ID VARCHAR2(6) not null,
CUST_NUM VARCHAR2(2) not null,
OUTLET NUMBER(5) not null,
RATE_CODE VARCHAR2(2) not null,
PROCESS_FLAG CHAR(1),
constraint OUTLET_DSTSERVICES_PK primary key (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE)
);
create index OUTLET_DSTSERVICES_IX on OUTLET_DSTSERVICES (
CORP_ID ASC,
RATE_CODE ASC
)
tablespace EMRGIDX;
I think using the index would be more fast than table scan for this case. Please explain why not? Is there any way to change query to accelerate the speed? - currently it runs about an hour!
Thanks
June 14, 2004 - 1:19 pm UTC
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>
why do you believe the index would be faster??
indexes are great for getting to A ROW.
however, you seem to want every single row.
when you go to a library -- do you ask for a book a page at a time? or do you ask for the book?
You might use an index (card catalog) to find a book -- but to get a book that you want all of the pages from -- you'd full scan it.
Can hint be used in alter table ...?
A reader, June 22, 2004 - 2:27 pm UTC
Tom,
Can a parallel hint be used in 'alter table ...'? I have a command below to add a primary key to a huge table, how to speed up the primary key creation process?
alter table T add constraint t_pk primary key (col1, col2, ...);
Thanks in advance for your help.
June 22, 2004 - 9:19 pm UTC
you can create the (unique) index in parallel, with nologging -- as fast as it can be done
and then.....
alter table big_table add constraint big_table_pk
primary key(id)
enable novalidate;
alter table big_table modify constraint big_table_pk validate;
the first alter table will put in place the constraint and start enforcing it. (very fast)
the second will take longer but doesn't require a full table lock for the entire statement -- just real quick at the start/stop of it (eg: pretty much an online thing). the validate is needed to get the NOT NULL part in place.
Re: Can hint be used in alter table ...?
A reader, June 23, 2004 - 9:23 am UTC
Tom,
Thanks a lot for the valuable advice. I have two more related questions.
1. How can I tell the DDL was processed by multiple servers in parallel? I queried V$PQ_SESSTAT and v$PQ_SYSSTAT dictionary views right after the parallel DDL, but found all 0's in the statistic values. Does that mean the DDL was not processed by parallel servers? I have the following parallel initial parameters in the init.ora file:
*.parallel_max_servers=20
*.parallel_min_servers=5
Did I do something wrong there?
2. In the 'alter table ... add constraint ...' statement below, should I use 'using index' clause to make Oracle to pickup the index that already created? And also should the constraint name 'OUTLET_DSTSERVICES_tmp_pk' be exact same as the index name?
The following are the SQL commands I executed in this order:
SQL> create index OUTLET_DSTSERVICES_tmp_pk on
2* outlet_dstservices_tmp (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE) parallel 12 nologging;
Index created.
Elapsed: 00:26:44.27
SQL> select * from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 0 0
Server Threads 0 0
Allocation Height 0 0
Allocation Width 0 0
Local Msgs Sent 0 0
Distr Msgs Sent 0 0
Local Msgs Recv'd 0 0
Distr Msgs Recv'd 0 0
11 rows selected.
Elapsed: 00:00:00.00
SQL> select * from v$pq_sysstat;
STATISTIC VALUE
------------------------------ ----------
Servers Busy 0
Servers Idle 5
Servers Highwater 0
Server Sessions 0
Servers Started 0
Servers Shutdown 0
Servers Cleaned Up 0
Queries Initiated 0
DML Initiated 0
DDL Initiated 0
DFO Trees 0
STATISTIC VALUE
------------------------------ ----------
Sessions Active 0
Local Msgs Sent 0
Distr Msgs Sent 0
Local Msgs Recv'd 0
Distr Msgs Recv'd 0
16 rows selected.
Elapsed: 00:00:00.00
SQL> alter table OUTLET_DSTSERVICES_TMP add constraint OUTLET_DSTSERVICES_tmp_pk primary key
2 (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE) using index enable novalidate;
Table altered.
Elapsed: 00:00:01.10
SQL> alter table OUTLET_DSTSERVICES_TMP modify constraint OUTLET_DSTSERVICES_tmp_pk validate;
June 23, 2004 - 9:40 am UTC
1) hints can be used in some ddl, like a create table as select -- the select can be hinted.
i'd have to actually see your statement in order to tell you if it even had a chance of going parallel.
2) the server will immediately "kidnap" existing indexes for primary/unique key constraints. you need not tell it.
Re: Can hint be used in alter table ...?
A reader, June 23, 2004 - 10:55 am UTC
Tom,
I already gave you the DDL statement in the previous response message. Okay, here is the one in case you didn't see it:
SQL> create index OUTLET_DSTSERVICES_tmp_pk on
2 outlet_dstservices_tmp (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE) parallel 12 nologging;
The table structure is as following:
SQL> desc OUTLET_DSTSERVICES_TMP
Name Null? Type
----------------------------------------- -------- ----------------------------
CORP_ID NOT NULL NUMBER(10)
HOUSE_ID NOT NULL VARCHAR2(6)
CUST_NUM NOT NULL VARCHAR2(2)
OUTLET NOT NULL NUMBER(5)
RATE_CODE NOT NULL VARCHAR2(2)
PROCESS_FLAG CHAR(1)
Please tell me HOW do you tell if the DDL is processed by parallel servers or not?
By the way, should I execute:
SQL> alter index OUTLET_DSTSERVICES_TMP_PK logging;
after the index is validated?
Thanks again.
June 23, 2004 - 11:29 am UTC
sorry -- but in "1" above I
a) saw no ddl
b) saw no hints
so I naturally didn't put 1 and 55 together to get 2. I see now that you had a ddl statement there, but still - no 'hints' in it. so I didn't associate it with anything.
from another session, while the statement is executing, you can use the v$px views:
ops$tkyte@ORA9IR2> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------ ---------- ----------
P000 IN USE 17 32449 18 1
P001 IN USE 18 32451 17 1
P002 IN USE 19 32453 20 1
P003 IN USE 20 32455 19 1
ops$tkyte@ORA9IR2> select * from v$px_session;
SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
-------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
55641A54 13 35358 13
55644970 18 1 13 35358 1 1 1 1 2 4
55644004 17 1 13 35358 1 1 1 2 2 4
55645C48 20 1 13 35358 1 1 2 1 2 4
556452DC 19 1 13 35358 1 1 2 2 2 4
ops$tkyte@ORA9IR2> select * from v$px_process_sysstat;
STATISTIC VALUE
------------------------------ ----------
Servers In Use 4
Servers Available 0
Servers Started 6
Servers Shutdown 2
Servers Highwater 4
Servers Cleaned Up 0
Server Sessions 4
Memory Chunks Allocated 5
Memory Chunks Freed 0
Memory Chunks Current 5
Memory Chunks HWM 5
Buffers Allocated 54
Buffers Freed 30
Buffers Current 24
Buffers HWM 24
15 rows selected.
that was the result of running:
big_table@ORA9IR2> create index bt_idx on big_table(id,owner,object_name,object_id,created,last_ddl_time,timestamp,status) parallel 4;
as for the last question -- only you can answer that. do you want the index to be nologging or not after the initial build? it'll affect subsequent rebuilds of the index only (not normal insert/update/delete operations, they are ALWAYS logged)
Re: Can hint be used in alter table ...?
A reader, June 23, 2004 - 12:08 pm UTC
Tom,
Thanks for your quick response. I did have 'hints' in the DDL - 'parallel 12 nologging' clause at the end - it crosses to the next line on the web page - you probably missed it. By the way, is this the only way to put 'hints' in DDL statements? The /*+ parallel (...) */ is only for DMLs - right?
Anyway, you mentioned only V$PX views can catch the parallel information. So are the V$PQ views obsolete for 9i?
One more question: does parallel DDL require to enable session's parallel DML as following:
SQL> alter session enable parallel dml;
June 23, 2004 - 1:06 pm UTC
that is not a hint
/*+ parallel */
that is a hint
create index i on t(x) parallel 12
is a "directive"...
v$px let you monitor from other sessions, they are what I use.
you do not need to enable parallel dml to do parallel DDL
Re: Can hint be used in alter table ...?
A reader, June 23, 2004 - 1:54 pm UTC
Tom,
The topic becomes very interesting now. You mean we can use a hint (/*+ parallel */) in a DDL? In Oracle performance and tuning guide and reference document, it says the optimizer hints can be only used in:
· A simple SELECT, UPDATE, or DELETE statement.
· A parent statement or subquery of a complex statement.
· A part of a compound query.
Can we use hints as following?
SQL> create /*+ parallel 12 */ index t_pk on t (col1, col2, Â…);
Regarding the hints in DDLs, I have more questions:
1. I do know hints for some particular DDLs, such as ‘create … as select …’, but I don’t have any information about hints in other DDLs, such as ‘create index …’. Could you please provide me more details and examples on this?
2. What are the differences between the hints and ‘directive’ in the DDL? In my case, do the following statements have the same approach?
SQL> create index OUTLET_DSTSERVICES_tmp_pk on outlet_dstservices_tmp (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE) parallel 12 nologging;
SQL> create /*+ parallel 12 */ index OUTLET_DSTSERVICES_tmp_pk on
outlet_dstservices_tmp (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE);
3. Why the ‘directive’ in my DDL cannot tell you if it even had a chance of going parallel? You insisted on seeing a ‘hint’ in DDL?
4. Even in the DDL example you provided, you didn’t use ‘hint’, but only ‘directive’:
big_table@ORA9IR2> create index bt_idx on
big_table(id,owner,object_name,object_id,created,last_ddl_time,timestamp,status) parallel 4;
Please forgive me if those questions make nonsense to you. Thanks again.
June 23, 2004 - 2:45 pm UTC
in as much as CREATE TABLE .... AS SELECT .... is DDL, yes, it can be.
the SELECT is hintable. but that is about it.
hints are hints, they will not cause a statement to fail if used incorrently or non-sensically.
directives are commands, they will cause a statement to fail if use incorrectly or improperly.
you cannot hint a create in that fashion -- create /*+ parallel 12 */ just becomes a comment.
3) i wasn't looking at that ddl statement at all in the context of answering your question. as stated -- i did not put it together with your question 1).
Re: Can hint be used in alter table ...?
A reader, June 23, 2004 - 5:40 pm UTC
Tom,
Thanks for all explanations. Now I am re-run the DDL, but it seems to me Oracle does not pick up multiple servers to run the job.
SQL> create index OUTLET_DSTSERVICES_tmp_pk on
2 outlet_dstservices_tmp (CORP_ID, HOUSE_ID, CUST_NUM, OUTLET, RATE_CODE) parallel 12 nologging;
During it's running, I opened another session to make a check:
SQL> select * from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------ ---------- ----------
P000 AVAILABLE 12 25471
P001 AVAILABLE 13 25473
P002 AVAILABLE 14 25475
P003 AVAILABLE 15 25477
P004 AVAILABLE 16 25479
SQL> select * from v$px_session;
no rows selected
SQL> select * from v$px_process_sysstat;
STATISTIC VALUE
------------------------------ ----------
Servers In Use 0
Servers Available 5
Servers Started 0
Servers Shutdown 0
Servers Highwater 0
Servers Cleaned Up 0
Server Sessions 0
Memory Chunks Allocated 4
Memory Chunks Freed 0
Memory Chunks Current 4
Memory Chunks HWM 4
STATISTIC VALUE
------------------------------ ----------
Buffers Allocated 0
Buffers Freed 0
Buffers Current 0
Buffers HWM 0
15 rows selected.
As you can see, 'Servers In Use = 0' and no records in v$px_session view. Could you please tell me what's wrong there? Thanks as always.
June 24, 2004 - 8:31 am UTC
show me output of
SQL> show parameter parallel
SQL> select * from v$version;
Re: Can hint be used in alter table ...?
A reader, June 24, 2004 - 8:43 am UTC
Tom,
Here is the information you need. Thanks.
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 20
parallel_min_percent integer 0
parallel_min_servers integer 5
parallel_server boolean FALSE
parallel_server_instances integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
June 24, 2004 - 10:17 am UTC
with same settings, same version, I cannot reproduce. I'll have to refer you to support -- for me, it immediately went totally parallel.
Index hint
A reader, November 03, 2004 - 11:07 am UTC
Tom,
At the beginning of this thread, you suggested not to use HINT except for 4 cases. Here I have some cases for you to review:
SQL> set autotrace traceonly
SQL> set timing on
1 SELECT media_content.content_id "contentId", media_content.title, media_storage.media_storage_id "storageId"
2 FROM media_content, media_storage
3 WHERE (media_storage.equipment_asset_id = 690001)
4 AND (media_storage.removed_flag='N')
5 AND (media_storage.storage_status_value IN ('1','2','3','4','5','7','8','9','10','11','12'))
6* AND (media_storage.content_id = media_content.content_id)
SQL> /
82 rows selected.
Elapsed: 00:00:03.38
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1572 Card=22433 Bytes=1144083)
1 0 HASH JOIN (Cost=1572 Card=22433 Bytes=1144083)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA_STORAGE' (Cost=825 Card=22536 Bytes=473256)
3 2 INDEX (RANGE SCAN) OF 'BIX_MEDIA_STORAGE' (NON-UNIQUE) (Cost=25 Card=1554971)
4 1 TABLE ACCESS (FULL) OF 'MEDIA_CONTENT' (Cost=631 Card=93
834 Bytes=2815020)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14193 consistent gets
6542 physical reads
0 redo size
4226 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
82 rows processed
1 SELECT /*+ index(media_content MEDIA_CONTENT_PK) */
2 media_content.content_id "contentId", media_content.title, media_storage.media_storage_id "storageId"
3 FROM media_content, media_storage
4 WHERE (media_storage.equipment_asset_id = 690001)
5 AND (media_storage.removed_flag='N')
6 AND (media_storage.storage_status_value IN ('1','2','3','4','5','7','8','9','10','11','12'))
7* AND (media_storage.content_id = media_content.content_id)
SQL> /
82 rows selected.
Elapsed: 00:00:01.14
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23361 Card=22433 Bytes=1144083)
1 0 NESTED LOOPS (Cost=23361 Card=22433 Bytes=1144083)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA_STORAGE' (Cost=825 Card=22536 Bytes=473256)
3 2 INDEX (RANGE SCAN) OF 'BIX_MEDIA_STORAGE' (NON-UNIQUE) (Cost=25 Card=1554971)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA_CONTENT' (Cost=1 Card=1 Bytes=30)
5 4 INDEX (UNIQUE SCAN) OF 'MEDIA_CONTENT_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7817 consistent gets
0 physical reads
0 redo size
4226 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
82 rows processed
My questions are:
1. From the statistics data showed above, the index hint forces Oracle use index on media_content table and yield a better performance. So why not use hints?
2. Why did not Oracle optimizer pick up the index instead of table scan on media_content table? The table scan costs 6542 physical reads, and index scan has 0 physical read. Does it mean the optimizer is not smart enough?
Thanks as always.
November 04, 2004 - 1:21 am UTC
tkprof's would be needed -- my question to you -- when you do the tkprof and you see the ACTUAL rows processed through each step of the plan -- do they match the ASSUMPTIONS made by the optimizer? if not -- we need to correct that (eg: stats are wrong or incomplete).
Re: Index hint
A reader, November 09, 2004 - 10:59 am UTC
Tom,
I was busy with other stuff in past few days. Now I come back to run the tkprof that you suggested. The result shows me the tkprof and autotrace generated the same execution plan. The following is the output from tkprof for the trace file:
SELECT media_content.content_id "contentId", media_content.title, media_storage.media_storage_id "storageId"
FROM media_content, media_storage
WHERE (media_storage.equipment_asset_id = 690001)
AND (media_storage.removed_flag='N')
AND (media_storage.storage_status_value IN ('1','2','3','4','5','7','8','9','10','11','12'))
AND (media_storage.content_id = media_content.content_id)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 7 0.51 0.50 6483 14464 0 82
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.51 0.52 6483 14464 0 82
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 136 (NC314)
Rows Row Source Operation
------- ---------------------------------------------------
82 HASH JOIN
87 TABLE ACCESS BY INDEX ROWID MEDIA_STORAGE
78339 INDEX RANGE SCAN BIX_MEDIA_STORAGE (object id 83534)
93845 TABLE ACCESS FULL MEDIA_CONTENT
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
82 HASH JOIN
87 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'MEDIA_STORAGE'
78339 INDEX (RANGE SCAN) OF 'BIX_MEDIA_STORAGE' (NON-UNIQUE)
93845 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'MEDIA_CONTENT'
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7 0.00 0.00
SQL*Net message from client 7 0.00 0.00
db file scattered read 452 0.00 0.11
db file sequential read 9 0.00 0.00
********************************************************************************
Do you think it is necessary here to add the index hint to enforce Oracle to use PK index instead of full table scan?
Thanks.
November 09, 2004 - 11:15 am UTC
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1572 Card=22433 Bytes=1144083)
1 0 HASH JOIN (Cost=1572 Card=22433 Bytes=1144083)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA_STORAGE' (Cost=825
Card=22536 Bytes=473256)
3 2 INDEX (RANGE SCAN) OF 'BIX_MEDIA_STORAGE' (NON-UNIQUE) (Cost=25
Card=1554971)
4 1 TABLE ACCESS (FULL) OF 'MEDIA_CONTENT' (Cost=631 Card=93
834 Bytes=2815020)
see the cards are all wrong, it is thinking 1,554,971 vs actual 78,339 and 22,536 vs actual of 87.
so, we need to fix/correct that assumption. how exactly do you gather statistics on that table/set of indexes (media_storage) and are they current?
Re: Index Hints
A reader, November 09, 2004 - 1:48 pm UTC
Tom,
We have a stored procedure to collect the statistics data every night at 1:00 AM. The following line is copied from the procedure for collecting media_content statistics data:
EXECUTE IMMEDIATE 'analyze table media_content compute statistics';
If we find anything wrong in the execution plan, how to correct it in general?
November 09, 2004 - 2:24 pm UTC
well, i'd stop using analyze.
start using dbms_stats.
alter the tables to be monitoring.
Use gather_schema_stats for "stale" objects only -- but before you do that, you would EXPORT (using dbms_stats) the existing statistics.
If something appears to have gone wrong, you can always put back the last set of stats -- to see if it was related to the statistics (useful to have the good and bad, now we can postmortem)
In this case, this data looks skewed -- the values you are asking for are "small" in cardinality -- but if you just took the number of distinct values for these columns and divided into the number of rows - the optimizer is thinking "big". Could be that histograms would be needed here to give the optimizer information -- so it knows "78k records, not 1.5 million" and so on.
consider the difference in cardinality estimates in the following example:
ops$tkyte@ORA9IR2> create table t
2 as
3 select 99 id, a.*
4 from all_objects a;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t set id = 1 where rownum <= 100;
100 rows updated.
ops$tkyte@ORA9IR2> create index t_idx on t(id);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain;
ops$tkyte@ORA9IR2> select * from t where id = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=13947 Bytes=1338912)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=39 Card=13947 Bytes=1338912)
ops$tkyte@ORA9IR2> select * from t where id = 99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=13947 Bytes=1338912)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=39 Card=13947 Bytes=1338912)
<b>here, all the optimizer knew was:
a) about 28,000 rows in table
b) two distinct values for id</b>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true, method_opt=>'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from t where id = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=9600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=100 Bytes=9600)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=100)
ops$tkyte@ORA9IR2> select * from t where id = 99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=27793 Bytes=2668128)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=39 Card=27793 Bytes=2668128)
ops$tkyte@ORA9IR2> set autotrace off
<b>Now, it had a better idea of what is really true about this table.</b>
You will do this in your TEST system right - you'll learn about histograms over there....
Re: Index hint
A reader, November 10, 2004 - 12:31 pm UTC
Tom,
It is very, very helpful information.
I have several more questions:
1. should I always use "method_opt=>'for all indexed columns size 254' " option in the dbms_stats.gather_table_stats procedure when the indexed clumn data gets skewed?
2. You said 'Use gather_schema_stats for "stale" objects only ". Why can I not use it for non-stale objects?
3. Oracle always suggest to use dbms_stats instead of 'analyze table' command. If I don't use the method_opt, are the following commands the same?
SQL> analyze table media_content compute statistics;
SQL> exec dbms_stats.gather_table_stats( user, 'MEDIA_CONTENT', cascade=>true);
From your examples, it seems they are the same.
Thank you very much for your advice.
November 10, 2004 - 2:32 pm UTC
1) always -- no. It is best to understand what histograms are, how the work (and frankly, the best explanation I've ever seen was by Jonathan Lewis -- a one hour session).
if there are less than 254 discrete values -- this'll get it right very precisely.
when there are more than 254 values -- well, it gets dicey. Say you have about 500 values and it is skewed in this sort of fashion:
ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA9IR2> insert into t
2 select mod(rownum,trunc(250/2))*2, rownum from all_objects;
30907 rows created.
ops$tkyte@ORA9IR2> insert into t
2 select rownum*2-1, rownum from all_objects where rownum <=250/2;
125 rows created.
ops$tkyte@ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2> select x, count(*) from t group by x order by x;
X COUNT(*)
---------- ----------
0 247
1 1
2 248
3 1
.....
248 247
249 1
250 rows selected.
ops$tkyte@ORA9IR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', method_opt=>'for all indexed columns size 254' );
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> select count( case when x = 100 then 1 end ),
2 count( case when x = 101 then 1 end )
3 from t;
COUNT(CASEWHENX=100THEN1END) COUNT(CASEWHENX=101THEN1END)
---------------------------- ----------------------------
247 1
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where x = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=244 Bytes=4148)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=8 Card=244 Bytes=4148)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=244)
ops$tkyte@ORA9IR2> select * from t where x = 101;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=17)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)
<b>so, got it, dead on -- but</b>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t;
Table truncated.
ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.
ops$tkyte@ORA9IR2> insert into t
2 select mod(rownum,trunc(300/2))*2, rownum from all_objects;
30907 rows created.
ops$tkyte@ORA9IR2> insert into t
2 select rownum*2-1, rownum from all_objects where rownum <=300/2;
150 rows created.
ops$tkyte@ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', method_opt=>'for all indexed columns size 254' );
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> select count( case when x = 100 then 1 end ),
2 count( case when x = 101 then 1 end )
3 from t;
COUNT(CASEWHENX=100THEN1END) COUNT(CASEWHENX=101THEN1END)
---------------------------- ----------------------------
206 1
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where x = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=245 Bytes=4165)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=8 Card=245 Bytes=4165)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=245)
ops$tkyte@ORA9IR2> select * from t where x = 101;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=203 Bytes=3451)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=7 Card=203 Bytes=3451)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=203)
<b>cannot possibly get it, there are not enough buckets -- it is (in this very very very
extreme, degenerate case) back to extrapolation.
remember, this is extreme here, I faked the data to get this outcome -- carefully
placing data in every other slot...</b>
2) gather stale is a method to avoid gathering stats on tables that probably don't need it -- non-stale objects are those that haven't changed much. it is the point of "monitoring" and "stale"
3) they are not the same, they come to different values for somethings, the CBO is built to expect the values from dbms_stats, dbms_stats has much more functionality (stale gathering only for example, parallel for exmaple, exporting/importing/setting for example)
and I've promised to never use analyze in examples anymore -- to do it "right"
Re: Index Hinds
A reader, November 10, 2004 - 7:05 pm UTC
Tom,
Very good example and explanation. Thanks a lot!
Is there an oracle doc where all SQL hints are listed?
A reader, November 22, 2004 - 10:58 am UTC
Tried searching through the usual suspects (sql reference,
Oracle reference) - could not find it.
Thank you.
November 22, 2004 - 3:23 pm UTC
thank you
A reader, November 22, 2004 - 5:59 pm UTC
RULE hint
Manjunath Kottur, January 11, 2005 - 11:44 am UTC
Hi Tom,
I need to understand the change in behaviour in the following 2 sql statements. Both are exactly identical but for:
1. The RULE hint has been removed from the 2nd statement.
2. HASH_JOINS were enabled for the 2nd statement.
The stats may not be reflecting this but the 1st statement was consuming 100% of the cpu and was never allowing the process to complete(atleast not before 12 hours when it was killed). There were latch free waits but when I knew the culprit was the RULE hint, I did not get a chance to proceed further.
My doubts are why is the number of rows visited drastically different, when there is no change in the way the table was accessed?
Also, we have in many places in our code set the hash_joins to enabled on a per session basis.
I have been trying to reverse this and make it enabled at system level but so far have not been successful. I know that starting 10g, this parameter has been made obsolute. Will the procedures error out when we move to 10g or Oracle will have some system to recognize such items and allow them to continue?
Thanks a lot for all the invaluable help your site has been for people like me.
Manjunath
INSERT INTO LYACCOUNTCONVERSIONTABLEITEM ( accountconversiontableitemkey,
name, description, accountkey, accountconversiontablekey )
SELECT /*+ RULE */ LYACCOUNTCONVTABLEITEM_SEQ.NEXTVAL, m1.glcode,
NVL(m1.gldesc,' '), a.accountkey,
c.accountconversiontablekey FROM LYFTAXACCTMAP m1, LYFTAXLEMAP m2,
LYACCOUNT a, LYACCOUNTCONVERSIONTABLE c,
LYACCOUNTCONVERSIONTABLEITEM I WHERE m2.skey =
m1.locatorkey AND a.accountcode = RPAD(m1.accountcode,
8,' ') AND a.enterprisekey = :b_enterprisekey AND
RTRIM(c.name) = RTRIM(m2.entitycode) AND
c.enterprisekey = :b_enterprisekey AND (I.name
= RPAD(m1.glcode,32,' ') OR RTRIM(I.name) IS NULL )
AND I.accountconversiontablekey(+) = c.accountconversiontablekey AND
m2.locatorcode IN (SELECT keychar FROM T#PARSETEMP WHERE jobkey = :b_jobkey)
AND m2.listkey = :b_producttype AND m1.officekey
= :b_officekey AND m1.glcode IS NOT NULL AND
NVL(RTRIM(m1.glcode), ' ') <> ' ' AND I.accountconversiontableitemkey
IS NULL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 16 0 0
Execute 1 0.86 0.86 66 42840 507 96
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.88 0.88 66 42856 507 96
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 109 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
96 SEQUENCE
96 NESTED LOOPS
96 NESTED LOOPS
5236 NESTED LOOPS
1 FILTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 VIEW
1 SORT UNIQUE
1 TABLE ACCESS FULL T#PARSETEMP
1 TABLE ACCESS BY INDEX ROWID LYACCOUNTCONVERSIONTABLE
1 INDEX RANGE SCAN FKLYACCOUNTCONVTBL1_IND (object id 245634)
0 TABLE ACCESS BY INDEX ROWID LYACCOUNTCONVERSIONTABLEITEM
0 INDEX RANGE SCAN FKLYACCTCONVTABLEITEM4_IND (object id 245637)
5236 TABLE ACCESS BY INDEX ROWID LYACCOUNT
5236 INDEX RANGE SCAN UKLYACCOUNT1 (object id 240696)
96 TABLE ACCESS BY INDEX ROWID LYFTAXACCTMAP
502656 INDEX RANGE SCAN UKLYFTAXACCTMAP01 (object id 241146)
96 TABLE ACCESS BY INDEX ROWID LYFTAXLEMAP
96 INDEX UNIQUE SCAN PKLYFTAXLEMAP (object id 241153)
INSERT INTO LYACCOUNTCONVERSIONTABLEITEM ( accountconversiontableitemkey,
name, description, accountkey, accountconversiontablekey )
SELECT LYACCOUNTCONVTABLEITEM_SEQ.NEXTVAL, m1.glcode,
NVL(m1.gldesc,' '), a.accountkey, c.accountconversiontablekey
FROM LYFTAXACCTMAP m1, LYFTAXLEMAP m2, LYACCOUNT a,
LYACCOUNTCONVERSIONTABLE c, LYACCOUNTCONVERSIONTABLEITEM I WHERE
m2.skey = m1.locatorkey AND a.accountcode
= RPAD(m1.accountcode,8,' ') AND a.enterprisekey
= :b_enterprisekey AND RTRIM(c.name) =
RTRIM(m2.entitycode) AND c.enterprisekey =
:b_enterprisekey AND (I.name = RPAD(m1.glcode,32,' ')
OR RTRIM(I.name) IS NULL ) AND
I.accountconversiontablekey(+) = c.accountconversiontablekey AND
m2.locatorcode IN (SELECT keychar FROM T#PARSETEMP WHERE jobkey = :b_jobkey)
AND m2.listkey = :b_producttype AND m1.officekey
= :b_officekey AND m1.glcode IS NOT NULL AND
NVL(RTRIM(m1.glcode), ' ') <> ' ' AND I.accountconversiontableitemkey
IS NULL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 9 511 330 96
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 9 511 330 96
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
96 SEQUENCE
96 NESTED LOOPS
96 FILTER
96 NESTED LOOPS OUTER
96 HASH JOIN SEMI
96 TABLE ACCESS BY INDEX ROWID LYACCOUNTCONVERSIONTABLE
193 NESTED LOOPS
96 NESTED LOOPS
96 TABLE ACCESS BY INDEX ROWID LYFTAXACCTMAP
96 INDEX RANGE SCAN UKLYFTAXACCTMAP01 (object id 241146)
96 TABLE ACCESS BY INDEX ROWID LYFTAXLEMAP
96 INDEX UNIQUE SCAN PKLYFTAXLEMAP (object id 241153)
96 INDEX RANGE SCAN FKLYACCOUNTCONVTBL1_IND (object id 245634)
1 TABLE ACCESS FULL T#PARSETEMP
0 TABLE ACCESS BY INDEX ROWID LYACCOUNTCONVERSIONTABLEITEM
0 INDEX RANGE SCAN FKLYACCTCONVTABLEITEM4_IND (object id 245637)
96 TABLE ACCESS BY INDEX ROWID LYACCOUNT
96 INDEX UNIQUE SCAN UKLYACCOUNT1 (object id 240696)
********************************************************************************
January 11, 2005 - 1:41 pm UTC
different plans = radically different execution profiles.
a bad plan = poor performance.
not much more to be said really. The RBO is rather "brain dead", it follows a very strict (and documented in the perf guide) set of simple RULES
Thinks like a hash join, the RBO isn't even vaguely aware they exist -- it cannot do them, it doesn't know they are available. Only the CBO knows that.
Thanks a lot..
Manjunath, January 11, 2005 - 6:10 pm UTC
Tom,
This part of the question remained unanswered. This is not about hints, so I am sorry for adding it in my question in the first place:
********************
Also, we have in many places in our code set the hash_joins to enabled on a per
session basis.
I have been trying to reverse this and make it enabled at system level but so
far have not been successful. I know that starting 10g, this parameter has been
made obsolute. Will the procedures error out when we move to 10g or Oracle will
have some system to recognize such items and allow them to continue?
*****************************
Thanks
Manjunath
January 11, 2005 - 7:27 pm UTC
"why haven't you been successful"
Is it possible to stimulate the desired execution path
Mohan, January 12, 2005 - 12:11 am UTC
I have two questions regarding Query optimization
1) I want to obtain an execution path in a rule based optimizer(without statistics) that is exactly same as the path obtained in a Cost based optimizer just by using hints.Is it possible?. Is there any tools that tells the hints used for a query in Cost Based optimizer?.
2) I am running a complex SQL statement involving 8 tables join of 5 small tables and 3 large tables. The small tables are used to obtain a short list of values. The explain plan looks very complex and confusing. I find it is doing FULL scan on large table and index scan on small tables. To reduce the complexity of the query I experimented two methods
(i) Use an In-line view of 5 small tables. This view returns a small set of values. Here we need to make sure that Oracle processes the in-line view seperately instead of merging the tables in the view the main query.
(ii) insert the small set of values from small tables into a global temporary table and join this table with 3 large tables. Now the explain plan shows only 4 tables.
I want to use method (i), but oracle merges tables in inline view with main query. Is there any Hint to tell Oracle to process in-line view independent of the main query.
Mohan
January 12, 2005 - 8:20 am UTC
1) possible, sure. easiest way would probably be to use query plan stability. use the CBO to "get the stored outline", then use that stored outline at runtime.
Else, it is all about you -- figuring out what magical set of hints would influence it to do the right thing.
at the end of the day, sort of rather self defeating, isn't it. I mean, "software knows what to do, but we don't want to let it do it"
2) insufficient data, 8 tables isn't horribly complex. I cannot comment really on the data supplied.
you can read about the no_merge hint, however I am concerned that YOU are trying to be a query optimizer and that is just wrong. Massive hinting is not a "good idea(tm)"
Reason for the hash join setting being false..
A reader, January 12, 2005 - 11:41 am UTC
Hi Tom,
Very briefly, the reason is the concern of making system level changes in a fairly well run environment. Considering the business implications, I dont disagree with them either.The mistake(setting it to false at system level) happened when we were still not in Production.
10g making this parameter obsolete, has changed the equation. We have many places in our code that enable the hash joins at session level.
I have tried to tell my higher ups the importance of these joins and they are also aware of examples in our own code where the processes run much faster(with hash joins enabled). But it is simply a matter of 'dont fix it, if it is not broke'.
Thanks
Manjunath
use_hash(a)
Reader, January 12, 2005 - 2:51 pm UTC
Can you tell what exactly does the use_hash hint do when it is used with only one table .Is it the driving table(outer table) ?
ie use_hash(a)
Thanks
January 12, 2005 - 4:02 pm UTC
it just says "try to use a hash join when joining to this table"
hash joins always try to figure out which one will be smaller to hash, it does not affect the driving order.
ops$tkyte@ORA9IR2> drop table t1;
Table dropped.
ops$tkyte@ORA9IR2> create table t1 as select * from all_users;
Table created.
ops$tkyte@ORA9IR2> drop table t2;
Table dropped.
ops$tkyte@ORA9IR2> create table t2 as select * from all_users;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100);
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ use_hash(t1) */ *
2 from t1, t2
3 where t1.user_id = t2.user_id
4 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1000000 Bytes=78000000)
1 0 HASH JOIN (Cost=41 Card=1000000 Bytes=78000000)
2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=17 Card=100 Bytes=3900)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=17 Card=1000000 Bytes=39000000)
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 100);
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ use_hash(t1) */ *
2 from t1, t2
3 where t1.user_id = t2.user_id
4 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1000000 Bytes=78000000)
1 0 HASH JOIN (Cost=41 Card=1000000 Bytes=78000000)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=17 Card=100 Bytes=3900)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=17 Card=1000000 Bytes=39000000)
ops$tkyte@ORA9IR2> set autotrace off
<b>note the flip flop -- due to size</b>
RE: DRIVING_SITE hint disabled when insert is used?
Eddy Mak, April 14, 2005 - 5:59 am UTC
November 13, 2003
Reviewer: Peter Staunton from Ireland
I've been looking at some query plans and notice that the DRIVING_SITE optimiser
hint only appears to work when performing a simple SELECT, but is ignored if
INSERT SELECT or CREATE TABLE AS SELECT is used.
For example:
INSERT INTO local_table(a,b)
SELECT /*+DRIVING_SITE(big)*/
big.a
,big.b
FROM big_table@remotedb big
, small_table sml
WHERE big.c=sml.c
From my knowledge of data volumes, it is much better if the join happens
remotely rather than locally, but DRIVING_SITE doesn't appear to work here.
Is this a bug or a feature??
Followup:
what happens with insert /*+ driving_site(big) */ into ....
--------------------------------------------------------
!!!!!HELP!!!!!
I have the same problem in Oracle 9i too.
When there was no "Insert into xxx", the explain plan showed the driving site was BIG. But when "Insert into xxx" was added, BIG became remote site. Any solution for that? Thank you.
April 14, 2005 - 8:05 am UTC
can I see the autotrace traceonly explains of the select and the insert/select.
RE: DRIVING_SITE hint disabled when insert is used?
Eddy Mak, April 14, 2005 - 10:20 pm UTC
Case 1: Without "Insert Into"
SQL> l
1 SELECT /*+ DRIVING_SITE(B) */
2 '1' MATCH_TYPE,
3 A.NO_MTC NO_MTC_4GROUPING,
4 A.NO_MTC NO_MTC,
5 NULL NO_REF,
6 MAX(B.ID_AGENT_PRN) AGENT_OR_PRN,
7 MAX(DECODE(A.TP_CPARTY, 'BROKER', B.TP_BS, NULL)) BROKER_BS,
8 MAX(DECODE(A.TP_CPARTY, 'CLIENT', B.TP_BS, NULL)) CLIENT_BS,
9 MAX(A.DT_VALUE) DT_VALUE,
10 MAX(A.DT_TRADE) DT_TRADE,
11 MAX(A.CD_SEC) CD_SEC
12 FROM DDST00_DSTL00 A,
13 DDST50_DDTX99 B,
14 RPTT02_BTTEMP_STLR008 C
15 WHERE
16 A.CD_OFFICE = ' FJ'
17 AND A.CD_CMPNY = '001'
18 AND A.DT_ASOF = to_date('02-MAR-2005')
19 AND A.TP_CXL = '0'
20 AND A.NO_MTC IS NOT NULL
21 AND A.TP_CPARTY <> 'INTERNAL'
22 AND A.ST_POST <> '2'
23 AND A.CD_OFFICE = B.CD_OFFICE
24 AND A.CD_CMPNY = B.CD_CMPNY
25 AND A.NO_REF = B.NO_REF
26 AND A.DT_TRADE = B.DT_TRADE
27 AND B.TP_CXL = '0'
28 AND B.NO_REF_CXL IS NULL
29 AND A.DT_VALUE <= C.DT_NEXT_N_BUSINESS
30 AND B.CD_MKT_EXCH = C.CD_MKT
31* GROUP BY A.NO_MTC
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=3328 Card=1 Bytes=130)
1 0 SORT (GROUP BY) (Cost=3328 Card=1 Bytes=130)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DDST50_DDTX99' (Cost=3 Card=1 Bytes=38)
3 2 NESTED LOOPS (Cost=3325 Card=1 Bytes=130)
4 3 NESTED LOOPS (Cost=3322 Card=1 Bytes=92)
5 4 TABLE ACCESS (FULL) OF 'DDST00_DSTL00' (Cost=3320 Card=1 Bytes=74)
6 4 REMOTE* (Cost=2 Card=4 Bytes=72)
7 3 INDEX (RANGE SCAN) OF 'DDSI50_DDTX99_90' (UNIQUE) (Cost=2 Card=1)
6 SERIAL_FROM_REMOTE SELECT "CD_MKT","DT_NEXT_N_BUSINESS" FROM "R
PTT02_BTTEMP_STLR008" "A1" WHERE :1<
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
Case 2: With "Insert Into"
SQL> l
1 INSERT /*+ DRIVING_SITE(B) */ INTO RPTT01_BTTEMP_STLR008
2 SELECT /*+ DRIVING_SITE(B) */
3 '1' MATCH_TYPE,
4 A.NO_MTC NO_MTC_4GROUPING,
5 A.NO_MTC NO_MTC,
6 NULL NO_REF,
7 MAX(B.ID_AGENT_PRN) AGENT_OR_PRN,
8 MAX(DECODE(A.TP_CPARTY, 'BROKER', B.TP_BS, NULL)) BROKER_BS,
9 MAX(DECODE(A.TP_CPARTY, 'CLIENT', B.TP_BS, NULL)) CLIENT_BS,
10 MAX(A.DT_VALUE) DT_VALUE,
11 MAX(A.DT_TRADE) DT_TRADE,
12 MAX(A.CD_SEC) CD_SEC
13 FROM DDST00_DSTL00 A,
14 DDST50_DDTX99 B,
15 RPTT02_BTTEMP_STLR008 C
16 WHERE
17 A.CD_OFFICE = ' FJ'
18 AND A.CD_CMPNY = '001'
19 AND A.DT_ASOF = to_date('02-MAR-2005')
20 AND A.TP_CXL = '0'
21 AND A.NO_MTC IS NOT NULL
22 AND A.TP_CPARTY <> 'INTERNAL'
23 AND A.ST_POST <> '2'
24 AND A.CD_OFFICE = B.CD_OFFICE
25 AND A.CD_CMPNY = B.CD_CMPNY
26 AND A.NO_REF = B.NO_REF
27 AND A.DT_TRADE = B.DT_TRADE
28 AND B.TP_CXL = '0'
29 AND B.NO_REF_CXL IS NULL
30 AND A.DT_VALUE <= C.DT_NEXT_N_BUSINESS
31 AND B.CD_MKT_EXCH = C.CD_MKT
32* GROUP BY A.NO_MTC
33 /
0 rows created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=57 Card=1 Bytes=152)
1 0 SORT (GROUP BY) (Cost=57 Card=1 Bytes=152)
2 1 HASH JOIN (Cost=54 Card=1 Bytes=152)
3 2 NESTED LOOPS (Cost=51 Card=1 Bytes=134)
4 3 REMOTE* (Cost=48 Card=1 Bytes=53)
5 3 REMOTE* (Cost=3 Card=1 Bytes=81)
6 2 TABLE ACCESS (FULL) OF 'RPTT02_BTTEMP_STLR008' (Cost=2 Card=82 Bytes=1476)
4 SERIAL_FROM_REMOTE SELECT /*+ */ "CD_OFFICE","CD_CMPNY","DT_TRA
DE","NO_REF","NO_REF_CXL","TP_CXL","
5 SERIAL_FROM_REMOTE SELECT "DT_ASOF","CD_OFFICE","CD_CMPNY","NO_
REF","DT_VALUE","DT_TRADE","TP_CXL",
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
862 bytes sent via SQL*Net to client
2105 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
Both RPTT01_BTTEMP_STLR008 and RPTT02_BTTEMP_STLR008 are local tables while both DDST00_DSTL00 and DDST50_DDTX99 are remote tables (ie. very big tables & linked by synonym).
In case 1, I can drive the Oracle to execute the SQL at where DDST00_DSTL00 and DDST50_DDTX99 installed.
In case 2, I want to do the same thing as case 1 does and transfer all the result back to local table RPTT01_BTTEMP_STLR008.
BUT ORACLE DOESN'T WORK!
April 15, 2005 - 8:28 am UTC
those cardinalities look teeny tiny don't they?
are statistics even close to reality? 82 rows doesn't seem painful to bring back, but if it were lots more than 82...
RE: DRIVING_SITE hint disabled when insert is used?
Eddy Mak, April 16, 2005 - 3:58 am UTC
In case 2, 82 rows are results processed in the local BTTEMP table. But it pulled all/most of the stuff of the VERY BIG DDSTxx remote tables to driving site (ie. where local BTTEMP tables hosted) that caused the entire SQL running very very slow.
Any idea to fix the driving site problem?
April 16, 2005 - 8:50 am UTC
4 3 REMOTE* (Cost=48 Card=1 Bytes=53)
5 3 REMOTE* (Cost=3 Card=1 Bytes=81)
are those numbers even remotely accurate.
are there any statistics on the remote site at all?
RE: DRIVING_SITE hint disabled when insert is used?
Eddy Mak, April 16, 2005 - 8:57 am UTC
I cannot gather the statistic from remote site because I am not DBA. I have no right to do so. But it took very long to run and insert the records. 2 hours (case 2)!!! but it took 15mins to select records (case 1)!!!
April 16, 2005 - 9:09 am UTC
*are the cardinalities even remotely close to reality* that is my question.
garbage in
garbage out
suggest you might either want to
a) have a view on the remote site that does the join and do a simple select from view
b) get proper statistics
c) use the select and fetch the data, inserting locally
d) use the sqlplus copy command.
The CBO - too clever for its own good
Angus Mctavish, April 30, 2005 - 5:54 am UTC
I have had a similar and very frustrating experience to Eddy Mak.
I have a packaged procedure that does two inserts into a remote table, selecting from a BIG local table:
This is done in a loop; vDate is passed to the procedure.
INSERT INTO Trans@HIST SELECT /*+ index (t ix_trans_sdate) */ *
FROM Trans t
WHERE t.Sdate BETWEEN
TO_DATE(TO_CHAR(vDate,'DD-MON-YYYY' ) || ' 00:00:00' , 'dd-mon-yyyy hh24:mi:ss' ) AND
TO_DATE(TO_CHAR(vDate,'DD-MON-YYYY' ) || ' 23:59:59' , 'dd-mon-yyyy hh24:mi:ss' )
AND t.TypeId = 10
AND NVL(t.AppName,' ') <> 'Merged data';
INSERT INTO Trans@HIST SELECT /*+ index (t ix_trans_sdate) */ *
FROM Trans t
WHERE t.Sdate BETWEEN
TO_DATE(TO_CHAR(vDate,'DD-MON-YYYY' ) || ' 00:00:00' , 'dd-mon-yyyy hh24:mi:ss' ) AND
TO_DATE(TO_CHAR(vDate,'DD-MON-YYYY' ) || ' 23:59:59' , 'dd-mon-yyyy hh24:mi:ss' )
AND t.TypeId = 20
AND NVL(t.AppName,' ') <> 'Merged data';
The hint is specified because without it Oracle chooses the index on TypeId, which is very inefficient in the circumstances.
When the first of these insert statements is executed Oracle does use the hint, but in the second it does not; the only difference in the 2 statements is the TypeId = ?. If the statements are run as simple selects, both use the specified hint; it is only when the 'insert into' is added that the hint is ignored in one instance but not the other. I tried various ways of getting Oracle to use the ix_trans_sdate index on the second statement and the only way I could get it to use it was to create a local copy of Trans@HIST and insert into that instead of inserting into the remote table. This meant that I then had to do a subsequent 'insert into Trans@HIST select * from TransLocal' followed by a 'delete from TransLocal'.
I would like the facility to allow me to force Oracle to take notice of a hint, i.e. make it a directive, as there are occasions when we know better what to do then Oracle does. The sad fact of the CBO is that it sometimes tries to be too clever for its own good and comes up with something far from ideal.
I know Tom to be a great fan of the CBO, but I regularly find occasions where a simple RULE hint solves a performance problem without the need to go messing about with other hints or regenerating statistics or trying to find some old set of statistics that fit the bill. At least with the rule based optimizer you knew where you were and you weren't suddenly going to hit some performance problem out of the blue because Oracle in all its wisdom suddenly hit upon a new execution plan. Regenerating statistics on a production system can be a nail-biting experience, not knowing what the new statistics have in store for us. Why regenerate them you might ask? Well, if the data is changing surely that is the point, but it is a risky business. If we have the CBO we should also have the facility to force an execution plan, regardless of what Oracle thinks is best.
April 30, 2005 - 11:02 am UTC
are you sure? for such a simple query -- that hint should be used if it could be.
can we see the tkprof's with the real queries the server saw and the row source operations performed (the real plan -- not an explain plan)
The CBO - too clever for its own good ... (cont.)
Angus McTavish, May 03, 2005 - 7:39 am UTC
I am certain that the hint was ignored in one case but not the other. I'm afraid I no longer have the tkprofs, but let me give you a little more detail.
The Trans table has about 60 million rows. There are about 10 million with a TypeId = 10 and about 40 million with a TypeId = 2. All are spread fairly evenly across dates with only a few thousand rows per day. You will see from the sql that I am processing only one day at a time, so it makes sense to use the index on the date. When I 'explained' the sql without a hint it always indicated that the index on TypeId would be used, which you will agree would have been highly inefficient, so I added the hint to get Oracle to use the index on the date column. The explain plan then indicated that it would use the index specified in the hint. So far, so good. However, when I came to actually RUN the sql, it used the hint for the first statement, but not for the second. The first statement typically took about ten seconds to run, and it was because the second one was taking forever that I investigated further and discovered that in that case it had decided to use the index on TypeId. As I mentioned in my first email I tried various other hints to get Oracle to do what I wanted but none of them worked, and the only thing I found that would get it to work as I wanted was to change the table being inserted into to be a local rather than a remote table. I find this very odd - I do not see that the whereabouts of the table being inserted into should have any bearing on the execution plan used to query the table being selected from. Anyway, the gist of my comments are that it would be a great help if we could force Oracle to use a hint (assuming that it is syntactically correct), because it would provide us with a level of certainty in execution plans that is currently lacking. I have always been very wary of the benefits of the CBO, and unfortunately sometimes it simply gets it plain wrong.
May 03, 2005 - 1:58 pm UTC
can you reproduce at all?
Hints in distributed CATS or insert/select really doesn't work in 8i
A reader, January 11, 2006 - 8:39 am UTC
-- select no hint
SELECT D.dlicint, D.dlinlis, D.dlidfin
FROM jstmpsup1 S, artdelis@tpvc D, artenlis@tpvc E, grslig@tpvc G
WHERE D.dlicint = S.sitcint
AND D.dlinlis = S.sitnlist
AND (TRUNC(D.dlidfin) >= TRUNC(sysdate)-1 OR D.dlidfin IS NULL)
AND E.elicmag = D.dlicmag
AND E.elinlis = D.dlinlis
AND G.grscmag = E.elicmag
AND G.grsngr = E.elingr
AND G.grsnmag = S.sitnpv
AND (TRUNC(G.grsdfin) >= TRUNC(sysdate)-1 OR G.grsdfin IS NULL);
COST CARDINALITY BYTES QUERY_PLAN
------------------------------------------
5361 483 60375 SELECT STATEMENT Cost = 5361
128 129527 10880268 HASH JOIN
88 15252 350796 REMOTE
24 7864 479704 HASH JOIN
17 508 14732 REMOTE
6 1548 49536 TABLE ACCESS FULL JSTMPSUP1
-- select hint
SELECT /*+ DRIVING_SITE (E) */ ...
COST CARDINALITY BYTES QUERY_PLAN
--------------------------------------------
6333 3358 251850 SELECT STATEMENT REMOTE Cost = 6333
6333 3358 251850 HASH JOIN
11 564 7896 TABLE ACCESS FULL GRSLIG
6320 666963 40684743 HASH JOIN
56 15252 167772 TABLE ACCESS FULL ARTENLIS
6196 1333926 66696300 NESTED LOOPS
4 1548 49536 REMOTE
4 8617093 155107674 TABLE ACCESS BY INDEX ROWID ARTDELIS
3 8617093 INDEX RANGE SCAN ARTDELIS_3
-- CATS hint
Create TABLE XXX AS
SELECT /*+ DRIVING_SITE (E) */ ...
COST CARDINALITY BYTES QUERY_PLAN
--------------------------------------------
5361 483 60375 CREATE TABLE STATEMENT Cost = 5361
LOAD AS SELECT
128 129527 10880268 HASH JOIN
88 15252 350796 REMOTE
24 7864 479704 HASH JOIN
17 508 14732 REMOTE
6 1548 49536 TABLE ACCESS FULL JSTMPSUP1
Hints and global temporary tables
Thierry Nestelhut, February 24, 2006 - 6:34 am UTC
Hello Tom,
Is it true that somes hints are not efficient on global temporary tables (eg, /*+ parallel (m,n) */) ?
Thank you.
Best regards./.
February 24, 2006 - 8:49 am UTC
it is true that some hints are not efficient in ANY CASE.
I believe however you mean "effective". Parallel query is not effective against a global temporary table since the parallel execution servers run in separate sessions and would not be able to 'see' the data you put into the global temporary table..
RE : Hints and global temporary tables
Thierry Nestelhut, February 24, 2006 - 10:58 am UTC
Thank you Tom,
You said :
"Parallel query is not effective against a global temporary table since the parallel execution servers run in separate
sessions".
I am not sure to understand what you mean. Does it mean that I have to commit before performing parallel query ? May be you speak about insert/update/delete and not select ?
I have performed an example with two tables:
t1 : standard table
t2 : global temporary table
SQL> create table t1 as select * from all_objects;
Table created.
SQL> delete from t1 where owner = 'SYS';
12520 rows deleted.
SQL> set autotrace on explain
SQL> select owner, count(*) from t1 group by owner;
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 4
DIC 469
OFS 36
OPS$MDW 441
OUTLN 7
PUBLIC 15928
SYSTEM 413
TEM 11
WMSYS 129
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T1'
SQL> select /*+parallel (t1,10) */ owner, count(*) from t1 group by owner;
OWNER COUNT(*)
------------------------------ ----------
TEM 11
PUBLIC 15928
DBSNMP 4
DIC 469
SYSTEM 413
OFS 36
OUTLN 7
OPS$MDW 441
WMSYS 129
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=32999 Bytes=
560983)
1 0 SORT* (GROUP BY) (Cost=11 Card=32999 Bytes=560983) :Q8001
2 1 SORT* (GROUP BY) (Cost=11 Card=32999 Bytes=560983) :Q8000
3 2 TABLE ACCESS* (FULL) OF 'T1' (Cost=5 Card=32999 Bytes= :Q8000
560983)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,COUNT(SYS_OP_CSR(
A1.C1,0)) FROM :Q8000 A1 GROUP BY A1
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C0 C0,SYS_OP_MSR(COU
NT(*)) C1 FROM (SELECT /*+ NO_EXPAND
3 PARALLEL_COMBINED_WITH_PARENT
=====================================
=> Same result with parallel hint
=====================================
SQL> create global temporary table t2 on commit preserve rows as select * from t1 where 1=0;
Table created.
SQL> insert into t2 select * from t1 where owner != 'PUBLIC';
1510 rows created.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T1'
SQL> select owner, count(*) from t2 group by owner;
OWNER COUNT(*)
------------------------------ ----------
DBSNMP 4
DIC 469
OFS 36
OPS$MDW 441
OUTLN 7
SYSTEM 413
TEM 11
WMSYS 129
8 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T2'
SQL> select /*+parallel (t2,10) */ owner, count(*) from t2 group by owner;
OWNER COUNT(*)
------------------------------ ----------
OPS$MDW 441
WMSYS 129
OUTLN 7
OFS 36
SYSTEM 413
DBSNMP 4
DIC 469
TEM 11
8 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=8168 Bytes=13
8856)
1 0 SORT* (GROUP BY) (Cost=6 Card=8168 Bytes=138856) :Q10001
2 1 SORT* (GROUP BY) (Cost=6 Card=8168 Bytes=138856) :Q10000
3 2 TABLE ACCESS* (FULL) OF 'T2' (Cost=2 Card=8168 Bytes=1 :Q10000
38856)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,COUNT(SYS_OP_CSR(
A1.C1,0)) FROM :Q10000 A1 GROUP BY A
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C0 C0,SYS_OP_MSR(COU
NT(*)) C1 FROM (SELECT /*+ NO_EXPAND
3 PARALLEL_COMBINED_WITH_PARENT
=====================================
=> Same result with parallel hint
=====================================
======================
=> Test with update
======================
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
SQL> update /*+ parallel (t1,10) */ t1 set owner = 'TOTO';
17438 rows updated.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel
SP2-0612: Error generating AUTOTRACE EXPLAIN report
SQL> commit;
Commit complete.
SQL> update /*+ parallel (t2,10) */ t2 set owner = 'TOTO';
1510 rows updated.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE (Cost=2 Card=8168 Bytes=13
8856)
1 0 UPDATE OF 'T2'
2 1 TABLE ACCESS* (FULL) OF 'T2' (Cost=2 Card=8168 Bytes=138 :Q17000
856)
2 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1.ROWID,A
1."OWNER" FROM "T2" PX_GRANULE(0, BL
==================================
==> parallel update seems to work
==================================
SQL> commit;
Commit complete.
SQL> select owner, count(*) from t1 group by owner;
OWNER COUNT(*)
------------------------------ ----------
TOTO 17438
SQL> select owner, count(*) from t2 group by owner;
OWNER COUNT(*)
------------------------------ ----------
TOTO 1510
================
==> Result OK
================
==> Apparently parallel queries work also on temporary tables. Where am I wrong ?
February 24, 2006 - 11:31 am UTC
another case to add to the "explain plan doesn't tell the truth, the whole truth and nothing but the truth"
big_table is a 1,000,000 row table. row width of 100 bytes give or take:
connect /
set echo on
set linesize 1000
/*
drop table t1;
drop table t2;
create table t1 as select * from big_table.big_table;
create global temporary table t2 as select * from big_table.big_table where 1=0;
exec dbms_stats.set_table_stats( user, 'T1', numrows => 10000000, numblks => 100000 );
exec dbms_stats.set_table_stats( user, 'T2', numrows => 10000000, numblks => 100000 );
*/
insert into t2 select * from t1;
set autotrace on
select /*+ PARALLEL( t1, 4 ) */ count(*) from t1;
select /*+ PARALLEL( t2, 4 ) */ count(*) from t2;
set autotrace off
while that is running from another session - query v$px_session.
You'll see px sessions for the first query, none for the second.
see
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#sthref7490 <code>
Alberto Dell'Era, February 24, 2006 - 1:03 pm UTC
Curious - even v$sql_plan "lies" with gtt/parallel in 9.2.0.6:
dellera@ORACLE9I> create global temporary table gtt (x int);
Table created.
dellera@ORACLE9I> select /*+ parallel (gtt,2) */ count(*) from gtt;
COUNT(*)
----------
0
dellera@ORACLE9I> col operation form a20
dellera@ORACLE9I> col options form a11
dellera@ORACLE9I> col other_tag form a40
dellera@ORACLE9I> select id, operation, options, other_tag
2 from v$sql_plan p, v$sql s
3 where p.address = s.address
4 and p.hash_value = s.hash_value
5 and p.child_number = s.child_number
6 and s.sql_text = 'select /*+ parallel (gtt,2) */ count(*) from gtt'
7 order by id;
ID OPERATION OPTIONS OTHER_TAG
---------- -------------------- ----------- -------------------------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 SORT AGGREGATE PARALLEL_TO_SERIAL
3 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT
Does this mean that the decision to run serially is made by the Sql Engine, not by the CBO - ie it's a decision made at "runtime" (=execution time) and not at "compile time" (= parse time) ?
(thanks in advance of course)
February 24, 2006 - 1:22 pm UTC
so does sql_trace.
the data in a temporary table just isn't available...
It is like it did a parallel degree 0 type of query - one process, the originating session is involved (if you trace it - there will even be parallel wait events, in the single process).
Alberto Dell'Era, February 24, 2006 - 2:38 pm UTC
> It is like it did a parallel degree 0 type of query
Oh! makes sense ... so the hint is not "ignored", as stated by the docs. A more careful definition would be that "the hint is honored, and then the parallel plan is silently downgraded to parallel 0 at execution time".
This shows that the distinction is not academic, since the plan can be quite different with or without the hint (9.2.0.6) :
dellera@ORACLE9I> create global temporary table gtt (x int, y varchar2(100)) on commit preserve rows;
Table created.
dellera@ORACLE9I> create index gtt_idx on gtt(x);
Index created.
dellera@ORACLE9I> insert into gtt (x,y) select rownum, rpad(rownum,100) from dual connect by level <= 100000;
100000 rows created.
dellera@ORACLE9I> alter session set optimizer_dynamic_sampling=2;
Session altered.
dellera@ORACLE9I> select /*+ parallel (gtt,2) parallel_index (gtt,2) */ count(y) from gtt where x < 50000;
COUNT(Y)
----------
49999
dellera@ORACLE9I> select /*+ noparallel(gtt) */ count(y) from gtt where x < 50000;
COUNT(Y)
----------
49999
From v$sql_plan:
select /*+ parallel (gtt,2) parallel_index (gtt,2) */ count(y) from gtt where x < 50000
--------------------------------------------------------------------------------
| Id | Operation |Name| Rows | Cost | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 6 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | SORT AGGREGATE | | 1 | | 29,00 | P->S | QC (RAND) |
|* 3 | TABLE ACCESS FULL | GTT| 55092 | 6 | 29,00 | PCWP | |
--------------------------------------------------------------------------------
select /*+ noparallel(gtt) */ count(y) from gtt where x < 50000
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 |
| 1 | SORT AGGREGATE | | 1 | 65 | |
| 2 | TABLE ACCESS BY INDEX ROWID| GTT | 55092 | 3497K| 10 |
|* 3 | INDEX RANGE SCAN | GTT_IDX | 74 | | 2 |
----------------------------------------------------------------------------
"different plan" doesn't imply "better or worse", of course ...
How can we collect the right the statistics ?
ZF_WU, March 02, 2006 - 3:41 am UTC
Tom,about your above dialog,I have a question.
your dialog:
Reviewer: A reader from Herndon, VA USA
Tom,
It is very, very helpful information.
I have several more questions:
1. should I always use "method_opt=>'for all indexed columns size 254' " option
in the dbms_stats.gather_table_stats procedure when the indexed clumn data gets
skewed?
Thank you very much for your advice.
your answer:
1) always -- no. It is best to understand what histograms are, how the work
(and frankly, the best explanation I've ever seen was by Jonathan Lewis -- a one
hour session).
if there are less than 254 discrete values -- this'll get it right very
precisely.
when there are more than 254 values -- well, it gets dicey. Say you have about
500 values and it is skewed in this sort of fashion:
----------------------------------------------------------------
About that I have a questions:
When run dbms_stats.gather_table_stats procedure,we often do not know the right value of column data size. How can we collect the right the statistics ?
Thanks!
March 02, 2006 - 12:14 pm UTC
...we do not know the right value of column data size...
I'm not sure what you meant by that? Do you mean "what are the considerations for deciding the number of buckets- the SIZE parameter".
It likely should either be
a) default (1)
b) 254
Update - Jonathans talk is now written in his book! Highly recommended.
Update on parallel query with global temporary tables
Thomas Kyte, April 07, 2006 - 11:14 am UTC
In discussing this with Jonathan Lewis - turns out my information was dated. Parallel Query does now work with global temporary tables.
He wrote:
...
Simple explanation for the thing about parallel
query and GTTs. - your notes are out of date,
8i doesn't support parallelism on GTTs
create global temporary table gtt1 (n1 number);
alter table gtt1 parallel (degree 2);
ORA-14451: unsupported feature with temporary table
Not a problem with my 9.2.0.6.
.....
and provided a nice test case that demonstrates this (the power of the test case :)
spool temp
truncate table gtt1;
drop table gtt1;
create global temporary table gtt1
on commit preserve rows
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
rownum id,
lpad(rownum,10,'0') small_vc,
rpad('x',80) padding
from
generator v1,
generator v2
where
rownum <= 1000000
;
select count(*) from gtt1;
exec dbms_stats.set_table_stats( user, 'GTT1', numrows => 10000000, numblks => 100000 );
select /*+ no_merge(iv) */
count(*)
from (
select /*+ parallel(gtt1, 4) */
id, count(*)
from gtt1
group by
id
having
count(*) > 1
) iv
;
select /*+ PARALLEL( gtt1, 4 ) */ count(*) from gtt1
.
break on dfo_number skip 1 on tq_id skip 1 on server_type
select
dfo_number,
tq_id,
server_type,
process,
num_rows,
bytes,
waits,
timeouts,
avg_latency,
instance
from
v$pq_tqstat
order by
dfo_number,
tq_id,
server_type desc
;
spool off
I've tried it out myself (always the skeptic) and sure enough, it demonstrated a parallel query.
DOES THE QUERY HINT WORKING AGAINST VIEWS
rajesh, July 18, 2006 - 9:20 am UTC
Hi Tom,
I have a very generic questions: For example, I have a table called study, a view called study_view, there is a index study_timedate_idx on base table study. I found that:
query 1 against base table:
select /*+ no_index(s study_timedate_idx) */ s.* from study s where
s.study_timedate > sysdate -7 order by s.study_timedate desc;
Query 2 against view:
select /*+ no_index(sv study_timedate_idx) */ sv.* from study_view sv where
sv.study_timedate > sysdate -7 order by sv.study_timedate desc;
In query 1, the hint is functional; but in query 2, the hint does not functional.
My question: Deos the query hint functional against views? or it only functional against on base table?
Thanks.
Rajesh
July 19, 2006 - 8:05 am UTC
A clarification
Rambabu, August 28, 2006 - 8:16 am UTC
Hello Tom,
I have gone through most of your explainations about hints. I know one should not use hints in the query. I was trying to generate a query by specfying a hint. Even after specfying a hint I observed Oracle was not using that hint. Are there any settings in the init.ora file which will ignore any of the hints provided? I wanted to check how much will be the cost if Oracle uses the other index. Can you let me how to force Oracle to use the index I specified?
I'm working on Oracle 9i. Optimizer mode is Choose.
Thanks
Rambabu
August 28, 2006 - 10:58 am UTC
you just were not specific enough OR the query could not possibly USE the hint for it would get the wrong answer if it did.
For example:
ops$tkyte%ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select /*+ INDEX( t t_idx ) */ *
2 from t
3 order by x;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=2132)
1 0 SORT (ORDER BY) (Cost=5 Card=82 Bytes=2132)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)
<b>The hint to use the index cannot be taken here - X permits nulls, the index on (x) therefore does not index every row in table T - if we used the index, we would MISS rows and get the wrong answer, however:</b>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter table t modify x NOT NULL;
Table altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select /*+ INDEX( t t_idx ) */ *
2 from t
3 order by x;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=82 Bytes=2132)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=82 Bytes=2132)
2 1 INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=26 Card=82)
ops$tkyte%ORA9IR2> set autotrace off
<b>Now the hint may be taken to heart - we can use it since X is now NOT NULL...</b>
so, you either
a) hinted us to do something impossible
b) hinted incorrectly
c) did not use sufficient hints - you were not specific enough.
Thanks !!!
Rambabu, August 29, 2006 - 4:17 am UTC
Hello Tom,
Thanks for the reply. In my last posting purposely I didn't send the query as it's too big and I had read the note in which you have clearly mentioned not to post huge queries.
I still remember couple of times I posted huge queries and you didn't reply.
Coming to your reply may be you are correct, one of the row had null value in one of the column. Though I could not confirm as we don't have privileges to update the column value to non-null and test it.
There's one question which may sound very vague but I'm facing this problem. Guys working on ReportNet ask me to tune or suggest a way to improve the performance of the query. Neither I have any privileges to view any of the V$ tables nor the DBA_ tables to check whether statistics are updated. Nor I've privileges to update any of the development data to prove my assumption. All I do is, run autotrace and check whether indexes are used or not?
Can you suggest what other areas to look to find out the problem? By the way I have gone through the performance tuning material but I get struck as I don't have any privileges.
Thanks
Rambabu
August 29, 2006 - 7:16 am UTC
if you have access to NOTHING
there it nothing I can do.
(if you see indexes being used that could mean one of three things:
a) it is really bad they are used
b) it is really good they are used
c) it really didn't matter one way or the other
indexes do NOT imply "fast = true")
What to look for...
Steve, August 30, 2006 - 8:10 am UTC
I have a query which has 3 unions and each union is a 5 table join. The explain plan shows that the CBO will use various indexes on the tables - some of which have millions of rows - and return 1 row - all well and good. Now this query takes 10 minutes or more but, if I hint each union with use_hash (<table>) then the result come back sub second. All stats are up to date and a tkprof of the query shows that disk reads are very high on the unhinted query which suggests that the indexes aren't being used? on the hinted query the disj reads are negligible. What should I be doing to track down why the optimiser chooses a less than performant execution path?
August 30, 2006 - 8:50 am UTC
take your autotrace traceonly explain (with the estimated cardinality guesses for each step) and compare it to the tkprof row source execution plan (which has the ACTUAL cardinalities for each step)
look for wide divergences.
see it there isn't some obvious reason why the cardinalities are very far off.
(suggesting you do that, not suggesting you post the autotrace for a 3 union 5 table join query and tkprof..)
Steve, August 30, 2006 - 7:23 pm UTC
The tkprof output doesn't show the plan. All I ahve is this:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 137.75 140.46 74462 3494270 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 137.76 140.47 74462 3494270 0 1
And from the autotrace (don't worry, not going to show the explain plan) I have:
Statistics
----------------------------------------------------------
320 recursive calls
0 db block gets
3494329 consistent gets
104701 physical reads
0 redo size
322 bytes sent via SQL*Net to client
3455 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Why is it that tkprof doesn't always show a plan? If I trace a session and run both hinted and non-hinted queries I get a plan for the hinted query but not the other.
August 30, 2006 - 7:42 pm UTC
you have to close the cursor.
get one where the cursor is closed and it'll show it.
Got it
Steve, August 30, 2006 - 8:20 pm UTC
Yes - thank you. Found the info. on another thread - exited sql*plus before tkprof'ing and it was there. Anyhow, the comparison revealed extremely skewed data in the index - used a no_index hint and the query returns faster than the use_hash hint. Now just need to figure out the best number of buckets for a histogram on the column and we should be sorted.
Once again - thanks.
SELECT /*+ FIRST_ROWS*/ ... INTO ...
Matthew, November 10, 2006 - 11:40 am UTC
Having moved from a company where very few hints were used to one where many SELECT... INTO... statements used the FIRST_ROWS hint, I have pondered which company had the correct approach. Therefore I would like to ask whether either of the following statements about SELECT... INTO... true?
1) The output from a SELECT... INTO... will only ever contain the first row of the result set. Therefore, one should typically use the FIRST_ROWS hint with such statements.
2) The output from a SELECT... INTO... will only ever contain the first row of the result set and the optimizer will recognize this fact. Therefore there is no point to using the FIRST_ROWS hint with such statements.
November 10, 2006 - 2:50 pm UTC
1) select intos fetch at least one and at most one row. I would not use first_rows, I would use first_rows(1)
first_rows is "deprecated" these days, first_rows(NN) is the way to go.
2) wrong, do this:
select 1 into x from all_objects;
it'll fetch two rows. By telling the optimizer "we expect one row only - first_rows(1)" you are giving it in fact valuable information, nothing wrong with it.
Matthew, November 13, 2006 - 7:12 am UTC
>I would not use
first_rows, I would use first_rows(1)
>first_rows is "deprecated" these days, first_rows(NN) is the way to go.
Unfortunately our clients are unwilling to upgrade from 8i. I suspect that their office is lit by oil lamps and that their CEO has a company horse and buggy.
Reader, July 14, 2009 - 2:08 pm UTC
Tom,
this is the query plan I get for my query. This is run only for one day. If I try to change this for getting data for 2 months, it took almost one hour
to return the results. Is there anything you can let me know by the query plan what I should do to improve the query?
Execution Plan
----------------------------------------------------------
Plan hash value: 3229805902
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2820 (1)| 00:00:40 | | |
| 1 | HASH GROUP BY | | 1 | 32 | 2820 (1)| 00:00:40 | | |
| 2 | VIEW | VIEW_TST_QUERYPLAN1 | 1 | 32 | 2819 (1)| 00:00:40 | | |
| 3 | HASH GROUP BY | | 1 | 2125 | 2819 (1)| 00:00:40 | | |
|* 4 | VIEW | | 1 | 2125 | 2818 (1)| 00:00:40 | | |
| 5 | WINDOW NOSORT | | 1 | 378 | 2818 (1)| 00:00:40 | | |
| 6 | SORT GROUP BY | | 1 | 378 | 2818 (1)| 00:00:40 | | |
|* 7 | HASH JOIN | | 1 | 378 | 2817 (1)| 00:00:40 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | edi_12345678 | 1 | 51 | 2 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | 1 | 347 | 2586 (1)| 00:00:37 | | |
|* 10 | HASH JOIN | | 1 | 296 | 2584 (1)| 00:00:37 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| udi_1234 | 1 | 60 | 2 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 1 | 271 | 2542 (1)| 00:00:36 | | |
|* 13 | HASH JOIN | | 4 | 844 | 2534 (1)| 00:00:36 | | |
| 14 | PARTITION RANGE SINGLE | | 462 | 44814 | 3 (0)| 00:00:01 | 29 | 29 |
|* 15 | TABLE ACCESS FULL | ct1_tstquery1 | 462 | 44814 | 3 (0)| 00:00:01 | 29 | 29 |
| 16 | PARTITION RANGE SINGLE | | 225K| 24M| 2530 (1)| 00:00:36 | 320 | 320 |
|* 17 | TABLE ACCESS FULL | ord_tstquery2123 | 225K| 24M| 2530 (1)| 00:00:36 | 320 | 320 |
|* 18 | INDEX RANGE SCAN | udi_1234_UIDX | 1 | | 1 (0)| 00:00:01 | | |
| 19 | PARTITION RANGE SINGLE | | 5841 | 142K| 42 (0)| 00:00:01 | 319 | 319 |
|* 20 | TABLE ACCESS FULL | symbols_history | 5841 | 142K| 42 (0)| 00:00:01 | 319 | 319 |
|* 21 | INDEX RANGE SCAN | edi_12345678_UIDX | 1 | | 1 (0)| 00:00:01 | | |
| 22 | PARTITION RANGE SINGLE | | 13875 | 420K| 230 (1)| 00:00:04 | 320 | 320 |
|* 23 | TABLE ACCESS FULL | ord1_tstquery212 | 13875 | 420K| 230 (1)| 00:00:04 | 320 | 320 |
-----------------------------------------------------------------------------------------------------------------------------
July 15, 2009 - 11:15 am UTC
what do you expect someone to do with a plan? I don't know your data. I don't know the volume of data. I don't know the query. I don't know the question.
Here is my answer based on what I know about your data: Maybe an hour is really fast, well done.
That could well be the answer. I don't know, it could be that you are processing 5tb of data.
NO_USE_NL (continuing education question)
J, November 24, 2010 - 11:42 am UTC
Hello Tom.
On 10.2: The documentation for NO_USE_NL states "However, in some cases tables can be joined only by using nested loops. In such cases, the optimizer ignores the hint for those tables."
In brief (if 'brief' is possible), what are the conditions where NLs cannot be avoided?
Best Regards,
- J
November 24, 2010 - 1:39 pm UTC
well, I guess if you simultaneously used
/*+ NO_USE_HASH NO_USE_NL NO_USE_MERGE */
:)
very contrived example here
ops$tkyte%ORA11GR2> create table t1 as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2> create index t1_idx on t1(object_id);
Index created.
ops$tkyte%ORA11GR2> create table t2 as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2> create index t2_idx on t2(object_id);
Index created.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 1000000, numblks => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1000000, numblks => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_column_stats( user, 'T1', 'OBJECT_ID', distcnt => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_column_stats( user, 'T2', 'OBJECT_ID', distcnt => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_index_stats( user, 'T1_IDX', CLSTFCT => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_index_stats( user, 'T2_IDX', CLSTFCT => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select *
2 from t1, t2
3 where t1.object_id= t2.object_id and t1.object_id = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 3586391610
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2000 | 3 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 10 | 2000 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 10 | 1000 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 1000 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_IDX | 10 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
<b>left to its own devices, merge join cartesian</b>
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OBJECT_ID"=55)
6 - access("T2"."OBJECT_ID"=55)
ops$tkyte%ORA11GR2> select /*+ no_use_nl(t1 t2) */ *
2 from t1, t2
3 where t1.object_id= t2.object_id and t1.object_id = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 968669903
<b> as it stands now, we can rule out the NL join and get a HASH join</b>
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2000 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 10 | 2000 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 1000 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_IDX | 10 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - access("T2"."OBJECT_ID"=55)
5 - access("T1"."OBJECT_ID"=55)
ops$tkyte%ORA11GR2> alter table t1 add constraint t1_pk primary key(object_id);
Table altered.
ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(object_id);
Table altered.
ops$tkyte%ORA11GR2> select /*+ no_use_nl(t1 t2) */ *
2 from t1, t2
3 where t1.object_id= t2.object_id and t1.object_id = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 229192751
<b>but if the optimizer sees that both columns are unique - it blows off the NO_USE_NL hint</b>
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 0 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 200 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 100 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_IDX | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."OBJECT_ID"=55)
5 - access("T1"."OBJECT_ID"=55)
ops$tkyte%ORA11GR2> set autotrace off
A reader, November 24, 2010 - 4:10 pm UTC
Hello Sir,
First you set stats for table t2 as below indicating total 1,000,000 rows
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 1000000, numblks => 100000 );
than you set stats for column object_id for table t2 as below which indicate 100,000 distinct values for column object_id
ops$tkyte%ORA11GR2> exec dbms_stats.set_column_stats( user, 'T2', 'OBJECT_ID', distcnt => 100000 );
and then later you set primary key on t2.object_id
ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(object_id);
The way you mimic the stats there is 10 rows for each value of object_id
who could its possible to define primary key? since total no of rows in t2 <> total distinct values for column t2.object_id ?
don't you think oracle complains when primary key is define?
could you please explain this behaviour?
Thanks
November 25, 2010 - 8:42 am UTC
... don't you think oracle complains when primary key is define?
...
of course it doesn't complain, statistics are gathered at some point in time and are static whereas the data is dynamic and constantly changing (eg: data does not look like its statistics for very long in an active system - they are never "the same")
To the optimizer - it just looks like the column stats "must be out of date, I see there are 1,000,000 rows in the table, and someone it trying to tell me there are 100,000 distinct values of object_id - but the primary key constraint is telling me 'someone is lying to me' - there are not 1,000,000 rows or there are more than 100,000 distinct values or both"
"parallel degree 0 type of query"?
Duke Ganote, January 05, 2011 - 8:25 am UTC
I'm reading the discussion between you and Alberto Dell'era above:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8912905298920#57844621000792
and don't understand what degree zero implies.
I don't find anything obvious in the documentation.
http://blogs.oracle.com/datawarehousing/2010/02/auto_dop_and_parallel_statemen.html
http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/parallel002.htm#insertedID3
Yet it obviously exists:
<code>
select degree, count(*)
from dba_indexes
group by degree
order by 1;
DEGREE COUNT(*)
---------- --------------------
0 555
1 5,207
8 95
DEFAULT 109
What does it mean?</code>
January 05, 2011 - 8:44 am UTC
it is no degree - serial, no parallel
and 1?
Duke Ganote, January 05, 2011 - 8:48 am UTC
And that's different that a degree of one?
January 05, 2011 - 9:01 am UTC
nothing really - check out the 'indexes' that have degree zero, they should be lob segment indexes (indexes you don't really ever 'touch') - they are special.
I believe in that discussion we were using the term 'degree 0' figuratively to represent "not being done in parallel"
sure 'nuff! thanks!
Duke Ganote, January 05, 2011 - 9:28 am UTC
select index_type, count(*)
from dba_indexes
where degree = '0'
group by index_type
order by 2
INDEX_TYPE COUNT(*)
--------------------------- --------------------
LOB 555
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:378418239571#21087406927531 "index on a lob column" where you wrote:
<quote>
An out of line lob (over 4,000 bytes of lob data is always out of line) consists of a lob pointer (think "primary key"), a series lob index entries that point to the lob chunks, and the lob chunks.
Think of a lob as a master/detail. master has primary key (lob locator), detail has lob locator + sequence (chunk number). we derefernece the lob locator -- via the lob index to find the chunks of the lob out there in lob land.
</quote>
query optimizing
Crispo, August 19, 2011 - 3:27 pm UTC
Hi Tom,
I have the following simplified tables:
SET ECHO ON
column NAME format A10
column ID format 9999
column ENCOUNTER_ID format 9999
column START_EVENT format A10
column END_EVENT format A10
drop table patient_name;
drop table patient_details;
create table patient_name (id number(10), name varchar2(30) );
create table patient_details (patient_id number(10), encounter_id number(10), encounter_date date, event_id number(10), last_in_event_id number(10), event_type number(1) );
insert into patient_name values (1, 'IAN');
insert into patient_name values (2, 'ANDREW');
insert into patient_name values (3, 'ROB');
insert into patient_name values (4, 'JOHN');
insert into patient_name values (5, 'DAVE');
insert into patient_details values (1, 234, to_date('01/01/2010', 'dd/mm/yyyy'), 10, null, 1);
insert into patient_details values (1, 234, to_date('15/01/2010', 'dd/mm/yyyy'), 11, 10, 2);
insert into patient_details values (1, 234, to_date('23/01/2010', 'dd/mm/yyyy'), 23, null, 1);
insert into patient_details values (1, 234, to_date('27/01/2010', 'dd/mm/yyyy'), 24, 23, 2);
insert into patient_details values (2, 555, to_date('13/03/2010', 'dd/mm/yyyy'), 31, 30, 2);
insert into patient_details values (3, 601, to_date('06/03/2010', 'dd/mm/yyyy'), 42, null, 1);
insert into patient_details values (4, 203, to_date('08/04/2010', 'dd/mm/yyyy'), 21, null, 1);
insert into patient_details values (4, 203, to_date('23/04/2010', 'dd/mm/yyyy'), 22, 21, 2);
insert into patient_details values (4, 306, to_date('04/07/2010', 'dd/mm/yyyy'), 45, null, 1);
insert into patient_details values (4, 306, to_date('28/07/2010', 'dd/mm/yyyy'), 46, 45, 2);
commit;
SQL>
SQL> select * from patient_name;
ID NAME
----- ----------
1 IAN
2 ANDREW
3 ROB
4 JOHN
5 DAVE
SQL>
SQL> select * from patient_details ;
PATIENT_ID ENCOUNTER_ID ENCOUNTER EVENT_ID LAST_IN_EVENT_ID EVENT_TYPE
---------- ------------ --------- ---------- ---------------- ----------
1 234 01-JAN-10 10 1
1 234 15-JAN-10 11 10 2
1 234 23-JAN-10 23 1
1 234 27-JAN-10 24 23 2
2 555 13-MAR-10 31 30 2
3 601 06-MAR-10 42 1
4 203 08-APR-10 21 1
4 203 23-APR-10 22 21 2
4 306 04-JUL-10 45 1
4 306 28-JUL-10 46 45 2
10 rows selected.
event_type = 1 denotes the start of an encounter
event_type = 2 denotes the end of an encounter
The requirement I have is to group the data, per patient, encounter, and related events.
If both a starting row and ending row exist, i display the start and end dates.
If neither row exist for a patient, i don't display the patient row.
If only a starting row exists, i leave the end date null.
If only an ending row exists, i populate the start date with the end date.
This query satisfies the conditions, but does not scale well. When run against production tables it is taking more than 30mins to insert (Append) the 1.7m rows from this query
Can i rewrite the query to be more effecient? It's doing 3 separate FTS but on unindexed tables this seems unavoidable. I was wondering if USE_HASH hint would help, but it's already doing hash joins. Does this mean that the smallest table is already automatically loaded into memory?
Thanks
SQL>
SQL> select n.name, c.patient_id, c.encounter_id, c.start_date, c.end_date
2 from patient_name n
3 ,(select coalesce(s.patient_id, e.patient_id) patient_id
4 ,coalesce(s.encounter_id, e.encounter_id) encounter_id
5 ,coalesce(s.encounter_date, e.encounter_date) start_date
6 ,e.encounter_date end_date
7 from (select * from patient_details where event_type = 1) s
8 full outer join (select * from patient_details where event_type = 2) e on (s.patient_id = e.patient_id and s.encounter_id = e.encounter_id and s.event_id = e.last_in_event_id)
9 ) c
10 where n.id = c.patient_id
11 order by c.patient_id, c.encounter_id;
NAME PATIENT_ID ENCOUNTER_ID START_DAT END_DATE
---------- ---------- ------------ --------- ---------
IAN 1 234 01-JAN-10 15-JAN-10
IAN 1 234 23-JAN-10 27-JAN-10
ANDREW 2 555 13-MAR-10 13-MAR-10
ROB 3 601 06-MAR-10
JOHN 4 203 08-APR-10 23-APR-10
JOHN 4 306 04-JUL-10 28-JUL-10
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3005938957
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 500 | 41 (5)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 500 | 41 (5)| 00:00:01 |
|* 2 | HASH JOIN | | 5 | 500 | 40 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL | PATIENT_NAME | 5 | 150 | 13 (0)| 00:00:01 |
| 4 | VIEW | VW_FOJ_0 | 5 | 350 | 27 (4)| 00:00:01 |
|* 5 | HASH JOIN FULL OUTER| | 5 | 480 | 27 (4)| 00:00:01 |
| 6 | VIEW | | 5 | 240 | 13 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PATIENT_DETAILS | 5 | 305 | 13 (0)| 00:00:01 |
| 8 | VIEW | | 5 | 240 | 13 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PATIENT_DETAILS | 5 | 305 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"."ID"=COALESCE("S"."PATIENT_ID","E"."PATIENT_ID"))
5 - access("S"."PATIENT_ID"="E"."PATIENT_ID" AND
"S"."ENCOUNTER_ID"="E"."ENCOUNTER_ID" AND "S"."EVENT_ID"="E"."LAST_IN_EVENT_ID")
7 - filter("EVENT_TYPE"=1)
9 - filter("EVENT_TYPE"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
1030 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
August 30, 2011 - 1:28 pm UTC
ops$tkyte%ORA11GR2> select a.name, b.*
2 from patient_name a,
3 (
4 select patient_id, encounter_id, nvl(last_in_event_id,event_id) new_event_id,
5 nvl( max(decode( event_type, 1, encounter_date )),
6 max(decode( event_type, 2, encounter_date ))) start_date,
7 max(decode( event_type, 2, encounter_date )) end_date
8 from patient_details
9 group by patient_id, encounter_id, nvl(last_in_event_id,event_id)
10 ) b
11 where a.id = b.patient_id
12 order by b.patient_id, b.encounter_id, b.start_date
13 /
NAME PATIENT_ID ENCOUNTER_ID NEW_EVENT_ID START_DAT END_DATE
-------- ---------- ------------ ------------ --------- ---------
IAN 1 234 10 01-JAN-10 15-JAN-10
IAN 1 234 23 23-JAN-10 27-JAN-10
ANDREW 2 555 30 13-MAR-10 13-MAR-10
ROB 3 601 42 06-MAR-10
JOHN 4 203 21 08-APR-10 23-APR-10
JOHN 4 306 45 04-JUL-10 28-JUL-10
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2271366207
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 870 | 9 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 870 | 9 (34)| 00:00:01 |
|* 2 | HASH JOIN | | 10 | 870 | 8 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | PATIENT_NAME | 5 | 150 | 3 (0)| 00:00:01 |
| 4 | VIEW | | 10 | 570 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 10 | 740 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| PATIENT_DETAILS | 10 | 740 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."PATIENT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Crispo, September 01, 2011 - 2:50 pm UTC
Thanks! That's awesome
Driving_site hint on views
pranav, April 10, 2012 - 5:36 pm UTC
Hi Tom,
Could you please let me know how to use driving_site hint on view?
For example,
create view test_view as select * from dba_objects a, dba_objects@stg_dblink b;
select /*+DRIVING_SITE(?) */* from test_view;
In the above example, how can we specify driving_site hint in the select statement when pulling data from view?
Thank you very much for your help.
April 11, 2012 - 11:18 am UTC
Driving_site hint on views
pranav, April 11, 2012 - 12:53 pm UTC
Thank you very much Tom.
Append Hint
Shimmy, April 12, 2012 - 9:40 am UTC
April 12, 2012 - 1:13 pm UTC
xappend is just "not append" :)
it is the same as using the /*+ this_is_not_append */
it just changed the word append enough to make it effectively disappear.
driving_site hint
A reader, April 24, 2012 - 1:03 pm UTC
Hi Tom,
Thanks for your wonderful site.
How to determine why Oracle didn't use the driving_site hint? I have a complex view where most of the large tables(pulling minimum 8M rows) are in target. I am using driving_site hint(global hint as it's view) to run my query in target, but somehow oracle is ignoring the hint. I tried to pull 10053 trace but not sure if it contains this information. Please let me know if you would like to see the explain plan. All I see in the plan is, there are some of the (sub)sql's listed under "Remote SQL Information". I want the whole query to be executed at the target. I even tried to execute the query separately using driving_site but no luck.
If you could please provide some information, that would be great.
Thanks once again for all your valuable time.
April 25, 2012 - 9:41 am UTC
show the query first.
Index hint
Anna, July 27, 2012 - 1:20 am UTC
Tom,
1. Does hint reduce the performance in any scenario ?
2. I am using an index hint on a query which really slows down the performance. what could be the reason ?
Question on Parallel hints across Instance
Snehasish Das, November 12, 2012 - 1:20 am UTC
Hi Tom,
I was going through the 11gR2 and 10GR2 hints
http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId63 There I could not find the description to Hints where we specify Instance like PARALLEL(TABLE1,4,2).
I wanted to know (say we have 2 node rac)
1. The number of parallel slaves for this kind of queries will it be 4 (spread across 2 nodes) or 8 (4*2).
2. Do we need Failover or Load balancing to use this kind of setting i.e any special setting required to use parallel slaves across nodes.
3. What happens if the requested slaves can't be found in the other node will parallel slaves be used from the current node.
4. Will cache fusion be a factor for these kind of queries.
Regards,
Snehasish Das
RAC and Parallel hints
snehasish Das, November 22, 2012 - 1:19 am UTC
Hi Tom,
I am unable to get the answers to above questions. It would be really helpful if you can guide me here.
November 29, 2012 - 6:27 am UTC
Parallel Query across Nodes.
Snehasish Das, November 30, 2012 - 1:13 am UTC
Hi Tom,
Thanks a lot for your reply.
Can you please let me know if parallel slaves can be from different nodes, If yes what is the internal algorithm behind it. i.e Which node to take the slave from how the slaves are assigned etc
Regards,
Snehasish Das