| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL query tuning problem
Well Greg......I just ordered
the SQL Tuning book by Guy Harrison from Amazon....
hope it is real good as u
recommend :)
Thanks and Cheers
!!
Samir Sarkar
Oracle DBA - Lennon
Team Schlumberger<FONT
face=Impact color=#800000>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com <FONT face=Verdana
color=#000080
size=1>
samir.sarkar_at_sema.co.uk <FONT face=Verdana
color=#000080 size=1>Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115 - 957 6418
Ext. 76217 Fax
: +44 (0) 115 - 957
6018
<FONT face=Tahoma
size=2>-----Original Message-----From: Greg Moore
[mailto:sqlgreg_at_pacbell.net]Sent: 08 November 2001
18:28To: SARKAR, SamirSubject: Re: SQL query tuning
problem
>> syntax for having multiple tables in the hint
/*+ full( a b ) */
Look in the Oracle documentation, in the Tuning Guide. There is a
complete chapter on Hints.
If you are interested in tuning, get Guy Harrison's book. It's
available on Amazon. It's one of the best Oracle books ever
written.
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Greg,
Thanks a bunch for ur
detailed reply. I am trying out all the options enumerated by you and
I will hopefully get some
results.
I was doing a mistake with
the hint to disable indexes.....I was using the table name instead of the
alias in the hint.
Could u please tell me the
syntax for having multiple tables in the hint ?? Tahat is, if I want
the query to run by
disabling the indexes in all the joined tables, what would the syntax be
??
Thanks
again,
Samir Sarkar
Oracle DBA - Lennon
Team <FONT face=Impact
color=#000080>Schlumberger<FONT face=Impact
color=#800000>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com <FONT
face=Verdana color=#000080
size=1>
samir.sarkar_at_sema.co.uk <FONT face=Verdana
color=#000080 size=1>Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115 - 957
6418 Ext. 76217 <FONT face=Verdana color=#000080
size=1>Fax : +44 (0) 115 - 957
6018
<FONT face=Tahoma
size=2>-----Original Message-----From: Greg Moore
[mailto:sqlgreg_at_pacbell.net]Sent: 07 November 2001
20:09To: SARKAR, SamirSubject: Re: SQL query tuning
problem
Samir,
To disable an index, use the hint FULL in the SQL.
select /*+ full(e) */ ename, phone, address
from emp e
where ename = 'Smith'
This will cause a full table scan on the emp table (instead of using
an index). Note you must give the table an alias in the FROM clause,
and you must use that alias in the hint. Also, the hint must have no
errors. If it contains errors you won't get an error message.
So after putting in a hint, be sure to run an explain plan to see if your
hint worked. For the FULL hint, obviously, your explain plan should
show a full table scan and should not show use of an index.
The explain plan you sent is very interesting. The words "MERGE
JOIN (CARTESIAN)" are very interesting. Normally a Cartesian join is
a mistake, because the programmer forgot to include a join between two
tables in the WHERE clause. In your case, however, the tables appear
to be all properly joined.
Oracle enhanced the optimizer for data warehouses, where there is
usually one big table an many smaller ones. The smaller tables can't
be joined to each other, only to the large table. However, the most
efficient solution is for Oracle to first join all the small tables, and
since they can't be linked with primary and foreign keys, Oracle just does
a Cartesian join. This produces lots of results, but not too many
because the tables are all small. Then, for the last step, Oracle
finally joins the results from these small tables to the big table.
The idea is to put off dealing with the big table until the end, and
then only deal with it once. This is faster than joining each small
table to the big table, one at a time.
Although this was meant for data warehouses, sometimes Oracle will
create a plan like this simply because it sees that one table is very big
and the others are small. I think that is what is happening with
your SQL and explain plan.
I am sorry to report that even with the proper indentation, I cannot
fully understand your explain plan. It is simply different from what
I am used to seeing and I don't understand how a couple of the steps work
together.
I can, however, offer some ideas for things to try:
(1) Use timed_statistics=true and then run a trace and
TKPROF. Then look in the TKPROF report at the very left of the
explain plan. There is a column that says ROWS. Right now it
contains all zero's! This column has very useful information -- it
tells how many rows were processed by each step in the plan. Often
you can use this information to isolate the step where things are going
wrong because you see too many rows being processed. Often this is
an indication that an index needs to have a column added, or some other
change needs to be made. At least you see where lots of work is
being done, so you can focus your efforts on that step.
(2) It will be interesting to try your idea of using the FULL
hint instead of indexes. I hope you are able to turn on
timed_statistics, if only for your session, so you can see complete TKPROF
reports when you try various hints, so you can see all the things that
change.
(3) The major thing that jumps out at me is the last line of
the explain plan, where there is a full table scan on
valid_pricing_unit. I'm reasonably sure that this step is the inner
part of the nested loops step that appears near the top of the plan.
This means that Oracle is looping through the valid_pricing_unit table
again and again, and each time it's a full table scan! If you had
timed_statistics=true then you could see if this is causing trouble,
because each full scan means all the rows are processed, so if you are
looping through that table thousands of times, the ROWS column would show
a very large number. But even without that information, this seems
suspicious, unless the table is very small. Is there an index on
this table? What columns? Is it on the columns used in your
WHERE clause? Does it also include any columns from this table in
the SELECT list? If it includes all the columns in the SQL, Oracle
can use the index alone and not use the table, and this might be the best
solution of all, unless this table is very small.
That's all from me. Have fun!
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
----- Original Message -----
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:
<A title=Samir.SARKAR_at_nottingham.sema.slb.com
href="mailto:Samir.SARKAR_at_nottingham.sema.slb.com">SARKAR, Samir
To: <A title=sqlgreg_at_pacbell.net
href="mailto:sqlgreg_at_pacbell.net">'Greg Moore'
Cc: <A title=ORACLE-L_at_fatcity.com
href="mailto:'ORACLE-L_at_fatcity.com'">'ORACLE-L_at_fatcity.com'
Sent: Wednesday, November 07, 2001
3:40 AM
Subject: RE: SQL query tuning
problem
Hi
Greg,
<FONT
size=3>
Thanks for
replying.......I have reformatted the Explain Plan statement which is
appended below
:
<FONT
size=3>
<FONT
size=3>
<FONT
face=Courier size=2>Rows Execution
Plan
<FONT
size=3>----------
----------------------------------------------------------------------------------------------------------------------------------------------
<SPAN
class=497161811-07112001><FONT
size=2><SPAN
class=497161811-07112001> 0
INSERT STATEMENT GOAL:
CHOOSE
<FONT
size=3> <FONT
face=Courier
size=2>0 <FONT
face=Courier size=2>FILTER
<FONT
face=Courier> 0
SORT ( GROUP BY )
<FONT
face=Courier> 0
NESTED LOOPS<SPAN
class=497161811-07112001>
<SPAN
class=497161811-07112001> 0
MERGE JOIN
(CARTESIAN)
0
<SPAN
class=497161811-07112001> &
nbsp; TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
<FONT
face=Courier>
<FONT
face=Courier> '<FONT
face=Courier>VALID_SEGMENT’
0
<FONTF
ace="Courier">INDEX (RANGE SCAN) OF
‘IX_VALID_SEGMENT’ (UNIQUE)
<FONT
face=Courier>
0
SORT (JOIN)
<FONT
face=Courier>
0
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
<FONT
face=Courier><FONT face=Arial
size=3> <FONT face=Courier
size=2>
‘VALID_PRICING_UNIT’
<FONT
face=Courier>
0
INDEX (RANGE SCAN) OF ‘IX_VALID_PR_UNIT’
(UNIQUE)
<FONT
face=Courier>
0
INDEX (UNIQUE SCAN) OF ‘IX_VALID_TRANS’
(UNIQUE)
<FONT
face=Courier>
0
SORT (AGGREGATE)
<FONT
face=Courier>
0
TABLE ACCESS GOAL: ANALYZED (FULL) OF
<FONT
face=Courier><FONT
face=Courier>
‘VALID_PRICING_UNIT’
<FONT
face=Courier><FONT
face=Courier>
<FONT
face=Courier>Also, if I want
to disable the indexes on this query, could u please tell me how do I
provide a hint here ??
Sorry to ask this but I
am not very conversant with Application Query Tuning.
<FONT
face=Courier>Any help would be greatly
appreciated.
Samir Sarkar
Oracle DBA - Lennon
Team <FONT face=Impact
color=#000080>Schlumberger<FONT face=Impact
color=#800000>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com <FONT
face=Verdana color=#000080
size=1> s
amir.sarkar_at_sema.co.uk <FONT face=Verdana
color=#000080 size=1>Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115 -
957 6418 Ext. 76217 <FONT face=Verdana
color=#000080 size=1>Fax : +44 (0) 115 - 957
6018
<FONT face=Tahoma
size=2>-----Original Message-----From: Greg Moore
[mailto:sqlgreg_at_pacbell.net]Sent: 06 November 2001
20:42To: SARKAR, SamirSubject: SQL query tuning
problem
Samir,
(1) The TKPROF report is incomplete because your init.ora
parameter TIMED_STATISTICS is not set to true. This is why your
report shows elapsed time = 0, etc. Issue
alter system set timed_statistics=true
This allows you to use TKPROF and see the complete TKPROF
report. If you don't do this the TKPROF reports don't show much
useful information, so you might as well just be using explain
plan.
(2) Your explain plan is difficult to read because in your
email the indentation is messed up. Can you run an explain plan
on this statement and then send it using a font in your email
that will preserve the spacing, such as courier
font?<FONT
size=3>___________________________________________________________________________This
email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented
are solely those of the author and do not necessarily represent
those of SchlumbergerSema. If you are not the intended
recipient, be advised that you have received thisemail in error and
that any use, dissemination, forwarding, printing, or copying of
this email is strictly prohibited.If you have received this
email in error please notify the SchlumbergerSema Helpdesk by telephone
on +44 (0) 121 627
5600.___________________________________________________________________________
Received on Fri Nov 16 2001 - 11:30:01 CST
 |
 |