| |
 |
|
Oracle Database Tips by Donald Burleson
|
Allocating Oracle Objects into Multiple
RAM Data Buffers
Since very few Oracle database can afford the cost
of full RAM caching, many rules have developed for the segregation and
isolation of cached objects. Some of these will provide clues about
the best way to utilize SSD in a solid-state Oracle environment:
Segregate large-table full-table scans
– Large tables that experience full-table scans will benefit from the
largest supported block size and should be placed in a tablespace with
the largest block size.
Use the RECYCLE Pool
– The db_recycle_cache_size
parameter should not be used if
db_cache_size
is not set to the largest supported block
size for the server. Instead, create a
db_32k_cache_size
(or whatever the max is), and assign all
large tables that experience frequent full-table scans to the largest
buffer cache in the database.
Segregate Indexes
- In many cases, Oracle SQL statements will retrieve index information
via an index range scan, scanning the b-tree or bitmap index for
ranges of values that match the SQL search criteria. Hence, it is
beneficial to have as much of an index residing in RAM as possible.
One of the very first things the Oracle 9i database administrator
should do is migrate all Oracle indexes into a large blocksize
tablespace. Indexes will always favor the largest supported blocksize.
Segregate random
access reads - For those
databases that fetch small rows randomly from the disk, the Oracle DBA
can segregate these types of tables into 2K tablespaces. Even though
disk space is becoming cheaper every day, try not to waste available
RAM by reading in more information than is actually going to be used
by the query. Hence, many Oracle DBAs will use small block sizes in
situations of tiny, random access record retrieval.
Segregate LOB column
tables - For those Oracle tables
that contain raw, long raw, or in-line large objects (LOB), moving the
table rows to large block sizes will have an extremely beneficial
effect on disk I/O. Experienced DBAs will check
dba_tables.avg_row_len to
make sure that the blocksize is larger than the average size. Row
chaining will be reduced while the entire LOB can be read within a
single disk I/O, thereby avoiding the additional overhead of having
Oracle read multiple blocks.
Segregate
large-table full-table scan rows
- When the recycle pool was first introduced in Oracle8i, the idea was
to quickly flush the full table scan data blocks which are not likely
to be re-read by other transactions through the Oracle SGA. The result
is reserving critical RAM for those data blocks which are likely to be
re-read by another transaction.
Check the average
row length - The block size for
a tables' tablespace should always be greater than the average row
length for the table (dba_tables.avg_row_len).
If it is smaller than the average row length, rows chaining occurs and
excessive disk I/O is incurred.
Use large blocks for
data sorting – The TEMP
tablespace will benefit from the largest supported blocksize. This
allows disk sorting to happen in large blocks with a minimum of disk
I/O.
These suggestions are very important to the study
of the best way to utilize SSD as an alternative caching mechanism.
However, recent TPC-C benchmarksmake it clear that very-large RAM regions are a central
component in high-performance Oracle databases. The 2004 UNISYS
Oracle Windows benchmark exceeded 250,000 transactions per minute
using a Windows-based 16-CPU server with 115 gigabytes of Oracle data
buffer cache. Here are the Oracle parameters that were used in the
benchmark. The benefit of large-scale RAM caching is clearly shown:
db_16k_cache_size = 15010M
db_8k_cache_size = 1024M
db_cache_size = 8096M
db_keep_cache_size = 78000M
At this point it is very
clear that RAM resources are a very important factor in maintaining
the performance of I/O intensive Oracle systems.
This is an excerpt from the book
Oracle
Solid State Disk Tuning.
You can get it for more than 30% by buying it directly from the
publisher and get immediate access to working code examples.
Market Survey of SSD vendors for
Oracle:
There are many vendors who offer rack-mount solid-state disk that
work with Oracle databases, and the competitive market ensures that
product offerings will continuously improve while prices fall.
SearchStorage notes that SSD is will soon replace platter disks and that
hundreds of SSD vendors may enter the market:
"The number of vendors in this category could rise to several
hundred in the next 3 years as enterprise users become more familiar
with the benefits of this type of storage."
As of January 2015, many of the major hardware vendors (including Sun and
EMC) are replacing slow disks with RAM-based disks, and
Sun announced that all
of their large servers will offer SSD.
Here are the major SSD vendors for Oracle databases
(vendors are listed alphabetically):
2008 rack mount SSD Performance Statistics
SearchStorage has done a comprehensive survey of rack mount SSD
vendors, and lists these SSD rack mount vendors, with this showing the
fastest rack-mount SSD devices:
| manufacturer |
model |
technology |
interface |
performance metrics and notes |
|
IBM |
RamSan-400 |
RAM SSD |
Fibre
Channel
InfiniBand
|
3,000MB/s random
sustained external throughput, 400,000 random IOPS |
|
Violin Memory |
Violin 1010 |
RAM SSD
|
PCIe
|
1,400MB/s read,
1,00MB/s write with ×4 PCIe, 3 microseconds latency |
|
Solid Access Technologies |
USSD 200FC |
RAM SSD |
Fibre Channel
SAS
SCSI
|
391MB/s random
sustained read or write per port (full duplex is 719MB/s), with
8 x 4Gbps FC ports aggregated throughput is approx 2,000MB/s,
320,000 IOPS |
|
Curtis |
HyperXCLR R1000 |
RAM SSD |
Fibre Channel
|
197MB/s sustained
R/W transfer rate, 35,000 IOPS |
Choosing the right SSD for Oracle
When evaluating SSD for Oracle databases you need
to consider performance (throughput and response time), reliability (Mean Time Between failures) and
TCO (total cost of ownership). Most SSD vendors will provide a
test RAM disk array for benchmark testing so that you can choose the
vendor who offers the best price/performance ratio.
Burleson Consulting does not partner with any SSD vendors and we
provide independent advice in this constantly-changing market. BC
was one of the earliest adopters of SSD for Oracle and we have been
deploying SSD on Oracle database since 2005 and we have experienced SSD
experts to help any Oracle shop evaluate whether SSD
is right for your application. BC experts can also help you choose
the SSD that is best for your database. Just
call 800-766-1884 or e-mail.:
 for
SSD support details. DRAM SSD
vs. Flash SSD
With all
the talk about the Oracle “flash cache”, it is important to note that there
are two types of SSD, and only DRAM SSD is suitable for Oracle database
storage. The flash type SSD suffers from serious shortcomings, namely
a degradation of access speed over time. At first, Flash SSD is 5
times faster than a platter disk, but after some usage the average read time
becomes far slower than a hard drive. For Oracle, only rack-mounted
DRAM SSD is acceptable for good performance:
|
|
Avg. Read speed
|
Avg. write speed
|
|
Platter disk
|
10.0 ms.
|
7.0 ms.
|
|
DRAM SSD
|
0.4 ms.
|
0.4 ms.
|
|
Flash SSD
|
1.7 ms.
|
94.5 ms.
|
|
HTML-DB support:
 |
For HTML-DB development support just call to get an
Oracle Certified professional for all HTML-DB development
projects. |
|
