Hi, Cherie,
Sorry for the delayed reply. Yes, I already did, and analyzed the table,
too. But, logically, will it do any good given that one of the columns in
join condition has NULLs only, the other being everything but NULL?
Anyway, I'll test it again. Thanks for your reply.
Best & have a great weekend.
Sergey
-----Original Message-----
Sent: Friday, January 25, 2002 2:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Session_wait
Sergey,
Have you considered adding an index to that queried column in table B?
Many third-party vendors allow the DBA to add indexes even when they
won't allow them to alter the code.
Something to consider.
Cherie Machler
Oracle DBA
Gelco Information Network
"Babich ,
Sergey" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<SBabich@hande cc:
xmail.com> Subject: RE: Session_wait
Sent by:
[EMAIL PROTECTED]
om
01/25/02 12:31
PM
Please respond
to ORACLE-L
Hi, Ross,
This has been running for about 4 hours now. I got an SQL trace file, and
looked at the execution plans. So here's the deal. There's an interesting
join condition "...where A.col1=B.col1.....". However, A has a few hundred
distinct values in that column, none of them being NULL, and B, which has a
few hundred thousand rows, has ALL NULLs in the corresponding column, and
that column is not indexed, too. That's the query where it sits for a
couple
of hours. Guess what the optimizer is doing (8i)? I think internal effects
are secondary in this scenario. It is the production (including the
database) designed by the company named DELTEK, so nobody can change the
code. Anyway, I reported my findings...
Thank you very much for your help, it's always appreciated.
Best,
Sergey
-----Original Message-----
Sent: Friday, January 25, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
This is a busy little beaver...how long you say this runs?
opened cursors cumulative 1072
session logical reads 77233609
db block gets 2642119
consistent gets 74591490
physical reads 148822
db block changes 3005410
consistent changes 141
no work - consistent read gets 72058049
table scans (short tables) 210918
table scans (long tables) 36
table scan rows gotten 798264962
table scan blocks gotten 71788386
table fetch by rowid 1074164
leaf node splits 4018
execute count 74445
bytes sent via SQL*Net to client 59650
bytes received via SQL*Net from client 84233
SQL*Net roundtrips to/from client 342
buffer is not pinned count 73513922
I see some updates (or inserts) and heavy reads...at first glance,
though, you don't appear to be I/O throttled but likely have inefficiencies
in buffer cache. Pin some small tables? Reexamine access paths for
fts, even if on "small" tables (generally defined to be around
5% in blocks of buffer cache size?)and consider seeking out
and destroying nested loops joins in favor of hash joins. There's
more CPU, but less buffer cache splashing around....don't forget to
review init.ora settings for hash, buffer pools, and query planning.
(...be happy to do a flyover of those as well, if you like.)
That's about all i can get in a one minute glance, but there are alot of
people on the list who'll see more. Look to them for longer posts with
more explanation.
Good Luck!
- Ross
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
