Oracle-Shared Pool Concept

Oracle interview question on shared pool:

The shared pool is one of the most critical memory components particularly when it comes to how SQL executes.

The way you write SQL doesn’t just effect the individual SQL statement itself. The combination of all SQL that executes against the database has a tremendous effect on overall performance and scalability due to how it affects the shared pool.

Shared Srinimf JOBSpool is where Oracle caches program data. Every SQL statement executed will have its parsed form stored in the shared pool. The area within the shared pool where statements are stored is called the library cache.

Even before any statement is parsed, Oracle will check the library cache to see if that same statement already exists there. If it does exist, then Oracle will retrieve and use the cached information instead of going through all the work to parse the same statement again. The same thing goes for any PL/SQL code you run.

The really nifty part is that no matter how many users may want to execute the same SQL statement, Oracle will typically only parse that statement once and share it among all users who want to use it. Maybe you can see where the shared pool gets its name.

SQL statements you write aren’t the only things stored in the shared pool. The system parameters Oracle uses will be stored in the shared pool as well. In an area called the dictionary cache, Oracle will also store information about all the database objects. In general, Oracle stores pretty much everything you could think of in the shared pool. As you can imagine, that makes the shared pool a very busy and important memory component.

Since the memory area allocated to the shared pool is finite, statements that originally get loaded may not stay there for very long as new statements are executed. A Least Recently Used (LRU) algorithm regulates how objects in the shared pool are managed. To borrow an accounting term, it’s similar to a FIFO (First In First Out) system. The basic idea is that statements that are used most frequently and most currently are what are retained. Unlike a straight FIFO method, how frequently the same statements are used will effect how long they remain in the shared pool.

If you execute a SELECT statement at 8 A.M.and then execute the same statement again at 4 P.M., the parsed version that was stored in the shared pool at 8 A.M. may not still be there. Depending on the overall size of the shared pool and how much activity it has between 8 A.M. and 4 P.M., as Oracle needs space to store the latest information throughout the day, it will simply reuse older areas and overlay newer information into them. But, if you execute a statement every few seconds throughout the day, the frequent reuse will cause Oracle to retain that information over something else that may have originally been stored later than your statement but hasn’t been executed frequently, or at all, since it was loaded.

Stratus- Architecture Of Fault Tolerant System

Modularity- The hardware and software are constructed of modules of fine granularity. These modules constitute units of failure, diagnosis, service, and repair. Keeping the modules as decoupled as possible reduces the probability that a fault in one module will affect the operation of another.

Fail-Fast Operation. A fail-fast module either works properly or stops. Thus, each module is self-checking and stops upon detecting a failure. Hardware checks (through error-detecting codes; and software consistency tests support fail-fast operation.

Single Failure Tolerance –When a single module (hardware or software) fails, another module immediately takes over. For processors, this means that a second processor is available. For storage modules, it means that the module and the path to it are duplicated.

Online Maintenance – Hardware and software modules can be diagnosed, disconnected for repair and then reconnected, without disrupting the entire system’s operation.

COBOL Features Not Allowed In CICS

UntitledMany features of command level features not allowed in CICS program.

  • Under CICS, all terminal I/O is handled by the terminal control module, whose services are typically requested by issuing SEND MAP and RECEIVE MAP commands. Because of that, COBOL Accept and Display statements aren’t allowed in CICS programs.
  • Under CICS, all file I/O is handled by the file control module, whose services are requested by issuing CICS file control commands like READ, WRITE, REWRITE, and DELETE. Because of that, the COBOL statements for file I/O aren’t allowed. In addition, Environment Division and File Section statements that pertain to data management should not be coded.

  • COBOL statements that invoke operating system functions, like Accept Date and Accept Time, are not allowed. However, COBOL intrinsic functions are allowed.

  • Although the COBOL statements for sorting (Sort, Release, and Return) are allowed, their functionality is severely restricted. Because of that, you’ll probably never use them. Merge statements are never allowed.

Below are the statements that are not allowed in CICS:

  • Operator communication statements
  1. Accept
  2. Display
  • File I/O statements
  1. Open
  2. Close
  3. Read
  4. Rewrite
  5. Delete
  6. Start

-Other statements

  1. Accept Date/Time
  2. Merge

Ultra Basic Unix Commands – Part-1

UNIX+PL-SQL JOBSBelow are basic UNIX commands:

cat – display or concatenate files

cd – change directory

chmod – change the permissions on a file or directory

compress – compress a file

cp – copy a file date – display the current date and time

diff – display differences between text files

echo – echo arguments to the standard output

file – determine the type of a file

find – find files of a specified name or type

finger – display information about a user

ftp – file transfer program

grep – searches files for a specified string or expression

kill – kill a process

lpr – print out a file

ls – list names of files in a directory

man – display an on-line manual page

mkdir – make a directory

more – scan through a text file page by page

mv – move or rename files or directories

nice – change the priority at which a job is being run

passwd – change your password

ps – list processes

pwd – display the name of your current directory

quota – disk quota and usage

rm – remove files or directories

rmdir – remove a directory

sort – sort and collate lines

talk – talk to another user

wc – display a count of lines, words and characters

cat – display or concatenate files

cat takes a copy of a file and sends it to the standard output (i.e. to be displayed on your terminal, unless redirected elsewhere), so it is generally used either to read files, or to string together copies of several files, writing the output to a new file.

cat ex displays the contents of the file

ex. cat ex1ex2 > newex

creates a new file newex containing copies of ex1 and ex2, with the contents of ex2 following the contents of ex1.

Read my blog for remaining commands.

PL/SQL Architecture

When a PL/SQL program unit is passed to Oracle, it gets processed through a compiler. The compiler creates a syntax tree, passes it through an optimizer, and generates machine code that’s stored in the database for execution at a later time. Oracle 9i introduced native compilation. Using native compilation, the code is converted to shared C libraries on the database host. Computation-intensive PL/SQL programs will notice an improvement in performance since little interpretation is required by Oracle in order to process the instructions.

Execution is performed in the PL/SQL engine with the assistance of the PL/SQL virtual machine and the SQL engine. When a program unit is called, code that is compiled as interpreted has its machine code (MCode) heap loaded into the SGA. The instructions are processed by the PL/SQL virtual machine which communicates with the RDBMS kernel. The PL/SQL engine carries out the instructions with the assistance of the SQL engine to help with the SQL statements.

If a program unit is compiled using native compilation, instead of the machine code heap being loaded to the SGA, the shared libraries are loaded to the PGA. The PL/SQL virtual machine is still required, though it does not need to do any interpretation of the code. The PL/SQL and SQL engines perform the same functions as with interpreted.

Early Binding Vs Late Binding

Late binding means that code is compiled at execution. Languages that use late binding are generally very flexible since modifications can be made up to and during compilation and execution.

Early binding means that code is compiled prior to execution. Namespaces are verified, permissions are checked, and all syntax is validated. This saves considerable time during execution since much of the work has already been completed. Oracle PL/SQL employs early binding.

UNIX- Sed And AWK Commands

Sed Command:

The sed command does much the same thing as ed. The main difference is that sed performs these actions in a noninteractive way. Sed is a stream editor (thus the name), and it is designed to work on a specified stream of text according to rules set by the user beforehand. This text stream is usually the output of a previous operation, whether instigated by the user or part of a list of commands that run automatically. For example, the output of the ls command produces a stream of text — a directory listing — that can be piped through sed and edited. In addition, sed can work on files. If you have a group of files with similar content and need to make a particular edit to the contents of all these files, sed will enable you to do that very easily. For example, have a go at the following “Try it Out” section, in which you combine the contents of two files while at the same time performing a substitution for the name “Paul” in both files.

Try it Out: Work with Sed

Editing commands for sed can be provided at the command line:

Create two files, each with a list of first names, in vi:

% vi names1.txt

% vi names2.txt


At the command line enter and run the following command:

% sed -e s/Paul/Pablo/g names1.txt names2.txt > names3.txt

Display the output of the third file to discover the resulting list of names:

% cat names3.txt

How It Works

The sed utility reads the specified files and/or the standard input and modifies the input as directed by a list of commands. The input is then written to the standard output, which can be redirected if need be.

In this example, the sed command is searching for all instances of the name Paul in the two files provided in the command-line argument and replacing them with the name Pablo. After the search and replace has been completed, the output is redirected from standard out to a new file called names3.txt. Notice the trailing g in the command, s/Paul/Pablo/g:

% sed s/Paul/Pablo/g names1.txt names2.txt > names3.txt

This specifies that sed should look globally. Without that trailing g, if the name Paul happened to be on the same line twice, only the first would be substituted.

Note that while only one line from each file was affected by substitution, all the lines from both files are displayed, in the order they are processed, in the output from sed. The original files are unchanged; only the output, or in this example the file created from the output, contains the substitution of Pablo for Paul.

-e Option

Using the -e Option

Multiple commands may be specified by using the -e option:

% sed -e ‘s/Paul/Pablo/; s/Pat/Patricia/’ names1.txt names2.txt


The -e option is necessary when supplying more than one editing command as a command-line argument to sed.
Note that while enclosing the instructions in single quotes is not required (they weren’t used in the first sed example), they should be used in all cases.
Enclosing the instructions in quotes helps the user visualize what arguments are related to editing and what arguments are related to other information, such as which files to edit.
Moreover, the enclosing single quotes will prevent the shell from interpreting special characters or spaces found in the editing instruction.
AWK Command
Sed works much like editing commands manually in any type of text editor, so it’s a good choice for editing text in a file or from other commands in a noninteractive, batch environment. But sed does have some shortcomings, such as a limited capability to work on more than one line at a time, and it has few rudimentary programming constructs that can be used to build more complicated scripts. So there are other solutions when it comes to scripting complex text processing; AWK, which offers a more general computational model for processing a file, is one of them.

A typical example of an AWK program is one that transforms data into a formatted report. The data might be a log file generated by a Unix program such as traceroute, and the report might summarize the data in a format useful to a system administrator. Or the data might be extracted from a text file with a specific format, such as the following example. In other words, AWK is a pattern-matching program, akin to sed.

Try it Out: Use AWK

Try out this one awk command at the command line:

%awk '{ print $0 }' /etc/passwd

The results will look something like the following, depending on the entries in the /etc/passwd file:

sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
pdw:x:500:500:Paul Weinstein:/home/pdw:/bin/bash

How It Works

AWK takes two inputs: a command, set of commands, or a command file and a data or data file. As with sed the command or command file contains pattern-matching instructions for which AWK is to use as a guideline for processing the data or data file.

In this example, AWK isn’t processing any data but is simply reading the /etc/passwd file’s contents and sending the data unfiltered to standard out, much like the cat command. When AWK was invoked, it was provided with the two pieces of information it needs: an editing command and data to edit. The example specifies /etc/passwd as input file for data, and the edit command simply directs AWK to print each line in the file in order. All output is sent to standard out (which can be directed elsewhere), a file, or another command.

PL/I- Compile JCL And Run JCL

Compile JCL for PL/1

000003 //* PARM.PLI=’O=0,NED,NM,LD,ND,NS2,NE,NL,C60′   <= FOR PL1LF
000006 //

RUN JCL for PL/1

000002 //STEP1 EXEC PGM=PRG01                                                   <= PGM NAME
000007 //

CICS – DATE And TIME Command

When we are working on CICS, we need to obtain Date and Time from CICS terminals. When a task is started, CICS will store these values in exec interface block fields like EIBDATE and EIBTIME.

When a task is started these fields will be updated with TIME and date.

There is a way how to extract Date and Time from CICS.

The ASKTIME command will update DATE and TIME into EIBDATE and EIBTIME fields. Later we need to use FORMATTIME command to get Date and Time in format whatever way we want.

ASKTIME [ ABSTIME(data-area) ]

ABSTIME command places DATE and TIME fields into local area.

How to get DAY-OF-WEEK using below code?

This example shows how to obtain an absolute time and, using a FORMATTIME command, get the current day-of-week. ABSOLUTE-TIME is a 15-digit packed-decimal field (PIC S9(15) COMP-3), and DAY-OF-WEEK is a binary fullword (PIC S9(8) COMP).


Keep reading my blog and like my Facebook page.


CICS – Multithreading And Working Storage

When you code a command-level CICS program in COBOL, you can use the Working-Storage Section just as you do in any other COBOL program: for program variables and switches, as well as for data areas for terminal and file I/O.

When CICS loads your program, it allocates storage for its Working-Storage Section and associates that storage with your program.

If more than one task is executing the same program, CICS uses multithreading to provide each with a separate copy of working storage, So you don’t have to worry that the fields you’ve set will be changed by another user’s execution of the program.

Main storage is a more valuable resource under CICS than it is in batch COBOL, so use common sense when dealing with working storage. For example, don’t define a 10,000-byte table when a 1,000-byte table will do.

  • Interview Questions-What is multi threading in CICS?
  • What is main storage?