My replacement laptop arrived this week to fix an occasional problem that I had with the old laptop:

I thought that I would perform a test on the new laptop, so I thought that I would try a slightly modified version of the script that appeared in the “Expert Oracle Practices” book in the Buffer Cache Hit Ratio section of chapter 8. The table creation part of the script tends to be very CPU intensive for an hour or two, so if you want to try the script, set aside plenty of time.

The script builds a table that has 100,000,000 rows, displays the execution plan for a SQL statement that selects a small percent of the table when the OPTIMIZER_INDEX_COST_ADJ is set to 100 and again at 5, and finally excutes the SQL statements with a 10046 trace enabled.

So, the optimizer is predicting that an execution using a full table scan will complete in 44 minutes and 29 seconds, while an execution using an index range scan will complete in 40 minutes and 25 seconds. Let’s see how the different execution plans performed.

The full table scan when selecting 2.55% of the rows in the table required 14.39 seconds while the index range scan required 1 minute 24.36 seconds. I guess that is not a bad improvement for leaving the OPTIMIZER_INDEX_COST_ADJ parameter at the default value of 100. But wait, the full table scan was CPU bound, not IO bound! What if we allow the SQL statement to use more than 1 CPU?

Here is a screen capture that was captured as the full table scan ended (I had to execute the above several times so that the screen capture showed the very end of the execution):

That helped a little, the full table scan completed in 12.01 seconds and used about 24% of the CPU capacity. Interesting that the screen capture shows that the network utilization was 207Mb/s – the laptop was connected to a wireless connection with Oracle Database 11.2.0.1 running locally. The full table scan might be IO bound now (note that there were only two datafiles, so I supposed that we could still be CPU bound if only two parallel processes could be used).

Let’s see the storage requirements for the table and index (ASSM AUTO tablespace):

It might be somewhat interesting to mention that somehow we ended up with one odd 59MB extent in the table, otherwise the extent allocation followed the typical pattern on 16 extents at 64KB, 63 extents at 1MB, 120 extents at 8MB, and then 64MB extents.

It might be a good idea to take a look at the TKPROF summaries for the executions. Just as a reminder, the normal (non-parallel, serial) table scan required 14.39 seconds, the parallel table scan required 12.01 seconds, and the index range scan required 1 minute 24.36 seconds.

TKPROF output full table scan: elapsed time 6.18 seconds (8.21 seconds less than what was reported by SQL*Plus) with 5.28 seconds of CPU time:

Numbers from the twilight zone? But wait, there is more. The numbers were so illogical that I took the database offline, rebooted, and was immediately greeted with a warning that the RAID 0 array in the new laptop failed! Try this experiement at your own risk.

————————————-

So, what have we learned from this blog article? To get you started, what was the buffer cache hit ratio for each of the executions? How can you explain the decrease in CPU time when parallel execution was used?

—-

Edit August 14, 2010: In case you are wondering, the Oracle database was running on the laptop, not a remote server. The 11.2.0.1 database was configured with Oracle’s default parameters, with the exception of SGA_TARGET which was set to 12G and PGA_AGGREGATE_TARGET which was set to 1800M. Oracle Database on the Windows platform supports only direct, asynchronous I/O. Windows 7 Ultimate running on the laptop does not offer large memory page support, unlike the Server type Windows operating systems.

The laptop has a lot of nice features. The only faults that I have found with it so far are:
* It needs a SATA 3.0 spec (6Gb/s) hard drive interface
* The slim power supply (power brick) for the laptop is about twice as wide and 1.5 times as long as the power supply for a Dell Inspiron 9400/1705 laptop – for comparison, the power supply for the Dell Inspiron 9400/1705 is probably twice as large as what is found on Dell’s current 17.3″ Inspiron laptops.
* The aluminum case on the laptop has fairly sharp edges and the laptop probably weighs 30% more than a Dell Inspiron 9400/1705 laptop.
* I can’t tell the difference between the high-end LCD screen in the laptop from that of the high-end LCD in the 3.5 year old Dell Inspiron 9400 laptop, other than it has a matte finish while the LCD in the 9400 is glossy.

I was strongly considering the purchase of a Sager branded Clevo X7200 (http://www.clevo.com.tw/en/products/prodinfo.asp?productid=279) with a 6 core desktop CPU (I was also considering the older Clevo 900f with the same 6 core desktop CPU). The reasons why I did not buy the Sager:
* The X7200 was not available for sale at the end of July, and the X7200 page on Clevo’s site was blank.
* The X7200 only offered 1920 by 1080 resolution in a wide-screen 16:9 aspect ratio, and I suspected that the LCD quality was poor like that of the Clevo 900f.
* Offers USB 3.0, but does not offer SATA 3.0 spec (6Gb/s).
* The 300 watt power supply is supposedly huge, although I have not seen it.
* The laptop weighs close to twice as much as a Dell Inspiron 9400/1705 laptop.
* The laptop is probably 50%, possibly 100% thicker than a Dell Inspiron 9400/1705 laptop.
* I was a little concerned about the quality of construction – would it be sturdy enough to be used as a laptop computer, in one’s lap.
* The 12 cell battery only offers about 1 hour of run time.
* Would a laptop this large fit under an airplane seat when in a large enough laptop bag?

Very impressive one! Would you please have a look at this model and give your opinion about that whether its worth to be used for an Oracle professional to buy who would be running VM’s over it,http://www.sony.co.in/product/vpcz128gg

It does not appear that Sony offers that laptop in the United States, but does offer the “Z” series (VPCZ1290X) which appears to have several features in common.

I looked at the link that you provided – it appears to be a very impressive laptop. I do not know much about Sony’s compluter/laptop reliability. I know that the quality of most of their comsumer electronics products (TV, DVD, Blu-Ray player, etc.) is very high.

When reviewing the specifications, this is what I found:
* The Core i7 CPU is a dual core 32nm processor with hyperthreading support. The 32nm processors were introduced in early 2010 and reduce the amount of power needed for the CPU, which should extend battery life. The Core i7 620 in the laptop should be faster than the Core i7 940QM in my laptop when there are only a couple of active processes (maybe up to 4) in the VMs. The Core i7 620 uses 1066MHz (or 800MHz) memory rather than the faster 1333MHz memory – so memory accesses could be a bit slow when you have several VMs running at the same time. The CPU supports Intel Virtualization Technology, which is required by some VM packages (Microsoft’s free VM no longer requires CPUs to offer this feature).http://ark.intel.com/Product.aspx?id=43560&processor=i7-620M&spec-codes=SLBPD,SLBPE,SLBTQ
* The laptop uses 4 – 64GB SSD drives in a RAID 0 array. I do not think that 4 standard sized SSD drives will fit into such a small laptop, so these could be very expensive to replace if they break. Having 4 of these drives in the RAID 0 array will double your chances of encountering the problem that I had with the RAID 0 array in my laptop. In theory, this could be a very fast SSD configuration, offering up to 1GB/s of performance, but 64GB drives are typically 25% to 50% as fast as 256GB SSD drives. The number of drives should help when you have multiple VMs running at the same time if the RAID controller is optimized for performance.
* The laptop has an extremely high resolution LCD (13 inches) in a 16:9 aspect ratio, offering the same resolution as some of Dell’s high-end 17.3″ laptops.
* The laptop comes with 8GB of memory, which is the maximum supported by most laptops running 64 bit Windows. How many VMs will you be able to open if you want to allocate 2GB or 4GB per VM? Will that be a problem?
* Wireless and wired network options appear to offer the current standard for highest possible performance (802.11n and gigabit Ethernet)
* The video camera is low resolution 640 x 480 pixels (0.3 megapixels). This could be too low of resolution for video conferencing.
* The laptop has both a VGA (15 pin D sub) and HDMI (high definition TV) output.
* Offers bluetooth support and USB 2 connectors, no USB 3 connectors.
* Very light weight at 1.43KG (3.15LBS)
* The 5 hour battery will probably provide 3 hours of light use while on battery.

It appears to be a very good laptop, offering a lot of power and capability in a very small package.

Many thanks for looking into the laptop and giving the detailed review. I guess there are different variations for this series which may be there in the United States as I picked it from Sony’s India website. About the performance of the laptops from Sony, there is a known thing about them which is fan’s voice but other than that, they are pretty good and in fact, becoming very popular, at least here in India.

>>The laptop comes with 8GB of memory, which is the maximum supported by most laptops running 64 bit Windows. How many VMs will you be able to open if you want to allocate 2GB or 4GB per VM? Will that be a problem?

I guess that’s the maximum RAM which is coming at the moment in the laptops. I shall not run more than 2 VMs at one time with max of 2gb RAM given to them so I believe, that shouldn’t impact the host machine too much. What’s your thoughts are ?

>> The laptop uses 4 – 64GB SSD drives in a RAID 0 array. I do not think that 4 standard sized SSD drives will fit into such a small laptop, so these could be very expensive to replace if they break. Having 4 of these drives in the RAID 0 array will double your chances of encountering the problem that I had with the RAID 0 array in my laptop.

Okay, I didn’t get this one. How can the SSDs break? Can you please this elaborate more since the laptop is projected for moving professionals and this kind of thing shouldn’t happen for any reasons. I do agree that Sony must has done some unique thing to fit those into such small cabinet.

>> In theory, this could be a very fast SSD configuration, offering up to 1GB/s of performance, but 64GB drives are typically 25% to 50% as fast as 256GB SSD drives. The number of drives should help when you have multiple VMs running at the same time if the RAID controller is optimized for performance.

I won’t be able to do any such tuning myself I guess, at least not without reading about it beforehand. But I am sure, this should be a faster media to work with.

>> Okay, I didn’t get this one. How can the SSDs break? Can you please this elaborate more since the laptop is projected for moving professionals and this kind of thing shouldn’t happen for any reasons. I do agree that Sony must has done some unique thing to fit those into such small cabinet.

I have been reading a lot about SSD drives in the last couple of months. Apparently, SSD drives do not immediately erase data when files are deleted (or replaced). The operating system, specifically Windows 7, will periodically send a TRIM command to the SSD drives when the drives have not been used for a while (maybe an hour), and that causes the SSD drives to start the cleanup process which helps to restore the SSD drive’s speed to nearly new condition. Not all SSD drives support the TRIM command, and if the drives are connected to a RAID array the TRIM command will very likely never reach the SSD drives (I heard that Intel’s latest driver update corrects this problem when the drives are connected using Intel’s motherboards, but I have not located the driver – possibly http://downloadcenter.intel.com/detail_desc.aspx?agr=N&ProductID=&DwnldID=15251). Some SSD drives support a built-in feature called garbage collection that performs a similar task, even when the drives are connected to a RAID controller – the drives themselves determine when to perform garbage collection.

The most common reason for failure appears to be software related – the software that is built into the SSD drives has bugs or is “flashed” by the owner to an updated version. A bad batch of memory chips, faulty capacitor (several 3 year old motherboards in Dell computers have had this problem, requiring a motherboard replacement), exceeding the maximum number of re-writes for a block (this should be rare if the drives are not full, the SSD drive should try to even out the wear on all memory cells), and probably a couple of other causes for SSD drive failure.

I still do not know what happened to my laptop when I restarted the laptop after the test for this blog article – only 1 drive was indicated as attached to the computer following the BIOS post operation. I rebooted again and both drives appeared, but I received a message that no boot device was available. I went into the BIOS setup, where I found that booting from the hard drive was not an option. I rebooted again and the laptop was fine.

I’m wondering about that “even out the wear on all memory cells” in terms of Oracle controlfile and data file header blocks. Does that mean you inevitably get into a state where those blocks are continuously running around the SSD? Just thinking about that wears me out.

The less expensive, higher capacity MLC chips in most SSD devices offer fewer write cycles (by several orders of magnitude) than the SLC chips in some of the higher-end SSD devices (I think that Intel’s SSDs are SLC, although they recently partnered with Micron/Crucial to develop new 34nm chips for SSDs – I believe that Crucial’s high-end C300 line uses MLC chips, so I wonder if future Intel SSDs will use MLC also). It might be interesting to see if a file “update” on a particular SSD model is handled like an Oracle index update, where an index update is actually a delete followed by an insert – and the new index entry cannot occupy the same position as the old (unless there was a commit in between). If that is the case, write leveling might still help. But of course this also raises the question of how datafile updates might be interpretted by the SSD.

It almost sounds like repeatedly updating a file will intentional “fragment” the file, scattering the data all over the drive. It would seem that after a while this should degrade read performance – unless of course read performance is already constrained to a lower threshold by the SATA interface (or PCI Express bus or some other internal limit in the server).

I know I am sounding little fussy here but I am not sure(yet) that whether it would be a right purchase or not and the reason for this confusion is just the SSD. I know its good, fast but may be its just a new piece of technology, which I never used thats why I am more worried. I loved the rest of the part of the notebook, have read couple of other reviews as well, so far , so good! What do you recommend, will it be a right purchase? I am afraid that the HDD model is not there and won’t be there as well so if I have to get, the only choice is the SSD!

PS: I have got my copy of the Expert best practices from Oak Table 🙂 . I had a quick glance at the chapter co-authored by you and Randolf and it just appeared “awesome” . Hope it would sink in my slow mind slowly 🙂 . Congratulations for the book and I hope I shall write a review too soon about it :).

Which book did you read that stated a block read into the PGA could be copied into the SGA? I read that somewhere also, and I thought that I wrote a comment about it, but I cannot find that comment right now. I am disappointed/frustrated that I cannot remember where I saw that statement.

Thank you for posting additional information about the laptop computer. If I understand the article correctly, there are actually only two SSD drives in the laptop (a total of two SATA2 interfaces in use). Each of the two SSD drives is actually composed of two 64GB memory banks that are probably treated lke dual channel SDRAM memory – or an internal RAID 0 array if you want to look at it that way. I understand that some of the SSD manufacturers are creating their SSD drives with a similar feature (treated like dual channel SDRAM memory) to help improve performance. While the manufacturer advertises 256GB of storage space, the actual storage space will probably be between 220GB and 230GB because the SSD will likely reserve some of its storage capacity for use when some of the memory cells start failing. It sounds like the thoughput is roughly the same as a single SSD drive with a SATA 2 connection – about 250MB per second read speed, and probably much slower write performance.

Will 220GB be enough storage capacity for all of the virtual machines’ operating systems, Oracle binaries, Oracle documentation, trace files, and database files? My 3.5 year old laptop has an upgraded 320GB hard drive and only 29GB of storage space available – and there are no videos, MP3s, or other files that take up a lot of room. My new laptop has 2 – 256GB Crucial C300 SSD drives in a RAID 0, and provides 476GB of storage space. The read throughput of one of these Crucial SSD drives when attached to a SATA 3 (6Gb/s) connection is a bit more than 360MB/s, but only achieves about 250MB/s when connected to a SATA 2 connection. For comparison, a fast 7200 RPM hard drive might be able to achive 130MB/s, and might offer between 500GB and 750GB of storage capacity in a size small enough for a laptop (2.5 inch form factor).

I suggest finding one of the Sony laptops and trying to type on it for a while. The keyboards on some laptops are extremely poor quality. My old Dell laptop has a very nice keyboard, even though some of the keys do not make contact very well (this might cause missing letters). My new Dell laptop has a very nice keyboard, but there is a pointer stick (like the old IBM Thinkpad laptops) in the center of the keyboard. It seems like I bump that pointer stick frequently, which causes the cursor to jump and then I start typing into the wrong part of the document. My Toshiba netbook has an OK keyboard – it even has full size keys on the keyboard, but I can feel the keys flex a little when typing. My boss at work just bought an HP laptop, where the keys have square corners, rather than tapered edges – that seems to make it easy to hit the wrong key on the keyboard.

—

I look forward to reading your review of the book. The first chapter than Randolf and I wrote was originally expected to be a quick 5 or 10 page introduction to the different options, and then the remaining 20 or 25 pages was intended to be used to tell when someone should use one method (such as Statspack) or another (such as a review of a raw 10046 trace file) (there was only supposed to be a single chapter). If you saw the first of the two chapters, you probably know that the material for the 10 page introduction idea actually became the strength of the two chapters – the number of pages grew significantly. In my opinion, the 10053 trace and 10046 trace sections are the most impressive parts of the first chapter. Check the typos in Chapter 8 here – most of these typos were caught before printing, but the changes could not be fixed at that point:https://hoopercharles.wordpress.com/2010/01/01/expert-oracle-practices-oracle-database-administration-from-the-oak-table-book/

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: