Sometimes statements take longer than you’d like to complete. This can happen for a number of reasons including data changes, table growth, stale statistics and the occasional optimizer bug you haven’t encountered before. When it’s a SQL query that is misbehaving it’s pretty easy to find the cause and, in many cases, fix it. When it’s PL/SQL it isn’t as easy to do. There are views available from Oracle to assist in this endeavor and, (more...)
Managing free space in a tablespace seems to be an easy task, what with the views DBA_DATA_FILES, DBA_TEMP_FILES and DBA_FREE_SPACE avaliable. Those views can provide accurate information for datafiles not set to autoextend, however for autoextensible datafiles they can paint a bleaker picture because they’re based on the current size of the file, not the autoextend limit that file could reach. Let’s look at how those views, without taking autoextend into consideration, can report a (more...)
In yet another Oracle forum (yes, I’m in a number of them) the following question was posed:
I'm reading oracle 9i sql and there is an example of a subquery which I'm trying to write as a group by query. select empno, ename, sal from emp where sal = (select min(sal) from emp); This works but I don't understand why I can't write this as a group by expression select empno, ename, min(sal) from emp (more...)
In a forum I frequent a question was raised regarding an index not being used. The poster is using Oracle 22.214.171.124, which limits the index access paths Oracle can use; a concatenated index is created on the table (which has columns x,y,z,t,q and w, for lack of better names) on columns x, y, and z. Let’s look at what 126.96.36.199 will do with such a situation then explain what (more...)
Exadata is a different system for a DBA to administer. Some tasks in this environment, such as running the exachk script, require root O/S privileges. This script can be run by the system administrator, and this will be the case if you are managing Exadata as a DBA. However, a (more...)
Compression is often mentioned in reverent tones as the cure for database space problems, and properly used it can be just that. However simply throwning compression at a table without careful consideration of what can be accomplished or what behavior to expect afterwards can leave you scratching your head in (more...)
An interesting question was posed on the Oracle-L list a while back; a member was having difficulty getting a constraint created using an existing non-unique index as Oracle would consistently consume all of the TEMP space and cause the process to terminate for lack of resources. The conventional wisdom was (more...)
A question was recently asked by a colleague regarding unique indexes and constraints. He was complaining that the unique index did not create the associated constraint by default. Unlike the situation when a unique constraint is created (the associated unique index is created if no other index is specified) there (more...)
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...)