Several of us had a meeting earlier this week to discuss the Oracle 10gR2 upgrade project. Our goal is to have 10g in production by March. Mike has spent time this week working on the installation of the accompanying HTTP server and the creation of an Oracle DAD. I have worked on some research (Google searches) as he has worked through the errors that have arisen. What I have learned the most (over the last couple of years, really) is that Oracle's documentation is lacking very much. It would be nice to have nice procedures in place to provide some direction and insight for taking on these projects. Maybe, in the not-to-distant future, we can work toward providing some documentation, at least in-house, so we can have a record of the steps we've taken to configure, enhance and maintain our Oracle database.
Recently in Staff Development Category
We had another Programmers' Meeting this week, and I think this was the best one we've had yet! The real highlight of the meeting was the sharing of tools and other useful things that we all incorporate into our project management activities. Topics discussed:
â—¦ Overview of the Portal
â—¦ Demonstration of a tool to import BLOBs into the database
â—¦ Open Project (another project management tool)
â—¦ Job Logger (a tool to quickly log tasks and activities)
â—¦ Samples of Javascript code to enhance a GUI
â—¦ Brief discussion of AJAX, including some standard Javascript to interact with the database in real-time
â—¦ Use of IFRAMEs
â—¦ Embedded calendar
â—¦ Use of new web template in SQL
â—¦ Brief discussion of WebSmith
â—¦ Current projects to improve network and database security
â—¦ Use of popup calendar
â—¦ LDAP authentication and security
Like Ron's most recent blog, this post is geared for the UCS folks.
PL/SQL Developer 7.1.1 -- How to Dock the Window List
Tools menu > check Window List
Window menu > Save Layout
This setting will now remain in effect the next time you launch PL/SQL Developer.
I posted this question to Allroundautomations' forums and had the answer within 3-4 hours.
We had another good meeting earlier this week. We had a good discussion about how we manage the phases of a project. For the most part, we all agree that project phases are broken down as follows:
- Design
- Development
- Testing
- Routine Maintenance
A few of us elaborated on some of these areas, including brainstorming, research, coding, the release of the application to the end-users, and problem-solving. For our next meeting, we'll focus on more of the details that take place within each phase. We also talked about some of the pros and cons of both Copper and MS Project.
Programmers' Meeting
We had a Programmers' Meeting on Tuesday, which was the first "formal" meeting we've had in a long time. Among the topics of discussion, Alex gave a good demonstration of the use of the Copper project management tool, which is available to help us document and track our progress over the life cycle of a project. He compared Copper, a web-based interface, with Microsoft Project, which is available on TS4. We also expressed the desire, as a group, to work towards streamlining for efficiency, obviously, to produce even better products for our customers. Over the longer term, a major goal will be to develop applications that will be easier to maintain, using more "shared" code when appropriate. A year or two from now, for example, when we (or another Programmer) evaluate our application to resolve a problem, we need to be able to quickly get up-to-speed and debug the code.
Staff Development
Mike Ross and I had a good meeting this morning. We decided to take some time to explore Copper. We were able to quickly enter information for a project and get up-to-speed. Everything seemed fairly straightforward, but we were both somewhat disappointed with the gantt charts. Over the course of several months within a project, it is necessary to scroll a great deal in order to see the entire chart. While Copper presents good general information for management (which is important, mind you); we, as Developers, probably wouldn't use it on a daily basis to manage detailed tasks within a project.
Today we decided to take a look at Oracle's alert log. This is the place where the system documents important events in the life of the database. As part of a service request to Oracle, Mike needed to submit the most recent events from this log. We discovered that the alert log file itself is HUGE, with entries going back to 2002 when the database was placed into service. He had placed a copy of the log file on his H: drive and then used Notepad to "trim" that copy down to size in order to send to Oracle.
Mike and I continued our examination of Oracle applications and the use of bind variables. It's been very interesting to track the number of SQL statements being sent to Oracle's shared pool, as reported by my jobs which monitor this on a daily basis.
We've observed that third-party and "external" applications (e.g., Perl scripts) that query the Oracle database seem to be less efficient, as they add more duplicate SQL code to the shared pool. We've worked with a couple of programmers within UCS to implement bind variables within some Perl scripts, and we've seen significant improvements.
Third-party applications will pose greater challenges. Viking (used within the University Advancement office), for example, seems to make extensive use of dynamic SQL to allow the end-user to generate custom reports. Also, the Viking code was written using WebDB, which prevents us from even viewing the packages. We hope to work with the developers to see if bind variables can be implemented.
For yesterday's meeting, Mike and I discussed the need to upgrade to Oracle 10g and sent an email to the key players within UCS. We are hoping to have a meeting next Tuesday to begin to plan for the details of this migration. As we are currently running Oracle 9i, release 1, we are concerned that this version is disappearing from Oracle's radar of support. We look forward to moving ahead with the migration to version 10g.
We also continued our exploration into the implementation of bind variables within Oracle. We corrected some problems with the procedure that we implemented a few weeks ago; we discovered that no data was actually being written to one of our tables used to display the contents of queries.
During our meeting this morning, Mike and I decided to take another look at our use of bind variables and the analysis procedure that we implemented a few weeks back.
Within the table generated by our procedure, the "isitup_discovery" package was not listed this time. This means that, as that package runs, there are no parsed SQL statements in the cache which differ by only one number or one word. This is good, as the specific changes that Mike and I had suggested have been in production for several days now.
We created a job within my schema (this served as a good learning opportunity for me as well) that will run our analysis procedure daily. It'll be interesting, over the long term, to make similar improvements within other packages.
As Mike and Ron had worked yesteerday on the setup of Oracle Wallet Manager (OWM) and ran into difficulties, we decided to delve into this for our Staff Development time this week. OWM will allow the web server running on the Oracle server to properly handle requests for HTTPS URLs.
Between Mike, Ron and myself (I have the least knowledge of this between the three of us; I am definitely in this for the learning experience), we still don't have everything working properly. Mike and I had concluded that our difficulties were being caused by not having sufficient rights at the UNIX command prompt, and we tried various things in order to successfully complete the installation. Each time, the whole process would simply stop without any sort of errors.
Later today, Mike did some more research and sent Ron and I some additional information about another command to try that should resolve everything. We were not successful today, however, so we will work on this again next week when we can consult with Travis.
Bind Variables, Continued
We continued our exploration into the use of bind variables within Oracle. We examined two production packages that contained inefficient queries (with a high number of writes to the cache) that were reported by the code that we implemented last week. In the first package (within the "isitup" schema), we were quickly able to identify where we can make improvements to a query, and will work with the programmer in the next few days to do some testing to see if we can better implement bind variables.
In the second package we investigated (within the "coed" schema), we can't figure out why the queries are reported as writing a high number of entries to the cache. When we studied the code, it looked like the programmer had successfully implemented bind variables and all parameters were being passed properly between the procedures. We'll likely continue with this package next week.
A key point that Mike and I both realized at the same time is that every reference to a PL/SQL variable is in fact a bind variable. Oracle and PL/SQL manage bind variables very efficiently most of the time. We just need to learn how to make improvements when this is not the case.
During our weekly meetings, Mike and I have focused most of our attention on Oracle database administration topics. Today we decided to explore the concept of bind variables, with more of a focus on application development.
For me, at least, Oracle's use of bind variables is a difficult concept to grasp; like many of our projects thus far, there seem to be many, many details to understand. In order to prepare for this morning's meeting, I spent considerable time yesterday reading about bind variables within Tom Kyte's AskTom discussion forum on Oracle's website. I'd like to think that this time was well-spent, in that I gained at least a very basic inkling of an understanding of the topic. With the use of bind variables, the goal is to make more efficient use of the caching provided within the Oracle database.
In his own research (again, exploring AskTom), Mike found some very useful code that we were able to quickly implement. This will provide us with the means to take a snapshot and generate some statistics to help us determine the extent of library cache latch contention within Aero. This shows actual SQL statements running within the database; it'll be interesting to run this script from time to time to see what's happening within our system.
We hope to continue this project next week and take a closer look at some production code to see if we can implement bind variables and improve efficiency.
For our Staff Development meeting this morning, Mike and I discussed several things.
We attempted to resolve an issue with SVCHOST on a staff machine at the Physical Plant. Mike had tried to fix the machine earlier this morning and ran into difficulties. We were hoping that putting our two heads together would help, but no such luck; Mike officially reported this to the Service Request Desk.
Next, we investigated some of the Oracle event logs. We are still concerned with the number of sessions for our database and were hoping to gain some insight. We cleaned up a few of the logs that wer no longer needed, and investigated a deadlock within a specific application.
We would both like to begin working with Oracle 10g in a test enviromnent in the near future, but we need to coordinate this within the department. For our future Staff Development meetings, we may explore more application development and programming topics.
Mike and I continued our exploration of the statspack evaluation report. We decided to investigate the use of forall and bulk collect to replace the use of a for loop within a test environment. I found what seemed to be a good article that described how to accomplish this, including some good sample code. We successfully tested the "before" code, which created a test table, then populated the table with 10,000 rows of data through the use of a for loop. We ran into problems, however, when we tried to implement the actual forall loop (replacing the original for loop), as PL/SQL can't seem to recognize our record structure. We plan to continue this test project the next time we meet, after the Thanksgiving weekend.
Mike and I had planned to continue working on our statspack project, but he was troubleshooting a problem reported with a machine, located at the Werner Center, which controls a Trane HVAC system.
The proprietary application, it seems, worked fine until last week when the upgrade to IE7 was pushed out to everyone. Now, the application doesn't work from a remote desktop session. The Trane application works fine if you physically login to the machine, however.
We double-checked several things within Windows, including running Windows Update (which needed to be updated itself), and verifying that Mike was a local administrator. I also wondered if the problem wasn't somewhere within Remote Desktop. We finally decided to try uninstalling IE7. After remotely rebooting the machine, the Trane HVAC application seemed to work. Of course, IE7 was shortly installed once again, but at least we figured out a workaround for the short term.
It was interesting to spend some time once again with Windows configuration and troubleshooting once again.
Mike and I decided to have a Staff Development meeting on Monday this week, as we didn't meet last week. We'll most likely meet on Thursday as well.
Mike found an interesting site worth investigating called statspackanalyzer.org. You can submit a statspack report to this site and get an instantaneous (and I do mean instantaneous) report back that provides a somewhat in-depth analysis of the information you submitted. The resulting report contains recommendations for improving the efficiency of your Oracle database.
In order to submit our report to the site, we had to take two snapshots of our database, roughly fifteen minutes apart, then go through a somewhat involved process to generate and save the statspack report. This served as a good review for both of us, as we had worked through these steps several months ago.
Having an understanding of the recommendations contained in the generated report will be helpful as we prepare for the installation of Oracle 10g. We will take an in-depth look at the report from statspackanalyzer.org when we meet next time. Also, Mike wrote a very good blog about this, entitled Statspack Rides Again.
Since we had a UCS Staff Meeting on Thursday last week, Mike and I decided to have a Staff Development meeting on Friday. While doing some research using Oracle's Ask Tom resource, Mike found an article discussing a method for inserting encrypted data into an Oracle table and decrypting the information upon retrieval. As neither of us had done anything like this in the past, we thought it would be a great Staff Development project. A practical use for this concept would be to encrypt passwords within any application which has not yet been modified to authenticate using LDAP. By the end of our meeting, we had written a simple test package and had verified that we can insert encrypted data into a table. We expect to continue with this project in our next meeting.
For our meeting today, Mike showed me a page within the SWAMP application where he had made good use of Javascript to control the form action for the value checked for a radio button. We also checked the FINAID schema within the Wilbur database, as we had been told that users were still trying to use that instance of the application. We found that our email message (which goes to Mike) seems to be working properly whenever someone tries to launch the application. Now that we're seeing extremely minimal "hits" to the Wilbur database, we expect to bring it down within the next few days. While we were checking Wilbur, we decided to take a look at the tablespaces using the OEM interface. We saw very few actual tablespaces, most of which are in fact owned by the system and needed to run and maintain the Oracle database. We also noticed that the Web tablespace was very large, as it is really the only one configured for general use.
Also, Mike and I met on Wednesday to upload new data into the Viking application, used for the Alumni database. He had two sets of data to upload; he explained the steps as he completed the first data set, then had me work through the process for the second set. We had to upload the actual file through a UNIX session, then we inserted the data into the actual tables through the PL/SQL Developer interface using control files.
In a break from tradition, Mike and I had our Staff Development meeting on Friday this week!
I gave Mike a demonstration of what I have accomplished this week with the Scheduler project. Earlier in the week, I had placed some error-checking logic at the wrong place within one of my packages. This resulted in some processes that Mike (as DBA) had to terminate for me, as they were using way too many resources on the Oracle server. Mike also showed me some of the steps he had taken to terminate these processes; he had to actually go to a UNIX telnet session as well as work with Oracle Enterprise Manager to figure out which processes needed to be terminated (killed).
Mike also showed me a test package where he has used DHTML and cascading style sheets (CSS) in a test environment. He has made extensive use of DHTML and CSS within the SWAMP application.
For this morning's meeting, Mike and I were able to apply some of our knowledge gained during past Staff Development times in order to resolve a minor problem with the COED schema residing on Aero.
First, we decided to ping Aero's Apache server and noticed that, while most packets were sent and received successfully, about 10-12 percent were lost. We wondered if this wasn't indicative of some larger problems within Aero.
Using UNIX commands, Mike checked the contents of a log file and noticed that it was quite large. In order to trim the file down to a manageable size (listing only the most recent events), he first made a copy of the file, then used the Vi editor to edit the copy. As it is quite difficult (impossible) within Vi to mark a large block of content for deletion, we decided to copy the file to the local PC and edit with Notepad. This was successful, and we were able to isolate some incidents.
According to the log, the COED schema had run out of disk space; using OEM, we were quickly able to increase the allocation. With the added space, COED is only using 75 percent of its space allocation, and ping reports that 100 percent of the packets are sent and received successfully. We'll be monitoring the disk usage of COED over the next few weeks, and can quickly add space again if needed.
During our meeting this morning, Mike showed me some of the Javascript code that he has recently implemented within the SWAMP application. He has also figured out how to manipulate an internal array system that Internet Explorer uses when displaying forms with columns that are two or more rows in length. I definitely need to take a closer look at his work, as I was very impressed by all of this.
Because of vacation leave and other meetings the last few weeks, it's been some time since Mike Ross and I have worked on Staff Development projects. In our meeting this morning, we talked about some changes that Mike was working on within a SWAMP report; he was in the midst of that project, so this was a good opportunity to take a look at some code. We actually devoted most of our meeting time to the continued investigation of Oracle analytics. Mike found a coding example of how various totals can be calculated when dealing with partitions of data within a table. The example calculated a running total as well as a department total that was reset to zero when the department number changed. In a test query against a SWAMP table, Mike easily implemented the concepts presented. Here's a link to the article; we focused on the first example that calculates a running total.
Mike Ross found a very good slide presentation that gives an introduction to Oracle analytics. Analytics are an extension of SQL and are used within that layer, and not within the PL/SQL language in general. They greatly enhance the functionality of SQL.
From the slide presentation:
"Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group."
"Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. Therefore, analytic functions can appear only in the select list or ORDER BY clause."
The presentation gave some excellent examples of the use of analytics to find minimum and maximum values, as well as different ways to rank values, including rank and dense rank (when duplicates exist).
We look forward to working with some of our existing code to put some of this new-found knowledge to good use.
Here's a link to the presentation.
http://soug.acomp.usf.edu/TechDay2006/Lewis_Cunningham-Oracle_Analytics.pdf
In an attempt to give proper credit where credit is due, the author is David Wong. The presentation was made available by Lewis R. Cunningham of Sheperd Systems and the Suncoast Oracle Users' Group.
Mike was interested in my Scheduler application, so we took some time this morning so that I could give him a demonstration of the functionality I currently have in place within the Administrative module. I showed him the table I'm using for authentication purposes and worked through several scenarios to illustrate the security and permissions I have developed thus far. Mike would like to establish some better security within some areas of the Swamp application, so I think this time was well-spent.
Next, we began to investigate a new area of Oracle's Metalink support system called My Configurations and Projects. We watched a short video that gave an overview of this service and the coordination of project support and the submission of service requests to Oracle in a more efficient manner. In order to use this service, we will need to install Oracle Configuration Manager (OCM). We will spend more time with this project in the coming weeks.
As I had a meeting on Thursday during our normal Staff Development time, Mike Ross and I decided to meet this morning. This provided a good change-of-pace on an otherwise quiet Friday during these Dog Days of Summer.
We took a few minutes to review where we stand with the allocation of storage for the tablespaces on Aero. We saw no new surprises in terms of tablespaces approaching their quotas, so that was good. Mike logged in to Aero through telnet in order to take a look at some of the error logs used to track problems with the database, including deadlocks with packages and queries. Mike was particularly interested in a recurring problem with the online course scheduling application and the accurate reporting of empty seats reported within the real-time class schedule. No new surprises here, at least for now, so we'll likely investigate this further when fall registration picks up.
I came back to my office and did a little research for my Scheduling application. We would like to have the capability to send text messages to students as reminders of upcoming appointments. I found a couple of services that allow you to send a text message via email to any cell phone number. It should be fairly easy to implement this functionality.
Mike Ross and I met with Michael Ellis and John Rushing for most of yesterday's Staff Development time. Michael was having difficulties getting a package to compile; it appeared that his package couldn't see another package that he was calling within another schema. In explaining everything, Michael actually solved the problem himself. The package he was calling (within another schema) just happened to have the same name as the schema from which he was trying to compile the originating package. He was quickly able to "fix" this, and everything compiled fine.
This was a very good meeting. Michael also shared with us some details about his current programming projects. There may be future opportunities for all of us to work together in database administration.
For our Staff Development time this morning, Mike Ross and I worked on a new report within the SWAMP application, used by the Physical Plant. He had been working on a new report to track postage and mail room charges. We resolved some problems with the queries, worked through some "date-arithmetic" needed to retrieve charges for the previous month, and changed the decimal precision for the dollar amounts to allow up to three decimal places. Mike still has a little fine-tuning left in order to finish the report, but I think we made good progress this morning.
Mike Ross and I continued our exploration of Oracle Enterprise Manager (OEM). We learned how to analyze a tablespace in order to find out if there are any problems with the actual tables. For the smaller tablespaces, everything looked fine; in analyzing some of the larger tablespaces (such as "swamp" and "sir" (which contains one very large table)), everything looked fine. Finally, we decided to be a little more adventurous and dove into the "web" tablespace. As this is a large, heavilly-used tablespace that contains many tables, the analyzer tool found some tables with some issues. Next week, we will likely investigate how to "fix" the issues that are reported. OEM is a great tool!
Mike Ross and I met this morning and worked once again with Oracle Enterprise Manager (OEM). We continued working through the Aero schemas. In a couple of instances, we were able to reassign significant amounts of space from what we had reclaimed from those schemas with extremely low utilization.
Today I continued my exploration of Ubuntu. I received an email from Troy Knabe as a comment to my blog entry for last Thursday. He offered a suggestion for how to gain root access in order to install updates. I'm still having difficulties, however, as it looks like I can gain root access through a terminal window, but I'm still being prompted for a password when trying to download the updates. If I enter the root login password, an error states that I can't login as root; if I enter the password that goes with my login, nothing happens.
At this point, I believe my best option is to abandon version 5.10 in favor of a clean installation of 6.06; most of the documentation I've seen has been written for 6.06. Working with Ubuntu thus far has been a great learning experience.
Today I continued my exploration of the Ubuntu Linux distribution (version 5.10).
Most of the time when installing software, if given a choice, I go with a "custom" setup. I like to do this because, if nothing else, it gives me a better awareness of the options available, even if I don't make changes to those presented by the installer. Today I decided to work through another custom installation of Ubuntu.
One of my goals was to actually configure a "root" login (this wasn't a choice in the standard installation I completed a couple days ago). Today's custom installation worked just fine (as I have had problems with my 24-inch LCD monitors with previous installation attempts, I borrowed an 18-inch LCD display).
Everything worked fine until I tried to download and install the 152 updates that Gnome announced were available. When I attempted this, Gnome prompted for my password, which I entered. At this point, nothing happened. I then tried to do the updates manually. As I am new to Ubuntu and Gnome, I didn't know how to do this. I talked to Ron about all of these obstacles, and he gave me some suggestions. None of these worked, however, as I have concluded that my login doesn't have the necessary permissions. It also appears that Ubuntu won't allow root to login to Gnome. I need to do some research and learn how to either login to Gnome as root, or to change the permissions of my login to allow the downloading and installation of the updates.
The next time I work with Ubuntu, I'll either put more research into these items, or I'll reinstall again, this time with version 6.06. I doubt this upgrade will solve these issues, however, so I'll still need to do some research.
In my
blog entry of June 8 documenting my Staff Development activities that day, I discussed my attempt to install version 5.10 of Ubuntu Linux. I decided to work on this project again this morning. Today's installation was successful!
First, I decided to re-install Ubuntu from scratch, choosing a standard install. This was, of course, somewhat faster than the custom installation I had attempted earlier, as I didn't have to work through all of the detailed choices. This time, when I rebooted and tried to launch Gnome, I got the same error message, seeming to indicate that my graphics mode was not not compatible.
Of course, I have two Dell 24-inch wide-screen LCD monitors on my desk. (These monitors are absulultely fantastic, by the way.) Somehow, in its initialization, Gnome is unable to render these dimensions. I then borrowed a standard 18-inch LCD from Paul and tried once again to boot and launch Gnome. Once again, no luck.
I decided, at this point, to try reinstalling Unbuntu from scratch once again, using the 18-inch LCD. Everything worked fine when I rebooted and launched Gnome; the GUI seemed to initialize correctly this time. I successfully logged in and tested Firefox, all with success. I saw that there were 158 updates available for download, so I selected all of these. Within 5-10 minutes, these were successfully installed. I then decided to try my analog 24-inch LCD again, so I shut down, connected the display and rebooted. This time, Gnome indicated that it was adjusting the dimensions for the display and launched successfully.
Today Mike and I investigated the use of the Oracle Enterprise Manager (OEM). We decided to look at the disk space allocation and utilization for the namespaces. Many of these are only using a very small percentage of their allocated disk space; we discovered that we can quickly modify these settings. We were able to reclaim a significant amount of disk space that had been unused and that we thought would be very unlikely to be used in the future. For example, many namespaces use only about one percent of their allocation. For several of these, we decreased the allocated space by 90 percent, thus increasing the utilization to about 10 percent. In the future, we can very quickly add space to the allocation when needed. For a few namespaces, we used some of the reclaimed storage to add space. We will continue with this project next week, as we have only finished to the letter H in the alphabetical listing.
Ubuntu Linux 5.10
As Mike Ross is gone this week, I worked on another project for today's Staff Development time. I decided to install Ubuntu Linux, version 5.10, on a CPU that I borrowed from Joe. I had the ambitious goal of completing the installation of Ubuntu as well as the Firefox browser, and actually writing this blog from that environment. That, apparently, was too lofty of a goal, as I encountered technical problems.
I decided to go with version 5.10 (not the current 6.06) of Ubuntu for the following reasons:
- I have a CD handy that contains an ISO image of version 5.10.
- I can upgrade to the current version as a later project.
I completed the entire installation process (I chose a custom install), working through the various options screens. The installer copied everything to the hard drive that I had selected (literally, everything), rebooted and copied the remaining packages.
Everything seemed to work fine until I rebooted after the installation had finished. With this reboot, the "splash" screen appeared, then an error message when Ubuntu apparently tried to load the Gnome GUI. At first I suspected that something was incompatible with my 24-inch wide-screen LCD monitor. I pressed the power button on the CPU and, while the operating system closed files to prepare to shut down, saw a command login prompt. I couldn't login, however, because the operating system was shutting down.
I went through this process a few more times, going into the GRUB boot loader to see if there were any other boot options (I couldn't find any). By this time, I decided to borrow a smaller monitor from Joe and see if the problem could be resolved. This made no difference.
Finally, I loaded GRUB again and chose to boot into recovery mode. This, at least, gave me a command login prompt. I was able to login as root and as the user I had created for myself. Once logged in, I tried to run the Gnome GUI, but didn't know the proper command (or if this was even possible within the recovery mode).
In summary, in order to move forward with this project, I'll need to do some research. I'll probably install Ubuntu from scratch again with a smaller monitor and see if that makes a difference.
For our Staff Development today, Mike and I decided to examine how the Oracle tablespaces are organized. When applications were migrated from Wilbur to Aero, many tables were placed in the WEB tablespace. In the next few days, I plan to work with several applications developed by the Library and Media Services department to segregate the tables into a different tablespace.
For yesterday's Staff Development time, Mike and I continued our study of Oracle database tuning and decided to take a look at Oracle Enterprise Manager. Our major accomplishment was discovering a very easy way to manage (and increase) the storage allocation for any given tablespace. This was something we learned completely by accident; in viewing a particular screen, we decided to just try to increase the storage allocation, and it worked. This knowledge will be very useful, as we will be able to make these changes quickly when needed. We also began to investigate Oracle Management Server and Oracle Intelligent Agent. We plan to put more research into these topics next week.
Mike and I continued working with the SIR.RAW_DATA table and our test _P and _P2 versions of that table (each with well over 1 million rows, only a few columns, most of which are numbers) to see whether or not we could improve the speed of some test queries. We decided to try analyzing each table and then compare the speeds of various queries. The table SIR.RAW_DATA has several indexes; the other versions of the table only have two indexes.
Our results were "all over the map". Sometimes our queries of the non-partitioned table were faster; in other cases, the partitioned tables resulted in faster queries. Our results thus far have been non-conclusive.
We both agree that, if we could only have one tool at our disposal for use in "tuning" a table, that one tool would be the capability to index, even at the point of overkill.
Today Mike and I experimented with partitioning an Oracle table. We chose the table sir.raw_data, which is part of the SIR Reporting Application that Paul and I are developing. This table contains over one million rows. Simply put, our goal was to find out if we could improve the time and efficiency of some of our queries.
First, we created a new table, sir.raw_data_p, with the appropriate columns, and configured the partitions -- all in one SQL statement -- as follows:
create table sir.raw_data_p
(QUEST_NUM NUMBER,
QUEST_VALUE NUMBER,
YEAR NUMBER,
CRN NUMBER,
SURVEY_NUMBER NUMBER,
TERM NUMBER)
partition by range(crn)
(partition part_1 values less than (20100),
partition part_2 values less than (20400),
partition part_3 values less than (20800),
partition part_4 values less than (30000),
partition part_5 values less than (30300),
partition part_6 values less than (30700),
partition part_7 values less than (31000),
partition part_8 values less than (200000));
Next, we inserted rows from the original table:
insert into sir.raw_data_p
(select * from sir.raw_data);
Finally, we created an index to the partitioned table on the CRN column.
In our simple test queries, we really didn't see a huge benefit of this partitioning. This table data, considering the CRN field, is heavilly concentrated within about 175,000 rows.
I hope to do more testing in the next few days to see if I can improve the efficiency of partitioning for this particular table. Hopefully our skills will improve over time, and we will be able to put this knowledge to good use.
On Tuesday of this week, as part of my own "staff development" time, I spent most of the morning viewing several movies available on the VTC site. The advanced Oracle DBA/tuning movies that I viewed were part of the course that Mike Ross and I have been working through in recent weeks. A couple of major highlights for me included seeing an example of how a table can be partitioned, as well as a review of the use of the STATSPACK utility. I believe this time was well-spent, as I felt better prepared for today's staff development time.
For our Staff Development time the last two weeks, Mike and I have viewed several VTC courses dealing with Performance Tuning. The instructor demonstrates the very detailed steps needed to gather statistics and begin to evaluate the performance of a database. I have reviewed a few of the courses on my own as well. I believe I'm beginning to gain an understanding of these concepts. Mike and I both feel ready to do some testing with our databases on Wilbur and Aero.
For today's Staff Development time, Mike and I worked through a VTC training course covering the use of SQL Trace and TKPROF to evaluate the performance and efficiency of an Oracle database. In the course, the instructor demonstrated the step-by-step approach involved in evaluating database queries. The demonstration was very methodical and detail-oriented. Having viewed this material and having worked through a similar exercise with our own Oracle database, I believe I'm just starting to gain some minimal understanding of these very technical concepts. To prepare for next week's Staff Development time, I plan to take time to view the VTC course again.
In our exploration of Oracle database tuning, Mike and I were successful in capturing a snapshot of activity on Aero, our Oracle server.
To accomplish this, we opened the SQL Trace facility, configured it to create a trace file, and let it run for a few minutes while we ran a couple of simple queries. After we closed SQL Trace, we had to find the file that was created. Once we had the file, we used TKPROF to format it for readability of the text.
Looking through the contents of the trace file, we saw the queries we had executed, and tried to analyze the numbers, which really didn't mean much to us at this point. For next week, we plan to test things with more complex queries.
Our goal with this project is not only to see where we can improve the performance of existing PL/SQL applications, but to develop future applications in a more efficient manner.
Mike and I learned yesterday that Troy has RMAN working within a test Oracle 10g environment. Therefore, we have decided to postpone further exploration of RMAN until a later date when we have access to a working 10g environment. Mike and I are both interested in looking into performance tuning of our database, so it looks like this will be our next focus for Staff Development.
For all practical purposes, Mike and I are finished with our Wilbur-to-Aero migration/cleanup project. We are considering several interesting projects in the coming weeks, including the investigation of analytic functions within Oracle. Our long-term goal is to become familiar enough with these concepts so that we can do more with SQL and have our applications run more efficiently. We'll likely get started with this project in earnest during this week's Staff Development time. We are also very much interested in installing and using RMAN Recovery Manager within our Oracle environment. When implemented, this will enable more quick and efficient recovery of data when needed. We alss discussed possibly installing a version of Linux in a dual-boot environment on a Windows-based machine. This has always been an area of interest for me.
At this point, Mike and I are finished with most of the details of the Wilbur schemas and the migration details. The only major outstanding application (that we are aware of, at least) that needs to be migrated to Aero is the Admissions schama. As noted in earlier blogs, Summer is taking care of this by redesigning a new application on Aero.
Mike and I decided to develop a WIKI page that will list applications and their Aero URLs. Our thinking is that this will be a handy tool for the Service Request Desk staff and students as they provide customer service. I will meet with Joan Guralnick on Monday to show her the page.
For the next project, Mike and I hope to do more Oracle database administration. This will be an excellent opportunity for me to learn from Mike, our Oracle Guru!
Mike Ross and I continued our Oracle migration project. We made a copy of our "move" procedure to a new "msg" procedure. This will be used in a few strategic applications to display a message for users to contact either Mike or myself when launching the Wilbur-based application. Through the message page, the user is also notified that the application has been moved to Aero and that we will provide assistance to ensure users get to the Aero-based application. This will help us to determine who may be using an application that we might have othewise decided to delete.
I also showed Mike what I know about LDAP authentication and some of the techniques I have used to verify LDAP authentication to an application. Mike is considering using LDAP authentication with some of tha SWAMP applications.
Wilbur -- Aero Migration Project
Mike Ross and I continued our project of going through the Wilbur schemas to determine if they have been migrated to Aero. We have now reviewed each schema and have a better idea of the general state of things. We plan to continue with this project in the coming weeks, but I'm not entirely sure about the next phase.
General Tasks
We also created a new tablespace for me, goodeld. As my schema had been originally created when I worked in the Library, all of my tables were placed in the library tablespace. Having my own tablespace will be better for consistency. Mike also created a new schema, SIR, which we will use as the production environment for the SIR Application.
Upcoming Project: RMAN Installation
Mike has said that he would like to install and configure RMAN on Aero once we're finished with our migration project. RMAN will give us much greater capability to recover objects when necessary. This project will provide an excellent opportunity for me to learn more about the maintenance of our Oracle database environment.
Wilbur -- Aero Migrations
Mike Ross and I worked on this project again today and continue to make good progress. We're still reviewing each Wilbur schema and editing what we believe is the "main" package.
When we know that a package has been migrated, we add a call to our "move" and "mailmessage" procedures. This alerts the user that the application has been moved, gives them the correct URL and sends an email message to Mike as notification that someone has attempted to run the application on Wilbur.
When we're not certain that an application has been moved (including times when we're not entirely sure if an application is even still in use), we simply add a call to our "mailmessage" procedure within the "main" and/or "login" package.
The "mailmessage" procedure has been a very valuable tool for us. When I have specific knowledge of the current state of an application, I use my own "mailmessage", which sends the email to me.
Our expectation is, as users update their bookmarks/favorites over time with the current URLs, that we will receive fewer email notifications that Wilbur-based applications are being visited.
As I have said in past blogs, I have learned a great deal through working on this project.
Mike Ross and I continued working on the remaining Oracle/Aero migration details. Although our progress seems to be slow but sure, we believe the applications are in much better shape now than a month ago. We have replaced our "redirect" procedure call with our "move" procedure call in several applications. I also showed Mike a small change in the "mailmessage" procedure that I found through some research. This change combines the CHR (13) and CHR (10) non-printing characters to send a carrage-return/line-feed at the end of each line. This results in text being printed in the body of the message. Working with Mike on this project has been very helful to me in learning the technical details as well as how the applications are organized on the server.
Wilbur to Aero Migrations
Mike Ross and I worked on several things this morning. Our big accomplishment was making a copy of the "redirect" procedure to a "move" procedure. We have this in place in a couple of packages. When used, this will provide a different "redirect" page where they will see the URL of the application as it resides on Aero. This URL will not be a link, but will be plain text instead. This will force users to update their records (favorites, bookmarks, etc.) to get to the migrated application.
Online Admissions Application
Mike Ross and I spent most of the morning working with the online Admissions application. We fixed several broken links and resolved a problem with HTTPS and the paths in referencing some links. This particular application was developed using WebDB several years ago, and the code is very complex. We will keep working on this project over the coming days, with the goal of keeping the appearance (graphic images) in tact until the application can be rewritten in PL/SQL.
For whatever reason, a few Oracle-based applications were not migrated from Wilbur to Aero last summer. Mike Ross and I worked on these today and made some good progress. We took care of the Purchase Order system, an application for Financial Aid and an application for PCHP.
We took a quick look at the Admissions schema and came to the conclusion that this migration will be more involved. We may work on this during next week's staff development time.
Migration of Safety Applications from Wilbur to Aero
Mike Ross and I met to discuss the details concerning my part of the migration of the Safety schema from Wilbur to Aero, which will take place next Tuesday, December 20. Today's meeting was very helpful for me, as we were able to better clarify my assignment.
I will redirect the login package to Aero. I'll comment out the code on Wilbur and add two lines of code to redirect to the corresponding package on Aero, then send an automated email message to Mike giving the Wilbur package/procedure name. What this means for the end user is that, if they happen to call a link to the login package on Wilbur (perhaps through a link on a webpage somewhere), they will be taken automatically to the application residing on Aero. For the remaining packages within the Safety schema on Wilbur, I'll just add the code to each procedure to send the automated email message; no need to comment out the code and redirect at this time.
I have devoted time most days to the study of Oracle. I recently finished reading the book, Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein (O'Reilly Media Inc., 2002). The book covers the basics of program development with the PL/SQL language. The authors also explore, what I would consider, more advanced topics, such as PL/SQL Server Pages (PSP). PSPs allow the creation of web pages by embedding PL/SQL commands within HTML. For my overall knowledge of PL/SQL, this concept may be useful in the future. In addition, the authors present a detailed discussion of techniques for facilitating interaction and communication with the outside world. Topics discussed here include sending and receiving email within PL/SQL and the database, the use of the mail sender, fetching data from a remote site, and integration with other languages (including Java and C). As a side note, the book makes no mention of the PHP language. In general, a good book!
I am currently reading the book, Oracle9i PL/SQL Programming, by Scott Urman (Oracle Press series by McGraw-Hill/Osborne, 2002). The authors present a detailed discussion of the syntax of PL/SQL, including code examples that are very straightforward.
For staff development this morning, I was involved in several discussions and activities.
-- Planning for Migration of Safety Application from Wilbur to Aero. I met with Mike Ross, Summer Runyan, Brian Wendler and Mike Soukup to discuss this project. Apparently, Mike Ross worked with Will Drury (before Will left) on this migration, and some of the work has already been completed. My task will be to redirect the existing applications, residing on Wilbur, to Aero. Mike Ross and I had a look at the code I will be using for this. He also showed me a procedure he had written that will generate an email message (sent to Mike or I) if someone clicks a link, pointing to Wilbur, that may have been overlooked in the migration process.
-- Oracle Database Troubleshooting. Working with Mike Ross and Troy, we had to restart ("bounce") the Oracle server running on Aero. This was necessary because of a runaway application that had attempted to update a dynamic table with a large amount of data. The developer, a Library staff member, corrected the problem by commenting the bad code.
With the short week due to the Thanksgiving holiday weekend, it was decided that Wednesday, November 23 would be a day of staff development within UCS. Mike Ross and I spent much of the day continuing our adventures with PHP and Oracle database connectivity. Although we still have some ground to cover, as well as some issues to resolve, we believe we are getting closer to our goal of using PHP to query an Oracle table, manipulate data and generate useable output.
We have successfully established a connection to an Oracle table using PHP's PEAR extensions, but are running into difficulty when trying to use the OCI library. Our test code generates a fatal error when we try to call the oci_new_collection function.
We also met with Bill for a few minutes to see the Zend development environment, which he is evaluating with PHP. Although this product is somewhat expensive, it may be worth consideration if we decide to pursue PHP development for the long term.
We hope to continue with our project this week, with the goal of conquering the fatal error. For another staff development project, however, we hope to complete the migration of a few critical PL/SQL applications from Wilbur to Aero within the next week or so. These projects may be the priority this week.
PHP Tutorials, Continued
Mike Ross and I continued our exploration of the PHP programming language. We successfully queried the Oracle database that we connected to last Thursday. The query turned out to be quite simple -- removing a comment from a print statement included in the code that Ron gave us last week.
We also explored some additional PHP tutorials at zend.com. We read entries introducing arrays and security concerns (preventing SQL injection code).
At this point, we both would like to examine PHP in more detail in order to better plan how we might use it as a project development tool with our Oracle databases.
Mike Ross and I continued working through our evaluation of the PHP programming language. Our goal this week was to try to connect to and query an Oracle database.
Ron Swartzendruber was a great help; we borrowed two of his PHP books, and he gave us some sample code. With very little tweaking, we were able to quicky connect to a schema residing on Aero and query a table.
Because of our department's significant investment in Oracle over the last several years, we believe PHP will offer greater flexibility in future project development. MySQL, the open-source database widely used with PHP, will also be a viable option when we consider future needs for truly open-source project development.
For this morning's Staff Development time, Mike Ross and I began working through a tutorial in the use of the PHP programming language. We examined several things:
Development of a Hello World! Application. We thought it appropriate to keep with tradition!
Environment Variables to Determine the User's Browser. We tested this with Internet Explorer and Firefox.
PHP Environment. We copy-pasted code from the tutorial to show the specifics of the installation of PHP.
Forms. We copied an example that shows the development of a simple form. This is very powerful.
Here are the test pages we developed.
Mike Ross and I worked with Shaun on the continuation of the Solaris 10 installation on the Sun CPU. Shaun had to take care of some localization configurations (the list of UNIX commands took up almost an entire page) in order to bring Solaris into compliance with the department's security standards, as well as to optimize the system in preparation for the installation of Oracle 10g.
When attempting to reboot after making the configuration changes, Shaun could not login as root. Travis came to the rescue, suggesting that Shaun boot into single-user mode and making some changes.
We look forward to tackling the Oracle installation when we meet again next Thursday. It'll be good to have an Oracle 10g test environment at our disposal.
Today saw the first installment of our dedicated weekly staff development time within University Computing Services. I participated in Shaun Gatherum’s demonstration of the installation of the Solaris 10 operating system on a Sun Blade 1500 CPU. Other staff members participating in this session included Ron Swartzendruber, Summer Runyan and Joe Crowe. Travis Knabe was also a great resource, graciously offering useful advice and technical expertise when appropriate. Summer had good notes that she had developed during past installations of Solaris through a “flash� configuration on her Sun CPU. In addition, Google was helpful as we worked through a few challenges. Shaun successfully installed Solaris 10 today and he plans to install Oracle 10g next week.
Dedicating a set time each week for staff development is an excellent idea. This will provide me with an excellent opportunity to learn new skills and concepts, as well as to develop a greater awareness of the kinds of projects being managed within UCS.
