Home » RDBMS Server » Performance Tuning » Hint - First_Row
Hint - First_Row [message #157813] Mon, 06 February 2006 06:16 Go to next message
vban2000
Messages: 207
Registered: March 2005
Senior Member
Dear Gurus

I have a distinct on a join sql statment, when I run it took 4 minutes, when I put /*FIRST_ROW*/ hint for it, it took about 18 seconds. In addition, consistent gets has gone down by 26%.

I am a Newbie in the SQL Tuning.. I would like to know what does the FIRST_ROW do to make SQL run faster.

Also, when using a hint, is it always best to have anlaysed the table first.

If possible, could anyone provide me the direction on the tuning the SQL, website or manual.

Many Thanks in Advance
AnDy
Re: Hint - First_Row [message #157815 is a reply to message #157813] Mon, 06 February 2006 06:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Depends on your need.
if your application is paginating, FIRST_ROWS may help.
http://www.oracledba.co.uk/tips/9i_first_rows.htm
http://asktom.oracle.com/pls/ask/f?p=4950:8:4214877806566408799::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064

Re: Hint - First_Row [message #157818 is a reply to message #157813] Mon, 06 February 2006 06:54 Go to previous messageGo to next message
vban2000
Messages: 207
Registered: March 2005
Senior Member
Hi Mahesh

Thank you for the rapid response.

Actually, I am not sure if the following is 'paginating'?

Here is what I try to do (with HINT..)
emp has 81853 records and paypoint has 4025

SQL> SELECT          /*+ FIRST_ROWS */
  2         DISTINCT SUBSTR (mindeptcd, 1, 2) || sect_cd ,
  3                  NVL
  4                     (d.costcentre,
  5                       get_para ('COSTCENTRE', SYSDATE)
  6                     ) cost_centre
  7             FROM emp a, paypoint d
  8            WHERE a.pypcd = d.pypcd
  9              AND a.mincd = d.pyp_mincd
 10              AND a.mindeptcd = d.pyp_sectcd
 11  /

313 rows selected.

Elapsed: 00:00:18.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=359 Card=1
           Bytes=54)

   1    0   SORT (UNIQUE) (Cost=359 Card=1 Bytes=54)
   2    1     HASH JOIN (Cost=354 Card=1 Bytes=54)
   3    2       TABLE ACCESS (FULL) OF 'paypoint' (Cost=7 Card=47
          38 Bytes=123188)

   4    2       TABLE ACCESS (FULL) OF 'emp' (Cost=316 Card=2
          67421 Bytes=7487788)


Statistics
----------------------------------------------------------
      81827  recursive calls
          0  db block gets
     166975  consistent gets
          5  physical reads
          0  redo size
       8353  bytes sent via SQL*Net to client
       1116  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        313  rows processed



Now, without the HINT..
313 rows selected.

Elapsed: 00:04:09.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'PAYPOINT'
   5    3         INDEX (RANGE SCAN) OF 'EMP_MINCD_I' (NON-UNIQUE)




Statistics
----------------------------------------------------------
      81826  recursive calls
          0  db block gets
    4385813  consistent gets
          0  physical reads
          0  redo size
       8527  bytes sent via SQL*Net to client
       1116  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        313  rows processed


Now, when I applied the following
ANALYZE TABLE ANDY.EMP COMPUTE STATISTICS;

The SQL completed in 16 seconds.. So, back to one of the question When using a hint, is it always best to have anlaysed the table first? (It seems yes to me - but is this always true?? or anything I have to lookout..)

By the way, which table should I look for my current optimizer_mode? I cannot find in the V$parameter.

Many Thanks & Regards
AnDy
Re: Hint - First_Row [message #157821 is a reply to message #157818] Mon, 06 February 2006 07:11 Go to previous messageGo to next message
Gerardo Fernandez Herrera
Messages: 58
Registered: January 2006
Location: Montevideo, Uruguay
Member
Quote:

By the way, which table should I look for my current optimizer_mode? I cannot find in the V$parameter.



SQL> select value from v$parameter where name = 'optimizer_mode';

VALUE
--------
CHOOSE

Re: Hint - First_Row [message #157824 is a reply to message #157818] Mon, 06 February 2006 07:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Asktom link clearly explains it.
In simple ( Not so technically correct ) words,
pagination is where you browse through a result set from X rows to y rows.
With first_rows, oracle fetches the first set of N rows very fast and the rest with lesser priority ( very useful when you generating a 100 page report or say the first 100 records of the 10000 record resultset)

Look into V$parameter or just "show" it.
scott@9i > show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE


Usually hints are supposed to be used as the last resort.
Whether using hints or not, You need to analyze the table!.
And use DBMS_STATS.
Try this (without hints) and please post the results.
First, Gather table stats for both the tables involved EMP and PAYPOINT (like this) and then try your query.
scott@9i > exec dbms_stats.gather_table_stats('SCOTT','EMP',method_opt=>'for all indexed columns',cascade=>true);

PL/SQL procedure successfully completed.


[Updated on: Mon, 06 February 2006 07:48]

Report message to a moderator

Re: Hint - First_Row [message #157828 is a reply to message #157813] Mon, 06 February 2006 08:18 Go to previous messageGo to next message
vban2000
Messages: 207
Registered: March 2005
Senior Member
Thanks Gerardo.

Many Thanks Mahesh.. http://www.orafaq.com/forum/images/message_icons/icon14.gif

WOW! now the time elapsed and consistent gets are the same, with or without HINT.

[b][color=blue]SQL> REM -- With the Hint .. [/color][/b]
SQL> 
SQL> SELECT          /*+ FIRST_ROWS */ 
  2         DISTINCT SUBSTR (mindeptcd, 1, 2) || sect_cd, 
  3                  NVL 
  4                     (d.costcentre, 
  5                      get_para ('COSTCENTRE', SYSDATE) 
  6                     ) cost_centre 
  7             FROM emp A, paypoint d 
  8            WHERE A.pypcd = d.pypcd 
  9              AND a.mincd = d.pyp_mincd 
 10              AND a.mindeptcd = d.pyp_sectcd
 11  /

281 rows selected.

[b][color=red]Elapsed: 00:00:17.03[/color][/b]

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1000 Card=
          16803 Bytes=470484)

   1    0   SORT (UNIQUE) (Cost=1000 Card=16803 Bytes=470484)
   2    1     HASH JOIN (Cost=340 Card=81853 Bytes=2291884)
   3    2       TABLE ACCESS (FULL) OF 'PAYPOINT' (Cost=8 Card=40
          25 Bytes=64400)

   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=319 Card=81853
          Bytes=982236)





Statistics
----------------------------------------------------------
      81858  recursive calls
          0  db block gets
[b][color=red]     167073  consistent gets[/color][/b]
        879  physical reads
          0  redo size
       7391  bytes sent via SQL*Net to client
       1032  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        281  rows processed

[b][color=blue]SQL> REM -- Without the Hint .. [/color][/b]
SQL> 
SQL> SELECT DISTINCT SUBSTR (mindeptcd, 1, 2) || sect_cd,
  2                  NVL (d.costcentre,
  3                       get_para ('COSTCENTRE', SYSDATE)
  4                      ) cost_centre
  5             FROM emp A, paypoint d
  6            WHERE A.pypcd = d.pypcd
  7              AND a.mincd = d.pyp_mincd
  8              AND a.mindeptcd = d.pyp_sectcd
  9  /

281 rows selected.

[b][color=red]Elapsed: 00:00:17.03[/color][/b]

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1000 Card=16803 Byte
          s=470484)

   1    0   SORT (UNIQUE) (Cost=1000 Card=16803 Bytes=470484)
   2    1     HASH JOIN (Cost=340 Card=81853 Bytes=2291884)
   3    2       TABLE ACCESS (FULL) OF 'PAYPOINT' (Cost=8 Card=40
          25 Bytes=64400)

   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=319 Card=81853
          Bytes=982236)





Statistics
----------------------------------------------------------
      81857  recursive calls
          0  db block gets
[b][color=red]     167073  consistent gets[/color][/b]
        716  physical reads
          0  redo size
       7550  bytes sent via SQL*Net to client
       1032  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        281  rows processed

SQL> 


Some question still pondering.. When (or how often) should the Statistics be generated, weekly or only when there is major change in the table (e.g. lot of rows got inserted or updated, deleted...)? Is there a general rule on this one??

When should HINT be used?

just read here... Analyze and DBMS_STATS (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4347359891525) which stated DBMS_STAS is better and more accurate.

Many Thanks & Best Regards
AnDy

[Updated on: Mon, 06 February 2006 08:23]

Report message to a moderator

Re: Hint - First_Row [message #157831 is a reply to message #157828] Mon, 06 February 2006 08:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> When (or how often) should the Statistics be generated,
>> weekly or only when there is major change in the table (e.g. lot of rows got inserted or updated, deleted...)?
>> Is there a general rule on this one??

Whenever there is a big change in data ( and when you expect the plan to change).
Infact if you are happy with the current plan, you can even have it as a standard.
Please look here.

http://www.orafaq.com/forum/t/58303/42800/

>>When should HINT be used?
http://asktom.oracle.com/pls/ask/f?p=4950:8:18022720145153134059::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7038986332061
Re: Hint - First_Row [message #157891 is a reply to message #157813] Tue, 07 February 2006 01:06 Go to previous messageGo to next message
vban2000
Messages: 207
Registered: March 2005
Senior Member
Many Thanks Mahesh for answering the question and great reference on the articles.

Re: Hint - First_Row [message #157954 is a reply to message #157813] Tue, 07 February 2006 08:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Mahesh, as a heads up, watch out with the dbms_stats command you gave above -- regarding bucket size of the histograms. Defaults may be very version dependent, but it appears to be safer to, as a starting point, always specify 'size 254' in there. Otherwise you might end up with only one bucket.
Re: Hint - First_Row [message #157959 is a reply to message #157954] Tue, 07 February 2006 08:41 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Indeed!. very true. Agreed.

[Updated on: Tue, 07 February 2006 08:42]

Report message to a moderator

Previous Topic: bad view - slow reports
Next Topic: SQL Query with aggregate function taking long time to execute
Goto Forum:
  


Current Time: Thu Mar 28 04:08:38 CDT 2024