We know about ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$ (will be populated only for update / delete), ORA_ERR_OPTYP$, ORA_ERR_TAG$ – These are mandatory columns for any DML error logging table. Note, I have created a column called ID which is common to EMP1 and DEPT1 table.

Now, lets try to perform INSERT into EMP1 and DEPT1 tables. Here I am using multi table inserts just for the sake of it.

Explanation for above result set – The first 4 records Oracle error number 1722 – is due to insertion of character value in number field in EMP1 table.

The next 4 records Oracle error number 12899 is due to insertion of value larger than expected into DEPT1 table name field.

Note the ID column – that has the value of respective ID’s for the table concerned. Now, we can easily identify which table it belongs to from ORA_ERR_TAG$ table – which contains the description. In this way we can make DML error loging process generic and efficient. DML error logging is quite good when we try to perform INSERT based on SELECT from global temporary table / normal table. As there is no direct error logging feature for bulk inserts using SELECT as we have it for BULK DML operations using COLLECTIONS.

Having said all this – its quite important to know why it had caused the error in first place.

To find NTH maximum value from a table, we may need to query the same table twice to get the desired output. This will lead to table scans / index scans for the same table twice. The same can be achieved via analytical functions by querying the table once. Have enclosed the execution plan and have highlighted the difference.

To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be part of a huge query – helps to avoid group by – so the need to query the same table multiple times is reduced.

To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time.

NON ANALYTICAL WAY –

Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno

With the usual way to pick last set of records from any table minimum 3 joins are required. Using analytical functions the same can be reduced to single table hit to retrieve the last N rows and display the row numbers for the same. This is particularly useful when something like this needs to be achieved in a big query that has multiple joins and multiple column fetch.

In FORALL collections.FIRST .. collections.LAST it is not possible to use the indices sequntially if the collection is sparse. But, the same can be handled in Oracle 10g by using FORALL IN INDICES OF keyword.

FORALL i IN VALUES OF emp_id_tabINSERT INTO EMP1 VALUES emp_tab(i);
END;
/
OUTPUT

PL/SQL procedure successfully completed.

EMPNO STILL_EMPLOYED
10 Y
9 Y
8 Y

If we note the above results, the VALUES OF clause exactly matches the elements of one collection vs the elements of other collection and inserts values. This cannot be achieved in Oracle 9i unless we explicitly match up the elements in the WHERE clause.

SET SERVEROUTPUT ON
DECLARE
TYPE software_tab IS TABLE OF VARCHAR2(10);
software_list_1 software_tab := software_tab(‘Oracle’,’C’,’C#’,’VB’,’Sql’);
software_list_2 software_tab := software_tab(‘Oracle’,’C’,’PHP’,’Java’);
software_list_3 software_tab;
BEGIN
IF (software_list_3 IS NULL) AND (software_list_1 IS NOT NULL) THEN
DBMS_OUTPUT.put_line(‘Value – list3 is null and list1 is not null’);
END IF;
software_list_3 := software_list_1;
IF (software_list_3 = software_list_1) AND (software_list_3 != software_list_2) THEN
DBMS_OUTPUT.put_line(‘list3 = list1 and list3 != list2 ‘);
END IF;

IF (SET(software_list_2) SUBMULTISET software_list_1) AND (software_list_1 NOT SUBMULTISET software_list_2) THEN
DBMS_OUTPUT.put_line( ‘list2 submultiset of list1 and list1 is not sub multiset of list2’);
END IF;

Cursor_Sharing enables to notice similar SQL statements that are already parsed and available in SQL area. When the query is issued for the first time, the same is stored in the SQL area. Later, on issuing same / similar SQL statements, the query in the memory would be processed – parsing will not take place again. Default cursor sharing parameter is EXACT. Only if the SQL statement is exactly similar the query will be used otherwise it will be parsed again.

Setting cursor_sharing to FORCE or SIMILAR enables similar statements to share the SQL.
FORCE – forces similar SQL statements to share the SQL area, detoriating the explain plans.
SIMILAR – similar SQL statements to share the SQL area, without detoriating the explain plans.
EXACT – only exact SQL statements share the SQL area. This is the default value.

Caution: Setting CURSOR_SHARING to FORCE or SIMILAR prevents any outlines generated with literals from being used if they were generated with CURSOR_SHARING set to EXACT.

All the above three modes will work similarly for bind variables and literals. This results in bind variable peaking problem – it will choose the same explain plan however the data is distributed without considering the percentage of data returned by the predicate.

Oracle 11g has introduced a new feature adaptive cursor sharing to to choose different plan for queries containing bind variables on skewed data .

With adaptive cursor sharing for the first query retrieving 500 records index scan; second query where 23929 records were fetched – FTS was performed.

2. Bind Variable Peeking

There could be columns with heavily skewed data or otherwise. It should be the case that heavily skewed data should use a different execution plan as opposed to normal data.

If a query needs to be tuned that has bind variable peeking issue then, we can deactivate bind peeking. It uses bind-aware cursor sharing.
Adaptive cursor sharing is the solution for bind variable peeking problem. It only shares the plan only if bind variables are equal. If the bind variables are equal and falls within the range then, it uses the same plan. If the bind values are not equivalent then it creates a new plan.

About This Blog

An out and out example-based blog for improving performance of PL/SQL or SQL code, understanding new features based on what we already know, and advantages and illustrations for built-in Packages and other features in Oracle.