Goals for This Week

1
Goals for This Week

• Brief SQL refresh
• New SQL in Oracle 9i
• Overview of Oracle Architecture
• Basic Oracle Administration
• Performance Tuning
• Schema Design
• Back-end Tools

2
Switch to SQL Intro
3
Role of Database Server
Results
SQL or Procedure Call
SQL or Procedure Call
Results
HTML
HTTP GET
Client
4
Role of Database Server (4 tier)
Results
App. Server
SQL or Procedure Call
Proc./ Trans Call
Results
HTML
HTTP GET
Client
Web Server
5
Logical Objects
6
Logical Objects
Database
consists of
Schemas
own
Tables Indexes Sequences Views Procedures Triggers
Etc
Objects
7
Schema Types

• Application Schemas
• Payroll
• Student records
• Financial Reporting
• Nascar
• Hollywood
• User Schemas
• Joe
• Fred
• Dave

Database
consists of
Schemas
own
Objects (tables, indexes, etc)
8
Schema Attributes

• Application Schemas
• Usually referred to as a schema object
• Contains a set of closely related tables and
  associated objects
• Typical Object Reference Hollywood.Movies
• User Schemas
• Often referred to as a User or Account
  object.
• May or may not contain personal tables
  (Joe.Contacts).
• May or may not have the right to even create
  objects.
• Are generally granted privileges to objects in
  application schemas.

9
LAB
Create a user schema And an application
schema Using dba studio
Note security weakness System (pw
manager) Sys (pw change_on_install) Scott (pw
tiger) Sysman (pw oem_start)
10
Caution
Creating a user schema from SQL
CREATE USER CALVIN IDENTIFIED by HOBBES
11
LAB
Creating a user schema from SQL
CREATE USER CALVIN IDENTIFIED by HOBBES
DEFAULT TABLESPACE USERS TEMPORARY
TABLESPACE TEMP QUOTA 100M on users
QUOTA 100M on temp QUOTA 100M on
rbs -- quota on RBS not necessary on
9i GRANT CONNECT TO CALVIN -- let him log
in GRANT RESOURCE TO CALVIN -- let him create
tables and other objects
12
Oracle vs. SQL Server
Database
SQL Server Software Instance
defines
consists of
Schemas
Databases
Users
own
own
contain
Objects (tables, indexes, etc)
Objects (tables, indexes, etc)
13
Oracle vs. SQL Server
Database
SQL Server Software Instance
defines
consists of
Schemas (users)
Databases
Users
own
contain
Granted Privileges to
own
Objects (tables, indexes, etc)
Granted Privileges to
Objects (tables, indexes, etc)
14
Controlling Application Access

• Each User is given an Oracle Account and control
  is maintained by granting privileges to tables,
  procedures and other objects. (e.g. DESIGN)
• All users connect ominously to a web site. The
  Web or Application server connects to Oracle in
  the context of a particular user.
• Although the Web or Application server connects
  to Oracle in the context of a particular user,
  individuals connecting to the web site must still
  authenticate for the interface to determine what
  they should be allowed to do
• Oracle Authentication
• Local user table in application
• Network authentication

15
Roles and Profiles

• Role
• Functions similar to NT Groups
• Specific Object and System Privileges are granted
  to the Role
• That Collection of Privs can be quickly granted
  to a USER by granting them the Role (Example
  GRANT DBA to HOMER)
• Profile
• Provides default values for USER attributes such
  as
• Number of Concurrent Sessions
• Password complexity

16
LAB
Creating a role
Create ROLE trainee Grant connect to trainee
-- right to log on Grant resource to trainee --
right to create objects Grant select_catalog_role
to trainee -- dictionary privs
17
LAB
Creating user schemas from PL/SQL
DECLARE v_sqlstatement varchar2(200)
v_newclass newclassrowtype CURSOR
newclass_cursor IS SELECT from newclass BEGIN
DBMS_OUTPUT.PUT_LINE (' begin
account creation') OPEN newclass_cursor
FETCH newclass_cursor into v_newclass WHILE
newclass_cursorfound LOOP
DBMS_OUTPUT.PUT_LINE (v_newclass.userid)
v_sqlstatement 'create user '
UPPER(v_newclass.userid) ' identified by
oracle ' v_sqlstatement v_sqlstatement
'DEFAULT TABLESPACE USERS '
v_sqlstatement v_sqlstatement 'TEMPORARY
TABLESPACE TEMP ' v_sqlstatement
v_sqlstatement 'QUOTA 100M on users '
v_sqlstatement v_sqlstatement 'QUOTA 100M
on temp ' v_sqlstatement
v_sqlstatement 'QUOTA 100M on rbs ' -- rbs
quota not needed in 9i execute immediate
v_sqlstatement -- this is dynamic SQL needed
because of early binding v_sqlstatement
'GRANT trainee TO ' v_newclass.userid
execute immediate v_sqlstatement FETCH
newclass_cursor into v_newclass END LOOP
DBMS_OUTPUT.PUT_LINE (' end
account creation') CLOSE newclass_cursor END
/
18
Oracles Architecture
19
Instance vs. Database
Instance (memory structures)
Database (disk structures)
20
Multiple Instances for 1 Database
Instance A (memory structures)
Instance B (memory structures)
Database 1 (disk structures)
21
Multiple Databases for 1 Instance
Instance A (memory structures)
Database 1 (disk structures)
Database 2 (disk structures)
22
Instance to Instance Communication
Instance A (memory structures)
Instance B (memory structures)
Via client Or replication process
Database 1 (disk structures)
Database 2 (disk structures)
23
Key structures and Processes
ORACLE INSTANCE
CLIENT
CLIENT
CLIENT
Database Buffer Cache
Redo Log Buffer
Shared Pool
Library Cache
LSTNR
Network
Data Dictionary Cache
SERVER
SERVER
SERVER
DBWR
LGWR
SMON
PMON
CKPT
Background Processes
ARCH
On-line Redo Log Files
Data Files (Inc. RB Segments)
Control Files
On-line Redo Log Files
Data Files (Inc. RB Segments)
Control Files
On-line Redo Log Files
24
Database Buffer Cache
Database Buffer Cache
Number of Buffers determined by Initialization
File Parameter DB_BLOCK_BUFFERS (8i)
DB_CACHE_SIZE (9i) Block Size is a multiple of
OS Block. Block Size if fixed in Oracle
8i. Block Size is not fixed in 9i except for the
system, rollback, and temp tablespaces. LRU
algorithm used to determine buffered data.
DBWR
Data Files (Inc. RB Segments)
Data Files (Inc. RB Segments)
Free block
Used block
Dirty block
25
Buffer Hit Ratio

• Consistent Gets DB Block Gets - Physical Reads

100
Consistent Gets DB Block Gets
Generally, a hit ratio below 80 indicates too
many physical reads and suggests that
DB_Block_Buffers should be increased.
26
Hit Ratio Since Instance Startup
LAB

• Select
• sum(decode(NAME, 'consistent gets',VALUE, 0))
  "Consistent Gets",
• sum(decode(NAME, 'db block gets',VALUE, 0)) "DB
  Block Gets",
• sum(decode(NAME, 'physical reads',VALUE, 0))
  "Physical Reads",
• round((sum(decode(name, 'consistent gets',value,
  0))
• sum(decode(name, 'db block
  gets',value, 0)) -
• sum(decode(name, 'physical reads',value, 0)))
  /
• (sum(decode(name, 'consistent gets',value, 0))
  
• sum(decode(name, 'db block gets',value, 0)))
  100,2) "Hit Ratio"
• from vsysstat
• Next change number of buffers, bounce service,
  large query, check hits

27
Shared Pool

• Library Cache
• Holds parsed SQL statements
• Interrogate via vsqlarea vlibrarycache
• CURSOR_SHARING FORCE
• Data Dictionary Cache
• Holds table and column definitions
• and privileges

Shared Pool
Library Cache
Data Dictionary Cache
28
Shared Pool Hit Ratios
LAB

• Library Cache Hit Ratio
• Select (sum(pins) / (sum(pins) sum(reloads)))
  100 Lib. Hit Ratio
• from vlibrarycache
• Dictionary Hit Ratio
• Select (sum(gets) / (sum(getmisses)
  sum(gets))) 100 Dict. Hit Ratio
• from vrowcache

Also look at vsqlarea
29
Oracle Processes

• Background Processes
• run on server
• as separate processes on UNIX (ps ef grep
  ltORA_SIDgt)
• As separate threads in single service on NT
• Listener Process
• one or more per server
• listens for connection requests
• hands off to server process (on diff. Port)
• Server Processes
• Run on server
• Typically one process to support each connected
  user (unless MTS)
• Client (User) Process
• runs on client machine
• communicates with a server process on server

30
Oracle Background Processes

• DBWR
• Writes dirty blocks from the buffer pool back to
  disk.
• SMON
• Checks for consistency, initiates recovery
• PMON
• Cleans up resources if process fails
• CKPT
• Updates database status after commits and other
  key events.
• LGWR
• Writes before and after images of changed rows
  into the on-line redo log files
• ARCH
• Numbers and archives on-line redo log files

31
LAB
Start and stop the instance And listener services
using Various methods
32
LAB
Who is logged in? Look at vsession Alter
system kill session sid, serial
33
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 30000
DB-7F4 JANE 36000
Data requested might or Might not be in the
buffer.
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
34
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 30000
DB-7F4 JANE 36000
BEGIN SCN 412
May or may not Have written at this point.
LGWR
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
35
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 30000
DB-7F4 JANE 36000
RB-65B
BEGIN SCN 412
May or may not Have written at this point.
LGWR
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
36
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 30000
DB-7F4 JANE 36000
OLD7F3 FRED 30000
RB-65B NULL
OLDRB-65B
BEGIN SCN 412
May or may not Have written at this point.
LGWR
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
37
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 30000
DB-7F4 JANE 36000
NEWRB-65B FRED 30000
OLDDB-7F3 FRED 30000
RB-65B FRED 30000
OLDRB-65B
BEGIN SCN 412
May or may not Have written at this point.
LGWR
May or may not Have written at this
point. (rollback segs. First)
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
38
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 40000
NEW7F3 FRED 40000
DB-7F4 JANE 36000
NEWRB-65B FRED 30000
OLDDB-7F3 FRED 30000
RB-65B FRED 30000
OLDRB-65B
BEGIN SCN 412
May or may not Have written at this point.
LGWR
May or may not Have written at this
point. (rollback segs. First)
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
39
Oracle Commit Processing
Database Buffer Cache
Redo Log Buffer
DB-7F3 FRED 40000
COMMIT SCN 412
NEW7F3 FRED 40000
DB-7F4 JANE 36000
NEWRB-65B FRED 30000
OLDDB-7F3 FRED 30000
RB-65B FRED 30000
OLDRB-65B
BEGIN SCN 412
Definitive write through commit record
LGWR
May or may not Have written at this
point. (rollback segs. First)
DBWR
On-line Redo Log File C
Data Files (Inc. RB Segments)
Update Emp Set Sal 40000 Where nameFRED
On-line Redo Log File B
Data Files (Inc. RB Segments)
On-line Redo Log File A
40
LAB
Simulate Server Failure after update But before
commit
41
GO BACK
Go Back 2 slides And discuss consistent gets
42
Logical to Physical Mapping(controlling where
objects are stored)
Database
Oracle Tablespace is similar to SQL Server File
Group.
Stores objects in
Tablespaces
Made Persistent via
DataFiles
43
Why multiple Tablespaces?

• Reduce Disk Contention by spreading disk I/O over
  multiple spindles.
• We control which TS objects are stored on
• We control physical location of TS datafiles
• Even if all disks are in use currently, the best
  practice is to keep major apps on different
  tablespaces to more easily allow for future
  movement.
• Reduce performance degradation during on-line
  physical backups.
• Allows different applications to have different
  physical parameters
• block size
• Fragmentation level
• etc

44
Typical Tablespaces

• SYSTEM 32464 E\ORACLE\ORADATA\INSY
  \SYSTEM01.DBF
• RBS 265600 E\ORACLE\ORADATA\INSY
  \RBS01.DBF
• USERS 89440 E\ORACLE\ORADATA\INSY
  \USERS01.DBF
• TEMP 10560 E\ORACLE\ORADATA\INSY
  \TEMP01.DBF
• TOOLS 1280 E\ORACLE\ORADATA\INSY
  \TOOLS01.DBF
• INDX 2560 E\ORACLE\ORADATA\INSY
  \INDX01.DBF
• DRSYS 2560 E\ORACLE\ORADATA\INSY
  \DR01.DBF
• OEM_REPOSITORY 3841 E\ORACLE\ORADATA\INSY
  \OEM_REPOSITORY.ORA

45
Typical OFA segregation

1. Oracle software
2. System tablespace
3. System indexes
4. RBS tablespace
5. Data tablespace
6. Data Indexes TS
7. Temp tablespace
8. Tools tablespace inc. index
9. Online redo log 1
10. Online redo log 2
11. Online redo log 3

1. Control file 1
2. Control file 2
3. Control file 3
4. Application software
5. User Tablespace inc. indexes
6. Archive log dest

46
RAID Redundant Array of Inexpensive Disks
Volume Set
1 2 3 4 5 6
7 8 9 10 11
12 13 14 15
C
Stripe Set (Raid Level 0)
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
47
RAID Redundant Array of Inexpensive Disks
Mirror set (Raid level 1)
1 3 2 4 3 5
1 3 2 4 3 5

Drive Controller
48
RAID Redundant Array of Inexpensive Disks
Stripe Set with Parity (Raid Level 5)
1 0 1
1 1 0
0 0 1
0 1 0
0 0 0
0 0 1
1 0 0
1 0 1



0 1 0
1 0 0
1 0 0
0 1 0
0 1 0
1 1 0
1 1 1
0 1 1

parity

data
49
RAID Redundant Array of Inexpensive Disks
Stripe Set with Parity (Raid Level 5)
1 0 1
0 0 1
0 1 0
0 0 0
1 0 0
1 0 1



0 1 0
1 0 0
0 1 0
0 1 0
1 1 1
0 1 1

parity

data
50
RAID Redundant Array of Inexpensive Disks
Mirrored Stripe Set (Raid Level 0 1)
Data container
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
Mirror container
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
51
RAID Redundant Array of Inexpensive Disks
Stripe Set of Mirrors (Raid Level 1 0)
Stripe Set
Mirror Set
Mirror Set
Mirror Set
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
52
Large Scale RAID dependent Oracle Installation
Cabinet 1 (9 Containers, 42 drives)
Log 1
Ndx 3
Log 2
Ndx 4
Log 3
Intf 1
c3
c1
c2
Data 1, Cntrl 1
Data 2, Cntrl 2
Sys, Rbs, Temp
Cabinet 2 (9 containers, 42 drives)
Log 4
Ndx 1
Log 5
Ndx 2
Log 6
Intf 2
c6
c4
c5
Data 3, Cntrl 3
Data 4, Cntrl 4
Archived Logs
RAID 1 (Log 1)
RAID 0 1 (Ndx 3)
Controller 1 (c1) Details
RAID 0 1 (Data 1, Cntrl 1)
Source Oracle Performance Tuning 101, Oracle
Press
53
Small Scale RAID dependent Oracle Installation
optimized for read/write operations.
Redo Log Files
Arch Log Files
Datafiles, INIT, and Control Files
Raid 1
Raid 1
RAID 1 0
54
LAB
Directly Examine dba_tables dba_tablespace
s Vdatafile Or indirectly _at_\\neelix\oracle\scr
ipts\admin\datafiles _at_\\neelix\oracle\scripts\admi
n\usertabs Look at Same using DBA studio Create
a new tablespace And related datafile(s).
55
Creating Indexes and Tables in specific
tablespaces.

• Each user is assigned a default tablespace.
• Dont create tables when logged in as system or
  sys unless instructed to do so when installing
  Oracle tools.
• Override defaults when necessary through extended
  DDL syntax.

CREATE TABLE GLACCOUNTS ( AccountNum char(6)
Name varchar2(30), Balance
number(12,2)) TABLESPACE FINANCIALS