in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?
View 3 RepliesI have this function to calculate the length of the string in inches.
I would like to know how can I add a line break or CRLF right after X length and not to break any words in the string.?
I'm having a multi-record detail block in my form and after querying the form, when I scroll through the scroll bar, it is hanging (freezing) just before reaching the last record.
I have noticed that it never hangs if I use arrow keys to scroll between the records. It only hangs when I use scroll bar or page down key.
Regardless of the number of records fetched, it only hangs when I click the scroll bar (or press page down key) to fetch the last record.
It happens to all my custom forms and I'm developing this for Oracle EBS Apps 11.5.10.2. Oracle JInitiator Version is 1.3.1.21 and my form builder is Forms 6.0.8.28.0.
How to free up the tablespace size when it reaches its threshold (Max limit)?
Say for example USERS & SYSAUX tablespace reaches above 90%.
I am using, Release 11.2.0.3.0 of oracle. In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'. After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention ' that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),
Now i can see two of the oracle jobs has been scheduled at this particular time.
1)dbms_autotask_prvt.age
2)dbms_scheduler.auto_purge
And these were having frequency daily.So, i am suspecting these were experiencing the waits. Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB? Or should change the schedule time to some less peak hour or whether these are 'not required' and can be disabled?
We had a massive jump in cluster waits specifically; gc buffer busy acquire during an RMAN backup. We identified the cause of the waits to a few hot blocks with a table that may well need re-building in terms of ITLs and PCTFREE (Although I thorght ASSM would manage PCTFREE and PCTUSED..)
What happens during an RMAN backup that may cause huge cluster waits on hot tables? Is there some crazy redo issue going on or maybe flashback?
Either way the spike is there and we can pinpoint the activity on the database but just dont understand why RMAN would cause the issue.We have just found that the LARGE_POOL has not been set, in fact there is no SGA_TARGET either! Could this have an effect on RMAN and cluster waits?
os version is solaris 10
db version 11.2.0.3
how to tune log file sysnc waits in 11gr2 what parameters are involving in tuning log file sync and what would be its optimal values.
We have a table (call it PROBLEM table) that varies between 60 and 100 million records from one data archival process to the next.The table has data from all 50 states and is range partitioned by each states unique code and has a primary key based on the following:
STATE the data is from
BMRK YEAR the year the data was last benchmarked
AREA location the data is from
SERIES industry the data is located in
YEAR the data was reported
MONTH the data was reported
DATA TYPE the type of data that was reported
ESTIMATE TYPE the type of statistical estimate that was produced from the data
REPORT ID report id unique to each state
REPT SFC state the reporter resided in
We have developed a job that will process data from other tables and create some statistics resulting in approx 2.2 million inserts, .5 million updates and .5 million deletes on PROBLEM table during each job run. Once this table is loaded another process (call this PROBLEM_PROCESS)takes off that reads this updated information to produce some statistical data that is stored in another table. This data is produced by summing anywhere between 2 and 5000 records per query from the PROBLEM table.Here is the query (call it PROBLEM_QUERY)
select round(sum(cm_value*sample_weight),3),round(sum(pm_value*sample_weight),3)
from matched_sample
where state_fips_code = :1
and area_fips_code = :2
and series_type_code = 'B'
and year = :3
and month = :4
[code]...
When we run this job it is very fast (20 minutes) until it gets to the PROBLEM_QUERY, at this point we get DB file scattered read waits, DB file sequential read waits and async IO waits and the job has run for as long as 2 days. When I look at grid, the PROBLEM query is doing a full table scan and ignoring the index.
Our systems people advised us to re-organize and re-index that data in the PROBLEM table after all of the DML and before the PROBLEM_PROCESS takes off. If we do this, it works and cuts our time down alot. However, the re-organize and re-index process adds eight hours to this process. Note: When we re-roganized the data we create a copy table on another set of table spaces and insert the data into the copy sorting the data on the primary key columns. After this we re-index the new table and drop the old table renaming the new table. When we do this later, we move the data back to another set of table spaces. So, we move the data back and for between table spaces A and B we can say.
We have Oracle installed on a M5000 server with Solaris as the OS the binaries and data files are stored on a NetApp Storage array(model 3160) of 500 1TB SATA 7200 RPM drives. However, there are 128 other databases on the NetApp filer as well.
IMO the array and the slow disks are the problem. I believe this because we are catering to the slow disks by re-organizing and re-indexing the PROBLEM table during each run. I don't believe this should be neccessary. We normally re-organize and re-index our data each week in our production system after many more transactions than this.
Our systems people state it is our application. Oracle Support tells us the statistics are out of date but have not answered us on why the statistics are out of date and the index is abandoned after 1 run.
We are experiencing Network waits on one of our 2-node clustered databases...In every 1 hour of clock time we are finding 700-900 seconds of Network waits
From the AWR data I find that "ARCH wait on SENDREQ" is one of the main constituent for these Network waits and as such I suspect Network between Production database and its corresponding database might be slow
Question 1) Does this understanding look correct?
Question 2) Apart from the above what could be the other causes of the Network waits. Can we point out any particular area from following AWR extract...Seeing some gc* waits initially I thought it might be due to slow interconnect between the cluster nodes but some google search denotes it is not the case...So what could be other causes? I mean which network link I would check?
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 22631 22-May-13 10:00:11 976 7.9
End Snap: 22632 22-May-13 11:00:28 978 8.1
Elapsed: 60.29 (mins)
DB Time: 795.66 (mins)
[code].....
Is there any relationship b/w tuning BUFFER CACHE and BUFFER BUSY WAITS?
1) Buffer Busy Waits are happening as the User process found the same Datablock is being used by another user in the BUFFER CACHE.
2) And also happens, when the server process found the same Datablock are being used in the Datafile.
Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
[code]...
During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.
During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION
[code]...
However instead of self join when I creaed global temporary table as
create global temporary table t as select * from invoice_header where release='A5'
And used it in the update as
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)
It updated the records in a second!!
Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?
know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case
follow SQL query would lead to an ORA 01850: hour must be between 0 and 23 error? I'm getting this error don't not sure if this query is causing it.
select
mlh_date_begin,
mat_desc,
mlh_alert_text,
mat_code
from s_mlh_mem_alert_history,
s_mat_member_alert_type
where mlh_mem_uid=f_mem_uid
and mlh_date_begin <= trunc(sysdate)
[Code]...
I am receiving the following Oracle ERROR:
HIPDAO -> getXAlerts():SQLExceptionORA-01850: hour must be between 0 and 23
HERE is the code where it's occuring:
public List xMethod(
int memUID)
throws DAOException {
[Code].....
The error doesn't make sense because I don't think I'm setting a date anywhere in the code.
I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.
SERVER_NAME DATE_TIME PRO_VITUAL_BYTES
MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00
[Code]....
After prepared this xml . I make some edit(like add two record) in my emp and dept table i need every one hour update of my xml . How i will do this one ?
select xmlelement("AllDepartments",
xmlagg(
xmlelement("Department", xmlattributes(d.deptno as deptno, d.dname as dname),
xmlagg(
[code].........
how to extract hour part from a date?
suppose my date is like
29-mar-2004 09:20:34
i wanna get only hour from the above date-
Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic
1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.
total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.
i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.
can i get in this format?
I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours
I'm looking for a way to say if a time range includes a specific hour.
If I was looking to work out if 01:00 to 04:00 includes 02:00, then this works:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('01:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
But, if the time go over two days (with midnight in between), it doesn't work:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('18:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct
Quote:trunc(sysdate+1) +4/24
The next interval will show 9:25:20pm.
Quote:trunc(sysdate+1) + interval '4' hour
Incorrect syntax
I have been trying to develop a script for generating ADDM Reports every hour and save it in a directory on the server. I was able to develop a script to run the AWR Reports for every hour and save them in a directory, but I ran into troubled waters in the ADDM script.
Database Version : Oracle Database Enterprise Edition 10.2.0.3
OS : IBM AIX 5.3
I'm trying to debug the script below to generate ADDM reports on a per hour basis and save them in a folder as well as mail than to a particular entity.
########################################################################################
# Set up Oracle environment variables...
#------------------------------------------------------------------------------
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/oracle/app/oracle/product/10.2.0/bin:/usr/bin/X11:/sbin:.:/oracle/app/oracle/product/10.2.0/bin
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_SID=sarcotest2.ora
export ORAENV_ASK=NO
[Code].....
Initially, the script did return me an ADDM report, but the problem was that it generated the report for only a set of two snapshots (eg: 410110 and 410111). later when the snapshots advanced further, it still generated the same report for the same snapshots.
Now, it seems it have made several changes to the script in the declare and dbms_advisor section and the reports are not being generated anyway.
One problem I am facing is
ORA-13605: The specified task or object ADDM_UAT does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2043
ORA-06512: at "SYS.DBMS_ADVISOR", line 560
ORA-06512: at line 1
It seems the task is not being generated at all in my user's schema.
In an 11g database is it possible to create an awr report including the current hour or do I always have to wait till the full hour?
View 1 Replies View RelatedI have the following query:
SELECT
d_dtm,
BTS_ID,
CASE WHEN D_DTM = (D_DTM-24/24)
THEN sum(V_ATT_CNT)
[Code]....
But it is not returning any results because of the "having" clause. I know it should return results because all I want it to do is in one column have the V_ATT for the current time period, and in the 2nd column, have the V_ATT 24 hours ago. I've checked the data and I should get results back but can't seem to figure out why this is not working...
I have logged into Oracle and Discoverer several times and every time when I'm running a report in Discoverer, it quits responding. This is after I already have the data, and I'm scrolling to the bottom of the data to get the next 10,000 rows.
I just get the "hour glass symbol" and then i basically have to shut down and restart my computer.how to recover from these type of hour glass symbol.
Oracle Version: 11.1.0.7.0
Active Dataguard
Statspack has been configured for Active Dataguard on Primary database.We got an spike of Buffer busy waits for about 5 min in Active Dataguard, this was causing worse Application SQL's response time during this 5 min window.Below is what i got from statspack report for one hour
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 18611 21-Feb-13 22:00:02 236 2.2
End Snap: 18613 21-Feb-13 23:00:02 237 2.1
Elapsed: 60.00 (mins)
[code]...
Why there could sudden spike of demand on UNDO data in Active Data Guard ?
I am getting the ORA-01555 Snapshot Too Old error while excecuting my procedure (which is running in OLTP system). My procedure contains one table which master table of my DB which holds 1.2 GB of data. The proc is using the MERGE statement which is inserting or updating the data in the master table, while the condition of MERGE is using the subquery in which 2 DBlink table is used (in which one table is updating quite frequently). The proc. is executing in the interval of 6 hrs i.e 4 times a day and its is scheduled in job. find the proc. as an attachment.
View 3 Replies View RelatedI am unable to drop a snapshot , i tried even from sys, it is giving the following error,
drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists
but, when i try to create a new snapshot with the same name
CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.
our DB machines time is one hour ahead of Active Directory, as we sync our time with AD, is there any contribution of Database in the wrong time of solaris machines ? and for the resolution of problem, what can be checked at DB side.
View 1 Replies View RelatedI wish to know about Controlfile Snapshot in RMAN. Is this equalent to controlfile backup?
View 4 Replies View Related