Search

Top 60 Oracle Blogs

Recent comments

Oracle

Oracle OpenWorld 2010: The Oracle Real-World Performance Group

Now that Oracle OpenWorld 2010 is just under 70 days away I thought I would take a moment to mention that the Oracle Real-World Performance Group will again be hosting three sessions.   This year I think we have a very exciting and informative lineup of sessions that are a must-attend for those wanting to see and hear Oracle Database performance insight right from Oracle’s own performance engineers.  Hope to see you there! And for those who are interested, there will likely be many discussions about the Oracle Database Machine and Oracle Exadata.  Very hot stuff! Session ID: S317164 (Monday 2:00PM) Session Title: The Latest Real World Performance Challenges Session Abstract: Oracle’s Real-World Performance Group — the group that first presented at Oracle OpenWorld parallel query techniques with partitions, the index-less database, cardinality challenges with the optimizer, over-processed databases and connection storms — this year presents the performance issues before you experience them and how to plan for future projects with success. All topics discussed in this session come from the Real-World Performance Group’s observations and problem solving. Session ID: S317166 (Monday 5:00PM) Session Title: Real-World Performance Panel Session Session Abstract: This session is your chance, via written questions, to ask a [...]

Installing systemtap on OEL5, update 5

Systemtap is a scripting language for analyzing linux systems. Systemtap needs debuginformation to be able to know what is going on. Systemtap is considered the “answer” to Sun/Oracle’s DTrace. Systemtap and DTrace differ, most notably because DTrace doesn’t need additional software (debug information) for both kernel and userspace. Let’s see how Systemtap can be installed [...]

Writing Some Chapters in a Book

My friend Karen Morton asked if I would be willing to contribute to a book that she is working on (i.e. write a few chapters). Of course I said yes. The book’s title is Pro Oracle SQL and it is to be published by Apress sometime before the end of the year (my first deadline is fast approaching).

Karen is the lead author, but there are also several co-authors involved in this project, all of whom I have a lot of respect for. Here’s the List (in alphabetical order by last name):

Robyn Sands
Riyaj Shamsudeen
Jared Still

Dropping and creating tables in read only tablespaces?!

You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

SQL> create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;

Tablespace created.

SQL> create table test tablespace ronly as select * from all_users;

Table created.

SQL> alter tablespace ronly READ ONLY;

Tablespace altered.

SQL> drop table test;

Table dropped.

I just dropped a table from a read only tablespace! Well, perhaps it’s because that instead of dropping the table was put into recyclebin instead (which is a data dictionary update)? Let’s check which segments remain in the RONLY tablespace:

SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'RONLY';

OWNER   SEGMENT_NAME                    SEGMENT_TYPE
------- ------------------------------- ------------------
TANEL   BIN$ix7rAUXZfB3gQKjAgS4LXg==$0  TABLE

Indeed, it seems that the table segment wasn’t actually dropped. Well, let’s purge the recycle bin to try to actually drop the table segment:

Fully Exploiting Exadata

As a member of the Real-World Performance Group at Oracle I have participated in quite a number of Exadata POCs over the past two years. Often times those POCs are constrained in a number of ways: time, schema/app modifications, etc., because the objective is a proof, not a full blown migration. As a result there is often significant performance that is left on the table just waiting to be fully exploited — the kind of performance that really makes a database performance engineer excited — mind blowing performance. This includes, but is not limited to, data model changes, SQL query modifications and re-engineering batch processes. The reason these types of modifications get me so excited is that design decisions are often influenced by the then current deployment platform and with the Exadata powered Oracle Database Machine those restrictions are frequently lifted. You see, with Exadata the rules change, and so should your design decisions. Sure, you could just pluck-and-plop an existing Oracle data warehouse database onto an Oracle Database Machine and it would likely run much faster than it does on your current system, and you will be wowed, but you very well may shouting four letter expletives describing how [...]

Exadata and Parallel Queuing

Over the years Oracle has added many enhancements in order to allow individual SQL statements to take full advantage of multiprocessor computers. A few months ago Cary Millsap did a talk where he recalled the presentation Larry Ellison did when Oracle first announced the Parallel Query feature. During Larry’s demo he had a multiprocessor computer all to himself. I don’t remember how many processors it had, but I remember he had some kind of graphic showing individual CPU utilization on one screen while he fired up a parallel query on another screen. The monitoring screen lit up like a Christmas tree. Every one of the CPU’s was pegged during his demo. When Cary was telling the story he said that he had wondered at the time what would have happened if there had been other users on the system during the demo. Their experience would probably not have been a good one. I remember having the exact same thought.

Oracle’s parallel capabilities have been a great gift but they have also been a curse because controlling the beast in an environment where there are multiple users trying to share the resources is pretty difficult. There have been many attempts at coming up with a reasonable way of throttling big parallel statements along the way. But to date, I think this technology has only been used effectively in batch processing environments and large data warehouses where consuming the whole machine’s resources is acceptable due to the relatively low degree of concurrency required by those environments.

Cloning prior to patching/upgrading/CPU: what do you do?

In addition to my previous post about cloning, please tell me your experiences and practices. What do you do when altering your Oracle database software? I think a home name should include a FULL version number (so 11.2.0.1 instead of 11.2.0) to indicate the version of the home. If you strictly clone the home before [...]

Cloning your Oracle database software installation

With the coming of the binary version of the installer repository (with Oracle 9 if my memory serves me well), some things changed which did not make everybody happy. The biggest disappointment of most people: it was not supported to tar (or zip, cpio, whatever) your Oracle installation and put it in another place. This [...]

Little Things Doth Crabby Make – Part XIV. Verbose Linux Command Output Should Be Very Trite. Shouldn’t It?

Not all topics I blog about in my Little Things Doth Crabby Make series make me crabby. Often times I’ll blog something that I presume would make at least one individual somewhere, sometime crabby. This one actually did make me crabby. Huh? Was That Verbose? I’m blogging about the –verbose option to the Linux mdadm(8) [...]

The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!