Oracle Architectural Components

• Identify the main components of the Oracle system global area. What are the subcomponents of each, and what are their roles?

SOLUTION: The System Global Area, or SGA, consists of several different items: the buffer cache, shared pool, and redo log buffer, as well as a few other items that will be discussed later in the unit. The following subtopics explain the primary components of the Oracle SGA. The buffer cache memory structure consists of buffers the size of database blocks that store data needed by SQL statements issued in user processes. A database block is the most granular unit of information storage in Oracle, in which Oracle can place several rows of table data. The buffer cache has two purposes: to improve performance for subsequent repeated select statements on the same data, and to allow Oracle users to make data changes quickly in memory. Oracle writes those data changes to disk later. The shared pool has two mandatory structures and one optional structure in the Oracle shared pool. The first required component is the library cache, used for storing parsed SQL statement text and the statement’s execution plan for reuse. The second is the dictionary cache, sometimes also referred to as the row cache, which is used for storing recently accessed information from the Oracle data dictionary, such as table and column definitions, usernames, passwords, and privileges. (If you don’t know what the data dictionary is, you’ll find more out about it later in this chapter.) These two components are designed to improve overall Oracle performance in multiuser environments. The optional shared pool structure contains session information about user processes connected to Oracle. The redo buffer temporarily stores in memory the redo entry information generated by DML statements run in user sessions until Oracle writes the information to disk. DML statements include update, delete, and insert statements run by users. What is a redo entry? It is a small amount of information produced and saved by Oracle to reconstruct, or redo, changes made to the database by insert, update, delete, create, alter, and drop statements. If some sort of failure occurred, the DBA can use redo information to recover the Oracle database to the point of database failure.

• Describe the role of the DBW0 process

SOLUTION: Called the database writer process, the DBWR background process writes dirty data blocks from buffer cache to disk. The writes are done when the server process needs to make room in the buffer cache to read more data in for user processes, when DBWR is told to write data to disk by the LGWR process, or every three seconds due to a timeout. The event that causes LGWR to tell DBWR to write to disk is called a checkpoint. You will learn more about checkpoints in Chapter 7. Because Oracle8i allows multiple database writers to run on the host machine, the DBWR process is sometimes referred to as DBW0, where 0 can be any digit between 0 and 9, representing one of several DBWR processes running in Oracle.

• Describe the role of the LGWR process

SOLUTION: Called the log writer process, the LGWR background process writes redo log entries from the redo log buffer to online redo log files on disk. LGWR has some other specialized functions related to the management of redo information that you will learn about in Chapter 7. LGWR also tells DBWR to write dirty buffers to disk at checkpoints, as mentioned earlier.

4. What are the filesystem components of an Oracle database?

SOLUTION: Oracle disk resources are broken into two categories: physical and logical. Oracle physical disk resources include control files, datafiles,and redo log files. Logical disk resources include tablespaces, segments, and extents. Two additional resources are the init.ora file and the password file.

4. Why does the Oracle database need two online redo logs

Solution: LGWR writes to redo logs in a sequential manner such that, when one fills, LGWR starts writing to the next one. When the last online log fills, LGWR overwrites the contents of the first and starts the process anew. When redo logs are being archived, ARC0 archives the filled log while LGWR writes new redo to the other.

4. What purpose does the control file serve in the database?

SOLUTION: the control file identifies the filesystem location for all Oracle-related database files and online redo logs. When Oracle attempts to open the database, it first looks in the control file to figure out where all of its database file components are. Without the control file, Oracle wouldn’t know where to find its files!

4. How does Oracle know where to find the control file when the instance starts?

SOLUTION: The location of the control file is passed to Oracle in the init.ora file via the CONTROL_FILES parameter.

4. What purpose does a password file serve?

SOLUTION: A password file is used to secure the DBA’s ability to login from machines other than the one hosting the Oracle database and perform privileged operations such as startup or shutdown. This file is not required on your system unless you want to perform remote database administration.   Practice for Section

1. Describe the purpose of the tnsnames.ora file. Find this file on a machine containing the Oracle client software. Review the contents of the tnsnames.ora file you located.

SOLUTION: tnsnames.ora is used for resolving an Oracle connect string to determine where to find the host machine and other vital information required for connecting remotely to an Oracle database. This file is usually found in the network/admin directory under the Oracle software home directory.

1. Open your Oracle8i generic documentation on the Oracle software distribution CD-ROM. In it, review the Oracle8i Error Messages and the Net8 Administrators Guide documents to identify the resolutions to the following error messages:
   1. ORA-3113
   2. TNS-03505
   3. TNS-12203
   4. TNS-12224 or TNS-12541
   5. TNS-12500

SOLUTION: ORA-3113 means unexpected end of file on communications channel. This means that you have been disconnected from Oracle unexpectedly. There could be many causes for this error. A network process may have failed, or the Oracle server itself may have failed. To resolve the issue, you must try to reconnect to the database. Your attempt to do so may result in another failure, which will most likely tell you more about the root cause of the problem. TNS-03505 means TNS could not resolve service name. This means that Net8 couldn’t resolve your connect string supplied for login. The connect string may have been mistyped, or it may not exist in your local copy of tnsnames.ora. If you are using Oracle Names, the connect string may not have a corresponding connect descriptor in the Names server. TNS-12203 TNS means was unable to connect to destination. The name of the machine hosting Oracle configured for the connect descriptor is most likely incorrect. Check it out in your tnsnames.ora file or in the Oracle Names server, and correct the hostname. Sometimes, you get this problem in TCP/IP when the domain-names service used to translate hostnames to IP addresses doesn’t contain the hostname specified, or when there is no DNS server for your network. In this case, you can usually specify the IP address rather than the hostname in your connect descriptor to fix the problem. TNS-12224 or TNS-12541 means TNS found no listener on the host. Check to make sure the listener is running. If not, then start it. If the listener is running, ensure that your listener is tuned into port 1521 or that the port in your connect descriptor in tnsnames.ora or Oracle Names matches the port your listener is tuned to. TNS-12500 means the TNS listener failed to start a dedicated server process. The SID_LIST configured for the listener process in listener.ora may not contain the right information for the Oracle SID on the machine hosting the listener and the Oracle database. SID_LIST is a list of Oracle SIDs for which the listener monitors user connection requests. Alternatively, there may be an error in the connect descriptor in tnsnames.ora or in Oracle Names for this connect string.

3. Describe how user processes get connected to a server when dedicated servers are used. What is the difference between prespawned dedicated servers and shared servers?

SOLUTION: User process first must resolve connect string given in SQL*Plus or other Oracle client into a connection description, which in turn contains host machine and Oracle database to connect to. This information is then passed onto the network where it is heard by the listener process. The listener responds to the Oracle client by either spawning a new dedicated server process for the client to use, or by assigning the user to a prespawned dedicated server. Although prespawned dedicated servers and shared servers are the same in that both are running on the host system before the user tried to connect, they are different because prespawned dedicated servers still handle work for only one user, while shared servers handle work for many users.

3. Describe how user processes get connected to a server when MTS is used.

SOLUTION: User process first must resolve connect string given in SQL*Plus or other Oracle client into a connection description, which in turn contains host machine and Oracle database to connect to. This information is then passed onto the network where it is heard by the listener process. The listener responds by passing the user process request to a dispatcher. The dispatcher then assigns the user to a shared server according to which shared server has the lightest load.

3. (BONUS) Can you identify any potential issues with performance implications related to the use of shared versus dedicated servers? (HINT: Where might bottlenecks lie in each architecture?)

SOLUTION: First, shared servers may be overloaded due to a limited number of shared servers running on the machine, thus reducing overall performance because users have to wait for the shared server to give their process some attention. Another performance bottleneck in dedicated server architecture is that too many users connected to the machine could cause the host machine to run out of real memory. This degrades performance for all users because database SGA will get swapped to virtual memory.  

• Enable the AUTOTRACE feature in SQL*Plus as follows.

1. Move to the $ORACLE_HOME/sqlplus/admin directory on your machine.
2. Run SQL*Plus from the command line in that directory as the SYS or INTERNAL user.
3. Run the plustrce.sql script by issuing "@plustrce.sql" at the SQL prompt.
4. Log out of SQL*Plus.
5. Change directories to $ORACLE_HOME/rdbms/admin
6. Log back into SQL*Plus as another user to which the DBA role is granted.
7. Run the utlxplan.sql script by issuing "@utlexplan.sql" at the SQL prompt
8. Issue the SET AUTOTRACE ON command at the SQL prompt.

SOLUTION: The net results for this set of steps is that users can enable the AUTOTRACE feature in SQL*Plus.

• Issue the SELECT * FROM USER_TABLES command in SQL*Plus. Then review the contents of the statement execution plan to get an understanding of the results of the RDBMS parsing operations happening behind the scenes.

SOLUTION: The output from this command that users will likely care about is shown below. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS (OUTER) 4 3 NESTED LOOPS 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 6 5 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE) 7 4 TABLE ACCESS (CLUSTER) OF 'TAB$' 8 7 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE) 9 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 11 2 TABLE ACCESS (CLUSTER) OF 'SEG$' 12 11 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 13 1 TABLE ACCESS (CLUSTER) OF 'TS$' 14 13 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 171 consistent gets 0 physical reads 0 redo size 4281 bytes sent via SQL*Net to client 661 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 9 rows processed  

• Now, review the contents from the statistics listing. Identify which of the following items are shown as output in this context:

1. Redo entry size
2. Network roundtrips
3. Time it took the listener to connect the user with a server
4. Frequency of DBW0 write activity
5. BONUS: For those items you could not find in the statistical output for AUTOTRACE, determine where you can find that information. (HINT – issue SELECT NAME FROM DICT WHERE SUBSTR(‘TABLE_NAME’,1,1) = ‘V’)

SOLUTION: You can find both redo entry size and network roundtrips in the statistics given by AUTOTRACE. You cannot find information about the time it took for the listener to connect a user with the server or the frequency of DBW0 write activity behind the scenes. Information about are captured as statistics in the V$SYSSTAT performance view, where the contents of the name column are prefixed with DBWR. For information about connecting users with servers, you can find that for MTS environments in the V$DISPATCHER_RATE view.

• Determine where you can find information about rollback segments on your database. (HINT – issue SELECT NAME FROM DICT WHERE SUBSTR(‘TABLE_NAME’,1,1) = ‘V’). List all associated views.

SOLUTION: V$ROLLNAME V$ROLLSTAT DBA_ROLLBACK_SEGS

• Determine where you can find information about locks in your database. (HINT – issue SELECT NAME FROM DICT WHERE SUBSTR(‘TABLE_NAME’,1,1) = ‘V’). List all associated views.

SOLUTION: V$LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT