Borndba

We had  an outage, where pga eating up all the memory crashing the vcs. We temporarily solved the issue by moving to shared server and reducing the pga size.

Came across some of the suggestion to restrict pga,

1. Private_sga in MTS

2. 10261 event

10261, 00000, "Limit the size of the PGA heap"
// *Cause:    the limit is one kilobyte times the level of the event. If the
//            pga grows bigger than this signal an internal error.

to set a session to 100mb

useful note is found in

http://dioncho.wordpress.com/2010/06/14/rapid-pga-size-increase/

for setting multiple events in spfile, you can use below command
ALTER SYSTEM SET event='10261 trace name context forever, level 15000000','600 trace name heapdump, level 0x20000001' COMMENT='pga issue' SCOPE=SPFILE;

for resetting use,





2:


ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;




Note: Have tried setting 10261 event in shared server db, but process didn't crash once reached the limit.

3. DBMS_SESSION.FREE_UNUSED_USER_MEMORY

convert this post to pdf.

We got an requirement to schedule a script after BD6+1. Means next day to BD6, which can be a holiday. But incase between BD1 to BD6 federal holiday falls, it needs to be moved to next day.

Create schedule for all the holidays,

begin
dbms_scheduler.create_schedule( schedule_name => 'NYD_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1231;BYDAY=FRI',
comments => 'Friday alternative for New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'NYD_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=0102;BYDAY=MON',
comments => 'Monday alternative for New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'NewYearsDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=0101;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=NYD_FRI,NYD_MON', comments => 'New Year''s Day');
dbms_scheduler.create_schedule( schedule_name => 'MartinLutherKing',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=JAN;BYDAY=3MON',
comments => 'Martin Luther King Day');
dbms_scheduler.create_schedule( schedule_name => 'PresidentsDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=FEB;BYDAY=3MON',
comments => 'President''s Day');
dbms_scheduler.create_schedule( schedule_name => 'MemorialDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=MAY;BYDAY=-1MON',
comments => 'Memorial Day');
dbms_scheduler.create_schedule( schedule_name => 'ID_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=0703;BYDAY=FRI',
comments => 'Friday alternative for Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'ID_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=0705;BYDAY=MON',
comments => 'Monday alternative for Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'IndependenceDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=0704;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=ID_FRI,ID_MON', comments => 'Independence Day');
dbms_scheduler.create_schedule( schedule_name => 'LaborDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=SEP;BYDAY=1MON',
comments => 'Labor Day');
dbms_scheduler.create_schedule( schedule_name => 'ColumbusDay',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=OCT;BYDAY=2MON',
comments => 'Columbus Day');
dbms_scheduler.create_schedule( schedule_name => 'VD_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1110;BYDAY=FRI',
comments => 'Friday alternative for Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'VD_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=1112;BYDAY=MON',
comments => 'Monday alternative for Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'VeteransDay',
repeat_interval => 'FREQ=YEARLY;BYDATE=1111;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=VD_FRI,VD_MON', comments => 'Veterans Day');
dbms_scheduler.create_schedule( schedule_name => 'Thanksgiving',
repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=4THU',
comments => 'Thanksgiving');
dbms_scheduler.create_schedule( schedule_name => 'XMS_FRI',
repeat_interval => 'FREQ=YEARLY;BYDATE=1224;BYDAY=FRI',
comments => 'Friday alternative for Christmas');
dbms_scheduler.create_schedule( schedule_name => 'XMS_MON',
repeat_interval => 'FREQ=YEARLY;BYDATE=1226;BYDAY=MON',
comments => 'Monday alternative for Christmas');
dbms_scheduler.create_schedule( schedule_name => 'Christmas',
repeat_interval => 'FREQ=YEARLY;BYDATE=1225;BYDAY=MON,TUE,WED,THU,FRI;'
|| 'INCLUDE=XMS_FRI,XMS_MON', comments => 'Christmas');
end;
/

– create a schedule which clubs all the schedule created above.

begin
dbms_scheduler.create_schedule( schedule_name => 'FederalHolidays',
repeat_interval => 'NewYearsDay,MartinLutherKing,PresidentsDay,' ||
'MemorialDay,IndependenceDay,LaborDay,ColumbusDay,VeteransDay,' ||
'Thanksgiving,Christmas', comments => 'Federal Holidays');
end;
/

– Job which needs to be scheduled

create or replace procedure stats_run_after_BD6 as
begin

---- some job;
end;
/

— Create a job, note the “EXCLUDE” and “BYSETPOS” option

BEGIN
dbms_scheduler.create_job(
job_name => 'Stats_Run_After_BD6+1'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin stats_run_after_BD6; end; '
,repeat_interval => 'FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=7'
,enabled => TRUE
,comments => 'Run Stats Gather next day of BD6');
END;
/

What are all the dates this job is going to execute. We can use the below procedure.

create or replace procedure print_dates
is
nr_of_dates  pls_integer :=30;
start_date timestamp with time zone := sysdate;
date_after timestamp with time zone := start_date - interval '1' second;
next_execution_date timestamp with time zone;
correct_execution_date timestamp with time zone;

begin
for i in 1 .. nr_of_dates
loop
dbms_scheduler.evaluate_calendar_string
('FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; byhour=14;byminute=30;bysecond=0; EXCLUDE=FederalHolidays; BYSETPOS=6', start_date, date_after, next_execution_date);

correct_execution_date := next_execution_date + interval '1' day;
DBMS_OUTPUT.PUT_LINE(correct_execution_date);

date_after := next_execution_date;
end loop;
end;
/

sometime the BD6+ 1 date will be weekend. If you want to execute in the weekend you can add “interval ’1′ day”,

correct_execution_date := next_execution_date + interval ’1′ day;

else remove that.

convert this post to pdf.

When one of the db got upgraded from 10202 to 10205, we found out one of the query is very slow. Didn’t taken the export of stats before upgrade. This is one way we forced a profile to this query.

declare
pln_sql_id varchar2(20) :='fathgmks3vb1g';
pln_plan_hash_value number := 3396554601;
orig_sql_id varchar2(20) := 'fathgmks3vb1g';
new_prof_name varchar2(20) := 'SQL_PROFILE_1';
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin

select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/*/outline_data/hint'
passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan
where sql_id = pln_sql_id and plan_hash_value = pln_plan_hash_value
and other_xml is not null)) d;

select sql_text into cl_sql_text
from dba_hist_sqltext where sql_id = orig_sql_id;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
name => new_prof_name,
force_match => true);

end;
/

The below script can be used to see what’s the access path for a hash_value. This can be seen when you create outln and query ol_hint$

select
substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
hash_value = 2378699969
and child_number = 0
and other_xml is not null
)
) d;

You need to drop any sql_profile created before forcing a plan.

exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_014a4a1e346a8004', ignore => TRUE);

convert this post to pdf.

Steps for export and importing the db stats. This helps when you change the stats gathering approach.

exec dbms_stats.create_stat_table(ownname => ‘SYS’, stattab => ‘db_stat’, tblspace => ‘SYSTEM’)

exec dbms_stats.export_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

exec dbms_stats.import_database_stats(stattab => ‘db_stat’,statown => ‘SYS’)

convert this post to pdf.