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
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
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
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
May 5, 2011
why different elapsed time in Oracle9i and oracle10g for the same query ?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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