Scripts and process to check the fragmented table details. of the query and improve the performance

1.Check the execution time of the table.

set timer on;
select /*+ gather_plan_statistics */ count(*) from <Table_name> where <Conditions>;



2.Check the BLOCKread and Physical reads
   Generate explain plan and Check the execution plan also

set autotrace traceonly stat
select * from <table_name> where <Conditions>;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     153854  consistent gets
     153847  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed




3.Check the count(*) of the table.

select /*+ gather_plan_statistics */ count(*) from <Table_name> where <Conditions>;




4.check the size of the table.

select sum(bytes)/1024/1024 Size_MB from dba_segments where segment_name='<TABLE_NAME>';




5.Check the table fragmentation details.

---//
---// table fragmentation in Dev //---
---//
 select table_name,round((blocks*8/1024),2) "size (MB)" ,
  round((num_rows*avg_row_len/1024/1024),2) "actual_data (MB)",
  (round((blocks*8/1024),2) - round((num_rows*avg_row_len/1024/1024),2)) "wasted_space (MB)"
  from dba_tables
  where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
  and table_name = 'EVENT_DETAILS';

TABLE_NAME       size (MB) actual_data (MB) wasted_space (MB)
--------------- ---------- ---------------- -----------------
EVENT_DETAILS       1206.9          1001.36            205.54





6.Check the  High watermark.

---//
---// EVENT_DETAILS high water mark from Dev //---
---//
select t.table_name, s.blocks  "Block Allocated",
t.blocks "Block Used", (s.blocks-t.empty_blocks-1) "High Water Mark"
from user_tables t, user_segments s
where t.table_name=s.segment_name
and t.blocks <> (s.blocks-t.empty_blocks-1) and t.table_name ='EVENT_DETAILS';


TABLE_NAME      Block Allocated Block Used High Water Mark
--------------- --------------- ---------- ---------------
EVENT_DETAILS            155648     154483          155647






7.Do the table RE-ORG and STATS collection.

   
---//
---// reorganize fragmented table in QA //---
---//
SQL> alter table <table_name> move tablespace <tablespace_name>;

Table altered.


---//
---// gather table stats in QA //---
---//
SQL> exec dbms_stats.gather_table_stats('MYAPP','EVENT_DETAILS',degree=>2);

PL/SQL procedure successfully completed.
 





8.Check the TABLE FRAGMENTATION deatils and HIGH watermark details again.

9.Check the excution time

10.Check the physical and block reads again.



If still the performance problem exist, Create index on the condition columns.

check the EXECUTIOn PLAN and EXECUTION time and PHYSICAL READS and BLOCK READS