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

Steps to open Debug in Oracle

 https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9528615800346614204

select * from dba_network_acls;

grant execute on DBMS_DEBUG_JDWP to  <User>

grant   DEBUG CONNECT SESSION , DEBUG ANY PROCEDURE to  <USER>;

 begin

         dbms_network_acl_admin.append_host_ace

          (host=>'127.0.0.1',

           ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,

                         principal_name=>'<USER>',

                           principal_type=>sys.XS_ACL.PTYPE_DB) );

       end;

    /

exec dbms_network_acl_admin.drop_acl( ACL_NAME);

Index usage with like operator and Domain Index

 https://tipsfororacle.blogspot.com/2017/02/index-usage-with-like-operator-and.html?m=1

Dynamic sql

Retrieving DML Results into a Collection with the RETURNING INTO Clause

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm


sql_stmt := 'UPDATE Scott.emp
SET sal = (sal+ sal * :a)
where empno = :b
RETURNING sal
INTO :c';

/* Executing a Dynamic SQL for Oracle Version 8.1.7 onwards */

EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno RETURNING INTO UpdatedSalary;

/* You can use this way als0
EXECUTE IMMEDIATE sql_stmt USING VarSalPercentage,VarEmpno,OUT UpdatedSalary; */

------------------------------------------------------------------------------
COPY is Undead: copy data from one database to another.

Copy command is obsolete now, but still useful when you need to copy large amount of data (espcially LONG datatype)  without filling the undo/rollback segments.

http://docs.oracle.com/cd/B10500_01/server.920/a90842/apb.htm

--------------------------------------------------------
Dont Forget: http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html

Reference Cursor

 

Create procedure (dept_in  Number,  emp_ref_cur  SYS_REFCURSOR)

IS
Begin

Open emp_ref_cur  FOR   select * from employees where dept_id = dept_in;

If emp_cur%rowcount = 0 Then

....

End if;

End;
/

Bulk Exception with FORALL .. SAVE EXCEPTION

 The Bulk Exception are used to save the exception information and continue processing .

In order to  bulk collect exception information, we use FORALL clause with SAVE EXCEPTIONS keyword.

All exceptions raised during execution as saved in %BULK_Exception attribute.

SQL%BULK_EXCEPTION(i).ERROR_CODE  hold the corresponding Oracle error code.

SQL%BULK_EXCEPTION(i).ERROR_INDEX  holds the iteration number of the FORALL statement.

SQL%BUK_EXCEPTION.COUNT  holds the total number of exceptions encountered.

Select * BULK COLLECT INTO  v_collections
From TestTable;

FORALL  idx  IN  v_collections.FIRST .. v_collections.LAST  SAVE EXCEPTIONs
.....
.....

EXCEPTION

 WHEN OTHERS THEN

 FOR idx IN 1..  SQL%BULK_EXCEPTIONS.COUNT
 LOOP
   v_ind := SQL%BULK_EXCEPTIONS(idx).Error_index ;   

 Dbms_output.Put_Line('Error encountered at '||SQL%BULK_EXCEPTIONS(idx).Error_index);

 Dbms_output.Put_Line('Values '||v_collections(v_ind).Emp_id
                                || v_collections(v_ind).First_name
                                || v_collections(v_ind).Last_name );

 Dbms_output.Put_Line('Oracle Error Ora-Code '||SQL%BULK_EXCEPTIONS(idx).Error_code);

 End Loop;

End;