Title: Query Tuning Help
I'm trying to tune the following query to use an index on the FILE_DTS column, rather than a FTS on the CLASS_CONFIG table (~350,000 rows).
SELECT a2.class_config_id, a1.schedule_name
FROM class_config a2, class_schedule a1
WHERE a2.class_config_id = a1
because its not always faster to use an index. try using a hint for the index and see
which runs better.
From: David Wagoner [EMAIL PROTECTED]
Date: 2003/10/27 Mon AM 10:34:26 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Query Tuning Help
I'm trying to tune
Title: Query Tuning Help
David,
The
calculation "( SYSDATE - 35)" is not causing the problem. The
To_Date(a2.file_dts, 'mmddyyhh24miss') is.
You
said you created a function based index. I think you also need to
set:
Query_ReWrite_Integrity =
TRUSTEDQuery_ReWrite_Enabl
Title: Query Tuning Help
Tom,
Thanks
for the init.ora parameter tips, I consulted the docs and did that first
:-). It just seems that the CBO would rather use an index, even though I
know that's not always the case.
Best regards,
David B. Wagoner Database Administrator Arsenal Digital
Do you have query rewrite privilege? What is the
query_reqrite_inegrity set to? How about optimizer parameters
(optimizer_index_caching,optimizer_index_cost_adj)? Is everything
analyzed?
On 10/27/2003 10:34:26 AM, David Wagoner wrote:
I'm trying to tune the following query to use an index on
Title: Query Tuning Help
The
FILE_DTS column is VARCHAR2(12) NOT NULL and has data in the following
format: 07220301.
Best regards,
David B. Wagoner Database Administrator Arsenal Digital
Solutions Phone: 919-466-6723
Cell: 919-412-8462
Pager: [EMAIL PROTECTED]
Fax: 919-466-6783
E
David,
I would probably not try to tune a query to make it use an index but tune a query to
make it run faster - I have had recently a nice example, a join between a 500K row
table and two 2K row tables (returning about 2K rows too) was running faster with FTS
(followed by hash joins) on
Title: RE: Query Tuning Help
Mladen,
Thanks for your response. Comments are in-line.
Do you have query rewrite privilege?
Yes.
What is the query_reqrite_inegrity set to?
TRUSTED.
How about optimizer parameters (optimizer_index_caching,optimizer_index_cost_adj
PMFJI
How about optimizer parameters
(optimizer_index_caching,optimizer_index_cost_adj)?
optimizer_index_caching=0 Are these reasonable values?
optimizer_index_cost_adj=100
sorry for the space everyone ms outlook 101 is a class I *badly* need
like how do I reply in plain text with
Hi all,
I have been struggling with the follwing query for hours with no avail.
This is oracle 8.1.7.4 on Tru64 unix with a data warehouse setup. Invc_line is the
fact table with about 267 mil records.
There is a bitmap index on shipto_key(invc_line_bix04) and another bitmap index on
]
inal.comcc:
Sent by: Subject: Query tuning stumper
[EMAIL PROTECTED
[EMAIL PROTECTED]
inal.comcc:
Sent by: Subject: Query tuning
stumper
[EMAIL PROTECTED]
.com
16/07/2003 06:49
Please respond
PROTECTED]
Subject: Query Tuning Question
Hi fellow DBAs,
This is kind of the follow-up of my last E-mail on wait event.
I have a query that is taking hours to complete and the plan looks ok. While one of
the tables is huge (267mil rows) it is being accessed using one of its indexes.
I recorded
[EMAIL PROTECTED]
Date: 2003/06/12 Thu PM 03:54:59 EDT
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Subject: Query Tuning Question
Hi fellow DBAs,
This is kind of the follow-up of my last E-mail on
wait event.
I have a query that is taking hours to complete
Title: RE: Query Tuning Question - new discovery
A Ha ...
it is refers to _B_TREE_BITMAP_PLANS variable ... it is true by default and what you see is the side effect. If you are not using BMI, set it to false.
http://tinyurl.com/e8ws for more info
Raj
Hi
fellow DBAs,
This
is kind of the follow-up of my last E-mail on wait event.
I have
a query that is taking hours to complete and the plan looks ok. While one of the
tables is huge (267mil rows) it is being accessed using one of its
indexes.
I
recorded some stats from v$session_wait
PROTECTED]
Date: 2003/06/12 Thu PM 03:54:59 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Query Tuning Question
Hi fellow DBAs,
This is kind of the follow-up of my last E-mail on wait event.
I have a query that is taking hours to complete and the plan looks ok. While
What could be the cause of this wait?
At the risk of asking the bleeding obvious, have you tried doing a 10046
trace on the query?
Gudmundur
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gudmundur Bjarni Josepsson
INET: [EMAIL PROTECTED]
Fat City Network Services
BDY.RTF
Description: RTF file
[EMAIL PROTECTED] wrote:
The following qry takes large amt of time to retrieve data on production
database.
Reason being for a single row in psd, there are multiple records in
piar_fr_psd representing diff parties.
How to optimise this qry .
Select CUST_BAS_NO,BR_COD,CUST_NAM,BR_NAM
Hi Listers,
I have the below query which is taking almost 3 min. to execute.
Can anybody help me in rewriting the query and/or reducing the query
execution time. Please note that V_SHIP is a view created the source for
which is as as follows:
View Source
CREATE OR REPLACE VIEW V_SHIP (
I have the Oracle Performance Tuning 101 book and I have been reading the Performance
Tuning Guide on OTN, however, I found its explanation of when to use messages and how
to write queries extremely terse. Are there any websites or books which focus strictly
on query tuning that provide more
found its explanation of when to use messages and how
to write queries extremely terse. Are there any websites or books which focus strictly
on query tuning that provide more in depth explanations and examples?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL
to
use messages and how to write queries extremely terse. Are there any
websites or books which focus strictly on query tuning that provide more in
depth explanations and examples?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: [EMAIL PROTECTED]
INET: [EMAIL
encoded content removed -- binaries not allowed by ListGuru
The previous attachment was filtered out by the ListGuru mailing
software at fatcity.com because binary attachments are not appropriate
for mailing lists. If you want a copy of the attachment which was
removed, contact the sender
Title: Message
Hello
list.
I have recently been
tasked with trying to optimize some slow performing queries (Oracle 8.1.7) for
an applicationthat generates reports in a data warehouse type
environment. I have noticed in most of the queries that the table names
have been aliased, but not
Wendy - I think the difference between using an alias or not is negligible.
My reasoning is that this would be easy to test (good idea if you have a
moment) and there are enough picky Oracle developers that if this was not
negligible, people would have been bragging about this as their secret
Subject: Query tuning with tablename
alias
Sent by:
[EMAIL PROTECTED
Title: RE: Query tuning with tablename alias
but if you provide hints on such statements, you better be using aliases for hints .
Aliases are used for readability ... you either use the aliases or user tablename.column but not both ... world is already confusing enough ...
Raj
.
Hopper, Wendy
S To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
wendy.hopper@ cc:
eds.com Subject: Query tuning with
tablename alias
:
"DUAL"."DUMMY": invalid identifier
SQL select dual.dummy from dual;
D-X
Waleed
-Original Message-From: Hopper, Wendy S
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 20, 2002
1:13 PMTo: Multiple recipients of list ORACLE-LSubject:
Query tuning with tab
To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
wendy.hopper@ cc:
eds.com Subject: Query tuning with
tablename alias
Sent by:
[EMAIL PROTECTED
this properly).
Regards,
Mark.
Hopper, Wendy
S To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
wendy.hopper@ cc:
eds.com Subject: Query tuning
S To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
wendy.hopper@ cc:
eds.com Subject: Query tuning with
tablename alias
Sent
Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle
slapped the data back in just a second. Thanks everyone for the ideas to
try.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, September 10, 2002 3:42
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
AND so.code = 11
AND sa.ret = 'SB'
Dennis,
If you use the ordered hint and have sa then so then am and also hint to use
the index on sa(ret) then I think that would be about the best as you'd be
starting with the best filter ie 1.3m/281 giving less than 5000 on average
(assuming ret is indexed). I don't know if you'd have to
DENNIS WILLIAMS wrote:
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
:
Subject:SQL Query tuning help
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
:SQL Query tuning help
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
Thanks everyone for your wonderful suggestions. And thanks for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't change often, so
that may be a good alternative.
Iain - Thanks so much for the detailed suggestions.
Rick - Good sanity
Just in case anyone out there is interested, we use the term Mickey
Mouse schema to refer to a very specific design tactic. We're *not*
using the term's slang meaning of unimportant or uninspired.
(...Which always seemed odd to me, because MM is a really strong, high
quality brand.)
Subject: RE: SQL Query tuning help
Thanks everyone for your wonderful suggestions. And thanks
for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't
change often, so
that may be a good alternative.
Iain - Thanks so much
Hello Folks,
Given an Oracle 7.3.4 database, how would you tune a query as under, other
than suggesting a migration to a higher version. This query is currently
performing a lot of I/O, obviously doing a full tablescan on CAMPMAIN.
SELECT CAMPNAME,ASGNMTTYPE,CAMPRTGNUM, LTRIM(RTRIM(CAMPTYPE))
[EMAIL PROTECTED] wrote:
Hello Folks,
Given an Oracle 7.3.4 database, how would you tune a query as under, other
than suggesting a migration to a higher version. This query is currently
performing a lot of I/O, obviously doing a full tablescan on CAMPMAIN.
SELECT
]
sfaroult@oricc:
ole.com Subject: Re: Query Tuning
Sent
: 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 SchlumbergerSema Email :
[EMAIL PROTECTED]
[EMAIL PROTECTED
]]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
EPABX : +44 (0) 115 - 957 6418
Ext. 76217 Fax
: +44 (0) 115 - 957
6018
-Original Message-From: Greg Moore
[mailto:[EMAIL PROTECTED]]Sent: 08 November 2001
18:28To: SARKAR, SamirSubject: Re: SQL query tuning
problem
syntax for having multiple tables in the hint
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arich Henneman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
good
knowledge about SQL query tuning in Oracle. Please let me know any books are
available on it also.
Makarand
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Makarand Kurkure
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538
Hi All,
I want to know what are the sources on Internet which gives me good
knowledge about SQL query tuning in Oracle. Please let me know any books are
available on it also.
Makarand
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Makarand Kurkure
INET
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arich Henneman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Hi,
We have a query which is taking a long time as compared to time taken last
month..
this query does a select on view1..this view1 is based on join oof 2 views
view2 and view3..
view2 is based on join of 6 tables while view3 is based on 9 tables..with
each table have significant number or
Title: RE: Imedia query tuning
Hello,
Thanks
Lisa for your comment but.
I try
to always use an index hint to return the rows in the required
order. This avoids a sort
if
it's possible to use an index which can avoid both sort and table access - and
it often won't be possible
Dear DBA Gurus,
I have the following two queries along with execution plan and statistics.
The first one is taking a long time to execute. The second query is taking
a long time to execute when I use the order by clause. I am also sending
the table details. Is there any way I can
Dear DBA Gurus,
I have the following two queries along with execution plan and statistics.
The first one is taking a long time to execute. The second query is taking
a long time to execute when I use the order by clause. Is there any way I
can reduce the execution time as these queries
-
Van: Ranganath K [mailto:[EMAIL PROTECTED]]
Verzonden: woensdag 16 mei 2001 13:56
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Imedia query tuning
Dear DBA Gurus,
I have the following two queries along with execution plan and
statistics.
The first one is taking a long time
Hello,
I'm not a guru : I just try to help you. :-
Hint for Query 2 :
Have you try to hint so you run on the editor_choice index. If it
works. Can you remove the Order command. Because the index is already
ordered.
Regards
Henrik E.
On Wed, 16 May 2001, Ranganath K wrote:
Dear DBA Gurus,
Title: RE: Imedia query tuning
Henrik, FYI
The ordered hint refers to the order in which the tables are accessed in the query. It has nothing to do with an index.
Lisa Rutland Koivu
Oracle Database Administrator
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton
Riyaj Re-yas Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
Koivu, Lisa [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/16/01 09:36 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Imedia query
I just thought I should advertise this... usually people just complain.
Hi Patrice,
I have added indexes on the foreign keys in the [schema_name]
tables. Also, I tuned the SQL statements I was using to perform the
desired deletes.
Results: The one delete job that ran for
62 matches
Mail list logo
