Dontcheff

No. 1 Happiest Job for 2014: Database Administrator

In DBA on April 14, 2014 at 20:11

Recently, Forbes Magazine published the happiest and unhappiest jobs in 2014.

Happy_DBA

The list comes from an analysis of 25,000 reviews by the site’s users, collected over the past year. The reviews included 7,800 different job titles.

Why does database administrator rank as the No. 1 happiest job, and what exactly does the work entail? Here’s the Bureau of Labor Statistics description of the job: “Database administrators (DBAs) use specialized software to store and organize data, such as financial information and customer shipping records. They make sure that data are available to users and are secure from unauthorized access.”

I am not sure that the happiness with the job is directly related to storing and organizing the data. According to the article all this may not sound too exciting, but according to the BLS, the job is reasonably well-compensated with a mean annual wage of $79,000 and the demand for DBAs is on the rise.

DBA_facts

The BLS predicts the field will grow by 15% between 2012 and 2022. The job has become more popular as DBAs’ roles have changed, with technology moving to the center of what many businesses do. “Because of their importance inside organizations, they can be the key driver of success,” she says. In the survey, DBAs gave especially high marks for the quality of their daily tasks and for job control.

For students, here are some programs to consider.

Read more about the DBA profession:

1. Dentist or DBA?
2. DBA skills are among the Top 10 IT skills for 2014

And now, here is some fun stuff about 12c, believe it or not!

12c_fun1

Shouldn’t SQL queries return the same results regardless of the hint?

12c_fun3

A recent search on LinkedIn jobs shows almost 2000 positions for DBAs, the keyword is “Database Administrator”:

DBA_search

Online reorganization enhancements in Oracle 12c

In Database tuning, DBA, Oracle database, Oracle utilities on March 27, 2014 at 18:38

The Online Reorganization and Redefinition feature in Oracle Database offers DBAs significant flexibility not just to modify the physical attributes of a table but also to defragment and compress the table while allowing online access to the database.

DB_reorg

A 5TB database can have 1TB of white space, in fact it can even have 4TB for all I have seen! Reclaiming the wasted space is important from both storage and performance perspective.

Before discussing the new 12c features related to online database reorganization, I will show you how easy it is to reorganize a table in 12c while at the same time all operations on the table are allowed. I have a tablespace called EM_DATA with just one datafile em_data_01.dbf and a table called EM_OBJECTS having more than 11 million rows. The file is almost 4GB in size. There are no another segments within the EM_DATA tablespace.

EM_OBJECTS

As you can see the segment EM_OBJECTS was 1774MB in size before the reorganization and after compressing it online we managed to shrink it to 644MB. The data in the table was not artificially ordered in any way whatsoever. Moreover, after the reorganization, I was able to resize the file down to 640MB and thus release more than 3GB of storage space on filesystem level!

Clearly, in 12c the complete procedure is very simple, there is no need to create explicitly the interim table but if an errors occurs during the execution, then the interim table is dropped, and the REDEF_TABLE procedure must be re-executed.

Now, here are all new features related to online redefinition in Oracle 12c:

1. The REDEF_TABLE procedure enables you to perform online redefinition on the table’s storage properties in a single step when you want to change the following properties:

- Tablespace changes, including a tablespace change for a table, partition, index, or LOB columns
- Compression type changes (as shown in the example above), including a compression type change for a table, partition, index key, or LOB columns
- For LOB columns, a change to SECUREFILE or BASICFILE storage

Check out these examples.

2. In 11g, it was only possible to add or drop columns when redefining tables online. In 12c, we can now set an unused column online. Check Doc ID 1120704.1 for details.

3. New in Oracle Database 12c: drop index online, alter index visible/invisible, alter index unusable online, and drop constraint online. I would suggest the master note “An overview of online redefinition of tables” Doc ID 1357825.1 for detailed examples and known bugs.

4. Since 10gR2, it has been possible to reorganize a single partition with online reorganization. With 12c, the redefinition of multiple partitions in a single redefinition session gives us the possibility to reduce the completion time to redefine multiple partitions.

5. Redefinition of tables that have Virtual Private Database (VPD) policies defined on them helps us eliminate downtime for redefining these tables. On top of that, there is a new function called EXPAND_SQL_TEXT which has been added to the DBMS_UTILITY packages. It makes seeing what SQL is actually executed pretty easy.

6. The performance of SYNC_INTERIM_TABLE with Materialized View Log processing has been significantly improved in 12c. I really hope not to see any more ORA-32416.

7. There is also improved resilience of FINISH_REDEF_TABLE with better lock management: you can avoid user cancellations or indefinite waits by using the new dml_lock_timeout parameter when running DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure. Amit Bansal gives more details here.

Finally, here is all about DBMS_REDEFINITION in 12c from Dan Morgan’s library.

junk_data

Online database reorganization is something of paramount importance, especially nowadays, when downtime is hard to get and data grows rather exponentially. Removing the unnecessary data is something few people in the companies care about although often more than 50% of database data is nothing else but junk data. Buying and adding more storage seems to be the easier option although reorganized and defragmented databases have better performance and require less memory and CPU.

Additional resources:
Understanding, detecting, and eliminating harmful database fragmentation by Craig A. Shallahamer
Reorganizing Tables in Oracle – is it worth the effort? by Uwe Hesse
Oracle table reorganization: Online or Offline by FJ Franken
Oracle Tablespace Reorg to reclaim space by AppsDBA
Using DBMS_REDEFINITION package to reorganize tables online by Marco Vigelini
Reclaiming Unused Space in Datafiles by Tim Hall

DBA skills are among the Top 10 IT skills for 2014

In Data, DBA on February 24, 2014 at 15:27

Recently, IT Business Knowledge and Global Knowledge published the Top 10 IT skills for 2014. In survey order, they are: Programming and Application Development, Help Desk and Technical Support, Networking, Mobile Applications and Device Management, Project Management, Database Administration, Security, Business Intelligence/Analytics, Cloud and Interpersonal.

I_LOVE_MY_DBA

“Though database administration missed the 2013 list, it will be quite hot in 2014. That stems from the growing interest in big data. Organizations have been gathering huge amounts of information from websites, social media, and third-party arrangements. Now they want to be able to use that data to make better decisions on products, services, and their customers.”

Although the above quote is somehow correct, there are definitely other factors that make the DBA profession being on top of the lists of hiring and salary surveys.

According to an estimate, 90% of the data in the world today has been created in the last two years alone. I think this is based on the estimate that every day we create 2.5 quintillion bytes of data. It is more like that (on top of all the social media and unstructured data) we have created IT systems with logging and auditing processes that generate a significant amount of that data.

Several colleagues of mine, including me, are fascinated by the fact how much unnecessary data is being logged into corporate databases. I would say more than 80% is usually either audited/logged data or data that is not being accessed/used. However, according to Red Hat CEO Jim Whitehurst the advice is to capture everything you can. It is a very interested view on big data’s chicken-and-egg question, I do recommend it.

Of course, we cannot capture it all. For example, there is no computer/storage in the world that can record the processes that take place in the human body within 1 second. Estimates of the human brain’s memory capacity vary wildly from 1 to 1,000 terabytes. For comparison, all the 19 million volumes in the US Library of Congress represents about 10 terabytes of data.

BDA

Data!

The growing amount of data is just a consequence of the complex data and database administration that comes with new technologies and software options that rather increase the complexity of IT systems. Increasing business and legal demands require companies to implement them. Keeping unstructured data outside the database (as the easier option) also increase the complexity. It takes extra skills and persuasion by data architects and DBAs to convince IT organization to seriously take the concept of keeping unstructured data with (relational) databases. I do recommend Marcelle Kratochvil’s book Managing Multimedia and Unstructured Data in the Oracle Database.

Only taking into use Audit Vault and Total Recall in an Oracle databases, might often cause exponential data growth. When such options are implemented in conjunction with other data and database mechanisms such as compression, partition, deduplication, redaction and encryption, one really needs a very experienced DBA (rather a team of DBAs) to support and maintain this growing complexity. Not to mention when such databases are being replicated, have standby copies, patched or part of an EXA environment. On top of that the constantly growing requirement of infinite availability makes the DBA skills very, very hot.

Database Administrator Average Starting Salaries are growing year by year, the 2 year increase is often more than 10%:

DBA_startig_salary

Add 5% for IBM DB2 database skills, 9% for Oracle database skills and 10% for Microsfot SQL Server database skills.

In addition, 2 from the Top 10 highest paying IT certifications for 2014 are database related.

Recently Forbes published the Top Jobs for 2014. Database Administrators are among the Top 10 with:

Total employment in 2013: 119,676 jobs
Jobs added between 2010 and 2013: 11,241 (up 10%)
Median hourly earnings: $37.39

Last but not least, according to Sweet Top 10, the Database Administrators are among the top 10 highest paying jobs in 2014. They also note that the rise of big data and the collection of massive amounts of data greatly increases the demand for database administrators.

DBA

Follow

Get every new post delivered to your Inbox.

Join 246 other followers