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 it
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 HP-U
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
Ora
Rick
"Mercadante,
Thomas F" To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
ate.ny.us> Subject: RE: sql tuning help
dante,
Thomas F" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
ate.ny.us> Subject:
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
That is
"Mark J.
Bobak" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
t> Subject: Re: sql tuning help
e-
> > From: Robson, Peter [SMTP:[EMAIL PROTECTED]]
> > Sent: Friday, December 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 su
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!
Lisa Koivu
Oracle Database Supermom to 4 Boys.
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA
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 p.phy_id
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) use_
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
ark J.
Bobak" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
t> Subject:
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
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 condi
check out the status in v$sess
Regards,
Sathyanarayanan
|+--->
|| "Sergei" |
|||
|| |
|| 27/11/2002 |
|| 00:24|
|| Please |
> 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
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
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 t
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,
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 '1
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
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 to_char(NVL(SUM(bet_amo
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 < add_months(T
Try turning the subquery into a join like this. Also make sure you've got sufficient hash_area_size to do a hash join instead of a sort/merge join. 20m should be plenty. If you have to, put a use_hash hint on the query to force it to a hash join.
SELECT to_char(NVL(SUM(bet_amount),0))FROM sb_bet
Try one of these variations
SELECT TO_CHAR(NVL(SUM(sb.bet_amount),0))
FROM sb_betssb
,customers c
WHERE sb.processed_date >= ADD_MONTHS(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1)
AND sb.processed_date < add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19)
AND s
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
c
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
28 matches
Mail list logo
