 |
|
|
|
|
|
|
|
|
|
|
|
|
|
All of the following articles may be viewed free of charge. You have permission to distribute these materials within your company; but you may not, however, re-publish any articles without first asking for permission.
Thanks, Mike.
Excerpt:
Often developers are tasked with not only developing functional code, but also with ensuring that the code they develop scales well and performs in the application environment. The tests that need to be performed against developed code are threefold in nature:
- Function testing: verifies that the code performs its proposed function.
- Scalability testing: verifies that the code consumes as few resources as possible while performing its proposed function.
- Goal testing: verifies that the code executes in less time than any specified service level agreement (SLA).
|
Excerpt:
Well, once again I am getting home from a trip abroad, this time it was California, Abu Dhabi and London. After four weeks out I am glad to be back in my messy home office. It has been several months now since Oracle11g was released with much hoopla and fan fare, and as predicted acceptance has been rather flat as folks wait for release 2 this summer to really kick the tires.
I have been working with some of the new features and overall I find them meeting my expectations. As expected SQL Replay and Real Application Testing turned out to be just regression testing tools (good ones, but still just one trick ponies). Automated tuning seems to work well ...
|
The Dash for Flash
PDF Document; 153kb
|
Overview:
In this article, Mike supplies commentary and tech insights to assist Chris Edwards examine whether solid-state flash memory technology can really replace the hard disks that have stored computer data for the last 50 years. Chris Edwards argues that performance gains sound promising in principle.
|
|
Excerpt:
A great deal of attention is being focused on the capability to perform workload replay either at the session or database level in Oracle11g, which is one of the standout features for this release. Benchmark Factory® for Databases from Quest Software has been able to perform trace replays for several Oracle releases with additional functionality that a simple replay doesn’t allow. In the Oracle11g replay scenarios, a single set of SQL statements can be replayed to generate a regression report or an entire database transaction set can be replayed to generate a regression report. While this replay capability in Oracle11g is great for regression work, it leaves much to be desired in the scalability or goal testing arenas. ... In this paper we will examine the use of scalability testing when moving to a new database platform.
|
Using the PIVOT clause in Oracle11g
Microsoft Word Document; 27kb
|
Excerpt:
I see quite a few questions about creating a crosstab table. Oracle 11g has provided the new PIVOT clause which allows this to be done quite readily in Oracle11g. For example, to create a cross-tab on values in the v$object_statistics view you could do the following: ...
|
Are DBAs Necessary?
A DBA retrospective, the changing roles of the DBA since 1990
Microsoft Word Document; 70kb
|
Excerpt:
Since I began working with Oracle in 1990, version 6.0.22, many things have changed. We have seen an explosion in the number and complexity of database features within Oracle and in the size and complexity of the databases built with Oracle.
In the early 1990s a VLDB was considered to be around 10 gigabytes and 500 users. The web, as it currently exists, wasn't heard of. ...
|
|
Excerpt:
Oracle’s Oracle10g requires more memory than was ever required before. If you utilize any of the new features such as automatic storage management (ASM) and automatic shared memory management (ASMM) then you really need to pay attention to what memory is doing in 10g.
Why do I say that you need to really pay close attention? Well, in 10g the ASMM feature will rob Peter to pay Paul as the old saying goes. If it sees the shared pool requires more space, it will take it, if it can, from the buffer cache, and of course, the obverse is also true. ...
|
|
Excerpt:
Hints in Oracle have been around since version 8. Hints are like compiler directives that tell Oracle what path to take when optimizing a SQL statement (generally speaking.) However, Oracle will ignore the hint if it can’t do it or it is formatted poorly.
Most tuning products for SQL will make use of hints if statement re-arrangement doesn’t solve the problem. In later versions of Oracle, outlines became available. You see in many applications ...
|
|
Excerpt:
When I started working with Oracle in 1990 there weren’t many SQL tuning tools available. At most you had the Oracle supplied tools which allowed you to capture an explain plan and minimal tracing. Most SQL tuning consisted of either encouraging the use to a particular index by making sure the column in the index was on the leading edge of the index or, defeating the use of the index through null concatenation to character values or adding zero or multiplying by one for numeric values. I can’t recall what access paths were actually available but not much more than full table scan or nested loops I am sure. ...
|
|
Excerpt:
Well, I decided that Linuxtown wasn’t going anywhere very fast and with the all the SRs I was leaving in my wake I soon would not be welcome by the locals so I decided I would take it on the lamb over to Windowsville for a while. Besides it was looking like the local disk array was going to pin a power supply failure on me.
Let me tell you, they don’t have it much better. If you are expected to be surrounded by happy smiling Oracle applications holding hands in Windowsville on Oracle11g you may have a surprise. My first wakeup call in Windowsville came ...
|
|
Excerpt:
A great deal of attention is being focused on the capability to perform workload replay either at the session or database level in Oracle11g, indeed it is one of the standout features for the 11g release. Benchmark Factory has been able to perform trace replays for several Oracle releases with additional functionality that a simple replay doesn’t allow. In the Oracle11g replay scenarios a single set of SQL statements can be replayed to generate a regression report or, an entire database transaction set can be replayed to generate a regression report. While this replay capability in Oracle11g is great for regression work, it leaves much to be desired in the scalability or goal testing arenas. ... ...
|
|
Excerpt:
I have heard it said that since Oracle will reorder the WHERE clause statements to ensure the leading edge of an index will be used if possible, and, that since the new skip-scan index search method is available, it is not required to try to properly order the columns in a concatenated index. In tests against an Oracle10g database (10.2.0.1) I found this assertion that the order of columns in a concatenated index is not important to be false and can result in poorly performing queries. This paper will show the results from some basic tests to try to disprove this statement. ...
|
|
Excerpt:
Well, the 32 bit 11g beta is officially over with the GA of the 11g 32 bit release on the Oracle download site. I replaced my beta copy with the production release and started playing about 2 weeks ago. I thought you all might like to know some of the gotchas I have run into so far.
Granted, some of these may be my fault and yes, I have filed SRs on them and Oracle has been responsive in troubleshooting them, however on the off chance they are bona fide bugs I would like to give you all a heads up. ...
|
|
Excerpt:
It is getting near the end of summer, school is right around the corner, vacations are coming to a close and everyone getting geared up for Oracle11g. What? You aren’t getting geared up for Oracle11g? Supposedly over 20 percent of you will be jumping right into the deep end with Oracle11g right away, if you believe the hype that is.
Oracle has announced (as of July 11) that Oracle11g is literally right around the corner, due out in August. However, keeping my ear to ground (hard I know from 30,000 feet, but I manage) I am hearing rumbles from betaland that it just may not be so. While a limited, 11g release on Linux 32 bit may be possible in August, few believe a full blown release will occur. How many are using 10g on 32 bit Linux right now, a show of hands? I thought so, I am afraid a limited 32 bit release just won’t pack the punch needed to support a 20 percent implementation rate. ...
|
|
Excerpt:
Defining Oracle good practices is a difficult process. The problem is that there are always exceptions for each good practice. Indeed each good practice has to include its own "IF" and "WHEN" clauses to be valid. Of vital importance are the versions of both the operating system and the Oracle software. All of this being said I will now attempt to list the Oracle good practices I have found to be applicable in most situations. I will break these good practices in several areas:
- Oracle Environment
- Oracle Backup, failover and disaster recovery
- Oracle System Security
- Oracle Design practices
- Oracle Coding best practices
-
Oracle Performance management & Tuning ... |
|
Excerpt:
There are several facets of testing of which each DBA should be aware, these facets determine what type of test is best for a specific question asked of a database or system. For example, how many users will my current system support is a question best answered by a scalability test, while will my new system meet my response time SLA with X users would be a goal type of test. Of course the old is X system better than Y system for Z type of load is best answered by a benchmark.
A Scalability Scenario
A scalability test is usually based on one or more SQL statements; selects, inserts, updates and deletes as well as PL/SQL calls (in Oracle) can be a part of a scalability test. A goal test can also use one or more statements but is usually restricted to the most resource intensive ones. Let’s look at an example scalability test. ...
|
|
Excerpt:
In my last blog entry we looked at sizing tables in 10g, of course the other side of that coin is the sizing of indexes. As with tables the Oracle8 manuals had an explanation of how to size indexes, but manuals since then have been strangely silent on the subject. In this blog I will address the sizing of normal, b-tree indexes since they are still the in the majority as far as type of index used in most applications.
In indexes you have root, branch and leaf blocks. Generally speaking the number of leaf blocks will far out-number the quantity of root and branch blocks for any given index. Usually you can disregard the size requirements of the root and branch blocks since they will form less than 1 percent of a large indexes size. ...
|
|
Excerpt:
On one of my first Oracle consulting assignments I had to come up with the sizing estimates for tables for a data warehouse for a telecommunications company in California. At that time, (1994) there were few products that provided sizing algorithms for Oracle tables and indexes. In fact, all that was available were the somewhat cryptic sizing formula provided by Oracle (discontinued from the manuals in version 9). Not being one to be easily dissuaded, I programmed the calculations into a sizing spreadsheet and I have been diligently maintaining that spreadsheet with each release starting with version 7. ...
|
|
Excerpt:
One of the “hot” new features in Oracle11g is the much touted SQL Replay facility. The SQL Replay facility allows the replay of a SQL workload and the generation of a delta report to show you which statements in the workload improved and which may have regressed due to changes.
SQL Replay sounds good in theory but I found the practice a bit harder. Of course maybe it would have been easier had I been able to use the Database Control in 11g to generate the require SQL Tuning Set (STS) and then utilize the wizards to perform the SQL Replay, but, during the last step of the install the Database Control on the Windows XP release refused to startup properly, complained about security certificates and fell over. So I ended up doing my testing in manual using the PL/SQL APIs provided. ...
|
|
Excerpt:
I remember reading “Brave New World” when I was in high school, we needn’t mention the year. I was fascinated by the system described in the book that seemed so logical on the surface but needed so many props underneath to keep it going. It reminds me at times of some of the Oracle management that goes on these days.
What exactly do I mean? Many times Oracle management tools that look good on the surface are actually held together with bailing wire and duct tape underneath, in another series I read as a young sailor, a character in the book was told to walk up to a skilled Thai Monkey Boxer and just push his shoulder if he executed a particular move, of course the character did so and ended up crippling the much more experienced boxer. The moral there was knowing the way things work, you can break them pretty easily. ...
|
|
Excerpt:
In dealing with computers the one constant is change. We are constantly beset with new and better types of CPUs, larger and faster memories, larger and faster disk arrays. In addition to changes at the hardware level there are upgrades to our operating systems and applications that also must be dealt with. Changes to our hardware, operating system or applications are usually driven by the vendors, the market or by internal forces and are usually a required part of doing business in today’s market place. However, there are also voluntary changes to our environment where some makes a conscience decision to change the way they do something related to computers, their operation or their monitoring. ...
|
|
Excerpt:
I’ve been searching for a definitive answer to the question: “What is the cost of poor database design?” No doubt you have all seen the cost/benefit graphs for fixing application problems and the pyramid showing how up to 90 percent of performance issues in a running database are SQL and index related, but what about the cost of building an application on a poorly designed database that forces poor SQL usage?
No doubt most of you have seen them; databases ported directly from a flat file or legacy non-relational system where no concept of normalization was ever applied. Databases deliberately designed to be flat files, databases designed with deliberate multiple recursion in tables (“flex fields”), ...
|
|
Excerpt:
I am participating in the Oracle11g beta. The next time I tell you I am going to do a beta please lock me up until the fit passes. I have subjected myself to this abuse since Oracle8, usually tied with writing or updating a book. Maybe I am getting smarter as I get older as I am not (currently) involved in a book project although there may be one or two more in the pipeline in the future.
To begin with you need to apply for the program which basically means writing down a detailed project plan including all participants, features you plan on testing and a time schedule for the testing project as well as a commitment to give your first born applications over to Oracle at the conclusion of the test (well, not really, but they do ask how soon you will be releasing a product using the new release.) You must also sign waivers and promises not to discuss anything about the features you test without approval signed in blood from Oracle’s lawyers. ...
|
|
Excerpt:
With the use of RAC on the rise it is time to talk about the use of a feature very under-utilized in Oracle. This feature I refer to is cross-instance parallel query. Many times on site visits I see people using single-instance parallel query, but no one whose system I reviewed has used cross-instance parallel. Some weren’t aware it was available while others assumed Oracle did it automatically.
In order to enable cross-instance parallel query you must specify the INSTANCES parameter in the PARALLEL clause, for example the statement:
ALTER TABLE h_lineitem PARALLEL(DEGREE 8); ...
|
|
Excerpt:
I often see DBAs and developers who don’t know the first thing about tuning SQL. They ask “Why do I need to know that, can’t Oracle tune itself?” The truth of the matter is that Oracle is, for the most part, able to do a pretty decent job of tuning itself if it is given enough information and the queries or tuning tasks aren’t too complex. However, there are times when Johnny must tune.
What do I mean by “enough information”? Well, Oracle utilizes a cost based optimizer (CBO) in most releases (if you have a release prior to 10g, then you will have both rule and cost based optimizer) and this CBO uses what it knows about database objects in regards to size and data distributions to determine the cost of accessing the objects through various paths, ...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|