Here is a very useful query to highlight which tables/indexes would benefit from a reorg/rebuild or a shrink
SELECT segment_name, segment_type, round(allocated_space/1024/1024,3) alloc_mb ,round(used_space/1024/1024,3) used_mb ,
round(reclaimable_space/1024/1024,3) reclaimable_mb, round(reclaimable_space/allocated_space*100,3) pct_wastage
FROM TABLE(dbms_space.asa_recommendations())
where segment_owner=’HILDA’
order by 6 desc
Reminder for me on how useful ASH is
– find snapid where a particular sql occurs
select distinct snap_id from dba_hist_sqlbind where sql_id=’80bd6bb7n33tk’
– find bind variable values for the specified sql
select * from dba_hist_sqlbind
where snap_id = 20637 and trunc(last_captured) >= ‘17-NOV-2011′ and sql_id=’80bd6bb7n33tk’
order by sql_id, position
select snap_id, sample_time, session_id, session_serial#, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event from dba_hist_active_sess_history
where (session_id,session_serial#) in (select session_id,session_serial# from dba_hist_active_sess_history where sql_id=’0trpbjywfsdp6′ and snap_id between 20637 and 20639)
and snap_id between 20637 and 20639 order by snap_id, sample_time
4a2fkw1d4u01u — deletion profile that was blocked
select snap_id, sample_time, session_id, session_serial#, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event from dba_hist_active_sess_history
where (session_id,session_serial#) in (select session_id,session_serial# from dba_hist_active_sess_history where sql_id=’4a2fkw1d4u01u’ and snap_id between 20637 and 20639)
and snap_id between 20637 and 20639 order by snap_id, sample_time
select ‘USER’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from v$sql where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=762 and session_serial#=45940
and snap_id between 20637 and 20642 order by snap_id, sample_time
– blocked ETL Session
select ‘ETL’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from DBA_HIST_SQLtext where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=829 and session_serial#=31419
and snap_id between 20637 and 20642 order by snap_id, sample_time
–USER1@host2( SID=915 ) is blocking ETL_USER@host1( SID=829 )
– blocked by 915, which funnily enough only active 17 Nov from 17:44 till 22:15 - somehow this session did not release the lock at all until killed the following day
select ‘USER’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from DBA_HIST_SQLtext where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=915 and session_serial#=2165
select * from dba_hist_active_sess_history a
where session_id=915 and session_serial#=2165
select * from dba_tab_columns where column_name=’HOST_NAME’ and table_name like ‘DBA_HIST%’
select * from DBA_HIST_SQLbind where sql_id=’4a2fkw1d4u01u’ and snap_id between 20637 and 20642
The following script can be used to determine which indexes would benefit from a rebuild, how much they will shrink by (assuming 8k block size and 10% pctfree), results ordered by severity.
——————————–
select a.*, round(index_leaf_estimate_if_rebuilt/current_leaf_blocks*100) percent, case when index_leaf_estimate_if_rebuilt/current_leaf_blocks < 0.5 then 'candidate for rebuild' end status
from
(
select table_name, index_name, current_leaf_blocks, round (100 / 90 * (ind_num_rows * (rowid_length + uniq_ind + 4) + sum((avg_col_len) * (tab_num_rows) ) ) / (8192 - 192) ) as index_leaf_estimate_if_rebuilt
from (
select tab.table_name, tab.num_rows tab_num_rows , decode(tab.partitioned,'YES',10,6) rowid_length , ind.index_name, ind.index_type, ind.num_rows ind_num_rows, ind.leaf_blocks as current_leaf_blocks,
decode(uniqueness,'UNIQUE',0,1) uniq_ind,ic.column_name as ind_column_name, tc.column_name , tc.avg_col_len
from dba_tables tab
join dba_indexes ind on ind.owner=tab.owner and ind.table_name=tab.table_name
join dba_ind_columns ic on ic.table_owner=tab.owner and ic.table_name=tab.table_name and ic.index_owner=tab.owner and ic.index_name=ind.index_name
join dba_tab_columns tc on tc.owner=tab.owner and tc.table_name=tab.table_name and tc.column_name=ic.column_name
where tab.owner='HILDA' and ind.leaf_blocks is not null and ind.leaf_blocks > 1000
) group by table_name, index_name, current_leaf_blocks, ind_num_rows, uniq_ind, rowid_length
) a where index_leaf_estimate_if_rebuilt/current_leaf_blocks < 0.5
order by index_leaf_estimate_if_rebuilt/current_leaf_blocks
——————————–
Here is a script originally written by my friend Jim Baker, which I frequently use to check CPU utilisation on a shared DB server by instance
####################
# topinstance.sh
####################
#!/bin/ksh
DBS=`ps -eo args | grep pmon | grep -v grep | cut -c 10-`
PROCS=/tmp/_$$_procs.txt
ps -eoargs,pcpu > $PROCS
for DB in `echo $DBS`
do
echo `date` $DB `cat $PROCS | grep $DB | grep -v grep | sed ’s/.* *//’ | \
awk ‘BEGIN { totalcpu = 0.0 } { totalcpu += $0 } END { print totalcpu }’
`
done
rm -f $PROCS
#####################
This can then be combined with
####################
# pollstat.sh
####################
#!/bin/ksh
touch /home/users/hilda/hostname_usage.log
while [ true ]
do
/home/users/hilda/topinstance.sh >> /home/users/hilda/hostname_usage.log
sleep 30
done
===============
to grab a snapshot every 30 seconds.
This has proven to be useful when combined with AWR reports, so we can quickly determine whether a performance issue is self inflicted within an application, or because some other database is hogging the resource.
Sometimes after a database refresh, we discover that the sequence number has fallen behind compared to actual max value in the table. This then results in PK constraint error when we try to insert a new row using the sequence.
To realign the sequence, you can obviously drop/recreate/alter if you have the right privilege, however here is a tip of how to do it if you do not have the schema owner privilege.
declare
v_table number;
v_seq number;
v_diff number;
begin
select max(seq_id) into v_table from TABLE1;
select last_number into v_seq from all_sequences where sequence_owner=’OWNER01′ and sequence_name=’TABLE1_SEQ’ ;
select v_seq - v_table into v_diff from dual;
if v_diff < 0
then
select (v_diff * -1) + 2 into v_diff from dual;
for i in 1 .. v_diff
loop
select TABLE1_SEQ.nextval into v_table from dual;
end loop;
end if;
end;
/
In a traditional table, Oracle stores previous image of the data (due to delete or update actions) in the UNDO tablespace, so this image could be rolled back in case of failure. However for tables with LOBs, oracle uses something different. The previous image of the data is stored inside the table itself, and this amount of space reserved as the virtual UNDO is controlled by the PCTVERSION parameter.
The default value is 10, meaning 10% of the LOB storage space is reserved for maintaining the old versions of the LOB. The older versions of the LOB data will not be overwritten until they consume 10% of the overall LOB storage space.
Use the storage clause
STORE AS (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0 cache STORAGE(BUFFER_POOL DEFAULT))
I am in the middle of handover process, prior to moving to a new project, and was asked to jot down some performance tuning fundamentals that I follow. It is harder than I thought, as how do you fit in 20 years of experience into a short handover document.
But I think the following points would perhaps be a good start
o Oracle is a very powerful RDBMS system. Oracle license is expensive for a good reason, i.e. it has a lot of functionalities that will benefit the applications if used correctly. Do not treat Oracle simply like a data storage
o Do not get obsessed with things you can not influence, like disk technology, database OS platform. 90% of performance improvement can be obtained by improving our own design/code.
o Ensure that your development environment is build as similarly as possible to UAT/Live.
o Be self sufficient in generating statistics for your tables. Do not rely on DBA overnight jobs. This way we will have full control on the best time and best method to generate the statistics.
o When choosing an index strategy, make sure you understand how the table is going to be used. Understand the different type of indexes and choose the right ones. Do not OVER indexed.
o Implement bind variables. Not only this will reduce parsing (memory usage), it will also allow performance investigation to be done more easily.
o When writing a query with complex joins, the single rule of thumb is, “read as little data as possible” - avoid Full Table Scan and as much as possible, filter first then join, not the other way around
o Favour bulk operations, rather than row by row, unless you are streaming intraday, or doing manual updates from a front end.
o Remember sqlldr for data loading
o Tuning is an ongoing exercise. Keep reviewing the application performance on a regular basis.
o Think outside the box, performance tuning is more than tuning individual SQL.
Just reminder for myself
WITH t AS (
SELECT ‘AAA,a,b,c,d,e,f,g,h,i,j,k,l’ or_str from dual
)
SELECT SUBSTR(
or_str,
INSTR(’,’ || or_str,’,',1,LEVEL),
INSTR(or_str || ‘,’,',’,1,LEVEL) - INSTR(’,’ || or_str,’,',1,LEVEL)
) SubString
FROM t
CONNECT BY LEVEL <= LENGTH(or_str)-LENGTH(REPLACE(or_str,’,')) + 1
AND PRIOR or_str = or_str
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
/
Let’s say that we need to reconcile data between 2 tables, where we literally need to compare values of each columns and highlight differences between two.
I found the use of MINUS to be very beneficial in letting us do a very quick, yet meaningful reconciliation.
Say you need to reconcile content of TAB1 against content of TAB2.
The outline of the query will look like this
- find items that are in TAB1, but not in TAB2
combine that with
- find items that arein TAB2, but no in TAB1
So your SQL might look like this
(select ‘missing from TAB2′ as status, col1,col2,col3,col4,col5,col6 from TAB1
minus
select ‘missing from TAB2′ as status col1,col2,col3,col4,col5,col6 from TAB2
)
union all
(
select ‘missing from TAB1′ as status, col1,col2,col3,col4,col5,col6 from TAB2
minus
select ‘missing from TAB1′ as status, col1,col2,col3,col4,col5,col6 from TAB2
from TAB1)
Easy. I have recently tested to reconcile a 500k row table against another, where each table has 15 columns (even spread between number and varchar2 fields), the reconciliation took just around 10 seconds.
Often I need to be able to quickly make a judgment on how my databases perform historically. A bog standard AWR report does not give me the information that I need straight away, and sometimes I do feel overloaded with information that I do not need until I need to drill down to a specific problem.
So here are a few queries which I hope you would find useful too
– historical DB usage day by day by instance
with a
as (select trunc(begin_interval_time) cob, min(snap_id) start_snap_id, max(snap_id) end_snap_id
from dba_hist_snapshot group by trunc(begin_interval_time))
select (select distinct name from gv$database) as db, (select distinct name from gv$database)||case when s.instance_number=1 then ‘A’ else ‘B’ end as instance,
s.cob, to_char(s.cob,’Day’) as day_of_week, s.instance_number, round((end_value-start_value)/1000000/3600) db_time_hours from (
select a.cob, s.instance_number, s.value start_value from dba_hist_sys_time_model s, a where s.snap_id=a.start_snap_id and s.stat_name=’DB time’) s,
(select a.cob, e.instance_number, e.value end_value from dba_hist_sys_time_model e, a where e.snap_id=a.end_snap_id and e.stat_name=’DB time’) e
where s.cob=e.cob and s.instance_number=e.instance_number order by s.cob desc ,2
– historical DB usage day by day cummulative both nodes
with a
as (select trunc(begin_interval_time) cob, min(snap_id) start_snap_id, max(snap_id) end_snap_id
from dba_hist_snapshot group by trunc(begin_interval_time))
select (select distinct name from gv$database) as db, s.cob, to_char(s.cob,’Day’) as day_of_week, round((end_value-start_value)/1000000/3600) db_time_hours from (
select a.cob, sum(s.value) start_value from dba_hist_sys_time_model s, a where s.snap_id=a.start_snap_id and s.stat_name=’DB time’ group by a.cob) s,
(select a.cob,sum(e.value) end_value from dba_hist_sys_time_model e, a where e.snap_id=a.end_snap_id and e.stat_name=’DB time’ group by a.cob) e
where s.cob=e.cob order by s.cob desc ,2
– historical Physical IO usage day by day by instance
with a
as (select trunc(begin_interval_time) cob, min(snap_id) start_snap_id, max(snap_id) end_snap_id
from dba_hist_snapshot group by trunc(begin_interval_time))
select (select distinct name from gv$database) as db, (select distinct name from gv$database)||case when s.instance_number=1 then ‘A’ else ‘B’ end as instance,
s.cob, to_char(s.cob,’Day’) as day_of_week, s.instance_number, round((end_value-start_value)*8192/1024/1024/1024,2) physical_read_write_gb from (
select a.cob, s.instance_number, sum(s.value) start_value from DBA_HIST_SERVICE_STAT s, a
where s.snap_id=a.start_snap_id and s.stat_name like ‘physical%’ group by a.cob, s.instance_number) s,
(select a.cob, e.instance_number, sum(e.value) end_value from DBA_HIST_SERVICE_STAT e, a
where e.snap_id=a.end_snap_id and e.stat_name like ‘physical%’ group by a.cob, e.instance_number) e
where s.cob=e.cob and s.instance_number=e.instance_number order by s.cob desc ,2
– historical Physical IO usage day by day cummulative both nodes
with a
as (select trunc(begin_interval_time) cob, min(snap_id) start_snap_id, max(snap_id) end_snap_id
from dba_hist_snapshot group by trunc(begin_interval_time))
select (select distinct name from gv$database) as db, s.cob, to_char(s.cob,’Day’) as day_of_week, round((end_value-start_value)*8192/1024/1024/1024,2) physical_read_write_gb from (
select a.cob, sum(s.value) start_value from DBA_HIST_SERVICE_STAT s, a
where s.snap_id=a.start_snap_id and s.stat_name like ‘physical%’ group by a.cob) s,
(select a.cob, sum(e.value) end_value from DBA_HIST_SERVICE_STAT e, a
where e.snap_id=a.end_snap_id and e.stat_name like ‘physical%’ group by a.cob) e
where s.cob=e.cob order by s.cob desc ,2
– historical DB usage day by day by service_name cummulative both nodes
with a
as (select trunc(begin_interval_time) cob, min(snap_id) start_snap_id, max(snap_id) end_snap_id
from dba_hist_snapshot group by trunc(begin_interval_time))
select (select distinct name from gv$database) as db, s.cob, to_char(s.cob,’Day’) as day_of_week, s.service_name, round((end_value-start_value)/1000000/3600,2) db_time_hours from (
select a.cob, s.service_name, sum(s.value) start_value from DBA_HIST_SERVICE_STAT s, a
where s.snap_id=a.start_snap_id and s.stat_name = ‘DB time’ group by a.cob, s.service_name) s,
(select a.cob, e.service_name, sum(e.value) end_value from DBA_HIST_SERVICE_STAT e, a
where e.snap_id=a.end_snap_id and e.stat_name = ‘DB time’ group by a.cob, e.service_name) e
where s.cob=e.cob and s.service_name=e.service_name order by s.cob desc ,db_time_hours desc
– Drill down DB time hour by hour by instance
select * from (
select (select distinct name from gv$database) as db, (select distinct name from gv$database)||case when instance_number=1 then ‘A’ else ‘B’ end as instance, instance_number,
begin_interval_time, end_interval_time, stat_name, db_time_in_hr_by_period, rank() over (partition by instance_number, trunc(begin_interval_time) order by db_time_in_hr_by_period desc) heaviest_period_first
from (
select begin_interval_time, end_interval_time, instance_number, stat_name, round((value-prev_value)/1000000/3600,2) db_time_in_hr_by_period from (
select b.begin_interval_time, b.end_interval_time, a.instance_number, a.stat_name, a.value, lag(a.value,1,0) over (order by a.snap_id) as prev_value
from dba_hist_sys_time_model a,dba_hist_snapshot b
where a.snap_id=b.snap_id and a.stat_name=’DB time’
and a.instance_number=b.instance_number
and a.instance_number=1
order by a.snap_id) where prev_value <> 0 )
union all
select (select distinct name from gv$database) as db, (select distinct name from gv$database)||case when instance_number=1 then ‘A’ else ‘B’ end as instance, instance_number,
begin_interval_time, end_interval_time, stat_name, db_time_in_hr_by_period, rank() over (partition by instance_number, trunc(begin_interval_time) order by db_time_in_hr_by_period desc) heaviest_period_first
from (
select begin_interval_time, end_interval_time, instance_number, stat_name, round((value-prev_value)/1000000/3600,2) db_time_in_hr_by_period from (
select b.begin_interval_time, b.end_interval_time, a.instance_number, a.stat_name, a.value, lag(a.value,1,0) over (order by a.snap_id) as prev_value
from dba_hist_sys_time_model a,dba_hist_snapshot b
where a.snap_id=b.snap_id and a.stat_name=’DB time’
and a.instance_number=b.instance_number
and a.instance_number=2
order by a.snap_id) where prev_value <> 0 )
)
order by instance_number desc, trunc(begin_interval_time) desc, heaviest_period_first