Transcript Chapter 9 - Jerry Post

All Powder Board and Ski
Oracle 9i Workbook
Chapter 9: Database Administration
Jerry Post
Copyright © 2003
1
Oracle System Tables (Synonyms):
Metadata
Prefixes
ALL_
DBA_
USER_
Synonym
Description
CONSTRAINTS
IND_COLS
MVIEWS
SEQUENCES
SYNONYMS
TAB_COLUMNS
TABLES
TRIGGER_COLS
TRIGGERS
TYPES
USERS
VIEWS
Table constraints and keys
Indexed columns
Materialized views
Sequences
Synonyms
Table columns
Tables
Trigger columns
Triggers
User-defined data types
Users
Views (saved queries)
SELECT Table_Name, Pct_Free FROM USER_TABLES
2
Oracle Data Storage
Tablespace
Table Data
Tablespace
Rollback segments
Redo logs
Disk Drive
Data Files
Data Files
Disk Drive
Data Files
Data Files
RAID drives automatically spread files across multiple drives.
Even without RAID you can manually assign table data and
rollback segments to different drives.
Goal: Substantially improved performance and recovery in
case of hardware failure.
3
Gather Statistics
Statistics about the data within each table tell Oracle how to optimize
queries. The tuning system also uses the statistics to make
recommendations about indexes to improve performance.
The older command is: Analyze Table Customer compute statistics;
Oracle now recommends that you use the DBMS_STATS package
instead to analyze the entire database (or schema) at one time.
Exec DBMS_STATS.Gather_Database_Stats
Or
Exec DBMS_STATS.Gather_Schema_Stats(‘powder’)
Or
Exec DBMS_STATS.Gather_Table_Stats (‘powder’, ‘Customer’)
But, you might first have to run the catproc.sql script
4
Enterprise Manager Console
Diagnostics Pack
Lock Manager
Performance Tuner
Performance Overview
Top Sessions
Top SQL
Trace Data Viewer
Tuning Pack
Oracle Expert
Outline Manager
SQL Analyze
Tablespace Map
5
Performance Overview (Monitor)
6
Drill Down to Find Causes
7
Oracle Expert: Tuning Session
Select all items
Comprehensive
8
Tuning: Collect Statistics
You might skip
the Instance
checks for now
Use the schema options
to select your schema
9
Select Schemas
Click the button to
see a list of schemas
Be sure to
include your
schema that
holds the All
Powder tables
10
Expert Recommendations
Specific table index
recommendations
Details on
storage
locations
11
SQL Analyze
SELECT Lastname, Firstname, Customer.CustomerID
FROM Customer, Sale
WHERE Customer.CustomerID = Sale.CustomerID
AND Customer.CustomerID NOT IN
(SELECT CustomerID FROM Rental)
ORDER BY Lastname, Firstname;
List customers who bought items but never rented anything.
Note that the query analyzer does not
support the newer INNER JOIN syntax
12
Index Recommendations
Get index
recommendations
Virtual Index Wizard
13
SQL Tuning Wizard
14
Tuning Wizard Recommendation
15
Original Query Costs
16
Revised Query Costs
Note the
correlated
subquery
Note the two hash
joins instead of one
Substantially lower
total costs
17
Backup and Recovery

You could shut down the database and copy the data files and the
control file

Make sure the Oracle Management Server is installed and
running.




You need Archive Log mode set to handle a running backup




You might have to install it from the main install wizard
Make sure the OracleOraHome92ManagementServer service is
running (it is set to Manual start)
Use the Enterprise Manager Console to log in. The initial
username/password is: sysman/oem_temp
Select the database/Instance/Configuration in the tree view
Under the Recovery tab, check the Archive Log mode
This option will generate lots of data files since all changes to the
database will be saved in these archive files
Run or schedule the backup

Tools/Database Tools/Backup Management/Backup
18
Backup and Recovery Manager
Channels are disk or
tape locations to hold
the backup copies
19
Schedule Backup
20
User-Level Security
Database
Administrator
Assign permissions
Database Application
Form1 Form 2 Form 3 Form 4
Workgroup database
Usernames and
passwords
User 1
User 2
21
User Groups
Sales
table
Customer
table
Item
table
Sales clerks
S,U,I
S,U,I
S
Sales Managers
S,U,I,D
S,U,I
S
S,U,I
S
Rental Managers
Sales Managers
Assign permissions to groups
or roles based on tasks, and
assign users to groups.
Permissions only have to be
set once. Employee changes
are handled by moving
individuals into or out of
groups.
Sales clerks
Individual users
22
Create New Users
Internal or external
authentication
For many accounts
at once, use SQL
23
Create New Roles
Select table
object
Grant
permissions
24
Assign Roles to Users
25