If you missed this one, there is still
good news for IOUG-A members: the papers and presentation files
are available for you to download via the IOUG-A web site (www.ioug.org).
Take advantage of the incredible information as well as the Tips
Section that shows tips created by Peter Koletzke and his team
of experts.
Although a lot can be learned from the
papers and presentation, being at the conference gives you the
opportunity to hear first hand and ask questions. It also is the
best opportunity for networking with others that may have a similar
Oracle architecture to your own. That’s why you should begin
making plans to attend IOUG-A Live! 2001, which will take place
April 29 – May 3 in Orlando, Fla., US.
Change is in the Air!
You might have noticed that my column
is appearing in a new place in the magazine. That’s because
this spring I was elected president of the IOUG-A. I have enjoyed
serving as your Executive Editor for Select Magazine, but
I also look forward to this new challenge in helping make our
organization a driving, independent force within the Oracle community.
| • |
Michael Abbey – The IOUG-A Live Conference
Master (Director of Conferences) |
| • |
Ian Abrahmson – The Educator (Director
of Educational Programming) |
| • |
Mike Gangler – Partner Helper (Director
of User Group Relations) |
| • |
Peter Koletzke – The WebMaster (Director
of Web Initiatives) |
| • |
Brian Laskey – The CFO (VP of Finance) |
| • |
Donna McConnell – Growth Master (Director
of Member Services) |
| • |
Rich Niemiec – Right hand man (Executive
Vice President) |
| • |
Merrilee Nohr – The Oracle Partner (President) |
| • |
Rita Palanov – Marketing Master (Director
of Marketing) |
Before we look ahead, however, it’s
important to recognize the Board of Directors and their many accomplishments
during the past year.
We say goodbye to Michael Abbey, Mike
Gangler and Rita Palanov. Most recently, Michael Abbey served
as director of conferences. In doing so, he has coordinated members
of the conference committee along with juggling all of the details
to produce a conference orchestrated to perfection. Thanks Michael
for all of the hard work and dedication!
Rita Palanov has served many roles at
IOUG-A; most recently, she was the director of marketing. Her
efforts along with those of the Director of Membership have put
us at 10,000 IOUG-A members. She will be missed.
Mike Gangler has always been the eyes
and ears of the IOUG-A. Mike is always pulling for every customer
of Oracle to make life easier and better. Early on, Mike served
as our vice president of finance and later worked on alliances
and membership. Mike continues to build and cultivate the grass
roots user groups especially in the Detroit area.
We also say hello to four excellent new
board members. Bill Burke who will serve as the director of user
group relations formerly sat on the board of the Oracle Development
Tools User Group. Kimberly Floss, serving as director of marketing,
comes to us from Leo Burnett, which is a large Oracle customer.
Karen Langley will serve as the director of conferences and was
the Chris Wooldridge Award winner this year. Karen has served
on the IOUG-A Conference Committee for several years. The IOUG-A
also appointed a Director at Large this year. Bogdan Capatina
comes to us from Ford and will serve on this appointed position.
Bogdan will focus on developing our relations and support of educational
institutions.
Tech Tip: How to monitor your code using
Autotrace and PL/SQL
A common way to check both the timing
and the execution path of a piece of code is to use the AUTOTRACE
feature that is found in SQL*Plus 3.3 and higher. It gives a variety
of statistics when a given query is run. PL/SQL can also be used
to time long running pieces of code with many queries (shown after
autotrace).
To use Autotrace, you need to go into
SQL*Plus and set the autotrace on (shown below). By using the
command SET AUTOTRACE ?, you will see all of the possible options
for Autotrace which include options for NOT displaying the output
among others. The example below shows a very simple example of
using the Autotrace command.
Setting Autotrace On:
Go into sqlplus and type the command
listed below before you run your query.
[begin code]
SQLPLUS> SET AUTOTRACE ON
[end code]
Next...run the query
[begin code]
select count(name)
from emp7
where name = ‘SMITH’;
Autotrace Output:
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF ‘EMP7_I1’ (NON-UNIQUE)
Statistics
0 recursive calls
0 db block gets
1 consistent gets
1 physical reads
0 redo size
223 bytes sent via SQL*Net to client
274 bytes recd via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
[end code]
The first section of the output (above)
gives the Explain Plan of the query (the order of execution) while
the second section gives statistics about resources used during
the execution. If the output above gives and inordinate amount
of physical reads, you are probably missing an index or driving
the query with the wrong table (in a multi-table join). Adding
an index, creating a function-based index for an index being suppressed
by a function or using the ORDERED hint to change the order of
the driving table may all be solutions to a problem with a lot
of physical reads. Autotrace is very helpful in fixing a single
problem query.
Using the Stop_Watch for Timing Larger
Programs
This section will describe how to build
a stopwatch to time those long running programs where you just
don’t know where the problem query is (because there are
so many queries). This is taken from Joe Trezzo’s PL/SQL
book.
First we create the starting point for
the timer routine and capture the elapsed time. This package includes
procedures that will capture the initial starting time and then
calculate the elapsed time that a piece of code took to run.
[begin code]
STOP_WATCH
CREATE OR REPLACE PACKAGE stop_watch AS
pv_start_time_num PLS_INTEGER;
pv_stop_time_num PLS_INTEGER;
pv_last_stop_time_num PLS_INTEGER;
[endcode]
This procedure creates a starting point
for the timer routine and is usually called once at the beginning
of the PL/SQL program unit.PROCEDURE start_timer.
This procedure retrieves a point in time
and subtracts the current time from the start time to determine
the elapsed time. The interval elapsed time is logged and displayed.
This procedure is usually called repetitively for each iteration
or a specified number of iterations.
[begin code]
PROCEDURE stop_timer;
END stop_watch;
/
STOP_WATCH
CREATE OR REPLACE PACKAGE BODY stop_watch AS
PROCEDURE start_timer AS
BEGIN
pv_start_time_num := DBMS_UTILITY.GET_TIME;
pv_last_stop_time_num := pv_start_time_num;
END start_timer;
PROCEDURE stop_timer AS
BEGIN
pv_stop_time_num := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(‘Total Time Elapsed: ‘ ||
TO_CHAR((pv_stop_time_num - pv_start_time_num)/100,
‘999,999.99’) || ‘ sec Interval Time: ‘ ||
TO_CHAR((pv_stop_time_num - pv_last_stop_time_num)/100,
‘99,999.99’) || ‘ sec’);
pv_last_stop_time_num := pv_stop_time_num;
END stop_timer;
END;
/
[end code]
Now you can call these procedures from
any piece of code. Run the piece of code (calling these procedures)
that you would like to get timings for to find out where the slow
part is. The following shows how to start and stop the stop_watch
by running stop_watch.start_timer and stop_watch.stop_timer around
our piece of code (which in this case sleeps for 5 seconds). Remember
that you have to create the package and package body first (as
shown in the preceding section) before you call it.
[begin code]
STOP_WATCH
BEGIN
stop_watch.start_timer;
FOR lv_count_num IN 1..5 LOOP
DBMS_LOCK.SLEEP(5);
stop_watch.stop_timer;
END LOOP;
END;
/
[end code]
This results in the following output:
[begin code]
Output:
Total Time Elapsed: 5.01 sec Interval Time: 5.01 sec
Total Time Elapsed: 10.03 sec Interval Time: 5.02 sec
Total Time Elapsed: 15.04 sec Interval Time: 5.01 sec
Total Time Elapsed: 20.05 sec Interval Time: 5.01 sec
Total Time Elapsed: 25.06 sec Interval Time: 5.01 sec
PL/SQL procedure successfully completed.
[end code]
You can now see how you can use this
to see where things are slow in a particular piece of code. Once
you find the slow part, you can then tune that slow part. Once
the package is built, developers can easily call it from any piece
of code. Good luck!
