RelationalDBDesign
SiteMap
Advanced Tuning
SQL Tuning
Perform Tuning
Tuning Instance
Tuning Basics
External Environment
Reduce Disk I/O | Process
«Prev
Next»
DBA Taks
Database Monitoring
Tuning Components
Tuning Roles
Network Performance
Disk I/O DB Performance
Memory Usage Performance
CPU Usage
OS Tuning Conclusion
Control File Parameters
iostat Utility
Define hot Disk
Disk Load Balancing
OS methods File Striping
OS File Striping Methods
PCTfree Striped Table
Disk IO Tuning
Managing Redo Log
Virtual Memory
Unix Memory Management
CPU Bottlenecks vmstat
Oracle Table Statistics
Reduce Memory Usage
Virtual Memory Conclusion
Redolog Files
Monitor CPU Usage
Monitor CPU via vmstat
CPU Dispatching
Changing Dispatch Priorities
Monitoring CPU Consumption
Manage Tablespace Files
Resequence Table Rows
ctas Resequence Table Rows
Remove Table Extents
Table Freelists
Using CTAS to re-sequence table rows - Exercise
Re-sequence a table
Objective:
Re-sequence the ORDERS rows into a new tablespace.
Exercise scoring
This exercise is worth a total of 10 points. Once you complete your answers, submit them to a tutor.
Background
You have noticed from your bstat-estat report that your ORDERS table has been getting a high amount of disk I/O. You are running Oracle with 8k blocks and each row is 80 bytes in length. Over 95% of the queries against the order table use the ORDER_NBR_IDX index and access an average of 10 rows per query. Your index creation syntax is on a file called /tmp/order_idx.sql.
Instructions
You have been asked to re-sequence the ORDERS rows into a new tablespace. You have already created an ORDER_NEW tablespace for this purpose and planned to do this on Christmas Day when there is nobody on the database. Write the statements CTAS, RENAME, index create and ANALYZE statements required to accomplish this task.
Hints:
Check the clustering_factor for the index.
Submitting your exercise
Enter your queries in the text box below and click the
Submit
to submit your responses.