On some 11.1 security features and enhancements

Last week security was one of the hot topics at my company, I found myself writing and talking with one of my DBA friends on Oracle’s security options and the history, we agreed that security is one of the areas where in time Oracle invested a lot but still there were important failures all around the software. Then we started discussing on 11.1 security features and enhancements and I remembered reading some cool options from Lutz Hartmann‘s 11g New Features book related chapter, like;

– Against brute force attacks, sec_ax_failed_login_attemps specifies the number of unsuccessful authentication attempts before the client is dropped,
– Against denial of service attacks, sec_protocol_error_further_action and sec_protocol_error_trace_action specify how the database should react, sec_protocol_error_further_action with DELAY,n option specify in seconds how long the server waits before it accepts further requests and sec_protocol_error_trace_action with LOG option creates a short audit log instead of creating huge trace files with TRACE option,
– 11g’s password verification function(utlpwdmg.sql) is enhanced,
– After 11g Audit is by default enabled and audit_trail is set to DB,
– sqlnet.allowed_logon_versions parameter defined the minimum client version that is allowed to connect.

And much more of them in the chapter, but they did not mention who wrote which chapters in the book. Where as it is so obvious that which chapters belong to Lutz in the book since he put a lot of efforts with chapters 8(security), 10(change management), 11(sql management), 12(Flashback) and 13(ASM) with examples they beautifully help understanding the new enhancements after 11.1 on these areas. Also I caught that using your name as a username at the SQL*Plus prompt is a good technique to claim your examples later on(Kyte and Foote uses this technique a lot) :)

I must of course mention that the other chapters of the other authors in this book were big disappointments for me, I thought what if Lutz didn’t write for the book will they still publish this book since there is nearly no value added to the already available text at Oracle’s documentation? Who knows. There is an important lesson for me here, if one day I decide to write a book I will choose to write alone or choose the co-authors and the press I will work with very carefully. Since I love reading Lutz and he is not blogging like the old days because he was angry to some people copying his blog posts and making money out of them, I hope he chooses to write alone for his future book projects and I can consume more of his quality Oracle readings.



Today I was looking at the recent TPC-H benchmark results, I saw a vendor named EXASOL which dominated the benchmark results, what kind of a performance is this? They have their own operating system and they are listed as clustered, who are these guys? :)

Once I wrote on Oracle vs. SQL Server – a never ending story and advised on checking the other open source options as well. Today global economic crisis is upon all of us so I observe that any kind of financial decision is stressed as never before. EXASOL seems to be an interesting case study for our reporting database needs maybe, needs careful testing of course parallel to each individuals’ needs.

Really congratulations to EXASOL because of their engineering/creativity against giants like Oracle and Microsoft.

On Troubleshooting Oracle Performance book by Christian Antognini

I want to advise Christian Antognini‘s book Troubleshooting Oracle Performance to the newbies and especially Oracle database application developers. For years I advised Efficient Oracle by Design by Thomas Kyte to the beginners because of similar reasons and now this book also has lots of practical hints and additionally lots of 11g new features are mentioned.

Especially I loved Chapter 9 Optimizing Data Access, 11 Beyond Data Access-Join Optimization and 12 Optimizing the Physical Design. The last two chapters has important performance tips for the Oracle database application developers, for example column retrieval cost in CBO calculation and the following example on best practices in datatype selection are in my opinion some of the most simple and discarded ones. Below example on the NUMEBR dataype shows how the same value may have different scales and since the main datatype used to store floating-point numbers and integers is NUMBER with Oracle database applications this kind of tips are really important to be aware of.

set numwidth 18

INSERT INTO t VALUES (1/3, 1/3);

                N1                 N2
------------------ ------------------
,33333333333333333                ,33

SELECT vsize(n1), vsize(n2) FROM t;

         VSIZE(N1)          VSIZE(N2)
------------------ ------------------
                21                  2

Additionally related to the NUMBER datatype, it is mentioned not to be efficient when supporting number-crunching loads and as of 10g two new datatypes are available, BINARY_FLOAT and BINARY_DOUBLE which implement the IEEE 754 standard so a CPU can directly process them and they are fixed-length. Please check Binary_double vs. Number in compute intensive processes post for an example how this decision may affect the performance.

So if you also find yourself requested tuning of applications on production databases again and again like me, I strongly believe that it will be a very wise action in long term to let the Oracle database application developers around you to know about this book and make sure they read and understand the last two chapters so that they can be aware of the different datatypes, table types and index types options with Oracle database during the design and implementation of their database applications.

How to find the parallelism degree of a query that has been finished?(on 10.2)

This was the question of one of my colleague DBAs yesterday. Since he was on 10.2 and had AWR as a cost option we worked on DBA_HIST_ACTIVE_SESS_HISTORY first. After a while I decided to have the opinions of the experts at oracle-l and there Deepak Sharma advised a query based on DBA_HIST_SQLSTAT and Niall Litchfield advised DBMS_XPLAN.

So there is a guy who ran his query and insists that it took too long since he couldn’t get the DOP he requested that morning and he thinks this is DBA’s fault since he configured the Resource Manager not appropriate. As a result my DBA colleague wants to show that he really got the DOP what he requested for that query and he needs an accurate method to prove this on 10.2.

For the details of the discussion, it is still ongoing here and if you have any comments please let me know :)

Application Express 3.2 :)

Today I saw this warning when I try to login to my workspace at apex.oracle.com; This site will be unavailable on Wednesday, 11-FEB-2009 from 0900 – 1300 EST (1400 – 1800 GMT). During this time, this site will be upgraded to Application Express 3.2.

Happy to hear about 3.2 and wanted to share :)

What can be the purpose with “DBA 2.0” synonym by Oracle?

Today Mr.Mughees A. Minhas, director of product development/database management was our guest at my company and he gave a presentation on “DBA 2.0” idea. The presentation had three parts:
– Subjects related to how to sustain optimal performance with Oracle database,
– Subjects related to preserve order amid change with Oracle database,
– Subjects related to managing more with less with Oracle database

Lots of nice powerpoint slides related to extra cost options were discussed as usual :) One of my comments after the presentation was Oracle DBAs’ lives are getting much more complicated day by day and what Oracle marketing stuff is forcing IT managers to believe is completely the opposite, this is not fair just to sell more.

Change is of course inevitable and we are all somehow have to accept the changes which are managed by the guys at San Francisco head quarters, but in my opinion forcing management that if they migrate their DBA stuff to version 2.0 somehow everything will be easier and the stuff will be doing absolutely less is not accurate. For example with RAT DBA’s are now also testers, with ASM DBA’s are now storage admins right? Until now Oracle was able to automate easier parts like Memory, Undo, Segment Space management and they aggressively continue to come up with new features which need manual attention. But calling the older DBAs who are using Statspack not Grid Control or file system backup not RMAN as version 1.0, I believe this is not right.

ps: if until now you didn’t read it, here is the paper that started these kind of arguments in my organization: Performing a DBA 1.0 to DBA 2.0 Upgrade