Oracle SQL Parallel hint

Each SQL statement undergoes an optimization and parallelization process when it is parsed. If parallel execution is chosen, then the following steps occur:

1. The user session or shadow process takes on the role of a coordinator, often called the query coordinator.
2. The query coordinator obtains the necessary number of parallel servers.
3. The SQL statement is executed as a sequence of operations (a full table scan to perform a join on a nonindexed column, an ORDER BY clause, and so on). The parallel execution servers performs each operation in parallel if possible.
4. When the parallel servers are finished executing the statement, the query coordinator performs any portion of the work that cannot be executed in parallel. For example, a parallel query with a SUM() operation requires adding the individual subtotals calculated by each parallel server.
5. Finally, the query coordinator returns any results to the user

Example
Parallel hint at Query level
SELECT /*+ PARALLEL(4) */

               e.name,

               e.dept
FROM   emp e,

               dept d
WHERE e.emp_id = d.emp_id

Parallel hint at Table level

Use table alias to provide hint at Table level

SELECT /*+ PARALLEL(e, 4) */ 

               e.name,

               e.dept
FROM   emp e,

               dept d
WHERE e.emp_id = d.emp_id

Oracle Performance Tuning: Flush Cache and Share memory

If you are doing Performance tuning every time you execute the Program/Query its important that you Flush the Cache and Shared memory. If you don't the results of Performance tuning exercise will be incorrect as the query will use Data available in Shared memory or Cache instead of fetching from DB thus resulting in faster execution.

1. alter system flush shared_pool is one way the alter system command statement can be used to clear existing data and re-load fresh data.

SQL> alter system flush shared_pool;

  
2. The FLUSH SHARED POOL clause of ALTER SYSTEM lets you clear all data from the shared pool in the SGA (system global area).


SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; 

Oracle Database Random Function in PLSQL

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.

Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551

Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

Generating an alphanumeric string of 20 characters.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

Generating a string of printable 20 characters.

SQL> select dbms_random.string('P', 20) str from dual;