This assignment is designed to help you to setup an environment to facilitate performance testing.
Prior to beginning this lab, create a text or wp document to record your results. Name the file CS3540L7Observations.
At specified steps, record your observations and/or answer the question. This document will be uploaded at the end of the lab.

1. Open a SQL*Plus Session
2. Connect to the database as SYSTEM.
3. Begin spooling to file CS3540Lab7s.txt
4. Drop and/or Delete any tablespaces, users and/or objects left over from prior labs.
5. Create a new Tablespace named idxTest.
   Set the size to 500M.
   Name the datafile idxTest01.dbf and place it with the other datafiles in your OFA tree.
   (hint: Find CREATE TABLESPACE syntax in your text page 79 and/or Internet resources)
6. Check DBA_TABLESPACES and/or OFA tree to insure that the tablespace was created correctly.
7. Create a user named idxtester with a password of lost
   Set the user's default tablespace to be idxTest.
   Set the temporary tablespace to be temp
8. Give the user a unlimited quota on the tablespace.
9. Give the new user privileges to create a session, connect, create a cluster, create a table,
   create an index, create a sequence and to create a procedure.
10. Open up a second SQL*Plus Session
11. Connect to this session as idxtester
12. Begin spooling to file CS3540Lab7u.txt
13. At each remaining step of the lab insure that you are using the correct session/user.
    (note that you can also use the SQL*Plus command: show user)
14. Insure that you will see script output by typing set server output on
15. Download and execute the script: CS3540PLSQL2.TXT
    http://rbhilton.com/wsucs/cs3540/cs3540plsql2.txt
    (note that it has errors. to see what the error is, you can type show errors at the SQL> prompt)
16. Correct the problem with the script file, save it and rerun it.
17. Query the count of the Student and Registration tables to insure that they were created and loaded.
18. Record the observation of the amount of time it took to create and load the tables.
    Verify that the correct number of rows were created.
19. Create and run a query to determine the number of unique student names that were created.
    Record your observation.
20. As the system user, query the table dba_ind_columns to determine what indexes exist in the student and registration tables. Record your observation.
    (Make sure you specify a table in upper case since there are thousands of indexes)
21. Create a new PL/SQL script file to time various SQL statements.
    Your script file should allow you to put SQL statements inside a section that will display the beginning and ending times.
    Name your file tester.sql
22. Use the tester.sql script to time the following SQL statement:
    Record your observations.
    SELECT lastname, courseid, count(*) as "number of rows"
    FROM student s, registration r
    WHERE s.studentid = r.studentid
    GROUP BY lastname, courseid;
23. Use the tester.sql script to time the following SQL statement:
    Record your observations.
    SELECT lastname, courseid, count(*) as "number of rows"
    FROM student s, registration r
    WHERE s.studentid = r.studentid
    AND lastname LIKE '%Locke%'
    AND courseid LIKE '%2650%'
    GROUP BY lastname, courseid;
24. Use the tester.sql script to time statements that create indexes on lastname, firstname and courseid;
    Record your time and any other observations.
25. Modify the CS3540PLSQL2.txt script to create indexes on lastname, firstname and courseid.
26. Re-run the CS3540PLSQL2.txt script and record time results.
    How did having the indexes defined prior to inserting the rows affect performance?
    How does the total time compare with the time to create first and place indexes after.
27. With the new indexes in place, redo step 22 and record your observations.
    
28. Use both tester.sql and CS3540PLSQL2.txt to test other index and query scenarios.
    Record your observations.
29. End Spooling
    
    

Use Assignment Uploader to submit both your spool file and your observation document.