Xref: alice comp.databases.oracle.server:22278 Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail From: tkyte@us.oracle.com (Thomas Kyte) Newsgroups: comp.databases.oracle.server Subject: Re: SQL_TRACE Question Date: Sat, 06 Jun 1998 15:04:58 GMT Organization: Oracle Government Lines: 66 Message-ID: <357a5994.5988050@192.86.155.100> References: <35791F27.22B60E11@cressoft.com.pk> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Newsreader: Forte Agent 1.5/32.451 A copy of this was sent to Umar FArooq (if that email address didn't require changing) On Sat, 06 Jun 1998 15:51:20 +0500, you wrote: >Im currently working on SQL_TRACE. I view the .trc file using tkprof. In >the output file the number of rows is indicated for each >parse/fetch/execute operation. What does teh row column signify? How is >it different from the row count indicated alongside each execution step >in the explain plan statistics. > >Thx. > >Umar. consider: ========================================================================== select * from emp, dept call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 2 0 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 5 0.00 0.00 2 12 15 56 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 4 12 17 56 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 20 (SCOTT) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 56 NESTED LOOPS 4 TABLE ACCESS (FULL) OF 'DEPT' 56 TABLE ACCESS (FULL) OF 'EMP' ========================================================================= the rows column signifies the total number of rows processed by the statemt. In this case, 56 rows were returned. It took 5 fetches to do it (affected by arraysize settings -- sqlplus fetches more then 1 row per fetch). It is different from the row count in the execution plan as the row count in the execute plan shows the number of rows flowing through each step of the plan, while the rows in the Fetch line shows the number of rows that eventually got returned. The execution plan shows that 4 rows were accessed in the DEPT table and a total of 56 rows (i had 14 rows in emp, 4*14 = 56) from the emp table were accessed. Thomas Kyte tkyte@us.oracle.com Oracle Government Herndon VA http://govt.us.oracle.com/ -- downloadable utilities ---------------------------------------------------------------------------- Opinions are mine and do not necessarily reflect those of Oracle Corporation Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it.