I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s
SQL> create table T ( ts number not null, c char(10)); Table created. SQL> insert into T 2 select 100+dbms_random.value(1,50),'x' 3 from ( select 1 from dual connect by level < 1000 ), 4 ( select 1 from dual connect by level < 1000 ) 5 / 998001 rows created. SQL> commit; Commit complete. SQL> SQL> exec dbms_stats.gather_table_stats('','T') ; PL/SQL procedure successfully completed.
So I’ve got ~1million rows, with column TS containing decimal numbers ranging between 100 to 150. Now first I’m going to reduce those numbers to integers, via an inline view, and then truncate those numbers to the nearest 100 (the outer statement). Because all the numbers are between 100 and 150, rounding down to the nearest 100, should return just a single row. Lets see what happens…
photo by youflavio
In a recent blog post Eric D. Brown defined an Agile Data Center as
#777777;">An Agile Data Center is one that allows organizations to efficiently and effectively add, remove and change services at the speed of the business, not the speed of technology - Eric D. Brown
In follow up post he said that a Agile Data Center could be implemented by Software Defined Data Center (SDDC) for example using machine virtualization to spin environments up and down.
#555555; padding-left: 30px;">This is a reposting of an old blog post that was on dboptimizer.com but is no longer accessible
#555555; padding-left: 30px;">More trace file analyzer tools at #2970a6;" href="http://ba6.us/node/177">http://ba6.us/node/177
Create an army of clone databases and applications in minutes
Tomorrow Jan 28 we will be installing Delphix on people’s laptops at the BIWA conference at Oracle conference center at Oracle head quarters in Redwood Shores.
Photo by Kevin Dooley
If you are in the Bay Area tomorrow, Jan 27, come see myself, Yann Ropars and #141823;">Yury Velikanov talk about how to leverage social media to boost your career. We will be talking at Oracle head quarters at the NoCOUG/BIWA conference at #444444;">2:30 pm
Why use social media as an IT technician? Because
Delphix is now available for 30 day trial direct download ! ( if you would like a longer version please contact me at firstname.lastname@example.org year trials and even indefinite trials potential possible for partners, bloggers, Oracle Aces etc)
Just got to the
The Delphix download trial consists of 3 pre-configured virtual machines downloadable as OVA files
You pick up little funny things in the day to day with Oracle.
Like this one when you try to drop a tablespace with a queue table in it:
drop tablespace MY_TSPACE including contents * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: Inappropriate utilities used to perform DDL on AQ table SCOTT.QUEUE_TABLE
You would think that since you’ve asked to drop everything, that well…everything could be dropped, but no :-)
The solution here is to use DBMS_AQADM.DROP_QUEUE_TABLE (with force=true if necessary) to clean it out, and the re-issue your command.
I’m sitting here watching the import of a moderately sized database via transportable tablespaces. You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast.
And fast it is.. until it reaches the following step:
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Now understandably, there’s plenty of stats to import, so its fair that it should take a little while. But a quick look at the SQL that’s being run, reveals something truly hideous. You’ll see a succession of giant PL/SQL blocks, chock full of literals and row-by-row (slow by slow) processing. Things like this:
I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 126.96.36.199 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases. Here’s some code to create a table and two indexes: