| Hint - First_Row [message #157813] |
Mon, 06 February 2006 06:16  |
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 #157818 is a reply to message #157813] |
Mon, 06 February 2006 06:54  |
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 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
vban2000
Messages: 207
Registered: March 2005
|
Senior Member |
|
|
Thanks Gerardo.
Many Thanks Mahesh.. 
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 #157954 is a reply to message #157813] |
Tue, 07 February 2006 08:18 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
