Wednesday, March 31, 2010

OBIEE: Dimension (Hierarchy) - SOLVED

Of course 12 minutes after I finished writing this up I figured it out...or at least think I figured it out.

I began to think of this in terms of SQL. How would I do this if I had to write this all out by hand.

I started from the top most Level, Year:
SELECT DISTINCT
calendar_year,
calendar_year_id
FROM times
GROUP BY
calendar_year,
calendar_year_id
ORDER BY calendar_year DESC;
That returned the results I expected. Let's add quarter:
SELECT DISTINCT
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc
FROM times
GROUP BY
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc
ORDER BY
calendar_year DESC,
calendar_quarter_desc DESC;
OK, good. How about month?
SELECT DISTINCT
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc,
calendar_month_id,
calendar_month_desc
FROM times
GROUP BY
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc,
calendar_month_id,
calendar_month_desc
ORDER BY
calendar_year DESC,
calendar_quarter_desc DESC,
calendar_month_desc DESC;
That little exercise got me thinking...as you drill down, predicates are applied. After this first one, you would have:
SELECT DISTINCT
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc
FROM times
WHERE calendar_year_id = 1111
GROUP BY
calendar_year,
calendar_year_id,
calendar_quarter_id,
calendar_quarter_desc
ORDER BY
calendar_year DESC,
calendar_quarter_desc DESC;
So on and so forth as you drill down. This makes perfect sense. You don't need to define a level (Quarter for example) YYYYYQQ, because it already knows the year. Likewise, the further down you go, the more predicates are added. That will allow you, at the Day Level, to just display the Day of the Week or the Day Number of the Week...or some such nonsense.

I confirmed this by reviewing the physical SQL being submitted to the database.

Funny how stepping back for a second and taking the time to write down the problem enables you to see the problem more clearly.

I should do this more often. :)

OBIEE: Dimension (Hierarchy)

In relation to the previous post on Report Drilldown(s), I've been playing with the Dimensions to get a really good idea of how they affect drilling capabilities.

Conceptually, it's not a problem, it's just hierarchical data. I wasn't born with the understanding of hierarchical data...I just kept messing around with it.

Anyway, I've run across a little problem. After perusing the usual places for answers, I couldn't find exactly what I was looking for. It's probably due to the fact that I'm still learning the BI lingo (Levels, Dimensions, Hierarchy, etc.). I did find a useful article from Mr. Rittman, but it didn't help me.

As I build and rebuild these dimensions (specifically Time), when I get to a certain level, the records are not rolling up. Here's what it should look like:

Time
-->Total
---->Year
------>Quarter
-------->Month
---------->Week
------------>Day

Easy enough right?

For the purposes of this demonstration, I'm only going to go down to the month level. This is also to keep you somewhat engaged.

Let's start with a screenshot of the Time Dimension in the BMM:



Instead of doing a million screenshots, I'll just explain how they are set up.

I pulled CALENDAR_YEAR and CALENDAR_YEAR_ID into the Year Level. From the Logical Level --> Keys tab, CALENDAR_YEAR is set up as a key with "Use for Drilldown" checked. I create another logical key on CALENDAR_YEAR_ID but I uncheck "Use for Drilldown". I then select CALENDAR_YEAR_ID as the Primary key for the table and also check off the Chronological Key for CALENDAR_YEAR_ID.

I did the exact same thing for the Quarter Level pulling in CALENDAR_QUARTER_DESC and CALENDAR_QUARTER_ID.

I switch over to Answers and run the report.



Now I drill down and I should only have one record.



This is where the trouble starts for me.

I do the exact same thing with the Month Level, pulling in CALENDAR_MONTH_DESC and CALENDAR_MONTH_ID.

When I run the report and click on a Quarter, I get the following:



You might not be able to fully see what it's doing...it's giving me a record for each day in that given month (all 3 quarters are represented). Suppressing the values (Column Format --> Suppress), it does the same thing.

If I remove CALENDAR_MONTH_DESC as a logical key and recheck "Use for Drilldown" on CALENDAR_MONTH_ID, it works just fine.



Only I don't care to have the users see the CALENDAR_MONTH_ID, it's meaningless to them.

When I looked at the SQL being issued to the database, I did notice that the first query (from year to quarter) had a DISTINCT clause and the second (from quarter to month) did not.

I've been beating my head against the wall because I know there is something small I am missing.

Any help would be much appreciated.

Update (12 minutes later)
Check out this one for my resolution.

Monday, March 29, 2010

RDBMS + NoSQL Articles

There seem to be a whole lot of these running around lately. So I'm going to post the ones I know of here and update it as I find new ones. If you know of any, let me know and I'll post them here.

Somewhat related are the ones posted here:

Application Developers vs Database Developers
Application Developers vs. Database Developers: Part II
The "Database is a Bucket" Mentality
-->Everything is a Bit Bucket by Michael O'Neill
---->The Case for the Bit Bucket by Michael Cohen

Here are some of the ones rolling in:

Getting Real about NoSQL and the SQL-Isn't-Scalable Lie by Dennis Forbes
Getting Real about NoSQL and the SQL Performance Lie by Dennis Forbes
I Can't Wait for NoSQL to Die by Ted Dziuba
-->Not Everyone Using noSQL is a Rails-Lovin’ Ass-Clown by m3mnoch (h/t Mr. Cohen)
-->Why NoSQL Will Not Die by Stephan Schmidt (h/t Mr. Cohen)
My Thoughts on NoSQL by Eric Florenzano
Social Media Kills the Database by Bradford Stephens
NoSQL vs. RDBMS: Let the flames begin! by Joe Stump
-->Responding to Joe Stump on the NoSQL Debate by Dennis Forbes

NoSQL vs. RDBMS: Apples and Oranges? by Mike Kavis
Mike seems to be advocating the use of NoSQL type products for the larger datasets, i.e. data warehouses.
Oracle inspires an open source NoSQL tea party by Dana Blankenhorn
Is it Oracle's fault?
Mapping The NoSQL Space by Gwen (Chen) Shapira

OBIEE: Report Drilldown

As I am doing mainly metadata work right now, I figured it would be good to get a better grasp of how the reporting works on the presentation layer, i.e. Answers.

To that extent, I've been having problems getting the drilldown reports to work the way I want them to. The problem is that the ID associated with the level is displaying, not the value.

I'm using the Time Dimension:

Total --> Year --> Quarter --> Month

Time Dimension

After initially creating it, I create an Answers report with one column, CALENDAR_YEAR.

Calendar Year Report

Might be hard to notice, but the year is hyperlinked, I should be able to click it and drill down to the quarter right?

Calendar Year ID

That's not what I want. I don't want to see the ID associated with it. That's meaningless to the user. You might be thinking, "What an idiot, why did he make the ID available in the presentation layer?"

I just thought of that too...removed it, and I received an error.

Anyway, the same thing (%_ID columns showing up instead of their description).

Click on Year (again) and I get the Quarters...well, the ID anyway.

Calendar Quarter ID

Click on the quarter (ID) and I get the month ID.

Calendar Month ID

So how do I fix this?

First, go back to the Admin tool, find the dimension and double click on Month to bring up the properties page

click on month

Select the Keys tab

Keys

You should see CALENDAR_MONTH_ID with the Chronological Key box checked. Select New

New Logical Level Key

Now check CALENDAR_MONTH_NUMBER and make sure that Drilldown is checked.

CMN Drilldown

That should take you back to the Keys screen. Now either double-click CALENDAR_MONTH_ID or select it and click Edit down at the bottom

click or edit

Now uncheck the Use for Drilldown box

Uncheck Use for Drilldown

Rinse and repeat for each of your dimension levels; Quarter and Year.

Go back to your report, remove the filters and remove everything but CALENDAR_YEAR, run the report, drill down and you should see this:

Final Report

Voila!

You can use whatever columns you want to display. You don't have to use the Month Number, you could use March instead.

Sunday, March 28, 2010

Random Things: Volume #18

1Z0-052
Some have noticed that I have been posting my exam topics notes. I have multiple reasons for this.
  1. Any search I have performed on the test returns a bunch of brain dumps, cheat sheets and of course some legitimate books and guides. I'm more concerned that all those brain dumps and cheat sheets proliferate. As I go along, putting my notes here, updating the main page with the link to the new subject, I get higher and higher in the search results. Last check had me in the top 30. I won't stop many from taking the easy route, but hopefully those that are wavering, can see that it is possible to do this with a little hard work.
  2. See #1
I thought I had more, but that turned out to be everything. Far too many people (anything more than 0 is too many) take the easy route and give many of us who actually care a bad name.

1Z0-052 Studying
Speaking of the testing...I'm about halfway through gathering notes. I'm tackling the ones I know fairly well first. As I find new information on previous topics, I go back and update that main page. I'm hoping to take the test sometime after I return from COLLABORATE.

NoSQL
Some NoSQL articles keep rolling in...

I Can't Wait for NoSQL to Die by Ted Dziuba

My Thoughts on NoSQL by Eric Florenzano

Social Media Kills the Database by Bradford Stephens

NoSQL vs. RDBMS: Let the flames begin! by Joe Stump

Not Everyone Using noSQL is a Rails-Lovin’ Ass-Clown by m3mnoch (h/t Mr. Cohen)

ID10T
A couple of weeks ago I wrote about DBA_TABLES vs DBA_OBJECTS. Basically, I had just created an object and couldn't find reference to it...well, I figured it out. I had created a locally named table, DBA_OBJECTS while doing some stuff with OBIEE...which of course took preference over the DBA_OBJECTS view...and made me a complete id10t.

Other
I have a friend, who has made appearances here (not as a guest author) who completed his, like, 47th interview with Oracle this week. He was offered the job and is, I imagine, in the process of negotiation. The position was fairly high profile held by one of the best in the field previously. I'll announce (i.e. brag for him) when the process is complete.

Of course I had about 30 different things I wanted to talk about...but that was at 3 this morning trying to fall asleep. I should have written them down. I'm sure they'll be remembered over the next couple days.

Saturday, March 27, 2010

1Z0-052 - Configuring the Oracle Network Environment

Main

Configure and Manage the Oracle Network

There haven't been too many times when I have used the tools provided by Oracle to manage net services. Mostly, I take an existing file and change it to suit my current needs. The biggest reason for this is probably because I never quite understood what exactly was being asked for.

The 3 files typically involved are sqlnet.ora, listener.ora and tnsnames.ora which can be found in the <ORACLE_HOME>/network/admin folder.

Typically, these files are created by Oracle on installation. Perusing the files that are on my 11.2 installation, they appear to be the default (and I don't believe I have ever had need to modify them).

Here's the default entry for sqlnet.ora

# sqlnet.ora Network Configuration File:
# /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle
I've seen NAMES.DIRECTORY_PATH before, but it appears that ADR_BASE is new to 11g. I'll have to read up a bit on that one and get back to you. To read up on the different parameters for the sqlnet.ora file, go here.

As far as the tnsnames.ora goes, I don't believe it is required to be configured on the database server itself. This is mainly a client configuration file. Here is what it looks like on my server:

TESTING =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testing)
)
)
And here is the entry on my host machine:

TESTING =
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP )( HOST = 192.168.1.5 )( PORT = 1521 ) )
( CONNECT_DATA =
( SERVER = DEDICATED )
( SERVICE_NAME = testing )
)
)
Note the one difference between the two, the HOST. The HOST tells you (or your client tool really) on which server this database resides. If this were a RAC instance, you would see multiple ADDRESS sections. That is beyond the scope of this test however.

To read more about the parameters for a tnsnames.ora file, go here.

Finally you have your listener.ora file. This file is absolutely necessary on your database server. The Oracle Net Listener is a separate process that runs on the database server. It receives incoming client connection requests and manages the traffic of these requests to the database server.

Here's what the default listener.ora file looks like on my installation:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle
Again, the ADR_BASE_LISTENER is new to 11g and requires further research.

Using the Oracle Shared Server architecture



Documentation

In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.

An idle shared server process picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of shared server architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.

For even better resource management, shared server can be configured for connection pooling. Connection pooling lets a dispatcher support more users by enabling the database server to time-out protocal connections and to use those connections to service an active session. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.

Shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, even if they are on the same machine as the Oracle Database instance.

Main

Friday, March 26, 2010

1Z0-052 - Managing Database Storage Structures

Main

Overview of tablespace and datafiles

From Exploring the Oracle Database Architecture

You cannot have one without the other. When creating a tablespace, you must specify an associated data file. Likewise, a data file is just a file if it is not associated to a tablespace.

There are 3 types of tablespaces:
  • A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
  • An UNDO tablespace is atype of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
  • A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.
Create and manage tablespaces

CREATE TABLESPACE
CREATE TABLESPACE tablespace_01
DATAFILE '/u01/oracle/db1/tablespace_01.dbf'
SIZE 50M
ONLINE;
ALTER TABLESPACE

Add a data file:
ALTER TABLESPACE tablespace_01
ADD DATAFILE /u01/oracle/db1/tablespace_01_001.dbf'
AUTOEXTEND ON;
Drop a(n empty) data file:
ALTER TABLESPACE tablespace_01
DROP DATAFILE /u01/oracle/dba/tablespace_01_001.dbf';
DROP TABLESPACE
DROP TABLESPACE tablespace_01
INCLUDING CONTENTS--must be specified if any objects exist
AND DATAFILES--if you want the OS to remove the datafile as well
CASCADE CONSTRAINTS;--if there is any RI to tables in another tablespace
Space management in tablespaces
Need more research here.

Main

Wednesday, March 24, 2010

SOUG + Toad

We had our monthly meeting tonight with Jeff Smith from Toad.

Although I am mostly a SQL*Plus snob, I can appreciate good tools (snicker...wait, that didn't sound right). Ummm...nevermind. No matter how I say it, it's not going to sound right. You know what I mean...get your mind out of the gutter.

My first exposure to Toad was...I was looking for a picture, but I can't find one. Let's just say this reminds me of it:



That picture is courtesy of The DailyWTF

Needless to say, after the Google-like simplicity of SQL*Plus, I had no desire what-so-ever to try it.

Like most things, it has evolved. The interface is much cleaner and is (maybe was always?) highly configurable.

Let's just say I didn't run screaming from the room this time.

<background>
I was a Microsoft Access "developer" masquerading as a secretary for a state run university. Access, at the time, was the best thing since sliced bread.

When I came to the Oracle world I was "handed" SQL*Plus and a tnsnames.ora file and told to get to work. Where were the pretty pictures? How can I "see" my tables? Within a week or two I believe I created an ODBC datasource and used Access as a front-end so that I could "see" stuff.
</background>

In Mr. Smith's capable hands, Toad didn't look so bad. I'm not ready to pay for it yet, but it was much better than I previously thought.

The session was informal with Mr. Smith taking a survey of the audience; naturally dominated by those neavil DBAs. I do know, and have used, the DBA module in Toad and found it quite useful.

One very cool feature was the ability to generate random data...with referential integrity. Awesome feature if you "like" to test. Building fake data can be a very time consuming process...building data that has RI, well, it is just painful.

The best feature was Spotlight. This is more of a DBA tool; it let's you view high level performance metrics and the interface to this is very cool...star trek like.

For a non-RAC system:



RAC:



After Party
Post-presentation festivities included a trip to the local Mexican restaurant and chance to meet SQLChicken, a local SQL Server guru guy who was born 4 years ago.

I haven't shaved in about a month and my beard is completely out of control, so hopefully I didn't scare them too much.

Thanks Jeff (and Quest) for extending your stay so you could speak to our group.

1Z0-052 - Administering User Security

Main

Create and manage database user accounts

CREATE USER

Grant and revoke privileges

GRANT SELECT ON table_01 TO jkuramot;

REVOKE SELECT ON table_01 FROM jkuramot;

Create and manage roles

CREATE ROLE

Create and manage profiles

A profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user. You can assign a profile to each user, and a default profile to all others. Each user can have only one profile, and creating a new one supersedes and earlier version.

Profiles are used to manage the resource limits of related users.

Profile resource limits are enforced only when you enable resource limitation for the associated database. Enabling this limitation can occur either before starting up the database (using the RESOURCE_LIMIT initialization parameter) or while it is open (using the ALTER SYSTEM statement).

Though password parameters reside in profiles, they are unaffected by RESOURCE_LIMIT or ALTER SYSTEM and password management is always enabled.


CREATE PROFILE

Main

1Z0-052 - Implementing Oracle Database Security

Main

Database Security and Principle of Least PrivilegeWork with Standard Database Auditing
  • What is Standard Auditing? In standard auditing, you audit SQL statements, privileges, schema objects, and network activity. You configure standard auditing by using the AUDIT SQL statement and NOAUDIT to remove this configuration. You can write the audit records to either the database audit trail or to operating system audit files.
  • Who Can Perform Standard Auditing? Any user can configure auditing for the objects in his or her own schema, by using the AUDIT statement. To undo the audit configuration for this object, the user can use the NOAUDIT statement. No additional privileges are needed to perform this task. Users can run AUDIT statements to set auditing options regardless of the AUDIT_TRAIL parameter setting. If auditing has been disabled, the next time it is enabled, Oracle Database will record the auditing activities set by the AUDIT statements.
  • When Are Standard Audit Records Created? When auditing is enabled in the database and an action configured to be audited occurs, Oracle Database generates an audit record during or after the execution phase of the SQL statement. Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

    The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

    Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

    In contrast, changes to schema object audit options become immediately effective for current sessions.
Main

1Z0-052 - Books

While undertaking this endeavor, I am doing my best to see if it is possible to do this using the Oracle Documentation alone. That means no study guides (other than the topics covered), no third-party books, nothing.

I don't believe I have ever purchased a book on or about Oracle. The documentation provided, especially for the database, is top notch. There is so much information available out there if you just know how to find it. Reading AskTom for years didn't hurt either.

Here are the books I have used to study for this exam. I will update this list as appropriate.

Tuesday, March 23, 2010

1Z0-052 - Exploring the Oracle Database Architecture

Main

Explain the Memory Structures
  • System Global Area (SGA) - The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
  • Program Global Area (PGA) - A PGA is a nonshared memory region that contains data and control information exclusively for use by an Orcle process. The PGA is created by the Oracle Database when an Oracle process is started.

    One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
  • User Global Area (UGA) - The UGA is memory associated with a user session.
  • Software Code Areas - Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs-a more exclusive or protected location.


Describe the Process Structures
  • Client processes run the application or Oracle tool code
  • Oracle Processes run the Oracle Database code. Oracle processes include the following subtypes:
    • Background Processes start with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
    • Server Processes perform work based on a client request.

      For example, these processes parse SQL queries, place them in the shared pool, create and execute a query plan for each query, and read buffers from the database buffer cache or from disk.
    • Slave Processes perform additional tasks for a background or server process.


Overview of Storage Structures
  • Physical
    • Data Files and Temp Files - A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The data is written to these files in an Oracle proprietary format that cannot by read by other programs.
    • Control Files - is a root file that tracks the physical components of the database.
    • Online Redo Log - is a set of file containing records of changes made to data.
  • Logical
    • Data Blocks - Oracle Database manages the logical storage space in the data files of a database in units called data blocks, also called logical blocks, Oracle blocks, or pages. An Oracle data block is the minimum unit of database I/O.
    • Extents - is a logical unit of database storage space allocation made up of contiguous data blocks. Data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.
    • Segments - is a seg of extents that contains all the data for a logical storage structure within a tablespace. For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table.
    • Tablespaces - is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files. A database must have the SYSTEM and SYSAUX tablespaces.
Main

WHERE rownum = 1

Or it's evil twin, WHERE rownum < 2

I've seen this a lot over the past few years. When I say a lot, I mean approaching infinity a lot. Well, it feels like it anyway. I'm allowed to exaggerate.

I'm pretty much convinced that this is a bug. I see it and immediately say, WTF?

The only thing I could see it being used for is some sort of EXISTS functionality, like this:
DECLARE
l_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM user_line_of_business
WHERE username = 'KPEDERSEN'
AND rownum < 2;

IF l_count = 1 THEN
do_something;
ELSE
raise_some_error;
END IF;
END;
I have multiple records in the table for KPEDERSEN, I just need to know if one exists. This would add a STOPKEY (command?) to the query plan and force it to...(ah, who am I kidding, I don't know what I am talking about...yet).

BTW, here's the table definition and data if you want to try it out.
CREATE TABLE user_line_of_business
(
username VARCHAR2(30),
line_of_business VARCHAR2(3)
);

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'TEN' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'OSX' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'BOL' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'JKURAMOT',
'OSX' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'JPIWOWAR',
'OSX' );
So the EXISTS functionality is OK. Not great, not something I'd really like to see, but it works.

How about this though?

What if I, say, I was filtering queries based on a users line of business? Just to add, I am using something like APPLICATION CONTEXT to set the variable so it will hold only one value (please, just go with me on this...I know there is a wway around). My point is, the variable only holds one value. I promise, I am not making this scenario up.
DECLARE
l_lob VARCHAR2(5);
BEGIN
SELECT line_of_business
INTO l_lob
FROM user_line_of_business
WHERE username = 'KPEDERSEN'
AND rownum < 2;

dbms_output.put_line( 'l_lob: ' || l_lob );
END;
Which returns TEN. TEN might be the right answer.

What happens if you run that again? Well, you'll probably get the same result.

However, say this table grows and username KPEDERSEN gets more records? Do you think you could guarantee that TEN would be returned each and every time?

The short answer (and the one I am able to provide) is NO. You can't guarantee the order of the rows returned without explicitly putting an ORDER BY clause on there.

WHERE rownum = 1 or WHERE rownum < 2 are the devil (which contains evil).

1Z0-052 - Moving Data

Main

Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
  • Data Pump
  • SQL*Loader
  • External Tables
  • APEX
  • SQL Developer
  • Toad
  • SQL*Plus (COPY)
  • Etc.
Explain the general architecture of Oracle Data Pump
The following was found in this great white paper (pdf): Data Pump in Oracle® Database 11g: Foundation for Ultra High-Speed Data Movement Utilities.
  • Master Table - This is a table created in the schema of the user running a Data Pump job. It is a directory that maintains all details about the job: the current state of every object being exported or imported, the locations of those objects in the dumpfile set, the user-supplied parameters for the job, the status of every worker process, the current set of dump files, restart information and so on.

    During a file-based export job, the master table is built during execution and written to the dumpfile set as the last step. Conversely, loading the master table into the current user's schema is the first step of a file-based import operation, so that the master table can be used to sequence the creation of all objects imported.

    The user of the master table is the key to the ability of Data Pump to restart a job in the event of a planned or unplanned job stoppage. Because it maintains the status of every object to be processed by the job, Data Pump knows which objects were currently being worked on, and whether or not those objects were successfully completed.
  • Process Structure - A Data Pump job comprises several processes. These processes are described in the order of their creation.
    • Client Process - This is the process that makes calls to the Data Pump API.
    • Shadow Process - This is the standard Oracle shadow (or foreground) process created when a client logs in to the Oracle Database. The shadow services Data Pump API requests. Upon receipt of a DBMS_DATAPUMP.OPEN request, the shadow process created the job, which consists primarily of creating the master table, the AdvancedQueuing (AQ) queues used for communication among the various processes, and the master control process. Once a job is running, the main task of the shadow process consists of servicing GET_STATUS requests from the client. If the client detaches, the shadow process also goes away.
    • Master Control Process (MCP) - the MCP controls the execution and sequencing of a Data Pump job. There is one MCP per Data Pump job, maintaining the job state, job description, restart, and dumpfile information in the master table. A job is divided into various phases of metadata and data unloading or loading, and the MCP hands out work request to the worker processes appropriate for the current phase. The bulk of MCP processing is performed in this work dispatch loop. The MCP lalso performs central file management duties, maintaining the active dumpfile list and handing out file pieces as request by processes unloading data or metadata. An MCP has a process name of the form: <instance>_DMnn_<pid>.
    • Worker Process - Upon receipt of a START_JOB request, the MCP creates worker processes as needed, according to the value of the PARALLEL parameter. The worker processes perform the tasks requests by the MCP (primarily unloading and loading of metadata and data), and maintain the object rows that make up the bulk of the master table. As database objects are unloaded or loaded, these rows are written and updated with the current status of these objects: pending, completed, failed, and so on. The worker processes also maintain type completion rows, which describe the type of object currently being worked on: tables, indexes, views and so on. The types completion rows are used during restart. A worker process has a name of the form: "*DWnn*".
    • Parallel Query (PQ) Process - If the External Tables data access method is chosen for loading or unloading a table or partition, some parallel query processes are created by the worker process that was given the load or unload assignment, and the worker process then acts as the query coordinator. The are standard parallel execution slaves that exploit the parallel execution architecture of Oracle Database, and enable intra-partition loading and unloading. In Real Application Clusters (RAC), parallel query processes may be created on an instance other than that on which the Data Pump job was initiated. All other processes described thus far are created on that initial instance.
  • Data Movement
    • In order of speed (top most being the fastest)
      1. Data File Copying (transportable tablespaces)
      2. Direct Path load and unload
      3. External Tables
      4. Conventional Path
  • Metadata Movement - The Metadata API (DBMS_METADATA) is used by worker processes for all metadata unloading and loading. The Metadata API extracts object definitions from the database, and writes them to the dumpfile set as XML documents. This allows great flexibility to apply XML XSLTs when created the DDL at import time. The COMPRESSION parameter can be used to decrease the size of metadata written during a Data Pump export job.
  • Interprocess Communication - uses AQ.
  • File Management - Each worker and parallel query process makes local process requests to the file manager to allocate space, read a file chunk, write to a buffer, or update progress statistics. The local file manager determines if the request can be handled locally and if not, forwards it to the MCP using the command and control queue. Reading file chunks and updating file statistics in the master table are handled locally. Writing to a buffer is typically handled locally, but may result in a request to the MCP for more space.
  • Directory Management
Use Data Pump Export and Import to move data between Oracle databases

Done it.

Main

1Z0-052 - Managing UNDO Data

Main

Local Posts on Undo:
"Shrink" UNDO Tablespace
UNDO Brain Damage by Kellyn Pedersen

Overview of UNDO

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consits of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:
  • Roll back transaction when a ROLLBACK statement is issued.
  • Recover the database.
  • Provide read consistency.
  • Analyze data as of an earlier point in time by using Oracle Flashback Query.
  • Recover from logical corrputions using Oracle Flashback features.
When a ROLLBACK is issued, undo records are used to undo chnages that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.


Transactions and undo data

Managing undo
  • Creating an Undo Tablespace
    CREATE UNDO TABLESPACE undo_tablespace_02
    DATAFILE '/u01/oracle/db1/undo0201.dbf'
    SIZE 2M
    REUSE
    AUTOEXTEND ON;
  • Altering an Undo Tablespace
    • Actions Permitted (UNDO is system managed, so only the following are permitted)
      • Adding a datafile
        ALTER TABLESPACE undo_tablespace_02
        ADD DATAFILE '/u01/oracle/db1/undo0202.dbf'
        AUTOEXTEND ON
        NEXT 1M
        MAXSIZE UNLIMITED;
      • Renaming a datafile
      • Bringing a datafile online or taking it offline
      • Beginning or ending an open backup on a datafile
      • Enabling or disabling undo retention guarantee
  • Dropping an Undo Tablespace - You need to have one UNDO tablespace at any given time, otherwise, this statement will fail with ORA-30013: undo tablespace 'UNDO_TABLESPACE_02' is currently in use
    DROP TABLESPACE undo_tablespace_01;
    Since this is an UNDO tablespace, it has the same effect as DROP TABLESPACE...INCLUDING CONTENTS;
  • Switching Undo Tablespaces
    ALTER SYSTEM SET undo_tablespace = undo_tablespace_02;
  • Establishing User Quotas for Undo Space - The Oracle Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group)

    You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.

    When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.
  • Undo Space Data Dictionary Views
    • V$UNDOSTAT - Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also used this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
    • V$ROLLSTAT - For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.
    • V$TRANSACTION - Contains undo segment information.
    • DBA_UNDO_EXTENTS - Shows the status and size of each extent in the undo tablespace.
    • DBA_HIST_UNDOSTATS - Contains statistics snapshots of V$UNDOSTAT information.
Main

T-Shirt: DBA in Training

For motivation

DBA in Training

Monday, March 22, 2010

1Z0-052 - Managing the Oracle Instance

Main

Setting database initialization parameters
  • Read all about Initialization Parameters here
Describe the stages of database startup and shutdown
  • Starting Up and Shutting Down
    • Tools
      • SQL*Plus
      • RMAN
      • Enterprise Manager
      • SRVCTL - if database is managed by Oracle Restart
        • Any components on which the database depends (such as Oracle Automatic Storage Management and the Oracle Net Listener) are automatically started first, and in the proper order.
        • The database is started according to the setting in its Oracle Restart configuration. An example of such a setting is the server parameter file location.
        • Environment variables stored in the Oracle Restart configuration for the database are set before starting the instance.
  • Starting Up an Instance
    • NOMOUNT - Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files
    • MOUNT - Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database.
    • OPEN - Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only.
    • FORCE - Force the instance to start after a startup or shutdown problem.
    • OPEN RECOVER - Start the instance and have complete media recovery begin immdiately.
  • Shutting Down a Database
    • NORMAL - Default shutdown method.
      • Conditions
        • No new connections are allowed after the statement is issued.
        • Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
      • Does not require any instance recovery.
    • IMMEDIATE
      • Situtation
        • To initiate an automated and unattended backup
        • When a power shutdown is going to occur soon
        • When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
        • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
      • Does not require any instance recovery.
    • TRANSACTIONAL
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • After all transactions have completed, any client still connected to the instance is disconnected.
        • At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
      • Does not require any instance recovery.
    • ABORT
      • Situations
        • You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute)
        • You experience problems when starting a database instance.
      • Conditions
        • No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
        • Current client SQL statements being processed by Oracle Database are immediately terminated.
        • Uncommitted transactions are not rolled back.
        • Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.
      • The next startup of the database will require automatic instance recovery procedures.
    • TIMEOUT
Using alert log and trace files
  • Alert Logs
    • Query the V$DIAG_INFO view to find the path that corresponse to the Diag Trace entry
    • Open file alter_SID.log with a text editor to view text version
    • Open file log.xml to view file with XML tags
  • Trace Files
Using data dictionary and dynamic performance views
SELECT *
FROM dictionary
Main

1Z0-052 - Preparing the Database Environment

Main

Identify the tools for Administering and Oracle DatabasePlan an Oracle Database Installation
  • The logical storage structure of the database (which affects)
    • The performance of the computer executing running Oracle Database
    • The performance of the database during data access operations
    • The efficiency of backup and recovery procedures for the database
  • The overall database design
  • A backup strategy for the database
  • Hardware Considerations?
  • Network Considerations?
Install the Oracle software by using Oracle Universal Installer (OUI)
  • Been there, done that.
Main

Oracle Database 11g: Administration I - 1Z0-052

This is the subject matter according the the Oracle website for the Oracle Database 11g Administration I test (OCA). You can find it here. I've also created a "clean" version of it here.

Here's the list of books (Oracle Documentation) I have used.

Exploring the Oracle Database Architecture
  • Explain the Memory Structures
  • Describe the Process Structures
  • Overview of Storage Structures
Preparing the Database Environment
  • Identify the tools for Administering an Oracle Database
  • Plan an Oracle Database installation
  • Install the Oracle software by using Oracle Universal Installer (OUI)
Creating an Oracle Database
  • Create a database by using the Database Configuration Assistant (DBCA)
Managing the Oracle Instance
  • Setting database initialization parameters
  • Describe the stages of database startup and shutdown
  • Using alert log and trace files
  • Using data dictionary and dynamic performance views
Configuring the Oracle Network Environment
  • Configure and Manage the Oracle Network
  • Using the Oracle Shared Server architecture
Managing Database Storage Structures
  • Overview of tablespace and datafiles
  • Create and manage tablespaces
  • Space management in tablespaces
Administering User Security
  • Create and manage database user accounts
  • Grant and revoke privileges
  • Create and manage roles
  • Create and manage profiles
Managing Data and Concurrency
  • Monitor and resolve locking conflicts
Managing Undo Data
  • Overview of Undo
  • Transactions and undo data
  • Managing undo
Implementing Oracle Database Security
  • Database Security and Principle of Least Privilege
  • Work with Standard Database Auditing
Database Maintenance
  • Use and manage optimizer statistics
  • Use and manage Automatic Workload Repository (AWR)
  • Use advisory framework
  • Manage Alerts and Thresholds
Performance Management
  • Use Automatic Memory Management
  • Use Memory Advisors
  • Troubleshoot invalid and unusable objects
Intelligent Infrastructure Enhancements
  • Use the Enterprise Manager Support Workbench
  • Managing Patches
Backup and Recovery Concepts
  • Identify the types of failure that can occur in an Oracle database
  • Describe ways to tune instance recovery
  • Identify the importance of checkpoints, redo log files, and archived log files
  • Overview of flash recovery area
  • Configure ARCHIVELOG mode
Performing Database Backups
  • Create consistent database backups
  • Back up your database without shutting it down
  • Create incremental backups
  • Automate database backups
  • Manage backups, view backup reports and monitor the flash recovery area
Performing Database Recovery
  • Overview of Data Recovery Advisor
  • Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)
Moving Data
  • Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
  • Explain the general architecture of Oracle Data Pump
  • Use Data Pump Export and Import to move data between Oracle databases

Random Things: Volume#17

If you ever wondered where Volume #15 went, so do I. I seemed to have skipped it. Apparently I can't set the DEFAULT property of my surrogate key equal to a sequence.nextval just yet.

Database as a Bit Bucket
Some of you may recall last weeks article titled, The Case for the Bit Bucket. In that regard, I found a couple of good articles (hat tip Gary) worth reading by Dennis Forbes:
Getting Real about NoSQL and the SQL-Isn't-Scalable Lie
Getting Real about NoSQL and the SQL Performance Lie

One of those examines Diggs recent move to Cassandra.

Consulting
I'm in week 11 of 12 of my first full-time consulting gig. I could do without all the travel, but it has been fun. I miss the family of course, but a job is a job. Consulting itself I enjoy because you (mostly) get to stay out of the politics and just perform a specific task. I've even been able to expand that a little helping out with some APEX debugging.

Miscellaneous
I recently moved over to the Google Custom Domain from FTP publishing. It was something I kept putting off (FTP is being turned off) but finally took the plunge. The one thing I really like about it, no more waiting for the posts to publish. Their instantly available.

I have been storing images at Picasa for the last year or so, so image links (at least those that fell in that time period) didn't break. I was storing as much as possible on either Picasa, Google Docs or Google Code. I did have to put all css inline, until today, when I figured out how to reference it in Google Docs.

The only other annoyance was the path for labels changed. It was www.oraclenerd.com/labels/tag.html and now it is www.oraclenerd.com/search/labels/tag.html

If you do see something is broken, let me know and I'll fix it as soon as possible.

Oracle OpenWorld
The deadline passed to submit your paper(s). I decided not to submit my "presentation" How to Use Social Media to Increase Productivity. I'm not sure why either. I will still try to attend as a blogger however...and do plan on having the ability to go this year. My employer has already offered to pick up travel for COLLABORATE 10 in Las Vegas...so one way or another.

Sunday, March 21, 2010

Oracle DBA Certification

Just some notes I have been gathering for my go at the Oracle Certified Professional (OCP) certificate.

Resources
Oracle Certification blog.
Steps to Become Oracle Certified - From there you can drill down into your area. Specifically, it takes you to the paths for all certification programs.

I'm interested in the Certified Professional track for Database 11g Administration.

Basically there are 2 steps:

Oracle Certified Associated (OCA)



You can complete the OCA path without taking any classes. There are 2 parts (i.e. 2 tests) that you need to pass in order to receive the OCA. Tests are $195. Your choices for the first test are:For the second part, you have 1 test available:

Oracle Database 11g: Administration I Exam Number: 1Z0-052

Oracle Certified Professional (OCP)



This is where it gets expensive. In order to get an OCP, you must take an instructor led online or in-class course. I'm not sure the reasoning behind that exactly, but it is what it is. This will definitely be a barrier to completion.

The list of available courses can be found here.

Here's the list of 11g related courses:

11g courses

Finally, there is the Course Submission Form. Basically, this tells Oracle that you are pursuing the OCP (or OCM) certification so that they can track and verify your completion of the materials.
The Course Submission Form is a tool for certification candidates to submit their required training details for verification. This form is required in order to obtain most Oracle Certified Professional and Oracle Certified Master certifications as well as some Expert Program certifications. Completing the Course Submission Form will accomplish two tasks related to your certification:
  • It will trigger a request for Oracle to verify your attendance in an approved hands-on Course.
  • After your attendance has been verified by Oracle, the form will generate a permanent record in Oracle's certification database, showing that you have met the requirement.
The form may be submitted at any time in your certification path, after you have completed the training requirement. However, you will only receive the certification success kit once all certification requirements have been met.
That's it for now. If you have any other resources or comments, you know what to do.

Friday, March 19, 2010

Brothers gotta HEUG!

From one of my all-time favorite movies, Tommy Boy.

I just caught Mr. Ted Simpson (not wearing his cool oraclenerd t-shirt) in this short video on the Oracle OpenWorld blog.



HEUG, or Higher Education Users Group, pronounced "hug."

All this means is that Ted no longer has time to share with us...he's too busy actually doing stuff. :)

SOUG + Toad Evangelist = Fun!

Twitter saves the world...again. On Monday I saw @hillbillytoad's announcement that he would be in Tampa.

the hillbillytoad announces trip to tampa

I proceeded to try and make plans to meet up with him along with @SQLChicken who resides in the area as well.

Then someone on the SOUG board decides to call a board meeting Monday night...at 9 PM...during 24. Jerk.

We discussed moving our March meeting to early April to accommodate another speaker. I told them that that Jeff Smith (aka @hillbillytoad) would be in town the 23rd and 24th (our scheduled meeting is on the 25th) and that I would contact him to see if he was available to stay longer or present on the 24th. While in the meeting, I sent him an email (apparently Mr. Smith goes to bed at 9:15 or something).

Tuesday morning I received confirmation that he would love to speak on Wednesday the 24th.

Who is this Jeff Smith character?
...is a Solutions Architect at Quest Software. He has been with Quest since 2001, working on the Toad R&D, Product Management, and Sales organizations. Jeff is the primary author of the Toad Handbook, 2nd Edition. Jeff is a regular speaker at Toad User Groups and Oracle User Groups worldwide.
You can find his blog here.

If you use Toad or are interested in learning more about Toad, come on over to his presentation/talk on Wednesday, March 24th. Directions and stuff can be found at the SOUG website.

Thursday, March 18, 2010

UPPER( 'dba' )

I'm tired of understanding enough to know how frustrating it is to not be able to speak to certain topics. I have discussions where I know (or believe) something is not right, but I lack the ability to articulate the reason. That blows. I don't like being in that position...at all.

I want to be a real DBA now.
UPPER( 'dba' );
Since I am working with OBIEE right now, and I want to continue with that, I have to choose an alternate method to become a DBA. Reading and studying (which I already do), will not suffice.

I have no incentive, other than my natural drive. That hasn't been enough though. I can't apply it on a daily basis and continue to work as I do.

So I've decided to go the certification route. Now, there are many problems with certification and it seems to be watered down. I have met my fair share of those holding certain certifications who couldn't hit water if they fell out of a boat. My reason for choosing certification is that it will push me to follow through with the training and give me a specific path towards that goal.

I believe I started down that path when I first got into IT, but I failed the first DBA test...barely. Then, it was pretty much rote memorization...now, with 7 more years of experience under my belt, it should be a lot easier as I have a better understanding of most of the underlying concepts.

Anyone out there taken a similar path? Or were you just thrown into the fire? I would prefer the fire, but I have chosen my path (OBIEE) for now.

Wednesday, March 17, 2010

APEX: Database Object Dependencies

I have this tendency to talk about APEX a bit...at my current client, they recently began using it. When one of their applications broke (built by a consultant), I was asked to help out.

Love the APEX.

In particular, I was asked if I could find out how the security was being implemented. Active Directory was being used to authenticate, but they weren't quite certain how authorization was used. I was given a package body and got started.

First thing I thought of was the Database Object Dependencies report. This is one of my all-time favorite features of APEX. It allows you to easily find all the references to database objects and includes a link to see each page where said object is used. Makes learning about an application super easy.

To get to this report, go to your application home. Off on the right side, you'll see a link that says Application Reports.

Application Reports

That first section there under Application you should see a link for Database Object Dependencies

dod

You'll probably have a blank page, go ahead and click on the Compute Dependencies button

click on DOD

You'll get a lovely report like this

DOD report

Now you can drill down and find the pages where the object is referenced. I chose DEMO_CUSTOMERS

DOD Report Detail - DEMO_CUSTOMERS

Click on the page number to go to...you guessed it, that page. Remember the name of the component, locate it and see your object.

How nice is that?

Tuesday, March 16, 2010

The Case for the Bit Bucket

By Michael Cohen

Mr. Cohen is most famous here for the discussions we've had before about Application Developers vs. Database Developers. Part I is here, Part II is here. Mr. Cohen is a friend of mine. I have a great deal of respect for him. We obviously disagree in some areas, but I've learned to appreciate his push-back and learn from it.

He had left a comment the yesterday on my most recent post, The "Database is a Bit Bucket" Mentality (Michael O'Neill posted his followup, Everything is a Bit Bucket, as well). I thought the comment would get short shrift, so I asked him to write up a full post. I deleted that comment and here is his post.


Modern RDBMS's are quite powerful today. Pretty much every one of them has full support for SQL, including vendor extensions, all of the features we've come to expect from a relational database, a full fledged programming language built in, and quite often support for extras like full text search or native handling of XML. Most now also now ship with highly feature specific add-ons - PostgreSQL has a geospatial package that makes it the defacto standard in that domain, MySql has hot replication in a master-slave paradigm, Oracle has....well, Oracle has all kinds of things, a full object system and Java inside, a message broker, an HTTP server, a complete UI toolkit, among other things.

So the question arises as to how much of this capability one should use. I think it's becoming apparent that the answer to this is, "not much." Why shouldn't you take advantage of as much of the database's feature set as possible? The answer is performance and scalability. But wait, aren't stored procedures faster than ad hoc queries? Yes (theoretically). Won't it be more performant to execute business logic as close as possible to the data it operates on? Why should we introduce yet another component into the architecture when the database is perfectly capable of handling a particular task?

For one thing, the programming languages and environments offered by relational databases are now relatively long in the tooth, and have been eclipsed by modern OO languages. Developers are much more productive building applications with these new languages, and find it painful and tedious to work within the relational model, with SQL. You can see proof of this now with the overwhelming popularity of ORM frameworks in most of the popular OO languages out there. Java has Hibernate/EJB/JPA and many others. Ruby has ActiveRecord, DataMapper, and Sequel. Python has SqlAlchemy and Djanjo's ORM. And it's not because these developers lack the skills to work with the database directly. Quite the contrary actually, it takes intimate knowledge of the database to work effectively with an ORM. What's more, the ORM is often able to make runtime optimizations that would be difficult or prohibitively time consuming to hand code. Finally, clustered caches offer massive performance and scalability improvements, handling writes back to the database transparently behind the scenes, but for the most part they preclude implementing complex business logic in the database.

The overall trend is clear, across languages and platforms. It's the movement of data out of the database and into the application layer. Less and less reliance on the database, perhaps only for archival purposes. John Davies has a good comment on this. He's operating in a unique environment with extremely rigorous performance requirements, but we're now starting to see similar constraints imposed by the web. There's a whole class of software that has come about due to the inability to scale the relational database beyond a certain point. Facebook developed Cassandra, now used by Twitter, Reddit, and Digg, among others. LinkedIn built Voldemort. My employer doesn't deal with the massive scale of these companies, but we do large scale data processing with Hadoop. HBase, another non-relational persistent data store, is a natural fit, and just about the only option really. We use MySql less and less.

Of course, not everybody is building applications with such high scalability requirements. But even for applications with less intensive scalability requirements I would argue the same tendency to minimize the workload on the database should apply. Cameron Purdy has a good quote, "If you don't pick your bottlenecks, they'll pick you." Design your application to bottleneck, he says. What he means is, your application is going to bottleneck on something, so you need to explicitly decide what it will bottleneck on. Unfortunately, most applications bottleneck on the database, as this is the hardest layer to scale. It's pretty easy to scale the front end, we just throw more instances of Apache out there. It's a little bit harder, but not much, to scale the app server. But it's pretty hard to scale the database tier, particularly for write intensive applications. For well funded organizations, Oracle RAC is the standard. MySql's master-slave setup and hot replication saw it win out over PostgreSQL despite the fact that Postgres is a much better database in just about every other respect. The NoSql projects listed above grew out of the inability even to scale out MySql.

The trend is clear. We're collecting and processing more data than ever before, and this will only increase as we go forward. Unfortunately, the relational database (at least in it's current form) isn't well suited to the scale of data processing an already significant and growing number of organizations deal with on a daily basis. We're now seeing new solutions come forth to address the shortcomings of the traditional RDBMS, and the same forces that have necessitated such developments are at work even in smaller organizations. At all levels, developers would do well to require as little functionality as possible from the database, essentially, to treat it as a bit bucket.

OBIEE: Variable Rendering

I was fighting with this one particular issue for a few days when I finally called in the big gun. Of course it took him about 5 minutes to figure out.

The Scenario
One of the report developers was trying to set some presentation variables based on the values from an init block.

I created a report referencing those values like VALUEOF(VARIABLE_NAME) so that he could easily reference those variables and see what their values were.

However, the report returned the value in the Default Initializer, and not the value from the SELECT statement.

The SELECT statement was using the built-in USER variable to find a record specific to that user, it looked something like this:
SELECT
group,
user_display_name
FROM user_table
WHERE user = UPPER( 'VALUEOF(NQ_SESSION.USER)' )
I assumed it wasn't firing but couldn't find any indication of it in the NQServer.log.

I then removed the single quotes around VALUEOF(NQ_SESSION.USER). Logged out and logged back in. In the log there was an error, invalid identifier. OK, so it is firing...so why aren't the values being populated?

I tried hardcoding the username in the query, tested it, logged out, logged in. No error in the log file. The report was still showing the Default Initializer though. WTF?

Have you found the error yet?

Here's some pretty pictures to better illustrate.

First, the init block.

init block

Variable

variable

Answers definition

answers definition

Answers rendered

answers rendered

Do you see it now?

So it was silly...very silly.

Solution
VALUEOF(TEST) should have been VALUEOF(NQ_SESSION.TEST)

new definition

Don't forget to logout. Session Initialization Blocks are fired upon login, otherwise, you'll get this lovely error:

error

And finally, it works

it works!

The part that I don't understand is why it showed me anything. If it knew (evidenced by the fact that it rendered as 'BEFORE INITIALIZATION'), why didn't it just work? Why didn't it just throw an error or something at runtime?

Debugging this particular problem was difficult. A syntax error that wasn't really a syntax error. There should be a some sort of warning...especially if it is aware.

Sunday, March 14, 2010

Afraid to COMMIT;

Going through some old documents, I found this little gem I had uncovered doing some analysis of the source code.

I found this crazy enough to save, so I hope you enjoy it.

I found 14 or 15 commits in a 115 line procedure. I was shocked and stunned. It was wrong on so many different levels. I share with you the pain I went through.

We'll start off with a call to the logging table (just a quick note, this was not a stored procedure but a INSERT statement).
log_something;
Creating a record in the THIS_TABLE, it will have a status of A. Everything in there has a status of A.
INSERT INTO this_table (my_id, start_date) 
VALUES ( l_my_id, sysdate)
RETURNING table_id INTO l_table_id;
I know some could argue for COMMITs being inside stored procedure, but it was hammered into my head at an early age that the calling application should perform the COMMIT.
COMMIT;
THIS_TABLE had multiple "rules" tables. I understood the concept, but the implementation was not so good. This is the first "rules" table.
UPDATE rule_tab
SET status = 'D'
WHERE my_id = l_my_id
AND status = 'A';
Just so you are aware, that STATUS column had no constraint on it other than the size VARCHAR2(1).

Guess what time it is?
COMMIT;
Here is the second
UPDATE other_rules_table
SET status='D'
WHERE my_id = l_my_id
AND status='A';
Guess what?
COMMIT;
OK...here comes the other awesome part...to switch the status back to A (Active), we're going to create a job...in 30 minutes
dbms_job.submit
( job => ln_jobno,
what => 'update rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';',
next => sysdate+(.5/24));
COMMIT;
I kind of understand that one...for the job to go into the queue you have to issue the COMMIT.

For some reason, it's now cool to use a nested block.
BEGIN
dbms_job.submit
( job => ln_jobno,
what => 'update other_rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';',
next => sysdate+(.5/24) );

COMMIT;
EXCEPTION
WHEN OTHERS THEN
insert into errors (name,error_date, text)
values ('it broke', sysdate, ' MY_ID '||l_my_id||' creating job to set to a ');
COMMIT;
END;
Now we have 2 jobs created, one to update THIS_TABLE and one to update my first "rules" table. -10 for using DML in a job. -10 for not putting them into a single job that could fail together. -30 for not creating a stored procedure to do this.

But wait, it's not over yet. We're getting to the very best part I think.
BEGIN
dbms_job.submit
( job => ln_jobno,
what => 'insert into errors (name,error_date, text)
values (''wow'', sysdate, ''error turning it back on') ;',
next => sysdate+(.5/24));

EXCEPTION
WHEN others THEN
insert into errors (name,error_date, text)
values ('doing stuff', sysdate, ' MY_ID '||l_my_id||' creating job to log errors');

COMMIT;
END;
Did I catch a "niner" in there?

Did you catch what that final job did? It "logged" an error from the previous 2 jobs. Really? Do jobs work like that? I'm not really sure that they do.

This little snippet is just a snapshot into my life over the last few years. This kind of thing is everywhere. (I know everywhere!). I've mentioned before, but I've been reading The Daily WTF since 2005. Daily. I've learned more from that site than perhaps any other because you learn what not to do...which is just as important as what to do. It takes years to gain the necessary experience (read: screwing up) to know what not to do, The Daily WTF speeds that up significantly by allowing you to witness others mistakes. We've all made them, to be sure. It's whether we learn from them that is important.