Hi Tracy,
A few observations
1. Can we use a more selective where clause?( Currently we
are going for a PK Range Scan. Can we change the where clause to go for a PK
Uniq scan?)
2. The Fetch time is very high.I guessthe fetch
is a single row fetch. We can tune the code for bulk fetch by
Tracy,
Take a look at the thing calling this 43,814
times. Can this query be used as an inline view for the thing using this querys
result set? If so, then youll eliminate 87,629 database calls.
As Tom Kyte says, Tune the
QUESTION, not the query.
Cary Millsap
Hotsos Enterprises,
it depends on how the code is written ... maybe it is doing row
operations ... care to show the code (at-least pseudo code)
??
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
Comments in line.
On 01/22/2004 12:24:26 PM, Tracy Rahmlow wrote:
This statement is from a batch program within a pl/sql procedure.
(Also, I
have many similar ones within the process) The policy table has
approximately 6.2 million rows. The procedure is to
incrementally(daily)
build an extract
Since you're doing an aggregate function, you may want to investigate
using materialized views here. Since, I'm assuming, policy
effective dates aren't something that changes on a minute-to-minute
basis, you could set up a materialized view that refreshed every night
and would answer this
Venu,
Do your 10046 tracing and run it through tkprof of the Hotsos Profiler and you'll know
exactly why your query is taking so long.
Regards,
Gudmundur
Can anyone tell me whats wrong
with the explain plan below#8230; this update is running for quite long time#8230;.
Even without a single
without knowing the requirement,index,statistics it is very hard to
tell.
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Venu GopalSent:
Thursday, December 18, 2003 5:29 AMTo: Multiple recipients of list
ORACLE-LSubject: SQL tuning...
Dennis,
I tend to treat use of hints for exceptional cases only. With
collections CASTed as tables, I seem to have a generalized problem of
tables involved being scanned FULL (not using the available indexes)
and query response being slow. I can't seem to build a query with
collections and have
Sundeep,
Have you reviewed Tim Gorman's paper titled: 'The Search For Intelligent Life In The
Cost-Based Optimizer'? Check it out at http://www.evdbt.com. It may help.
- Kirti
-Original Message-
Sent: Wednesday, February 05, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L
Sundeep - Have you tried other hints, like FIRST_ROWS? Or are you trying to
avoid hints entirely?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, February 05, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L
Oracle 8.1.7.4 on
Hamid Alavi wrote:
Hi List,
Is anybody have any idea for better performance for the following query:
I just change the OR to UNION ALL
Appreciate any idea.
SELECT a.evh_event_id
FROM event_history_evh a
WHERE
(a.evh_event_id = 2 AND
At 11:26 AM 12/18/2002 -0800, Jibo John wrote:
Hello DBAs,
I am currently involved in improving the search performance for a tool
which queries a table having a million records (and the table is growing
at a rate of 3000 records per day).
So sounds like, in the next year, it will at least 2
Michael, thanks for responding to my question.
I understand that the possible solution would be to go with partitioning.
Already started looking into that :)
Thanks again,
-Jibo
Sounds like your hitting the scalability barrier for context searches,
whatever that may be. I am assuming
Mercadante,
Thomas F To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
ate.ny.us Subject: RE: sql tuning help
Sent
My first thought would be that the plan shows a full table scan for
phy_boards for each in-line view. This is almost certainly due to the
'pb.expiration_Date is null' condition in the where clause of each
in-line view. Since Oracle does not store NULLs in an index (except for
bitmaps), that
Hmmm - this is a Friday afternoon, you know. My suggestion is to forget
it until Monday - don't spoil your weekend
peter
edinburgh
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 06 December 2002 12:54
To: Multiple recipients of list ORACLE-L
.
Bobak To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
t Subject: Re: sql tuning help
Title: RE: sql tuning help
Something like this might help ...
SELECT p.phy_id
,CASE WHEN (b.description LIKE 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' AND cnt 0 ) THEN 'X' ELSE ' ' END
,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF ANESTHESIOLOGY%' AND cnt 0 ) THEN 'X' ELSE
I think the problem (without really getting into how they coded this) is the
nested-nested loops.
try this hint:
/*+ no_merge use_hash(board_aaps) use_hash(board_aba) use_hash(board_abem)
use_hash(board_abfp) use_hash(board_abim) use_hash(board_abp)
use_hash(board_abr) use_hash(board_aobem)
Rick,
does this return *any* records at all? the only reason that I ask is that
in the 'where' clause, it is saying:
where p.phy_id = board_aaps.phy_id (+)
and p.phy_id = board_aba.phy_id (+)
and p.phy_id = board_abem.phy_id (+)
and p.phy_id = board_abfp.phy_id (+)
and
Title: RE: sql tuning help
That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow
Lisa Koivu
Oracle Database Supermom to 4 Boys.
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
06, 2002 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: sql tuning help
Hmmm - this is a Friday afternoon, you know. My suggestion is
to
forget
it until Monday - don't spoil your weekend
peter
edinburgh
-Original Message
.
Bobak To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
t Subject: Re: sql tuning help
Sent by:
[EMAIL PROTECTED
Title: RE: sql tuning help
bad
news for me I guess - doing a Production Install right now!
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 06, 2002
11:29 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: sql tuning help
,
Thomas F To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
ate.ny.us Subject: RE: sql tuning help
Title: RE: SQL tuning help
Sergei,
How many records in each table? What indexes are in these tables? What version of Oracle?
What do you mean by 'began to hang'?
I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id.
You could always
Sergei,
When the query is running try to collect some stats, especially session
waits, from v$session_wait and see where the waits are happening.
Or you could do this from command line
alter session set event '10046 trace name context forever, level 8';
your query
alter session set event
Remove the group by clause. It does nothing.
Also, if the cardinality for subsite_id in the table tmp_brian_metareward1
is low,
you may use a PL/SQL block instead of a single update statement.
HTH, Krishna
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sergei,
By hang I'm going to assume that I can replace that with the phrase
running really slow. If it is actually hanging then I think a call to
Oracle is in order.
I'm guessing that perhaps statistics were updated or one of the tables
changed in size enough to convince the optimisor to take a
Title: RE: SQL tuning help
Let
me clearfy.
I am running Oracle 8.1.6 on solaris 8
Fastcash has 50M
record
tmp_brian_metareward1 has 600
records.
I was able to tune the query so it runs fast now.
I created a combined index on subsite_id
and attempt and I added a hint
UPDATE
This week it began to hang and I can't figure out why.
The first thing I would check are locks. The statement is trying to update
a table. Try something like the following while the statement appears to be
hung. These are two different ways (and certainly not the only ways) of
checking for
check out the status in v$sess
Regards,
Sathyanarayanan
|+---
|| Sergei |
|| sergei@netfl|
|| ip.com |
|| |
|| 27/11/2002 |
|| 00:24
:
Sent by: Subject: RE: SQL Tuning - How to avoid
TOCHAR function against a date
[EMAIL PROTECTED]
om
recipients of list ORACLE-L
[EMAIL PROTECTED]
tery.orgcc:
Sent by: Subject:
RE: SQL Tuning - How to avoid TOCHAR function
against a date
I have not got a system to test this out on at the moment but can you do a
substr on the to_char so that the format matches the date_key
Something like substr((TO_CHAR(:b1,'DD-MON-'),11)
John
-Original Message-
[EMAIL PROTECTED]
Sent: 08 April 2002 18:57
To: Multiple recipients of
Cherie,
Couldn't you do
SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE = trunc(:b1)
and oracle_date trunc(:b1) + 1
which should at least give a range scan.
Iain Nicoll
-Original Message-
Sent: Monday, April 08, 2002 6:57 PM
To: Multiple recipients of list ORACLE-L
I've got the
Could you maybe calculate a range of date values that encompasses the period
you want and use BETWEEN on the raw date column? I'm thinking something
along the lines of:
SELECT DATE_KEY
FROM DATE_DIM
WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ;
but like, more elegant.
by:Subject: RE: SQL Tuning - How to
avoid TOCHAR function against a date
[EMAIL PROTECTED
I don't think you can do it.. I mean, you could change it to trunc the
oracle_date field (that eliminates the minutes) and then do a to_date
of :b1 but you will still be operating on the oracle_date field.
Okay, I HATE to suggest this, but since the table is small:
add another field to the
]
anais.com Subject: RE: SQL Tuning - How to avoid
TOCHAR function against a date
04/08/02 12:37
Something like:
WHERE ORACLE_DATE between trunc(:b1) and trunc(:b1) + 1 - 1/(24*60*60)
-Original Message-
Sent: Monday, April 08, 2002 12:57 PM
To: Multiple recipients of list ORACLE-L
I've got the following SQL statement that is running very long on a
nightly
data load. The
Cherie,
How about using the TRUNC function on the date field. That will use
only thre ,MM,DD of the ORACLE_DATE column. Then you will be
comparing like columns without going through the to_char conversion.
WHERE TRUNC(ORACLE_DATE) = TRUNC(:b1)
Ron
ROR mª¿ªm
[EMAIL PROTECTED] 04/08/02
Ron,
the TRUNC function will also prevent the use of an index on the oracle_date
column.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, April 08, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
Cherie,
How about using the TRUNC function on the
let's face it Rachel, the date column is probably incorrect as the table was
designed. knowing that it is important in queries, and that the minutes
cause problems during query, your suggestion should have been incorporated
in the original design (or truncing the oracle_date field via a
I suppose if you wanted to collect statistics about hourly usage, then
the minutes info would be necessary
but then, most people don't think about how they really want to use the
date when they add a date field
--- Mercadante, Thomas F [EMAIL PROTECTED] wrote:
let's face it Rachel, the date
Tom,
I realize that there would not be an index but I was trying to
eliminate some overhead by using the TRUNC function as compaired to the
to_char for the fields.
Cherie,
If the table is not to large how about pinning it to save on disk
reads?
Ron
ROR mª¿ªm
[EMAIL PROTECTED] 04/08/02
To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
[EMAIL PROTECTED] cc:
ate.ny.us Subject: RE: SQL Tuning - How to avoid
TOCHAR function
If the tables are all analyzed, and cost-based optimizer is enabled,
the order in the FROM clause does not matter (it would matter only if
you added an /*+ ORDERED(...) */ hint.
I'm not sure what you mean by your question about guidelines in the
predicate. I assume you're trying to tune the
Greg Moore wrote:
That's a great SQL tuning presentation, but I don't follow what he says in
slide #91:
---
No WHERE clause with an ORDER BY
Often a screen will return rows with a predefined order, e.g., ORDER BY
NAME. The user is expected to
Create index containing 3 columns in the view. After that this query will
read only index and not table - fast full index scan - should be much
faster - especially if you are on 8.1.6 or higher and can use compress -
CASE_WIP2WIPBIN is a very good candidate. I would created index like -
create
]
treas.gov cc:
Sent by: Subject: RE: SQL tuning /
optimization problem - IS NOT NULL -
[EMAIL PROTECTED] Clarify
Hello,
I think, there are 3 factors in performance tuning:
- Time
- Amount
- Speed
Most imporatnt factor in performance tuning is the time. Of course others are
important, too. But, others are indirect indicator. For example:
- 1 block 1000 ms
- 1000 block 1 ms
As we see above, second one
Matt,
have you tried replacing the IN statement with an EXISTS
statement?
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning help
I have been having
Here's
some thoughts.
I
don't know if between is faster, but it might be, and won't a straight join do
the same as your subquery?
SELECT
to_char(NVL(SUM(bet_amount),0))
FROM
sb_bets
WHERE
processed_DATE between add_months( .) and
add_months( ..) AND
Try joining the 2 tables as below. I think the IN will do a FTS always.
Correct me if I'm wrong.
Rick
SELECT to_char(NVL(SUM(a.bet_amount),0))
FROM sb_bets a, customer b
WHERE a.processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1)
AND a.processed_DATE
Instead of customer_id in...try
where exists (select 'x' from customers
where sb_bets.customer_id = customers.customer_id
and sb_bets.customer_id and LICENSEE_ID=6130)
[EMAIL PROTECTED] 02/07/01 08:30AM
I have been having some problems with this statement
SELECT
56 matches
Mail list logo
