Feeds:
Posts
Comments

Posts Tagged ‘MySQL’

Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type – related_details.

The V$EVENT_NAME view provides a complete list of all the enq: wait events.

TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Several Situation of TX enqueue:
————————————–
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.

3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.

Troubleshooting:

for which SQL currently is waiting to,

select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in (‘WAITING’)
and wait_class != ‘Idle’ and event=’enq: TX – row lock contention’
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

Advertisements

Read Full Post »

To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements. The SQL Access Advisor enables to optimize data access paths of SQL queries by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.

A materialized view provides access to table data by storing the results of a query in a separate schema object. A materialized view contains the rows resulting from a query against one or more base tables or views.

A materialized view log is a schema object that records changes to a master table’s data, so that a materialized view defined on the master table can be refreshed incrementally.

The SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A functional index derives the indexed value from the table data. For example, to find character data in mixed cases, a functional index can be used to look for the values as if they were all in uppercase characters.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

1)BFILE:
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.

2)BLOB:
Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.

So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
——————————————-
32

As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)

Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.

3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte

4)CHAR VARYING
Maximum size: 4000 bytes

5)CLOB
Same as BLOB in the range of 4T to 128T.

6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters

7)LONG
Maximum size: 2 GB – 1

8)NCHAR
Maximum size: 2000 bytes

9)NCHAR VARYING
Maximum size: 4000 bytes

10)NCLOB
Same as BLOB in the range of 4T to 128T

11)NUMBER
Maximum size: 999…(in this way 38 9s) * power(10,125)
Minimum size: -999…(in this way 38 9s) *power(10,125)

12)RAW
Maximum size: 2000 bytes

13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.

14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.

Read Full Post »