Oracle Performance Tuning: 2018

DBMS_XPLAN.DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views.

DBMS_XPLAN: The DBMS_XPLAN package provides an easy way to display the output of
the EXPLAIN PLAN command in several, predefined formats.You can also use the DBMS_XPLAN
package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or
stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution run time statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.

Parameter for DBMS_XPLAN.DISPLAY_CURSOR:
SQL_ID: sql_id we will get from V$SQL OR V$SQLAREA. Default this value as NULL.

STEPS:
1. Execute the query

select e.ename , e.sal, d.dname/*sql_execution2*/
from emp e ,dept d where d.deptno = e.deptno;

2. Then search SQL ID for the query that is executed

select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';

OUTPUT:
SQL_ID SQL_TEXT
----------------- ------------------------------------------------------------------------------------------

bt7bfjp0525vu select e.ename , e.sal, d.dname/*sql_execution2*/ from emp e ,dept d where
3. Chose the correct SQL_ID write to your query, then execute the below query to get actual plan

select * from table(dbms_xplan.display_cursor('bt7bfjp0525vu'));

DBMS_XPLAN

One more way we can see the Explain Plan by using DBMS_XPLAN package.

DBMS_XPLAN automatically queries the last plan in PLAN_TABLE.
It uses a TABLE() function with another pipelined function.

Example of DBMS_XPLAN:

select * from table(dbms_xplan.display);    

PLAN_TABLE Detils

In PLAN TABLE some of the important columns descriptions are as follows :

what to look for to help performance

Identify the solution for what problem by looking the TKPROF.

If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.

If Disk reads are very high: Indexes are not being used or may not exist

If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.

If parse elapse time is high: There may be a problem with the number of open cursors.

If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.

If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

1. SQL Statements: The first part of a TKPROF statement is the SQL Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics: It has all the Statistics for this SQL Statements. It has eight columns

call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

Count number of times each individual activity has been performed on this particular CALL.
CPU time used by this CALL.
ELAPSED time for this CALL(includes the CPU time).
DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of the last user to parse this statement.

4. EXPLAIN PLAN : This most useful section of the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.

Oracle SQL TRACE & TKPROF utility

Oracle SQL TRACE utility to measure timing statistics for a given query.
SQL TRACE records many vital information regarding actual query execution into a trace file.
SQL TRACE helps developers analyze every section of a query.
It writes query execution statistics like number of logical I/O, physical I/O, the CPU and elapsed timings, number of rows processed, query plans with row counts at each levels,information in wait events etc.
Its bit difficult to read the trace file .Using TKPROF we can generate readable report from trace file.

Steps for SQL TRACE

1. We need to set the below parameters.

ALTER SESSION SET TIMED_STATISTICS=TRUE;

This enables and disables the collection of timed statistics , such as CPU and elapsed time etc. The value can be TRUE or FALSE.

Below is the default destination of trace file.

USER_DUMP_DEST = \oracle\product\10.2.0\admin\tsm\udump

2. Enable the SQL TRACE for a session.

ALTER SESSION SET SQL_TRACE=TRUE;

3. Run the query(run your SQL).

SELECT ENAME,EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=10;

4. Disable the SQL TRACE .

ALTER SESSION SET SQL_TRACE=FALSE;

Steps for TKPROF

Once your trace file is ready then we need to run the TKPROF at the command line to generate the report.TKPROF accepts input as a trace file and it produces a formatted output file(report).

Syntax:

tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

Details of important parameters:
Tracefile: This is the name of the SQL TRACE file containing the statistics by SQL_TRACE.
Output_file: This is the name of the file where TKPROF writes its output.
sort = parameters: A multiple number of sorting options are available.

FCHCPU (CPU time of fetch);
FCHDSK (disk reads for fetch);
FCHCU and FCHQRY (memory reads forfetch);
FCHROW (number of rows fetched);
EXEDSK (disk reads during execute);
EXECU and EXEQRY (memory reads during execute);
EXEROW (rows processed during execute);
EXECPU (execute CPU time); PRSCPU (parse CPU); and
PRSCNT (times parsed).

print = number: This is the number of statements to include in the output. If this statement is not included, TKPROF will list all statements in the output.

Explain = username/password: Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option creates a plan_table of its own,so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished,this table is dropped. Ensure that you use the username/password of the user that parsed the cursor (ran the query) to ensure the explain is by the correct user.

waits=yes/no: Record summary for any wait events.

aggregate=yes|no: If no, then tkprof does not combine multiple users of the same SQL text.

insert=filename: This option creates a script to create a table and store the TRACE file statistics for each SQL statement traced.

table=schema.table: The table in which tkprof temporarily put execution plans before writing them to the output file.

Once your trace file is ready then we need to run the below command:

5. Go by command line to (cmd) .

oracle\product\10.2.0\admin\oracle\udump

find your trace file with your SPID

6. Run TKPROF command .

TKPROF <trace_file> <output_report.prf>

Decode Vs Case

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot.
Differences between DECODE and CASE are listed below:

DECODE performs an equality check only but CASE can work with logical operators other than "=".
DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.
CASE is a statement while DECODE is a function.
DECODE and CASE treats NULL differently. Normally, including CASE, NULL = NULL results in NULL, however when DECODE compares NULL with NULL result is TRUE.
CASE expects datatype consistency, DECODE does not.
CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

Example 1: DECODE and CASE treats NULL differently

SELECT  DECODE(NULL,NULL,-1) decode,

CASE NULL WHEN NULL THEN -1 END case

FROM DUAL;
/

Output:

DECODE CASE
---------- ----------

-1

Example 2: CASE expects datatype consistency, DECODE does not

SELECT CASE 2 WHEN 1 THEN 1 WHEN to_number('2') THEN to_number('2') END case
FROM DUAL
/
Output:
CASE
----------
2

SELECT  DECODE(2,1,1,'2','2') decode
FROM  DUAL
/

DECODE
-------
     2

SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
FROM  DUAL
/
SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case
                                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Relational Vs Nested Table

What is the difference between relational table and nested table?

A relational table can have virtual column, whereas nested table can not have virtual column.
Primary key, foreign keys are allowed on a relational table, whereas constraints are not allowed on nested table.

Truncate Vs Delete

What is the difference between truncate and delete?

Truncate deletes all records at once unconditionally, whereas delete can delete the records conditionally or unconditionally.
Deleted data by truncate command can not be rolled back, whereas deleted data by delete command can be rolled back.
Memory will be released after deletion of record by truncate command whereas memory will not be released after deletion of record by deleted command.
Truncate is a DDL command, whereas delete is DML command.
Trigger does not get fired in case of TRUNCATE whereas triggers get fired in case of a DELETE command.
We can not TRUNCATE a table if table have any foreign key constraint. We need to remove the constraint then truncate the table and recreate the constraint.

Long Vs Lob Data Type

What is difference between LONG and LOB data types?

Differences between LONG and LOB data type are listed below:

LOB

1) The maximum size is 4GB.

2) LOBs (except NCLOB) can be attributes of an object type.

3) LOBs support random access to data.

4) Multiple LOB columns per table or LOB attributes in an object type.

LONG

1) The maximum size is 2GB.

2) LONGs cannot.

3) LONGs support only sequential access.

4) Only one LONG column was allowed in a table

EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed.
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;

Using Tag:

EXPLAIN PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'

OR we can use below query to see the output in proper format

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

It shows the following information:

The row source tree is the core of the execution plan.
An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement
Data operations like filter, sort, or aggregation.

In addition to the row source tree, the plan table contains information about the following:

Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

Query processing can be divided into 7 phases :

Syntactic : Checks the syntax of the query
Semantic : Checks that all objects exist and are accessible
View Merging : Rewrites query as join on base tables as opposed to using views
Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
Optimization : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
QEP Generation : QEP = Query Evaluation Plan.
QEP Execution : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

Using toad , this can be achieved by following the steps below.

Connect to the Oracle SID
Open a SQL editor, and write the SQL query for which the explain plan is required.
CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL.
Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

ORA-01008 : Not all variables bound

ERROR: ORA-01008 : Not all variables bound

CAUSE : A SQL statement (or Dynamic SQL ) containing substitution variables was executed without all variables bound.

ACTION: All substitution variables must have a substituted value before the SQL statement is executed.

ORA-06548: no more rows needed

ERROR: ORA-06548: no more rows needed

CAUSE: The caller of a pipelined function does not need more rows to be produced by the pipelined function.

ACTION: Catch the NO_DATA_NEEDED exception in an exception handling block.
Catching the NO_DATA_NEEDED exception inside the pipelined function allows the function to perform any clean-up needed after the loop.

Example:
The table function returns 1000 rows, but the client (which communicates using ODBC) only fetches 200 at a time. If all the rows are fetched,
then there is no issue; however, if only a subset are fetched before another command is executed, the exception gets raised.

DECLARE

  --...

BEGIN

 ....

EXCEPTION

WHEN no_data_needed
THEN
RETURN; --clean up the resource

END;

/

CURRENT OF Clause

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.
The most recent row fetched from the table should be updated or deleted.
To use this clause we must declare the cursor with the FOR UPDATE clause.

Oracle provides the FOR UPDATE clause in SQL to allow the developer to lock a set of Oracle rows for the duration of a transaction. In Other sessions can only query the rows but they can not update or delete.

Current of clause is useful when a table doesn't have unique values or the table contains duplicate record or updating a column based on some condition . Otherwise if a table contains unique values or table doesn't contain duplicate records or updating columns without any conditions then no need of the where current clause.

Check the below example:

I have one table "EMP_DETAILS"with below data and in this table have duplicate data .

Ename	Sal
Smith	52000
Jones	50000
Smith	70000
King	60000

Now we are updating SAL with 1000 for each employee using below PL/SQL code.

DECLARE
CURSOR EMPLOY_CUR IS SELECT * FROM EMP_DETAILS;
BEGIN
FOR INDEX1 IN EMPLOY_CUR
LOOP
UPDATE EMP_DETAILS
SET SAL=SAL+1000
WHERE ENAME=INDEX1.ENAME;
END LOOP;
COMMIT;

END;

/

But in this code have some problem with duplicate record . Now try to understand the problem in update.Cursor in 1st record and Ename="Smith" and it will satisfy two record now SAL became
SAL=52000+1000 =53000
SAL=70000+1000 =71000

Cursor in 2nd record Ename="Jones"
SAL=52000+1000 =53000

Cursor in 3rd record Ename="Smith" again it will satisfy two record now SAL became
SAL=53000+1000=54000
SAL=71000+1000=72000

Now my cursor in 4th record Ename="King"
SAL=60000+1000=61000

Check the out put which is not correct :

Ename	Sal
Smith	54000
Jones	51000
Smith	72000
King	61000

Now we will write a code by using WHERE CURRENT OF clause

DECLARE
CURSOR EMPLOY_CUR IS SELECT * FROM EMP_DETAILS FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR INDEX1 IN EMPLOY_CUR
LOOP
UPDATE EMP_DETAILS
SET SAL=SAL+1000
WHERE CURRENT OF EMPLOY_CUR;
END LOOP;
COMMIT;

END;

/

Check the out put which is correct :

Ename	Sal
Smith	53000
Jones	51000
Smith	71000
King	61000

FORMAT_ERROR_BACKTRACE

FORMAT_ERROR_BACKTRACE in the DBMS_UTILITY package has been introduced in the Oracle version 10g. Format error backtrace is use to find the exact position where the exception has occurred.
When an exception is raised, one of the most important piece of information that a developer would like to know is the line of code that raised that exception for that we are using Format Error Backtrace.
Other then FORMAT_ERROR_BACKTRACE we use FORMAT_ERROR_STACK and FORMAT_CALL_STACK Functions.
To find the error we are using below functions

FORMAT_ERROR_BACKTRACE
FORMAT_ERROR_STACK
FORMAT_CALL_STACK.

Example:

PL/SQL Records

A PL/SQL Record is a group of related data items stored in individual fields, each with its own attribute name and data type. To access any field of a record, we use the dot (.) operator.

PL/SQL can handle the following type of records:

Table Based Record.
Cursor Based Record.
User Define Records.

Table Based Record:

%ROWTYPE attributes enables a programmer to create table based and cursor based records.

We are using the EMP table to show the concept of Table Based Record.

DECLARE
emp_rec emp%rowtype;
BEGIN
SELECT * into emp_rec
FROM emp
WHERE empno=7839 ;
dbms_output.put_line('Employee ID: ' || emp_rec.empno);
dbms_output.put_line('Employee Name: ' || emp_rec.ename);
dbms_output.put_line('Employee Department: ' || emp_rec.deptno);
dbms_output.put_line('Employee Salary: ' || emp_rec.sal);
END;

Cursor Based Record:

For Cursor Based Record also we are using %ROWTYPE. To show the concept of Cursor Based Record we are using MEP table.

DECLARE
CURSOR emp_cur is
SELECT EMPNO, ENAME, SAL
FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur into emp_rec;
EXIT WHEN emp_cur%notfound;
DBMS_OUTPUT.put_line(emp_rec.empno || ' ' || emp_rec.ename);
END LOOP;
END;

User Define Record:

PL/SQL provides a user-defined record type that allows you to define the different record structures.

These records consist of different fields. Suppose you want to keep track of your employee details.

You might want to track the following attributes about each employee−

Employee Name
Employee ID
Employee SAL
Employee Department Number

Now we are creating the above record

DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
emp_id number,
emp_sal number,
emp_dent_no number
);

/*Now we are declaring record-name type_name;*/

EMP_record1 employee_details_typ ;
EMP_record2 employee_details_typ ;
.....

Example:

DECLARE
TYPE employee_details_typ IS RECORD
(emp_name varchar2(50),
emp_id number,
emp_sal number,
emp_dent_no number
);
emp_datils1 employee_details_typ;
emp_datils2 employee_details_typ;
BEGIN
-- employee1 specification
emp_datils1.emp_name := 'Rabindra';
emp_datils1.emp_id := 2315;
emp_datils1.emp_sal := 50000;
emp_datils1.emp_dent_no := 10;
-- employee1 specification
emp_datils2.emp_name := 'Samir';
emp_datils2.emp_id := 2316;
emp_datils2.emp_sal := 50000;
emp_datils2.emp_dent_no := 20;


END;

BULK COLLECT in Oracle

The BULK COLLECT clause tell to SQL engine to bilk bind output collect before returning them to PL/SQL engine, we can use BULK COLLECT in SELECT INTO ,FETCH INTO ,RETURNING INTO clause.

BULK COLLECT in SELECT INTO Clause:

BULK COLLECT in FETCH INTO Clause:

BULK COLLECT in RETURNING Clause:

BULK COLLECT With LIMIT:
Collection is stored in the PGA memory, not the SGA memory. SGA memory is sared by all sessions connected by Oracle Database but PGA memory is allocated for each session. Thus if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, i assition to the memory allocated to the SGA.

To avoid this memory problem Oracle use the LIMIT clause.
Example:

DECLARE

CURSOR emp_cur IS
SELECT * FROM EMP;

TYPE emp_aat IS TABLE OF emp_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_emp emp_aat;
V_LIMIT_VAL NUMBER :=5;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp LIMIT V_LIMIT_VAL;
FOR IDX IN 1..l_emp.COUNT
LOOP
--write your logic
null;
END LOOP;
END LOOP;
CLOSE emp_cur;

END;
/

Bulk Bind in Oracle

Bulk Bind or we can say FORALL in Oracle.
The assigning of values to PL/SQL variables in SQL statements is called binding.The binding of an entire collection at once is called bulk binding.

Oracle use two engine to process the PL/SQL code.The PL/SQL engine accepts any valid PL/SQL block or subprogram and SQL engine executes the SQL statements.

Check the below figure how context switch between the PL/SQL and SQL engines.

PL/SQL engine executes procedural statements and sends SQL statements to the SQL engine.
SQL engine executes the SQL statements and returns data to the PL/SQL engine(switch between the PL/SQL and SQL engines ).
If context switch between the PL/SQL and SQL engines is many then it is performance issue.
That can happen when SQL statements execute inside a loop using collection (index-by table, nested table, varray, or host array) elements as bind variables.

Check the below example:

DECLARE
TYPE empno_type is table of emp.empno%type
index by pls_integer;
v_emp_no empno_type;
BEGIN
select empno bulk collect into v_emp_no
from emp where deptno in (10,20);
for i in v_emp_no.first..v_emp_no.last
loop
update emp set sal=sal+1000 where empno=v_emp_no(i).empno;
commit;
end loop;
END;

in this program UPDATE statement is sent to the SQL engine with each iteration of the FOR loop.

[NB:In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably.]

Using BULK BIND to improve performance:

Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines.
With bulk binds, entire collections(not just individual elements) are passed to the SQL engines for UPDATE the record at once.

Example:

DECLARE
TYPE empno_type is table of emp.empno%type
index by pls_integer;
v_emp_no empno_type;
BEGIN
select empno bulk collect into v_emp_no
from emp where deptno in (10,20);
forall i in v_emp_no.first..v_emp_no.last
update emp set sal=sal+1000 where empno=v_emp_no(i);
commit;

END;

FORALL Statement:

The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

It allows only DML (INSERT,UPDATE AND DELETE)

The collection subscript cannot be an expression.

All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30, 40);
BEGIN
depts.DELETE(3); -- delete third element
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
-- raises an "element does not exist" exception
END;

FORALL with SAVE EXCEPTION:

Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL.

Using FORALL i am inserting 100 record into a table, after 50 record failed with error.Then remaining record will not insert into the table.
Using FORALL with SAVE EXCEPTION , the error record will ignore( or error record will insert into your error log table) and remaining record will insert .

Create one table call exception_test.
Example:

DECLARE

TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

l_tab t_tab := t_tab();
l_error_count NUMBER;

ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
END LOOP;

-- Cause a failure.
l_tab(50).id := NULL;
l_tab(51).id := NULL;

EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO exception_test
VALUES l_tab(i);
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END;
/