Tuning

Tuning:
1st
/
>alter system set optimizer_mode=’choose’;
>alter system set optimizer_index_cost_adj=1;
>alter system set optimizer_index_caching=100;
9i>alter system set timed_statistics=true;
10g>alter system set statistics_level=typical;
/
2nd
/
a.$top
>select pid from v$process where spid=222;
>select sid,sql_id from v$session where sid=123;
>select sql_text,sql_fulltext from v$sqlarea where sql_id=’AXQQQ’;
(Or)
b.>select sql_text,(1-(disk_reads/buffer_gets))*100 from v$sqlarea order by 2; (<90% is problem) (or)
c.>Select sql_id,start_time from v$session_longops order by start_time;                                        >select sql_text from v$sqlarea where sql_id=’   ‘;                        (or)
d.>Select sql_text,disk_reads from v$sqlarea order by disk_reads desc; (top query)            (or)
e.>select  sql_text, executions from v$sqlarea order by execution desc;
/
3rd
/
   >select * from emp where eno=1;
> set timing on;
>analyze table emp compute statistics;
>select * from emp where eno=1;
Output:
/
Execution plan:
Statistics:
Physical reads:
Db block gets:
Consistence gets:
/
disks-PR,current-db,query-cgets
Check problem:
(1-(PR/(db+cgets)))*100     output is above 95% mean no problem
Tune  the Index:
>analyze index ind_eno validate structure;
Check Index fragmentation:
>select (del_lf_rows/lf_rows)*100 from index_stats where name=’IND_ENO’;
Output:
< 20% means no fragmentation
>20% means fragmentation
Rebuild Index:
>alter index ind_eno rebuild;
Segment IO Tuning
1.Row chaining check
>@?/rdbms/admin/utlchain.sql
>analyze table emp list chained rows;
>select count(*) from chained_rows where table_name=’EMP’;
Output:
Count =0 no problem
Count > 0 means problem
>alter table emp move tablespace users;
(Or)
Export and drop and import
Check high watermark :
>analyze table emp compute statistics;
>select blocks from dba_segments where segment_name=’EMP’;
Out put:
Ex: 8(total block)
>select blocks,empty_blocks from dba_tables where table_name=’EMP’;
Output:
Ex: used block=5 and empty blocks=2  and remaining 1 block is use to extents
>select count(distinct dbms_rowid.rowid_block_number(rowed)) from emp;
Output: 2 means remaining 3 is deleted records so different is >0 means problem
So export and drop and import
Using TK Prof:
>alter session set sql_trace =true;
>select * from emp where eno=1;
>alter session set sql_trace =false;
Check udump
$tkprof /opt/oracle/udump/ora_test_111.trc output.log
$vi output.log
Disks =PR
Current=db gets
Query =consistence gets
Explain plan:
>@?/rdbms/admin/utlxplan.sql
>explain plan set statement_id =’emp_id’ for select * from emp eno=1;
>select * from plan_table where statement_id=’emp_id’;
Output: little bit same for tkprof