Print Message using user defined procedure

Print_line user defined function will help you to print the line which is more than 255 bytes.

create procedure print_line (
                 string_in IN VARCHAR2,
                 split_in  IN PLS_INTEGER DEFAULT 255
                 ) as
begin
   for i in 1 .. ceil(length(string_in)/split_in) loop
      dbms_output.put_line(substr(string_in,split_in*(i-1)+1,split_in));
   end loop;
end;
/
create or replace public synonym put_line for put_line;
grant execute on put_line to public;

Continue Statement

Finally continue statement added to oracle 11g PL/SQL language. It signals an immediate end to a loop iteration and return to first statement of in the loop.
e.g. Addition of odd numbers.

DECLARE
    total BINARY_INTEGER := 0;
BEGIN
    FOR i in 1..10 LOOP
        IF MOD(i/2) = 0 THEN
          CONTINUE;
        END IF;
        SUM = SUM + i;
    END LOOP,
    DBMS_OUTPUT.PUT_LINE('Sum of odd numbers is '|| sum );
END;
/

PL/ SQL Optimization

 

PL/ SQL Optimizer

Oracle 10g introduced  a new PL/SQL optimization feature to optimize the PL/SQL code by rearranging codes for better performance. PLSQL optimizer do this for you. Optimizer is enabled by default for interpreted p-code and native compilation. The default value for the optimizer is set to 2 but it can be unset or modify compiler aggressiveness.

PLSQL_OPTIMIZE_LEVEL = 0     no optimization
PLSQL_OPTIMIZE_LEVEL = 1     moderate optimize may leave unused code and exception
PLSQL_OPTIMIZE_LEVEL = 2     Aggressive optimize may rearrange source code flow

ALTER SESSION SET PLSQL_OPTIMIZER_LEVEL = 1;

ALTER PROCEDURE procedure_01 COMPILE plsql_optimizer_level =1;

ALTER PROCEDURE procedure_02 COMPILE reuse settings;

Subprogram Inlining

Subprogram inlining replaces a subprogram invocation from the copy of invoked subprogram. To allow subprogram inlining invoking set PLSQL_OPTIMIZE_LEVEL  to 2 (default value) or set it to 3. Subprogram inlining can also be done by using Pragma INLINE. PRAGMA INLINE can enable or disable subprogram inlining.

PRAGMA INLINE (subprogram, YES);              # enable subprogram inlining

PRAGMA INLINE (subprogram, NO);               # disable subprogram inlining

Candidates for Tuning

• Older code that does not take advantage of new PL/SQL language features.
• Older dynamic SQL statements written with the DBMS_SQL package.

• Code that spends much time processing SQL statements.

• Functions invoked in queries, which might run millions of times.

• Code that spends much time looping through query results.

• Code that does many numeric computations.

Unix Commands

Command	Description
passwd	Change user password
pwd	Print current directory
cd	Change directory
ls	List of the file in the directory
Wildcards	* matches any number of characters, ? matches a single character
file	Print the type of file
cat	Display the contents of a file
pr	Display the contents of a file
pg or page	Display the contents of a file one page at a time
more	Display the contents of a file one page at a time
clear	Clear the screen
cp or copy	Copy a file
chown	Change the owner of a file
chgrp	Change the group of a file
chmod	Change file modes, permissions
rm	Remove a file from the system
mv	Rename the file
mkdir	Create a directory
rmdir	Remove a directory
grep	Pattern Matching
egrep	Grep command with extended regular expression
find	Used to locate files or directories
date	Display the system date and time
echo	Write strings to standard output
sleep	Execution halts for the specified number of seconds
wc	Count the number of words, lines, and characters in a file
head	View the top of a file
tail	View the end of a file
diff	Compare two files
sdiff	Compare two files side by side (requires 132-character display)
spell	Spell checker
lp, lpr, enq, qprt	Print a file
lpstat	Status of system print queues
enable	Enable, or start, a print queue
disable	Disable, or stop, a print queue
cal	Display a calendar
who	Display information about users on the system
whoami	Display $LOGNAME or $USER environment parameter
who am i	Display login name, terminal, login date/time, and where logged in
talk	Two users have a split screen conversation
write	Display a message on a user’s screen
wall	Display a message on all logged-in users’ screens
rwall	Display a message to all users on a remote host
rsh or remsh	Execute a command, or log in, on a remote host
df	File system statistics
ps	Information on currently running processes
netstat	Show network status
vmstat	Show virtual memory status
iostat	Show input/output status
uname	Name of the current operating system, as well as machine Information
sar	System activity report
basename	Base filename of a string parameter
man	Display the on-line reference manual
su	Switch to another user, also known as super-user
cut	Write out selected characters
awk	Programming language to parse characters
sed	Programming language for character substitution
vi	Start the vi editor
emacs	Start the emacs editor

Different type of shell to declare

#!/usr/bin/sh      OR      #!/bin/sh               Declares a Bourne shell
#!/usr/bin/ksh    OR      #!/bin/ksh            Declares a Korn shell
#!/usr/bin/csh    OR      #!/bin/csh             Declares a C shell
#!/usr/bin/bash  OR     #!/bin/bash          Declares a Bourne-Again shell

Full table scans

The oracle optimizer uses full table scan in one of the following condition:

Lack of Index :  if query is unable to use the existing index, optimiser uses a full table scan ( unless a ROWID filter or cluster access path is available).

Large amount of data with low selectivity

Small table : A table which contains less than DB_FILE_MULTIBLOCK_COUNT_READ  blocks under high water mark. Full table is good for small tables.

Full Table Hints: If table is using FULL table hint.

High Degree of parallelism : Because of high degree parallelism optimizer uses full table scan over range scan.For more information on Degree of parallelism refer ALL_TABLES.