Feeds:
Posts
Comments

Posts Tagged ‘Commit (data management)’

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

Small example to find the table fragmentation.

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              72952          30604.2

Note = 72952 – 30604 = 42348 Kb is wasted space in table
 

The difference between two values is 60% and Pctfree 10% (default) – so, the table has 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation?

For that we need to reorganize the fragmented table.

We have four options to reorganize fragmented tables:

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition

Option: 1 “alter table … move + rebuild indexes”

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1’;

STATUS INDEX_NAME
——– ——————————
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1’;

STATUS INDEX_NAME
——– ——————————
VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              38224           30727.37

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;

TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              85536            68986.97

SQL> select status from user_indexes
2 where table_name = ‘BIG1′;

no rows selected

SQL> –Note we need to create all indexes.

Option: 3 “export / truncate / import”

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = ‘BIG1′;

STATUS
——–
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:>exp scott/tiger@Orcl file=c:big1.dmp tables=big1

Export: Release 10.1.0.5.0 – Production on…..

Export terminated successfully without warnings.

SQL> truncate table big1;

Table truncated.

imp scott/tiger@Orcl file=c:big1.dmp ignore=y

Import: Release 10.1.0.5.0 – Production on….

Import terminated successfully without warnings.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’BIG1′);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;

TABLE_NAME size
—————————— ——————————————
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1’;

TABLE_NAME size
—————————— ——————————————
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = ‘BIG1’;

STATUS
——–
VALID

SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,’BIG1′,-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 “dbms_redefinition”….Which we will disscus afterwords….

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

Advertisements

Read Full Post »

Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.

We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.

Here is a test. Inside test schema I have made an example.

Connect as test user and create three tables.

SQL> conn test/test
Connected.

SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.

SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.

SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.

2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.

SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.

3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string NONE

SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.

4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,’A’,’B’);
1 row created.

SQL> commit;
Commit complete.

5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.

SQL> conn / as sysdba
Connected.

SQL> startup force
ORACLE instance started.

Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.

SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string DB, EXTENDED

6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.

SQL> insert into test values(1,’Tes’,’T2′);
1 row created.

SQL> commit;
Commit complete.

7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected

SQL> select * from test1;
A B C
———- — —
1 Tes T2

SQL> select * from test2;
A B C
———- — —
1 Tes B

8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.

SQL> insert into test values(2,’Test’,’Test2′);
insert into test values(2,’Test’,’Test2′)
*
ERROR at line 1:
ORA-12899: value too large for column “TEST”.”TEST1″.”B” (actual: 4, maximum:3)
ORA-06512: at “TEST.TEST_T”, line 2
ORA-04088: error during execution of trigger ‘TEST.TEST_T’

SQL> select username,sql_text from dba_audit_trail;
USERNAME                             SQL_TEXT
————–                       ————————–
TEST                                 insert into test values(2,’Test’,’Test2′)

Read Full Post »

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;

Read Full Post »