select qtd,count(*) from (
select trunc(FIRST_TIME),trunc(count(*)/10)*10 qtd
from v$log_history group by trunc(FIRST_TIME) order by 2
)group by qtd order by 1;
Usually is good pratice make 2 log switchs per hour
select qtd,count(*) from (
select trunc(FIRST_TIME),trunc(count(*)/10)*10 qtd
from v$log_history group by trunc(FIRST_TIME) order by 2
)group by qtd order by 1;
Usually is good pratice make 2 log switchs per hour
Sometimes you want to log your own message on alert
just use package ksdwrt
e.g.: dbms_system.ksdwrt(2,’ORA-00600: URGENT – starting 4031 PROJECTS BY Helio Dias’);
I wrote the message started with 00600 to allow our monitoring tool “see” the error.
First parameter (2) write message on alert, (1) write on trace files
Two months ago i´d received a very brave propose: “Test our product and say whatever you want.”
I´d thought , ohhh it must be good , or they must be crazy, cause if they make me lost my time testing a bad product i´ll write terrible things.
And i accepted try the Oracle z31 (administration one for OCA/OCP 9) cause to me this one of the best of Oracle, so a good or bad product will show it face on it.
And i started the trial of Ucertify , and ask to my brother try with me.
And the result is: Ucertify is without any doubt an amazing product and i recommend it to anyone that will try an Oracle certification.
10 – Description Phase:
Development is requesting more information.
11 – Code Bug (Response/Resolution):
Bug is being worked by Development.
16 – Support bug screening:
Bug is being reviewed by our Bug Diagnostics group.
30 – Additional Information Requested:
Bug is being worked by Support and/or more information was requested by Development.
37 – To Filer for Review/Merge Required:
Bug has been fixed but the patch will be merged into the next patchset.
80 – Development to Q/A:
Bug is being regression tested for future release.
96 – Closed, Duplicate Bug:
Bug is closed as a duplicate bug.
select max(value)
from v$sesstat natural join v$statname
where name = ’session cursor cache count’ ;
Select amount,SESSION_CACHED_CURSORS*30+30 Cached_Cursors_Rounded from
(select trunc(value/30) SESSION_CACHED_CURSORS,count(*) Amount
from V$sesstat natural join v$statname
where name = ’session cursor cache count’
group by trunc(value/30) order by 1);
AMOUNT CACHED_CURSORS_ROUNDED
———- ———————-
223 30
32 60
15 90
6 120
2 150
3 180
5 210
7 rows selected.
As we can see 210 will allow every session to cache what they need.
First you need to know how many cursors are you session using at maximum
select max(value) from V$sesstat natural join v$statname
where name = ‘opened cursors current’;
Then you can give some safe e.g. 30% and do an
alter system set open_cursors=&New_value_plus_20pct;
Select amount,Opened_cursos_rounded*10+10 from
(select trunc(value/10) Opened_cursos_rounded,count(*) Amount from V$sesstat natural join v$statname
where name = ‘opened cursors current’
group by trunc(value/10) order by 1);
AMOUNT OPENED_CURSOS_ROUNDED*10
———- ————————
35 0
59 10
10 20
3 30
2 40
1 60
1 70
1 80
1 130
1 180
1 190
1 200
1 220
2 230
14 rows selected.
On my example you can see that most of sessions are using just 10 cursors, but my max are at 230.
So put a limit on 300 is sounds good.
Sometimes alter system kill session doesn´t work , and you don´t have access to SO.
So use Oradebug instead, but remember to change to correct session:
oradebug event immediate crash;
Sometimes you need to something happen just after logon, e.g. an alter session , or an audit DML.
So you may use Logon trigger to do this:
GRANT ALTER SESSION TO SYSTEM;
GRANT CREATE SESSION TO SYSTEM;
create or replace trigger set_trace after logon on database
begin
if user not in (’SYS’,'SYSTEM’) then
if user=’GERAL’ THEN
execute immediate ‘alter session set TRACEFILE_IDENTIFIER= ”CLIENT_TRACE10046”’;
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set events ”10046 trace name context forever, level 12”’;
end if;
end if;
exception
when others then
null;
end;
/
GRANT ALTER SESSION TO SYSTEM;
GRANT CREATE SESSION TO SYSTEM;
create or replace trigger GC_set_session after logon on database
begin
if user not in (’SYS’,'SYSTEM’) then
execute immediate ‘alter session set session_cached_cursors=250′;
end if;
exception
when others then
null;
end;
/
To do a fine tuning on memory area, you should first look the shared pool, then go to PGA and at the end adjust the db_cache
SELECT SHARED_POOL_SIZE_FOR_ESTIMATE MB_ESTIMATE,SHARED_POOL_SIZE_FACTOR
FACTOR_PERCENT,
ESTD_LC_TIME_SAVED_FACTOR TIME_PERCENT FROM V$SHARED_POOL_ADVICE;
SELECT PGA_TARGET_FOR_ESTIMATE MB_ESTIMADO,PGA_TARGET_FACTOR
FATOR_PERCENT,
ESTD_PGA_CACHE_HIT_PERCENTAGE ACERTO_PERCENT FROM V$PGA_TARGET_ADVICE;
SELECT SIZE_FOR_ESTIMATE MB_ESTIMADO, SIZE_FACTOR
FATOR_PERCENT,
ESTD_PHYSICAL_READ_FACTOR TEMPO_PERCENT FROM V$DB_CACHE_ADVICE;
Below are good notes from metalink
Metalink Note: 223730.1 Automatic PGA Memory Management in 9i and 10g
Metalink Note: 62172.1 Understanding and Tuning Buffer Cache and DBWR
Metalink Note: 255409.1 Size Shared Pool using V$shared_pool_advice.