OraFAQ Forum: Performance Tuning » How to measure the sql run time?

Home » RDBMS Server » Performance Tuning » How to measure the sql run time? (Oracle 10.2.0.4.0 64bit , AIX 5.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to measure the sql run time? [message #513299]

Sun, 26 June 2011 04:41

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, guys
Is there any other ways to measure a sql run time more accurate and more efficient?
I meant using a spool and set timming on like this.

[oracle@milo ~]$ cat measure_sql.sql

spool result.txt;
set timing on;
select * from t3 where empno=7788;
set timing off;
spool off;

[oracle@milo ~]$ sqlplus scott/tiger @measure_sql.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 01:36:32 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

16 rows selected.

Elapsed: 00:00:00.01

Thanks very much.
BR,
Milo

Report message to a moderator

Re: How to measure the sql run time? [message #513300 is a reply to message #513299]

Sun, 26 June 2011 05:30

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on what you want to measure.
The response time for end user? This is the correct way.
The execution time in db server? Then activate trace.

Regards
Michel

Report message to a moderator

Re: How to measure the sql run time? [message #513301 is a reply to message #513300]

Sun, 26 June 2011 05:39

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, Michel
Can I think like this:
response time for end user = execution time in db server + network transfer time to client?

Report message to a moderator

Re: How to measure the sql run time? [message #513302 is a reply to message #513301]

Sun, 26 June 2011 05:42

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

+ time to fetch data inside the application.

Regards
Michel

Report message to a moderator

Re: How to measure the sql run time? [message #513303 is a reply to message #513302]

Sun, 26 June 2011 05:51

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Oh. OK.
Then I should measure the execution time in db server.
And when active trace on sql, how can I check execution time on db server？


Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   300 |  4200 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   300 |  4200 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

So is the eclipse time with set time on is the execution time or the Time in ID=0 is the execution time?
I don't think either of them are correct.

Thanks,
Milo

Report message to a moderator

Re: How to measure the sql run time? [message #513304 is a reply to message #513303]

Sun, 26 June 2011 08:12

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on what yu want, CPU time or elapsed time to execute?
Here have a resolution of 1 second, what do you expect when you have a time of 1 second, it can be 0 and 1.
If you want more details you want to activate SQL_TRACE, this is what I meant not SQL*Plus autotrace that, in addition, does not give execution time but estimated execution time from an exlain plan.

See How to set trace for others sessions, for your own session and at instance level from Pete Finnigan.

Regards
Michel

Report message to a moderator

Re: How to measure the sql run time? [message #513316 is a reply to message #513303]

Sun, 26 June 2011 14:33

John Watson
Messages: 8944
Registered: January 2010
Location: Global Village

Senior Member

The dynamic performance view v$sqlstats can be helpful:

orcl> select count(*) from emp;

  COUNT(*)
----------
        14

orcl> select elapsed_time from v$sqlstats where sql_text='select count(*) from emp';

ELAPSED_TIME
------------
       29855

elapsed_time is in microseconds, there are several other useful columns.

Report message to a moderator

Re: How to measure the sql run time? [message #513322 is a reply to message #513316]

Sun, 26 June 2011 19:43

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, Michel
Thanks. I am want to measure the elapsed time to execute. I have do the sql trace and got this msg after tkprof.

********************************************************************************

select * 
from
 t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch    15294      0.50       0.82          0      16562          0      229378
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    15298      0.50       0.82          0      16562          0      229378

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 31  

Rows     Row Source Operation
-------  ---------------------------------------------------
 114689  TABLE ACCESS FULL T1 (cr=8281 pr=0 pw=0 time=114836 us)


Elapsed times include waiting on following events:
  Event waited on                                   Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     15294        0.00          0.01
  SQL*Net message from client                 15294        0.69         45.23
********************************************************************************

so the eclipse time of execution is 0.82 s? I compare the value in John's solution, is about 0.43s.
I got a little confuse about it.

Report message to a moderator

Re: How to measure the sql run time? [message #513323 is a reply to message #513322]

Sun, 26 June 2011 19:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>so the eclipse time of execution is 0.82 s? I compare the value in John's solution, is about 0.43s.
>I got a little confuse about it.
Different tables with different number of rows.
So why are you confused?
Besides it appears you ran "select * from t1" TWICE; which means about 0.41 per execution.

Report message to a moderator

Re: How to measure the sql run time? [message #513325 is a reply to message #513323]

Sun, 26 June 2011 20:15

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, BlackSwan
It's the same table, but I think you are right.
Thanks.

Report message to a moderator

Re: How to measure the sql run time? [message #513348 is a reply to message #513325]

Sun, 26 June 2011 23:23

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And if you want detailed results read the raw trace, do not use TKPROF that sum up them.

Regards
Michel

Report message to a moderator

Re: How to measure the sql run time? [message #513358 is a reply to message #513348]

Mon, 27 June 2011 01:39

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

OK. I will check that. Thanks.

Report message to a moderator

Re: How to measure the sql run time? [message #514308 is a reply to message #513358]

Sun, 03 July 2011 16:11

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hello,

I think, the method via sql tracing is a little bit laborious. It is a good way to measure sql's, which you cannot execute. But if you can, you can do the following:

alter session set statistics_level=all;

select * from t3 where empno=7788;

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ALLSTATS LAST'));

You will get the runtime statistics not only for entire sql but also for certain steps of execution plan.

Regards
Leonid

Report message to a moderator

Previous Topic:	SQL tuning
Next Topic:	Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 08 03:38:58 CDT 2024