I have just migrated database to 11.2 ..Migration is successfull and now database is in open mode working fine.BUT i m getting following mesage in alert log file
"Time drift detected. Please check VKTM trace file for more details."I m using windows platform.
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]....
We have a scenario where we need to get the details of quota details on different tablespaces for a user. Is there any way to get this information directly using some views etc.
I tried with following option.. but not found in this... i think we can get this TOAD some other tools. but i never tried using SQL directly...
SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;
I want to trace the time it takes to refresh the MV for the last few runs. How can i find that? The MV runs every 7 hours..for the last few times it runs, I would like to trace the time it took to complete.
View 1 Replies View RelatedI have created a fast refresh materialized view.now i wanted to know my materialized view how long processing for refresh.which table stores this information with syntax.
View 2 Replies View RelatedI have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
I want to get the details of the SYS/SYSTEM users logon and Logoff's time.
Steps 1:
=======
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:ORACLEPRODUCT10.2.0ADMIN
DB10GADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>
Step 2:
=======
Set the Necessary parameters:
-----------------------------
alter system set audit_trail=db scope=spfile;
Step 3:
=======
Shutdown the DB
Step 4:
=======
Restart the DB
Step 5:
=======
For auditing the Session. Fire the below command.
AUDIT CREATE SESSION;
Step 6:
=======
select DISTINCT USERNAME,os_username,timestamp,logoff_time
from DBA_audit_session;
When I fire the above query, I couldn't get the SYS user's LOGOFF time. But for rest of the users, I am getting.
I've got a query running a select count (*) over a table. The default plan takes in the order of 15 minutes to return, a hinted plan to use a different index takes 3 minutes to return.
Unfortunately I cant get at the index stats and a few other areas which I suspect may be key here.When running autotrace against the two queries I see fairly different values as one would expect.
Query
select count (*) from fulfilmentitem bfi where created >= sysdate-30 AND bfi.status = 'FA' AND bfi.fulfilmentmethod = 'D'
Slow run
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 33119 (1)|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| FULFILMENTITEM | 12525 | 183K| 33119 (1)|
|* 3 | INDEX RANGE SCAN | IDX_FULFIL_METHODSTATUS | 250K| | 1786 (1)|
----------------------------------------------------------------------------------------------
[code]....
IDX_FULFIL_METHODSTATUS is across FULFILMENTMETHOD & STATUS in that order.
IDX_BFI_CREATED is on CREATED and is approx 70% of the size of the other index
The row counts estimated in the explain plan are out, the count(*) comes in at 32.8k rows.As you will have seen, the fast run shows a pretty significant consistent get increase compared to the slow run and a decent though not dramatic physical read drop.
My uncertainty is around if these changes in consistent get/phys read values would typically be enough to suggest the real time improvements I'm observing or if other (albeit perhaps temporary) factors are involved. It is a prod OLTP environment so the data will be rapidly changing and that may be a factor.
I know it can never be an exact science without intimately knowing the hardware/current loads etc but I also know that there's enough experience on these boards to have a loose handle on if the time shifts between queries are likely (or not) to be reflective of the stat changes or if those differences alone shouldn't (or typically wouldn't account) for it.
I'm thinking about instructing the query to ignore its original plan but am hesitant to do so without being a little more confident that it's not just a timing thing or something other than the change of index approach which may be causing the improvement. the autotrace stat changes observed I couldn't put my hand on heart say "yup - that change is good, ignore the default index all the time for this job".
I am getting below mentioned error in alertlog file very frequently.
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 747
ORA-06512: at "BEE_CODE_05252013.SS_INDEX_JOB_PKG", line 496
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Errors in file /beprddb/diag/rdbms/beprddb/beprddb/trace/beprddb_ora_10581.trc:
Tue Jul 02 15:31:15 2013
[code]....
i am using 10.2.0.4.0 version of oracle.I am having trace file info as below, for one of the query. So how should i interpret the trace file? What is the issue in the query, and the scope of improvement in the query? I have removed the query and its plans from the trace file, i have only posted the wait sections.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.13 0 0 1 0
Execute 1 6.63 162.12 33540 72921 383 0
Fetch 17272 178.89 1933.95 274835 3147603 20 259063
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17274 185.66 2096.21 308375 3220524 404 259063
[code]....
I need to take the database trace of each page of my web application. I am giving the following commands.
BEGIN
dbms_monitor.session_trace_enable(session_id=>122, serial_num=>NULL, waits=>true, binds=>true);
END;
Accessing the web application. Once it renders completely, I am executing the following command.
BEGIN
dbms_monitor.session_trace_disable(session_id=>122);
END;
In the user_dump_dest folder, I am expecting to see a new trace whenever I execute these commands. But the same trace file is getting updated. How do I make oracle to create a new trace for each iteration. I am using Oracle 11g Release on CentOS 5.x
We are running Oracle 11.2.0.3 on a Windows 2008 R2 Server with an app server on the same. Our app has a search function that has recently started timing out on us. Sometimes intermittently but often now. I've ran a trace in our dev environment, below code, in an attempt to track down the issue but I'm finding very little useful information in relation to the errors I see in the trace file below. My experience in deciphering trace code is nill so how to interpret these errors.
*** 2013-04-18 14:27:23.353
*** SESSION ID:(3093.63097) 2013-04-18 14:27:23.353
*** CLIENT ID:() 2013-04-18 14:27:23.353
*** SERVICE NAME:(SYS$USERS) 2013-04-18 14:27:23.353
*** MODULE NAME:(w3pw.exe) 2013-04-18 14:27:23.353
*** ACTION NAME:() 2013-04-18 14:27:23.353
[code]....
As a sys user i want to trace a user session. so i am using
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
to trace. but this trace file is mixing with another trace files in udump. did some Google and found that we can use
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION".
but this will generate trace file for sys user account not for the desired user session. how we can define trace file name for desired user session trace.
after apply patch 10.2.0.4 for 2 10gr2 node i tried to start asm on 2 node but i found that the 1st node (master node)
can not started but the second node is ok and this error found in trace file for lmon.trc
kjxgmpoll: terminate the CGS reconfig.
Error: KGXGN polling error (15)
error 29702 detected in background process
ORA-29702: error occurred in Cluster Group Service operation
ksuitm: waiting up to [5] seconds before killing DIAG
Oracle version: 11.2.0.3.0 Enterprise Edition
OS - IBM/AIX RISC System/6000
I am trying to generate a trace file from a piece of code executed by java server. What I asked the java developer to do is to place this block immediately after establishing a connection:
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''M1''';
dbms_monitor.session_trace_enable(waits => FALSE, binds => TRUE);
END;And at the end of the logical java block of code:
BEGIN
dbms_monitor.session_trace_disable;
END;
What I want to know is how many rows the java server fetches after executing one particular select statement, because they complain about receiving less in count rows from the select statement than expecting. For example, if I execute the same sql query in sqlplus session, then I fetch let's say 1000 rows. When the same query is executed from java side, the fetched rows are less in count, let's say 500.And because I doubt it, I wanted to trace to see what actually is executed and how.the excerpt of the trace file I see exactly the same query which I execute myself in a sqplus session.is no fine-grained control on the udnerlying tables in the query.
And my question is, how to interpret the FETCH phase of the cursor (for the select statement)?example, if I see one FETCH for this cursor, does this mean that the java server has fetched only one row?If I see 100 FETCHes, does this mean they fetched 100 rows from the cursor?
Here is a short excerpt from the trace file (don't crucify me for the query and the obvious denormalized design of the tables, this is not invented by me):
PARSING IN CURSOR #4573587152 len=667 dep=0 uid=737 oct=3 lid=737 tim=17685516462413 hv=954980718 ad='70000006d3e4940' sqlid='69pm96nwfrqbf'
select /* ordered */ o.id, nvl(o.par_id, -1) as par_id, o.NAME_GER, o.NAME_ENG, o.NAME_ESP, o.NAME_ITL,o.NAME_FRA, decode(lo.lflag, 'Y', 'L', 'N') as leaf_or_node, lo.distance + 1 as "LEVEL", to_char(o.beg_date,
[code]...
I got the below message in trace file. What does the line "swap info: free = 0.00M alloc = 0.00M total = 0.00M" trying to say?
I have
RAM=1.5G
Swap=3.5G
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle
System name:Linux
Node name:linuxdev
Release:2.6.5-7.97-default
Version:#1 Fri Jul 2 14:21:59 UTC 2004
[Code]....
I am working on tuning the performance of one of the concurrent request in our 11i ERP System having database 11.1.0.7
I had enabled oradebug trace for the request and generated tkprof out of it. For below query which is taking time , I found that , in the trace generated , wait event is "db file sequential read" on an PO_LINES_N10 index but in the generated tkprof , for the same below query , the full table scan for PO_LINES_ALL is happening , as that table is 600 MB in size.
Below is the query ,
===============
UPDATE PO_LINES_ALL A
SET A.VENDOR_PRODUCT_NUM = (SELECT SUPPLIER_ITEM FROM APPS.IRPO_IN_BPAUPDATE_TMP C WHERE BATCH_ID = :B1 AND PROCESSED_FLAG = 'P' AND ACTION = 'UPDATE' AND C.LINE_ID =A.PO_LINE_ID AND ROWNUM = 1 AND SUPPLIER_ITEM IS NOT NULL),
LAST_UPDATE_DATE = SYSDATE
===============
Index PO_LINES_N10 is on the column LAST_UPDATE_DATE , logically for such query , index should not have got used as that indexed column is not in select / where clause.
Also, why there is discrepancy between tkprof and trace generated for the same query .
So , I decided to INVISIBLE the index PO_LINES_N10 but still that index is getting accessed in the trace file .
I have also checked the below parameter , which is false so optimizer should not make use of invisible indexes during query execution.
SQL> show parameter invisible
NAME TYPE VALUE
----------- optimizer_use_invisible_indexes boolean FALSE
i want to know about trace file and TKPROF. any example.
View 1 Replies View RelatedI am having enq: TX - row lock contention in top wait event. it is occurring between 10pm - 2am.
We are having sqlloader job running every one hour(conventional path). But for the specific period of time i am getting "Global Enqueue Services Deadlock detected". Between 10-5. I analyzed related trace file it is make me little confusion.I found there are four insert query culprit for this locking. out of four sql , tow of them are ran by same SID, other two insert ran by same id. I got confused because how same sid locking them self. trace file below. during this period oracle maintenance window is active.
Trace file:
*** 2012-10-09 03:40:31.135
user session for deadlock lock 0x15365e060
sid: 1104 ser: 22256 audsid: 8797820 user: 49/iurth flags: 0x45
pid: 71 O/S info: user: oracle, term: UNKNOWN, ospid: 8601
image: oracle@sgh0909
client details:
[code]....
In Trace file I am always geeting one error "Error encountered: ORA-10980" due to which MVs fails . how to overcome from this error?
View 2 Replies View RelatedIn my database smon tracefile was huge,now we want to delete the same,I also tried with ordebug operation but no use, delete tracfile which Smon generates.
View 5 Replies View RelatedI am trying to get a readable version of a .trc file generated by Oracle
10.2.0.4.0 with tkprof, but I still have been unable to get this done...and this is what I have already tried:
tkprof /u00/app/oracle/admin/DB/udump/*BITN1234.trc /batch/salidas/student/BITNTRACE.txt
And whether I run this from my PL/SQL process (PRO*C) or from a command line, it returns:
TKPROF: Release 10.2.0.4.0 - Production on Thu Jan 6 11:04:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
could not open trace file /u00/app/oracle/admin/DB/udump/BITN1234.trc
I have already (from my PRO*C code and from command line)...
- made sure that the file is in the directory.
- run this from the udump directory, where the .trc file is...didn't work.
- run this from the udump directory, and specifying explicitely the
complete path anyway in the tkprof line (redundant...I know)...didn't work.
- tried to copy the file to another directory in order to run the tkprof,
and it returns:
cp: BITN1234.trc: The file access permissions do not allow the specified
action.
- tried changing privileges with:
chmod a+r BITN1234.trc
and it returns:
chmod: BITN1234.trc: Operation not permitted.
- tried to run tkprof01 instead of tkprof and it returns:
We trust you have received the usual lecture from the local System Administrator. It usually boils down to these two things:
#1) Respect the privacy of others.
#2) Think before you type.
I have a simple query which will return either A or B depending on the projected oven out date and time. If the projected oven out date and time is between 6am and 6pm, A should be returned. Otherwise B if time is between 6pm and 6am of the next day. My problem is that I do not know how to display A or B depending on the projected oven out date and time. I am using the query below to get the projected oven out date and time.
SELECT
to_char((ti.txndate + pm.baketime/24),'MM/DD/YYYY HH:MI:SS PM') FCSTDOvenOut
FROM CONTAINER c
[Code].....
I am trying to install Oracle 11g R2(64bit) on Windows 7 64 bit OS. While Creating Database using DBCA. I am getting error. Below is the screenshot.
Below is the error in the Trace file...
oracle.sysman.assistants.util.step.StepExecutionException:
Error in Process: C:Oracleapporacleproduct11.2.0serverinorapwd.exe
Unable to find error file %ORACLE_HOME%RDBMSopw<lang>.msb
I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.
I cannot modify the procedure as its on production. I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ? How to check the cursor stats at runtime. I want to check up to which record the cursor has been fetched and how many are still remaining. I have cursor name. Is there some dynamic view to check cursor stats at runtime ?
customer have a monitoring system that execute a script to check response time db (9i database).
The script do the following steps:
- Date
- select count(*) from all_tables
- select count(*) from v$lock
- Date
And then calculate the gap from end and start date, this is the response time.So, response time change from db to db depending on number of tables.
Is there any different method or different object to use?
We are facing one issue on one of the database. The database is generating large trace files(14000) from last two days. That consumes around 15G space on the disk. And the content of the trace files is not having any meaningful message to debug:
cat /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc
*** TRACE DUMP CONTINUES IN FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***
Dump file /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc
*** TRACE DUMP CONTINUED FROM FILE /apps/oracle/admin/fs90uat/bdump/fs90uat_p050_23966.trc ***
... (Many lines with above message)
The alert log is having one repeated error yesterday:
Thu May 6 22:00:03 2010
Errors in file /apps/oracle/admin/fs90uat/bdump/fs90uat_j000_11811.trc:
ORA-12012: error on auto execute of job 2647927
ORA-04063: ORA-04063: package body "ORACLE_OCM.MGMT_DB_LL_METRICS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "ORACLE_OCM.MGMT_DB_LL_METRICS"
ORA-06512: at line 1
The corresponding trace file is having error:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/product/10.2.0/db_1
System name: SunOS
Node name: corpqadb30
[Code] .......
I have a deadlock trace file to analyse and i used to be able to see the rowid as a 16 bit hex value in the trace file, which i could then query on to get the actual real world row name.I see in the 11GR2 deadlock trace the formatting is different and for the life of me i am unable to see the rowid. Has Oracle stopped reporting this now, or is their another way to get this value? The deadlock graph shows me
*** 2013-05-14 14:49:15.047
Submitting asynchronized dump request [28]
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.16e] :
BLOCKED 0x4362890f8 5 wq 2 cvtops x1 TX 0x3d001b.0x18a3021 [FF000-0001-00000002] inst 1
BLOCKER 0x436288f38 5 wq 1 cvtops x28 TX 0x3d001b.0x18a3021 [102000-0001-00000002] inst 1
[code]....
I'm having a 2node 11.2.0.3 RAC setup with ASM on Solaris 10 x64 platform.During the restart of the database, it is taking longtime to start around 10mins.
View 3 Replies View RelatedNeed to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
which view we can use ?