Do you know Create DB first

1
Do you know Create DB first

• Oracle Server Architecture and its main
  components(Memory Structure)
• Oracle Instance , SGA , BK Pro
• How to write SQL Statement ?
• What is index ? Types of joins ? Cluster
• What are dynamic view ? Partioning
• What are segments ?
• Files
• DBMS_UTILITY package
• By .. Satish Shrikhande

2

• An instance Which is basically a set of memory
  structures and background processes that cache
  the most used data, and serve as an exchange
  point between the users(clients) and the
  datafiles that have the information on disks.
• Datafiles These datafiles are located on hard
  disks, and they compose all the data that is
  stored in Oracle,control files,tablespaces,
  redo-log files,etc.
• Oracle-Software This contains the Oracle-kernel
  which is much like a sub-operating system on top
  of the host system , because it coordinates the
  memory-structures and processes (Instance) and
  the file updates (Datafiles) on the hard drives.

3

• 1 SGA
• gtDatabase Buffer Cache(depends on DB_BLOCK_SIZE
  DB_BLOCK_BUFFERS parameters)
• gtRedo Log Buffer (depends on LOG_BUFFER ,
  monitored VSYSSTAT )
• gtShared Pool (depends on SHARED_POOL_SIZE )
• gtgtLibrary Cache
• .Shared SQL Areas (Contains the parse
  tree and execution plan for a single SQL
  statement)
• .Private SQL Areas (Contains data
  such as bind information and runtime buffers,each
  session that issues a SQL statement has a private
  SQL areamany private SQL areas can be associated
  with the same shared SQL area, if a session is
  connected via a dedicated server private SQL
  areas are in the users PGA. If the sessions
  connected via the multi-threaded server, the
  private area is kept in the SGA )
• ..Persistent (Contains
  bind information that persists accorss
  executions,code for data type conversion and
  other state information, in contrast to
  Runtime,the persistent area remains waiting after
  the statement completes, the open cursors that
  will not be used should be closed to free the
  persistent area and to minimize the amount of
  memory required for the application)
• ..Runtime ( Contains
  information used while the SQL statement is being
  executed, it is the first step in an execute
  REQUEST, it releases this memory when the
  statement finishes)
• gtgtDictionary Cache (Also known as the row
  cache, because it holds data as rows instead of
  buffers)
• gtgtControl Structures
• gtLarge Pool (Optional,used for large memory
  allocations) MTS- to allocate uga in sga
• 2.. PGA
  backup
  for rman disk I/o buffers
• A PGA (Process Global area ) is a memory region
  containing data and control information for a
  single process. One PGA is allocate for each
  server process.
• Stack Space A PGA always contains stack space,
  which is memory allocate to hold a sessions
  variables,arrays, and other information.
• Session Information If the instance is in
  single-serer mode, the PGA also contains
  information about the users session, such a
  private SQL areas. If the instance is in
  multi-threaded server, this session information
  is not in the PGA, but is instead allocated in
  the SGA. The PGA's initial size is fixed and OS
  specific, however the parameters OPEN_LINKS and
  DB_FILES affect the sizes of PGAs.
• Sort Areas Use memory from the PGA of the
  Oracle server process that performs the sort on
  behalf of the user process. However, a part of
  the sort area ( up to SORT_AREA_RETAINED) exists
  in the runtime area of the process's private SQL
  area. The sort area can grow up to limit
  SORT_AREA_SIZE.

4

• Background Processes
• 1. DBWR (Writes the modified (dirty) buffers in
  the database buffer cache to disk)
• 2. LGWR (Writes the redo log buffer contents to a
  redo log file on disk, it write of the buffer to
  disk when A user process commits a transaction,
  every three seconds, when the redo-log buffer is
  one-third full, when a DBWn process writes
  modified buffers to disk)
• 3. PMON (Process Monitor, performs process
  recovery when a user processes fails, it is
  responsible for cleaning up the database buffer
  cache and freeing resources that the user process
  was using )
• 4. SMON (System Monitor, Performs crash recovery
  , if necessary at instance startup, it is also
  responsible for coalescing contiguous free space
  within table spaces, and cleaning up temporary
  segments that are no longer in use)
• 5. ARCH Copies online redo log files to a
  designated storage device once they become full
  or when the ALTER SYSTEM SWITCH LOGFILE command
  forces a log switch.
• 6. CKPT (when a checkpoint occurs , Oracle must
  update the headers of all data files to record
  the details of the checkpoint.)
• 7. RECO Used with distributed database
  configurations to automatically resolve failed
  distributed transactions
• 8. SNPn ( Job Queue/Snapshot Process) With the
  distributed database configuration, up to 36 job
  queues can automatically refresh table snapshots.
  
• 9. LCKn In Oracle Parallel Server, a lock
  process provides inter-instance locking
• 10. QMN ( Queue Monitor) (Optional for Oracle
  Advanced Queuing which monitors the message
  queues)
• 11. Snn (shared Server Processes serve multiple
  client requests in the multi-threaded server
  configuration )
• 12. Dnn (Dispatcher Processes, support
  multi-threaded configuration by allowing user
  processes to share limited number of server
  processes.
• 13. Pnnn (Parallel query processes)

5

• List the Stages in processingqueries,DML
  statements, COMMITS.
• Queries The server session hashes de SQL
  statement passed to it and compares that hash
  number with the hash numbers of statements
  already saved in the Shared SQL area. If an exact
  duplicate of the statement is found in the shared
  pool, the parsed form of the statement and the
  execution plan that are already stored are used.
  If a match is not found in the shared pool, the
  server session parses de statement.
• Next, the server checks to see wheter the data
  blocks necessary to complete the transaction
  (query) are already stored in the database buffer
  cache. If the block are not in the buffer, the
  server reads the necessary blocks from the data
  files and copies them into the cache. NOW it
  returns the information.
• DML Statements It performs the same steps up to
  when the data is copied into the buffer (data
  block buffer cache), before it returns the
  information (as is the case with a query),the
  blocks in memory (the cache) are modified
  accordingly. Once they are modified in memory
  these blocks are marked as dirty, and are placed
  on the dirty list. Redo log information is also
  generated on this transaction and placed in the
  redo-log cache. Up to this point any of the
  following can occur
• 1 The user commits, this signals the LGWR to
  flush the redo-log buffer to the online redo-log
  file.
• 2 The redo information that was generated causes
  de redo-log buffer to become one-third full. This
  triggers a redo-log buffer flush by LGRW
• 3 The number of dirty blocks reached a threshold
  length. This triggers DBWR to flush all the dirty
  blocks in the database buffer cache to the data
  files, which in turn also causes LGWR to flush
  the redo-log buffers to the online-redo log
  files.
• 4 A database checkpoint occurs. This triggers the
  database buffer cache ( with DBWR) as well as the
  redo-log buffers ( with LGRW) to flush.
• 5 The number of available free buffers in the
  buffer cache drops below the threshold value.
  This also causes the database buffer cache to
  flush
• An unrecoverable error occurs. This forces the
  transaction to be terminated and rolled back and
  an error reported back to the server session.
• NOTE The transaction never records as
  successful until the redo-log buffer successfully
  writes to the online redo-log files. This
  demonstrates the importance of the LGRW and ARCH
  processes.

6
Oracle 8i Tuning

• Tuning the Shared Pool
• Tuning the Buffer Cache
• Tuning the Redo Log Buffer
• Database Configuration and I/O Issues
• Using Oracle Blocks Efficiently
• Optimizing Sort Operations
• Tuning Rollback Segments
• Monitoring and Detecting Lock Contention
• SQL Issues

7
Tuning the shared Pool

• Tune the library cache and the data dictionary
  cache
• Measure the shared pool hit ratio
• Size the shared pool appropriately
• Pin objects in the shared pool
• Tune the shared pool reserved space
• Describe the User Global Area (UGA) and session
  memory considerations
• Configure the large pool

8
Tuning the Buffer Cache

• Describe how the buffer cache is managed
• Calculate and tune the buffer cache hit ratio
• Tune the buffer cache hit ratio by adding or
  removing buffers
• Create multiple buffer pools Size multiple pools
  
• Monitor buffer cache usage
• Make appropriate use of table caching
• Diagnose LRU latch contention
• Avoid free list contention

9
Tuning the Redo Log Buffer

• Determine if processes are waiting for space in
  the redo log buffer
• Size the redo log buffer appropriately
• Reduce redo operations

10
Database Configuration and I/O Issues

• Diagnose inappropriate use of SYSTEM, RBS, TEMP,
  DATA, and INDEX tablespaces
• Use locally managed tablespaces to avoid space
  management issues
• Detect I/O problems
• Ensure that files are distributed to minimize I/O
  contention and use appropriate type of devices
• Use striping where appropriate
• Tune checkpoints
• Tune DBWn process I/O http//www.electrocompinstit
  ute.com/ElectroComp/catalog/ODBA.htm

11
Optimizing Sort Operations

• Identify the SQL operations that require sorting
  
• Ensure that sorting is done in memory where
  possible
• Reduce the number of I/Os required for the sort
  runs
• Allocate temporary space appropriately

12
Tuning Rollback Segments

• Use the dynamic performance views to check
  rollback segment performance
• Reconfigure and monitor rollback segments
• Define the number and sizes of rollback segments
  
• Appropriately allocate rollback segments to
  transactions

13

• Tuning Rollback Segment
• gt Introduction -
• Rollback segment holds the data snapshot during
  update . If the transaction is rolled back, then
  the data snapshot is applied.When setting up your
  DB , reserve multiple tablespaces for rollback
  segemnts so that user donot contend with each
  other in the same tablespace .
• vrollname vrollstat vsession vsqltext
  vtransaction
• gt Monitoring Rollback segment
• select a.name,b.extents,b.rssize,b.xacts,b.waits,b
  .gets,optsize,status
• from vrollname a , vrollstat b
• where a.usn b.usn
• Examine the result
• (xacts is above 1) wait for any rollback segemnt
  , increase the number of rollback segments to
  eliminate (potential) contention .
• General rule -
• Number of concurrent transaction number of
  rollback segment
• less than 16 - 4
• 16-32 - 8

14
Using Oracle Blocks Efficiently

• Determine an appropriate block size
• Optimize space usage within blocks
• Detect and resolve row migration
• Monitor and tune indexes

15
Tuning

• Autotrace
• Sql_trace And tkprof
• DECLARE
• timing PLS_INTEGER
• x PLS_INTEGER
• BEGIN
• timing DBMS_UTILITY.get_time
• FOR i IN 1 .. 100000 LOOP
• x i
• END LOOP
• DBMS_OUTPUT.put_line ('It took 'round((DBMS_UTIL
  ITY.get_time - timing)/100),2))' ..secs')
• END

16
SQL Tuning

• The smallest table at last in the FROM clause
• The largest table column at first in the WHERE
  clause
• Avoid to use IN , LIKE , NOT IN operator
• Use of DECODE improves performance
• Query for the required columns only
• Indexing the column which we use more frequently
  in where clause (See the should shouldnt have
  more I/U/D) otherwise we will face Disk
  contention then we will have to REBUILD the index
  .
• Contention Disk and I/O Assign a separate
  temporary table space to the user so that it will
  not
• Mix with the other table spaces
• Avoid any function on index column as it avoids
  the use of index .
• 
  Contd..

17
SQL Tuning

• Fragmentation
• Row Chaining and Row Migration
• Row chaining occurs when the pctfree is 0 and
  there is no space to an updated row so it stores
  somewhere in the other data block
• Row Migration the inserted/updated row has no
  space to store the data then it stores at some
  other space in the segment
• Check the BLEVEL from USER_INDEXES and if it is
  1,2,3,4 the its ok otherwise rebuild the index
• Hints which tells compiler to process a
  different way
• User_merge(Merged join) no index on both table
  retrieves fisrt row and compare it with the
  second table and gives the result but not with NL
  till all the rows compare , use_NL(Nested Loop)
  Index on one table ,hash join , cluster join ,
  and to avoid index use . Index , first_row ,
  all_row