| 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
Henry,
I remember reading some reviews of it when
it came out and thinking I would like it. I presume it includes function based
indexes (indices?), more on partitioning etc.
I also recall the book being about £40 UK
whereas other comparable sized books such as Jonathan Lewis's one are normally
around £33 UK.
Looking at Amazon now both books are still
available.
In the review section somebody has commented
about the tools provided on CD as being pretty non-robust. That reminds me that
I never managed to get any of them working which was a bit of a shame. Thinggs
have moved on since then and I have found alternative tools or scripts to do the
same job.
John
-----Original
Message-----From: Henry Poras
[mailto:Henry.Poras_at_ctp.com]Sent: 19 November 2001
14:25To: Multiple recipients of list ORACLE-LSubject:
FW: SQL query tuning problem
John,
What has changed? I think we have both
versions floating around here somewhere (unless one of the books left with
some of our people). I'll try to take a stroll through the Table of
Contents.
Henry
<FONT
face=Tahoma>-----Original Message-----From: Hallas John
[mailto:John.Hallas_at_btcellnet.net]Sent: Monday, November 19, 2001
5:45 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: SQL query tuning
problem
I have the first edition which is an excellent book but I cannot justify buying the later version - pity really
-----Original
Message-----From: SARKAR, Samir
[mailto:Samir.SARKAR_at_nottingham.sema.slb.com]Sent: 16 November
2001 15:50To: Multiple recipients of list
ORACLE-LSubject: 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 <FONT color=#000080
face=Impact>Schlumberger<FONT color=#800000
face=Impact>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com <FONT
color=#000080 face=Verdana
size=1>
samir.sarkar_at_sema.co.uk <FONT color=#000080
face=Verdana size=1>Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115 -
957 6418 Ext. 76217 <FONT color=#000080
face=Verdana size=1>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="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
----- Original Message -----
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:
<A href="mailto:Samir.SARKAR_at_nottingham.sema.slb.com"
title=Samir.SARKAR_at_nottingham.sema.slb.com>SARKAR, Samir
To: <A
href="mailto:sqlgreg_at_pacbell.net" title=sqlgreg_at_pacbell.net>'Greg
Moore'
Cc: <A
href="mailto:'ORACLE-L_at_fatcity.com'"
title=ORACLE-L_at_fatcity.com>'ORACLE-L_at_fatcity.com'
Sent: Thursday, November 08, 2001
2:04 AM
Subject: RE: SQL query tuning
problem
<FONT
size=3>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 ??
<FONT
size=3>
Thanks
again,
Samir Sarkar
Oracle DBA - Lennon
Team <FONT color=#000080
face=Impact>Schlumberger<FONT color=#800000
face=Impact>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com <FONT
color=#000080 face=Verdana
size=1>
samir.sarkar_at_sema.co.uk <FONT color=#000080
face=Verdana size=1>Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0) 115
- 957 6418 Ext. 76217 <FONT color=#000080
face=Verdana 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="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
----- Original Message -----
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:
<A href="mailto:Samir.SARKAR_at_nottingham.sema.slb.com"
title=Samir.SARKAR_at_nottingham.sema.slb.com>SARKAR, Samir
To: <A
href="mailto:sqlgreg_at_pacbell.net" title=sqlgreg_at_pacbell.net>'Greg
Moore'
Cc: <A
href="mailto:'ORACLE-L_at_fatcity.com'"
title=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
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)
<FONT
face=Courier> 0 <FONT
face=Courier><SPAN
class=497161811-07112001> &
nbsp; TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
<FONT
face=Courier>
<FONT
face=Courier> '<FONT
face=Courier>VALID_SEGMENT’
<FONT
face=Courier> 0
<FONT
face=Courier>INDEX (RANGE
SCAN) OF ‘IX_VALID_SEGMENT’
(UNIQUE)
<FONT
face=Courier>
0
SORT
(JOIN)
<FONT
face=Courier><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><FONT
face=Courier>
0
INDEX (RANGE SCAN) OF ‘IX_VALID_PR_UNIT’
(UNIQUE)
<FONT
face=Courier><FONT
face=Courier>
0
INDEX (UNIQUE SCAN) OF ‘IX_VALID_TRANS’
(UNIQUE)
<FONT
face=Courier><FONT
face=Courier>
0
SORT (AGGREGATE)
<FONT
face=Courier><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 color=#000080
face=Impact>Schlumberger<FONT color=#800000
face=Impact>Sema
Email :
samir.sarkar_at_nottingham.sema.slb.com
<FONT color=#000080 face=Verdana
size=1> s
amir.sarkar_at_sema.co.uk <FONT
color=#000080 face=Verdana size=1>Phone : +44 (0) 115 - 95
76217 <FONT color=#000080 face=Verdana
size=1>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: 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.___________________________________________________________________________<FONT
size=3>**********************************************************************This
email and any attachments may be confidential and the subject oflegal
professional privilege. Any disclosure, use, storage or copyingof this
email without the consent of the sender is strictly prohibited.Please
notify the sender immediately if you are not the intendedrecipient and
then delete the email from your inbox and do notdisclose the contents to
another person, use, copy or store theinformation in any
medium.**********************************************************************
Received on Wed Nov 21 2001 - 09:41:13 CST
 |
 |