Error looging on Table in PLSQL Oracle 11g Feature

CREATE TABLE TEST(

  id           NUMBER(10)    NOT NULL,

  code         VARCHAR2(10)  NOT NULL,

  description  VARCHAR2(50),

  CONSTRAINT dest_pk PRIMARY KEY (id)

);

-- Create the error logging table.

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'TEST');

END;

It will create ERR$_TEST new table

INSERT INTO TEST

SELECT *

FROM   source

LOG ERRORS INTO err$_test ('INSERT') REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$

FROM   err$_test

WHERE  ora_err_tag$ = 'INSERT';

TKPROFF key words explanation in PLSQL Oracle

Column Value	Meaning
PARSE	Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE	Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECTstatements, this identifies the selected rows.
FETCH	Retrieves rows returned by a query. Fetches are only performed forSELECT statements.
COUNT	Number of times a statement was parsed, executed, or fetched.
CPU	Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
ELAPSED	Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
DISK	Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
QUERY	Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT	Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, andDELETE.
ROWS	Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.
Note: Query + Current = Logical Reads (total number of buffers accessed)

Pipe line function in PLSQL Oracle

This pipe line function used for call collection in select statement as a table instead of looping row by row.



create type t_row as object(
id number,
name varchar2(100),
dob date);

create type t_tab is table of  t_row;


/* Formatted on 2016/03/29 11:38 (Formatter Plus v4.8.8) */
CREATE or replace FUNCTION g_tab_rf (p_no NUMBER)
   RETURN t_tab
AS
   v_tab   t_tab := t_tab ();
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      v_tab.EXTEND;
      v_tab (v_tab.LAST) := t_row (i, ' NAMe ' || i, SYSDATE + i);
   END LOOP;

   RETURN v_tab;
END;


select * from table(g_tab_rf(10));



CREATE OR REPLACE FUNCTION g_tab_prf (p_no NUMBER)
   RETURN t_tab PIPELINED
AS
BEGIN
   FOR i IN 1 .. p_no
   LOOP
      PIPE ROW (t_row (i, ' Name ' || i, SYSDATE + i));
   END LOOP;

   RETURN;
   EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('OTHERS Handler');
    RAISE;
END;


select * from table(g_tab_prf(10))
where rownum <= 5;

 --Another Example

 -- Build package containing record and table types internally.
CREATE OR REPLACE PACKAGE ptf_api AS
  TYPE t_ptf_row IS RECORD (
    id           NUMBER,
    description  VARCHAR2(50)
  );

  TYPE t_ptf_tab IS TABLE OF t_ptf_row;

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY ptf_api AS

  FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_ptf_tab PIPELINED IS
    l_row  t_ptf_row;
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      l_row.id := i;
      l_row.description := 'Description for ' || i;
      PIPE ROW (l_row);
    END LOOP;
 
    RETURN;
  END;
END;
/

SELECT *
FROM   TABLE(ptf_api.get_tab_ptf(10))
ORDER BY id DESC;


SELECT object_name, object_type
FROM   user_objects;




CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2) RETURN NUMBER AS
  l_return  NUMBER;
BEGIN
  SELECT ms.value
  INTO   l_return
  FROM   v$mystat ms,
         v$statname sn
  WHERE  ms.statistic# = sn.statistic#
  AND    sn.name = p_stat;
  RETURN l_return;
END get_stat;


-- Test table function.
SET SERVEROUTPUT ON
DECLARE
  l_start  NUMBER;
BEGIN
  l_start := get_stat('session pga memory');

  FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_rf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));
                       
                       
l_start := get_stat('session pga memory');    

FOR cur_rec IN (SELECT *
                  FROM   TABLE(g_tab_prf(100000)))
  LOOP
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular table function : ' ||
                        (get_stat('session pga memory') - l_start));                  
END;
/

Difference between Btree and Bitmap Index differences in Oracle PLSQL

These both are indexes

1. For Bitmap index we use Bitmap key word and for btree no need any keyword.
2. Bitmap index best to create low  cordiality columns means high duplicate values exist in that column we use bitmap index. When have less duplicate values use B tree index.

B-Trees are the typical index type used when you do CREATE INDEX ... in a database:

They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically)

They work better when you have a lot of distinct indexed values.

Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.

They are not useful when there are few distinct values for the indexed data, or when you want to get a large (>10% typically) subset of the data.

Each B-Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.

This characteristics make B-Tree indexes very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID, and you want good concurrent performance.

Bitmap indexes are a more specialized index variant:

They encode indexed values as bitmaps and so are very space efficient.

They tend to work better when there are few distinct indexed values

DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.

They are very inefficient when inserting/updating values.

Bitmap indexes are mostly used in data warehouse applications, where the database is read only except for the ETL processes, and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.

As a very short summary: use B-Tree indexes (the "default" index in most databases) unless you are a data warehouse developer and know you will benefit for a bitmap index.

PLSQL Performance Tuning with Hints in Oracle

/* Append */

The APPEND_VALUES hint in Oracle 11g Release 2 now allows us to take advantage of direct-path inserts when insert statements include a VALUES clause. Typically we would only want to do this when the insert statement is part of bulk operation using the FORALL statement. We will use the following table to demonstrate the effect of the hint.
This is because during a regular (conventional-path) insert, Oracle tries to use up any free space currently allocated to the table, including space left from previous delete operations. In contrast direct-path inserts ignore existing free space and append the data to the end of the table. After preparing the base table we time how long it takes to perform conventional-path insert as part of the FORALL statement. Next, we repeat the same test, but this time use a the APPEND_VALUES hint to give us direct-path inserts.

Ex:

INSERT /*+ APPEND */ INTO forall_test
    SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)
    FROM   dual
    CONNECT BY level <= l_size;

/* PARALLEL(3) */

This query will execute in multi threads like 3 parallel operations of employee table. So its will be fast.

Ex:
SELECT /*+ PARALLEL(employees 3) */ e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id=d.department_id;

Ex:
SELECT /*+ PARALLEL(4) */ hr_emp.last_name, d.department_name
FROM   employees hr_emp, departments d
WHERE  hr_emp.department_id=d.department_id;

/* FIRST_ROWS(10) */

This hint we use when we need to get only top 10 rows then will use this.

Ex: SELECT /*+ FIRST_ROWS(10) */ * FROM employees;

/* LEADING(e1) */
This hint we use when multiple table in the query and consider which table should take first preference while executing we can give leading table alias.


/* INDEX (t1 t1_idx1) */
This hint we use for forcefully consider the index while executing the query.

SELECT /*+ index(t1 t1_abc) index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;

/*+ DRIVING_SITE([@queryblock] ) */
Forces query execution to be done at a user selected  site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.

EX:
SELECT /*+ DRIVING_SITE(p1) AAA */ p1.first_name, p2.first_name, p2.last_name
FROM person p1, person@psoug_user p2
WHERE p1.person_id = p2.person_id
AND p1.first_name <> p2.first_name;

/* Ordered table1 table2*/ 
this hint is which order table should join in From clause.

How to use DBMS_PROFILER in PLSQL for running the procedure or function in Oracle Database

Ask Oracle DBA's to setup DBMS_PROFILER package before we use.

Create your PLSQL procedure or function in your instance.

SQL> execute dbms_profiler.start_profiler('YOUR_OBJECT');

PL/SQL procedure successfully completed.

SQL> exec YOUR_OBJECT;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

See the execution timings by running this query:

select s.text ,
       p.total_occur ,
       p.total_time/1000000000 total_time,
       p.min_time/1000000000 min_time,
       p.max_time/1000000000 max_time
from plsql_profiler_data p, user_source s, plsql_profiler_runs r
where p.line# = s.line
and   p.runid = r.runid
and   r.run_comment = 'YOUR_OBJECT'
and   s.name ='YOUR_OBJECT'


Ex:

Step 1)
create table tab (col1 varchar2(30), col2 varchar2(30));

Step 2)
create or replace procedure TEST
is
 vNumber number;
begin
 for i in 1..100000 loop
   vNumber := dbms_random.random;
   insert into tab values (vNumber,vNumber);
 end loop;
end;


Step 3)
SQL> execute dbms_profiler.start_profiler('TEST');

PL/SQL procedure successfully completed.

SQL> exec TEST;

PL/SQL procedure successfully completed.

SQL> execute dbms_profiler.stop_profiler;

PL/SQL procedure successfully completed.

Step 4)
SQL> select s.text ,
  2         p.total_occur ,
  3         p.total_time/1000000000 total_time,
  4         p.min_time/1000000000 min_time,
  5         p.max_time/1000000000 max_time
  6  from plsql_profiler_data p, user_source s, plsql_profiler_runs r
  7  where p.line# = s.line
  8  and   p.runid = r.runid
  9  and   r.run_comment = 'TEST'
 10* and   s.name ='TEST'
SQL> /

TEXT                           TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
------------------------------ ----------- ---------- -------- --------
procedure binds                          1         .0       .0       .0
procedure binds                          3         .0       .0       .0
procedure binds                          0         .0       .0       .0
for i in 1..100000 loop             100001         .0       .0       .0
vNumber := dbms_random.random;      100000         .2       .0       .0
insert into t1 values (vNumber      100000        6.9       .0       .4
,vNumber);              
end;                                     1         .0       .0       .0
procedure binds                          2         .0       .0       .0

8 rows selected.