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!
----- Original Message -----
Sent: Wednesday, November 07,
2001 3:40 AM
Subject: RE: SQL query tuning
problem
Hi
Greg,
Thanks for
replying.......I have reformatted the Explain Plan statement which
is
appended below
:
Rows Execution
Plan
----------
----------------------------------------------------------------------------------------------------------------------------------------------
0
INSERT STATEMENT GOAL:
CHOOSE
0 FILTER
0
SORT ( GROUP BY )
0
NESTED LOOPS
0
MERGE JOIN
(CARTESIAN)
0 &
nbsp; TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
'VALID_SEGMENT’
0
INDEX (RANGE
SCAN) OF ‘IX_VALID_SEGMENT’ (UNIQUE)
0
SORT
(JOIN)
0
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF
‘VALID_PRICING_UNIT’
0
INDEX (RANGE SCAN) OF ‘IX_VALID_PR_UNIT’
(UNIQUE)
0
INDEX (UNIQUE SCAN) OF ‘IX_VALID_TRANS’
(UNIQUE)
0
SORT (AGGREGATE)
0
TABLE ACCESS GOAL: ANALYZED (FULL) OF
‘VALID_PRICING_UNIT’
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.
Any help would be greatly
appreciated.
Samir Sarkar
Oracle DBA -
Lennon Team
SchlumbergerSema
Email :
[EMAIL PROTECTED]
s
[EMAIL PROTECTED]
Phone : +44 (0) 115 - 95 76217
EPABX : +44 (0)
115 - 957 6418 Ext. 76217
Fax : +44 (0) 115 - 957
6018
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?
___________________________________________________________________________
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 this
email
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.
___________________________________________________________________________
