Log Buffer #417: A Carnival of the Vanities for DBAs

This Log Buffer travels wide and deep to scour through the Internet to bring some of the most valuable and value-adding blog posts from Oracle, SQL Server and MySQL.

Oracle:

What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It’s a lightweight tool (only 11MB) developed by the SQL Developer team, which is fully compatible with Windows and Unix/Linux.  Also, you don’t need to install it so it’s totally portable.

Find Users with DBA Roles.

Virtual Compute Appliance 2.0.2 Released.

In case you are not familiar with WLST (the WebLogic Scripting Tool), it is a powerful scripting runtime for administering WebLogic domains.

The following article gives some useful hints-and-tips Richard used recently in helping people customizing tables and lists-of-values using Page Composer.

SQL Server:

With the idea of a generic Dacpac defined by international standard, comes the potential for a Visual Studio developer. This uses SSDT to create a generic database model to a SQL-92 compliant standard that can then be deployed to any one of the major RDBMSs.

Using the APPLY operator to reduce repetition and make queries DRYer.

Image a situation when you use the SQL Server RAND() T-SQL function as a column in a SELECT statement, and the same value is returned for every row as shown below. In this tip, Dallas Snider explains how you can get differing random values on each row.

This articles describes two ways to shred Unicode Japanese character from xls files into SQL Server table using SSIS.

Arshad Ali demonstrates how you can use the command line interface to tune SQL queries and how you can use SQL Server Profiler to capture the workload for tuning with Database Engine Tuning Advisor.

MySQL:

Postgres-Performance seit 7.4.

The Ubuntu 12.04.3 LTS release only provides MySQL 5.1 and MySQL 5.5 using the default Ubuntu package manager.

As part of a MySQL 5.5 to MySQL 5.6 upgrade across several Ubuntu servers of varying distros an audit highlighted a trivial but interesting versioning identification error in Ubuntu’s packaging of MySQL.

MySQL 5.6 will now automatically recreate the InnoDB redo log files during a MySQL restart if the size (or number) of these logs changes, i.e. a change to innodb_log_file_size.

Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.

Disable Lock Escalation in SQL Server

If a lot of rows or pages are locked, the SQL Server escalates to a table-level lock, to save resources. Each single lock takes approx. 100 bytes. So if you have many locks it takes a lot of resources to manage them. (There is a great blog about lock escalation, if you want some more info: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx)

 

Until SQL Server 2008, there was no way to change the lock escalation for a single table. You could deactivate the escalation for the server by using the Trace Flags:

  • 1211 - Disables Lock Escalation completely – allows to use 60% of the allocated memory – if 60% of memory is used and more locking is needed you will get an out-of-memory error.
  • 1224 - Disables Lock Escalation until the memory threshold of 40% allocated memory is reached – after that Lock Escalation is enabled.

 

But that was in most cases not a good choice and caused a lot of performance problems. In SQL-Server 2008 and above there is a new table option (ALTER-TABLE) that can be used to change the default Lock-Escalation. This helps you if you have a table where you want to disable the escalation or if the table is partitioned.

 

On a partitioned table activating the AUTO Option can improve concurrency, by escalating the locks to the partition-level and not to the table-level.

ALTER TABLE – table option:

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

  • AUTO (should be considered if you have a partitioned table)
  • If tables is partitioned – the locks will be escalated to the partition-level
  • If table is not partitioned – the locks will be escalated to the table-level
  • TABLE
  • Default behavior
  • Locks are escalated to the table-level
  • DISABLE
  • Lock escalation to the table-level is deactivated in most cases
  • In some necessary cases it’s allowed to escalate to the table-level

This is a cool feature, that are many developers are not aware of.

Thanks for Reading!

DBAKevlar at Collaborate 2015

Here’s a quick guide to my schedule of sessions at IOUG Collaborate 2015 for this year.  I’m looking forward to seeing everyone next week, (I’ll be arriving on Saturday, so I really should say in a couple days) and have no doubt we’re up for another phenomenal conference at Las Vegas, this year at the Mandalay!

Additionally, there are some great opportunities in professional development at the IOUG Strategic Leadership Program.  I’ll be speaking with James Lui on how to improve your value with personal brands and social media.

I’m the WIT luncheon speaker on Monday, so for those of you that signed up for this great, yearly event for women in tech before it sold out, I’m looking forward to meeting as many of the powerful women in our industry, (and hopefully a few men, too!) before and after the event.

There are also a few SIG’s that I’ll be attending that aren’t on the schedule:

Tue-12:30 p.m. – 1:00 p.m.Cloud Computing and Virtualization SIG Meeting (ID: 943)

Wed-12:30 p.m. – 1:00 p.m.Oracle Enterprise Manager SIG Meeting (ID: 949)

Id Number Date and Time Session Title Product Line(s) Session Track(s) Session Room
976 Sun. Apr. 12
9:00 am – 1:00 pm
Everything I Needed to Know About Enterprise Manager I Learned at COLLABORATE – Hands-on Lab Oracle Enterprise Manager Manageability Palm D
0 Mon. Apr. 13
9:15 – 10:15 am
Zero to Manageability in One Hour: Build a Solid Foundation for Oracle Enterprise Manager 12c Oracle Cloud – DBaaS/PaaS| Oracle Database| Oracle Enterprise Manager Manageability Banyan B
112 Mon. Apr. 13
3:15 – 4:15 pm
The Power of the AWR Warehouse Oracle Enterprise Manager Manageability Banyan B
967 Mon. Apr. 13
4:30 – 5:30 pm
IOUG Strategic Leadership Program: Staying on Top of Key Trends and Growing Your Personal Brand with Social Media Applicable to All Professional Development Banyan E
1003 Wed. Apr. 15
2:00 – 3:00 pm
OakTable: TED-Style Lightning Talks (2:00-3:00pm) Applicable to All Database Mandalay K
986 Mon. Apr. 13
12:45 – 1:45 pm
Twitter 101 for Oracle Professionals Applicable to All| Professional Development Banyan D

Thanks to everyone at IOUG for the opportunity to be so involved with this great conference and see everyone soon!



Tags:  ,

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon


Copyright © DBA Kevlar [DBAKevlar at Collaborate 2015], All Right Reserved. 2015.

Robert G. Freeman on Oracle 2015-04-09 18:19:36

My newest book effort is out! It's called Keeping Up With Oracle Database 12c Multitenant - Book One: CDBs, PDBs and the Multitenant World and you can find it here on Amazon.com.


If you have not looked at Oracle Multitenant - now is the time!


This is part one of what will be 3 or 4 books on Oracle Multitenant. You might look at this book and feel like it's not complete. Well, you are correct. This is part of a new series of books that I'm trying to get off the ground called the "Keeping Up with Oracle" series. The idea is to create a set of books (in this case 3 or 4 books eventually) on a given topic (Multitenant). Each book will be about 25% the size of a normally published book on the topic (for example, the Multitenant book will easily reach 500 pages by the time I'm done).


Big books take a long time to write. Something like the full sized Multitenant book might take a year. Often, by the time the book is released, some of the information in the book is old and stale. I wanted to figure out a way to address this problem.


By dividing the book into parts, I can write the first book, and publish current information that is not stale. As I write the remaining books, then the information in those books will be fresh too. So, I've released Book One now. It has five chapters and really gets you started on how to deal with Multitenant. I need to finish up the last bit of my 12c RMAN book and then I'll be starting on the second Multitenant book - picking up where the first left off.


The nice thing is that while I'm writing the second book, I may come across new things that I want to put in the first book - that will add value. That is almost impossible to do in the traditional publishing paradigm. In my paradigm - it's easy. I modify the work and re-publish it. It only takes about 24 hours to go through the re-publishing process. This also means that any errata related to the books will be much smaller - if it exists at all.


Please note that I've priced the books in such a way that even the total price of the full set (3 or 4 books) is LESS than what you would pay for a big doorstop of a book. 


Something else to note - I don't use a small font for the words in the book. While the book format is 6x9, the font size is such that I'm packing a great deal of information into the 6x9 format. I've thought about dropping the font size even more, but we will see how it goes with this first book. 


So, I hope you enjoy the book. I've already found the first two stupid errors in it. One of the chapter names on the table of contents got messed up and the chapter of contents is in upper case - I'm not sure how or why that last one happened. I'm sure I'll find other silly things. It's funny. You can look at your book over and over, you can look at the proofs of your book. You can have an editor look through the book for errors (which I did) and then ... when you publish it, it never fails that I find some kind of silly error within the first 30 pages. 


I hope that you will check out the book and let me know what you think about it's content and the new publishing model I'm going to try to use. Any comments on the book are welcome here. I'll also post an errata page here at some point in time.










Data Cloning and Refresh

For some time now, I’ve been creating blog posts that walk you through using some of the Enterprise Manager technology to perform specific tasks. For example, there’s one here on setting up Chargeback in EM 12.1.0.4. I’ve never been really satisfied with the way these blog posts turn out, as to document a step by step process like this takes lots of screenshots and you end up with a post that’s a mile long. It also gives the impression that doing something can be quite complex, when in fact it might only take a few minutes to perform all those steps.

In this blog post, I thought I’d take a different approach. Recently, I recorded a couple of screenwatches for use on OTN. One was on creating a test master database with continuous flow using a Data Guard standby database (available here) and the other on how to use Snap Clone outside the cloud environment (available here).

Both those URL’s stream off YouTube. If you want to download them and play them back off your own computer, here are the relevant links:

Continuous Flow – the relevant checksums if you want to make sure it has downloaded properly are:

CRC-32: f4fb51db
MD4: 8c3fbefff8cb8c464bf946739d30121b
MD5: a40a2a8232e53265b04ce789f8fa5738
SHA-1: ceafcb9e2d395a7756f25799fb58e4a7c11dbd10

Snap Clone outside the cloud environment – the relevant checksums if you want to make sure it has downloaded properly are:

CRC-32: 9e0cea20
MD4: dd9d87ac4fc78a65d1ef79cb93c3b690
MD5: 9101f17ed289714aeee6b42974abc085
SHA-1: 63c2e2228b4e33a7a0552484903b51883fc80da5

The screenwatches both start with some explanation of what you can achieve using EM to clone data in the way referred to in the video, and then walk you through the process of actually doing it. Of course, the only problem with loading videos (apart from the size) instead of screenshots is you will need to manually pause them if you want to stop and read the details of the screen. In any case, try them out and let me know (post a comment below if you like) which way you prefer – long step by step screenshot posts, or these shorter video versions.

UltraEdit v22.0

UltraEdit v22.0 has been released. You can see the new features here. For such a mature editor, it’s surprising when something new comes out that makes you take a step back. I think they’ve done just that in this release. The document map shows a miniaturized version of the page, making scrolling through large files really simple.

ultraedit22

I’m not sure if this is an idea they kicked from another browser, but this is the first time I’ve seen this and it’s really handy! Hopefully it will get added to the Linux and Mac versions soon.

Cheers

Tim…


UltraEdit v22.0 was first posted on April 9, 2015 at 2:51 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Partitioning Enhancements in Oracle Database 12c Release 1

I was planning to cover this subject in a single article, but it got a bit bulky, so I split it down into 6 little articles.

I’ve also created a links page to bring them all together.

I guess you could call it a list of nice-to-haves, rather than something revolutionary, but I’m sure someone will come back to me saying one of them has changed their life! :)

Cheers

Tim…


Partitioning Enhancements in Oracle Database 12c Release 1 was first posted on April 9, 2015 at 11:23 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.