When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Use Index To Minimize Lock On Parent Table?

Mar 27, 2013

I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?

View 2 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

Alter Index Shrink Space - Table Lock

Oct 18, 2012

alter index test_idx1 shrink space;

I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?

View 5 Replies View Related

PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?

Apr 24, 2013

I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:

CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;

Recreate Steps:

1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data

[code]...

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

View 3 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

Security :: How To Know Which Session Causing TX Lock

Feb 28, 2011

I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.

View 17 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.

I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.

View 5 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using
sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.

View 3 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I have a query which had a join:

a.c1=b.c1 and a.c2=@var

where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like

a.c1(+)=b.c1 and nvl(a.c2,@var)=@var

This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')

But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?

View 2 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Index And Clustered Index

Aug 3, 2010

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and Execution takes place.?

View 4 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Last Used Time Of Index

Apr 7, 2011

I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.

After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.

--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;

View 4 Replies View Related

Index With Degree More Than 1

Jul 29, 2010

There is an index with degree 16 on a rac env. The base table has no degree. The table and index are not partitioned. Does the degree of index only affect index DDL (alter, rebuild etc)? Any effects on query (PQ)?

View 4 Replies View Related

Index In Cache

Apr 21, 2013

i have a application which uses 32 tables for retrieval in this 4 tables are important and have a size more than 100 mb can i move the index of these 4 tables cache memory to improve the applications retrieval performance if i done so ,then that will affect any other applications performance

View 1 Replies View Related

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

SQL & PL/SQL :: Use Of Table Index?

Feb 10, 2012

Am pasting the sample code here, which i got from some site..

DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;

[code]...

Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?

I have tried using this, but doesnt work because loop variables are by default integers:

for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;

View 2 Replies View Related

SQL & PL/SQL :: Explicit Name For Index Of IOT

Feb 18, 2011

Need some Clarification on the below query:

Can we specify explicit name for the index of the IOT.

View 2 Replies View Related

SQL & PL/SQL :: How To Rename A Index

Aug 8, 2011

How can i rename a index? is there a way except for drop and create.

View 2 Replies View Related

When To Use A Reverse Key Index

Jul 3, 2012

I've read some documentation about the reverse key indexes, but I haven't understood when really it can be useful use them. when it's smart to use them according to your experiences?

View 4 Replies View Related

SQL & PL/SQL :: Functional Index

May 19, 2011

How can i turn this into functional index.

WHERE (IPC_DISP_ID IS NOT NULL AND DISP_EXIST > 0)
AND (IPC_UPU_BCD IS NOT NULL AND RECPT_EXIST > 0)
AND ( (IPC_ITM_ID IS NOT NULL AND ITM_EXIST > 0) OR IPC_ITM_ID IS NULL )

View 7 Replies View Related

SQL & PL/SQL :: Delete An Index

Jun 27, 2010

I have a very huge table. There are many indexes.My focus is on the following indexes:

APT_DEDUCTIBLE A_PT_ORDREGBUS_UNIT
APT_DEDUCTIBLE A_PT_ORDREGMD_CODE
APT_DEDUCTIBLE A_PT_ORDREGPT_CODE

[Code]...

My Question is, since this 3 indexes has two columns "Bus_unit and Md_code", which index can i delete. Will they effect on database performance, if i delete one of them?

View 4 Replies View Related

PL/SQL :: Index Not Being Used In Group By

Sep 14, 2012

Here is the scenario with examples. Big table 333 to 500 million rows in the table. Statistics are gathered. Histograms are there. Index is not being used though. Why?

  CREATE TABLE "XXFOCUS"."some_huge_data_table"
   (  "ORG_ID" NUMBER NOT NULL ENABLE,
  "PARTNERID" VARCHAR2(30) NOT NULL ENABLE,
  "EDI_END_DATE" DATE NOT NULL ENABLE,
  "CUSTOMER_ITEM_NUMBER" VARCHAR2(50) NOT NULL ENABLE,
  "STORE_NUMBER" VARCHAR2(10) NOT NULL ENABLE,
  "EDI_START_DATE" DATE,

[Code]...

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT num_rows FROM user_tables s WHERE s.table_name = 'some_huge_data_table';

  NUM_ROWS                                                                     
----------                                                                     
333338434                                                                     

SQL> SELECT MAX(edi_end_date)
  2    FROM some_huge_data_table p
  3   WHERE p.org_id = some_number
  4     AND p.partnerid = 'some_string';

MAX(EDI_E                                                                      
---------                                                                      
13-MAY-12                                                                      

Elapsed: 00:00:00.00

SQL> explain plan for

  2  SELECT MAX(edi_end_date)
  3    FROM some_huge_data_table p
  4   WHERE p.org_id = some_number
  5     AND p.partnerid = 'some_string';

Explained.

SQL> /

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 2104157595                                                                                                                                                                           
--------------------------------------------------------------------------------------------       
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |       
--------------------------------------------------------------------------------------------       
|   0 | SELECT STATEMENT             |             |     1 |    22 |     4   (0)| 00:00:01 |       
|   1 |  SORT AGGREGATE              |             |     1 |    22 |            |          |       
|   2 |   FIRST ROW                  |             |     1 |    22 |     4   (0)| 00:00:01 |       
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| some_huge_data_table_PK |     1 |    22 |     4   (0)| 00:00:01 |       
--------------------------------------------------------------------------------------------       

SQL> explain plan for

  2  SELECT MAX(edi_end_date),
  3         org_id,
  4         partnerid
  5    FROM some_huge_data_table
  6   GROUP BY org_id,
  7            partnerid;

Explained.

PLAN_TABLE_OUTPUT                                                                                  
----------------------------------------------------------------------------------------------------
Plan hash value: 3950336305                                                                                                                                                                            
-------------------------------------------------------------------------------                    
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                    
-------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT   |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   1 |  HASH GROUP BY     |          |     2 |    44 |  1605K  (1)| 05:21:03 |                    
|   2 |   TABLE ACCESS FULL| some_huge_data_table |   333M|  6993M|  1592K  (1)| 05:18:33 |                    
-------------------------------------------------------------------------------      

Why wouldn't it use the index in the group by? If I write a loop to query for different partnerid (there are only three), the whole things takes less than a second.

btw, I gave the index hint too. Didn't work. Version mentioned in the example.

View 18 Replies View Related

Index On Partitioning Key

Mar 13, 2012

There is a very large fact table that is range partitioned by a column DATE_KEY of type NUMBER(38), such that every hour is a different partition. There is a bitmap index BX$FACT#DATE_KEY on field DATE_KEY, which also is a foreign key referencing DATE_DIM (DATE_KEY). There is a different DATE_KEY for every hour, generated as YYYYMMDDHH24.

When I run

"SELECT * FROM FACT WHERE DATE_KEY >= 2012031207 AND DATE_KEY < 2012031208"

to get all the records for 7 am on March 12th, partition pruning kicks in and sees that only one partition is used. The CBO then decides to do a full scan of the partition. This behavior is correct/desired.

If, however, I run

"SELECT * FROM FACT WHERE DATE_KEY = 2012031207 AND DATE_KEY < 2012031209"

to get all the records for 7 and 8 am, Oracle knows that it will have to scan two partitions. The CBO then decides that using the BX$FACT#DATE_KEY must be a good idea and, instead of doing a full scan of the partitions, does access by local index rowid, which is many times slower.

I think I understand the cause - when more than a single partition is involved, Oracle has to use the global index stats (instead of the local ones, like in the first scenario) and the CBO decides to use it because that the selectivity for the global index is great, when in fact the query will return all the rows for that particular partition (no selectivity).

How to get the CBO to choose a full scan in the second scenario as well? I need to support ad-hoc queries generated by a BI tool, so I cannot add hints to the queries. I also can't get rid of the index on DATE_KEY, because in real life the predicates are on fields of the date dimension, not directly on the key, so I need to join on it.

View 1 Replies View Related







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