All Powder Board and Ski Oracle 9 i

Oracle System Tables (Synonyms): Metadata Prefi Synonym Description x e s ALL_ DBA _ USE R _ CONSTR AINTS IND_COL S MVIEWS SEQUEN CES SYNONY MS TAB_CO LUMN S TABLES TRIGGE Table constraints and keys Indexed columns Materialized views Sequences Synonyms Table columns Tables Trigger columns Triggers 2

Oracle Data Storage Tablespace Table Data Tablespace Rollback segments Redo logs Disk Drive 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 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. ID FROM Customer, Sale WHERE Customer. ID = Sale. Customer. ID AND Customer. ID NOT IN (SELECT Customer. ID 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 l You could shut down the database and copy the data files and the control file l Make sure the Oracle Management Server is installed and running. l l You need Archive Log mode set to handle a running backup l l You might have to install it from the main install wizard Make sure the Oracle. Ora. Home 92 Management. Server 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 l 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 tials creden Workgroup database Usernames and passwords Form 1 Form 2 Form 3 Form 4 logi n User 1 User 2 21

User Groups Sales table Customer table Item table Sales clerks S, U, I S Sales Managers S, U, I, D S, U, I S Rental 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 Managers 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