set serveroutput on;
declare
val1 number(10,2);
val2 number(10,2);
val3 number(10,8);
begin
select value into val1 from v$sysstat Where name in ('redo entries');
select value into val2 from v$sysstat Where name in ('redo log space requests');
val3:=(val2/val1);
if val3<.0002 then dbms_output.put_line('The ratio (redo log space requests/redo entries)is ok 'val3); end if; if val3>.0002 then
dbms_output.put_line('Increase the size of the redo log buffer 'val3);
end if;
end;
/
set serveroutput off;
/
_______________________________________________________________________________
Buffer Cache tune:-
set serveroutput on;
DECLARE
DB_BLK NUMBER(10,2);
CONS_GET NUMBER(10,2);
PHY_RDS NUMBER(10,2);
FINAL NUMBER(10,2);
begin
Select value INTO DB_BLK From v$sysstat Where name in ('db block gets');
Select value INTO CONS_GET From v$sysstat Where name in ('consistent gets');
Select value INTO PHY_RDS From v$sysstat Where name in ('physical reads');
FINAL:=(1-(PHY_RDS/(DB_BLK+CONS_GET)))*100;
if final>90 then
dbms_output.put_line('The BUFFER CACHE HIT RATIO IS OK 'final);
end if;
if final<90 style="font-style: italic;">tune SORT_AREA_SIZE:-
Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');
--If large number of sorts require I/O to disk,increase the initialisation parameter SORT_AREA_SIZE
_____________________________________________________________________________________
Tune Library cache:-
set serveroutput on;
DECLARE
hit NUMBER(10,2);
reload number(10,2);
begin
Select ((sum(pinhits) / sum(pins))*100) into hit From v$librarycache Where namespace in ('SQL AREA','TABLE PROCEDURE','BODY','TRIGGER');
select ((sum(reloads)/sum(pins))*100) into reload From v$librarycache Where namespace in ('SQL AREA','TABLE PROCEDURE','BODY','TRIGGER');
IF hit<85>85 then
dbms_output.put_line('THE hit ratio >85%. check shared pool and open cursor parameter 'hit);
end if;
dbms_output.put_line('*************************************************************************');
if reload>.02 then
dbms_output.put_line('The reload percent should be very low, 2% (i.e. 0.02) or less. this is ok'reload);
end if;
if reload<.02 then dbms_output.put_line('The reload >2% 'reload);
end if;
end;
/
set serveroutput off;
/
_____________________________________________________________________________________
Tune Dictionary cache:-
set serveroutput on;
DECLARE
ROWCACHE NUMBER(10,2);
BEGIN
Select ((sum(getmisses) / sum(gets))*100) INTO ROWCACHE From v$rowcache;
IF ROWCACHE<15>15 then
dbms_output.put_line('increase the initialisation parameter SHARED_POOL_SIZE 'rowcache);
end if;
END;
/
set serveroutput off;
/
____________________________________________________________________________________
How to find the Chained rows in table:-
1.We have to run the utlchain.sql file in the Schema
(where we want to find the chained rows) to create CHAINED_ROWS table.
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Then it will create CHAINED_ROWS table.
2.Execute the below command :-
SQL>analyze table
3.Query the CHAINED_ROWS table to get the details of the chained rows.
(or)
SELECT 'Chained or Migrated Rows = 'value FROM v$sysstat
WHERE name = 'table fetch continued row';
_____________________________________________________________________________________
Tuning of Indexes:-
grant execute on dbms_stats to &User_name;
grant select on index_stats to &User_name;
connect &Username/&Password
exec dbms_stats.gather_schema_stats(ownname=>'&User_Name');
(or)
analyze index owner.index_name validate structure;
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 "Wasted Space"
FROM Index_stats WHERE name IN (select index_name from dba_indexes where owner like 'USER_NAME');
if the value is more then 20% of any Index, then rebuild the Index.
_____________________________________________________________________________________
Compiling all views in a Schema: -
spool comp_all_views
select 'alter view 'owner'.'view_name' compile;' from dba_views
where owner = 'user_name';
spool off
@comp_all_views.lst
____________________________________________________________________________________
To bring Table read_only:-
1. SQL>alter table (table_name) constraint (constraint_name) check(1=1);
2. SQL>alter table (table_name) disable validate constraint (constraint_name);
To bring Table read_write:-
1. SQL>alter table (table_name) enable validate constraint (constraint_name);
____________________________________________________________________________________
How to See the used & free space in Temporary Tablespace: -
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
(or)
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
____________________________________________________________________________________
select sum(bytes), s.owner, s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'TABLE_NAME'
and s.segment_name = l.segment_name
and s.owner like 'USER_NAME'
group by s.owner,s.segment_name,s.segment_type;
-Will get exact LOBSegment size.
-If we want to get total size of the table (with LOBSegment): -
select sum(bytes), s.owner, s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'TABLE_NAME'
and s.segment_name = l.segment_name
and s.owner like 'USER_NAME'
group by s.owner,s.segment_name,s.segment_type;
--------(+) add both outputs--------
select bytes from dba_segments where segment_name like 'TABLE_NAME'
and owner like 'USER_NAME';
----------------------------------------------------------------------------------------------------------------------------
Checking CPU utilization for all users: -
select ss.username,se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;
_______________________________________________________________
Table size (with fragmentation):
select table_name,round((blocks*8),2)'kb' "size" from user_tables where table_name = 'TABLE_NAME';
Actual data in table:
select table_name,round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'TABLE_NAMEA';
Actual fragmentation size:
select table_name,round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) "Size in KB" from user_tables where table_name = 'TABLE_NEME';
_______________________________________________________________
Query to get number of partition rows for table: -
select partition_name,num_rows from dba_tab_partitions
where table_name = '& TABLE_NAME'
and table_owner = '&TABLE_OWNER';
(It will output upto LAST_ANALYZED time)
&
Declare
Cursor c1 is
select table_name,partition_name from all_tab_partitions where table_name ='&TABLE_NAME';
v_sql varchar2(2000);
temp_var number:=0;
BEGIN
for rec in c1
Loop
execute immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' into temp_var;
dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||temp_var||' rows');
END LOOP;
END;
&
select 'select count(*) from '||&TABLE_OWNER||'.'||&TABLE_NAME||'partition ('||PARTITION_NAME||');'
from dba_tab_partitions
where table_name = '& TABLE_NAME'
and table_owner = '&TABLE_OWNER';
