Home » RDBMS Server » Performance Tuning » How to measure the sql run time? (Oracle 10.2.0.4.0 64bit , AIX 5.3)
How to measure the sql run time? [message #513299] Sun, 26 June 2011 04:41 Go to next message
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
Re: How to measure the sql run time? [message #513300 is a reply to message #513299] Sun, 26 June 2011 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68509
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
Re: How to measure the sql run time? [message #513301 is a reply to message #513300] Sun, 26 June 2011 05:39 Go to previous messageGo to next message
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?

Re: How to measure the sql run time? [message #513302 is a reply to message #513301] Sun, 26 June 2011 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68509
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
+ time to fetch data inside the application.

Regards
Michel
Re: How to measure the sql run time? [message #513303 is a reply to message #513302] Sun, 26 June 2011 05:51 Go to previous messageGo to next message
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
Re: How to measure the sql run time? [message #513304 is a reply to message #513303] Sun, 26 June 2011 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68509
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
Re: How to measure the sql run time? [message #513316 is a reply to message #513303] Sun, 26 June 2011 14:33 Go to previous messageGo to next message
John Watson
Messages: 8880
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.
Re: How to measure the sql run time? [message #513322 is a reply to message #513316] Sun, 26 June 2011 19:43 Go to previous messageGo to next message
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.
Re: How to measure the sql run time? [message #513323 is a reply to message #513322] Sun, 26 June 2011 19:54 Go to previous messageGo to next message
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.
Re: How to measure the sql run time? [message #513325 is a reply to message #513323] Sun, 26 June 2011 20:15 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, BlackSwan
It's the same table, but I think you are right.
Thanks.
Re: How to measure the sql run time? [message #513348 is a reply to message #513325] Sun, 26 June 2011 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68509
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
Re: How to measure the sql run time? [message #513358 is a reply to message #513348] Mon, 27 June 2011 01:39 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

OK. I will check that. Thanks.
Re: How to measure the sql run time? [message #514308 is a reply to message #513358] Sun, 03 July 2011 16:11 Go to previous message
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
Previous Topic: SQL tuning
Next Topic: Performance Tuning in Oracle 10g during Migration - Heavy Insert Optimization
Goto Forum:
  


Current Time: Sat Sep 30 00:04:43 CDT 2023