| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Wrong number of rows in tkprof?
The figure that seems to be wrong is
the 2 beside the sort.
Oracle per 8.1.something generally listed the
number of rows accessed by each line
with odd numbers in some places. In the
latest versions (and I don't know exactly
when it changed) it lists the number of rows
supplied to the next step.
Hence the 6 is the number of rows returned after the scan of thing, and the 1 is the number of beer rows returned after the scan of category and the 2 beside the MERGE is the number of rows output from the merge.
I don't know why there is a 2 beside the two SORTs - I haven't examined this closely since the change, but I would have expected it to be 1 and 6 respectively.
-- Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html Rene Nyffenegger wrote in message ...Received on Fri Aug 10 2001 - 14:41:34 CDT
>Hi
>
>I decided to get to know tkprof better.
>Therefore, I created two tables without any indexes,
>and filled these with 2 and 6 rows, respectively:
>
>create table category (
> id int,
> description varchar(50)
>);
>
>create table thing (
> id int,
> description varchar(50)
>);
>
>
>insert into category values (1, 'fruits');
>insert into category values (2, 'beers');
>
>insert into thing values (1, 'apple');
>insert into thing values (1, 'banana');
>insert into thing values (1, 'apricot');
>insert into thing values (1, 'pears');
>
>insert into thing values (2, 'heineken');
>insert into thing values (2, 'budwiser');
>
>
>Then, I altered my session as follows:
>
>alter session set sql_trace=true;
>alter session set timed_statistics=true;
>
>Then, the query:
>
>select t.description from thing t, category c where t.id=c.id and
>c.description='beers';
>
>Then, in order to close the cursor for that statement:
>
>select 1 from dual;
>
>tkprof output:
>
>***************************************************************************
>*****
>
>select t.description
>from
> thing t, category c where t.id=c.id and c.description='beers'
>
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ---------- -----
>-----
>Parse 1 0.00 0.00 0 0 0
>0
>Execute 1 0.00 0.00 0 0 0
>0
>Fetch 2 0.00 0.00 0 2 8
>2
>------- ------ -------- ---------- ---------- ---------- ---------- -----
>-----
>total 4 0.00 0.00 0 2 8
>2
>
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 18 (RENE)
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 2 MERGE JOIN
> 2 SORT JOIN
> 1 TABLE ACCESS FULL CATEGORY
> 2 SORT JOIN
> 6 TABLE ACCESS FULL THING
>
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 2 MERGE JOIN
> 2 SORT (JOIN)
> 1 TABLE ACCESS (FULL) OF 'CATEGORY'
> 2 SORT (JOIN)
> 6 TABLE ACCESS (FULL) OF 'THING'
>
>***************************************************************************
>*****
>
>Now, it tells me, that it retrieved 6 rows from thing (which is correct)
>but only one row from category (which is not, there are two!). Or, does
>that 1 mean soemthing different?
>
>Another question: what does the 2 mean for the SORT (JOIN) line?
>
>And am I right in assuming that the 2 of MERGE JOIN is how many rows are
>returned?
>
>
>For any hint I want to thank in advance.
>
>Rene
>
>
>
>--
>Recherchen im Schweizerischen Handelsamtsblatt:
>http://www.adp-gmbh.ch/SwissCompanies/Search.php3
 |
 |