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
Oracle 8.1.7.4 on HP-UX
I am using collections to pass multiple values from client to the
database to gather values for more than input values. The queries
produce the results but the without the RULE hint the response is
dramatically slower. Following is just one of the examples but I have
many
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
Hi,
Oracle 8.1.6 NT 4.0
I have a rather complex query a developer gave to me to try to improve
performance.
There are 3 tables used. All relevant columns used are indexed. The tables
have been analyzed
SQLWKS select count(*) from physicians;
COUNT(*)
--
340043
1 row selected.
SQLWKS
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
Subject: sql tuning help
Hi,
Oracle 8.1.6 NT 4.0
I have a rather complex query a developer gave to me to try to improve
performance.
There are 3 tables used. All relevant columns used are
indexed. The tables
have been analyzed
SQLWKS select count(*) from physicians;
COUNT
.
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
Hello everybody,
I have the following query that runs every week.
UPDATE tmp_brian_metareward1 tmp
SET offers_seen = (SELECT count(f.fastcash_id) FROM
metareward.fastcash f
WHERE f.subsite_id = tmp.subsite_id
and attempt =
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]
by: Subject: SQL tuning help
[EMAIL PROTECTED]
om
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
|
|| |
|+---
--|
| |
| To: Multiple recipients of list ORACLE-L |
| [EMAIL PROTECTED] |
| cc: (bcc: Sathyanaryanan K/VGIL) |
| Subject: SQL tuning help |
--|
Hello
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19-1)
AND processed_DATE
add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19)
AND
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
( ..) AND
customers.customer_id = sb_bets.customer_id AND
customer.licensee_id = 6130;
Dan
"Just my thought"
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning hel
Tuning help
I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1) AND processed_DATE add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19
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
30 matches
Mail list logo
