Oracle Expert

I encountered this problem trying to install an ORACLE_HOME for a single node (non-rac) database on a 2 node cluster running RAC databases.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off
make -f ins_rdbms.mk ioracle

A few years ago, I worked in the past with a niche product called OST Business Rules, which empowers finance business users to develop their own accounting engine rules in a nice user friendly manner, therefore reducing the need for heavy development works from Finance IT.

OST was subsequently bought by Microgen, and where OST Business Rules have now evolved to a product called Microgen Aptitude. The concept is still fairly similar. You take various feeds from your upstream systems, either in the form of flat files, or xml messages. Aptitude via standard loader or MQ, will load the data into the database, and enrich them, prior to applying all the required business rules, to feed into various target, such as the general ledger, financial repository (data warehouse), for reporting purposes.

While Microgen Aptitude feels like a godsend to accountants, which allows them to create and implemented accounting business rules relatively quickly, like any other automatic wizzardy type applications, it can be misused, and create a maintenance nightmare.

Microgen Aptitude requires a database server and an application server.

The key point here is that data is processed row by row.

In order to achieve high throughput, the applications relies on sufficient bandwith between the database server and the application server, as well as relies heavily on concurrent process to be able to increase its throughput. So lets say you have 60k accounting events to process, and you have 1 hour to process it, that means will need to process 1000 events per minute. Depending on complexity of required enrichment, plus the complexity of the accounting rules, this could prove to be a lot of events to process within a minute. In this case you will perhaps be running 10 streams in parallel.

So yes, network traffic will be quite busy. You will be getting hot blocks on your tables, as multiple processes are likely to want to write to the same block at the same time.

To be continued …

I have always wondered what factor is  more important in building a good team. Staff with years of experience, or a team full of intelligent people? I hear you say, you can’t choose one way or the other, you need both. Of course, but if you have to choose, which one do you think is more important?

A lot of IT departments in financial institutions advertise their jobs with something along these lines:

 “at least 5 years of experience in Oracle, with experience working in large financial institutions”

 Staff with years of experience (especially with the tag of “have worked in other banks before”) are normally the preferred candidates. This is normal, because employers would like to have these people hit the ground running. Give them minimum training and orientation, and off they go. Don’t forget though, that naturally these experienced candidates also carry higher price tags.

 Can you take a bunch of inexperienced people, and turn them into a valuable member of staff? Two requirements are needed:

• Intelligent candidate
• Good mentoring

 Recently I spent three days remotely mentoring a bright twenty-two year old in India. She is a good 10 years younger than the average age of London staff. Her name is Smita, and she works as an outsourced junior DBA, while at the same time still pursuing her master degree in Computer Science. Over the few months I have known her, she really stands out from the crowd. She picks up mundane DBA tasks, and is enthusiastic about it, she follow things up properly and thoroughly. If she/other outsourced staff can’t fix some of the more technically challenging tasks, the senior staff in London would normally take over. Smita would “pester” me relentlessly “please tell me how you fix it!”. Or “how do you know where to look?”

The last 3 days I showed her how to perform an Oracle 10g upgrade on a database. This has been postponed for ages by the London staff because it contains an old installation of HTML DB, which needs to be upgraded to Oracle Application Express. There is a large backlog of databases to upgrade, and not enough people in the London team who know how to do it.

 So, here I was, spending hours with her through instant messaging, showing her step by step what to do. Sometimes deliberately letting her make mistakes, and later pointing out how to troubleshoot the error messages and to fix the errors.

 Three short days later (due to the time difference between UK and India ), she did it. She now joins the rank of a few people in our team who knows how to do this particular task. She painstakingly took notes as we go along. “I want to be able to do it in my sleep”, she said. She was ecstatic that she managed to do it, and I am incredibly proud of her.

 On another note, I co-own a software company Claromentis, which provides customizable information management portal and intranet software solutions. Most of the development work is done in Russia, and in the UK we concentrate on project delivery, sales and technical support. Recently we decided to expand our UK staff with customer facing developers, and went through the process of hiring. We already have an experienced development and testing team in Russia, so we decided that we would choose inexperienced candidates, as long as they show intelligence and good attitude. And being based in Brighton definitely helps, as it is a young vibrant city, a university town, so we are not short of candidates. We sieved through tons of CVs, needing to get it right. We finally made our selections. Now this is only the beginning. We will only succeed if we put the effort and time to nurture these young talents - which in itself is a challenge as the experienced Claromentis developers are as I mentioned in Russia. And I know we will.

I also believe that given the opportunity, I would love to take on the challenge to nurture young bright talented people  and teach them as much as I can about Oracle, and other things that matter to shape someone to be a caring, problem aware, customer centric Oracle professional.

I simply hate it when I see join conditions like this

WHERE table1.column1= UPPER(table2.column2)

If we build the systems ourselves, why can’t we simply ensure that the two columns have consistent data, especially if we are responsible in building application, from the ETL part, processing part all the way to the REPORTING part.

If table2.column2 is indexed, the indexed will be ignored in the above join.

To get around it, you need to create a function based index on table2

CREATE INDEX TABLE2_IX01 on TABLE2(UPPER(COLUMN2));

There is no such thing as free lunch though, there is an overhead to using function based index. It’s more costly to create than simple b-tree indexes.

While we are on function based indexes, my friend Jimbo has this great trick

Common problem - you’ve got a zillion row table of trades to process, with a status flag set to ‘PENDING’ in 10 cases and other values in the almost-a-zillion. You want the special rows.

Maintaining a convention B-tree index blows as the selectivity is so poor for N. So, how do you do it? I would have always gone for list partitioning with row movement until I devised this trick…

Use a CASE statement and a functional index to create an index in which the vast majority of entries are NULL - and so don’t appear. The index is thus tiny.

CREATE INDEX trade_trade_status_fidx ON trade ( CASE WHEN trade_status = ‘PENDING’ THEN trade_status ELSE NULL END );

Now select based on the condition that you indexed, it’ll use the index and it’ll be very fast.

SELECT * FROM trade

WHERE ( CASE WHEN trade_status = ‘PENDING’ THEN trade_status ELSE NULL END

) = ‘PENDING’;

DBA_HIST_SQLBIND: SQL Bind Information   

DBA_HIST_SQLSTAT: SQL Historical Statistics Information

DBA_HIST_SQLTEXT: SQL Text

DBA_HIST_SQL_BIND_METADATA: SQL Bind Metadata Information

DBA_HIST_SQL_PLAN: SQL Plan Information

DBA_HIST_SQL_SUMMARY: Summary of SQL Statistics

DBA_HIST_SQL_WORKAREA_HSTGRM: SQL Workarea Histogram History

DBA_HIST_STAT_NAME: Statistic Names

DBA_HIST_STREAMS_APPLY_SUM: STREAMS Apply Historical Statistics Information

DBA_HIST_STREAMS_CAPTURE: STREAMS Capture Historical Statistics Information

DBA_HIST_STREAMS_POOL_ADVICE: Streams Pool Advice History

DBA_HIST_SYSMETRIC_HISTORY: System Metrics History

DBA_HIST_SYSMETRIC_SUMMARY: System Metrics History

DBA_HIST_SYSSTAT: System Historical Statistics Information

DBA_HIST_SYSTEM_EVENT: System Event Historical Statistics Information

DBA_HIST_SYS_TIME_MODEL: System Time Model Historical Statistics Information

DBA_HIST_TABLESPACE_STAT: Tablespace Historical Statistics Information

DBA_HIST_TBSPC_SPACE_USAGE: Tablespace Usage Historical Statistics Information

DBA_HIST_TEMPFILE: Names of Temporary Datafiles

DBA_HIST_TEMPSTATXS: Temporary Datafile Historical Statistics Information

DBA_HIST_THREAD: Thread Historical Statistics Information

DBA_HIST_UNDOSTAT: Undo Historical Statistics Information

DBA_HIST_WAITCLASSMET_HISTORY: Wait Class Metric History

DBA_HIST_WAITSTAT: Wait Historical Statistics Information

DBA_HIST_WR_CONTROL: Workload Repository Control Information

Sometimes when we are asked to review performance of an application, we come across a bunch of codes which uses bind variables, and sniffing the SGA is not all that useful since it will show you the statements with the bind variables (:1, :2), not actually showing what values are being passed on.

Thankfully in 10g, the detective work is made easier.

SELECT * FROM A,B,C WHERE A.COL1=B.COL1 and A.COL1=C.COL1 and A.COL3=:1 and B.COL2=:2 and C.COL3=:3

Let’s grab the DBID and SQL_ID

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%A,B,C%’;

    DBID   |   SQL_ID    |         SQL_TEXT          |   

 ———-+————-+—————————+-

 1234567890|12345xxxxxxxx|<your statement here>      |                  

Then let’s dig in into the AWR to try to find what is being passed on

                                                                  select name, position, datatype_string

from dba_hist_sql_bind_metadata

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

  NAME | POSITION |  DATATYPE_STRING   

 ——+———-+——————–

 :1    |1         |DATE           

 ——+———-+——————–

 :2    |2         |VARCHAR           

 ——+———-+——————–

 :3    |3         |NUMBER            

This will highlight whether there are some implicit conversion done, lets say

col2=’1’ instead of col2=1 as it should

Another view that is useful would be

select *

from dba_hist_sqlbind

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

Sometimes you will receive complains from users that something that has been running ok for ages on a production server, suddenly is running very very slowly. In 10g, AWR gives you the ability to go and check and possibly compare the execution path of a query at different times in the past.

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%your query%’;

DBID      | SQL_ID      | SQL_TEXT |

———-+————-+—————————+-

1234567890|12345xxxxxxxx|UPDATE STAGING SET BLAH    |

We know that yesterday it was running fine, but today it’s not, and users have confirmed that data volumes are identical between the two dates

select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.dbid = 1234567890 and q.sql_id = ‘12345xxxxxxxx’

and q.snap_id = s.snap_id

and s.begin_interval_time between sysdate-2 and sysdate

order by s.snap_id desc;

When you find this, take note of the PLAN_HASH_VALUE from the 2 different times that you want to compare

Actually the 3 parameters that you need are:

·         DBID

·         SQL_ID

·         PLAN_HASH_VALUE

And to check the execution plan, run the following query:

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value1>

compare that with the second one

select id, operation, options, object_name, cost

from dba_hist_sql_plan

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

and plan_hash_value = <plan_hash_value2>

Hidden parameters are parameters that normally start with an underscore (_%)

To find out all hidden parameters within an instance, login as / as sysdba and run the following sql

SELECT inst_id, parno_kspvld_values, name_kspvld_values , ordinal_kspvld_values, value_kspvld_values, isdefault_kspvld_values FROM x$kspvld_values

WHERE name_kspvld_values LIKE ‘\_%’ ESCAPE ‘\’

AND inst_id = USERENV (’Instance’);

The bonus, this will also give you an example of how to search a string with an underscore in it.

For those who are not familiar with the terminology, an IT contractor is an IT professional who chooses to offer their expertise for a client as an independent contractor. IT contractors are normally hired on a fixed time basis, e.g. 3 months, 6 months or 1 year.

With the current credit climate, a lot of financial institutions have slimmed down their IT departments to cut cost, by reducing staff, or persuading some contractors to take on permanent employment.

Some IT contractors that are retained are normally some of the best talents within the department.  With the reduction of staff, workload level seems to go up and contributing to employee burnouts.

There was a tendency for managers to turn to these superstars for help more and more. It is natural. They excel in what they do. And because of that, more and more tasks are sent their way, from the flagship projects, to the annoying niggling operational issues which are costing quite a lot of overtime callouts.

Oh, and we have some angry business users demanding overdue responses from the IT department. Again, superstars sent to the rescue.

Managers forget to push tasks to the rest of the team, as it is easier to get results from the superstars. Instead of demanding more from the average performers, managers demand more from their superstars. When superstars are reassigned to look at some critical issues, existing tasks that are not as technically demanding are not reassigned and redistributed to the some of the more average performers within the team.

At the end of the day, IT contractors are only human, and they burnout too, physically or mentally. And then they would leave, leaving managers realizing (often too late) how the superstar actually has been taking on the workload of two or three average performers. Your best talents will not easily be put off with fierce market competition. Cream will always rise to the top.

Managers, your challenge is to spot these talents and hang on to them.

5) Mapping Table

I am reviewing the mapping table that is used to enrich the staging tables. The main structure of the mapping table is quite straightforward, can be simplified as follows:

SRC_FIELD

SRC_VALUE

DEST_FIELD

DEST_VALUE

AMENDED_BY

LAST_UPDATE_TIME

Here is a simple example of how the mapping tables will be populated

The update for enrichment will be done as follows:

UPDATE STAGING

SET COUNTRY = (select dest_value from mapping_table m

where m.src_field_name = ‘CNTRY’

and m.dest_field_name=’COUNTRY’

and rownum < 2)

where data_feed_id=12345;

What I am questioning here is why we need the rownum < 2?

Surely the data should be unique.

And unfortunately it is not.

select count(*), src_field, src_value, dest_field

from mapping

group by src_field, src_value, dest_field

having count(*) > 1;

The above query should return no rows, and unfortunately in this case, it returns quite a lot of rows.

In this case, developers were having a problem with the update statement, and decided to work around it by using rownum < 2. This is unfortunately is a lazy workaround, and is not helpful. This needs to be fixed, even though it could be painful and requires interaction with users to find out which rows are actually correct.

Considering there are AMENDED_BY and LAST_UPDATE_TIME fields, it seemed to me there was a half hearted attempt to perform an audit trail of changes in the mapping table, however it is incomplete. An IS_ACTIVE flag would help here, there the update can simply be modified as follows.

UPDATE STAGING

SET COUNTRY = (select dest_value from mapping_table m

where m.src_field_name = ‘CNTRY’

and m.dest_field_name=’COUNTRY’

and isactive=’Y’)

where data_feed_id=12345;

Not only this means that we can now create a useful unique index against the mapping table, which would help efficiency, this also means that we will actually be enriching correctly!

The be continued …