Skip to content

Commit d254879

Browse files
author
Dan McGhan
committed
2 parents 1951608 + ff55cd1 commit d254879

24 files changed

+501
-0
lines changed

‎optimizer/faster_stats/DEFAULT/modify_plan.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
--
2+
-- This modifies the default maintenance resource
3+
-- management plan as described in the blog
4+
-- post.
5+
--
6+
connect / as sysdba
7+
8+
exec dbms_resource_manager.clear_pending_area
9+
10+
begin
11+
dbms_resource_manager.create_pending_area();
12+
dbms_resource_manager.update_plan_directive(
13+
plan => 'DEFAULT_MAINTENANCE_PLAN',
14+
group_or_subplan => 'ORA$AUTOTASK',
15+
new_mgmt_p1 => 5,
16+
new_max_utilization_limit => 90,
17+
new_parallel_degree_limit_p1 => 4);
18+
dbms_resource_manager.update_plan_directive(
19+
plan => 'DEFAULT_MAINTENANCE_PLAN',
20+
group_or_subplan => 'OTHER_GROUPS',
21+
new_mgmt_p1 => 20);
22+
dbms_resource_manager.update_plan_directive(
23+
plan => 'DEFAULT_MAINTENANCE_PLAN',
24+
group_or_subplan => 'SYS_GROUP',
25+
new_mgmt_p1 => 75);
26+
dbms_resource_manager.validate_pending_area();
27+
dbms_resource_manager.submit_pending_area();
28+
end;
29+
/
30+

‎optimizer/faster_stats/DEFAULT/run_gather.sql

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
--
2+
-- This starts a batch window to execute auto stats
3+
-- immediately. In this case we are just using
4+
-- the default maintenance plan.
5+
--
6+
begin
7+
begin
8+
dbms_scheduler.drop_window('TEST_WINDOW',true);
9+
exception when others then
10+
if (sqlcode != -27476) then
11+
raise;
12+
end if;
13+
end;
14+
15+
dbms_scheduler.create_window(
16+
window_name => 'TEST_WINDOW',
17+
duration => numtodsinterval(20, 'minute'),
18+
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
19+
repeat_interval => 'FREQ=DAILY;INTERVAL=1');
20+
dbms_scheduler.add_group_member(
21+
group_name => 'MAINTENANCE_WINDOW_GROUP',
22+
member => 'TEST_WINDOW');
23+
dbms_scheduler.enable(name => 'TEST_WINDOW');
24+
dbms_auto_task_admin.enable('auto optimizer stats collection',NULL,
25+
'TEST_WINDOW');
26+
--dbms_auto_task_admin.enable('sql tuning advisor',NULL,
27+
-- 'TEST_WINDOW');
28+
--dbms_auto_task_admin.enable('auto space advisor',NULL,
29+
-- 'TEST_WINDOW');
30+
end;
31+
/
32+
33+

‎optimizer/faster_stats/README.md

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
2+
3+
First, create a user called STEST by adapting the the user.sql script to use an appropriate tablespace (which needs to have about 1.5GB free).
4+
5+
Two 'connect' scripts are available:
6+
cadm.sql <-- Connects to SYSDBA account
7+
cusr.sql <-- Connects to the STEST user account
8+
You can edit these to suit you system, particularly if you are using a multitenant environment.
9+
10+
Create tables:
11+
@tabs.sql
12+
13+
Gather database stats to start with a 'clean slate':
14+
@gather.sql
15+
16+
Make RM plan:
17+
@make_plan.sql
18+
Note that SCOPE=MEMORY so the plan change is temporary.
19+
If necessary, you can drop the plan with drop_plan.sql
20+
21+
If you want to use the DEFAULT_MAINTENANCE_PLAN instead,
22+
run modify_plan.sql in the 'DEFAULT' directory.
23+
24+
To use AUTO_DEGREE: @auto.sql
25+
To use serial execution: @noauto.sql
26+
27+
To user CONCURRENT: @conc.sql
28+
To disable CONCURRENT: @noconc.sql
29+
30+
Prepare the test by dripping stats on the test tables:
31+
@drop_stats.sql
32+
33+
Initiate stats gathering by creating a 20min batch window:
34+
@run_gather.sql
35+
If you want to use DEFAULT_MAINTENANCE_PLAN, then run 'run_gather.sql' in
36+
the 'DEFAULT' subdirectory instead.
37+
38+
I have put the monitoring queries I use in util.sql
39+
Take a look at scheduler information: get_status.sql
40+
Take a look at stale objects: stale.sql
41+
42+
If you want to stop the test: drop_window.sql [stop.sql is an alternative]
43+
44+
========================
45+
46+
You can run a manual stats gathering with 'manual.sql'

‎optimizer/faster_stats/auto.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
@cusr
2+
set serveroutput on
3+
4+
declare
5+
cursor c1 is
6+
select table_name
7+
from user_tables
8+
where table_name like 'BIGT%';
9+
begin
10+
for r in c1
11+
loop
12+
dbms_stats.set_table_prefs(user,r.table_name,'degree',DBMS_STATS.AUTO_DEGREE);
13+
dbms_output.put_line('Auto: '||r.table_name);
14+
end loop;
15+
end;
16+
/

‎optimizer/faster_stats/cadm.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
connect / as sysdba

‎optimizer/faster_stats/conc.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
--
2+
-- Enable concurrent collection for auto optimizer stats collection
3+
--
4+
@cadm
5+
6+
exec dbms_stats.set_global_prefs('CONCURRENT','AUTOMATIC')
7+
8+
select dbms_stats.get_prefs('CONCURRENT') from dual;
9+

‎optimizer/faster_stats/concm.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
--
2+
-- Enable concurrent collection for auto optimizer stats collection
3+
--
4+
@cadm
5+
6+
exec dbms_stats.set_global_prefs('CONCURRENT','MANUAL')
7+
8+
select dbms_stats.get_prefs('CONCURRENT') from dual;
9+

‎optimizer/faster_stats/cusr.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
connect stest/stest

‎optimizer/faster_stats/drop_plan.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
--
2+
-- Drop the test RM plan
3+
--
4+
@cadm
5+
6+
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' scope = memory;
7+
8+
declare
9+
plan_exists exception;
10+
pragma exception_init (plan_exists,-29358);
11+
begin
12+
dbms_resource_manager.create_pending_area();
13+
dbms_resource_manager.delete_plan('DB_RM_PLAN');
14+
dbms_resource_manager.submit_pending_area();
15+
exception
16+
when plan_exists then null;
17+
end;
18+
/
19+

‎optimizer/faster_stats/drop_stats.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
@cusr
2+
set serveroutput on
3+
4+
declare
5+
cursor c1 is
6+
select table_name
7+
from user_tables
8+
where table_name like 'BIGT%';
9+
begin
10+
for r in c1
11+
loop
12+
dbms_stats.delete_table_stats(user,r.table_name);
13+
dbms_output.put_line('Drop Stats: '||r.table_name);
14+
end loop;
15+
end;
16+
/
17+
18+
@stats

0 commit comments

Comments
 (0)