001 Exploring the Database Architecture Flashcards
An oracle server consists of the following two entities
the instance and the database
The instance consists of
memory structures and processes
The database consists of
files on disk
During the creation process
the instance is created
During the startup process
first the instance is started and then the database is opened
The logical structures (e.g. tables) are … related to physical structures (datafiles)
not directly
The processes that make up the instance are known as
background processes
The memmory structures| which are implemented in shared memory segments| are known as the
SGA (system global area)
User sessions consist of
a user process running locally to the user machine connecting to a server process
User sessions are referred to as
foreground processes
Each server process is associated with
an area of non-shareable memory| the PGA (program global area)
The PGA is … to the session
private
The shared pool consists of
Library Cache|Data Dictionary cache|PL/SQL area|SQL query and PL/SQL function result caches
The size of the shared pool buffer
is dynamic and can be automatically managed
The size of the log buffer
is static| fixed and cannot be automatically managed
The library cache stores
recently executed code in its parsed form
The data dictionary cache stores the following recently used object definitions
descriptions of tables| indexes| users| and other metadata definitions
Are object definitions in the SGA available for all sessions?
yes
Stored PL/SQL objects are
procedures| functions| packaged procedures and functions| object type definitions| and triggers
Explain the disadvantages of anonymous PL/SQL
is issued from user processes| cannot be cached but must be compiled dynamically| worse performance
The SQL Query and PL/SQL Function Result Cache …
stores the results of queries and invalidates the results on table updates
Problems of undersized shared pools
parsed statements are not cached long enough and have to be parsed again| there is a minimum size
Problems of oversized shared pools
It takes too long to search it
Algorithm for allocating memory in the shared pool
LRU (Least recently used)
