Ucertify Challenge

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.

Oracle Bug Status code on metalink

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.

How to finding Maximum SESSION_CACHED_CURSORS in use

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.

How Adjust Open_cursors on Oracle Database

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.

Killing an Oracle process without OS access

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;

Killing an Oracle process from inside Oracle

Logon Trigger

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;
/

Adjusting Oracle memory

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,name FROM V$DB_CACHE_ADVICE order by name,1;

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.