Message-Id: <10584.114201@fatcity.com> From: "Thomas L. Harleman" Date: Wed, 9 Aug 2000 13:50:09 -0500 Subject: RE: tkprof question 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@fatcity.com [mailto:root@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@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@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L