Search an element in a collection ("member of")

If we have to search for an element inside a collection (nested table), we often tend use a for loop and compare each element in the array.
Instead we can make use of inbuilt feature of Nested table "member of":
This searches whether the element exists inside the collection or not and returns True or False.

Below is a small example:

declare
type TYPJOB is table of varchar2(100); -- Nested Table type
V_TYPJOB TYPJOB ;
v_srch_str varchar2(100) := 'CLERK';
begin
select job bulk collect into V_TYPJOB from EMP;
if (V_SRCH_STR member of V_TYPJOB) then
DBMS_OUTPUT.PUT_LINE(v_srch_str || '--exists inside the Collection');
else
dbms_output.put_line(v_srch_str || '--doesnt exists inside the Collection');
end if;
end;

This will be pretty useful if the collection size is large.

Grants on an Object

If you would want to know what all grants does a Object have, we can make use of below procedure. This will mostly help to get grants from PRODUCTION environment, where developers have restricted access,when we do not have direct access to the main schema.
But before that we need to get access to DBMS_METADATA pkg from DBAs.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMP','SCOTT') from DUAL;

---------
"
GRANT SELECT ON "SCOTT"."EMP" TO "SCOTT"
"

Rebuild local Indexes in Parallel - DBMS_PCLXUTIL

If we are creating local index on large tables or rebuilding local indexes then we can consider using the dbms_pclxutil package.
it provides intra-PARTITION parallelism For Creating Partition Wise Local Indexes.
that means you can parallelize INDEX builds against each PARTITION in addition to across Partitions.
pclxutil just automates the rebuilding of the index using the job queues.


CREATE TABLE PCLXUTIL_TAB(ID NUMBER, DT_COB DATE) PARTITION BY RANGE (DT_COB)
(
PARTITION DT_2008 VALUES LESS THAN (TO_DATE('01/01/2009','MM/DD/YYYY')),
PARTITION DT_2009 VALUES LESS THAN (TO_DATE('01/01/2010','MM/DD/YYYY')),
PARTITION DT_2010 VALUES LESS THAN (TO_DATE('01/01/2011','MM/DD/YYYY')),
PARTITION DT_2011 VALUES LESS THAN (TO_DATE('01/01/2012','MM/DD/YYYY')),
PARTITION DT_2012 VALUES LESS THAN (TO_DATE('01/01/2013','MM/DD/YYYY')),
PARTITION DT_2013 VALUES LESS THAN (TO_DATE('01/01/2014','MM/DD/YYYY'))
);

INSERT INTO PCLXUTIL_TAB SELECT LEVEL , SYSDATE-LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
--SELECT * FROM PCLXUTIL_TAB;

CREATE INDEX PCLXUTIL_IDX ON PCLXUTIL_TAB(DT_COB) LOCAL UNUSABLE;
/* The above causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index */

BEGIN
DBMS_PCLXUTIL.BUILD_PART_INDEX(4,4,'PCLXUTIL_TAB','PCLXUTIL_IDX',TRUE);
END;
/

DBMS_PARALLEL_EXECUTE

One of the new features of Oracle 11G is DBMS_PARALLEL_EXECUTE package.
This allows us to do things in parallel.
If we have to update (insert) millions of rows, update statement isn't hard to write, but we need to think about rollback segment and more importantly time it takes to complete.
DBMS_PARALLEL_EXECUTE breaks up large tables based on some criteria and does the DML Operation in parallel on the chunks of data. This speeds up the DML Operations.
One can break the tables based on ROWIDs,NUMBER (Primary Key), Any Values, SQL Statement etc.Implicit commit happens.

To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the CREATE JOB system privilege.


http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html


http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php

Example:

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
num_col NUMBER,
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

insert /*+append*/ into test_tab select level,'Description for '|| level,case when mod(level,5)=0 then 10 when mod(level,3) = 0 then 20 else 30 end
from dual connect by level <=500000;

begin
DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'TEST_TASK');
END;

SELECT *
FROM user_parallel_execute_tasks;

--CREATE CHUNKS BY ROWID
BEGIN
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'TEST_TASK',table_owner => 'SCOTT',table_name => 'TEST_TAB',by_row => true,chunk_size => 10000);
end;

SELECT chunk_id, status, start_rowid, end_rowid
FROM user_parallel_execute_chunks
WHERE task_name = 'TEST_TASK'
ORDER BY chunk_id;

--RUN TASK

DECLARE
l_sql_stmt VARCHAR2(32767);
BEGIN
l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
SET t.num_col = t.num_col + 10
WHERE rowid BETWEEN :start_id AND :end_id';

DBMS_PARALLEL_EXECUTE.run_task(task_name => 'TEST_TASK',
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;

Oracle 11g: Error Logging in SQL *PLUS

One of the new features of 11G is error logging in SQL*PLUS.
When executing scripts from SQLPLUS, we will have to spool the output and then check for any errors in the spool file. But if we forget to spool, we will not be able to find out the errors.
In 11G we can simply turn on the error logging and all the errors will be stored in a special table called SPERRORLOG.

Example:
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> select 1/0 from dual;
select 1/0 from dual
SQL> commit;

Then we can query from any other session and see the errors:
select * from sperrorlog;
One has to commit in order to see the data in other session

We can pin all the errors to a message:
SQL> set errorlogging on identifier ‘RELEASE 2.4.0′

One has to have 11G client in order to make use of this feature.

RESULT_CACHE Parameters

Lets look into 4 result cache parameters:

select * from v$parameter where name like 'result_cache%';

NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0

Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.

So thats about the 4 parameters of result_cache of 11G.

Result_Cache Statistics

After enabling the result cache you would want to know whether it is really helping improve performance. This can be done with help of result cache views.
One such view is v$result_cache_statistics;
select name, value from v$result_cache_statistics;

NAME VALUE
Block Size(Bytes) 1024
Block Count Maximum 3036
Block Count Current 80
Result Size Maximum blocks 240
Create Count Success 10
Create Count Failure 80
Find Count 4
Invalidation Count 3
Delete Count Invalid 0
Delete Count Valid 80

If we examine the above output Create count failure is too high, which might not help in optimized performance.
Create count value tells us how many cache results that were failed to create.

Find Count value must be as high as possible for best performance. In this case its low. Find count depicts the number of cache results that were successfully found.

Delete count valid depicts the number of valid cache results deleted. This value should be relatively low to make system full use of server result caching.
block count current gives us the value of how much memory is used to store cached data.
This is some information about v$result_cache_statistics.