In
this practice we will look at Oracle’s shared server configuration and how to
determine performance.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Determine
the percent of the time each dispatcher is busy.
|
SQL>
l
1 select network
"Protocol", status "Status",
2 sum(owned)
"Clients",
3 sum(busy)*100/(sum(busy)+sum(idle))
"Busy Rate"
4 from v$dispatcher
5* group by network, status SQL>
/ Protocol
Status
Clients Busy Rate ----------------------------------------
---------------- ---------- ---------- (ADDRESS=(PROTOCOL=ipc)(KEY=#676.1))
WAIT
0 .00005158 SQL> |
2.
Determine
the performance of the shared server processes using v$mts.
|
SQL>
select maximum_connections, maximum_sessions,
servers_highwater
2 from v$mts; MAXIMUM_CONNECTIONS
MAXIMUM_SESSIONS SERVERS_HIGHWATER -------------------
---------------- -----------------
0
0
3 SQL> |
This
completes this practice.
This
practice covers the steps to create, use, and monitor an Oracle stored outline.
Assumptions
·
An
Oracle 9i database is up and running.
·
An
HR schema with an employees table.
Instructions
1.
Create
an outline called emphire which selects the employee_id,
first_name, last_name,
and hire_date from the employees table that where
hired in 96.
|
SQL>
connect / as sysdba Connected. SQL>
get emphire
1 create or replace
outline emphire on
2 select employee_id,
first_name, last_name,
hire_date
3 from hr.employees
4* where hire_date like ('%96') SQL>
/ Outline
created. |
2.
Execute
an alter session command to use stored outlines and run the query again.
|
SQL>
alter session set USE_STORED_OUTLINES=TRUE; Session
altered. SQL>
get emphire
1 select employee_id,
first_name, last_name,
hire_date
2 from hr.employees
3* where hire_date like ('%96') SQL>
/ EMPLOYEE_ID
FIRST_NAME
LAST_NAME
HIRE_DATE -----------
-------------------- ------------------------- ---------
120 Matthew
Weiss
133 Jason
Mallin
145 John
Russell
156 Janette
King
157 Patrick
Sully
158 Allan
McEwen
174 Ellen
Abel
184 Nandita
Sarchand
192
Sarah
Bell
04-FEB-96
201 Michael
Hartstein
17-FEB-96 10
rows selected. SQL> |
3.
Query
dba_outlines and dba_outline_hints
to see if the outline was used and what hints the outline is using for the
query.
|
SQL>
select name, used
2 from dba_outlines; NAME
USED ------------------------------
--------- EMPHIRE
USED SQL>
select name, hint
2 from dba_outline_hints; NAME
HINT ------------------------------
---------------------------------------- EMPHIRE
NO_EXPAND EMPHIRE
ORDERED EMPHIRE
NO_FACT(EMPLOYEES) EMPHIRE
FULL(EMPLOYEES) EMPHIRE
NOREWRITE EMPHIRE
NOREWRITE 6
rows selected. SQL> |
This completes this practice
This
practice covers the steps to trace applications or queries and see the results
and how to analyze tables.
Assumptions
·
An
Oracle 9i database is up and running.
·
An
HR schema with an employees table.
Instructions
1.
Turn
on SQL tracing for your session, run a simple query to generate some data, and
turn SQL tracing off again.
|
SQL>
connect hr/hr Connected. SQL>
alter session set sql_trace=true; Session
altered. SQL>
@c:\emphire EMPLOYEE_ID
FIRST_NAME
LAST_NAME
HIRE_DATE -----------
-------------------- ------------------------- ---------
120 Matthew
Weiss
133 Jason
Mallin
145 John
Russell
156 Janette
King
157 Patrick
Sully
158 Allan
McEwen
174 Ellen
Abel
184 Nandita
Sarchand
192
Sarah
Bell
04-FEB-96
201 Michael
Hartstein
17-FEB-96 10
rows selected. SQL>
alter session set sql_trace=false; Session
altered. |
2.
Run
TKPROF against the output trace file.
|
Note:
This trace file should be in the UDUMP directory. SQL>
host Microsoft
Windows 2000 [Version 5.00.2195] (C)
Copyright 1985-1999 Microsoft Corp. C:\>tkprof
c:\oracle\admin\dba9\udump\ora01524 output.txt TKPROF:
Release 8.1.7.0.0 - Production on Thu Apr 3 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. |
3.
View
the output file and find the information on the query that was run in step one.
|
C:\>type
c:\output.txt TKPROF:
Release 8.1.7.0.0 - Production on Thu Apr 3 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Trace
file: c:\oracle\admin\dba9\udump\ora01524.trc Sort
options: default *************************************************************************** ***** count
= number of times OCI procedure was executed cpu
= cpu time in seconds executing elapsed
= elapsed time in seconds executing disk
= number of physical reads of buffers from disk query
= number of buffers gotten for consistent read current
= number of buffers gotten in current mode (usually for update) rows
= number of rows processed by the fetch or execute call *************************************************************************** ***** alter
session set sql_trace=true … select
employee_id, first_name,
last_name, hire_date from
hr.employees where
hire_date like ('%96') call
count
cpu
elapsed
disk
query current rows -------
------ -------- ----------
---------- ---------- ---------- ----- ----- Parse
2 100.14
200.00
0
0
0
0 Execute
2 0.00
0.00
0
0
0
0 Fetch
4 0.00
0.00
0
8
4
20 -------
------ -------- ----------
---------- ---------- ---------- ----- ----- total
8 100.14
200.00
0
8
4
20 Misses
in library cache during parse: 2 Optimizer
goal: CHOOSE Parsing
user id: SYS Rows
Row Source Operation -------
--------------------------------------------------- 10
TABLE
ACCESS FULL EMPLOYEES …
6 user
SQL statements in session.
2 internal
SQL statements in session.
8 SQL
statements in session. *********************************************************** Trace
file: c:\oracle\admin\dba9\udump\ora01524.trc Trace
file compatibility: 8.00.04 Sort
options: default
2 sessions
in tracefile.
9 user
SQL statements in trace file.
3 internal
SQL statements in trace file.
8 SQL
statements in trace file.
4 unique
SQL statements in trace file.
103 lines
in trace file. |
4.
Analyze
the employee table to generate statistics on the table and query the number of
rows, blocks, empty blocks and average row length for the table.
|
SQL>
analyze table hr.employees estimate
statistics; Table
analyzed. SQL>
select num_rows, blocks, empty_blocks,
avg_row_len
2 from dba_tables
3 where table_name =
'EMPLOYEES' and owner = 'HR';
NUM_ROWS BLOCKS
EMPTY_BLOCKS AVG_ROW_LEN ----------
---------- ------------ -----------
107
3
12
71 SQL> |
This
completes this practice.