Re: count(*) to get timing on tuned SQL

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Wed, 16 Jul 2003 15:20:03 -0500
Message-ID: <7mcbhvk8rnb93rdlii26o3eaqk2qrcam7l@4ax.com>

rohitk1973_at_yahoo.com (rohit) wrote:

>One of the things I try while tuning is to do a count(*) over the
>tuned SQL, this saves me the full result dump, and doesnt lose the
>query execution timing while doing IO. But I am not very sure if
>thats a right thing to do.
>For example to get timing on a query like--
>
>select /*+ USE_HASH(t2) */
>from t1,t2
>where t1.a = t2.a
>and t2.b = 'xyz';
>
>I would run a SQL like:
>
>select count(*) from
>(select /*+ USE_HASH(t2) */
>from t1,t2
>where t1.a = t2.a
>and t2.b = 'xyz');
>
>and I may try different combinations of hints that look good in
>explain plan outputs.
>
>My assumption here is the nested query would have to run to
>completition and then a select count(*) would happen. So, if the
>select count(*) query took longer with Plan1 than Plan2, then nested
>query for Plan1 would run longer than the same for Plan2.
>
>Question is, am I right in making this assumption.
>
>Thanks,
>
>Rohit

You may get better info with Autotrace:

SQL> set autotrace traceonly
SQL> select empl_nm from hr_cur_rptng where empl_nbr like '00%';

6052 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=8065 Bytes=
          475835)

   1    0   TABLE ACCESS (FULL) OF 'HR_CUR_RPTNG' (Cost=118 Card=8065
          Bytes=475835)

Statistics

        442  recursive calls
         39  db block gets
       2308  consistent gets
       1878  physical reads
          0  redo size
     397365  bytes sent via SQL*Net to client
      49904  bytes received via SQL*Net from client
        406  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       6052  rows processed

SQL> edit
Wrote file afiedt.buf

1* select empl_nm from hr_cur_rptng where empl_nbr= '0001111111' ( not real just wanted to show effect of using index) SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=118) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HR_CUR_RPTNG' (Cost=2 Ca

rd=2 Bytes=118)

   2    1     INDEX (RANGE SCAN) OF 'I_HRCUR_EMPLID' (NON-UNIQUE) (Cos
          t=1 Card=2)

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          3  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        336  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

It wil tell you how much improvement you get... Received on Wed Jul 16 2003 - 15:20:03 CDT