Oracle 11g Chapter 2

DML examples

Select, insert, update, delete, merge

What Command Should You Use for Memory Suggestions

V$MEMORY_TARGET_ADVICE

DDL Examples

Create, Comment Alter, Drop, rename, truncate

How many databases can an instance access at a time

One Database, after you start an instance you must mount it to a database

PMON

PMON aka PMOM is an oracle background process that cleans up after you like your mom. It manages the system server processes. It cleans up failed processes by releasing resources and rolling back uncommitted data

Component 2 of the Oracle Database

Processes-Oracle processes only exist when the instance is running. Some are core mandatory processes others are optional depending on what features are enabled. You can check by viewing OS process listing

True or False An instance can exist without a database.

True You first must start an Oracle instance; you create the database from within the instance. An Oracle feature called Automatic Storage Management uses an instance but isn't associated with a database.

This buffer prevents the loss o dirty blocks

redo log buffer-records every SQL statement that changes data. The statement itself and any information required to reconstruct it is called a redo entry. Redo entries hang out here temporarily before being recorded on disk. This buffer protects against the loss of dirty blocks.

Define Clustering

the basis of Oracles Real App Clusters feature in which many instances on several severs are accessing one central database enabling scalability and high availability.

What is the formula for calculating target memory usage in oracle

(GB of memory × percent in decimal form) / Number of Eventual Databases = GB for MEMORY_TARGET per database

Instance vs Database

An Instance is the combination of memory and processes that are part of a running installation. A database is the physical component or the files. You might hear people use the term database instance to refer to the entire running database

3 Ways to manage memory in Oracle

Automatic, Manually, and combination of both

The Cousin of LRU

Buffer Cache -controls what blocks get to stay depending on available space and the block state (similar to how the shared pool decides what SQL gets to stay). The buffer cache uses its own version of the LRU algorithm.

Transaction Control Language (TCL) commands

Commit Rollback, Savepoint

Buffer=

Database Block-minimum amount of storage that Oracle reads or writes. All storage segments that contain data are made up of blocks. When you request data from disk, at minimum Oracle reads one block. Even if you request only one row, many rows in the same table are likely to be retrieve

Component 3 of oracle

Files & Structures-Once the database is created these database files always exist. Some of these files include essentials to run the database, some are things you created. These structures include tables, indexes and programs

The Data Control Language (DCL) commands

Grant, Revoke

What are the four main areas of a shared pool

Library Cache- a library of ready to go SQL statements thanks to parsing Dictionary Cache-a collection of information about you and the database' objects. It can check background-type information.. it also uses Least Recently Used (LRU) algorithm: If it's not the right size, information can be evicted. SQL result cache: This cache lets Oracle see that the requested data -requested by a recently executed SQL statement- might be stored in memory. This lets Oracle skip the execution part of the, er, execution,for lack of a better term, and go directly to the result PL/SQL function result cache The PL/SQL function result cache stores the results of a computation. (Eg. value of dollar based on Euro Exchange Rate)

What tool or parameter do you use to manage memory in Oracle

MEMORY_TARGET show parameter memory_target

What is component one of the Oracle Database

Memory- inhabits the RAM and only exist when software is running. They're activated when u start an Instance. Some memory structures are essential others r optional. Some are modifiable others are static.

Two main components of PGA

Session memory -Login information - Information such as settings specific to a session (for example, what format to use when dates are displayed) Private SQL area • Variables that might be assigned values during SQL execution • Work areas for processing specific SQL needs: sorting, hash-joins, bitmap operations • Cursors

Name the 5 Key SGA components

Shared Pool, Database Buffer Cache, Redo Log buffer, Large Pool, Java Pool- You can modify each parameter individually for optimum tuning

What is SMON responsible for

Smon is an oracle background process that is primarily responsible for instance recovery. If the database crashes and redo information must be read and applied, smon takes care of it. It also cleans and releases temporary space

This group of shared memory structures contains things like data and SQL It is shared between both Oracle background . processes and server processes.

System Global Area

What is the largest component of the SGA and what is its job?

The Database Buffer Cache-This cache has data that comes from the files on disk. Because accessing data from disk is slower than from memory, the database buffer cache's sole purpose is to cache the data in memory for quicker access

What triggers a block write and therefore a dirty block?

The database is issued a shutdown command -A full or partial checkpoint occurs — that's when the system periodically dumps all the dirty buffers to disk -A free block is needed and none are found after a given amount of searching -Certain data definition language (DDL) commands. (DDL commands are SQL statements that define objects in a database.

Distinguish between Java and Large Pool

The large pool relieves the shared pool of sometimes-transient memory requirements. Large pool is used by Oracle Recovery Manager, Oracle Shared Server, Parallel Processing. I/O related server Process Java Pool-an optional memory component; the database ships with its own Java Virtual Machine (JVM), which can execute Java code out of the SGA

PGA

contains information used for private or session-related information that individual users need.

When do RLBs get flsushed

✓ Every time there's a commit to data in the database ✓ Every three seconds ✓ When the redo buffer is 1⁄3 full ✓ Just before each dirty block is written to disk

What are the three states a A block in the buffer cache can be in?

✓ Free: Not currently being used for anything (point sys) ✓ Pinned: Currently being accessed ✓ Dirty: Block has been modified, but not yet written to disk (gambling)