Indexes

 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html

• Normal indexes. (By default, Oracle Database creates B-tree indexes.), used for high cardinality column.

• Bitmap indexes,   are small in size , used for low cardinality column., used in OLAP system where modification is rare as it locks the entire table. 

       refer https://tipsfororacle.blogspot.com/2016/09/oracle-indexes.html

• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.

• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.

• Domain indexes, which are instances of an application-specific index of type indextype.
  Refer https://tipsfororacle.blogspot.com/2017/02/index-usage-with-like-operator-and.html

Configure keep cache in Oracle

PIN Packages in the Shared Memory Pool- 

 DBMS_SHARED_POOL.KEEP ( name Varchar2, flag char default 'P');

Configure Keep Cache in Oracle-

Usually small objects should be kept in Keep buffer Cache .  DB_KEEP_CACHE_SIZE initialization parameter is used to create keep buffer pool. If  DB_KEEP_CACHE_SIZE  is not used then no Keep Buffer is created.

Step 1.  Check the current keep cache size

SQL> show parameter keep;

Step 2.  Check the table size need to keep in cache.

SQL> select bytes/1024/1024 from dba_segments where segment_name='&Tablename';

Step 3. Configure Keep cache

 SQL> alter system set db_keep_cache_size = 7G scope=both;

Step 4. Move the table into cache.

SQL> Alter table table_owner.table_name storage(buffer_pool Keep);

Step 5. Check the table is part of the keep pool using below query

SQL> select  segment_name, buffer_pool from dba_segments where segment_name=&Table_name;

SQL> select segment_name, segment_type from dba_segments where buffer_pool='KEEP' and segment_type='TABLES' ;

After moving objects to keep cache you can observe the performance and check the "segment ordered by logical reads" in segment statistics of AWR Report.

By pinning objects you can reduce /eliminate IOs. You can make response time for specific query predictable .

Server Result Cache 

A result cache is an area of memory ,  in the shared global area(SGA) that stores the result of the database query for reuse. 

SQL> select /*+ RESULT_CACHE */ dept_id , avg(salary)   FROM hr.employees
       GROUP BY department_id;

Good candidate for caching are queries that access a high number of rows but return a small number of rows, such as those in datawarehouse.

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-result-cache.htm