Welcome to Charles Blog

Oracle Database 11g: SQL Query Result Set Caching

SQL Query Result Set Caches

In previous versions it was possible to manipulate the instance’s memory structures to improve the performance of user queries. For instance, a table or index could be “pinned” into memory via usage of the db_keep_cache. Alternatively, tables could be “cached,” prohibiting tables – accessed via a full scan – from being placed on the tail-end of the LRU.

In 11g Oracle introduces a new area of memory within the shared pool, in which it pins the blocks of appropriately marked user queries and/or deterministic PL/SQL programs. Once pinned, these blocks can be reused by future submissions of the query and/or PL/SQL calls in question; this significantly reduces I/O. This is referred to as server-side SQL Result Caching.

A SQL Query Result Set will be cached within a subsection of a database instance’s shared pool in the Shared Global Area (SGA) depending on the setting for several new initialization parameters.

RESULT_CACHE_MODE. This new parameter accepts one of three values, and it can be set at either the database (ALTER SYSTEM) or individual session (ALTER SESSION) level:

• When set to MANUAL (the default), a SQL query result set will only be considered for potential caching if the query itself specifies the +RESULT_CACHE optimizer hint.
• If this parameter is set to FORCE, however, a query’s result set will always be cached unless the query specifies the +NO_RESULT_CACHE optimizer hint.
• Finally, if this parameter is set to AUTO, Oracle 11g uses an unpublished internal algorithm to automatically determine whether a query’s result set should be cached based on how often the result set could benefit future statement execution. Only if the query specifies the +NO_RESULT_CACHE optimizer hint will it be ignored.

Controlling Result Set Cache Memory Utilization. Oracle 11g also provides several methods to limit precisely the amount of memory that may be allocated for SQL query result set caching:

RESULT_CACHE_MAX_SIZE. To reserve an appropriate amount of SGA memory for all local result caches, the DBA can specify a value for the RESULT_CACHE_MAX_SIZE initialization parameter. Oracle 11g automatically rounds the supplied value to the nearest 32K boundary.

If no value is supplied, then Oracle 11g uses the following algorithm to allocate memory for Result Caches:

• If a value has been specified for the new Oracle 11g MEMORY_TARGET parameter (i.e. the total memory allocated to both SGA and PGA for the database instance), then Oracle sets RESULT_CACHE_MAX_SIZE to 0.25% of MEMORY_TARGET.
• If no value for MEMORY_TARGET has been set, but a value for SGA_TARGET has been set, then Oracle 11g sets RESULT_CACHE_MAX_SIZE to 0.5% of SGA_TARGET.
• Finally, if neither a value for MEMORY_TARGET or SGA_TARGET has been set, then Oracle sets RESULT_CACHE_MAX_SIZE to 1.0% of the memory allocated to the Shared Pool based on the setting for SHARED_POOL_SIZE.

Regardless of which calculation method is used, note that Oracle 11g will never set RESULT_CACHE_MAX_SIZE to more than 75% of SHARED_POOL_SIZE. Moreover, note that if the DBA wants to deactivate SQL Result Caching features completely, she merely needs to set the size of this memory allocation area to zero (0) to tell Oracle 11g to reserve absolutely no memory for results caching.

RESULT_CACHE_MAX_RESULT. This parameter tells Oracle 11g how much of the result cache should be allowed for any individual query. Its default value of 5% of the entire result cache should usually be sufficient, but it can also be set between 0% and 100%.

RESULT_CACHE_REMOTE_EXPIRATION. If a query depends on a remote database, then this parameter determines the number of minutes for which a result set should be retained. The default value of zero (0) minutes serves as a reminder that any changes to a remote database table can’t be detected at the local database, and therefore stale result sets might remain for an unduly long period of time. This parameter can be set globally (ALTER SYSTEM) or on a per-session basis (ALTER SESSION).

MOSS BACKUP

Share point backup/recovery can be done at different levels..

Binary Files  (OS) : Will be required to reinstall OS and provide for  updates

Configuration (IIS) : Need to backup IIS metabase, and document all IIS configuration.

Binary files (Office Share point server) : Installation Of Share point Server (MOSS)

Configuration (Office SharePoint Server) : Configurations in Office SharePoint Server are set in Central Administration and stored in the configuration database and the Central Administration content database and need to be backup regularly.

Customizations : Stsadm export and import operations, to archive and re-insert a site or subsite. Although using the export operation saves data for us, it does not produce a full-fidelity backup. The export operation can include content and security settings only. It does not include customizations and settings such as workflow, alerts, features, solutions, or Recycle Bin state which may have to be done by the content versioning module.

Content: Can be backup and restored using Versioning, which addresses the issue of losing data by overwriting a document. It allows the document library to keep multiple copies of the same document. In the event of an unwanted change, an overwrite, or a document corruption, the previous version can easily be restored by the end user. You turn on versioning at the library or list level.

Things to Do :

Backup all SQL DB regularly.
Backup all IIS metadata and document configuration.
Backup using stsadm export/import operation site configurations and data at site level and farm level
Use Share point versioning from the user level upwards.
Try to recreate the farm from the above strategies on to a different system If this application is critical, go ahead on the following:
Create a recovery farm/fail over farm
Recover DB & Site collection to new locations.

A practical and effective backup strategy for  Windows SharePoint Services installation is to use DB backup and Stsadm . At longer intervals, such as monthly, back up our  entire set of databases by using the Db backup tools. At shorter intervals, such as weekly, run Stsadm to back up just those site collections that have changed. This will facilitate quick recovery of lost items with a minimum of space usage, while the Db backup are available for large-scale disaster recovery.

MOSS review

In computing, the Microsoft SharePoint products and technologies sold by Microsoft includes browser-based collaboration and a document-management platform. It can be used to host web sites that access shared workspaces and documents, as well as specialized applications like wikis and blogs from a browser. SharePoint is not intended to replace a full file server. Instead, it is targeted as a collaborative workspace, a tool for the management and automation of business processes, and a platform for social networking. Microsoft markets this as Collaboration, Processes, and People. SharePoint interface is through a web interface, such as a task list or discussion pane. SharePoint sites are actually ASP.NET 2.0 applications, which are served using IIS and use a SQL Server database as a data storage backend. All site content data is stored within a SQL Server database called WSS_Content.

The term “SharePoint” collectively refers to two products, the platform and the services; this can be very confusing to beginners since it is counter-intuitive. WSS 3.0 is the platform while MOSS 2007 provides additional services. As of 2008 the most current of these two are:

* Windows SharePoint Services 3.0 (WSS)
* Microsoft Office SharePoint Server  (MOSS)

The SharePoint family

Windows SharePoint Services (WSS)

Windows SharePoint Services 3.0 (WSS) comprises a free add-on to Windows Server. It is for the state forms and use ASP.NET pages instead.

Microsoft Office SharePoint Server  (MOSS )

Microsoft Office SharePoint Server 2007 (MOSS) is a package that provides additional functionality on top of WSS 3.0. Live deployment of Microsoft Office SharePoint Server 2007 requires licensing of the server as well as a one time Client Access License (CAL). Microsoft’s licensing changes regularly and you should consult the Microsoft web page or a representive or licensing partner for the latest information. MOSS comes in two levels, standard and enterprise with enterprise costing more per CAL license. The standard version includes out-of-the-box workflows and reporting, additional policy management, auditing and records management for the file sharing capabilities, greater personalization of the sites, as well as further search capabilities including enterprise search and people search. The enterprise edition includes business data search, the Business Data Catalog, InfoPath Form Server and Business Intelligence capabilities such as Excel Services, Report Center and Dashboarding.

Microsoft Search Server

Microsoft Search Server (MSS), an enterprise search platform from Microsoft, builds on the search capabilities of Microsoft Office SharePoint Server.MSS shares its architectural underpinnings with the Windows Search platform for both the querying engine as well as the indexer. MOSS search provides the ability to search metadata attached to documents.

Microsoft has made Microsoft Search Server available as Search Server 2008, released March 2008. A free version, Search Server 2008 Express, is also available. The express edition features the same feature set as the commercial edition, including no limitation on the number of files indexed. However, it is limited to a stand-alone installation and cannot be scaled out to a cluster.

Microsoft Office SharePoint Designer

The WYSIWYG HTML editor Microsoft Office SharePoint Designer primarily targets the design of SharePoint sites and end-user workflows for WSS sites. It is the sucessor to FrontPage 2003. It shares its rendering engine with Microsoft Expression Web, its general web designing sibling, and Microsoft’s Visual Studio 2008 IDE. SPD represents a next-generation Microsoft replacement for Microsoft FrontPage. SPD requires that IIS has Frontpage extensions installed on the server.

PL/SQL Cursor For Loop

Sequence of steps to be  followed to define and use a cursor.

1.         Open the cursor.

2.         Start a loop.

3.         Fetch a row of data.

4.         Check for a new row, exit if not.

5.         Process the data.

6.         End the loop.

7.         Close the cursor.

Since these steps are almost always followed, Oracle provides a way to let PL/SQL perform most of the steps.  This is called the CURSOR FOR loop.  As in the name, it uses a FOR loop to process the cursor.

SQL> declare
2    cursor c8 is
3      select
4        initcap(author_last_name)  l_name,
5        initcap(author_first_name) f_name
6      from author;
7  begin
8    for r_c8 in c8 loop
9      dbms_output.put_line(r_c8.f_name||’ ‘||
10                           r_c8.l_name);
11    end loop;
12  end; /

Mark Jones
Alvis Hester
Erin Weaton
Pierre Jeckle
Lester Withers

The CURSOR FOR is a very efficient cursor.  Lines 2 through 6 define the cursor.  Line 8 handles opening the cursor, fetching the rows, testing for new rows, and exiting when a new row is not returned.  Notice that the record r_c8 is not defined in the declaration section.  The CURSOR FOR automatically defines r_c8 as a c8%rowtype.  The scope for r_c8 is only inside the CURSOR FOR loop.  Likewise, the cursor attributes for cursor c8 are defined only inside the CURSOR FOR loop.

If you define the record r_c8 in the declare section of the PL/SQL block, the variable you defined will go out of scope when the CURSOR FOR defines the record r_c8 and will return in scope when the loop ends.  If there is a requirement to access the cursor attributes from outside the loop, a variable must be defined and the cursor attribute copied into it before the loop ends.  The CURSOR FOR closes the cursor at the END LOOP clause.  Below is an example that calculates the average of total store sales by store, using the cursor %rowcount attribute.

SQL> declare
2    cursor c9 is
3      select sum(quantity) qty
4      from sales group by store_key;
5    sumer   number := 0;
6    counter number := 0;
7  begin
8    for r_c9 in c9 loop
9      sumer := sumer + r_c9.qty;
10      counter := c9%rowcount;
11    end loop;
12    dbms_output.put_line(‘Average is ‘||
13          sumer/counter);
14  exception
15    when others then
16      if c9%isopen then close c9;
17      end if;
18  end; /

Average is 11055

In order to count the number of items summed, the attribute c9%rowcount must be assigned inside the loop at line 10.  Also note that an exception happening inside the loop will jump to the exceptions handler which must ensure that the cursor is properly closed.

The CURSOR FOR can also be passed parameters.  It follows the same rules as the manual cursor in that if the cursor is defined with a parameter, it must be opened with a parameter, or the cursor will raise an exception.

SQL> declare
2    cursor c10(v_avg in number) is
3      select store_name, sum(quantity) qty
4      from store join sales using (store_key)
5      group by store_name
6      having sum(quantity) > v_avg;
7  begin
8    for r_c10 in c10 (11055) loop
9      dbms_output.put_line(initcap(
10          r_c10.store_name)||
‘         ‘||r_c10.qty);
11    end loop;
12  end; /

Borders                             21860
Books For Dummies            13000
Hot Wet And Sexy Books    24700
Wee Bee Books                 13700
Eaton Books                     12120

The variable v_avg is defined as an IN parameter.  This designation is optional as a variable can only be passed as mode IN, and the passed parameter can also be assigned a default value.  If a default value is assigned, then the cursor can be opened without passing a parameter and it will use the defined default value.

Notice in the example below that v_avg is assigned a default value in line 2, and the cursor is opened without passing a parameter in line 8.

SQL> declare
2    cursor c10(v_avg number:= 11055) is
3      select store_name, sum(quantity) qty
4      from store join sales using (store_key)
5      group by store_name
6      having sum(quantity) > v_avg;
7  begin
8    for r_c10 in c10 loop
9      dbms_output.put_line(initcap(
10         r_c10.store_name)||
‘         ‘||r_c10.qty);
11    end loop;
12  end;
13  /

Borders                   21860
Books For Dummies         13000
Hot Wet And Sexy Books    24700
Wee Bee Books             13700
Eaton Books               12120

Another nice feature of the CURSOR FOR is the ability to define the cursor inline.  Below is a cursor that is not declared in the declaration section of the executing block.  The cursor definition is included in the CURSOR FOR.

SQL> begin
2    for r_c11 in
(select author_last_name l_name,
3            author_first_name f_name
4                  from author) loop
5      dbms_output.put_line(initcap(
6        r_c11.l_name||
‘, ‘||r_c11.f_name));
7    end loop;
8  end;
9  /

Jones, Mark
Hester, Alvis
Weaton, Erin
Jeckle, Pierre
Withers, Lester

Defining a cursor inline has the advantage of self documenting the cursor in the code.  Instead of going back to the declare section to determine the cursor definition, the programmer maintaining the code can see the definition right there in the code that calls it.

However, if the cursor is used multiple times, it will be in multiple locations in the code, reducing maintainability.

Another problem with defining the cursor inline is that the cursor will have no defined name.  Therefore you will not be able to access the cursor attributes.  This can be a problem if an exception is raised inside the cursor that jumps out of the loop.

You can get around this problem by wrapping the loop inner statements in a separate block with an exception handler.

begin
for r_c11 in (select author_last_name l_name,
author_first_name f_name
from author) loop
begin
dbms_output.put_line(initcap(r_c11.l_name||
‘, ‘||r_c11.f_name));
– lots of other stuff to do.
exception
when others then
– handle all exceptions
end;
end loop;
end;

The exception handler must deal with all the exceptions without raising new ones unless it terminates the program.  Any exception raised in the exception handler will jump to the end of the loop and leave the cursor open.

Another powerful feature of the CURSOR FOR is the update feature.  This allows the programmer to efficiently update data returned by the cursor.

Oracle System Table Description

ALL_ARGUMENTS : Arguments in object accessible to the user
ALL_CATALOG :  All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS :  Comments on columns of accessible tables and views
ALL_CONSTRAINTS :  Constraint definitions on accessible tables
ALL_CONS_COLUMNS :  Information about accessible columns in constraint definitions
ALL_DB_LINKS  : Database links accessible to the user
ALL_ERRORS :  Current errors on stored objects that user is allowed to create
ALL_INDEXES :  Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS  COLUMNS : comprising INDEXes on accessible TABLES
ALL_LOBS :  Description of LOBs contained in tables accessible to the user
ALL_OBJECTS :  Objects accessible to the user
ALL_OBJECT_TABLES :  Description of all object tables accessible to the user
ALL_SEQUENCES :  Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS :  Snapshots the user can access
ALL_SOURCE :  Current source on stored objects that user is allowed to create
ALL_SYNONYMS :  All synonyms accessible to the user
ALL_TABLES :  Description of relational tables accessible to the user
ALL_TAB_COLUMNS :  Columns of user’s tables, views and clusters
ALL_TAB_COL_STATISTICS :  Columns of user’s tables, views and clusters
ALL_TAB_COMMENTS :  Comments on tables and views accessible to the user
ALL_TRIGGERS :  Triggers accessible to the current user
ALL_TRIGGER_COLS :  Column usage in user’s triggers or in triggers on user’s tables
ALL_TYPES :  Description of types accessible to the user
ALL_UPDATABLE_COLUMNS :  Description of all updatable columns
ALL_USERS :  Information about all users of the database
ALL_VIEWS :  Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL :  Database compatible parameter set via init.ora
DBA_DB_LINKS :  All database links in the database
DBA_ERRORS :  Current errors on all stored objects in the database
DBA_OBJECTS :  All objects in the database
DBA_ROLES :  All Roles which exist in the database
DBA_ROLE_PRIVS :  Roles granted to users and roles
DBA_SOURCE :  Source of all stored objects in the database
DBA_TABLESPACES :  Description of all tablespaces
DBA_TAB_PRIVS :  All grants on objects in the database
DBA_TRIGGERS :  All triggers in the database
DBA_TS_QUOTAS :  Tablespace quotas for all users
DBA_USERS :  Information about all users of the database
DBA_VIEWS :  Description of all views in the database
DICTIONARY :  Description of data dictionary tables and views
DICT_COLUMNS :  Description of columns in data dictionary tables and views
GLOBAL_NAME :  global database name
NLS_DATABASE_PARAMETERS :  Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS  NLS parameters of the instance
NLS_SESSION_PARAMETERS :  NLS parameters of the user session
PRODUCT_COMPONENT_VERSION :  version and status information for component products
ROLE_TAB_PRIVS :  Table privileges granted to roles
SESSION_PRIVS :  Privileges which the user currently has set
SESSION_ROLES :  Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP :  Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES :  Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP :  Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

Java Overview

Java is a programming language originally developed by Sun Microsystems and released in 1995 as a core component of Sun Microsystems’ Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities. Java applications are typically compiled to bytecode that can run on any Java virtual machine (JVM) regardless of computer architecture.

The original and reference implementation Java compilers, virtual machines, and class libraries were developed by Sun from 1995. As of May 2007, in compliance with the specifications of the Java Community Process, Sun made available most of their Java technologies as free software under the GNU General Public License. Others have also developed alternative implementations of these Sun technologies, such as the GNU Compiler for Java and GNU Classpath.

Java Platform

One characteristic of Java is portability, which means that computer programs written in the Java language must run similarly on any supported hardware/operating-system platform. One should be able to write a program once, compile it once, and run it anywhere.

This is achieved by compiling the Java language code, not to machine code but to Java bytecode – instructions analogous to machine code but intended to be interpreted by a virtual machine (VM) written specifically for the host hardware. End-users commonly use a JRE installed on their own machine, or in a Web browser.

Standardized libraries provide a generic way to access host specific features such as graphics, threading and networking. In some JVM versions, bytecode can be compiled to native code, either before or during program execution, resulting in faster execution.

A major benefit of using bytecode is Porting. However, the overhead of interpretation means that interpreted programs almost always run more slowly than programs compiled to native executables would, and Java suffered a reputation for poor performance. This gap has been narrowed by a number of optimisation techniques introduced in the more recent JVM implementations.

One such technique, known as just-in-time compilation (JIT), translates Java bytecode into native code the first time that code is executed, then caches it. This results in a program that starts and executes faster than pure interpreted code can, at the cost of introducing occasional compilation overhead during execution. More sophisticated VMs also use dynamic recompilation, in which the VM analyzes the behavior of the running program and selectively recompiles and optimizes parts of the program. Dynamic recompilation can achieve optimizations superior to static compilation because the dynamic compiler can base optimizations on knowledge about the runtime environment and the set of loaded classes, and can identify hot spots – parts of the program, often inner loops, that take up the most execution time. JIT compilation and dynamic recompilation allow Java programs to approach the speed of native code without losing portability.

Another technique, commonly known as static compilation, or ahead-of-time (AOT) compilation, is to compile directly into native code like a more traditional compiler. Static Java compilers translate the Java source or bytecode to native object code. This achieves good performance compared to interpretation, at the expense of portability; the output of these compilers can only be run on a single architecture. AOT could give Java something like performance, yet it is still not portable since there are no compiler directives, and all the pointers are indirect with no way to micro manage garbage collection.

Java’s performance has improved substantially since the early versions, and performance of JIT compilers relative to native compilers has in some tests been shown to be quite similar. The performance of the compilers does not necessarily indicate the performance of the compiled code; only careful testing can reveal the true performance issues in any system.

One of the unique advantages of the concept of a runtime engine is that errors (exceptions) should not ‘crash’ the system. Moreover, in runtime engine environments such as Java there exist tools that attach to the runtime engine and every time that an exception of interest occurs they record debugging information that existed in memory at the time the exception was thrown (stack and heap values). These Automated Exception Handling tools provide ‘root-cause’ information for exceptions in Java programs that run in production, testing or development environments.

Oracle Overview

Physical and logical structuring in Oracle

An Oracle database system — identified by an alphanumeric system identifier or SID — comprises at least one instance of the application, along with data storage. An instance — identified persistently by an instantiation number (or activation id: SYS.V_$DATABASE.ACTIVATION#) — comprises a set of operating-system processes and memory-structures that interact with the storage. Typical processes include PMON (the process monitor) and SMON (the system monitor).

Users of Oracle databases refer to the server-side memory-structure as the SGA (System Global Area). The SGA typically holds cache information such as data-buffers, SQL commands and user information. In addition to storage, the database consists of online redo logs (which hold transactional history). Processes can in turn archive the online redo logs into archive logs (offline redo logs), which provide the basis (if necessary) for data recovery and for some forms of data replication.

The Oracle RDBMS stores data logically in the form of tablespaces and physically in the form of data files. Tablespaces can contain various types of memory segments; for example, Data Segments, Index Segments etc. Segments in turn comprise one or more extents. Extents comprise groups of contiguous data blocks. Data blocks form the basic units of data storage. At the physical level, data-files comprise one or more data blocks, where the block size can vary between data-files.

Oracle database management tracks its computer data storage with the help of information stored in the SYSTEM tablespace. The SYSTEM tablespace contains the data dictionary — and often (by default) indexes and clusters. (A data dictionary consists of a special collection of tables that contains information about all user-objects in the database). Since version 8i, the Oracle RDBMS also supports “locally managed” tablespaces which can store space management information in bitmaps in their own headers rather than in the SYSTEM tablespace (as happens with the default “dictionary-managed” tablespaces).

If the Oracle database administrator has instituted Oracle RAC (Real Application Clusters), then multiple instances, usually on different servers, attach to a central storage array. This scenario offers numerous advantages, most importantly performance, scalability and redundancy. However, support becomes more complex, and many sites do not use RAC. In version 10g, grid computing has introduced shared resources where an instance can use (for example) CPU resources from another node (computer) in the grid.

The Oracle DBMS can store and execute stored procedures and functions within itself. PL/SQL (Oracle Corporation’s proprietary procedural extension to SQL), or the object-oriented language Java can invoke such code objects and/or provide the programming structures for writing them.

Database schema

Oracle database conventions refer to defined groups of ownership (generally associated with a “username”) as schemas.

Most Oracle database installations traditionally come with a default schema called SCOTT. After the installation process has set up the sample tables, the user can log into the database with the username scott and the password tiger. The name of the SCOTT schema originated with Bruce Scott, one of the first employees at Oracle (then Software Development Laboratories), who had a cat named Tiger.

The SCOTT schema has seen less use as it uses few of the features of the more recent releases of Oracle. Most recent examples reference the default HR or OE schemas.
Other default schemas include:

* SYS (essential core database structures and utilities)
* SYSTEM (additional core database structures and utilities, and privileged account)
* OUTLN (utilized to store metadata for stored outlines for stable query-optimizer execution plans.
* BI, IX, HR, OE, PM, and SH (expanded sample schemas containing more data and structures than the older SCOTT schema)

Memory architecture : System Global Area

Each Oracle instance uses a System Global Area or SGA — a shared-memory area — to store its data and control-information.

Each Oracle instance allocates itself an SGA when it starts and de-allocates it at shut-down time. The information in the SGA consists of the following elements, each of which has a fixed size, established at instance startup:

• The database buffer cache: this stores the most recently-used data blocks. These blocks can contain modified data not yet written to disk (sometimes known as “dirty blocks”), unmodified blocks, or blocks written to disk since modification (sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most-recently-used algorithm, the most active buffers stay in memory to reduce I/O and to improve performance.

• The redo log buffer: this stores redo entries — a log of changes made to the database. The instance writes redo log buffers to the redo log as quickly and efficiently as possible. The redo log aids in instance recovery in the event of a system failure.

• The shared pool: this area of the SGA stores shared-memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. An insufficient amount of memory allocated to the shared pool can cause performance degradation.

Library cache:

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement.

If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

Data dictionary cache:

The data dictionary comprises a set of tables and views that map the structure of the database.

Oracle databases store information here about the logical and physical structure of the database. The data dictionary contains information such as:

• * user information, such as user privileges

• * integrity constraints defined for tables in the database

• * names and datatypes of all columns in database tables

• * information on space allocated and used for schema objects

The Oracle instance frequently accesses the data dictionary in order to parse SQL statements. The operation of Oracle depends on ready access to the data dictionary: performance bottlenecks in the data dictionary affect all Oracle users. Because of this, database administrators should make sure that the data dictionary cache has sufficient capacity to cache this data. Without enough memory for the data-dictionary cache, users see a severe performance degradation. Allocating sufficient memory to the shared pool where the data dictionary cache resides precludes these particular performance problems. But after the things u have to restart your database and after the restarted the data base. go the system

Program Global Area

The Program Global Area or PGA memory-area contains data and control-information for Oracle’s server-processes.

The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:

• stack-space: the memory that holds the session’s variables, arrays, and so on.

• session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)

• private SQL-area: an area in the PGA which holds information such as bind-variables and runtime-buffers.

• sorting area: an area in the PGA which holds information on sorts, hash-joins, etc.

Process architecture : Oracle processes

The Oracle RDBMS typically relies on a group of processes running simultaneously in the background and interacting to further and monitor database operations. Such processes (and their standard abbreviations) can include:

• archiver processes (ARCn)

• checkpoint process (CKPT)

• coordinator-of-job-queues process (CJQn): dynamically spawns slave processes for job-queues

• * database writer processes (DBWn)

• dispatcher processes (Dnnn): multiplex server-processes on behalf of users

• memory-manager process (MMAN): used for internal database tasks such as Automatic Shared Memory Management

• log-writer process (LGWR)

• * log-write network-server (LNSn): transmits redo logs in Data Guard environments

• logical standby coordinator process (LSP0): controls Data Guard log-application

• media-recovery process (MRP): detached recovery-server process

• memory-monitor process (MMON)

• memory-monitor light process (MMNL): gathers and stores Automatic Workload Repository (AWR) data

• process-monitor process (PMON)

• process-spawner (PSP0): spawns Oracle processes

• queue-monitor processes (QMNn)

• recoverer process (RECO)

• remote file-server process (RFS)

• shared server processes (Snnn): serve client-requests

• system monitor process (SMON)

User processes, connections and sessions:

Oracle Database terminology distinguishes different computer-science terms in describing how end-users interact with the database:

• user processes involve the invocation of application software

• a connection refers to the pathway linking a user process to an Oracle instance

• sessions consist of specific connections to an Oracle instance. Each session within an instance has a session identifier or “SID”