Category Archives: Scripts

This page contains all scripts of dba-scripts.com, either performance tuning, diagnosis scripts or administration scripts.

Database maintenance tasks

Administration

oracle maintenance

The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

  • Optimizer statistics (OPTIMIZER_STATS)
  • Segment advisor (SEGMENT_ADVISOR)
  • SQL Tuning (SQL_TUNE_ADVISOR)
  • Health Monitor (HEALTH_MONITOR)

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

Consumer Group/subplan Level 1 Maximum Utilization Limit
ORA$AUTOTASK 5% 90
OTHER_GROUPS 20% –
SYS_GROUP 75% –

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          WINDOW_NEXT_TIME                                   WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- -------------------------------------------------- ----- -------- -------- -------- -------- --------
SUNDAY_WINDOW        27-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW      26-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
FRIDAY_WINDOW        25-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
THURSDAY_WINDOW      24-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW     23-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW       29-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
MONDAY_WINDOW        28-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

Continue reading Database maintenance tasks →

Find missing or stale statistics

Diagnostic and Tuning, Troubleshooting

Purpose

Statistics are primordial for the optimizer to choose the best execution plan possible. Sometimes you encounter a sub-optimal plan and need to find out if the tables involved in the statement are up to date. This query will help you find out which tables have been modified significantly since the last statistics gathering. For this query to work you will need the MONITORING to be activated on your tables, this is automatically the case since 11g.

Continue reading Find missing or stale statistics →

Analyze database activity using v$log_history

Troubleshooting

The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database.

v$log_history queries

You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the precision you want.

select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY 
from (
		select to_char(trunc(first_time), 'Day') DAY, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
		from v$log_history 
		group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
		order by 2
	 )
group by day;

This one gives the following output :

LOG_SWITCHES DAY
------------ ---------
207 Sunday
212 Monday
218 Friday
192 Thursday
207 Wednesday
216 Tuesday
209 Saturday

You can find out which day of the week is the most active. You can also have a day to day analysis for the last month :

select round(avg(LOG_SWITCHES)) LOG_SWITCHES, DAY 
from (
		select TRUNC(FIRST_TIME, 'DDD') DAY, count(*) LOG_SWITCHES
		from v$log_history 
		where first_time between sysdate -30 and sysdate
		group by TRUNC(FIRST_TIME, 'DDD'), to_char(trunc(first_time), 'Day')
		order by 1
	 )
group by day
order by 2;

here is the output :

LOG_SWITCHES DAY
------------ ---------
91 10-AUG-15
225 11-AUG-15
233 12-AUG-15
224 13-AUG-15
221 14-AUG-15
218 15-AUG-15
217 16-AUG-15
225 17-AUG-15
218 18-AUG-15
215 19-AUG-15
212 20-AUG-15
203 21-AUG-15
198 22-AUG-15
198 23-AUG-15
200 24-AUG-15
201 25-AUG-15
202 26-AUG-15
202 27-AUG-15
190 28-AUG-15
150 29-AUG-15
151 30-AUG-15
153 31-AUG-15
182 01-SEP-15
202 02-SEP-15
201 03-SEP-15
203 04-SEP-15
203 05-SEP-15
199 06-SEP-15
202 07-SEP-15
205 08-SEP-15
124 09-SEP-15

 

You can also drilldown to hours in the day :

select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
		select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
		from v$log_history 
		group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
		order by 1
	 )
group by Hour
order by Hour;

Here the output :

HO LOG_SWITCHES
-- ------------
00 8
01 9
02 8
03 8
04 25
05 7
06 8
07 8
08 8
09 8
10 8
11 8
12 8
13 8
14 8
15 8
16 8
17 8
18 8
19 7
20 8
21 7
22 8
23 15

Be creative 🙂