18c Installation On Premises

Documentation by @oraclebase  (Tim Hall) on installing 18c  On Premises on OEL :



Oracle Database 18c Installation On Oracle Linux 6 (OL6) and 7 (OL7)

performance with Result Cache in Oracle 11g

Oracle introduced the memory structure called Result Cache to store the results of SQLs & PL/SQL.
The database serves the results for the executed SQL queries and PL/SQL functions from the result cache instead of re-executing the actual query,
quite obvious this feature will increase the performance.
Memory allocated(can be controlled using RESULT_CACHE_MAX_SIZE parameter) for the result cache is taken from the shared pool.
By now one might come to the conclusion that result cache will improve the performance of the repeatly used PL/SQL & SQL.
RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache.
If the value of this parameter is 0, then the result cache feature is disabled.

SQL> select name,value from v$parameter where name like 'result_cache%';

result_cache_mode                                                                MANUAL
result_cache_max_size                                                            0
result_cache_max_result                                                          5
result_cache_remote_expiration                                                   0

RESULT_CACHE_MODE:
   RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan. If set to FORCE,
   all the queries are cached if they are qualifed and fit in cache.
   The default is MANUAL, which indicates that only queries with the hint will be cached.
 
RESULT_CACHE_MAX_SIZE:
 
   Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size.
   Also query result.
 
RESULT_CACHE_MAX_RESULT:

       Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy.
       (Just to prevent the big result set from throwing out all other small resultset).
     
RESULT_CACHE_REMOTE_EXPIRATION:

  Specifies the number of minutes that a cached result that accesses a remote object will remain valid.
  Setting this parameter to 0 implies that results using remote objects should not be cached.
  Setting this parameter to a nonzero value may produce stale answers
  (for example, if the remote table used by a result is modified at the remote database).
  I personally think it should be set to positive value if remote database is read only database.
 
SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  Result Cache: State Objs         2896
shared pool  Result Cache: Memory Mgr          200
shared pool  Result Cache: Cache Mgr          5552
shared pool  Result Cache: Bloom Fltr         2048

Like almost all memory structure in oracle instance,
Result cache is also protected by latches.
Latches, being serialization devices, are scalability inhibitors. Don’t just to come to conclusion that Latches are evil


SQL> select * from v$latchname where name like 'Result Cache%';

    LATCH# NAME                                                                   HASH
---------- ---------------------------------------------------------------- ----------
       391 Result Cache: RC Latch                                           1054203712
       392 Result Cache: SO Latch                                            986859868
       393 Result Cache: MB Latch                                            995186388


SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 5248K bytes (5248 blocks)
Maximum Result Size = 262K bytes (262 blocks)
[Memory]
Total Memory = 2400 bytes [0.001% of the Shared Pool]
... Fixed Memory = 2400 bytes [0.001% of the Shared Pool]
....... Cache Mgr  = 152 bytes
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... State Objs =  bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

DBMS_RESULT_CACHE package has lot more functions/procedures and if you are looking for more, login into 11g using SQL* Plus and execute “desc DBMS_RESULT_CACHE” and the documentation for the same can be found here.

Background Process

SQL> select description from V$BGPROCESS where name like 'RC%';

    LATCH# NAME                                                                   HASH
---------- ---------------------------------------------------------------- ----------
       391 Result Cache: RC Latch                                           1054203712
       392 Result Cache: SO Latch                                            986859868
       393 Result Cache: MB Latch                                            995186388

Virtual Columns in Oracle 11g

create table marks
 (
    physics      number,
    chemistry       number,
    total     number,
    aggre  varchar2(6)
    generated always as
    (
       case
          when total <= 50 then 'LOW'
          when total > 60 and total <= 80 then 'MEDIUM'
          when total > 81 and total <= 99 then 'HIGH'
          else 'ULTRA'
       end
     ) virtual
 );



 insert into marks (physics, chemistry, total) values (50,60,40);


 insert into marks (physics, chemistry, total) values (60,70,60);

 insert into marks (physics, chemistry, total) values (80,95,99);

commit;




12:45:32 SQL> select * from marks;

   PHYSICS  CHEMISTRY      TOTAL AGGRE
---------- ---------- ---------- ------
        50         60         40 LOW
        60         70         60 ULTRA
        80         95         99 HIGH


create index IN_TOTAL on marks (aggre);


select index_type from user_indexes where index_name = 'IN_TOTAL';


12:47:52 SQL> select index_type from user_indexes where index_name = 'IN_TOTAL';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL


12:48:25 SQL> select column_expression from user_ind_expressions where index_name = 'IN_TOTAL';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "TOTAL"<=50 THEN 'LOW' WHEN ("TOTAL">60 AND "TOTAL"<=80) THEN 'MEDIUM' WHEN ("TOTAL">81 AND "TOTAL"<=99) THEN 'HIGH' ELSE 'ULTRA' END