Hi Anne,
Expected rows - my impression is the number of rows it will return. However, if you are using set autotrace traceonly explain, I don't know how it could know that. So take that number with a grain of salt.
Cardinality - number of distinct values.
It would probably be best if you do the full explain plan/tkprof explain plan from the command line and send the output to the list. I can't speak for what the gui tools are giving you. I don't trust them.
like this - in sql*plus:
alter session set sql_trace = true;
run your query.
alter session set sql_trace = false;
find the trace file it created. It will be in your udump directory, I believe. Don't remember off the top of my head which dir it will be in - one of the dumps.
tkprof it, find the explain plan in the tkprof and forward it to us. That will tell us a lot more.
btw, we'll get a lot more info if timed_statistics = true.
Lisa
Signature Queen.
Ft. Lauderdale, FL, US
**friend?** really? I thought a majority of the people on this list considered me and my tacky sigs an annoyance :)
-----Original Message-----
From: Anne Yu [SMTP:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 4:28 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: What's wrong with this query
Thanks Lisa, I got this explain plan from the OEM. What is the
'expected rows' ? Cardinality factor ? This query (below) should only
return 1 or 0 row , not 164662478 rows. A consultant company created
this application for us. I have over 37 queries like this one. Some
expected rows are (8,298,736,866,720), some are(1417777E+1), some performs
Merge join Cartesian. None of these queries take more then 1 second to run
but they paused the database from time to time. Any ideas?
Million thanks,
By the way, I am readinng your email everyday, feel like you're one of my
friends.
Sent: Monday, September 10, 2001 2:38 PM
To: Multiple recipients of list ORACLE-L
Your hint is wrong. You have to tell it what index to use. example /*+
index (table_alias index_name) */
Remember hints, if they are wrong, will just be ignored.
Your execution plan seems OK. It's returning an awful lot of rows ...
Lisa Koivu
Oracle Doggie Administrator
Fairfield Resorts, Inc.
954-935-4117
-----Original Message-----
Sent: Monday, September 10, 2001 3:15 PM
To: Multiple recipients of list ORACLE-L
Hey list, Can anyone tell me what's wrong with this query?
many thanks,
select /*+ INDEX(b)*/ distinct d.batch_number
, d.document_number
, d.entry_user_id
, d.document_type_id
, d.document_processed_date
, b.batch_media_id, d.return_Method_Id
from submitter_batch b , document d
, ucc_master_amendment m
where d.batch_number = b.batch_number
and d.document_number = m.document_number
and d.imaged = 0
and b.batch_media_id = 4
and d.document_status_id = 4
Submitter_batch - 97853 rows
Document - 8043272 rows (fk_d_batchnumber index on
batch_number)
Ucc_master_adment - 0 rows (pk_uma_dn index on document_number)
Here is the explain plan:
explain plan expected rows object name
____________________________________________________________________
select statement 164662478
sort (unique) 164662478
nested loops 164662478
nested loops 2008079
table access(full) 46193 submitted
batch
table access(by idx) 2008079 document
index(range scan) 2008079
fk_d_batchnumber non-unique
index(unique scan) 82
pk_uma_dn unique
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>
--
Author: Anne Yu
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: Anne Yu
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).
