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
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
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
26 matches
Mail list logo
