RE: tkprof question

From: Thomas L. Harleman <tharleman_at_iquest.net>
Date: Wed, 9 Aug 2000 13:50:09 -0500
Message-Id: <10584.114201@fatcity.com>

What do you really want to do?

DELETE rpt_metric
WHERE ( ntts < 1566858236 ) OR ( ntts = 1566858236 AND ntseq <= 749223 ) AND ROWNUM < 1001

DELETE rpt_metric
WHERE ( ntts < 1566858236 OR ntts = 1566858236 ) AND ntseq <= 749223
AND ROWNUM < 1001

Tom Harleman
11080 Willowmere Dr.
Indianapolis, IN 46280
317-844-2884 Home
317-843-9122 Home Office

-----Original Message-----

From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Carle, William T (Bill), NBSO
Sent: Wednesday, August 09, 2000 9:54 AM To: Multiple recipients of list ORACLE-L Subject: tkprof question

Hi,

What am I doing wrong here? I have an index on ntts and ntseq. This is a 10 million row table.

Bill Carle

TKPROF: Release 8.0.6.0.0 - Production on Wed Aug 9 08:37:29 2000 © Copyright 1999 Oracle Corporation. All rights reserved. Trace file: ora_5058.trc
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

DELETE rpt_metric
WHERE
(ntts < 1566858236 OR ntts = 1566858236 AND ntseq <= 749223) AND ROWNUM < 1001

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 0 0
Execute 2 81.16 81.82 191119 3738052 14383 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 4 81.16 81.82 191119 3738052 14383 0
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 19 (NECOM)

Rows Row Source Operation
------- ---------------------------------------------------

0 DELETE RPT_METRIC
1000 COUNT

      0    CONCATENATION
 419223     FILTER
 419223      TABLE ACCESS BY INDEX ROWID RPT_METRIC
 419224       INDEX RANGE SCAN (object id 1973)
1436876     FILTER
1436876      TABLE ACCESS BY INDEX ROWID RPT_METRIC
1436877       INDEX RANGE SCAN (object id 1973)


Rows     Execution Plan


-------  ---------------------------------------------------

      0  DELETE STATEMENT   GOAL: RULE
      0   DELETE OF 'RPT_METRIC'
   1000    COUNT
      0     CONCATENATION
 419223      FILTER
 419223       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'RPT_METRIC'
 419224        INDEX (RANGE SCAN) OF 'IX_RPT_METRIC' (UNIQUE)
1436876      FILTER
1436876       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'RPT_METRIC'
1436877        INDEX (RANGE SCAN) OF 'IX_RPT_METRIC' (UNIQUE)




****************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 0 0
Execute 2 81.16 81.82 191119 3738052 14383 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 4 81.16 81.82 191119 3738052 14383 0
Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0

	3  user  SQL statements in session.
	0  internal SQL statements in session.
	3  SQL statements in session.
		1	statement EXPLAINed in this session.
****************************************************************************

Trace file: ora_5058.trc
Trace file compatibility: 7.03.02
Sort options: default

	1	session in tracefile.
	3	user  SQL statements in trace file.
		0  internal SQL statements in trace file.
	3	SQL statements in trace file.
		1	unique SQL statements in trace file.
			1	SQL statements EXPLAINed using schema:
		ORACLE.prof$plan_table

Default table was used.
Table was created.
Table was dropped.

55 lines in trace file.

--
Author: Carle, William T (Bill), NBSO
  INET: wcarle_at_att.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Wed Aug 09 2000 - 13:50:09 CDT