How To Get Total Elapsed Time Of Multiple Queries

May 7, 2013

I am executing below two SQL's at a time in a .sql file and I want the total execution time of these two queries:-

set timing on;
select 1 from dual;
select 2 from dual;

The output of the above sql file is returning the results like below which is expected:-

1
----------
1

Elapsed: 00:00:00.01

2
----------
2

Elapsed: 00:00:00.00

I want the total execution time of the two SQL queries at a time, I mean post execution of the two queries I want to know the total execution time, in this case is execution case will be 00:00:00.01.

View 2 Replies


ADVERTISEMENT

Find Queries Bases On Elapsed Time From Window Of 3 Months

Nov 19, 2012

how to find out queries bases on elapsed time from the window of 3 Months

DBA_HIST_SQLSTAT and DBA_HIST_SQLTEXT

View 6 Replies View Related

Server Administration :: Elapsed Time In AWR Report?

Nov 7, 2011

I have an AWR report based on two snapshots one an one hour time window, under the "SQL ordered by Elapsed Time" section, there is a query having Elapsed Time = 7012 seconds, which is almost two hours, how is this possible? I have been told that this i aggregate value of all sessions.

View 3 Replies View Related

Performance Tuning :: SQL Ordered By Elapsed Time

Feb 2, 2013

Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text

3,263 32 1 3263.49 2.79 3ta0ms19fvgds httpd.exe SELECT CASE WHEN COUNT >= 1...Elapsed
6,360 164 2 3180.17 5.44 51jx99dm0swv7 cpm_srvscript@ahcaxasmil1b (TNS V1-V3) SELECT /*+ CCL<PFT_GET_RP...

On AWR, I see two script that are out of ordinary, and I want to make sure that I interpret them correctly.

1) "Elap per Exec (s)" shows 3263.49 with 1 "Executions".
2) "Elap per Exec (s)" shows 3180.17 each execution with 2 "Executions".

Does this mean that this script ran for ~ 54 minutes (3263.49 / 60 seconds) for 1 "Execution" and ~ 53 minutes (3180.17 / 60 seconds) per each execution? I need to understand "Elapsed Time (s),CPU Time (s),Executions ,Elap per Exec (s), % Total DB Time" represent.

View 9 Replies View Related

Performance Tuning :: Why Different Elapsed Time In Oracle9i And 10g For Same Query

May 5, 2011

why different elapsed time in Oracle9i and oracle10g for the same query ?

View 2 Replies View Related

Performance Tuning :: SQL Ordered By Elapsed Time And SQL Module?

Feb 24, 2013

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
2,423 1 3,919 0.62 1.83 gt49gg0fnc5x8 srv_dr@ahs (TNS V1-V3) UPDATE /*+ CCL<OENDB_FILE...
2,227 14 1 2227.16 1.68 bggfx8a04prj9 SQL*Plus select * from (select n.source...
.........

On [SQL ordered by Elapsed Time], [SQL Module] shows an indication that a SQL was executed by which process (i.e. srv_dr@ahs)outside of SQL*PLUS.If [SQL Modeule] shows as [SQL*Plus], does it mean the query was run in SQL*PLUS manually or directly?I have the SQL ID. How do I find out who, how, and exactly what time it was run?

View 5 Replies View Related

Performance Tuning :: Elapsed Time Not Accounted In Tkprof File

Jan 13, 2011

find the attached tkprof'ed file of session

I started the trace after the query started (upon user's complaint)

However even after tracing the session for more than 30 minutes I am not geeting where the 30 minutes are accounted in this file

View 11 Replies View Related

Performance Tuning :: Elapsed Time In Trace File Output

Nov 16, 2011

I executed a query which executed quickly (1.7 seconds) but since its output took time in displaying on the console the time shown by 'set timing on was 39.5 seconds

also I took trace (tkprof) for the same.My query is why the timings under 'Total Waited' (43.19 and 1.69) are not added to the elapsed time 1.83 seconds

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.06 0 10 0 0
Fetch 758 0.03 1.77 0 0 0 11345
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 760 0.03 1.83 0 10 0 11345
[code]....

View 1 Replies View Related

Performance Tuning :: Why Elapsed Time Changed While Execution Plan And Stats Remains The Same

Jun 4, 2010

attached query giving consistent execution plan but different timings across run

SELECT /*+ INDEX (CRT CRT_CUN_FK_I)*/
DISTINCT odr.dve_id
FROM company_requirements crt, orders odr, lelo_products la_pct
WHERE crt.qtn_cun_id = 10035637--10000021--10035667
AND crt.ID = odr.crt_id_quote_implemented
AND NVL (odr.cancellation_date, '31-Dec-9999') = '31-Dec-9999'

[code]....

we have 4 databases, 2 on each servers, such that db1 and db2 on server1 and db3 and db4 on server2

refer count of the records for column of biggest table in the query, taken on all 4 databases (The column is nullable)

select count(*) from company_requirements crt WHERE crt.qtn_cun_id = 10035637
db1 = 73335
db2 = 89073
db3 = 81182
db4 = 82936

First I executed the query on db1 and db2 while there wasn't any user logged on to the system

db1
**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 17.47 473.39 85704 1508102 0 0

[code]...

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 85704 0.31 460.55
latch free 1 0.00 0.00
SQL*Net message from client 1 14.98 14.98

[code]...

Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?

I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?

In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)

I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution

What I should have checked or monitored?

View 10 Replies View Related

Multiple Sub-queries In View

Jun 16, 2009

I have a table similar to the following,

USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...

which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.

select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user

However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:

user, latest_home_number, latest_work_number

View 3 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 Replies View Related

SQL & PL/SQL :: Multiple Parallel Queries Against Table

Jun 23, 2011

I would like to know the following.

If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.

In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.

View 2 Replies View Related

Multiple Queries To Reflect A Date Change

May 29, 2009

I need to create probably two subqueries in which to reflect when a date changes. For instance, an account has a code and I need to reflect the the date the code changes...see sample below:

Account Date Code Date Changed
123 1/1/2009 ABC 1/1/2009
123 2/1/2009 ABC 1/1/2009
123 3/1/2009 XYZ 3/1/2009
123 4/1/2009 XYZ 3/1/2009
123 5/1/2009 ABC 5/1/2009

I have the one query which will return all of the dates, but I believe I need another query that will return only the dates that changed....can't seem to get that one to work at all. I believe once I do this then I can join the two queries together.

View 3 Replies View Related

Server Utilities :: Get Total Time When Using Export A Table?

Jul 12, 2011

How can i get the total time when using export a table?

for example:

exp userid=test/test file=d: est.dmp tables=(tb_test)

View 4 Replies View Related

SQL Queries Taking Long Time After 11g Data Migration

Sep 14, 2010

I have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.

2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.

View 5 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session

View 3 Replies View Related

SQL & PL/SQL :: Build Parametric Queries - Input Date Field At Run-time

Aug 24, 2012

When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle

Example

SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC
FROM Sector
WHERE Sector.Date=[Enter Date];

View 15 Replies View Related

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

Performance Tuning :: Hierarchical Queries - Run Jobs Within Stipulated Time Frame

Nov 17, 2011

We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.

The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. I have attached in the text file.

View 4 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

SQL & PL/SQL :: Run Query Multiple Times With Time Gap

May 25, 2013

I need to run the below query and display the result every one hour.

Below code giving result only once.

I think my method is wrong.

SQL> declare
2 counter number :=0;
3 item varchar(300);
4 BEGIN
5 FOR item IN (

[Code]...

PL/SQL procedure successfully completed.

View 12 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Dates At Same Time?

Jan 10, 2012

how to calculate the difference between multiple dates at the same time..

Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;

Giving me an error...

ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..

View 8 Replies View Related

SQL & PL/SQL :: Split Single Row Into Multiple Rows Containing Time Periods Per Year

Nov 26, 2010

I have a table like this:

ID1 ID2 Ini_date End_date
1 1 2008-05-14 2010-09-16
1 2 2010-01-21 2010-08-26
..... ..... ............. ...................

and I would like to have a row for each year between ini_date and end_date.

ID1 ID2 YEAR
1 1 2008
1 1 2009
1 1 2010
1 2 2010

View 2 Replies View Related

SQL & PL/SQL :: Insert Multiple Records In Different Tables At A Time (Using Single Query)

Jun 8, 2010

I want to insert multiple record in diff. table by using single query...

how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..

But i want to do this task by using single query....how can i do it?

View 4 Replies View Related

Reports & Discoverer :: Lexical Parameters Are Used To Substitute Multiple Values At Run Time

Jan 15, 2012

I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"

Select * from emp, deptno &where.

and i know about substitution variables using & is this are same (lexical and substitution) or different.

View 14 Replies View Related

PL/SQL :: Total And Grand Total

Mar 26, 2013

I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.

SELECT   decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
         --decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
         farinva_invh_Code,
         spriden_id,
         --spriden_last_name "last Name"

[Code]....

View 3 Replies View Related

JDeveloper, Java & XML :: Date Time Omits Time Part

Jun 4, 2012

When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.

If I try the same in SQLplus with to_date it works however fails in PL/SQL.

XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......

View 3 Replies View Related

How To Set Interval Time 4hrs In Dbms_jobs But Starting Time 3:00 Am

Jul 25, 2013

how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.

i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..

View 2 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved