DBM 449 DBM449 DBM/449 ENTIRE COURSE HELP – DEVRY UNIVERSITY

Description

DBM 449 DBM449 DBM/449 ENTIRE COURSE HELP – DEVRY UNIVERSITY

DBM 449 Week 1 iLab

DBM 449 Week 2 iLab

DBM 449 DBM449 DBM/449 ENTIRE COURSE HELP – DEVRY UNIVERSITY

DBM 449 Week 1 iLab

DBM 449 Week 1 iLab

DBM 449 DBM449 DBM/449 ENTIRE COURSE HELP – DEVRY UNIVERSITY

DBM 449 Week 2 iLab

Quick Overview

In this lab we will focus on several common performance tuning issues that one might encounter while working with a database. You will need to refer to both your text book and the lecture material for this week for examples and direction to complete this lab.
To record your work for this lab use the LAB2_Report.doc found in Doc Sharing. As in your first lab you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 2.

L A B S T E P S
STEP 1: Examine Query Optimization using OEM

Oracle Enterprise Manager (OEM) provides a graphical tool for query optimization. The tables that you will be using in this lab are the same ones that were created in the first lab in the DBM449_USER schema.

1. Start OEM via Citrix iLab. If you need help or instructions on how to do this you can refer to theHow_to_use_OEM_in_Citrix iLab.pdf file associated with this link.

1. Select Database Tools icon from the vertical tool bar and Select SQL Scratchpad icon from the expanded tool bar. If you need help or instructions on how to do this your can refer to theExecuting_and_Analyzing_Queries_in_OEM.pdffile associated with this link.

1. Write a SQL statement to query all data from table COURSE (you will need to connect as the DBM449_USER). Click on Execute. Take a screen shot that shows the results and paste that into the lab document.

1. Click on Explain Plan. Take screen shot of the results and past that into the lab document.

1. Write a comment how this query is executed.

1. Write a SQL statement to query the course_name, client_name and grade from the COURSE, COURSE_ACTIVITY and CLIENT tables and order the results by course name, and within the same course by client name.

1. Click on Explain Plan. Take screen shot of the results and past that into the lab document.

1. Exit out of OEM at this point.

1. Write a comment on how this query is executed.

STEP 2: Examine Query Optimization using SQL*Plus

In this portion of the lab we are going to use SQL*Plus to replicate what we did in Step one using OEM. At the end of this part of the lab you will be asked to compare the results between the processes.

1. Before you can analyze an SQL statement in SQL*Plus you first need to create a Plan Table that will hold the results of your analysis. To do this you will need to download theUTLXPLAN.SQL file associated with this link and run this script in an SQL*Plus session while logged in as the DBM449_USER user. Once the script has completed then execute a DESC command on the PLAN_TABLE.

1. Again you are going to write a SQL statement to query all data from table COURSE. Remember to make the modifications to the query so that it will utilize the plan table that you just created.

1. Now write the query that will create a results table similar to the one below by using the DBMS_XPLAN procedure.

PLAN_TABLE_OUTPUT
Plan hash value: 1263998123

Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 5 345 3 (0) 00:00:01
1 TABLE ACCESSFULL COURSE 5 345 3 (0) 00:00:01

Note
PLAN_TABLE_OUTPUT
– dynamic spamling used for this statement

1. Now execute the second query you used in Step 1 and then show the results in the plan table for that query. HINT: Before you run your second query you will need to delete the contents of the plan table so that you will get a clean analysis.

1. Write a short paragraph comparing the output from OEM to the output from the EXPLAIN PLAN process you just ran. Be sure to copy/paste all of the queries and results set from this step into the lab report section for this step.

STEP 3: Dealing With Chained Rows

In this portion of the lab we are going to create a new table and then manipulate some data to generate a series of chained rows within the table. After you have generated this problem then we are going to go through the process of correcting the problem and tuning the table so that the chained rows are gone. The process is a little tricky and is going to require you to think through your approach to some of the SQL. Remember that every table has a hidden column named ROWID that is created implicitly by the system when the table is created. This column can be queried just like any other column. You will need this information in step 6 of this part of the lab.

1. For this part of the lab you will need to create a new user named GEORGE. You can determine your own password but you want to make sure that the default tablespace is USERS and the temporary tablespace is TEMP. Grant both the connect and resource rolls to the new user and then log in to create a session for the new user GEORGE.

1. Once logged in to the new user then write the SQL to create a new table using the given column information and storage parameters listed below. NOTE:the parameters have been chosen intentionally so please do not change them.

Table name: NEWTAB
Columns: Prod_id NUMBER
Prod_desc VARCHAR2(30)
List_price NUMBER(10,2)
Date_last_upd DATE

Tablespace: USERS
PCTFREE 10
PCTUSED 90
Initial and Next extents: 1K
MinExtents 1
MaxExtents 121
PCTINCREASE 0