Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
ACE director briefings
A thoroughly enjoyable day at the first day of ACE director briefings.
Of course, all of this stuff is Non-Disclosure so I cant really reveal any of the content, but as soon as OpenWorld is complete, then many of those restrictions will be lifted and we can all bask in the features that are coming.
Obviously, without giving too much away, you can expect lots of content with “next generation”, “latest release”, “Exa-this” and “Exa-that” plastered all over it. But look through the marketing hype and trust me, you’ll see some very very exciting things on the horizon.
For those of you not at OpenWorld, its still worthwhile going to the open world site – many of the talks are streamed online, so you can (partially) participate, although for my Australian colleagues, the time of day might not be that pleasant 🙂
The next release of Oracle….
….is going to be the next release.
[which is about all I can say at the moment]
How cool is that …
YY vs YYYY…It can make a BIG difference
Check out the execution plan differences here. The fully qualified date (2nd one) gets it right, and note that the optimizer gains the partition start / stop information.
SQL> select fds.fin_account_num,
2 sum(fds.sales) as sales from whs.fin_day_summary fds
3 where activity_date >= to_date('30-08-12','dd-mm-yy') - 390
4 and activity_date <= to_date('30-08-12','dd-mm-yy')
5 and fds.fin_account_num is not null
6 group by fds.fin_account_num ;
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29264 | 457K| | 48512 (1)| 00:09:43 | | |
| 1 | HASH GROUP BY | | 29264 | 457K| 1312K| 48512 (1)| 00:09:43 | | |
|* 2 | FILTER | | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 47222 | 737K| | 48318 (1)| 00:09:40 | KEY | KEY |
| 4 | PARTITION LIST ALL | | 47222 | 737K| | 48318 (1)| 00:09:40 | 1 | 4 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| FIN_DAY_SUMMARY | 47222 | 737K| | 48318 (1)| 00:09:40 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | FIN_DAY_SUMMARY_UQ | 47222 | | | 785 (1)| 00:00:10 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------------------
SQL> select fds.fin_account_num,
2 sum(fds.sales) as sales from whs.fin_day_summary fds
3 where activity_date >= to_date('30-08-2012','dd-mm-yyyy') - 390
4 and activity_date <= to_date('30-08-2012','dd-mm-yyyy')
5 and fds.fin_account_num is not null
6 group by fds.fin_account_num ;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29264 | 457K| | 24893 (5)| 00:04:59 | | |
| 1 | HASH GROUP BY | | 29264 | 457K| 1312K| 24893 (5)| 00:04:59 | | |
| 2 | PARTITION RANGE ITERATOR| | 47222 | 737K| | 24699 (5)| 00:04:57 | 98 | 153 |
| 3 | PARTITION LIST ALL | | 47222 | 737K| | 24699 (5)| 00:04:57 | 1 | 4 |
|* 4 | TABLE ACCESS FULL | FIN_DAY_SUMMARY | 47222 | 737K| | 24699 (5)| 00:04:57 | 389 | 612 |
---------------------------------------------------------------------------------------------------------------------
OakTable World is coming
If you’re at OpenWorld…then get some awesome additional content AND beer AND real life stories
ALL FOR FREE !
ORA-32033: unsupported column aliasing
When you see this
SQL> with xyz
2 (
3 select 1 my_alias from dual
4 )
5 select * from xyz;
(
*
ERROR at line 2:
ORA-32033: unsupported column aliasing
your first thought might be that there is something wrong with the column alias “my_alias”.
In fact, it got nothing to do with that….its missing the “AS”.
SQL> with xyz as
2 (
3 select 1 my_alias from dual
4 )
5 select * from xyz;
MY_ALIAS
----------
1
InSync12
If you are in Melbourne this week, hopefully you’ve registered for the InSync conference (www.insync12.com.au), which is the main user group conference in Australia. I’d love you to come share in my year long journey with Oracle clusterware and RAC upgrades on Tuesday afternoon. Pop in and say hi…
ORA-28511: lost RPC connection to heterogeneous remote agent
A client has been seeing this error when holding sessions open over HS for lengthy periods of time (greater than an hour or so).
Some workarounds that have worked are:
- when possible, issue, alter session close database link, and re-open it for subsequent queries
- Set INBOUND_CONNECT_TIMEOUT_<listener_name>=0 in listener.ora
- Set SQLNET.INBOUND_CONNECT_TIMEOUT=0 in sqlnet.ora
