Hash DataSet

Hashing :

1.  If the same DataSet is Passed to HashGenerator, we get the same Hash value out of it.

2. No two different Input Data Sets result in same Hash Value.


Based on above points, we can check if the process needs to be re-done.

Algorithm :

If HashOfTheProcessChanged then
    redo The Process;
else
    skip The Process;
end if;

           

Code :

 function generatehash (sqlstatement clob)  return varchar2 is

      xmlcontextHandle      DBMS_XMLGEN.CTXHANDLE;

      xml xmltype;

      xmlclob clob;

 begin

      xmlcontextHandle := dbms_xmlgen.newcontext (sqlstatement);

      begin

        xmlclob:=DBMS_XMLGEN.GETXML ( xmlcontextHandle);
      exception
        when others then
           -- close the context to release the resources & close the open cursors
          dbms_xmlgen.closecontext (xmlcontextHandle);
     end;

      dbms_xmlgen.closecontext (xmlcontextHandle);

      return (dbms_crypto.hash (xmlclob, 3));

 exception

    when others then   

         return (null);

 end generatehash;

Note : sqlstatement is the sql string (Can be Dynamic Sql)

          Be sure to use Order by Clause on the SQL Statement.

If Order by Clause not specified the order of the select fetch may change and it makes the hash change

Clob 1 :  (DataSet)

              1, kiran

              2, vishali

Clob 2 :  (DataSet)

              2, vishali

              1, kiran 

Hash on Clob 1 <> Hash on Clob 2

So the Sql Statement must specify the order by Clause to make sure we get the correct hash.

Example :

Select   generatehash  ( ' Select sysdate from dual' ) from dual;

Select   generatehash  ( ' Select ''1A2B3C'' from dual' ) from dual;

Declare 

  sqlStatement varchar2(100):= ' Select sysdate, ''1A2B3C'' from dual' ;

begin

  dbms_output.put_line(  generatehash (sqlStatement) );

end;

Oracle Table Clustering

Packages

Packages :

1. Specification (required)
2. Body (optional)

(i)   Information hiding : Implementation is hidden in the package body.
(ii)  Public and private : Specification (public) & Body (private).
(iii) Initialization:  package is initialized only once per session.
(iv)  Session persistence : If we establish a session and execute a program that assigns a value to a package-level variable,
                            that variable is set to persist for the length of the session, and it retains its value even if the
                            program that performed the assignment has ended.

Example:

create or replace package test_package
as
my_variable1 number;
my_variable2 varchar2(200);
end;
/

Session 1:
Begin
  test_package.my_variable1:=1;
  test_package.my_variable2:='kiran';
End;
/

Begin
  dbms_output.put_line(test_package.my_variable1);
  dbms_output.put_line(test_package.my_variable2);
End;
/

Session 2:
Begin
  dbms_output.put_line(test_package.my_variable1);
  dbms_output.put_line(test_package.my_variable2);
End;
/

Note:
The first time our session uses a package (whether by calling a program defined in the package, reading or writing a variable, or using a locally declared variable TYPE), Oracle initializes that package.
A package may be reinitialized in a session if that package was recompiled since last use
or
if the package state for our entire session was reset.

Oracle Pipelined Table Functions

PIPELINED functions are useful if there is a need for a data source other than a table in a select statement.PIPELINED functions will operate like a table.

Pipelined functions are simply "code you can pretend is a database table"

Pipelined functions give you the ability to : "select * from PLSQL_FUNCTION "

Steps to perform :

1.The producer function must use the PIPELINED keyword in its declaration.

2.The producer function must use an OUT parameter that is a record, corresponding to a row in the result set. 

3.Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword. 

4.The producer function must end with a RETURN statement that does not specify any return value. 

5.The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

Example:

CREATE OR REPLACE TYPE DateListTab AS TABLE OF date;

Create or replace FUNCTION TestPipelinedFunctions RETURN  DateListTab  PIPELINED IS

   fromdate date:= sysdate;

   Todate date:=sysdate+365;

begin

   for datecounter in to_number(to_char(fromdate, 'J')) .. to_number(to_char(Todate, 'J')) 

   loop

          PIPE ROW ( to_date(datecounter, 'J') );

   end loop;          

    RETURN;

end TestPipelinedFunctions;

SELECT * FROM TABLE(TestPipelinedFunctions);

Using Dates in For Loops

 To use dates simply convert the date to a number field using the ‘J’ format mask for dates, ‘J’ gives the number of days since 31 December 4713bc

Example code: 

declare

  fromdate date:= sysdate;

  Todate date:=sysdate+365;

begin

   for datecounter in to_number(to_char(fromdate, 'J')) .. to_number(to_char(Todate, 'J')) 

   loop

        dbms_output.put_line(to_date(datecounter, 'J'));    -- Convert back to date.

   end loop;          

end;

Pinning Tables into SGA ( Performance )

Identify Tables & Indexes need to be pinned onto SGA:

***************************************************************************

  SELECT    'alter table '

         || p.owner

         || '.'

         || p.name

         || ' storage (buffer_pool keep);'

    FROM dba_tables t,

         dba_segments s,

         dba_hist_sqlstat a,

         (SELECT DISTINCT pl.sql_id, pl.object_owner owner, pl.object_name name

            FROM dba_hist_sql_plan pl

           WHERE pl.operation = 'TABLE ACCESS' AND pl.options = 'FULL') p

   WHERE     a.sql_id = p.sql_id

         AND t.owner = s.owner

         AND t.table_name = s.segment_name

         AND t.table_name = p.name

         AND t.owner = p.owner

         AND t.owner NOT IN ('SYS', 'SYSTEM')

         AND t.buffer_pool <> 'KEEP'

  HAVING s.blocks < 50

GROUP BY p.owner,

         p.name,

         t.num_rows,

         s.blocks

UNION

SELECT    'alter index '

       || owner

       || '.'

       || index_name

       || ' storage (buffer_pool keep);'

  FROM dba_indexes

 WHERE owner || '.' || table_name IN

             (  SELECT p.owner || '.' || p.name

                  FROM dba_tables t,

                       dba_segments s,

                       dba_hist_sqlstat a,

                       (SELECT DISTINCT

                               pl.sql_id,

                               pl.object_owner owner,

                               pl.object_name name

                          FROM dba_hist_sql_plan pl

                         WHERE pl.operation = 'TABLE ACCESS'

                               AND pl.options = 'FULL') p

                 WHERE     a.sql_id = p.sql_id

                       AND t.owner = s.owner

                       AND t.table_name = s.segment_name

                       AND t.table_name = p.name

                       AND t.owner = p.owner

                       AND t.owner NOT IN ('SYS', 'SYSTEM')

                       AND t.buffer_pool <> 'KEEP'

                HAVING s.blocks < 50

              GROUP BY p.owner,

                       p.name,

                       t.num_rows,

                       s.blocks);

**********************************************************************

dbms_Shared_pool ( Performance )

dbms_shared_pool

Imagine a large object has to be loaded into the shared pool.

The database has to search for free space for the object.

If it cannot get enough contiguous space, it will free many small objects to satisfy the request.

If several large objects need to be loaded, the database has to throw out many small objects in the shared pool.

Finding candidate objects and freeing memory is very costly.  These tasks will impact CPU resources.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time.This process is known as pinning.

This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool. If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.


Pinning an object :  exec dbms_shared_pool.keep('owner.object');

View Pinned objects : select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';


How to Identify candidates that should be kept in the shared pool:

Step 1 :
select owner||'.'||name  Name ,
           type,
           sharable_mem,
           loads,
           executions,
           kept
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0
order by executions desc,loads desc,sharable_mem desc;

Step 2:

select * from x$ksmlru;

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.

KSMLRNUM  shows the number of objects that were flushed to load the large object
KSMLRISZ shows the size of the object that was loaded (contiguous memory allocated).

Analyze the x$ksmlru output to determine if there are any large allocations that are flushing other objects.
If this is the case, analyze the v$db_object_cache to identify the objects with high loads or executions.  These should be kept in the shared pool.