How To Find Time Taken For Parsing / Creating Plan And Actual Data Retrieval
Jul 13, 2010
Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.
View 3 Replies
ADVERTISEMENT
Apr 3, 2012
is there possible to retrieve date with its time in oracle ,if we didnot provide time while insertion? like hiredate column of emp table can we get hiredate of each emp with its time of joining. i am using "11g Enterprise Edition Release 11.2.0.1.0"
View 3 Replies
View Related
Feb 13, 2013
Where are the details of the Actual Responder Details stored in the case of the below scenario
If a notification for one user was closed by another user through access to the first user's worklist, the name of the second user, who actually took the action, is displayed as the responder.
I am only able to find the Original Recipeint username in the wf_notifications table.But where will the details of the Actual Responder name displayed in the notification be stored.
When I use the "wf_notification.Responder" function, I get the Original Recipeint username of the user but not the one who acted on the notification. Is there ANY way by which I can get the Actual responder user name who acted on the notification
select responder from wf_notifications where notification_id = <nid> also gives me the Original Recipeint username of the user but not the one who acted on the notification
View 5 Replies
View Related
Jan 17, 2012
i hv one table breco_data which is having two columns one is partycode and second is chequee no .and i just want filter the partycode and cheque no which are not availble in the breco_data table .and party cannot have same cheque no
View 2 Replies
View Related
Jul 4, 2012
The view that can be used to find the changes the plans (hash_value,plan_hash_value for a particular sql statement).
I have a particular sql statement for which the execution plan changes but, unfortunately i cannot find regarding which view can be used to find the details regarding this. V$SQL_PLAN_MONITOR is not working as well.
View 4 Replies
View Related
Jan 16, 2013
what could be the cause of the following scenario :
A form is running, data is entered and saved. After saving on form, checking in the backend, the data is present. But when using the same form again to retrieve data, no data is shown.. and also if tried to enter same data (previously entered for saving again) then error comes up that data is already in the database.
could this be related to any restrictions that might be placed on tables or something ??
View 5 Replies
View Related
Apr 10, 2008
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 to empid2 from emp table.
How do we optimise the data retrieval and storage for the table Team. optimize the query and data retrieval time from Team table.
View 4 Replies
View Related
Apr 12, 2013
How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.
View 7 Replies
View Related
Dec 27, 2011
I can not find the sql plan for insert statement,why?
Select sql_text From v$sql a
Where a.SQL_ID = '0yungrk19a277';
-------------------------------
INSERT INTO OS_USERBILL_ACTV_READ_MON_DT
(MONTHNO, MAILCODE, OPERTYPE, PROVCODE, AREACODE, DAY_TOTALCOUNT, TOTALCOUNT,
CREATETIME, MODIFYTIME, SENDER_TYPE, SENDER_DOMAIN)
VALUES
(:B9 , :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , SYSDATE, SYSDATE, :B7 , :B8 )
[code]....
View 4 Replies
View Related
Sep 14, 2011
Oracle Database Version : 9.2.0.8.0
Some of the datafiles status have been changed to 'RECOVER', because the datafiles are physically missing.
Now, how can i find that when (timestamp) the status of the datafiles have been changed, as i am unable to find when the datafiles have been physically lost?
Please consider both the case :
1) when the database is in ARCHIVELOG Mode.
2) when the database is in NOARCHIVELOG Mode.
View 1 Replies
View Related
Nov 12, 2010
I create a view on production server which takes almost 10 to 12 minutes when it shows data. this view contains 3 or 4 tables on which all primary and unique columns have indexes.which index will be better for fast retrieval of data .
View 5 Replies
View Related
Oct 24, 2013
How To Increase Data Retrieval / Insertion Speed my data base has more than 0.5 million records Forms Some Time Respond Very Slow .
View 8 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
Oct 15, 2012
For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd
but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.
View 7 Replies
View Related
Aug 16, 2012
We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space
SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;
which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?
Is there any relation of Tablespace disk space and Actual Data present in the tables ?
View 18 Replies
View Related
Jun 15, 2012
the most accurate/efficient way of obtaining the execution plan for a piece of running SQL in Oracle 9i. in 10g and 11g obviously dbms_xplan.display_cursor(sql_id) can be used,
How can this be achieved in 9i, currently I am simply obtaining the SQL_TEXT and then running an explain plan ("EXPLAIN PLAN FOR..") - I believe this is not necessarily the same explain plan that will be used for the sql that is executing though
View 7 Replies
View Related
Jan 12, 2011
I have to parse an XML and store the data in a table.
My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>
[Code].....
The output is :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
There will be cases when I will get multiple values for the columns conversationid, command, value.
<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
[Code].....
In that case the output should be as :-
CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1
iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2
Not sure how to go about XML parsing when I have multiple values.
View 4 Replies
View Related
Sep 7, 2012
I am loading content of an XML file into a table using SQL loader.Below is my Control file script -
LOAD DATA
INFILE *
INTO TABLE xx_cc_response_xml_stg TRUNCATE
xmltype(XML_DATA)
FIELDS
( COLUMN_ID constant 1,
file_name filler char(4000),
XML_DATA LOBFILE(file_name) TERMINATED BY EOF)
BEGINDATA
B2B_MasterDataUpdate_20120906152137.xml
------------------------------------------------------------------------------------
The file B2B_MasterDataUpdate_20120906152137.xml is correct and XML is well formed.When i try to query for XML_DATA (datatype XMLType) column in the table, i cannot see any content in the record, and it appears as (XMLTYPE)When I parse this XML using the below,
select value(d)
from xxnbn_cc_response_xml_stg a,
table(xmlsequence(extract(a.xml_data,'/InventorySearch'))) d;
------------------------------------------------------------------------------------
I get this error:
------------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [qmcxdsSelf4], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug - the first argument is the internal error number
------------------------------------------------------------------------------------
View 1 Replies
View Related
Sep 23, 2010
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies
View Related
Sep 10, 2013
I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)
SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........
However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:
SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........
Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken
I'm receiving a XML from some external source and can not change it.
View 8 Replies
View Related
Dec 7, 2010
I ran the following control file in sql loader:
LOAD DATA
INFILE "gateway.csv"
truncate
INTO TABLE GATEWAY
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
[code]....
and I got the following error:
zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-350: Syntax error at line 12.
Expecting "," or ")", found "INTERGER".
GATEWAYPROTOCOL INTERGER,
^
The current OS is Solaris, SunOS 5.10.
View 3 Replies
View Related
Mar 14, 2012
I want to get the execute plan of a sql in standby database(read only),but failed,how can i do?
SQL> explain plan for select count(1) from hxl.tb_objects;
explain plan for select count(1) from hxl.tb_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-16000: database open for read-only access
SQL> select count(1) from hxl.tb_objects;
COUNT(1)
----------
22238018
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
View 4 Replies
View Related
Apr 27, 2010
how to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.
eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.
O/P of view should be like this.
Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00
View 6 Replies
View Related
Jul 9, 2013
Oracle Developer Suite 10g
I want to create List item with time .
like in pic when I click on upper side at list item increase time and other side decrease time.
View 1 Replies
View Related
Jun 4, 2010
The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.
My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.
View 6 Replies
View Related
Mar 28, 2012
I wanted to calculate the time variance dynamically as below.I have one table called process_status where we can see the process name, start time, end time and the status .
1. Now i wants to calculate the duration(end_time - start_time) in hh:mi:ss format and i got the output using below query.
select name, start_time, end_time, status, to_char(trunc(sysdate) + (end_time - start_time), 'HH24:MI:SS') duration from process_status ;
I got currect oupput but i am stuggling with the below step.
2). Actually the process expectaiotn time is 2 hours only(fixed time). Now i wants to calculate variance b/w expected time and the duration time(end_time - start_time)
i.e variance = expected_time - (end_time - start_time ) ;
here expected time is 02:00:00(fixed).
I tried using with to_char and to_date function but no luck.
View 7 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Dec 18, 2012
we can easily find out the locks which are currently active session.
Is any other way to find out locks on particular date and time using data dictionary?
For example, we have to find whether TT_objects occured the lock or not on ON yesterday.
View 3 Replies
View Related
Aug 21, 2010
I need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
View 2 Replies
View Related
Jun 20, 2013
How to find date and time of a column in table ?
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
View 10 Replies
View Related
