Smart Scans are part of what Kerry Osborne calls the ‘secret sauce’ of Exadata, and he’s right. Smart Scans can definitely improve throughput by using the various optimizations to reduce the amount of data read and the volume of data returned to the database servers. You really want to have them working on an Exadata machine. But how do you know they’re working? Let’s look at a couple ways to prove you have Smart Scans working for you.
The easiest way to prove you have a Smart Scan working (from the database server side) is to query the V$SQL view (more...)
A common problem surfaced again in an Oracle forum I frequent — a user received a ‘table or view does not exist’ error when creating a procedure against a table he/she didn’t own. A straight select against the table succeeds from SQL*Plus so what is the problem?
It all boils down to privilege in this case. The user has grants on the table through a role, but no direct grants on that table have been made by the owner to the affected user. Since PL/SQL operates a bit differently than SQL*Plus direct grants are a necessity to create procedures and (more...)
Exadata offers many features to improve performance — Smart Scans, offloading, Infiniband internal network — but the one feature not often mentioned is the storage index. Designed to prevent Exadata from reading data blocks unnecessarily its purpose is sometimes not clearly explained, leading to confusion. So what is a storage index, where is it found and what does it do? Let’s see if we can answer those questions, and possibly a few more.
Exadata storage indexes are … indexes … but not in the usual bitmap/b-tree sense. You can’t use one to pinpoint the exact location of a particular record (more...)
In 2008 Oracle introduced Exadata, and data warehousing, business intelligence and OLTP applications have benefited from this integrated technology stack. Exadata is not like anything most DBAs have ever seen or managed; it’s a tuned combination of database server, storage server and private interconnects that truly can outperform commodity hardware configurations. Let’s take a peek inside Exadata and get a glimpse of what Oracle hath wrought.
An Exadata machine is more than just the parts that comprise it. In its smallest configuration the rack houses two database servers running Oracle Enterprise Linux, a storage server managing storage ‘cells’ (running its (more...)
Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:
“Sadly Oracle Support may not do anything about this for two reasons:
1) It’s valid code.
2) It’s not causing another error to surface.
“
(more...)
Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is (more...)
Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.” The definition seems simple enough but it may be deceptively so. To make matters even more confusing to use a collection you must create a database type for it to reference; a varray requires a type as well but (more...)
It intrigues me that some DBAs can be lost without tools like Oracle Enterprise Manager or TOAD, so much so that they can’t complete a task without a GUI. What makes this even more disconcerting is these DBAs can execute tasks that they may be unable to complete absent such tools. If what the tool does ‘behind the scenes’ is a mystery to the users it stands to reason that a user, using a GUI, could do some damage to a database by executing misunderstood tasks simply by pressing ‘buttons’.
When I started as a DBA [the earth's crust was (more...)
Indexes are interesting objects — they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let’s take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn’t be unexpected.
Setting the stage we’ll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index (more...)
Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.
Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It’s gone through (more...)
RSS