Some more clarification...
A reader, April 10, 2003 - 10:01 am UTC
Thanks for your response. I am suitably rebuked! Yes, I did seem to have missed the boat/point on this one.
No application will do 'select * from customer', it will _always_ be with some other limiting criteria (typically last_name or cust_no or some other duly indexed field).
My selfish concern was only for myself, when I just want to browse using Toad and clicking on the 'Data' tab takes forever.
Yes, I guess I can type (eek!) select /*+ first_rows */ from customer where rownum<50;
Few followup questions, if you dont mind.
1. If I give a hint like say /*+ first_rows */ in some view and use that view to create another view like so
create view v1 as select /*+ first_rows */ from ...
create view v2 as
select ... from v1
union all
select ... from v3
I find that the plan for
select ... from v2
has the /*+ first_rows */ hint.
Why does the hint in the definition of one of the views apply to any other view using that view? Is there any way to prevent this? I do want to re-use my views so as to share code, so to speak, but I dont want the optimization I did for one view to affect other views using that view. I didnt think the hint bubbled like this.
April 10, 2003 - 10:21 am UTC
1) i wouldn't hint a view - there are hints that can be used to push down into a view in the main query (so you can "hint" a view by hinting the query against the view). Putting a hint into a view has only made my life misery -- trying to figure out "why, why why is it doing that -- DOH, someone put that hint in there and it is totally incompatible with what I need"
Your example is a great reason NOT to hint the views.
So true
Jeremy, April 10, 2003 - 2:24 pm UTC
This is just great!! You don't know (well, you probably do) how many times I am pulled over by some "programmer" who is running a query in toad and sees the "first rows" return fast and then says, "See. The query is faster this way and slower the way you wrote it.", even when they are writing a program that has to process all the rows. I try to be patient... but it is hard.
April 11, 2003 - 8:04 am UTC
It happens so many times that I frequently ask now "are you running toad? well then page down to the LAST PAGE and then tell me what you think".
Silence is the most common result.
Had one guy that could not understand how a query without an order by could take 1 second, same query with order by 2 minutes.
I made him run both in sqlplus to get a tkprof and it turns out both took 2 minutes as coded -- he had never gotten past the first page. the order by had to get the last past in order to give the first -- the non-order by didn't. both queries had exactly the same runtime characteristics when fetched from fully.
one of the reasons I'll never use toad or anything like it to test with -- its all about sqlplus!
Similar problem
Jon, April 10, 2003 - 10:49 pm UTC
Couldn't agree more - I like to let the optimiser do what it thinks is best - usually that's just fine.
But I have a particular example that I can't seem to get the optimiser to evaluate in an efficient way. I have some functionality encapsulated in a complex view. When queries are run against that view directly using a constant, it produces an appropriate nested_loop/first_rows kind of plan. For example:
select *
from lvr_by_lcs
where loan_id = 1237
returns 30 out of a possible 400K+ rows in about 50 msecs - just perfect.
But when I join another table to the view:
select l.*
from redemption_batch rb, lvr_by_lcs l
where l.loan_id = rb.loan_id
and rb.redemption_batch_id = 200
the plan changes to hash_join/full_scan/all_rows kind of approach that is just ridiculous for this situation, and takes ages to run. redemption_details_id is the PK for that table, so there could only ever be one loan_id joining to the view. I have tried using no_merge, first_rows hints, and I can't get it to return results efficiently. It is actually making the view usless for its intended purpose, because no one can join to it without it disappearing into query land for half an hour. All tables are analyzed etc.
It think it needs to drive into the view off the loan_id, but it refuses to do it.
What is the CBO doing here - is there any way around it?
Thanks
Jon
April 11, 2003 - 8:46 am UTC
Well, autotrace would be useful -- to see the estimated cardinality
Trouble with FIRST_ROWS, how to NOMERGE subquery?
David Penington, April 11, 2003 - 1:46 am UTC
Very valid comments, and interesting because I just spent 4 hours struggling with a related problem on 9.0.1.3, including, of course, hunting in askTOM, which had some useful stuff. I have a large table and need to get the first N rows, or count how many rows there are, up to a limit. There are other criteria, but the killer is a subquery which is equivalent to getting a maximum date, and I know most rows only have one date. Analytics might do this better, but this has to run on Standard Edition. I tried FIRST_ROWS, USE_NL and others, but it wouldn't change. My conclusion was that the optimiser is merging up the sub-query with the MAX which is documented as overriding FIRST_ROWS.
Eventually I found NO_UNNEST hint (new in 9.0.1) which let the ROWNUM <= 11 work.
This takes 2 minutes 13 seconds (3.7million rows in table):
select /*+ FIRST_ROWS(10) */ * from SERVICE S1 where rownum <= 11 and S1.date_published=
(SELECT MAX(date_published) FROM SERVICE T2 WHERE T2.ID = S1.ID)
This takes 0.03 seconds:
select * from SERVICE S1 where rownum <= 11 and S1.date_published=
(SELECT /*+ NO_UNNEST */ MAX(date_published) FROM SERVICE T2 WHERE T2.ID = S1.ID)
I don't know how to give a NOMERGE hint at the top query that references this nameless subquery - is it possible (and is it relevant) ?
April 11, 2003 - 8:56 am UTC
analytics should be available with SE in 9i... can you try that.
(autotrace traceonly explain plans are always usefull)
Autotrace results...
Jon, April 13, 2003 - 3:26 am UTC
OK, here are the autotrace results:
1 select loan_id, portfolio_value, weighted_lvr
2 from lvr_by_lcs
3* where loan_id = 4568
empire_owner@EMPIRED> /
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=90 Bytes=11880)
1 VIEW (Cost=9 Card=90 Bytes=11880)
2 NESTED LOOPS (OUTER) (Cost=9 Card=90 Bytes=12690)
3 NESTED LOOPS (OUTER) (Cost=8 Card=7 Bytes=868)
4 NESTED LOOPS (OUTER) (Cost=7 Card=7 Bytes=819)
5 NESTED LOOPS (Cost=6 Card=7 Bytes=714)
6 NESTED LOOPS (Cost=5 Card=7 Bytes=483)
7 NESTED LOOPS (Cost=4 Card=7 Bytes=378)
8 NESTED LOOPS (Cost=3 Card=1 Bytes=23)
9 NESTED LOOPS (Cost=2 Card=1 Bytes=13)
10 TABLE ACCESS (BY INDEX ROWID) OF 'EM$_LOAN' (Cost=1 Card=1 Bytes=8)
11 INDEX (UNIQUE SCAN) OF 'EM$_LOAN_PK' (UNIQUE) (Cost=1 Card=1)
12 TABLE ACCESS (BY INDEX ROWID) OF 'EM$_TERMS_CONDITIONS' (Cost=1 Card=32 Bytes=16
0)
13 INDEX (UNIQUE SCAN) OF 'BOOKLET_TYPE_PK' (UNIQUE)
14 TABLE ACCESS (BY INDEX ROWID) OF 'LOAN_CLIENT' (Cost=1 Card=2 Bytes=20)
15 INDEX (RANGE SCAN) OF 'LOAN_CLIENT_IX6' (NON-UNIQUE)
16 TABLE ACCESS (BY INDEX ROWID) OF 'EM$_LOAN_CLIENT_SECURITY' (Cost=1 Card=471179 Byte
s=14606549)
17 INDEX (RANGE SCAN) OF 'EM$_LOAN_CLIENT_SECURITY_UK1' (UNIQUE) (Cost=1 Card=471179)
18 TABLE ACCESS (BY INDEX ROWID) OF 'SECURITY_MASTERFUND' (Cost=1 Card=34984 Bytes=524760
)
19 INDEX (UNIQUE SCAN) OF 'SECURITY_MASTERFUND_PK' (UNIQUE)
20 TABLE ACCESS (BY INDEX ROWID) OF 'EM$_SECURITIES' (Cost=1 Card=33659 Bytes=1110747)
21 INDEX (UNIQUE SCAN) OF 'EM$_SECURITIES_PK' (UNIQUE)
22 TABLE ACCESS (BY INDEX ROWID) OF 'LVR' (Cost=1 Card=38736 Bytes=581040)
23 INDEX (UNIQUE SCAN) OF 'LVR_PK' (UNIQUE)
24 TABLE ACCESS (BY INDEX ROWID) OF 'LVR' (Cost=1 Card=38736 Bytes=271152)
25 INDEX (UNIQUE SCAN) OF 'LVR_PK' (UNIQUE)
26 TABLE ACCESS (BY INDEX ROWID) OF 'PROSPECTUS' (Cost=1 Card=1291 Bytes=21947)
27 INDEX (UNIQUE SCAN) OF 'PROSPECTUS_PK' (UNIQUE)
Statistics
----------------------------------------------------------
55 recursive calls
0 db block gets
136 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
10 rows processed
/*********************************************************/
/*********************************************************/
1 select l.loan_id, l.portfolio_value, l.weighted_lvr
2 from redemption_batch rb, lvr_by_lcs l
3 where l.loan_id = rb.loan_id
4* and rb.redemption_batch_id = 200
empire_owner@EMPIRED> /
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12672 Card=98 Bytes=13720)
1 NESTED LOOPS (Cost=12672 Card=98 Bytes=13720)
2 TABLE ACCESS (BY INDEX ROWID) OF 'REDEMPTION_BATCH' (Cost=1 Card=1 Bytes=8)
3 INDEX (UNIQUE SCAN) OF 'PK_REDEMPTION' (UNIQUE)
4 VIEW
5 HASH JOIN (OUTER) (Cost=12671 Card=6082921 Bytes=857691861)
6 HASH JOIN (Cost=9649 Card=471179 Bytes=58426196)
7 TABLE ACCESS (FULL) OF 'EM$_TERMS_CONDITIONS' (Cost=1 Card=32 Bytes=160)
8 HASH JOIN (Cost=9613 Card=471179 Bytes=56070301)
9 TABLE ACCESS (FULL) OF 'EM$_SECURITIES' (Cost=100 Card=33659 Bytes=1110747)
10 HASH JOIN (Cost=6470 Card=471179 Bytes=40521394)
11 TABLE ACCESS (FULL) OF 'SECURITY_MASTERFUND' (Cost=19 Card=34984 Bytes=524760)
12 HASH JOIN (OUTER) (Cost=4831 Card=471179 Bytes=33453709)
13 HASH JOIN (OUTER) (Cost=3025 Card=471179 Bytes=26386024)
14 HASH JOIN (Cost=2161 Card=471179 Bytes=23087771)
15 HASH JOIN (Cost=502 Card=97235 Bytes=1750230)
16 TABLE ACCESS (FULL) OF 'EM$_LOAN' (Cost=248 Card=62219 Bytes=497752)
17 TABLE ACCESS (FULL) OF 'LOAN_CLIENT' (Cost=148 Card=97235 Bytes=972350)
18 TABLE ACCESS (FULL) OF 'EM$_LOAN_CLIENT_SECURITY' (Cost=639 Card=471179 Bytes=14
606549)
19 TABLE ACCESS (FULL) OF 'LVR' (Cost=43 Card=38736 Bytes=271152)
20 TABLE ACCESS (FULL) OF 'LVR' (Cost=43 Card=38736 Bytes=581040)
21 TABLE ACCESS (FULL) OF 'PROSPECTUS' (Cost=2 Card=1291 Bytes=21947)
Statistics
----------------------------------------------------------
55 recursive calls
41 db block gets
14673 consistent gets
34453 physical reads
0 redo size
832 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
10 rows processed
As you can see, it chooses not to drive off the loan table (em$_loan) in the second query - and I can't seem to force it to do so. I have tried global hints, from the top level query, but they don't seem to change anything. Any suggestions?
Thanks
Jon
April 13, 2003 - 8:11 pm UTC
sorry -- didn't realize this was a huge gnarly view here. all bets off -- I cannot tell anything about nothing without the query -- but -- as this is getting quite large (and you have a workaround), wait until I'm taking a question and post the relevant information there (views, indexes, tables, et. al.)
what does unnest hint do?
A reader, December 06, 2003 - 1:57 pm UTC
Hi
We recently fixed a view by using unnest hint, basically it was this query
select *
from abc x
where mynum = (select max(mynum)
from abc y
where x.col1 = y.col1
and x.col2 = y.col2
and xdate <= trunc(sysdate))
col1 and col2 are indexed
at first the plan fulls scans x and index fast full scans index on col1 and col2, takes ages
added unnest hint full scan became range scan
what does unnest do?
December 06, 2003 - 2:29 pm UTC
un-nested the subquery.
it turned it into a "semi join", sort of like writing the query as a join.
do you mean this
A reader, December 06, 2003 - 2:33 pm UTC
select *
from abc x
where mynum = (select max(mynum)
from abc y
where x.col1 = y.col1
and x.col2 = y.col2
and xdate <= trunc(sysdate))
into
select *
from abc x, (select col1, col2, max(mynum)
from abc
where xdate <= trunc(sysdate)
group by col1, col2) y
where x.col1 = y.col1
and x.col2 = y.col2
If this is the case I thought Oracle does this automatically :-0
December 06, 2003 - 3:02 pm UTC
similar -- but a semi join -- not just a simple join like that -- it would not need to do the group by.
try this out
select *
from (
select abc.* ,
max(case when xdate <= trunc(sysdate) then mynum end)
over (partition by col1, col2) max_mynum
from abc
)
where mynum = max_mynum;
it will do it automagically -- but, you give us no test case to look at, no inputs to tell you why it did what it did. I can only provide an example myself. I cannot imagine why it would want to use indexes *at all* here personally. Looks like full scan city to me (so something must be missing from your example)
DOC>create table t ( col1 int, col2 int, mynum int, xdate date, data varchar2(50) );
DOC>
DOC>insert into t
DOC>select mod(rownum,1000), mod(rownum,1000), object_id, created+50, rpad('x',50,'x')
DOC> from all_objects;
DOC>
DOC>create index t_idx on t(col1,col2,xdate);
DOC>
DOC>exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns', cascade=>true);
DOC>*/
ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*),
2 count(distinct col1||','||col2),
3 count(case when xdate <= trunc(sysdate) then 1 end)
4 from t;
COUNT(*) COUNT(DISTINCTCOL1||','||COL2) COUNT(CASEWHENXDATE<=TRUNC(SYSDATE)THEN1END)
---------- ------------------------------ --------------------------------------------
31702 1000 31577
ops$tkyte@ORA920>
ops$tkyte@ORA920> @trace
ops$tkyte@ORA920> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> select *
2 from t x
3 where mynum = (select max(mynum)
4 from t y
5 where x.col1 = y.col1
6 and x.col2 = y.col2
7 and xdate <= trunc(sysdate))
8 /
1000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
748 consistent gets
672 physical reads
0 redo size
31192 bytes sent via SQL*Net to client
1229 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
ops$tkyte@ORA920>
ops$tkyte@ORA920> select *
2 from (
3 select t.* ,
4 max(case when xdate <= trunc(sysdate) then mynum end)
5 over (partition by col1, col2) max_mynum
6 from t
7 )
8 where mynum = max_mynum
9 /
1000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
341 consistent gets
336 physical reads
0 redo size
36953 bytes sent via SQL*Net to client
1229 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
ops$tkyte@ORA920> set autotrace off
and tkprof says:
select *
from t x
where mynum = (select max(mynum)
from t y
where x.col1 = y.col1
and x.col2 = y.col2
and xdate <= trunc(sysdate))
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 68 0.41 0.40 672 748 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.41 0.40 672 748 0 1000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 549
Rows Row Source Operation
------- ---------------------------------------------------
1000 HASH JOIN (cr=748 r=672 w=0 time=393513 us)
1000 VIEW (cr=341 r=336 w=0 time=185614 us)
1000 SORT GROUP BY (cr=341 r=336 w=0 time=183179 us)
31577 TABLE ACCESS FULL T (cr=341 r=336 w=0 time=92427 us)
31702 TABLE ACCESS FULL T (cr=407 r=336 w=0 time=66313 us)
********************************************************************************
select *
from (
select t.* ,
max(case when xdate <= trunc(sysdate) then mynum end)
over (partition by col1, col2) max_mynum
from t
)
where mynum = max_mynum
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 68 0.39 0.38 336 341 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.39 0.38 336 341 0 1000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 549
Rows Row Source Operation
------- ---------------------------------------------------
1000 VIEW (cr=341 r=336 w=0 time=376949 us)
31702 WINDOW SORT (cr=341 r=336 w=0 time=332608 us)
31702 TABLE ACCESS FULL T (cr=341 r=336 w=0 time=70368 us)
meaning, you might look at anaytics.
same query, same plan, one with first_rows and is faster?
A reader, January 09, 2004 - 7:37 am UTC
Hi
I have these couple of queries, return same rows and share same execution plan but one is faster and does less reads... why?
SELECT /*+ first_rows */ a.signature
FROM sysadm.ps_rb_wf_signature a, sysadm.ps_rd_directory b
WHERE a.person_id = b.idtypeattrib AND b.userid = :a
AND recipient_type = 'PER'
38 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=567 Card=1878 Bytes=52584)
1 0 NESTED LOOPS (Cost=567 Card=1878 Bytes=52584)
2 1 TABLE ACCESS (FULL) OF 'PS_RB_WF_SIGNATURE' (Cost=3 Card=1878 Bytes=24414)
3 1 INDEX (RANGE SCAN) OF 'PS_RD_DIRECTORY' (UNIQUE) (Cost=1 Card=1 Bytes=15)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3933 consistent gets
29 physical reads
0 redo size
7799 bytes sent via SQL*Net to client
1073 bytes received via SQL*Net from client
40 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed
SELECT a.signature
FROM sysadm.ps_rb_wf_signature a, sysadm.ps_rd_directory b
WHERE a.person_id = b.idtypeattrib AND b.userid = :a
AND recipient_type = 'PER'
38 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=567 Card=1878 Bytes=52584)
1 0 NESTED LOOPS (Cost=567 Card=1878 Bytes=52584)
2 1 TABLE ACCESS (FULL) OF 'PS_RB_WF_SIGNATURE' (Cost=3 Card=1878 Bytes=24414)
3 1 INDEX (RANGE SCAN) OF 'PS_RD_DIRECTORY' (UNIQUE) (Cost=1 Card=1 Bytes=15)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15884 consistent gets
12481 physical reads
0 redo size
7799 bytes sent via SQL*Net to client
1073 bytes received via SQL*Net from client
40 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed
January 09, 2004 - 8:43 am UTC
i doubt they really have the same plans.
explain plan "lies" sometimes -- especially with binds.
Use sql_trace and tkprof -- or EXPLICITLY cast the binds (eg: to_number(:a) if is is really a number)
Execution Plan
Mahomed Suria, January 09, 2004 - 9:06 am UTC
Hi Tom,
I have a question on Execution Plan.
I have the following query( V8.1.7) :
select r.rakusr, r.whscde, r.rakblk, r.rakasl, r.rakcol,
r.raklvl, r.raksid, r.rakocc, r.rakfre, r.maxdep,
r.numitr, r.raktyp, nvl(r.profil, -1), nvl(r.stgtyp, -1),
r.palbas, (select count(*) from pallet p
where p.whscde = r.whscde
and p.rakblk = r.rakblk
and p.rakasl = r.rakasl
and p.rakcol = r.rakcol
and p.raklvl = r.raklvl
and p.raksid = r.raksid
and trnsts <> 5 ) rakcnt,
(select nvl(count(*), 0) from pallet p
where p.dstwhs = r.whscde
and p.dstblk = r.rakblk
and p.dstasl = r.rakasl
and p.dstcol = r.rakcol
and p.dstlvl = r.raklvl
and p.dstsid = r.raksid ) dstcnt
from rakloc r
where r.whscde = 1
and (r.rakblk = 1 or 1 is null)
and (r.rakasl = 1 or 1 is null)
and (r.rakcol = 1 or 1 is null)
and (r.raklvl = 1 or 1 is null)
and (r.raksid = 1 or 1 is null)
and r.raktyp in (select raktyp from bkrtyp
where blktyp = 2010)
It produces the following plan:
Elapsed: 00:00:01.86
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW OF 'VW_NSO_1'
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'BKRTYP'
5 4 INDEX (RANGE SCAN) OF 'BKRTYP_1' (NON-UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'RAKLOC'
7 6 INDEX (RANGE SCAN) OF 'RAKLOC_5' (NON-UNIQUE)
What I'd like to know is where does the SORT come in??
January 09, 2004 - 9:14 am UTC
in's do that lots.
ops$tkyte@ORA920PC> set autotrace traceonly explain
ops$tkyte@ORA920PC> select * from dual where dummy in ( select * from dual );
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DUAL'
4 1 SORT (JOIN)
5 4 VIEW OF 'VW_NSO_1'
6 5 SORT (UNIQUE)
7 6 TABLE ACCESS (FULL) OF 'DUAL'
we need to distinct the subquery so we can "join to it"
The optimizer rewrote the in as a join here.
How can FIRST_ROWS used for a query that is fired from Java
Sudipta, November 02, 2004 - 3:50 am UTC
I have the FIRST_ROWS hint and it returns me the first set of rows out of the total 1000 rows very fast ( in 500 Ms), however the same query being xcalled from Java program takes forever( 1.5 Mins) - The reason the java progranm waits for all the rows of the resultset to be returned befiore it can display teh page .
Do you have any code sample where a java program uses the power of ** FIRST_ROWS** hint to return results to the client very fast even before the entire resultset is returned.
When I ex[plained people about the first_rows hint , reply I got was how to use that in JAVA programs- to whcih I had no reply, kindly help me out with a code snippet.
Also since this is an OLTP app so people don't want to fire the same query again and agian to page through results( a very stupid idea !!) .
Thank s and regards,
Sudipta
November 02, 2004 - 7:44 am UTC
java is just submitting sql
sql is sql, sql in oracle has hints
so, java programmers would just put hint into sql?
but, if the java program waits to get all of the rows before the first row is display -- not too much anyone can do -- short of get java programmers that now how to fetch a little, display a little, fetch a little , display a little
How to make CBO behave like /*+ first_rows */
Dave, November 02, 2004 - 10:57 am UTC
Hi Tom,
It's a learing experience everyday and many thanks for your wonderful contribution.
Thanks
Dave
why first_rows change the order of the rows
reader, June 01, 2005 - 4:41 pm UTC
We have noticed that for following SQL when we remove the first_rows hint the order of certain records has been changed. We didn't understand that behavior as the front end application needs records in certain order, order is important for us. With hint the query use the nested loop outer and force the index to use while without hint the query use the hash join and perform better, the cost is also less with hash join.
SELECT uhg_received_date, uhg_creation_date, tracking_number,pkg_work_queue.fn_get_work_queue_age (uhg_received_date) age,assigned_to_user, status_desc, record_type_name, custom_field_1,custom_field_2, custom_field_3, comments, pkg_work_queue.fn_get_wq_reason_types (tracking_number) reasons,
pkg_work_queue.fn_get_wq_record_identifier (tracking_number)
IDENTIFIER,
record_type_id, to_process_id, type_id, status_id, last_modified_date,NVL (to_process_id, 0)
|| ','
|| tracking_number
|| ','
|| NVL (record_type_id, 0) action,
'rcondon'
|| ','
|| NVL (status_id, 0)
|| ','
|| NVL (assigned_to_user, 0)
|| ','
|| tracking_number checkall
FROM (SELECT /*+ first_rows */
uhg_received_date, uhg_creation_date,
tracking_number,
assigned_to_user, status_desc,
record_type_name,
custom_field_1, custom_field_2,
custom_field_3, comments,
record_type_id, to_process_id, type_id,
status_id,
last_modified_date, ROWNUM AS rownumber,
completed_by_user
FROM (SELECT wqi.uhg_received_date,
wqi.uhg_creation_date,
wqi.tracking_number,
wqi.assigned_to_user,
wqs.status_desc,
wqrt.record_type_name,
wqi.custom_field_1,
wqi.custom_field_2,
wqi.custom_field_3,
wqi.comments, wqi.record_type_id,
wqi.to_process_id, wqi.type_id,
wqi.status_id,
wqi.last_modified_date,
wqi.completed_by_user
FROM work_queue_item wqi,
work_queue_status wqs,
work_queue_record_type wqrt
WHERE wqi.record_type_id =
wqrt.record_type_id(+)
AND wqi.status_id = wqs.status_id
AND NVL (wqi.record_type_id, 1040) IN
(2010,
4050,
4020,
2009,
1012,
1011,
2011,
1030,
4010,
1010,
1001,
1020,
5001,
3010,
1040
)
AND TRUNC (wqi.uhg_received_date)
< '26-MAY-05'
AND wqi.status_id IN (1, 2, 3, 4)
AND wqi.assigned_to_user IS NULL
AND wqi.type_id = 2009
ORDER BY wqi.uhg_received_date,
wqi.status_id))
WHERE rownumber BETWEEN 1 AND 49
------------------------------------------------------------
Statement Id=0 Type=
Cost=2.64022960303481E-308 TimeStamp=01-06-05::16::31:58
(1) SELECT STATEMENT HINT: FIRST_ROWS
Est. Rows: 202 Cost: 705
(14) VIEW (Embedded SQL)
Est. Rows: 202 Cost: 705
(13) COUNT
(12) VIEW (Embedded SQL)
Est. Rows: 202 Cost: 705
(11) SORT ORDER BY
Est. Rows: 202 Cost: 705
(10) NESTED LOOPS OUTER
Est. Rows: 202 Cost: 697
(7) NESTED LOOPS
Est. Rows: 202 Cost: 495
(4) INLIST ITERATOR
(3) TABLE ACCESS BY INDEX ROWID COMPAS.WORK_QUEUE_STATUS [Analyzed]
(3) Blocks: 54 Est. Rows: 4 of 6 Cost: 3
Tablespace: TS_COMPAS_01_D
(2) UNIQUE INDEX RANGE SCAN COMPAS.PK_WORK_QUEUE_STATUS [Analyzed]
Est. Rows: 4 Cost: 1
(6) TABLE ACCESS BY INDEX ROWID COMPAS.WORK_QUEUE_ITEM [Analyzed]
(6) Blocks: 24,008 Est. Rows: 51 of 886,944 Cost: 123
Tablespace: TS_COMPAS_03_D
(5) NON-UNIQUE INDEX RANGE SCAN COMPAS.IDX_WORK_QUEUE_ITEM_02 [Analyzed]
Est. Rows: 447 Cost: 85
(9) TABLE ACCESS BY INDEX ROWID COMPAS.WORK_QUEUE_RECORD_TYPE [Analyzed]
(9) Blocks: 54 Est. Rows: 1 of 15 Cost: 1
Tablespace: TS_COMPAS_01_D
(8) UNIQUE INDEX UNIQUE SCAN COMPAS.PK_WORK_QUEUE_RECORD_TYPE [Analyzed]
Est. Rows: 1
Plan without hint :
Statement Id=4203092 Type=
Cost=2.64022960303481E-308 TimeStamp=01-06-05::16::38:36
(1) SELECT STATEMENT CHOOSE
Est. Rows: 202 Cost: 329
(13) VIEW (Embedded SQL)
Est. Rows: 202 Cost: 329
(12) COUNT
(11) VIEW (Embedded SQL)
Est. Rows: 202 Cost: 329
(10) SORT ORDER BY
Est. Rows: 202 Cost: 329
(9) HASH JOIN OUTER
Est. Rows: 202 Cost: 321
(7) HASH JOIN
Est. Rows: 202 Cost: 315
(4) INLIST ITERATOR
(3) TABLE ACCESS BY INDEX ROWID COMPAS.WORK_QUEUE_STATUS [Analyzed]
(3) Blocks: 54 Est. Rows: 4 of 6 Cost: 3
Tablespace: TS_COMPAS_01_D
(2) UNIQUE INDEX RANGE SCAN COMPAS.PK_WORK_QUEUE_STATUS [Analyzed]
Est. Rows: 4 Cost: 1
(6) TABLE ACCESS BY INDEX ROWID COMPAS.WORK_QUEUE_ITEM [Analyzed]
(6) Blocks: 24,008 Est. Rows: 303 of 886,944 Cost: 311
Tablespace: TS_COMPAS_03_D
(5) NON-UNIQUE INDEX RANGE SCAN COMPAS.IDX_WORK_QUEUE_ITEM_02 [Analyzed]
Est. Rows: 447 Cost: 86
(8) TABLE ACCESS FULL COMPAS.WORK_QUEUE_RECORD_TYPE [Analyzed]
(8) Blocks: 54 Est. Rows: 15 of 15 Cost: 5
Tablespace: TS_COMPAS_01_D
June 01, 2005 - 5:38 pm UTC
are you saying that the data isn't sorted by
ORDER BY wqi.uhg_received_date,
wqi.status_id
-- is
ORDER BY wqi.uhg_received_date,
wqi.status_id "unique" or are there duplicate dates/id's?
reader, June 02, 2005 - 10:33 am UTC
Not unique it can be duplicates, the query behave same way if I remove the inline view, do not understand why?
The same query just commented out the inline view that use rownumber, and changed the last where condition rownumber to rownum.
SELECT uhg_received_date, uhg_creation_date, tracking_number,pkg_work_queue.fn_get_work_queue_age (uhg_received_date) age,assigned_to_user, status_desc, record_type_name, custom_field_1,custom_field_2, custom_field_3, comments,
pkg_work_queue.fn_get_wq_reason_types (tracking_number) reasons,pkg_work_queue.fn_get_wq_record_identifier (tracking_number)IDENTIFIER,record_type_id, to_process_id, type_id, status_id, last_modified_date,NVL (to_process_id, 0)
|| ','
|| tracking_number
|| ','
|| NVL (record_type_id, 0) action,
'rcondon'
|| ','
|| NVL (status_id, 0)
|| ','
|| NVL (assigned_to_user, 0)
|| ','
|| tracking_number checkall
/* FROM (SELECT /*+ first_rows */
uhg_received_date, uhg_creation_date, tracking_number,
assigned_to_user, status_desc, record_type_name,
custom_field_1, custom_field_2, custom_field_3, comments,
record_type_id, to_process_id, type_id, status_id,
last_modified_date, ROWNUM AS rownumber, completed_by_user
*/
FROM (SELECT wqi.uhg_received_date, wqi.uhg_creation_date,
wqi.tracking_number, wqi.assigned_to_user,
wqs.status_desc, wqrt.record_type_name,
wqi.custom_field_1, wqi.custom_field_2,
wqi.custom_field_3, wqi.comments, wqi.record_type_id,
wqi.to_process_id, wqi.type_id, wqi.status_id,
wqi.last_modified_date, wqi.completed_by_user
FROM work_queue_item wqi,
work_queue_status wqs,
work_queue_record_type wqrt
WHERE wqi.record_type_id = wqrt.record_type_id(+)
AND wqi.status_id = wqs.status_id
AND wqi.record_type_id IN
(2010,
4050,
4020,
2009,
1012,
1011,
2011,
1030,
4010,
1010,
1001,
1020,
5001,
3010,
1040
)
AND TRUNC (wqi.uhg_received_date) < '26-MAY-05'
AND wqi.status_id IN (1, 2, 3, 4)
AND wqi.assigned_to_user IS NULL
AND wqi.type_id = 2009
ORDER BY wqi.uhg_received_date, wqi.status_id)
WHERE rownum BETWEEN 1 AND 8
June 02, 2005 - 5:10 pm UTC
what I asked was -- is the resulting data sorted by those two columns?
since they are not unique, the only thing I would expect is that the data is "sorted by them, but comes out in different order overall"
for example:
ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 100 );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 1, 200 );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t order by x;
X Y
---------- ----------
1 100
1 200
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t;
Table truncated.
ops$tkyte@ORA9IR2> insert into t values ( 1, 200 );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 1, 100 );
1 row created.
ops$tkyte@ORA9IR2> select * from t order by x;
X Y
---------- ----------
1 200
1 100
First_Rows hint doesn't work
Pete, June 02, 2005 - 9:21 pm UTC
Hi Tom,
I ran the timings on the following query using the first_rows hint and without it. The elapsed time was same ,. The query did not bring back the results till more than an hour. We have a requirement that the query should response within 1 minutes or a timeout will happen.Im my case First_rows hint is not working. I've tried using all the possible indexes to avoid the full table scan. When I do that the cost goes up and it takes more time to run.
The query is as follows with the plan and the number of rows in the tables:
SELECT TO_CHAR(payment.SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(payment.ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(payment.ProvId,1, PmtAmt, 0)) as ACHSubTot,
SUM(DECODE(payment.ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(payment.ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(payment.PmtAmt) AS Count,SUM(payment.PmtAmt) as Total
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3 and
payment.LfcyclCd='SN' and
payment.PmtId=WF_PmtAux.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt
Table # of Rows
payment 10,000,000
account 1,000,000
users 108
subsidy 10,000,000
cost:96045
12 SELECT STATEMENT
11 SORT [GROUP BY]
10 HASH JOIN
1 USERS TABLE ACCESS [FULL]
9 MERGE JOIN
6 SORT [JOIN]
5 NESTED LOOPS
2 Subsidy TABLE ACCESS [FULL]
4 Payment TABLE ACCESS [BY INDEX ROWID]
3 CBF.XPKpayment INDEX [UNIQUE SCAN] -- on pmtid
8 SORT [JOIN]
7 Account TABLE ACCESS [FULL]
Description Cost Est.Rows Ret Est. KBytes
1 Retrieves all rows from table USERS 1 3 0.015
2 Retrieves all rows from table Subsidy. 21,762 24,831 581.977
3 Retrieves a single ROWID from the B*-tree index XPKpayment. 1 2,498,909 --
4 Retrieves rows from table Payment through ROWID(s) returned by an index. 2 2,498,909 82,971.588
5 Nested Loops 71,424 24,831 1,406.443
6 Sort-join operation. 72,137 24,831 1,406.443
7 Retrieves all rows from table account. 2,880 1,037,774 20,269.023
8 Accepts a row set (its only child) and sorts it in preparation for a merge-join 23,890 1,037,774 20,269.023
9.Merge Join 96,027 5,968 454.594
10 Hash Join 96,037 484 39.23
11 Sort GROUP BY 96,045 478 38.744
June 03, 2005 - 7:09 am UTC
first_rows says "try to optimize to return the first rows as fast as you can"
it is a request, if it is not possible, it cannot be done.
before I make comments on the indexing scheme and the predicate -- do you really want the FIRST_ROW as fast as possible (making the time to retrieve the last row take even longer than it does not)
OR do you want ALL OF THE ROWS as soon as possible.
First Rows hint does not work
Pete, June 03, 2005 - 10:36 am UTC
Tom,
Our Dilemma is that if the query does not responds within a minute then the reporting query will time-out thats why I thought first_rows might help.
Im open to your suggestion. But this is one stubborn query. Some other info I found out for the payment table(10 million):
The columns Provid has 4 distinct values and lfcyclcd had also 4 distinct values. I think I should be creating a bitmap index rather than the current normal index. Not sure if this might help the query.
Appreciate your response.
Thanks
Pete
June 03, 2005 - 11:11 am UTC
you didn't really answer the question :)
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3
and payment.LfcyclCd='SN'
and payment.PmtId=WF_PmtAux.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY')
AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt
Ok, predicates on
WHERE users.Masterbillerid = 3
and payment.LfcyclCd='SN'
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY')
AND TO_DATE('03/03/2004','MM/DD/YYYY')
assumption masterbillerid isn't very selective.
assumption lfcyclcd isn't either
but settlementdt could be?
and you could
and subsidy.settlementdt between TO_DATE('03/01/2004','MM/DD/YYYY')
and TO_DATE('03/04/2004','MM/DD/YYYY')-1/24/60/60
which would let subsidy drive the query, using an index on settlementdt, which might also let the group by become a group by NOSORT, which could make first rows return almost instantly.
assuming index on settlementdt exists.
First_rows hint doesnt work
Pete, June 03, 2005 - 4:36 pm UTC
Tom,
Even when the settlementdt column is used the query never comes back. The plan now I get back is as follows:
14 SELECT STATEMENT
13 SORT [GROUP BY]
12 MERGE JOIN
8 SORT [JOIN]
7 MERGE JOIN
4 SORT [JOIN]
3 HASH JOIN
1 USERS TABLE ACCESS [FULL]
2 ACCOUNT TABLE ACCESS [FULL]
6 SORT [JOIN]
5 Payment TABLE ACCESS [FULL]
11 SORT [JOIN]
10 subsidy TABLE ACCESS [BY INDEX ROWID]
9 XIE1_subsidy INDEX [RANGE SCAN] -- settlementdt column
1)SELECT COUNT(lfcyclcd) FROM payment WHERE lfcyclcd = 'SN' gets 9.7 million rows out of 10 million-- no point using index
2)Users table is only 108 rows , so a FTS is fine there
3)
select count(settlementdt) from subsidy where settlementdt between TO_DATE('03/01/2004','MM)
and TO_DATE('03/04/2005','MM/DD/YYYY')-1/24/60/60 ;
It returns 117691 rows out 10 million so this is very selective . Even if the trunc is used in the statement the index is used on the settlementdt column with the same plan.
But the number of records is different if I use trunc and if I dont use trunc and specify the -1/24/60/60?
The plan for this is as follows: Can this be tuned any further.
COst is high:4019
4 SELECT STATEMENT
3 SORT [AGGREGATE]
2 SORT [AGGREGATE]
1 CBF.XIE1_subsidy INDEX [FAST FULL SCAN] -- index on settlementdt
4) Playing around with indexes dives the query even further. The biggest problem I think is the following predicate on the 10 million tables each:
payment.PmtId=subsidy.PmtId
I got the individual plan for running for which the cost is too high. Is there any scope to reduce the cost and speed it.Im not sure what else can be done with this, its using the indexes on both the tables!
SELECT count(1)
FROM bcpmt, wf_pmtaux
WHERE bcpmt.pmtid = wf_pmtaux.pmtid
cost:294,207
7 SELECT STATEMENT
6 SORT [AGGREGATE]
5 SORT [AGGREGATE]
4 MERGE JOIN
1 XPK_Subsidy INDEX [FULL SCAN] -- pmtid
3 SORT [JOIN]
2 XPK_Payment INDEX [FAST FULL SCAN] -- pmtid
Thanks
Tanu
June 03, 2005 - 5:35 pm UTC
first -- please keep indentation -- i cannot make heads or tails of any of that.
Let's see an autotrace traceonly explain of it with the first rows hint.
First_rows hint doesnt work
Pete, June 03, 2005 - 7:21 pm UTC
Here the index used on subsidy table is on the settlementdt
Elapsed: 00:54:34.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=148601 Car
d=8 Bytes=664)
1 0 SORT (GROUP BY) (Cost=148601 Card=8 Bytes=664)
2 1 MERGE JOIN (Cost=148493 Card=3365 Bytes=279295)
3 2 SORT (JOIN) (Cost=125772 Card=48696 Bytes=2873064)
4 3 MERGE JOIN (Cost=124053 Card=48696 Bytes=2873064)
5 4 SORT (JOIN) (Cost=4840 Card=84144 Bytes=2103600)
6 5 HASH JOIN (Cost=3336 Card=84144 Bytes=2103600)
7 6 TABLE ACCESS (FULL) OF 'USERS' (Cost=1
Card=3 Bytes=15)
8 6 TABLE ACCESS (FULL) OF 'ACCOUNT' (Cost=2880 Car
d=1037774 Bytes=20755480)
9 4 SORT (JOIN) (Cost=119213 Card=2498909 Bytes=849629
06)
10 9 TABLE ACCESS (FULL) OF 'PAYMENT' (Cost=33542 Card=
2498909 Bytes=84962906)
11 2 SORT (JOIN) (Cost=22721 Card=172125 Bytes=4131000)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'WF_SUBSIDY' (Cost=1
9731 Card=172125 Bytes=4131000)
13 12 INDEX (RANGE SCAN) OF 'XIE1_SUBSIDY' (NON-UNIQUE)
(Cost=461 Card=172125)
Statistics
----------------------------------------------------------
7988 recursive calls
41460 db block gets
258599 consistent gets
861366 physical reads
155972 redo size
919 bytes sent via SQL*Net to client
949 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
4 sorts (disk)
10 rows processed
-----------------------
The original : Without first_rows and trunc intact. Here the index used is on the payment table on PMTID column:
SELECT TO_CHAR(payment.SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(payment.ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(payment.ProvId,1, PmtAmt, 0)) as ACHSubTot,
SUM(DECODE(payment.ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(payment.ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(payment.PmtAmt) AS Count,SUM(payment.PmtAmt) as Total
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3 and
payment.LfcyclCd='SN' and
payment.PmtId=subsidy.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/15/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt
11 rows selected.
Elapsed: 00:17:13.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=96045 Card=478 Bytes
=39674)
1 0 SORT (GROUP BY) (Cost=96045 Card=478 Bytes=39674)
2 1 HASH JOIN (Cost=96037 Card=484 Bytes=40172)
3 2 TABLE ACCESS (FULL) OF 'USERS' (Cost=1 Card=3 B
ytes=15)
4 2 MERGE JOIN (Cost=96027 Card=5968 Bytes=465504)
5 4 SORT (JOIN) (Cost=72137 Card=24831 Bytes=1440198)
6 5 NESTED LOOPS (Cost=71424 Card=24831 Bytes=1440198)
7 6 TABLE ACCESS (FULL) OF 'Subsidy' (Cost=21762 C
ard=24831 Bytes=595944)
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'Payment' (Cost=2
Card=2498909 Bytes=84962906)
9 8 INDEX (UNIQUE SCAN) OF 'XPK_payment' (UNIQUE) (Co
st=1 Card=2498909)
10 4 SORT (JOIN) (Cost=23890 Card=1037774 Bytes=20755480)
11 10 TABLE ACCESS (FULL) OF 'Account' (Cost=2880 Card=10
37774 Bytes=20755480)
Statistics
----------------------------------------------------------
3583 recursive calls
3623 db block gets
1025043 consistent gets
451257 physical reads
71488 redo size
994 bytes sent via SQL*Net to client
927 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
2 sorts (disk)
11 rows processed
June 03, 2005 - 8:08 pm UTC
well, if
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY')
AND TO_DATE('03/03/2004','MM/DD/YYYY')
and
and subsidy.settlementdt between TO_DATE('03/01/2004','MM/DD/YYYY')
and TO_DATE('03/04/2004','MM/DD/YYYY')-1/24/60/60
something is *wrong*. that trunc gets any settlementdt on the 1, 2, 3 of March 2004. the between -- it does exatctly the same -- it is between midight march 1 and 11:59:59 march 3.
silly question -- are there indexes on all of the join columns (to make it so first rows would even have a ghost of a chance)
Could the "wild style" be the problem?
Gabe, June 03, 2005 - 10:00 pm UTC
The original post and query:
<quote>
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3
And payment.LfcyclCd = 'SN'
And payment.PmtId = WF_PmtAux.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN
TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt
</quote>
So, what is “WF_PmtAux.PmtId” and where is SUBSIDY joined with anything else?
June 04, 2005 - 8:22 am UTC
good point, i thought I checked the joins -- missed that one.
Yes, you have a cartesian product, the query is likely "not correct"
First_rows hint doesnt work
Pete, June 04, 2005 - 4:38 pm UTC
Sorry WF_PMTAUX is there by typo, it should be subsidy.
All the columns in the where clause are indexed. Tom, I think the problem is with the following two tables ( 10 million each). Im wondering if there's a way to speed this up?
SELECT count(1)
FROM bcpmt, wf_pmtaux
WHERE bcpmt.pmtid = wf_pmtaux.pmtid
cost:294,207
7 SELECT STATEMENT
6 SORT [AGGREGATE]
5 SORT [AGGREGATE]
4 MERGE JOIN
1 XPK_Subsidy INDEX [FULL SCAN] -- pmtid
3 SORT [JOIN]
2 XPK_Payment INDEX [FAST FULL SCAN] -- pmtid
Thanks
June 04, 2005 - 6:46 pm UTC
do you have hash joins disabled? I'd rather see a nice big hash join there.
what are your memory setting, are they way small for hash/sort areas? are you using manual or automatic workareas? what is the pga_aggregate_target?
First_rows hint doesnt work
Pete, June 04, 2005 - 7:27 pm UTC
Tom,
This is 8174 version.
hash_area_size integer 131072
hash_join_enabled boolean TRUE
shared_pool_size string 10000000
sort_area_size integer 65536
optimizer_index_cost_adj integer 100
Thanks
June 04, 2005 - 8:21 pm UTC
that would be *very very very* small for the work you are doing.
consider setting sort_area_size (which is used to set hash area size to 2x sort area size) *alot* bigger.
only you know the ram avaiable, but if you set this to 1m (via alter session) and re-ran, I'd guess you would see a very "big" difference..
First_rows hint does not work
Pete, June 06, 2005 - 4:34 am UTC
Finally this query is moving. Your suggestion to Increase the sort_area_size to 1MB definitely reduced the time but not under a minute. The only way this can be further reduced is by getting the index on settlementdt to be used.But the problem here is the trunc. The way application uses this sql is it requires users to input both the date values for the between. The way you have suggested is if the user inputs 03/01/2004 and 03/03/2004 then use:
SettlementDt BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/04/2004','MM/DD/YYYY')-1/24/60/60
i.e increase by one day the end date and subtract.
1) What does subtracting 1/24/60/60 from the end date achieve?
2)How can we use this in our case(dynamic input from the user).
Thanks
June 06, 2005 - 7:26 am UTC
so the user inputs
03/01/2004 and 03/03/2004
you would then:
where dt between to_date( :1, 'mm/dd/yyyy')
and to_date( :2, 'mm/dd/yyyy')+1-1/24/60/60
the 1/24/60/60 is 1 second. you are getting all of the dates between
a) 03/01/2004 00:00:00 (midnight) and
b) 03/03/2004 23:59:59 (upto but not including the 4th
another way would be:
where dt between to_date( :1, 'mm/dd/yyyy')
and to_date( :2 || ' 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
First_rows hint doesnt work
Pete, June 06, 2005 - 4:11 pm UTC
To further improve the performance I had created a monthly partition on subsidy table on the settlementdt column.
After doing a thorough test on the effects of these changes(first_rows, partitioning,increasing sort_area_size) Im seeing a difference in the execution plan of the same sql with different input values.On subsidy table the the primary key is PMTID.The TEST3_SETTLE_PMTAUX index used below on subsidy table is created on (PMTID,Settlementdt)/
The execution plan for 03/01/04 and 03/04/04 returns 4 rows. Elapsed time 38 sec. The non-optimized version took 6 minutes.
Elapsed: 00:00:37.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=105277 Car
d=10 Bytes=840)
1 0 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349005
2 1 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349004
3 2 HASH JOIN* (Cost=105259 Card=992 Bytes=83328) :Q349004
4 3 TABLE ACCESS* (FULL) OF 'USERS' (Cost=1 Card= :Q349001
3 Bytes=15)
5 3 HASH JOIN* (Cost=105257 Card=12240 Bytes=966960) :Q349003
6 5 NESTED LOOPS* (Cost=102209 Card=50929 Bytes=300481 :Q349002
1)
7 6 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAUX' :Q349002
(UNIQUE) (Cost=351 Card=50929 Bytes=1273225)
8 6 TABLE ACCESS* (BY INDEX ROWID) OF 'PAYMENT' (Cost= :Q349002
2 Card=2498909 Bytes=84962906)
9 8 INDEX* (UNIQUE SCAN) OF 'XPKPaymentT' (UNIQUE) (C :Q349002
ost=1 Card=2498909)
10 5 TABLE ACCESS* (FULL) OF 'ACCOUNT' (Cost=2880 Card=1 :Q349000
037774 Bytes=20755480)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(SUM
(A1.C4),COUNT(A1.C4),SUM(DECODE(A1.C
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A2.C1 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3
6 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKBCPMT") */ A2."ACCTID" C0,A
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_FROM_SERIAL
Statistics
----------------------------------------------------------
15617 recursive calls
2066 db block gets
378656 consistent gets
111356 physical reads
144128 redo size
528 bytes sent via SQL*Net to client
996 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
59 sorts (memory)
2 sorts (disk)
3 rows processed
The execution plan for 03/04/04 and 03/15/05 returns 275 rows.Elapsed time 15 minutes( not under the one minute limit).
There's a lot of data in the monthly partitions for the year 2004 and 2005 as compared to the data for 03/01/04 and 03/04/04.
275 rows selected.
Elapsed: 00:15:20.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2083968 Ca
rd=331 Bytes=27804)
1 0 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352005
2 1 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352004
3 2 HASH JOIN* (Cost=2083690 Card=20215 Bytes=1698060) :Q352004
4 3 TABLE ACCESS* (FULL) OF 'USERS' (Cost=1 Card= :Q352001
3 Bytes=15)
5 3 HASH JOIN* (Cost=2083688 Card=249313 Bytes=19695727) :Q352003
6 5 TABLE ACCESS* (FULL) OF 'ACCOUNT' (Cost=2880 Card=1 :Q352000
037774 Bytes=20755480)
7 5 NESTED LOOPS* (Cost=2075666 Card=1037345 Bytes=612 :Q352002
03355)
8 7 PARTITION RANGE* (ITERATOR) :Q352002
9 8 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAU :Q352002
X' (UNIQUE) (Cost=976 Card=1037345 Bytes=25933625)
10 7 TABLE ACCESS* (BY INDEX ROWID) OF 'PAYMENT' (Cost= :Q352002
2 Card=2498909 Bytes=84962906)
11 10 INDEX* (UNIQUE SCAN) OF 'XPKPAYMENT' (UNIQUE) (C :Q352002
ost=1 Card=2498909)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C2 C0,SYS_OP_MSR(SUM
(A1.C5),COUNT(A1.C5),SUM(DECODE(A1.C
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SW
AP_JOIN_INPUTS(A2) */ A2.C1 C0,A1.C6
6 PARALLEL_FROM_SERIAL
7 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKBCPMT") */ A2."ACCTID" C0,A
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
11463 recursive calls
2429 db block gets
17304285 consistent gets
4994667 physical reads
219620 redo size
17219 bytes sent via SQL*Net to client
2238 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
28 sorts (memory)
2 sorts (disk)
275 rows processed
Looks like Im back to square one.
Thanks
June 06, 2005 - 5:50 pm UTC
can we see the *real* query -- no edits.
First_rows hint doesnt work
Pete, June 06, 2005 - 7:01 pm UTC
Im assuming you mean: no decoding the table names: Here's the as is version
SELECT /*+ FIRST_ROWS index(bcpmt XPKBCPMT ) */ TO_CHAR(SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(ProvId,1, PmtAmt, 0)) as ACHSubTot,
SUM(DECODE(ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(PmtAmt) AS Count,SUM(PmtAmt) as Total
FROM BCPmt,test_wFPmtAux, BCAcct, WF_BillerAux
WHERE WF_BillerAux.Masterbillerid = 3 and
BCPmt.LfcyclCd='SN' and
BCPmt.PmtId=test_WFPmtAux.PmtId
AND BCPmt.Acctid = BCAcct.Acctid
AND WF_BillerAux.Billerid = BCAcct.Billerid
AND SettlementDt BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/15/2005'|| ' 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
GROUP BY SettlementDt
/
Elapsed: 00:15:20.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2083968 Ca
rd=331 Bytes=27804)
1 0 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352005
2 1 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352004
3 2 HASH JOIN* (Cost=2083690 Card=20215 Bytes=1698060) :Q352004
4 3 TABLE ACCESS* (FULL) OF 'WF_BILLERAUX' (Cost=1 Card= :Q352001
3 Bytes=15)
5 3 HASH JOIN* (Cost=2083688 Card=249313 Bytes=19695727) :Q352003
6 5 TABLE ACCESS* (FULL) OF 'BCACCT' (Cost=2880 Card=1 :Q352000
037774 Bytes=20755480)
7 5 NESTED LOOPS* (Cost=2075666 Card=1037345 Bytes=612 :Q352002
03355)
8 7 PARTITION RANGE* (ITERATOR) :Q352002
9 8 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAU :Q352002
X' (UNIQUE) (Cost=976 Card=1037345 Bytes=25933625)
10 7 TABLE ACCESS* (BY INDEX ROWID) OF 'BCPMT' (Cost= :Q352002
2 Card=2498909 Bytes=84962906)
11 10 INDEX* (UNIQUE SCAN) OF 'XPKBCPMT' (UNIQUE) (C :Q352002
ost=1 Card=2498909)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C2 C0,SYS_OP_MSR(SUM
(A1.C5),COUNT(A1.C5),SUM(DECODE(A1.C
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SW
AP_JOIN_INPUTS(A2) */ A2.C1 C0,A1.C6
6 PARALLEL_FROM_SERIAL
7 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKBCPMT") */ A2."ACCTID" C0,A
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
11463 recursive calls
2429 db block gets
17304285 consistent gets
4994667 physical reads
219620 redo size
17219 bytes sent via SQL*Net to client
2238 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
28 sorts (memory)
2 sorts (disk)
275 rows processed
Here's the one that runs fast due to the input date 03/01/2004 and 03/04/2004:
SELECT /*+ FIRST_ROWS index(bcpmt XPKBCPMT ) */ TO_CHAR(SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(ProvId,1, PmtAmt, 0)) as ACHSubTot,
SUM(DECODE(ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(PmtAmt) AS Count,SUM(PmtAmt) as Total
FROM BCPmt,test_wFPmtAux, BCAcct, WF_BillerAux
WHERE WF_BillerAux.Masterbillerid = 3 and
BCPmt.LfcyclCd='SN' and
BCPmt.PmtId=test_WFPmtAux.PmtId
AND BCPmt.Acctid = BCAcct.Acctid
AND WF_BillerAux.Billerid = BCAcct.Billerid
AND SettlementDt BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004'|| ' 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
GROUP BY SettlementDt
/
Elapsed: 00:00:37.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=105277 Car
d=10 Bytes=840)
1 0 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349005
2 1 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349004
3 2 HASH JOIN* (Cost=105259 Card=992 Bytes=83328) :Q349004
4 3 TABLE ACCESS* (FULL) OF 'WF_BILLERAUX' (Cost=1 Card= :Q349001
3 Bytes=15)
5 3 HASH JOIN* (Cost=105257 Card=12240 Bytes=966960) :Q349003
6 5 NESTED LOOPS* (Cost=102209 Card=50929 Bytes=300481 :Q349002
1)
7 6 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAUX' :Q349002
(UNIQUE) (Cost=351 Card=50929 Bytes=1273225)
8 6 TABLE ACCESS* (BY INDEX ROWID) OF 'BCPMT' (Cost= :Q349002
2 Card=2498909 Bytes=84962906)
9 8 INDEX* (UNIQUE SCAN) OF 'XPKBCPMT' (UNIQUE) (C :Q349002
ost=1 Card=2498909)
10 5 TABLE ACCESS* (FULL) OF 'BCACCT' (Cost=2880 Card=1 :Q349000
037774 Bytes=20755480)
1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(SUM
(A1.C4),COUNT(A1.C4),SUM(DECODE(A1.C
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A2.C1 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3
6 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKBCPMT") */ A2."ACCTID" C0,A
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_FROM_SERIAL
Statistics
----------------------------------------------------------
15617 recursive calls
2066 db block gets
378656 consistent gets
111356 physical reads
144128 redo size
528 bytes sent via SQL*Net to client
996 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
59 sorts (memory)
2 sorts (disk)
3 rows processed
Thanks
June 07, 2005 - 7:38 am UTC
Ok, i my self am really confused on settlementdt. when you do use correlation names, it is appearing to come from different places:
SELECT TO_CHAR(payment.SettlementDt, 'MM/DD/YYYY') AS PmtDate,
...
FROM users, payment,subsidy, account
...
AND TRUNC(subsidy.SettlementDt) BETWEEN
TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt
for example -- but in any case, since the query is properly written now (no trunc), lose the hints all together.
And don't expect for radically differing amounts of retrieved data to have performance anywhere near "the same"
And use correlation names 100% of the time please with the real queries, it'll cut way down on confusion.
Are you sure you want or need PQ here?
First_rows hint not working
Pete, June 07, 2005 - 5:33 pm UTC
Thanks Tom for the response. The pQ is coming from the degree specified on the indexes when they were created. Should I noparallel the indexes.
Also Tom how do I get rid of the trunc on an equal to eg:
where trunc(settlmentdt)=TO_DATE('05/05/2005','MM/DD/YYYY')
June 07, 2005 - 6:08 pm UTC
i would put the parallel back to default for now, yes.
where settlementdt between tO_DATE('05/05/2005','MM/DD/YYYY') and tO_DATE('05/05/2005','MM/DD/YYYY')+1-1/24/60/60;
parallel degree at table level
A reader, June 17, 2005 - 3:05 am UTC
Hi
I am told that if I add parallel degree at table level and parallel_* parameters are enabled my queries running against this table might use full table scan instead of index scans. Is it true?
June 17, 2005 - 2:51 pm UTC
sure, that is the *point*
remember, do this
a) say out loud "indexes are all all goodness"
b) say out loud "full scans are not evil"
c) goto (a) until you believe what you've just said
If some queries did not start going parallel, I'd be disappointed.
The index scans might turn into parallel scans.
The index scan followed by table access by index rowids might turn into parallel full table scans.
all by design.
How is this possible?
Reader, September 27, 2006 - 8:37 am UTC
Hello Tom,
(a) How is this possible that a query with high cost is taking less time than the query with less cost? These are identical queries except in the first explain plan I have specified FIRST_ROWS.
(b) What does time = 07:17:59 and 00:06:58 exactly indicate?
Query 1
WITH FIRST_ROWS
Execution Plan
----------------------------------------------------------
Plan hash value: 2659234344
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 535K| 75M| 2189K (1)| 07:17:59 |
| 1 | NESTED LOOPS OUTER | | 535K| 75M| 2189K (1)| 07:17:59 |
| 2 | NESTED LOOPS OUTER | | 535K| 68M| 1116K (1)| 03:43:17 |
| 3 | NESTED LOOPS | | 535K| 59M| 579K (1)| 01:56:00 |
|* 4 | TABLE ACCESS FULL | T1 | 544K| 21M| 11924 (4)| 00:02:24 |
| 5 | TABLE ACCESS BY INDEX ROWID| T1_CURRENT | 1 | 75 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | T1_CURRENT_PK | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 17 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 13 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | T3_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query 2
WITHOUT ANY HINTS
Execution Plan
----------------------------------------------------------
Plan hash value: 3308101482
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 535K| 75M| | 34793 (4)| 00:06:58 |
|* 1 | HASH JOIN | | 535K| 75M| 43M| 34793 (4)| 00:06:58 |
|* 2 | HASH JOIN RIGHT OUTER | | 544K| 37M| 1416K| 27122 (4)| 00:05:26 |
| 3 | TABLE ACCESS FULL | T2 | 49916 | 828K| | 1266 (1)| 00:00:16 |
|* 4 | HASH JOIN RIGHT OUTER| | 544K| 28M| 16M| 24040 (4)| 00:04:49 |
| 5 | TABLE ACCESS FULL | T3 | 677K| 8594K| | 9886 (5)| 00:01:59 |
|* 6 | TABLE ACCESS FULL | T1 | 544K| 21M| | 11924 (4)| 00:02:24 |
| 7 | TABLE ACCESS FULL | T1_CURRENT | 544K| 38M| | 3228 (4)| 00:00:39 |
--------------------------------------------------------------------------------------------------
September 27, 2006 - 3:21 pm UTC
a) cost is only loosely related to runtime. when you hint, you hint, you are telling it to do something, you artificially touch the "cost"
b) one indicates a bit over 7 hours of time and the other a bit over 6 minutes of time.
Time taken
Reader, September 28, 2006 - 12:00 am UTC
Hello Tom,
Thanks for the reply.
Both the queries took almost the same time (around 28 - 30 minutes). Even though it shows in hours it ran in less than 30 minutes.
Please clarify.
Thanks
September 28, 2006 - 3:02 am UTC
I don't know what those random numbers you just pasted here are, because I cannot see them in CONTEXT at all. Hence I really have no comment about them at all.
No it's not random numbers
Reader, September 28, 2006 - 8:05 am UTC
Hello Tom,
It's not any random numbers. I executed quite a few times and checked the results. It takes around 28 - 30 minutes but the explain plan shows varying time taken for 2 queries.
Since I cannot prove my point you need to believe me. (Just kidding)
Any suggestion how can I find out the exact time it takes?
Here are the steps I did:
(1) set autotrace traceonly explain
(2) executed the query
Thanks
September 28, 2006 - 8:12 am UTC
ok, I see the numbers there now, they are ESTIMATES. They are guesses.
All things in explain plan are - estimated cardinality, estimated IO's, estimated CPU, estimated temp space, estimate time.
It is all "the guess"
I though you were cutting those from somewhere, I did not realize they were from the plan, that is all.
Time Taken
Reader, September 28, 2006 - 8:57 am UTC
Hello Tom,
Thanks for the reply.
Now how do I prove the point that FIRST_ROWS hint is taking more time to get the output than without any hint because both are taking more or less the same time but plan shows different time.
I analyzed the table using ANALYZE TABLE COMPUTE STATISTICS command
I suppose Optimizer must compute instead of estimating because I have computed the statistics.
Please advice what to do next?
Thanks
September 29, 2006 - 7:24 am UTC
(you should not use analyze)
it would appear that if they run with the same amount of time, you cannot prove that particular point, since it "just ain't so"
don't know what to tell you to do, don't know what your goal is exactly.
Why cost is high using ALL_ROWS
Sachin, March 03, 2007 - 4:14 am UTC
Hi Tom,
I gave a mview with 294065 rows. there is an application query whidh takes more time when using default ALL_ROWS mode.
1* select /*+ FIRST_ROWS */ COUNTRY_ID from motif_ip_address where start_address <= 3280902159 and end_address >= 3280902159
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 691077897
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 18018 | 16 (0)| 00:00:01 |
|* 1 | MAT_VIEW ACCESS BY INDEX ROWID| MOTIF_IP_ADDRESS | 1001 | 18018 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IPENDADDRESSINDEX | | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("START_ADDRESS"<=3280902159)
2 - access("END_ADDRESS">=3280902159)
SQL> select /*+ ALL_ROWS */ COUNTRY_ID from motif_ip_address where start_address <= 3280902159 and end_address >= 3280902159;
Execution Plan
----------------------------------------------------------
Plan hash value: 1104178588
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40100 | 704K| 398 (4)| 00:00:05 |
|* 1 | MAT_VIEW ACCESS FULL| MOTIF_IP_ADDRESS | 40100 | 704K| 398 (4)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("END_ADDRESS">=3280902159 AND "START_ADDRESS"<=3280902159)
The result of the query is just one row.
SQL> select COUNTRY_ID from motif_ip_address where start_address <= 3280902159 and end_address >= 3280902159;
COUNTRY_ID
----------
3457
If ALL_ROWS is for best throughput then why it does a full scan when index is existing on START_ADRESS ?
Thanks in advance,
Sachin
FIRST_ROWS in 10GR2
Rajeshwaran, Jeyabal, March 28, 2012 - 7:42 am UTC
Tom:
I was reading about the
A Slow-Return Query from
Effective oracle by design and got same results for both FIRST_ROWS and FIRST_ROWS(1) Hints in 10.2.0.1.0. I dont see the output like your's is that something got changed in 10g optimizer?
declare
cursor c is select /*+ first_rows */ * from big_table order by id;
l_rec big_table%rowtype;
begin
open c ;
fetch c into l_rec;
close c;
end;
/
declare
cursor c is select /*+ first_rows(1) */ * from big_table order by id;
l_rec big_table%rowtype;
begin
open c ;
fetch c into l_rec;
close c;
end;
/
rajesh@10GR2>
rajesh@10GR2> select index_name, column_name
2 from user_ind_columns
3 where table_name ='BIG_TABLE'
4 /
INDEX_NAME COLUMN_NAM
---------- ----------
BIG_IDX ID
BIG_IDX_02 STATUS
Elapsed: 00:00:00.48
rajesh@10GR2>
rajesh@10GR2>
Tkprof shows me this.
SELECT /*+ first_rows */ *
FROM
BIG_TABLE ORDER BY ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 1.25 2.46 1134 15112 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.25 2.46 1134 15112 2 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 100 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=15112 pr=1134 pw=15096 time=2460054 us)
1031220 TABLE ACCESS FULL BIG_TABLE (cr=15112 pr=1120 pw=0 time=51 us)
********************************************************************************
SELECT /*+ first_rows(1) */ *
FROM
BIG_TABLE ORDER BY ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 1.73 4.43 3060 15112 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.73 4.43 3060 15112 2 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 100 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=15112 pr=3060 pw=15096 time=4435055 us)
1031220 TABLE ACCESS FULL BIG_TABLE (cr=15112 pr=3046 pw=0 time=1031254 us)
March 28, 2012 - 9:27 am UTC
is that something got changed in 10g optimizer?
probably - they are different code bases, I would not expect identical results - there are many things in that book that will turn out slightly different in 10g and 11g - in fact, I wouldn't be surprised to see slightly different results in 9i than 10g than 11g (three different results)
but something is whacky here - unless the primary key constraint is missing on ID, that should have used the index.
please provide your complete test case.
FIRST_ROWS in 10GR2
Rajeshwaran, Jeyabal, March 28, 2012 - 12:25 pm UTC
Tom:
As you said defined primary key on ID column in big_table and re-run the Script & Tkprof shows me this.
SELECT /*+ first_rows */ *
FROM
BIG_TABLE ORDER BY ID
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 1 0.00 0.03 3 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.03 3 4 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 100 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=4 pr=3 pw=0 time=30618 us)
1 INDEX FULL SCAN BIG_IDX (cr=3 pr=2 pw=0 time=322 us)(object id 182059)
********************************************************************************
SELECT /*+ first_rows(1) */ *
FROM
BIG_TABLE ORDER BY ID
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 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 100 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=4 pr=0 pw=0 time=31 us)
1 INDEX FULL SCAN BIG_IDX (cr=3 pr=0 pw=0 time=22 us)(object id 182059)rajesh@ORA10GR2> drop index big_idx;
Index dropped.
Elapsed: 00:00:00.57
rajesh@ORA10GR2>
rajesh@ORA10GR2> create unique index big_idx
2 on big_table(id) nologging;
Index created.
Elapsed: 00:00:14.78
rajesh@ORA10GR2>
rajesh@ORA10GR2> alter table big_table
2 add constraint pk_big_table
3 primary key(id) using index big_idx
4 /
Table altered.
Elapsed: 00:00:08.26
rajesh@ORA10GR2>
rajesh@ORA10GR2> @tktrace.sql
Session altered.
Elapsed: 00:00:00.01
Session altered.
Elapsed: 00:00:00.10
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
2 cursor c is select /*+ first_rows */ * from big_table order by id;
3 l_rec big_table%rowtype;
4 begin
5 open c ;
6 fetch c into l_rec;
7 close c;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
2 cursor c is select /*+ first_rows(1) */ * from big_table order by id;
3 l_rec big_table%rowtype;
4 begin
5 open c ;
6 fetch c into l_rec;
7 close c;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> @tkfilename.sql
RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'
------------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.1.0\ADMIN\ORA10GR2\UDUMP/ORA10GR2_ora_4012.trc
Elapsed: 00:00:00.06
rajesh@ORA10GR2>
rajesh@ORA10GR2> exit
March 28, 2012 - 12:29 pm UTC
and that is what we would expect to see.
stick with FIRST_ROWS in 10g? rather than FIRST_ROWS_N
Rajeshwaran, Jeyabal, November 21, 2012 - 9:24 am UTC
Tom,
We have some queries in our application, where FIRST_ROWS hint works as Index Range scan + Nested loops (excellent response time) where FIRST_ROWS(20) produces Full Table scan and Hash joins (takes more that 10 mins).
We are in 10g (10.2.0.5). Below is my test case to help you out.
1) Is that we need to still move with FIRST_ROWS in 10g rather than FIRST_ROWS(n) hint?
drop table t1 purge;
create table t1 as select * from all_objects;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
insert /*+ append */ into t1 select * from t1;
commit;
exec dbms_stats.gather_table_stats(user,'T1');
create index t1_ind on t1(object_id,owner) nologging;
In Oracle 10g database, i see this.rajesh@ORA10G> select /*+ first_rows */ * from t1 where object_id > 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 464K| 41M| 464K (1)| 01:32:57 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 464K| 41M| 464K (1)| 01:32:57 |
|* 2 | INDEX RANGE SCAN | T1_IND | 464K| | 1398 (1)| 00:00:17 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">5)
rajesh@ORA10G> select /*+ first_rows(25) */ * from t1 where object_id > 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 2350 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 25 | 2350 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">5)Where as in Oracle 11g, i see this.rajesh@ORA11G> select /*+ first_rows */ * from t1 where object_id > 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 568K| 52M| 570K (1)| 01:54:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 568K| 52M| 570K (1)| 01:54:03 |
|* 2 | INDEX RANGE SCAN | T1_IND | 568K| | 1727 (1)| 00:00:21 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">5)
rajesh@ORA11G> select /*+ first_rows(25) */ * from t1 where object_id > 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 2522 | 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 26 | 2522 | 29 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IND | | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">5)
rajesh@ORA11G>
November 21, 2012 - 11:01 am UTC
what is wrong with the full scan? I'd say it is perfection.
In fact, the index plan looks wrong to me.
it'll have to read a single database block to get the first 25 rows when object_id > 5!
rather than possibly up to 25 database blocks and 3 index blocks to retrieve it via the index....