001 Exploring the Database Architecture

Question 1

An oracle server consists of the following two entities

Answer 1

the instance and the database

Question 2

The instance consists of

Answer 2

memory structures and processes

Question 3

The database consists of

Answer 3

files on disk

Question 4

During the creation process

Answer 4

the instance is created

Question 5

During the startup process

Answer 5

first the instance is started and then the database is opened

Question 6

The logical structures (e.g. tables) are … related to physical structures (datafiles)

Answer 6

not directly

Question 7

The processes that make up the instance are known as

Answer 7

background processes

Question 8

The memmory structures| which are implemented in shared memory segments| are known as the

Answer 8

SGA (system global area)

Question 9

User sessions consist of

Answer 9

a user process running locally to the user machine connecting to a server process

Question 10

User sessions are referred to as

Answer 10

foreground processes

Question 11

Each server process is associated with

Answer 11

an area of non-shareable memory| the PGA (program global area)

Question 12

The PGA is … to the session

Answer 12

private

Question 13

The shared pool consists of

Answer 13

Library Cache|Data Dictionary cache|PL/SQL area|SQL query and PL/SQL function result caches

Question 14

The size of the shared pool buffer

Answer 14

is dynamic and can be automatically managed

Question 15

The size of the log buffer

Answer 15

is static| fixed and cannot be automatically managed

Question 16

The library cache stores

Answer 16

recently executed code in its parsed form

Question 17

The data dictionary cache stores the following recently used object definitions

Answer 17

descriptions of tables| indexes| users| and other metadata definitions

Question 18

Are object definitions in the SGA available for all sessions?

Answer 18

yes

Question 19

Stored PL/SQL objects are

Answer 19

procedures| functions| packaged procedures and functions| object type definitions| and triggers

Question 20

Explain the disadvantages of anonymous PL/SQL

Answer 20

is issued from user processes| cannot be cached but must be compiled dynamically| worse performance

Question 21

The SQL Query and PL/SQL Function Result Cache …

Answer 21

stores the results of queries and invalidates the results on table updates

Question 22

Problems of undersized shared pools

Answer 22

parsed statements are not cached long enough and have to be parsed again| there is a minimum size

Question 23

Problems of oversized shared pools

Answer 23

It takes too long to search it

Question 24

Algorithm for allocating memory in the shared pool

Answer 24

LRU (Least recently used)

Question 25

The shared pool is allocated at

Answer 25

instance startup time

Question 26

The resizing of the shared pool can be done

Answer 26

during runtime either manual or automatic

Question 27

The large pool is

Answer 27

optional| used by shared server processes | parallel execution servers

Question 28

If the large pool doesn’t exist| the

Answer 28

processes use shared pool instead

Question 29

The size of the large pool

Answer 29

is dynamic and can be automatically managed

Question 30

The size of the Java pool

Answer 30

is dynamic and can be automatically managed

Question 31

The resizing of the large pool can be done

Answer 31

during runtime either manual or automatic

Question 32

The resizing of the Java pool can be done

Answer 32

during runtime either manual or automatic

Question 33

The size of the Streams pool

Answer 33

is dynamic and can be automatically managed

Question 34

The resizing of the Streams pool can be done

Answer 34

during runtime either manual or automatic

Question 35

Connect to the database as system

Answer 35

sqlplus / as system

Question 36

Show the current| maximum| and minimum sizes of the SGA components that can be dynamically resized

Answer 36

select COMPONENT|CURRENT_SIZE|MIN_SIZE|MAX_SIZE from v$sga_dynamic_components|

Question 37

Determine how much memory has been and is currently allocated to program global area

Answer 37

select name|value from v$pgastat where name in (‘maximum PGA allocated’|’total PGA allocated’)|