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 Subject: RE: Session_wait 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).
