RE: Query Tuning Help

PMFJI >>How about optimizer parameters (optimizer_index_caching,optimizer_index_cost_adj)? optimizer_index_caching=0 < Um no probably not. O_i_c says (in Niall english) what percentage of my index blocks are likely to be in memory rather than on disk. The *baseline* for this is BCHR (or say

RE: Query Tuning Help

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_cach

RE: Query Tuning Help

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 _ALL

RE: Query Tuning Help

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

Re: Query Tuning Help

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 o

RE: Query Tuning Help

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 Di

RE: Query Tuning Help

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 = TRUST

Re: Query Tuning Help

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

Query Tuning Help

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

Re: Query tuning stumper

quot;Meng, Dennis" > <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > inal.com>cc: > Sent by: Subject: Query tuning stumper &g

Re: Query tuning stumper

ORACLE-L <[EMAIL PROTECTED]> inal.com>cc: Sent by: Subject: Query tuning stumper

Query tuning stumper

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 BILLG_DT_K

RE: Query Tuning Question - new discovery

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

RE: Query Tuning Question - new discovery

a move on the table > to rebuild it and possibly rebuilding the indexes in > question. > > have you gather statistics lately? Is it using the > same plan it was using a fwe weeks ago? > > > > > > From: "Meng, Dennis" <[EMAIL PROTECTED]> > > D

RE: Query Tuning Question - new discovery

> 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 one of > t

Re: Query Tuning Question

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

Re: Query Tuning Question

eng, Dennis" <[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 qu

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 some stats from v$session_wait whil

Re: Query Tuning urgent

[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_N

Query Tuning urgent

BDY.RTF Description: RTF file

Slow query tuning

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 (

RE: Query Tuning Documentation

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 directly and ask for it to be sent to you by private E-mail. This

RE: Query Tuning Documentation

ound 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

RE: Query Tuning Documentation

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: <[EM

Query Tuning Documentation

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 in

RE: Query tuning with tablename alias

"Hopper, Wendy S" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Query tuning with tablename alias Sent by:

RE: Query tuning with tablename alias

cal can explain this properly). Regards, Mark. "Hopper, Wendy S" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject:

RE: Query tuning with tablename alias

S" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Query tuning with tablename alias Sent by: [EMAIL PROTECTED]

RE: Query tuning with tablename alias

ED]]Sent: Wednesday, November 20, 2002 1:13 PMTo: Multiple recipients of list ORACLE-LSubject: Query tuning with tablename alias Hello list.   I have recently been tasked with trying to optimize some slow performing queries (Oracle 8.1.7) for an application that generates

RE: Query tuning with tablename alias

Regards, Mark. "Hopper, Wendy S" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Query tuning with tablename alias

RE: Query tuning with tablename alias

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

Re: Query tuning with tablename alias

"Hopper, Wendy S" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Query tuning with tablename alias

RE: Query tuning with tablename alias

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 metho

Query tuning with tablename alias

Title: Message Hello list.   I have recently been tasked with trying to optimize some slow performing queries (Oracle 8.1.7) for an application that 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

RE: SQL Query tuning help

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] -Original Message- Sent: Tuesday, September 10, 2002 3:4

RE: SQL Query tuning help

ist ORACLE-L > 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

RE: SQL Query tuning help

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.) Specifi

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 for the detailed suggestions. Rick - Good sanity ch

RE: SQL Query tuning help

of list ORACLE-L 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_k

Re: SQL Query tuning help

D]> cc: 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

Re: SQL Query tuning help

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.ac

RE: SQL Query tuning help

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 thro

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 AND so.code = 11 AND sa.ret = 'SB'

Re: Query Tuning

Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Query Tuning Sent by:

Re: Query Tuning

[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 CAMPNAME,ASGNM

Query Tuning

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)) F

RE: SQL query tuning problem

inggs 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:[EMAIL PROTECTED]]Sent: 19 November 2001 14:25To: Multiple recipients of list ORACLE-LSubject: FW: SQL query tuning problem

FW: SQL query tuning problem

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: S

RE: SQL query tuning problem

: 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

RE: SQL query tuning problem

) 115 - 95 76217 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 mu

RE: SQL query tuning problem

ssage-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   /*+ full( a b ) */   Look in the Oracle documentation, in the Tuning Guide.  There is a

RE: Oracle Query Tuning

ember 06, 2001 1:46 AM To: Multiple recipients of list ORACLE-L 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 F

RE: Oracle Query Tuning

s 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: [EMAIL PROTECTED] Fat City Network Services-- (858)

Re: Oracle Query Tuning

-- 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 -

Re: Oracle Query Tuning

-- 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 -

Oracle Query Tuning

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

QUERY TUNING

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

RE: Imedia query tuning

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 poss

RE: Imedia query tuning

       Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>         cc:                 Subject:        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

RE: Imedia query tuning

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

Re: Imedia query tuning

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

RE: Imedia query tuning

---Oorspronkelijk bericht- 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

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 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

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 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 reduce

Impact of query tuning

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 fo