Delete vs. Truncate - graphically

This is too funny to not share. Of course, everyone knows that a truncate is much more efficient than a full table delete. On my system, still suffering from a slow log writer, but absolutely quiet at the moment, I ran an insert of a million rows (ctas from tbl1 to tbl2), a delete of those million rows (from tbl2) , and then a truncate of them (from tbl1). I looked over at Enterprise Manager and this is what I saw: (I added the tags for benefit of blog readers)















I had not been expecting such a graphical re-enforcement of the rule. I had found the full table delete in some executing code and was curious how much redo it was generating. I ran René Nyffenegger's script 'how_much_redo' .
Assuming that it is giving accurate results, here is what I got for the 3 operations.

SQL> INSERT INTO T1_DEL (SELECT * FROM T1);

1161874 rows created.

SQL> exec how_much_redo;
New Redo Bytes Written: 408985600 (390 MB of redo TO INSERT)


SQL> delete from T1_DEL;

1161874 rows deleted.

Elapsed: 00:06:30.90 (6 MINUTES TO DELETE)
New Redo Bytes Written: 661496320 (630 MB of redo TO DELETE)



SQL> truncate table T1;

Table truncated.

Elapsed: 00:00:06.80 (6 SECONDS TO TRUNCATE)
New Redo Bytes Written: 815616 (less than 1Mb of redo to TRUNCATE)

Lazy Log Writer - The non Resolution

OK, after the huge emergency, "You can't attend the last day of open world because our problems are too big." After my debugging efforts, how did the problem of the slow writes to the redo log files resolve?

1. Management asked that the entire database be moved to the NAS disks because they initially seemed faster. I had to let them know, that, no, the writes there are not faster.

2. The efforts to cross the great divide and get the storage manager in the other company to actually look at his configuration resulted in, "Since the writes are slow on two different pieces of our machinery, it can't be our fault - it must be oracle. I'm debugging nothing."

3. The other company informed us, "By the way, we will be installing new hardware in a couple of weeks at the same time that you are making a major application upgrade." How does that sound for a prospect of a smooth transition? This was followed by an email, "Claudia, are you working with the other company on this?" Not only am I not working with them, I never heard of it!

4. Management informed me, "Since there will be a hardware change soon, don't bother to follow up on this problem."

So they will install the hardware, we will deploy the new application version, and there will be storm and drama about the excessive waits for redo log writes. I think that I should change my name to Cassandra.

A lazy log writer

I've been laughing because I live 1 block from Moscone Center. It was closer for me to walk between the conference and my house than it was to walk between some of the sessions. Today, I saw the other side of that coin. I got ordered back to work and missed the last day of the conference, (and Chen Shapira's presentation!!!)

What was going on at work? Not much - as in not what should have been. I like looking at the performance monitor on Enterprise Manager for a quick glance at what is going on. It wasn't a pretty picture.



Clicking on the 'Blocking Sessions' tab I saw that that the log writer session was blocking various other sessions.

I went into the alert log and was pointed to a log writer trace file. Inside the trace file I found

*** 2008-09-25 15:28:24.239
Warning: log write time 15460ms, size 6999KB

*** 2008-09-25 15:28:24.836
Warning: log write time 590ms, size 6898KB

*** 2008-09-25 15:28:29.852
Warning: log write time 5020ms, size 6999KB

I looked at metalink and got

" The above warning messages has been introduced in 10.2.0.4 patchset. This warning message will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file .

"These messages are very much expected in 10.2.0.4 database in case the log write is more than 500 ms. This is a warning which means that the write process is not as fast as it intented to be . So probably you need to check if the disk is slow or not or for any potential OS causes. "

We just upgraded to 10.2.0.4. Our storage is across the great divide at another company. We are often short of answers other than, "Everything is configured correctly". With quite a bit of work we have gotten a pair of LUNS allocated for redo logs.

As a test, I moved the redo logs from the SAN to a NSF drive - NOT one that should be allocated to redo. Here was the immediate result:



The log writer waits stopped. Compliments from management. A request from management to storage management to move the entire database to this kind of storage, everyone is happy

almost.

Tonight I looked at the trace file:

*** 2008-09-25 22:53:34.154
Warning: log write time 750ms, size 0KB
*** 2008-09-25 22:53:35.943
Warning: log write time 1770ms, size 28KB
*** 2008-09-25 22:53:39.889
Warning: log write time 940ms, size 0KB

Log writer is taking forever, and it isn't even doing anything!

To be continued..... and detective suggestions welcome!

OOW - Day 2 - Simple things

I spent most of the day in hands on classes. I learned to use the Configuration Management Pack of Grid Control to install patches. It looked really interesting. Now I have to go back to work and figure out how to install Grid Control so I can use it. I assume there will be a fair amount of kicking, screaming, and tearing out my hair in that little task which the Oracle demonstrator waved his hand at, as if to say, "It's nothing". Oh those little nothings.

I then went to a hands on on Oracle Advanced Compression in 11g. The class really was just working through a couple of OBE examples with the comfort of people who actually know the a LOT about Oracle Compression there to help with the silly little problems that people have working through examples. I wish I had had some sort of deep questions to ask about compression. All I learned was, it works. There is some time, and some space savings. It will be useful when we move up to 11g.

When I say that the Advanced Compression was 'Hands On', I mean it literally. Braille would have been more accurate. For some reason, the whole demo was available in a box slightly larger than a youtube video. I get annoyed with myself when I have difficulties with the parts that I think should be the simple parts.. Here were a whole group of Oracle employee experts who couldn't make the VM window expand to the whole screen. There was more compression in the demo than anyone intended!

OOW - Day 1 - Blogger's perks.

OOW'08 - Day 1. I spent the day in the 11g New Features Exam Cram. A couple of people encouraged me to try the exam. I have never touched 11g. I took the course as an intro to 11g, not as a refresher before the exam. If I passed the certification after just this, then the OCP really is meaningless. I prefer to actually know what I'm being tested on when taking an exam.

I happened to be seated next to Fuad Arshad, who apparently does work with Oracle 11g and is ready for the exam. I wish him all the best on the exam.

Fuad's conference badge had a large 'Blogger' written in bright orange on the case. I know that I signed up for one. I asked at the registration desk, and they gave me a 'Blogger' badge too. I lined up for the Keynote Address. Immediately, the ushers pushed me out of the main line and into a smaller line for press & bloggers. I was led to a special area of the auditiorium saved for bloggers - a long table set up so that we could record every golden world of Mary Matalin & James Carville. For Pete's sake! I think this 'Blogger' badge is going to have some strange side effects.

Then this evening I was able to get together with all of the other Oracle Bloggers who I have been following for a year. That was really special. What a nice bunch of people!

travelling trace file

I just noticed a detail that I don't like.

We had an error logged in the alert log. It informed me that details were in
db_ckpt_1234.log. Fine, but there was no such log in the bdump directory.

The dba who was here before me wrote a nice script to run every Sunday to clean up old trace files. What he didn't think of is that the trace files seem to be written per session, and that the checkpoint 'session' continues from instance start up.
His script came along and happily moved the trace file to a backup_bdump directory. Oracle couldn't find it and didn't write the details.

One more fix to make.

NoCOUG conference 8/21/08

For those in the San Francisco Bay Area. The NoCoug Congerence is tomorrow Thursday 8/21. Cary Millsap is the keynote speaker. All are invited.

Summer Conference 2008 Hosted by Chevron August 21, 2008 at Chevron, San Ramon, CA See directions to the conference location near the bottom of this page. If you will be coming, then please RSVP online now. Remember, conference attendance is free for NoCOUG members and $40 for non-members. 8:00 - 9:00 Registration and Continental Breakfast - Refreshments Served 9:00 - 9:30 General Session and Welcome - Roger Schrag, NoCOUG President 9:30 - 10:30 Keynote: “Millsap's Grand Unified Theory of "Tuning"” - Cary Millsap, Method-R.com 10:30 - 11:00 Break Room 1220 Room 1240 Room 1130 11:00 - 12:00 Session 1 “Case Studies in Performance Problem Diagnosis and Repair” by Cary Millsap, Method-R.com “Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros” by Greg Pike, Piocon “Architecting Data Systems for Compliance” by John Weathington, Excellent Management Systems 12:00 - 1:00 Lunch 1:00 - 2:00 Session 2 “Getting Coherence: Introduction to Data Grids” by Raanan Dagan, Oracle “Data Warehousing with Oracle 11g” by George Lumpkin, Oracle Corporation “Building a Web-based Application Using Application Express” by Willie Albino, Lockheed Martin 2:00 - 2:30 Break and Refreshments 2:30 - 3:30 Session 3 “Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager” by John Sheaffer, Oracle Corporation “Introduction to Java-PL/SQL Developers Take Heart” by Peter Koletzke, Quovera “Oracle Fusion Middleware Roadmap & Strategy” by Margaret Lee, Oracle Corporation 3:30 - 4:00 Raffle In the vendor area. 4:00 - 5:00 Session 4 “A Tour of the AWR Tables” by Dave Abercrombie, Convio “What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester” by Arnie Weinstein, Lawrence Livermore National Laboratory “Oracle Analytical Functions - the hidden treasure” by Ron Warshawsky, DBA Infopower 5:00 - ??? NoCOUG networking and happy hour at San Ramon Marriott, 2600 Bishop Dr., San Ramon, CA 94583 -- (925) 244-6171 Directions: Leaving the conference, go straight across Bollinger onto Camino Ramon. Turn left onto Bishop Drive. The Marriott is less than half a mile, on the left. *** Walk through the bar to the outside patio. *** Mark your calendar for NoCOUG's Fall Conference: November 13, 2008 at CarrAmerica Conference Center in Pleasanton. Speaker Abstracts for Spring Conference Keynote “Millsap's Grand Unified Theory of "Tuning"” - Cary Millsap, Method-R.com First there were the v$ and x$ views. And tkprof. Then came bstat/estat, Statspack, ASH, ADDM, OEM, and plenty of third-party tools that look kind of like those tools. There are loads of OS tools too that have been around a long time, like top and sar and strace and pstack and gprof. And then there are the methods: OPIM, and YAPP, and Method R to name three. In this presentation, Cary Millsap gives a brief tour of the moving parts and reveals his own perspective about how all these parts should fit together. Room 1220 “Case Studies in Performance Problem Diagnosis and Repair” - Cary Millsap, Method-R.com While we certainly learn from our own experiences, we can save ourselves plenty of time and frustration if we can learn from the experiences of others. This presentation reviews the real life experiences of professionals like you who faced some nasty performance problems and lived to tell the tale. Come experience their journeys as a spectator so that next time, when you're faced with a similar problem, you may have just the information you need to handle it with greater ease and efficiency. “Getting Coherence: Introduction to Data Grids” - Raanan Dagan, Oracle Grid-based infrastructures are being developed, deployed and used to achieve unlimited application scalability and continuous availability across multiple datacenters. Understanding the additional capabilities of these infrastructures and how they can be improved with the use of Data Grid technology to solve increasingly difficult and complex problems ensures that your organization is getting the maximum utility from Grid Computing. This session focuses on how Oracle Coherence Data Grid can easily help you achieve all of these goals and more! “Power at Your Fingertips - Overlooked Gems in Oracle Enterprise Manager” - John Sheaffer, Oracle Corporation Today's DBA is increasingly tasked with providing greater support across the enterprise - in some cases, being stretched so thin that leveraging automated tools is your only survival mechanism. In this session, you will learn about how often overlooked features of Oracle Enterprise Manager will help you improve efficiency, lower your blood pressure, and get you home on time! “A Tour of the AWR Tables” - Dave Abercrombie, Convio Introduced in version 10g, Oracle's Automatic Workload Repository (AWR) provides diagnostic information for performance and scalability studies, automatically recording a rich variety of database performance statistics. What's the best way to leverage this wealth of data? While you can run Oracle-supplied AWR reports, or use Oracle features such as the Automatic Database Diagnostic Monitor (ADDM), each Oracle database presents its own unique tuning challenges. In this session you'll learn how to work directly with AWR tables, using customized queries to improve insight into your own particular scalability issues. Topics include: Important AWR tables, their contents, how to join them, and their quirks and limitations. Sample queries that can be easily adapted to focus on your own unique set of problems. Estimating the "Average Active Session" metric. Simple statistical techniques to find spikes and other types of anomalous behavior. A comparison of techniques used for historical scalability studies with those used for real-time performance crisis resolution.. Use of DBMS_APPLICATION_INFO and JDBC end-to-end metrics. Useful tips on configuring AWR. Room 1240 “Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros” - Greg Pike, Piocon Although seasoned professionals understand the benefits of solving business problems with efficient queries or PL/SQL, the volunteer experts and Oracle Ace contributors on OTN's SQL and PL/SQL discussion forums raise the query writing bar to an entirely new level. Oracle professionals at any skill level will find this forum packed with a treasure chest of tips, tricks and techniques. With over 60,000 topics and 325,000 posts, it's the mother lode of SQL and PL/SQL education from recognized experts worldwide. In this session, the powerful techniques of advanced query authoring are explored by reviewing real-world forum threads and the unique solutions posted by the gurus. The resident experts from the OTN forums solve problems using a combination of analytic functions, hierarchical queries (CONNECT BY), collections (COLLECT ), XML functions/operators (SYS_XMLGEN, and XMLSEQUENCE), Pipelined Functions, the MODEL clause and more. “Data Warehousing with Oracle 11g” - George Lumpkin, Oracle Corporation Satisfying business intelligence requirements for all users throughout the enterprise requires a fast, reliable and scalable data warehouse to protect and maintain quality business information. Come learn why Oracle is the #1 database for data warehousing, why Oracle Warehouse Builder is the best tool for building data warehouses, how ground-breaking new features in Oracle Database 11g will dramatically speed query performance, and, how you can accelerate data warehousing deployments. “Introduction to Java-PL/SQL Developers Take Heart” - Peter Koletzke, Quovera Oracle's current focus on implementing database and development features based on the Java language may have you thinking that you need to learn Java. However, if you are familiar with PL/SQL, your first view of Java may be a bit discouraging because its object-oriented core makes it look very different. Also, you may be wonder about Java's strengths and weaknesses and where it fits in the industry. This presentation explains to PL/SQL developers who have had little or no exposure to Java, the basic concepts of, and terms used in Java. The presentation provides an overview of the language and reviews the concepts of object orientation upon which Java is based. It also discusses the fundamental Java code structures -- classes and methods -- as well as control statements; exception handling; data types; and variables. This explanation will act as a springboard for further study. Level: Beginner “What the Oracle Really Meant: The Quest for PL/SQL Testing Using Code Tester” - Arnie Weinstein, Lawrence Livermore National Laboratory Software developers face constant pressure to produce highly complex PL/SQL code under tight deadlines. Without an efficient and reliable way to perform thorough code testing, software is released with defects that would otherwise be eliminated. Using an automated test tool carries risks that may be mitigated by certain practices. These practices greatly improved our ability to develop high quality and efficient testing software. In this paper, we will share some of these practices Room 1130 “Architecting Data Systems for Compliance” - John Weathington, Excellent Management Systems A gentle introduction to compliance for database system architects, and introductory concepts for the architecture that's optimal for supporting the growing need for your company's compliance. “Building a Web-based Application Using Application Express” - Willie Albino, Lockheed Martin Application Express is a free web application development environment that comes with the Oracle database (Oracle 9.0.3+). Using a simple web browser, you can create and deploy web-based, database-centric applications very quickly. In addition you can upload and create applications from MS Excel spreadsheets. In this session, an introduction to the Application Express environment will be provided. In addition, an application will be built from tables existing in the database, as well as data uploaded from some Excel spreadsheets. “Oracle Fusion Middleware Roadmap & Strategy” - Margaret Lee, Oracle Corporation With the recent acquisition of BEA, a number of best-in-class products have been added to Oracle's robust portfolio of middleware solutions. Customers are interested in knowing what Oracle's strategy and roadmap will be around protecting existing BEA customers' investments and integrating BEA products into existing Fusion Middleware solutions. This session will detail how Oracle will leverage best products from both companies to provide solutions in Java & Transaction Processing, SOA and Business Process Management, and User Interaction and Enterprise 2.0. “Oracle Analytical Functions - the hidden treasure” - Ron Warshawsky, DBA Infopower Oracle introduced them in 8i, but do we really use them to their full potential? Oracle analytical functional can save great deal of coding and provide amazing results very fast. How about doing this - and this is only a beginning: Ranking data within subsets of a data set. Aggregating data within subsets of a data set. Performing aggregations over moving windows. Displaying and comparing aggregates to individual entries within a single query. Comparing two or more rows within a given data set. If you have suggestions for future meetings or would like to offer feedback on previous conferences, then please complete our online survey or send us an email. Directions to Chevron Park in San Ramon: Address: 6101 Bollinger Canyon Road, San Ramon, CA 94583 Please note that the conference building is just after the Chevron Campus. Upon arrival, enter the building at the East entrance where you'll find NoCOUG representatives ready to sign you in. From Highway 680 South: Exit at Bollinger Canyon Road. Turn left onto Bollinger Canyon Road (heading east over the freeway). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted. From Highway 680 North: Exit at Bollinger Canyon Road. Turn right onto Bollinger Canyon Road (heading east). After passing Chevron Park Circle West, turn right into Bishop Ranch 1. Address 6101 is the first building after entering the parking lot. Park anywhere not restricted. Map

Copyright © 2008 NoCOUG. All rights reserved.