Assignment # | Assignment |
7 | Read the essay on the course website titled "Success in Graduate School". |
8 | This assignment deals with b-tree performance. You will need to get a free Oracle account that allows a larger database. It's time to use your free trial of expanded services. Before you have to pay for them, we'll be done with them. An Excel spreadsheet that will generate a script to create a large performance database is located at Performance Script. Use it to create a large database for performance testing. The database should have very large tables that have columns of random integers and random characters, of type integer and varchar. Build indexes on those columns in the large tables, and compare the search performance of indexes on varchar and integer columns. Use EXPLAIN PLAN to learn how the database system is processing your queries. Draw and bring to class a graph of the performance for finding a single row based on search without and index, and with an index, for a single column of various types. The graph should show how the performance changes with table size, and it should show at least five data points for each curve. Note that you will need to clear the cache of your instance of Oracle between performance tests, so that cached pages don't interfere with your test results. |
9 | This assignment is a further exploration of some performance issues, particularly dealing with how queries are performed. Use the performance testing database that you've created with large tables with columns of different data types. You are to present your work at our weekly conference as a PowerPoint presentation, and also to submit it in Blackboard.. Use appropriate presentation methods to show your results, such as graphs. Use no more than five charts in your presentation. Here is the scenario. Think of yourself as working on a project to develop a major database application, for Citibank, for example. As one of the leading technical people on the project, you've been asked to put together a presentation of guidance for the other programers on one of the topics below. Do some performance experiments so that you have interesting data to share, and decide what's a good way to present it so that it is easy to understand and is persuasive in its support of the guidance you give.
First, let's divide the class into three sections usiing the first letter of your last name. Divide its position in the alphabet by 3, and use the remainder as your group number. That is, if your last name is Chu, then 3 is the position in the alphabet of C, and, divided by 3, the remainder is zero. So you are in group zero. There are also groups one and two. Depending on your group, carry out one of the assignments below:
0. Multi-table joins. You are going to give your colleagues guidance on how Oracle processes multi-table joins, so that they understand how to get good performance on their queries. The most important choice that pregrammers will have is which columns to index and how to index them, so that's the guidance you want to give, supported by data that you obtain with experiments. Some of the areas you may want to exploare are: a six-table join, using all columns of high selectivity; one six-table join of columns of high and low selectivity; one six-table join of columns of low selectivity. These are just examples, you decide which experimental data to present and how to present it. You can use EXPLAIN to determine how Oracle processed these queries. The important result of this experiment is your explanation of how the joins were processed and why. 1. Indexing and NULL. You will give your colleagues guidance about the use of indexes and query performance when a column can contain null values, and the cost of searching for null values. First, insert a single null value into half the columns of each table, varying the data type for columns you use. Now create indexes and composite indexes on another column as well as a column that contains a null value. Look for methods that force Oracle to use an index to search for a null value, and show experimental results. Your guidance to your colleagues is the important part of the assignment; your experiments should provide data that supports your explanation. 2. Join Performance. You will give your colleagues guidance on the performance of two-table joins with and without indexes, for different selectivity and different table sizes as well as exact, ineuquality and LIKE comparisons. Construct SELECT statements and use time reports and EXPLAIN to measure performance of various join processing methods. The important result of your experiments is how Oracle decides which method to use in processing a join; your experimental results should support your conclusions. |
10 | Turn in on Blackboard either the even or odd problems, depending on whether the final digit of your GWID is even or odd: 1. If a transaction has not committed before the final checkpoint on the log, what operation should be applied to it during recovery, redo or undo? Why? 2. If a failed transaction was increasing a salary in the database, if redo is applied to that transaction, will this result in a salary increase that’s double the desired increase? Explain. 3. Should the log entry for a transaction be written before the transaction is written to the database, or afterward, or does it not matter? Explain. 4. Find and describe the four levels of transaction isolation in the SQL Standard. Which of these is implemented in Oracle SQL and what are the SQL statements? 5. Show an example sequence of events leading to a deadlock between two transactions trying to obtain locks on resources. Describe how deadlock detection and resolution would occur. 6. Locking can occur at different levels of granularity. Two typical examples are the row level and the database page level. For a database system designer, what are the tradeoffs between these two different levels? Which would you choose for a database product that is intended to be sold for a wide range of applications? 7. Give an example database transaction that is a distributed banking application and explain how the ACID requirements apply to it. 8. Suppose a database is to be split between two different servers, with some tables on one server and some on another. Do you think the operation of COMMIT and ROLLBACK will work in the same way across two servers? |
11 | All exercises from section 6.5 of the text |
/