Oracle DBA in KSA

July 13, 2008

Data Pump & Arabic Character-set Conversion

Filed under: Oracle — ksadba @ 4:31 pm
Tags: , , ,

Those of you who used exp/imp know that you had to set the NLS_LANG in the session to obtain a correct export, otherwise it would export in default character-set which is US7ASCII. If you’re importing into a different database with a different character-set, you would have to set NLS_LANG again to the new character-set.

Now with Data Pump, setting NLS_LANG prior to exporting/importing is no longer required. Data Pump would do the conversion for you without any settings from your side.

Here is a quick test we ran:

DB1 character-set: AR8ISO8859P6

DB2 character-set: AL32UTF8

Session NLS_LANG: American_America.WE8ISO8859P1

We were able to export/import tables with Arabic data from/to DB1 & DB2 with no issues.

Hazem Ameen
Senior Oracle DBA

July 8, 2008

SQL*Net More Data to Client & SDU

Filed under: Oracle — ksadba @ 6:08 pm
Tags: , , ,

In one of our projects, we had an operation that moves large amounts of data through a db link. This operation was taking much longer than expected, a SQL trace indicated excessive number of SQL*Net more data to client waits. This led us to perform an extensive investigation of all layers between the 2 databases: OS parameters, network, firewall/DMZ which all came back OK.

In our development environment, we decided to increase SDU parameter to 32K and see the outcome of such modification. Prior to this test, our impression of SDU parameter was a very little improvement in performance can be achieved by increasing this value and not worth fiddling with.

Test Scenario Setup:

  • Create a db link pointing back to same database so we eliminate any network traffic overhead.
  • Run 2 tests; one with default values and the second test with SDU value set at 32767 at both the TNS and listener.
  • Create an additional listener with a different name and listening on any port outside 1521. This along with local_listener parameter left at default will disable dynamic registration with the new listener. Dynamic registration overwrites SDU parameter according to Metalink article 124802.1 SDU Value not Working with Dynamic Registration. When creating an additional listener use netca not netmgr. We couldn’t logon with listeners created with netmgr. We got an error ORA-01034 Oracle not available.
  • Create an additional TNS entry with new SDU value
  • Enable SQLNET trace to verify SDU parameter is set correctly.
  • Trace remote sessions with dbms_monitor.enable_session_trace.

Test 1 using default values

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      353     30.87      98.88      36206      53047          0    11503946
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      355     30.88      98.88      36206      53047          0    11503946

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows     Row Source Operation
-------  ---------------------------------------------------
11503946  TABLE ACCESS FULL TABLE1 (cr=53047 pr=36206 pw=0 time=11504289 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     353        0.00          0.00
  SQL*Net message from client                   353        0.32         38.37
  SQL*Net more data to client                 38967        0.16         68.63
  db file scattered read                       2274        0.04          4.42

Test 2 using SDU at 32K

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch      353     27.33      59.64      36872      53047          0    11503946
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      355     27.34      59.65      36872      53047          0    11503946

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows     Row Source Operation
-------  ---------------------------------------------------
11503946  TABLE ACCESS FULL TABLE1 (cr=53047 pr=36872 pw=0 time=11504274 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     353        0.00          0.00
  SQL*Net message from client                   353        0.30         69.02
  SQL*Net more data to client                  2223        0.24         29.76
  db file scattered read                       2316        0.12          6.87

In 2nd test with SDU set at 32K, we’ve seen SQL*NET more data to client total wait time dropped down more than half, in other tests (not published here), we seen 10% improvement in elapsed time. A very welcomed improvement.

How to Put in Production

We don’t totally understand the exact ramification of increasing this value on a production system, so to play it safe, we decided to create a new listener at the remote site and a new TNS entry local site as indicated earlier in the test setup. The db link would use both new TNS entry pointing to the new listener at the remote site.

Conclusion

Projects that move large amounts of data across a TCP link such as Oracle Streams, materialized view over a db link, etc… can see some benefits by increasing SDU value.

Hazem Ameen
Senior Oracle DBA

July 2, 2008

Oracle Streams: Recommendations for Heartbeat table

Filed under: Oracle — ksadba @ 1:50 pm
Tags: ,

Here are some simple recommendations we thought be useful when implementing a streams heartbeat table in a bi-directional environment:

  • Oracle suggests adding a row every 1 minute to the heartbeat table. We recommend scheduling the job every 10-15 seconds which improves your probability of catching intermittent problems like buggy firewall or a faulty network cards.
  • Partition the table for easier maintenance. Dropping a partition is easier than deleting thousands of rows. Remember to create a job that adds new partitions.
  • Implement 1 table only in your streams environment, checking 1 table is easier than checking multiple tables.
  • Generate the primary key based on date easier than creating a sequence. Add site name or abbreviation to make the primary key unique globally.

Here is an example:

create table streams_heartbeat
(
id varchar2(20),
hb_time date,
constraint streams_heartbeat_pk primary key (id) using index tablespace users
) pctfree 0
partition by range(hb_time)
(
partition p_20080628 values less than (to_date(’2008-06-29 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080629 values less than (to_date(’2008-06-30 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080630 values less than (to_date(’2008-07-01 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’)),
partition p_20080701 values less than (to_date(’2008-07-02 00:00:00′, ‘YYYY-MM-DD HH24:MI:SS’))
);

Site A:

begin
dbms_scheduler.create_job(
job_name => ’streams_heartbeat_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into streams_heartbeat (id, hb_time) values (”A-” || to_char(sysdate, ”YYYYMMDDHH24MISS”), sysdate);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=secondly; interval=15′,
enabled => TRUE,
comments => ‘Streams heartbeat job from site A’);
end;
/

Site B:

begin
dbms_scheduler.create_job(
job_name => ’streams_heartbeat_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘insert into streams_heartbeat (id, hb_time) values (”B-” || to_char(sysdate, ”YYYYMMDDHH24MISS”), sysdate);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=secondly; interval=15′,
enabled => TRUE,
comments => ‘Streams heartbeat job from site B’);
end;
/

Hazem Ameen
Senior Oracle DBA

June 29, 2008

Convert Between Gregorian & Hijri Calendars Using Oracle’s PL/SQL

Filed under: Oracle — ksadba @ 1:53 pm
Tags: , ,

Working with Hijri calendar in Oracle can get frustrating especially with date conversion from/to Gregorian where results can be off by 2 days in some cases.

Here is a solution that does date conversion through lookup and totally eliminates the dependency on Oracle’s to_date function. If a converted date happens to be off, just update the lookup table with correct values.

1. Create Lookup Table

drop table date_conversion;

create table date_conversion (
seq number(10),
h_date varchar2(10),
g_date varchar2(10),
constraint date_conversion_pk primary key (seq) using index tablespace users
) pctfree 0;

create index date_conversion_h_ix on date_conversion (h_date) tablespace users;

create index date_conversion_g_ix on date_conversion (g_date) tablespace users;

2. Insert data in Lookup Table

You can insert as many years as you like, 10 years account for about 3,500 rows only.

Just as an example:

insert into date_conversion values (1, ‘1419/01/01′, ‘1998/04/28′);
insert into date_conversion values (2, ‘1419/01/02′, ‘1998/04/29′);
.
.
insert into date_conversion values (3715, ‘1429/06/24′, ‘2008/06/29′);

commit;

In real life, you would have to do this through a loop or a more efficient way.

3. Create Stored Procedures

These functions will be called to perform calendar conversion and date math.

CREATE OR REPLACE
FUNCTION hijri_add(in_h_date varchar2, in_days in number)
return varchar2 IS

out_date varchar2(10);

begin

select h_date into out_date
from date_conversion
where seq =
(select seq + in_days
from date_conversion
where h_date = in_h_date);

return out_date;

end;
/

CREATE OR REPLACE
FUNCTION to_gregorian(in_h_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select g_date into out_date
from date_conversion
where h_date = in_h_date;
return out_date;
end;
/

CREATE OR REPLACE
FUNCTION to_hijri(in_g_date varchar2)
return varchar2 IS
out_date varchar2(10);
begin
select h_date into out_date
from date_conversion
where g_date = in_g_date;
return out_date;
end;
/

Examples

select to_hijri(to_char(sysdate, ‘YYYY/MM/DD’)) hijri_date from dual;

HIJRI_DATE
————-
1429/06/24

select to_gregorian (’1429/06/24′) “Gregorian Date” from dual;

Gregorian Date
—————
2008/06/29

select hijri_add (’1429/06/02′, 22) “New Date” from dual;

New Date
———–
1429/06/24

Performance Enhancement

This table is ideal to be placed in keep cache, also remember to cache the indexes too. Alternately, you can cache the entire table in PL/SQL table. This will consume some of PGA memory depends on the amount of data in the lookup table (PL/SQL table will be cached for every session not just 1 time).

June 25, 2008

Convert Between Gregorian & Hijri Calendars Using Oracle to_date

There are two ways to convert from/to Hijri Calendar (also called Hijrah Calendar):

1) Use Oracle’s function to_date. This is discussed in this entry
2) Build your own conversion functions. You can read about this here

Oracle will convert from nls_calendar in to_date function to nls_calendar session parameter in your session. If both calendars are the same value then no conversion will occur.

Here are some examples:

– Show nls_calendar nls_session_parameters
SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
GREGORIAN

– Convert from Hijri calendar to Gregorian Calendar
SQL>
SQL> select col1 hijrah_date,
to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Arabic Hijrah”’) to_gregorian
2 from hijrah_date;

HIJRAH_DATE TO_GREGOR
——————– ———
03/01/1429 12-JAN-08
03/02/1429 11-FEB-08

– Convert from Gregorian Calendar to Hijri
SQL> alter session set nls_calendar=’Arabic Hijrah’;

Session altered.

SQL> select value from nls_session_parameters
2 where parameter = ‘NLS_CALENDAR’;

VALUE
———————
Arabic Hijrah

SQL>
SQL> select col1 gregorian_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”Gregorian”’) to_hijrah
2 from gregorian_date;

GREGORIAN_DATE TO_HIJRAH
——————– ———————
25-Jun-2008 20 جمادي الثانية 1429
17-Mar-2008 09 ربيع الأول 1429

– Convert from Greorgian Calendar to Hijri and show date in English

alter session set nls_calendar=’English Hijrah’;

select col1 hijrah_date, to_date(col1,’dd/mm/yyyy’, ‘nls_calendar=”GREGORIAN”’) to_gregorian
from gregorian_date;

Session altered.

HIJRAH_DATE TO_GREGORIAN
——————– ————————-
25-Jun-2008 20 Jamada El Thaniah 1429
17-Mar-2008 09 Rabi’ Awwal 1429

2 rows selected.

Hazem Ameen
Senior Oracle DBA

June 18, 2008

Arguments Against Big Datafiles

Filed under: Oracle — ksadba @ 4:28 pm
Tags: , ,

I was running a health check on 10gR2 database and came across a tablespace with a single data file sized at 50 GB. We normally select datafile standard size between 2-6 GB range depending on how big the total database size is expected to be.

Here are some points why:

  1. The bigger the file, the harder it gets to tune I/O contention issues especially when your disks are not stripped. In this case, you will have to move high I/O tables to a different tablespace (possible downtime because of unusable indexes).
  2. ASM sometimes do I/O rebalancing among disks. Imagine doing 50 GB copy from one disk to another and the impact on your system.
  3. Recovering big datafiles is definitely another concern because of the prolonged downtime.
  4. Running dbv (dbverify) will take much longer unless you try to run it in parallel.

Hopefully this will slow you down from using “create bigfile tablespace”

Hazem Ameen
Senior Oracle DBA

Storing Arabic Characters in Western Character-set (WE8ISO8859P1) Database

Filed under: Oracle — ksadba @ 4:20 pm
Tags: , , , ,

I’ve seen an entire production system running with this setting:
DB Character-set: WE8ISO8859P1
Client NLS_LANG: WE8ISO8859P1

They were storing Arabic characters just fine.

It confused me as how this configuration could store Arabic correctly, and as such, I had to involve Oracle support. The answer from Oracle support was it could work but it is not supported.

Please don’t try it even though it’s good to know this can work.

The main issue you will face with this configuration is exchanging data with other systems whether using db link or exp/imp/data pump. Oracle simply will not do character-set conversion like it would do between other supported Arabic character-set (AR8ISO8859P6, AR8MSWIN1256, etc…).

Here are the steps to move data in such situation:

1. From source database WE8ISO8859P1 spool data to text file comma delimited.

2. From target database with character-set AR8MSWIN1256 create external table with character-set AR8MSWIN1256 (example below)

3. Perform an insert statement at the target.

Example of an external table with AR8MSWIN1256 character-set

SQL> CREATE TABLE read_in_arabic2
2 ( col1 VARCHAR2(255),
3 col2 VARCHAR2(255)
4 )
5 ORGANIZATION EXTERNAL
6 ( TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY “DUMP_DIR”
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET AR8MSWIN1256
10 BADFILE dump_dir:’atext.bad’
11 LOGFILE dump_dir:’atext.log’
12 READSIZE 1048576
13 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
14 MISSING FIELD VALUES ARE NULL
15 REJECT ROWS WITH ALL NULL FIELDS
16 (
17 col1 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
18 col2 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
19 )
20 )
21 LOCATION
22 ( “DUMP_DIR”:’atext.txt’
23 )
24 )
25 REJECT LIMIT UNLIMITED PARALLEL 4;

Hazem Ameen
Senior Oracle DBA

June 16, 2008

Updating Millions of Rows (Merge vs. Bulk Collect)

Filed under: Oracle — ksadba @ 3:21 pm
Tags: , ,

For a 9.2.0.5 database, I have been asked to add few columns and update them with new values from another table. Base table contained 35 million rows.

To accomplish this, we researched 3 solutions: update via insert, merge statement and pl/sql with bulk collect

Update via Insert

With this method, you don’t execute any updates, instead you create a new table with the old and new data.

Here are the steps:

1. Create a new empty table with both old and new columns.
2. Insert data using append hint into the new table.
3. Recreate indexes on new table.
4. Gather statistics on new table.
5. Rename old table or drop.
6. Rename new table to old name.

Pros:

  • The fastest method to finish.
  • Not complicated, steps are straight forward.

Cons:

  • When executing steps 5 & 6 there is a sub-second where the table is not accessible. Even that tiny bit is not acceptable in our environment.
  • If you have materialized view log or CDC (change data capture), you would have to drop it and then recreate it.

We didn’t benchmark this method because it wouldn’t work in our environment due the sub-second unavailability of base table.

Merge Statement

Use Oracle built-in function merge introduced in 9i.

Pros:

  • Easiest to implement, just 1 SQL statement.

Cons:

  • Merge statement was very slow. It took 17 hours to complete which was unacceptable.

Bulk Collect

Using rowid to perform bulk updates on the base table.

Here are the steps:

1. Add/populate rowid of the base table to new table containing new data.
2. If possible, try to sort the new data in the same way like base table. In my case, we sorted the new data by the primary key. This will reduce multiple reads/writes of the same base table blocks.

Pros:

  • It took 74 minutes down from 17 hours

Cons:

  • Writing pl/sql was a bit of pain and must be adjusted and retested every time there is a change.
  • You might have to drop materialized view log and change data capture tables and then recreate them again after the operation finishes.

Here is sample code for bulk collect

DECLARE
– source table cursor (only columns to be updated)

CURSOR base_table_cur IS
SELECT rowid, col1, col2
FROM base_table;

type base_type IS TABLE OF base_table_cur%ROWTYPE INDEX BY PLS_INTEGER;

base_tab base_TYPE;

– new data

CURSOR new_data_cur IS
SELECT row_id, new_col1, new_col2
FROM new_data;

type new_data_type IS TABLE OF new_data_cur%ROWTYPE INDEX BY PLS_INTEGER;

new_data_tab new_data_TYPE;

type row_id_type IS TABLE OF ROWID INDEX BY PLS_INTEGER;

row_id_tab row_id_TYPE;

TYPE rt_update_cols IS RECORD

(
new_col1 new_data.new_col1%type,
new_col2 new_data.new_col1 %type
);

TYPE update_cols_type IS TABLE OF rt_update_cols INDEX BY PLS_INTEGER;

update_cols_tab update_cols_type;

dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

open base_table_cur;
open new_data_cur;

loop

fetch base_table_cur bulk collect into base_tab limit 5000;
fetch new_data_cur bulk collect into new_data_tab limit 5000;

FOR i in base_tab.FIRST .. base_tab.LAST LOOP
row_id_tab(i) := new_data_tab(i).row_id;
update_cols_tab(i).col1 := new_data_tab(i).new_col1;
update_cols_tab(i).col2 := new_data_tab(i).new_col2;
end loop;

FORALL i IN base_tab.FIRST .. base_tab.LAST SAVE EXCEPTIONS

update (select col1, col2
from base_table )
set row = update_cols_tab(i)
where rowid = row_id_tab(i);

commit;

exit when base_tab.count < 5000;

end loop;

commit;

close base_table_cur;
close new_data_cur;

EXCEPTION

WHEN dml_errors THEN

FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP

dbms_output.put_line(’Output your error here’);

END LOOP;

END;

/

Conclusion

Use insert via update if sub-second of downtime is acceptable

Use merge if you have small set data

User pl/sql bulk collect if you have large set of data and even sub-second of downtime is not acceptable

Hazem Ameen
Senior Oracle DBA

June 13, 2008

Working in Saudi Arabia: Project Managers Mayhem

So you get off the plane coming from Chicago all happy, excited and energetic to start your first job in the kingdom of Saudi Arabia. You feel nothing can go wrong, things in your life are going in the right direction and under control.

Few weeks later in the job, you start telling yourself; there something really wrong about the project manager’s way of managing. There is hardly any project plan, you can totally forget about risk assessment, nor any high level design, because in most cases he lacks basic understanding on how technology works.

His e-mails usually consist of 3 types: “fyi (for your information)”, “fya (for your action)” and “any update”. If this is not enough, once in a while, he resends your email exactly without changing a word and puts his name on it. Isn’t this plagiarism?! But then when you talk with him, he feels he didn’t cross any line. You wonder if this is the work culture or the individual.

You totally convince yourself that it’s just matter of time before something big and bad happens to this person, and sure enough something big happens but not bad; he gets promoted. And then you watch everybody congratulating him on a well deserved promotion while you stand dumfounded.

Now after a few years of unforgettable experiences like this, it always makes my day when a newbie walks up to me and says: “shouldn’t the project manager come up a project plan” or “shouldn’t the project manager lead this activity”.

Here are some advices I give people:

It takes a while to adjust and adapt to the Saudian work culture, don’t become bitter and sarcastic. Give it time, it gets better. The tough period is the first six months.

Take it easy and always talk to people who where there before you, don’t quit on the spot or become constantly angry and not easy to talk to.

Try to befriend some Saudians, meet and talk with them outside the job environment. This will help you to understand the culture better, and you will not have the mentality of us vs. them.

From my experience here, I found out that the Saudian culture is generally laid back and relaxed, and there is also lots of job security more than I ever had back in the States. Surround yourself with positive, non-bitter people or your life will become very negative and sometimes unbearable.

Hazem Ameen
Senior Oracle DBA

June 11, 2008

How to Save Hindi Numbers in Your Database

Filed under: Oracle — ksadba @ 3:41 pm
Tags: ,

A while back, I received a requirement to save Hindi numbers in our database.

It took some research and here’s how I solved it:

If your character-set is UTF8 then you can save Hindi numbers in varchar2 column type.

If your character-set is AR8MSWIN1256 or AR8ISO8859P6, then you can’t save Hindi numbers in varchar2 columns. You only can save them in nvarchar columns. In this case your national character-set (UTF16 or UTF8) would come in play.

Most likely you won’t be able to type Hindi numbers in SQLPlus, Toad, etc…, instead you can use unistr function to insert and query back the data.

Here is quick example to demonstrate how to insert and select numbers 123:

Insert into hindi_table (col1) values (unistr (’\0661\0662\0663′));

Select col1 from hindi_table where col1 = unistr (’\0661\0662\0663′);

Remember either varchar2/UTF8 character-set or nvarchar2/(UTF16 or UTF8) national character-set

Hazem Ameen
Senior Oracle DBA

Next Page »

Blog at WordPress.com.