The Oracle Instructor
Another annual DOAG conference has passed, and I can only say the very best about it: Perfectly organized, large and modern location, impressive list of well known speakers and over 2100 attendees – wow!
My presentation Best of RMAN was scheduled at the first slot on the first day, so I was a bit concerned whether many people would attend that early. It turned out that the room got so full that I was asked by the organizers to deliver the same talk again next day – which I happily did, again with a packed room :-)
Apart from speaking myself, I enjoyed very much to see friends and colleagues again, as well as people I knew from social media before but never met in person yet. Thank you all for your good company guys – I may appear a bit quiet and distanced sometimes, but I really appreciate you and our little conversations. Personal highlight: The lady who approached me at the stairs, shaking hands and telling me that she knew me from an Oracle University class that she liked so much :-)
There were many interesting presentations, just to mention some of those I attended myself:
Frits Hoogland with Oracle Exadata and database memory
In his very detailed talk, he emphasized the importance of using Huge Pages on Exadata and that MEMORY_TARGET aka Automatic Memory Management is not an option here.
Jonathan Lewis presented Five Hints for Optimising SQL
I’m always amazed how much this man knows and how good he is able to explain it to us mere mortals :-)
Lothar Flatz was presenting Anatomie eines SQL Befehls (how to do Oracle Performance Tuning with a scientific approach)
During his very entertaining talk, he quoted no less than seven Oakies (including himself), so that was quite good advertising for the OakTable Network :-)
Frank Schneede delivered Eine Datenbank kommt selten allein (DB Cloning on Exadata using sparse diskgroups)
while Ulrike Schwinn presented Oracle DB Memory Techniken für mehr Performance (Overview about the meanwhile so many different ways to deal with memory in Oracle)
Couldn’t really catch her because she was running out of the picture all the time :-)
Martin Berger also did an interesting talk: Wie misst Oracle die Verwendung von Database Features? (How to make sense of DBA_FEATURE_USAGE STATISTICS)
I liked his presentation style with many live demonstrations very much!
My dear colleague Joel Goodman talked about Automatic Parallel Execution
Joel is what I call a ‘Standup Instructor’ – mention any database related topic and he will be able to deliver an excellent 30 minutes talk about it instantly :-)
A big THANK YOU to the many people from DOAG who helped to make that event take place again in such an impressive way! Hope to be there again next year.
Less famous 12c New Feature: If you do Create Table As Select (CTAS) or a direct load into an empty table, we will automatically collect optimizer statistics:
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production PL/SQL Release 22.214.171.124.0 - Production CORE 126.96.36.199.0 Production TNS for Linux: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production SQL> create table nu as select * from sales where rownum<=100000; Table created. SQL> select num_rows,blocks from user_tables where table_name='NU'; NUM_ROWS BLOCKS ---------- ---------- 100000 695 SQL> truncate table nu; Table truncated. SQL> insert /*+ append */ into nu select * from sales where rownum<=200000; 200000 rows created. SQL> commit; Commit complete. SQL> select num_rows,blocks from user_tables where table_name='NU'; NUM_ROWS BLOCKS ---------- ---------- 200000 1379
If the table is not empty, the statistics are not updated:
SQL> insert /*+ append */ into nu select * from sales where rownum<=100000; 100000 rows created. SQL> commit; Commit complete. SQL> select num_rows,blocks from user_tables where table_name='NU'; NUM_ROWS BLOCKS ---------- ---------- 200000 1379
You see that the previous statistics have not been changed here.
If it is a conventional insert:
SQL> truncate table nu; Table truncated. SQL> insert into nu select * from sales where rownum<=100000; 100000 rows created. SQL> commit; Commit complete. SQL> select num_rows,blocks from user_tables where table_name='NU'; NUM_ROWS BLOCKS ---------- ---------- 200000 1379
Again, the statistics have not been maintained automatically either. Can be done manually still, of course:
SQL> exec dbms_stats.gather_table_stats('ADAM','NU') PL/SQL procedure successfully completed. SQL> select num_rows,blocks from user_tables where table_name='NU'; NUM_ROWS BLOCKS ---------- ---------- 100000 748
So knowing that, you may be able to skip some manual statistics collections that you may have done in the past after CTAS or bulk loads :-)
Tagged: 12c New Features
The recipients of the Oracle University Leadership Circle Quarter 1 Fiscal Year 2016 have just been announced. That is a corporate award for the best instructors worldwide according to customer feedback. Not less than four (!) come from our small team of 14 instructors:
Yes, we have a great team – supported by a great manager: Thank you, Richard! Congratulations to everyone in the circle this time, I feel honored to be listed together with you!
By the way, I never understood why the fiscal year is ahead of the calendar year – suppose you need to study business economics for that ;-)
Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:
RMAN> create table adam.nu tablespace tbs1 as select * from adam.sales where rownum<=10000; Statement processed RMAN> alter system switch logfile; Statement processed RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf'; host command complete RMAN> select count(*) from adam.nu; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12 ORA-01115: IO error reading block from file (block # ) ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf' ORA-27072: File I/O error Additional information: 4 Additional information: 131 RMAN> alter database datafile 5 offline; Statement processed RMAN> restore datafile 5; Starting restore at 2015-10-20 11:50:43 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=187 device type=DISK creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf restore not done; all files read only, offline, or already restored Finished restore at 2015-10-20 11:50:45 RMAN> recover datafile 5; Starting recover at 2015-10-20 11:50:52 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2015-10-20 11:50:53 RMAN> alter database datafile 5 online; Statement processed RMAN> select count(*) from adam.nu; COUNT(*) ---------- 10000
Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it! Maybe not on a production system ;-)
Tagged: Backup & Recovery, RMAN
Oracle University has introduced a new format that suits the way many learn today: Oracle Learning Streams
Although I do not expect that this format will replace classroom training, I consider it a great enhancement – coming with a price like most good things…
As you can see, I’m happy to contribute to it in my area of expertise: You can find me here in the Database Stream.
My clips start with A Practical Guide because they all contain brief demonstrations that show how things can be done practically.
This is what I think viewers want to get instead of “Death by PowerPoint” :-)
It is an ongoing process (a stream of content, therefore the name), so I will keep on adding clips to the pool.
Especially if you like my kind of content, so have a look and get back to me to tell what you would like to see there, please!