Tuning-test

SQL> desc test;

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------------------- EID                                                NUMBER(30)

 ENAME                                              CHAR(2000)

 CITY                                               CHAR(2000)

SQL> create bitmap index test_ind on test(eid);

Index created.

[Insert 1000000 records and then delete little bitt records....]

SQL> delete from test where ename='name3';

delete from test where ename='name3'

            *

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'APPS_UNDOTS1'

SQL> select name,bytes/1024/1024 from v$datafile;

SQL>alter database datafile '/oracle/PROD/db/apps_st/data/undo01.dbf' resize 3g;

SQL>  delete from test where ename='name3';

351123 rows deleted.

Find the long running query:

SQL> Select sql_id,start_time from v$session_longops order by start_time;

SQL_ID        START_TIME

------------- ---------------

              20-APR-11

              20-APR-11

2y4b2utv5q4cg 20-APR-11

bk5m23jsmc2cm 20-APR-11

              20-APR-11

b0j5vyaykbb0p 20-APR-11

              20-APR-11

              20-APR-11

              20-APR-11

              21-APR-11

10 rows selected.

SQL> select sql_text from v$sqlarea where sql_id='b0j5vyaykbb0p';

SQL_TEXT

----------------

 delete from test where ename='name3'

Find the Owner of the table:

SQL> select owner,segment_name from dba_segments where segment_type='TABLE' and segment_name='TEST';

OWNER                 SEGMENT_NAME

-------------              -------------------------

SYS                           TEST

TEST                         TEST

SQL> alter session set sql_trace =true;

Session altered.

SQL> delete from test.test where ename='name3';

351123 rows deleted.

SQL> alter session set sql_trace =false;

Session altered.

[appprod@dev udump]$ tkprof prod12_ora_11741.trc  testtable1.log

TKPROF: Release 10.2.0.4.0 - Production on Thu Apr 21 16:48:15 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

[appprod@dev udump]$ vi testtable1.log

SQL> select (1-(796940/(916095+1051982)))*100 from dual;

(1-(796940/(916095+1051982)))*100

---------------------------------

                       59.5066656

SQL> analyze index test.test_ind validate structure;

Index analyzed.

SQL> select (DEL_LF_ROWS/LF_ROWS)*100 from index_stats where name='TEST_IND';

select (DEL_LF_ROWS/LF_ROWS)*100 from index_stats where name='TEST_IND'

                   *

ERROR at line 1:

ORA-01476: divisor is equal to zero

SQL> select DEL_LF_ROWS,LF_ROWS from index_stats where name='TEST_IND'

  2  ;

DEL_LF_ROWS    LF_ROWS

----------- ----------

          0          0
SQL> @?/rdbms/admin/utlchain.sql 

SQL> analyze table test.test list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows where table_name='TEST';

  COUNT(*)

----------

         0

SQL> select blocks from dba_segments where segment_name='TEST';

    BLOCKS

----------

        15

    982272

SQL> select blocks,empty_blocks from dba_tables where table_name='TEST';

    BLOCKS EMPTY_BLOCKS

---------- ------------

    980827         1445

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test.test;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

---------------------------------------------------

                                             622923

SQL> select (980827-622923) from dual;

(980827-622923)

---------------

         357904

[appprod@dev 10.2.0]$ exp log=/oracle/test2.log file=/oracle/test2.dmp tables=test.test

SQL> drop table test.test;

Table dropped.

[appprod@dev 10.2.0]$ imp log=/oracle/imptest.log file=/oracle/test2.dmp tables=test fromuser=test touser=test

SQL> SELECT BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TEST';

    BLOCKS

----------

        15

    629504

SQL> analyze index test.test_ind validate structure;

Index analyzed.

SQL> SELECT BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST';

    BLOCKS EMPTY_BLOCKS

---------- ------------

    629504            0

SQL> SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) FROM TEST.TEST;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

---------------------------------------------------

                                             622923

find highwatermark in session

SQL> select sessions_highwater from v$license;