Server Administration :: Contention On Data Blocks?

Oct 22, 2010

We are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.

My main question is

1) Should i need to increase freelist value (Right now my value is 1)

2) Or i have to move on segment space management = auto

SQL> select * from v$waitstat;

CLASS COUNT TIME
------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.

View 1 Replies


ADVERTISEMENT

Server Administration :: Continuously Changing Data Blocks?

Jan 10, 2011

If the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?

View 19 Replies View Related

Server Administration :: How To Reclaim Temp Free Blocks

Jul 26, 2011

we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?

TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;

TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426

1 row selected.
further when i checked the session details using TEMP segment, i got below output:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE

3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?

there is one more column - SEGBLK#
explain what is the exact meaning of this column ?

to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;

View 3 Replies View Related

Server Administration :: How To Repair Blocks That Gets Corrupted Found In Dbverify

Aug 8, 2013

how to repair the blocks that gets corrupted found in dbverify

View 8 Replies View Related

File Size (7680000 Blocks) Exceeds Maximum Of 4194303 Blocks

Sep 9, 2012

While increasing the tablespace i am getting below error. How to handle this

SQL> set lin 300
SQL> col TABLESPACE_NAME for a25
SQL> col FILE_NAME for a65
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB
2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;

TABLESPACE_NAME FILE_ID FILE_NAME AUT MB
------------------------- ---------- ----------------------------------------------------------------- --- ----------
SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300

SQL> Alter database datafile 3 RESIZE 60000M;
Alter database datafile 3 RESIZE 60000M
*
ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block

View 3 Replies View Related

Fast Way To Retrieve All Data Blocks

Apr 19, 2012

I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:

select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one

my question is can retrieve all the data in one block more quick than this query.

View 5 Replies View Related

Forms :: Export Two Data Blocks To Excel

Feb 19, 2011

We are having two data blocks as Earnings and Deductions. We need to export this to an excel in single sheet parallel [ imagine your payslip format ].

if we use normal text_io we are not able to get the result we want. so we have tried using a package called export2excel. we achieved what we want. The form is working perfectly in client server concept. When we move the same form to our Unix application server, it is not working.

View 1 Replies View Related

Forms :: Unable To Fetch Data Using Control-blocks

Dec 24, 2010

I have created two non base table text items(from date, to date) and if values are not entered from front end i.e. null then hadrcoded in pre_query trigger to default values.But even i enter values dynamically from front end and when ever i press fetch button ,it is taking default values i.e. it is not accepting values what i have entered.

View 6 Replies View Related

Forms :: Entering Multiple Data In A Form Containing 4 Blocks

Jul 18, 2011

I have 4 blocks in my form which is basically used for travel booking for the employees in a company within India

1) Header block :- contains info abt the person who is booking the tickets for number of employees. Here i have given booking no a primary key.

2) Employee Detail :- Here the basic info of an employee is entered. Here i have taken booking number as a foreign key and then given emp_cd & booking number as a composite primary key.

3) Travel Detail :- Here the travel detail of individual employee will be entered wherein a unique trv_no will b generated 4 every single travel. Again i have taken foreign key as bkng_no frm 1st blck and emp_cd frm 2nd blck and tkn a composite primary key which comprises of bk_no,emp_cd and trv_no. this is used to maintain the uniqueness for single travel.

4) Vehicle Hotel Details :- This block is placed on different canvas for same form.It is meant for Other details in which details regarding hotel,vehicle etc booking aftr reaching the destination is entered. In this block thrs no primary key, but i hv taken the composite primary key of 3rd block as a foreign key since thr will be multiple entries for this one entire travel.

At every level there will be multiple entries for each corresponding entered record. I am able to enter one single record properly i.e. for 1 emp i am able to enter multiple travel details and his other requirements but asi try to enter more then one employee info, his travel details, other requirements i face an error stating foreign key constraint violated.Parent key not found for 3 rd level block.

How can i get the desired o/p wherein all the multiple records for every single subsequent record are stored correctly taking all the constraints in to consideration.

View 8 Replies View Related

Server Utilities :: Total Estimation Using BLOCKS Method - Datapump

Apr 5, 2013

I have analyzed that, datapump estimation is 9.902GB. When i check size of .dmp file, it's shows 1.44Gb.

Export: Release 11.2.0.1.0 - Production on Fri Apr 5 02:00:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_LVGITRN_30_24_050413.dmp directory=DP_DIR logfile=expdp_LVGITRN_30_24_050413.log full=y exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: [bold]9.902 GB [/bold]

Why Datapump estimate so much than actual size?

View 8 Replies View Related

Enq - TX - Row Lock Contention?

Oct 10, 2012

In my current project, I am handling an Oracle database with version 10.2.0.3. I analyzed the AWR report for the past month abd saw the following wait events consistently:

CPU time - 45.6% (% Total Call Time)
db file sequential read - 30.6% (% Total Call Time)
enq: TX - row lock contention - 18.8 % (% Total Call Time)

I have uploaded the report for your reference.What should be my approach to troubleshoot this?

Attached File(s)

 awr_report.html ( 382.28K )
Number of downloads: 6

View 1 Replies View Related

SQL & PL/SQL :: Contention Between JOB_QUEUE_PROCESSES And Processes

Aug 30, 2011

In the program i wrote, posted in this thread I parallelize 340 jobs.

To do this, according to oracle documentation, i set the job_queue_processes parameters to 450. This works fine, all jobs are submitted, but, when i chek the dba_scheduler_job_run_details, i notice that there is a difference between REQ_START_DATE, which is my requested start date, when i enabled my job, and the ACTUAL_START_DATE, which is the date on which Oracle start this process.

And the more i submit job, the more difference between ACTUAL_START_DATE and REQ_START_DATE increase. But for each job, run duration is less than 1second. I check the processes parameter, and it is set to 100. if i have to increase the processes parameter to 450 too ?

View 5 Replies View Related

Performance Tuning :: Getting Enq - TS - Contention?

Jul 26, 2011

I am getting enq: TS - contention as top event in AWR Report.where can i look into database to fix this issue.

View 14 Replies View Related

TX - Index Contention On Update

Sep 3, 2012

I have a UPDATE statement which sets values on non-indexes columns and do not use indexed-column in where clause (so every column used in statement are not indexes). Lastly, I had some problem with database and saw in ASH report in TOP SQLs that update with events enq: TX - index contention and buffer busy waits. What does it mean? I read a lot of information about index contention, but always related to INSERT and DELETE. What has UPDATE to do with this contention?

View 3 Replies View Related

Server Administration :: Moving 10 GB Data To 200 GB Database Server

Jun 22, 2011

We have an Oracle Server database of Size 50 GB having 10 GB Data. And Planning to have a new Database Server of 200GB . So my question is after moving all the 10 GB data to 200 GB Database Server, will the performance of the system come down? Will it reduce the speed?

View 9 Replies View Related

Row Lock Contention - Trace File Shared

Nov 19, 2012

I 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]....

View 3 Replies View Related

Server Administration :: Data Refresh Between 8i And 11g?

Mar 19, 2012

The developer team wants to test their application. For the same they need some data which resides at oracle 8i instance. However they are not able to create a dblink between 11g and 8i as this is not supported by oracle.

Now they want the data to be refreshed from 8i to 11g by exp/imp.

Apart from exp and imp is there is any other alternatives?

View 2 Replies View Related

Server Administration :: RAW Data-type?

Apr 3, 2013

RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.

View 5 Replies View Related

Server Administration :: Synchronize Data Between Two Database

Jul 3, 2013

Now i have one problem. I have two database oracle 10g and install on two server (call is A and B). I already create database link between two database. Each database has over 200 tables.

Basically structure two database is same, but database A have data real time, database B is stand by server. Now i want to synchronize all data of database A to database B. What should i do?

View 4 Replies View Related

Server Administration :: Flashback Data Archive

Aug 23, 2011

I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);

So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.

create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;

create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;

alter table teta_admin.t_prac flashback archive audit_flash_archive;

and everything works fine but on sys user.
i can query this table using "as of timestamp" clause

select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')

but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.

interface user have such sys privs:

SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO

and table privs:

SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT

what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.

View 9 Replies View Related

Server Administration :: No Data To Read From Socket

Feb 5, 2010

I am getting an error through by application when i am trying to access a screen.

Error:No data to read from socket.

What could be the reasons for this error.

View 1 Replies View Related

Server Administration :: Data Migration Between Two Different Application?

Mar 9, 2011

We are planning to migrate data from an application called clintrace to another application called argus safety. Both the applications are related to pharmacovigilence safety operation. Both the applications functionality are similar. So both the database are having the same data though the table structures might be different. Both the database are oracle clintrace db is 9i and argus db is 11g.

View 8 Replies View Related

Server Administration :: Fresh Installation With Old ORA Data

Jan 6, 2013

we had a db working fine..we just take a proper shutdown of db and then try restart os...os never comes up.we have db installed in separate drive tahn C:(where os reside)

now the os is instaaled again...oracle is gone? i have left with app folder and all the database file in other drive

i do the fresh oracle installation/...now what things from the old app folder should i replace in new app folder created to get all the data back??

View 8 Replies View Related

Server Administration :: Data Block Corruption

May 12, 2011

1) What is PHYSICAL/LOGICAL Corruption.
2) How it occurs.
3) Will RMAN works on both the types of corruption or only Physical (My senior told it works on both).

View 3 Replies View Related

Server Administration :: How To See Whether Table Data Using Compression

Mar 13, 2013

1)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.

2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.

OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PAYMENT_SWP DISABLED

OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PREPAYMENT_SWP DISABLED

3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.

4) Below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.

if above statement is correct, then how to find out whether table data is using block level compression ?

5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.

confirm what is the conclusion on this ?

SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
/
2 3 4 5 6 7 8 9 1

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;

MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs

(ii) 2nd table

SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
2 3 4 5 6 7 8 9
10 /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;

MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA

View 3 Replies View Related

Server Administration :: Size Of Data Of Table

Oct 20, 2011

ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19 35 2810 3

Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.

View 32 Replies View Related

Server Administration :: Long Data Type Illegal?

Dec 26, 2012

when I try comnd create table a as select * from b where 1=2; it says illegal datatype long..i m bemused what sin has the long datatype done?

View 4 Replies View Related

Server Administration :: What Can Be Maximum Size Of Data File

Nov 24, 2011

I am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.

View 3 Replies View Related

Server Administration :: Pooling Meta Data Into New Schema?

May 22, 2012

I have created different scripts to monitor growth planning of the database my idea was generate a simple output file to a directory and create a report and send it as mail thru mail server. but the client requested me to create new schema and population all the stats i have collected into the tables in this schema .

How to pool all the stats from different databases into this new schema efficiently.

View 3 Replies View Related

Server Administration :: Data File / Table Relation

Feb 25, 2011

I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.

View 2 Replies View Related







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