Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tkprof question

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

or

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US