Elapsed time

Hi all ,


I have a select query . I want to know the time it takes to execute  without taking the time to display .I need to know the accurete time .

All these days i was using set timing on. In that also is there any command which says  milliseconds ?.
mun_786Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
try
set autotrace traceonly
or,
you can find the time from explain plan output
0
mun_786Author Commented:
Hi , I did taht as well.

Let me  explain  you . I am doing the  below  operation .

SQL> set timing  on ;
SQL> select sysdate from dual ;

SYSDATE
---------
29-MAR-10

Elapsed: 00:00:00.01
SQL>


the time shows as 0.01 seconds . Does this  value includes the time take to display the date as well ?.If so how can i  avoid that ?.If i use auto trace also the  TIME column of  output also displays 100th part of seconds and i dont think its accurate. in set timing on  i dont  think that oracle need .01 seconds to do this . Is there any thing that can give me accurate value in oracle.
0
sventhanCommented:
If you do not mind, could you tell me why do you want to do this? I hope there are ways to find them out in oracle.
 
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mun_786Author Commented:
Application team is asking for this.
0
it-rexCommented:
mun_786
elapsed time has many pieces to it ;that is way the analysis should start from you as a DBA built upon a request from the appdev;not the opposite.
as usually so many names used in Oracle technology are not exactly what they mean,.or you can say that they may have more into them like elaplsed time.

my advise is to make your appdev team aware of some facts about Oracle you ca start from here

http://www.oracle.com/technology/pub/articles/schumacher_analysis.html

then set down with them and see what they really need .
0
mun_786Author Commented:
Hi All ,

Thank you very much for all the input . This is what i am  doing . I  am testing the flashback  with the clause AS  OF SCN . Below is my sql  which fetches only one recod and displays on scree. Depending upon the  where clause the number of displayed records  will be changed. The time varies depending upon the  number  of records. My app team is asking to give the time taken to fetch those records  by not displaying the records. For some queries i am getting elapsed time as 00.00.00. since the last two numbers represent milli seconds , app team asking  to display that in micro or nano seconds becuase  we cannot say that sql takes  zero time.

SQL> select  NAME,TRANCHE, CDU_ASOF_DATE, TRANCHE_NO  FROM PERF01.INTEX_CDO_TRANCHES  as of scn 278710606 WHERE NAME like 'Comp_%'  and TRANCHE_NO=199;

NAME                      TRANCHE              CDU_ASOF_ TRANCHE_NO
------------------------- -------------------- --------- ----------
Comp_199                  Insert               26-MAR-10        199

Elapsed: 00:00:00.14
SQL>
0
dugyalasreekanthCommented:
select  METRIC_NAME,
        VALUE
from    SYS.V_$SYSMETRIC
where   METRIC_NAME IN ('Database CPU Time Ratio',
                        'Database Wait Time Ratio') AND
        INTSIZE_CSEC =
        (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

METRIC_NAME                         VALUE
------------------------------ ----------
Database Wait Time Ratio                6
Database CPU Time Ratio                94
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.