All About Databases

gathering system stats

2012-02-14T02:54:00.000-08:00

The workload needs to be very exhaustive to gather the correct set of values - a good mix of sessions some doing multi-block reads and others sequential reads. I tried to capture these stats using three different workloads, and the stats had a stark variation.
Workload1: created two indexes on a fact table.
Workload2: Executed 8 BI queries in parallel.
Workload3: Executed a bi query making it fairly complex by self joining the fact table.

These are the results:

	Workload1	Workload2	Workload3
CPUSPEED	948	948	916
CPUSPEEDNW	948	948	948
IOSEEKTIM	5	5	5
IOTFRSPEED	91662	91662	91662
MAXTHR	311120896	129651712	360448
MBRC		3	6
MREADTIM	16.741	2419.531	35318.899
SLAVETHR	5065728	4799488	4799488
SREADTIM		547.395	331.686

11.2 has a bug which miscalculates MREAD and SREAD time inflating them by a factor of 1000:
Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID 9842771.8]

best is to set these stats manually:
http://www.dbi-services.com/index.php/blog/entry/problem--strange-figures-while-collecting-the-oracle-optimizer-system-statistics-

Both these link are mentioned in Jonathan blog post - http://jonathanlewis.wordpress.com/category/oracle/statistics/system-stats/.

Further to this, as far as I understand the absolute values of these two attributes MREADTIM and SREADTIM is something that is inconsequential. Its the ratio of these two that drives optimizer to choose between Full table scan or Index scans.

Establishing remote connection to DB2 Server

2011-06-27T23:16:00.000-07:00

To be able to use db2 connect from client, there are few configuration steps to be followed as mentioned here:

on DB2 Server

db2set DB2COMM=tcpip
update database manager configuration using svcename 50000
db2stop
db2start

on Client Side

db2 UNCATALOG DATABASE db_name
db2 UNCATALOG DCS DATABASE db_name
db2 UNCATALOG NODE server_name
db2 CATALOG TCPIP NODE server_name REMOTE server_name SERVER 50000 REMOTE_INSTANCE DB2 SYSTEM server_name OSTYPE AIX
db2 CATALOG DCS DATABASE db_name AS db_name
db2 CATALOG DATABASE db_name AS db_name AT NODE server_name AUTHENTICATION DCS

$ db2 connect to db_name user user_name using password

   Database Connection Information

 Database server        = DB2/AIX64 9.1.6
 SQL authorization ID   = user_name
 Local database alias   = db_name

Intra-Block versus Inter-Block row chaining

2010-10-07T00:10:00.000-07:00

Oracle database can store only 255 columns in a single row-piece. Anything beyond 255 columns will be stored as a separate row, chained to the main one. This kind of chaining is different from what we know as migrated/chained rows which happen either because of lack of space in current block which causes rows to migrate when updated leaving behind their new address or due to row size more than the block size itself. Both these types of chaining are inter-block row chaining.

intra-block row chaining (as i mentioned above happens due to number of columns exceeding 255) does not cause as much a performance issue as inter-block row chaining. This is because all the row contents could still be found within the same block. When there is a full table scan, DB can easily do scattered reads over this table with number of blocks spanning up to the value for parameter db_file_multiblock_read_count. For scanning rows which are chained to another block, as is the case with migrated/chained rows, DB has to do sequential reads with one block read at a time. This summarily degrades query performance.

Unlike Inter-block row chaining, Intra-block row chaining cannot be detected using ANALYZE TABLE LIST CHAINED ROWS command. However, there is a way to see to what extent the rows are chained. Look out for statistic " table fech continued row". As you access the table with 255+ columns, this value will keep increasing.

select name, value from v$sysstat where name like 'table fetch %';

Note that if all the columns beyond 255 columns have NULL values, the row will not be chained.

BI Apps ETL: Handling statistics gathering over tables in parallel

2010-05-10T02:16:00.000-07:00

For one of the BI Apps incremental ETL execution, it was observed that the major time is consumed during statistics gathering over some of the warehouse tables. There were three tables which took 1 hr each to execute. When run standalone, gathering statistics over each of these tables took only 3 minutes. So, what was so wrong during the ETL ??

Upon closely observing the DAC execution plan it was found that all these three tables were analyzed in parallel. It was not a coincidence. In fact, each time these three tables would land up being analyzed in parallel. So, the question is how come !!!

There was a task group defined which had collection of six tasks which where loading into these three tables. Truncate table option was enabled over this task group. This caused all query indexes to be created over these three tables once all the tasks in this task group finished loading these three tables. While query indexes were created in parallel and one table had all indexes created while the other tables still had them being created, statistics gathering over the table did not spawn. Only after all the three tables had all the indexes created, analyze command over the three table was fired. This caused all these three tables getting created in parallel. Is executing these three tables in parallel so much resource consuming that time taken jumped 20 times than when run one at a time ??

Lets see the command being used to analyze these tables:

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'W_TAB_D', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false ,degree =>DBMS_STATS.AUTO_DEGREE );

When I executed all three tables in parallel, maximum time taken by any of analyze commands was 26 minutes (during etl there were some more Informatica jobs running which would have further slowed it down to take 1 hr). This is what I observed in v$sysstat view:

Before firing all three commands in parallel:
---------------------------------------------------

Parallel operations not downgraded                                        9
Parallel operations downgraded to serial                               0

After firing all three commands in parallel:
----------------------------------------------------
Parallel operations not downgraded                                       10
Parallel operations downgraded to serial                               2

This shows that all the parallel servers were put to use by analyze over one of the table and rest of the two were then executed serially. This serial execution caused the other two analyze to take more time.

Now, the option was to either segregate the tasks with in the task group in three different task groups, or, to do something to speed up the analyze over these three tables in parallel.

First option was not so enticing given that this task group is shipped OOTB and any changes might impact the functionality. Also, all the tasks in this task groups were loading different tables in a given order, so changing the order by segregating them in different task groups was not so straight forward. So, the choice left was to speed up the statistics gathering over the table.

Since it was observed that parallel servers were hogged by analyze over one single table, It was decided to override the degree parameter in the analyze command and set it a value so that all the three execution will get enough parallel servers. We may not get the performance as that of standalone execution, but it might still be better than what was observed.

So, the revised syntax is:

When executed in parallel, the maximum time taken by any of these stands at 19 minutes. v$sysstat shows no jobs being downgraded to serial.

Parallel operations not downgraded 16
Parallel operations downgraded to serial 2

This brought about performance improvement of 27%. Obviously, it is yet to be seen how it fares when we incorporate these changes in DAC for etl purpose. Will post more on what further is found.

Linux : Scanning for an occurance of string in all the files in folder and its sub-folders

2010-03-25T04:36:00.001-07:00

find . -exec grep "Upper" {} /dev/null \;

Upgrading AIX system to next Technology Level

2010-01-28T20:54:00.000-08:00

Refer to below link for upgrading technology level:
http://www.ibm.com/developerworks/aix/library/au-aixservicepacks/index.html

As root user:
# suma -c -a HTTP_PROXY=http://[proxy]:[port]/
# suma -c -a FTP_PROXY=http://[proxy]:[port]/
#smit suma
-> select option "Download Updates Now"
-> select "Download Maintenance Level or Technology Level
-> Provide TL as 5300-07 for e.g.

Once all files are downloaded. Start installation.

# inutoc
# smit update_all
-> provide the directory for software as "/usr/sys/inst.images"
-> toggle the "Accept new license agreement" to Yes.
-> [Enter]

-> [Enter]. 
-> Upgrade starts...

DAC: no db2jdbc in java.library.path

2010-01-07T01:27:00.000-08:00

This error is generated when you try to establish connection to DB2 using DAC even if you have db2java.zip present in lib directory.

To resolve this issue, edit \dac_env.bat and add \SQLLIB\bin to path variable.

Oracle : Values of Hidden init parameters

2009-12-06T21:04:00.000-08:00

Hiddle parameter values can not be accessed using v$parameter view. Use the following script:

SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,
1, 'BOOLEAN',
2, 'STRING',
3, 'INTEGER',
4, 'PARAMETER FILE',
5, 'RESERVED',
6, 'BIG INTEGER',
'UNKNOWN') typ,
decode(ksppstdf,
'TRUE', 'DEFAULT VALUE',
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
1, 'IS_SESS_MOD(TRUE)',
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
1, 'MODSYS(NONDEFERED)',
2, 'MODSYS(DEFERED)',
3, 'MODSYS(*NONDEFERED*)',
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
1, 'MODIFIED_BY(SESSION)',
4, 'MODIFIED_BY(SYSTEM)',
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
2, 'ORA_STARTUP_MOD(TRUE)',
'FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
FROM x$ksppi x,
x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%pga%';

Configure DBD::ODBC on AIX 5.3

2009-11-18T01:38:00.000-08:00

DBD::ODBC perl module helps connecting to various datasources using ODBC. In order to get this module working, one needs to install unixodbc on aix platform.

1.Download unixODBC-2.2.11
- Version 2.2.14 has some issues causing installation to fail.
2.
export CC=xlc_r
export CCC=xlC_r

3. For 64 bit OS
export OBJECT_MODE=64
export CFLAGS=-q64

It is possible to install 32 bit binaries on 64 bit OS. Just do not set the above two variables.

4 Execute configure
./configure --prefix=/opt/unixODBC --disable-gui CC=xlc_r CXX=xlC_r
make
make install

5. Generate shared object libraries (/opt/unixODBC/lib)
ar -x libodbc.a
ar -x libodbcinst.a
ar -x libodbccr.a

5. Set the following environment variables

export ODBCINI=/usr/scratch/odbc.ini
export ODBCHOME=/opt/unixODBC
export LIBPATH=/opt/unixODBC/lib:${LIBPATH}

If perl is already installed, install module DBI and then DBD::ODBC. For new install follow the procedure mentioned below:

1. Download perl 5.10.1

2 If OS is 64 bit use flag -Duse64bitall
sh Configure -Duse64bitall -Dprefix=/opt/perl -des
make
make install

To install 32 bit perl binaries, do not use flag -Duse64bitall

3. Configure CPAN. First configure it in default mode. Then make changes to the proxy settings
cpan> o conf init /proxy/
--specify ftp_proxy and http_proxy to be used
cpan> 0 conf commit

4. Install DBI module
cpan> install DBI

5. install DBD::ODBC
cpan> install DBD::ODBC

you should be all set to use perl DBD::ODBC module !!!

CPAN : reset proxy settings

2009-11-16T21:17:00.000-08:00

To reset proxy setting for CPAN

CPAN> o conf init /proxy/

setting root prompt with sudo access

2009-11-16T21:11:00.000-08:00

If you have sudo access to a machine, it is convenient to set root prompt and then execute commands instead of using "sudo command " everytime.

"sudo su -" would get you the root prompt.

Removing Oracle 11gR2 software

2009-10-07T22:26:00.000-07:00

To clean up the installed Oracle 11gR2 software, execute the deinstall script. It is present in $ORACLE_HOME/deinstall directory.

$ORACLE_HOME/deinstall/deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /scratch/Oracle/OraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################
Install check configuration START

Checking for existence of the Oracle home location /scratch/Oracle11gr2/product/11.2.0/dbhome_1
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: /scratch/Oracle11gr2
Checking for existence of central inventory location /scratch/Oracle/OraInventory

Informatica : Setting codepage

2009-06-07T23:16:00.000-07:00

Informatica integration service sometimes fails with the following error when trying to startup:

LM_36011

Code page mismatch. Service process is running in code page [ISO 8859-1 Western European] whereas the service is configured in the Admin Console to run in code page [UTF-8 encoding of Unicode]

Set the following environment variables on the Linux based server hosting Informatica nodes:
setenv LANG en_US.utf8
setenv LC_ALL en_US.utf8
setenv INFA_CODEPAGENAME UTF-8

For windows, set environment variables LC_ALL and LANG to us_UTF8

Re-start the node service.

Now, integration service can be started with codepage set to UTF-8.

Oracle : Using WITH Clause for improving performance

2009-04-26T22:05:00.000-07:00

Many times using inline views destroy the execution plans. This is mainly because the execution plan of inline view interfere with the overall execution plan of the query.

To counter this, inline view can be taken to the top inside WITH clause. But this alone will not help. We need to incorporate hint /*+ materialize */ , as only then database treats the view as a physical table. Database creates a temporary table out of the view which is then used in the query. Execution plan is found far improved than before.

Query with following structure showed nested loop join between tables tab1 and tab2. This mainly was influenced by the inline view. Specifying USE_HASH hint also does not work.

SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
(SELECT DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD')))) tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2

Changing the query to the following using WITH caluse leads to perfect execution plan using HASH join .

WITH TIM as (
(SELECT /*+ materialize */ DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD'))))
)
SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2

DB2 : collecting detailed statistics

2009-04-14T02:27:00.000-07:00

to get the best optimizer execution plan, collecting detailed table statisitics might help:

db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'"
db2 -v -f "runstats.db2"

DB2 : Generating SQL Explain plan

2009-04-07T23:32:00.000-07:00

This is how explain plan can be generated in DB2 database.

Create a file statements.db2:
select deptno from dept@

db2expln -database U001DB -stmtfile statements.db2 -terminator @ -terminal -user siebel siebel

Section Code Page = 1252

Estimated Cost = 30087412.000000
Estimated Cardinality = 19275950.000000

Access Table Name = SIEBEL.S_ORG_EXT ID = 7,201
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 7776000
| Estimated Probe Size: 9948879872
| Access Table Name = SIEBEL.S_ORG_EXT ID = 7,201
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
...
...

INFORMATICA : Truncate Logs

2009-04-06T21:39:00.000-07:00

There was an unusual behavior when i proceeded with repository manager to truncate the existing session logs. Option "Truncate Log" under "Edit" menu was disabled. Selecting/expanding the required folder also did not help.

Since my informatica server was on AIX with client on windows, i did the following to truncate the logs:

$ pmrep
pmrep>connect -r Powercenter -d Domain_server1 -n Administrator -x Administrator

pmrep>truncatelog -t all
truncatelog completed successfully.

DB2 : Establish remote connection on Unix/AIX

2009-04-05T21:54:00.000-07:00

On server hosting DB2 Database:
db2set DB2COMM=tcpip
update database manager configuration using svcename 50000
db2stop
db2start

On client server (with DB2 client installed)
db2 UNCATALOG DATABASE U001DB
db2 UNCATALOG DCS DATABASE U001DB
db2 UNCATALOG NODE SERVER1

db2 CATALOG TCPIP NODE SERVER1 REMOTE SERVER1 SERVER 50000 REMOTE_INSTANCE DB2 SYSTEM SERVER1 OSTYPE AIX
db2 CATALOG DCS DATABASE U001DB AS U001DB
db2 CATALOG DATABASE U001DB AS U001DB AT NODE SERVER1 AUTHENTICATION DCS

Where, SERVER1 is the server where db2 server resides. U001DB is the db2 database name.

Test connection:

On client server:
db2 connect to U001DB user siebel using siebel

Database Connection Information

Database server = DB2/AIX64 9.1.6
SQL authorization ID = SIEBEL
Local database alias = U001DB

Oracle :Outer Join pitfalls

2009-02-24T21:19:00.000-08:00

Starting Oracle 10G, Optimizer processes outer joins in much efficient way. Execution plan now has "HASH JOIN RIGHT OUTER", which means that the table with scant records will be scanned first. Table which it is outer joined to will be scanned in the end. This is especially use full for Datawarehouse queries where Fact table is outer joined to lot of dimension tables. So, DB now does not have to temp read and write the fact table records to join them later to the dimension tables.

But, before one jumps into concluding that this new method is almost sure way to win, there are few things one should keep in mind.
1. Ensure NOPARALLEL query execution. This new execution plan for outer joins is severely affected by parallel query i.e. if dimension tables are scanned in parallel and then joined to the fact tables, query exhibits worst performance. To avoid parallel execution, check that tables or any participating indexes have degree set to "1". Alternatively, one an also disable parallel query execution at instance level by setting init parameter "max_parallel_servers" to "0". But this is no advisable as there could be other kind of queries which might want to leverage the advantages of parallel query execution.

2. Keep huge dimension tables out of the outer joins. If Dimension tables (the one which is outer joined) is huge, lets say...1 Million records (for pga 4 gig), outer join using new execution plan may not help. This is because now hash tables for all the joining dimension could not be kept in memory and DB anyways have to do multipass hash join. "HASH JOIN RIGHT OUTER" based execution plan is effective only if the outer joined tables/dimensions are smaller in size.

3. Ensure that all the participating tables has the statistics collected. Missing statistics on tables would cause optimizer to pick up execution plan as it existed before 10G and you may not see the new execution plan for outer joined query.

Informatica 8.6 : issue with pmcmd

2009-01-15T00:45:00.000-08:00

On AIX 5.3 pmcmd command fails with following error:

$ ./pmcmd

Could not load program ./pmcmd:
Could not load module /u01/Informatica/PowerCenter8.6/server/bin/libpmser.a.
Dependent module /lib/libz.a could not be loaded.
The module has an invalid magic number.
Could not load module pmcmd.
Dependent module /u01/Informatica/PowerCenter8.6/server/bin/libpmser.a could not be loaded.
Could not load module .

This issue has been fixed on one of the latest hotfixes.

To workaround the issue, rename /lib/libz.a so that libz.a which is shipped with Informatica is picked up.

Oracle: Correlated sub-queries effectiveness

2009-01-11T22:36:00.000-08:00

During ETL extraction and loading into the star-schema based warehouse, we generally confront with the idea of using outer join or correlated subqeries to add dimension table primary key into the fact table. to clarify more, take an example below:

INSERT INTO w_emp_f
SELECT a.row_wid loc_wid,
b.ename,
b.address
FROM w_emp_fs b
LEFT OUTER JOIN w_location_d a
ON a.datasource_num_id = b.datasource_num_id
AND a.integration_id = b.integration_id

In the example above, row_wid is the unique identifier of the row in Location dimension which needs to be added to Employee Fact table. In reality, there could be up to 30-40 dimensions which need to be joined to Fact table. Outer joining these dimensions is one of the way, but it could be a big overhead if the dimension tables are very large e.g. >2million records. Outer join may not perform.
This is when correlated subqueries come to rescue. We can frame the above load query as follows:
INSERT INTO w_emp_f
SELECT (SELECT row_wid
FROM w_location_d a
WHERE a.datasource_num_id - b.data_source_num_id
AND a.integration_id = b.integration_id) loc_wid,
b.ename,
b.address
FROM w_emp_fs b

Correlated query would only perform good if the dimension columns used in join condition are indexed. This way DB memory can cache the index and return the records from the dimension table fast for each of the record in fact table.

We can choose to segregate dimensions attached to the fact table based on their size. Depending on the memory configuration of the DB system we can hit&trial the size threshold. Dimensions having size lower than this threshold can be outer joined to the fact table and the ones with size higher than the threshold can be pushed to correlated subqueries.

Oracle : Resolving ORA-29275: partial multibyte character

2008-08-20T22:29:00.000-07:00

Data might sometimes get corrupted and when accessing can result in error:

In case data volumes are huge, its difficult to pin point the row which is causing this. Though, there is strategy for doing this.

sql > set autotrace traceonly statistics
sql> execute query (select column from table)

This will show you how many rows are processed before the error occured.
e.g.

22787220 rows selected.
.
Statistics
----------------------------------------------------------
6295 recursive calls
0 db block gets
65041120 consistent gets
10050575 physical reads
0 redo size
4195673524 bytes sent via SQL*Net to client
10634511 bytes received via SQL*Net from client
1519151 SQL*Net roundtrips to/from client
108956 sorts (memory)
0 sorts (disk)
22787220 rows processed

So, you can create an duplicate table and delete these many rows ,i.e., 22787220 records using rownum.

then you again run the query and check how many further records are selected before corrupt data is encountered. This way you can single out the data which is causing issue. Generally this kind of data corruption occurs as a result of DB upgrades.

How to fix:

A simple straight way is to delete the record. but that means loosing information.

Following could be done to resolve this:

update table set column=column||'';

i.e., append empty string to the column and that is it...data corruption is fixed without any loss of it.

similar exercise can be done for number type columns. they generally result in end-of-file on communication channel.

you can check the corruption of it by dumping its canonical form.

sql >select column, dump(column) from table.
---- --------------------------------------------------
1 Typ=2 Len=21: 193,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1actual dump should have been "193,2", no trailing "1"s.

these records also can be updated to get them return to normal canonical form.

update table set column=column + 1 - 1;

Oracle : storing strings in multibyte character format

2008-08-01T02:06:00.000-07:00

Creating tables using varchar2 datatype by default allocates specified value in bytes. That means if one is storing multi-byte character then you may not be able to store all the characters as some character might be multi-byte i.e taking 2 bytes per character.

We can store multibyte charater strings with specified number of characters using CHAR along with the varchar2 datatype. e.g
Create table lingual ( text varchar2(2o) CHAR);

this way you can store up to 40 bytes or 20 multibyte characters in column text.

To find out how many characters a column of a row has, use length() function. This will return the number of character, multibyte or single byte.

Use lengthb() to determine the number of bytes the column value contains.

Oracle : Finding Non-numeric column record in a table

2008-07-09T23:35:00.001-07:00

Find ALL rows where you have non-numeric characters only:

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#,
CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) > 0
;

Find ALL rows where you have numeric characters only:

Find ALL rows where you have numeric characters only:

select ROW_SEQ#, STRING#
from TMP_DATA
WHERE length(STRING#)
- Length( TRANSLATE(STRING#, CHR(1)||TRANSLATE(String#,
CHR(1)||'1234567890', CHR(1) ), CHR(1) ) ) = 0
;

Oracle: Deleting duplicates from a huge table

2008-04-04T03:16:00.000-07:00

delete from t
where rowid in
 (select rid
    from (select rowid rid,
                 row_number() over (partition by KEY_FIELDS
                                      order by rowid) rn
            from t )
                where rn <> 1 );

reference :  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1224636375004

Oracle : ORA-4030 in AIX Environment

2008-03-25T04:23:00.000-07:00

If a session tries to allocate pga memory more than the upper limit specified at OS level, it will error out with:
ORA-04030: out of process memory when trying to allocate 249880 bytes (QERHJ hash-joi,kllcqas:kllsltba)

checking the ulimit settings on AIX:
> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 256000
stack(kbytes) unlimited
memory(kbytes) 256000
coredump(blocks) 2097151
nofiles(descriptors) 2000

You can see that the maximum memory which can be allocated in chunk is 250M.

This needs to be set to unlimited.

edit /etc/security/limits file and set it as follows:

oracle:
fsize = -1
core = 2097151
cpu = -1
data = -1
rss = -1
stack = -1
stack_hard = -1
nofiles = 2000

here oracle is an OS user using which oracle database is installed. relogin to OS with this user and issue shall be fixed.

Oracle: HASH JOIN RIGHT OUTER

2008-01-23T21:32:00.000-08:00

Queries with outer join would be executed by scanning the driving table first, which is ought to be the bigger table which is outer joined with the other table. This approach compromises with the performance as Larger table has to scanned and hashed first and then joined to the other tables, contrary to the very nature of Hash joins where smaller tables are hashed and stored in memory and then joined over to the bigger table.

In 10G, there is a new concept HASH JOIN (RIGHT OUTER) --could be seen in execution plan also--which scans the smaller tables first and then join them over to the bigger table. This surely is a welcome change as this plan is quite superior to the earlier execution plans in terms of performance.

But, beware, all your tables involved in joins should have statistics computed for 10g to pick up the new execution plan.

Informatica : Lookups Versus Outer Joins

2008-01-21T21:56:00.000-08:00

A plain lookup over a dimension (fetching ROW_WID) can be replaced by an outer join over the dimension in the parent sql itself.

I have created a prototype to demonstrate this.

SILOS.TEST_BULK_SIL mapping is created as a copy of SILOS.SIL_PayrollFact (loads W_PAYROLL_F from W_PAYROLL_FS).

Original mapping had a mapplet mplt_SIL_PayrollFact. This mapplet has 12 lookups over various dimensions. It takes input (datasource_num_id, integration_id etc) from the parent sql, looks up the ROW_WID and loads into the fact table.

I removed this mapplet completely and incorporated all the 12 dimensions in the parent sql itself, Outer Joining them to W_PAYROLL_FS. All the expressions which were built in the mapplet were taken care in the main mapping itself (some of them may require more finishing).

Following are the results:

Mapping	Records Loaded (million)	Time Taken (hr.)	RPS (Reader)
SIL_PayrollFact (uses lookup)	183.3	16.3	3132
TEST_BULK_SIL (uses Outer Join)	183.3	6.02	8429

Results show that Outer join based mapping ran approx 2.7 times faster than the one based on lookups.

Again, lookups which involve some complex calculations may not be replaced by outer join.

Oracle: hash_area_size and sort_area_size

2007-12-20T03:36:00.000-08:00

higher value for hash_area_size, though is desirable, does not affect the hashing performance in a big way. Reason could be that memory is only used for creating hashes and then the records are flushed to the temp segments (disk).

Whereas in case of sorting, due to complex logic performed for sorting all the records, data moves back and forth multiple time between memory and disk, thus requiring higher sort_area_size for minimizing these iterations. Temp space requirement also falls to a good extent.

One needs to set workarea_size_policy to MANUAL and specify the desired sort and hash area sizes. if hash_area_size is not defined then it defaults to 2*sort_area_size.

Solaris : Troubleshooting Memory & CPU Consumption

2007-11-29T21:29:00.000-08:00

Use the following command to see the TOP 5 processes which are consuming system memory.
prstat -s size -n 5

output shows the resource statistics for each thread of a server application:

prstat -L -p 3295

Processes consuming the most CPU resource:

prstat -s cpu -a -n 8

PERL : diff two files

2007-10-31T22:35:00.000-07:00

use strict;

open TXT1, "1.txt" or die "$!";
open TXT2, "2.txt" or die "$!";
my %diff;

$diff{$_}=1 while ();

while(){
 print unless $diff{$_};
}

close TXT2;
close TXT1;

Remove ^M from files transferred from Windows to Linux

2007-10-31T04:45:00.000-07:00

Inside vi [in ESC mode] type:

:%s/^M//g (to make the ^M -> CTRL+V then CTRL+M)

Oracle ODBC : Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

2007-10-19T01:33:00.000-07:00

While configuring DSN using Oracle ODBC driver, one might encounter error "Driver's SQLAllocHandle on SQL_HANDLE_ENV failed". Cause being, Permissions of the remote desktop software did not allow the user access via the remote desktop software.

Do the following to resolve the issue:

1. Select Administration Tools , Local Security Setting and Local Policy.
2. Then select "User Rights Assignment"
3. Double click on "Create Global Objects"
4. Select Add User or Group.
5. Make sure Object Types Group Box is checked.
6. Select Locations and highlight the name of the server you are working on (Not the Domain).
7. Enter "Remote Desktop Users" or "Everyone" (without the quotes) into the Object Names Box.
8. Select OK.
9. Add the same users to the group "Power User".
10. If this does not work, after step 8 a reboot may be required.

Oracle: Spooling Column of type Long

2007-10-16T03:03:00.000-07:00

Set the following sqlplus options for spooling a long column. This will avoild truncation of lines to a particular width.

SQL> set trimspool on
SQL> set linesize 20000
SQL> set long 100000000
SQL> set longchunksize 2000
SQL> set pagesize 0

Oracle : Support for Very Large Memory (VLM) Configurations

2007-10-04T04:07:00.000-07:00

Oracle Database for Windows supports Very Large Memory (VLM) configurations in Windows to access more than the 4 gigabyte (GB) of RAM traditionally available to Windows applications.

The requirements for taking advantage of this support are:

The computer on which Oracle Database is installed must have more than 4 GB of memory.
The operating system must be configured to take advantage of Physical Address Extensions (PAE) by adding the /PAE switch in boot.ini. See Microsoft Knowledge Base article Q268363 for instructions on modifying boot.ini to enable PAE.
It is advisable (though not necessary) to enable 4GT support by adding the /3GB parameter in boot.ini. See Microsoft Knowledge Base article Q171793 for additional requirements and instructions on modifying boot.ini to enable 4GT.
The user account under which Oracle Database runs (typically the LocalSystem account), must have the "Lock memory pages" Windows 2000 and Windows XP privilege.
USE_INDIRECT_DATA_BUFFERS=TRUE must be present in the initialization parameter file for the database instance that will use VLM support. If this parameter is not set, then Oracle Database 10g Release 1 (10.1) behaves in exactly the same way as previous releases.
Initialization parameters DB_BLOCK_BUFFERS and DB_BLOCK_SIZE must be set to values you have chosen for Oracle Database.
Registry parameter AWE_WINDOW_MEMORY must be created and set in the appropriate key for your Oracle home. This parameter is specified in bytes and has a default value of 1 GB. AWE_WINDOW_MEMORY tells Oracle Database how much of its 3 GB address space to reserve for mapping in database buffers.
Once this parameter is set, Oracle Database can be started and will function exactly the same as before except that more database buffers are available to the instance. In addition, disk I/O may be reduced because more Oracle Database data blocks can be cached in the System Global Area (SGA).
If DB_BLOCK_SIZE is large, however, the default AWE_WINDOW_MEMORY value of 1 GB may not be sufficient to start the database.

Reference Note:
http://download-west.oracle.com/docs/cd/B14117_01/win.101/b10113/architec.htm#sthref58

Reset OC4J Admin Password

2007-09-25T22:21:00.000-07:00

Reset the oc4jadmin password using the following procedure while you are logged in as the user who installed the Oracle Application Server instance:

Stop OC4J and the Application Server Control.

Enter the following command in the Oracle home of the application server instance:

(UNIX) ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J
(Windows) ORACLE_HOME\opmn\bin\opmnctl stopproc ias-component=OC4J

Locate and open the following file in a text editor:

(UNIX)ORACLE_HOME/j2ee/home/config/system-jazn-data.xml
(Windows)ORACLE_HOME\j2ee\home\config\system-jazn-data.xml

Locate the line that defines the credentials property for the oc4jadmin user.

The following example shows the section of system-jazn-data.xml with the encrypted credentials entry in boldface type:



   jazn.com
   
    .
    .
          oc4jadmin
         OC4J Administrator
         OC4J Administrator
            {903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH

Replace the existing encrypted password with the new password.

Be sure to prefix the password with an exclamation point (!). For example:
```
!mynewpassword123
```

ORA-27047: unable to read the header block of file

2007-09-18T02:36:00.000-07:00

If while restoring backup, Db errors out with ORA-27047, it implied that the block header is corrupt.

Following three actions can be taken to restore DB:

1. resize the database file in the source system and again take the cold backup. Resizing reformats the block header.
2. use the existing control files to startup the database and then resize the file.
3. If control files can not be reused, because of the change in file location where db files will be restored, just take the cold backup copy of only the resized file from the source system. replace the existing corrupt header file with the newly backup copy and create the control file.
revert back to the original corrupt header file and try to open db with the newly created control file. It will fail with checkpoint mismatch asking for some file needing recovery.
Give the command "recover database using backup control file". When asked for the achive logs, supply the redo log file (try with each of the log files), it will recover the DB.

Oracle BITMAP Index Structure

2007-09-05T03:47:00.000-07:00

Index block with PCTFREE 50

select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_50' order by extent_id

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 53953
1 1115 53841
2 1196 53745
3 1742 447857
4 1744 446833

SQL > alter system dump datafile 1742 block 447857

row#0[6126] flag: ------, lock: 0, len=1906
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): 2b 40 d2 29 00 08 <-Starting Rowid
col 2; len 6; (6): 2b 40 d2 c1 00 1f <-Ending Rowid
col 3; len 1886; (1886): <-Bitmap for the key value

row#1[4221] flag: ------, lock: 0, len=1905
col 0; len 1; (1): 80
col 1; len 6; (6): 2b 40 d2 c1 00 30
col 2; len 6; (6): 2b 40 d3 48 00 67
col 3; len 1885; (1885):

There are two rows in the block. Totallying their column size :
1+6+6+1886+1+6+6+1885 = 3797kb
So, block is occupying approx 50% of the block (8192kb)

Index block with PCTFREE 10

SQL> select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_10' order by extent_id;

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 63057
1 1115 62945
2 1196 62849
3 1742 457377
4 1744 456177
5 1786 429665
6 1809 411889

alter system dump datafile 1742 block 457377

row#0[4495] flag: ------, lock: 0, len=3537
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): b3 c6 f6 2b 00 28 <-Starting rowid
col 2; len 6; (6): b3 c6 f6 ce 00 37 <-Ending rowid
col 3; len 3517; (3517): <-Bitmap for the key value

row#1[957] flag: ------, lock: 0, len=3538
col 0; len 1; (1): 80
col 1; len 6; (6): b3 c6 f6 ce 00 48
col 2; len 6; (6): b3 c6 f7 74 00 37
col 3; len 3518; (3518):

There are two rows in the block. Totallying their column size:
1+6+6+3517+1+6+6+3518 = 7161 kb
So, block is occupying approx 90% of the block (8192kb)

MSSQL : Collation - a way out of case-insensitiveness

2007-08-16T23:12:00.000-07:00

Sql Server Databases by default are case-insensitive, both for the object names as well as the data contained in them.

To change a database to case sensitive one, its collation needs to be changed.

To check for the existing collation, check the properties of database and look for a row with heading "Collation"
by default collation is set to "SQL_Latin1_General_CP1_CI_AS". This collation renders database as case-insensitive.

To make your database case-sensitive (as some third party software would expect them to be), we need to change the Collation for the Database.

There are many collations available. you can check them by executing the following statement
select * from ::fn_helpcollations()

Collation "Latin1_General_BIN" is found to work fine for the purpose of making Database case-sensitive.

To change collation, execute an alter statement against the Database

Alter Database TESTDB collate Latin1_General_BIN

Note that the existing tables and their columns would still follow the earlier collation unless recreated or rebuilt.

Collation on specific Tables and their columns as also be changed using 'Alter Table alter column varchar(10) collate Latin1_General_BIN null

MSSQL : Deleting duplicate rows

2007-08-13T02:42:00.000-07:00

Select the duplicate key values into a holding table. For example:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Select the duplicate rows into a holding table, eliminating
duplicates in the process. For example:
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

At this point, the holddups table should have unique PKs,
however, this will not be the case if t1 had duplicate PKs,
yet unique rows
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

If count(*) returns more than 1 for certain rows,
determine which of the rows to delete which have the
duplicate keys but unique rows and then only process further.

Delete the duplicate rows from the original table.
For example:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2


Put the unique rows back in the original table.
For example:
INSERT t1 SELECT * FROM holddups

DAC/Informatica src files connection

2007-08-12T23:22:00.000-07:00

Informatica Server Variable > $PMSourceFileDir must be set exactly the
same as the DAC System Properties > InformaticaParamatereFileLocation as
follows: C:\oracleBI\DAC\Informatica\parameters (NB***no spaces in folder
name****)

Copy all files from OracleBI\dwrep\bin\Informatica\LkpFiles to the location specified in $PMSourceFilesDir

Copy all files from OracleBI\dwrep\bin\Informatica\SrcFiles to the location specified in $PMSourceFilesDir

Both Informatica and DAC must be able to access the folder specified above.

AWE and /3GB Switch for Windows

2007-08-09T00:01:00.000-07:00

Windows 32-bit Operating Systems can only address upto 4GB of available memory. In order to access more than 4 gb of install memory we have to go for Addressing Windowing Extensions. For this set /PAE in the boot.init file and reboot the server.

As per blog http://blogs.msdn.com/oldnewthing/archive/2004/08/19/217087.aspx :

The two are independent. AWE is how programs access physical memory. PAE is how the CPU accesses physical memory. AWE requires PAE in order to allocate more than 4GB of physical memory. You need PAE in order to access more than 4GB of physical memory, AWE or no AWE

For systems having up to 4GB of RAM, Server spares only 2GB for user mode memory (rest of 2GB is for kernel). We can strech up to 3GB by using tag /3GB in boot.ini.

Note: /3GB switch would limit use of memory to only 3GB even if system has 16 GB RAM installed.

Windows 2003 Enterprise Edition can by default address up to 16GB of RAM.

How to update boot.init
Go to My Computer.
Right click -> Properties
Go to Advanced Tab
Startup and Recovery -> Settings
Edit.

Informatica shared memory

2007-08-08T22:32:00.000-07:00

Informatica Server setup has a Load Manager where you can define the Shared Memory. This the total memory which server allocates to a particular session (defined by session parameter DTM Memory Buffer) default value 12000000.

MS SQL Server is too case sensitive

2007-08-08T03:32:00.001-07:00

Databases are case sensitive, we all know. MS Sql takes it a level further (good or bad ?). It is case sensitive even to the table names.

Don't believe me, try out yourself !!!

Restore MSSQL DB from mdf,ldf and ndf files

2007-08-08T03:21:00.000-07:00

MS Sql DB comprises mdf, ndf and ldf data files. If you happen to have taken a backup of the database (.bak), then restoring it on to a different instance is quite straight forward (using SQL Server Management Studio).
But in case you just have the database files with you, then one needs to attach it to the instance. Attach is a better technical term for this than restore.

Following is the way we can attach the Database to an instance:

use master
GO

EXEC sp_attach_db @dbname = N'NewDB',
@filename1 = N'C:\Program Files\MasterDB.mdf',
@filename2 = N'C:\Program Files\MasterDB_log.ldf',
@filename3 = N'C:\Program Files\DATA2.ndf',
@filename4 = N'C:\Program Files\DATA3.ndf'