Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

May 2010

SQL*Net compression

Submitted by Jonathan Lewis on Fri, 2010-05-07 20:21

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a few times. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select lpad(dbms_random.string('U',2),40,'X') v1 from generator [...]

Temporary note

Submitted by Jonathan Lewis on Fri, 2010-05-07 10:04

I’ve just added a link to Graham Wood’s presentation about ASH (v$active_session_history) to my catalogue of Statspack Examples. I’ve also added a link to the “Partition Stats” catalogue pointing to Doug Burn’s latest blog; I’ve also added a permanent entry for partitions to the “Special Links” menu on the right. This note will be deleted [...]

Statistics on Partitioned Tables - Part 6d - COPY_TABLE_STATS - A Light-bulb Moment

Submitted by doug on Fri, 2010-05-07 05:00

I'm pretty self-concious of the amount of waffle that surrounds any technical content here, so let's get the technical bit out of the way first, then the waffling can come later ...

I finally tracked down the mistake I didn't make in part 6a, but thought I'd identified and fixed in part 6b! Here are the two sets of subpartitions for the P_20100209 partition that's the source of the statistics and for the new P_20100210 partition that the stats were copied to. This is how part 6a originally looked

TEST_TAB1                      P_20100209_GROT                NO  22-APR-2010 11:24:12          3
TEST_TAB1                      P_20100209_HALO                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_JUNE                NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100209_OTHERS              NO  22-APR-2010 11:24:13          3
TEST_TAB1                      P_20100210_GROT                NO
TEST_TAB1                      P_20100210_HALO                NO
TEST_TAB1                      P_20100210_JUNE                NO
TEST_TAB1                      P_20100210_OTHERS              NO

and here it is after I'd fixed it.

Concatenated Bitmap Indexes Part I (Two Of Us)

Submitted by Richard Foote on Thu, 2010-05-06 14:17

Although Bitmap Indexes are commonly created on one column, you can create multi-column, concatenated Bitmap indexes as well.   Many of the same issues and factors in deciding to create a single, multi-column index vs. several, single column indexes apply to Bitmap indexes as they do with B-Tree indexes, although there are a number of [...]

Jonathan Lewis and Visual SQL Tuning

Submitted by khailey on Wed, 2010-05-05 23:40

Sometimes, it is a great idea to push away the keyboard when tackling the problems of an ill-performing, complex, query, and take up pencil and paper instead. By drawing a diagram to show off all the tables involved, the joins, the volume of data involved, and the indexes, you'll see more easily the relative efficiency of the possible paths that your query could take through the tables.

-- Jonathan Lewis
Check out Jonathan's insightful article on the power of graphically representing a SQL statement to help identify efficiencies and inefficiencies in a SQL query:

Not something new...

Submitted by tkyte on Wed, 2010-05-05 22:55

But something I see people learning over and over and over again.

I was reminded of a recently asked asktom question - regarding an "intermittent" 'Oracle' bug in a java application ('Oracle' used for sarcasm on my part).

I was reading the current Steven Feuerstein's Blog entry (read that link before going on). It was exactly the same problem found by the java developers on asktom - which they were certain would be an 'Oracle bug' (hint: it wasn't, it was clearly and demonstrably in their code).

Funny, I see the pattern so often that I saw the bug in both bits of code almost immediately. It jumped out and hit my in the face.

SPOILER ALERT: don't read past here if you want to test your ability to find the bug, read Steven's article first.

Maybe I'll put in a request for the to_date function to be overloaded to accept a date as input and just return that date as output.

edit: added after *thinking* about what I just said...

That of course would never work. People are expecting the date format to be applied to the string, so just returning the date could of course NOT be the right thing to do. I guess the overload would have to turn:

to_date( DATE, 'fmt' )

into

to_date( to_char(date,'fmt'), 'fmt' )

instead of

to_date( to_char(date, IMPLICIT_FORMAT ), 'fmt' )

as it does now, but it would be a HUGE change to existing code that 'relies' the way it currently works...

The to_date function takes a string as input and since the date can be converted to a string - it is. I've seen MANY people use:

to_date( date )

to "truncate" a date (horrible idea - not only slow, but RISKY) - it would break their code (not that it isn't already broken) so it would probably be questionable...

Important enough to point out to a wide audience though - beware implicit conversions and watch out for to_date( of a date )!

Row count 2

Submitted by Jonathan Lewis on Wed, 2010-05-05 20:42

Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was [...]

Bugs fixed in the 10.2.0.5 Patch Set

Submitted by Timur Akhmadeev on Wed, 2010-05-05 18:37

As you may already know, the terminal 10gR2 patch set (10.2.0.5) has been released recently on the Linux x86 & x86-64. Today I’ve installed 32-bit version on a test machine and was going to have a look on the Doc ID 1088172.1 for list of bugs fixed in this patch set, but there were almost [...]

Exadata Storage Server and the Query Optimizer – Part 3

Submitted by Chris Antognini on Wed, 2010-05-05 15:55

In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering.
Join filtering is not something specific to the Exadata Storage Server. In fact, it is an [...]

Public Demonstration of PFCLScan in Edinburgh Thursday May 13th

Submitted by pete on Wed, 2010-05-05 12:24

We have demonstrated our database security vulnerability scanner product to a lot of our customers and partners customers over the last few months and the reactions and feedback have been amazing. The product has two major modes, which map to....[Read More]

Posted by Pete On 05/05/10 At 01:24 PM