After the keynote Tom Kyte had a talk on “All about Metadata”. Tom Kyte told about how wrong metadata in the database is influencing the performance. So actual explaining the different reasons the performance can be wrong only due to wrong metadata or the lack of it in the database. It is important to feed the database with data you know of the data, because you know your database the best. That can mean adding dimensions, adding materialized views, etc. His most important points were, “Datatypes Count”, “Proper Lengths matter”, “Constraints are important” and “Metadata Matters”, so ensure they are right and there, and the optimizer can do its job the best way possible.

Tom Kyte’s talk was followed up by a talk from Richard Foote. His talk subject was “Oracle Indexing Myths”. When he started his presentation he seemed to be a big fan from “David Bowie” and “Radio Head”, from who he used a lot of times, names to refer to database objects, like table “BOWIE”. Richard told about a lot of different index myths and some of them where quite interesting. The myths he talked about were:
•    Deleted Index Space Is Deadwood
•    Indexes Always Prevent Sort Operations
•    CBO won’t consider an index when retrieving > x% of rows
•    Put Most Discriminating Column 1st In Concatenated Index
•    Small tables Don’t Benefit From Being Indexed
•    Bitmap Indexes Help With Low Cardinality Conditions
•    Bitmap Indexes Inefficient With High Cardinality Columns

Just to give a small impression I will give a few of the answers. In general we can say that deleted index space will be reused, so the idea of ending up with a lot of deadwood in our indexes is really a myth. For indexes and sorts it is always important to realize that “nulls” in columns are not put in the index and so having the index is not always a guarantee that the sorting will actually happen. For indexes it is important to know that the clustering factor is highly involved in choosing an index for an execution plan. Also the amount of rows which will be expected to be retrieved from the index in compare with the table are from influence. So will the index actually been used or not?

The myth of putting the most discriminating column first in a concatenated index, looks very challenging, but in fact it is really a myth. I was even a bit surprised to find this out, so why was my second thought. From base the most discriminating column should be first because of the to be accessed leaf blocks in the index are less, but in fact this doesn’t matter. So if another column is first in the index it will not influence the amount of the to be accessed leaf blocks. Richard showed with some small test cases that it really doesn’t matter, index performance stayed the same. Ok, this said what will happen if we place the most discriminating column at the first, it will actually give problems, like the “Index skip scan”, it will work only efficient as a little distinctive column is first in the index, instead of the other way around. Another index option like index compression will only work as the little distinctive column is first. This has to do with the way an index compression works.

This was just a few examples of the myth coverage Richard did. I think it is very good before starting designing index to check out the work Richard did in this area.

After having had lunch together and making new contacts the presentation room was split in half for two tracks of presentations. I had the choose to go to Alex Gorbachev with “Battle Against any Guess” or Dan Norris with “Consolidation Strategies for Oracle Database Machine”. I choose for Dan and got a good impression on how the Exadata was operating within the database machine. Also things like Oracle I/O Resource Manager and Oracle Database Resource Manager were discussed. After the presentation from Dan I had a small discussion with Christian Antognini, regarding the way a “smart scan” was operating. In my opinion the database is sending a “Bloom Filter” signature to the disk controllers were with the “Bloom Filter” a lot of the not interesting data is pre-filtered before sending it to the database instance. The sent data to the database instance will not be a normal database block format, so it will pass the database buffer cache. The passing of the database buffer cache was also mentioned by Dan in his presentation.

My next presentation I attended was done by Doug Burns with “How I Learned to Love Pictures- Oracle 10g/11g Performance Analysis Using OEM”. Doug tried to do a lot of demos, but due Murphy’s Law he had a lot of issues giving them. To do the load testings on his laptop system he used “Swingbench” as the load-tool. The demo showed how you can use in a very clear way Oracle Enterprise Manager (Grid). Doug showed that the first “Performace Home” screen was based on measured data and the “Top Activity” screen was based on sampled data (ASH). Although a lot of the demos were able to be shown Doug really tried hard to give the message, and in my opinion he did.

Next stop was Kevin Closson with “Ten Years After Y2K, and We Still ‘Party Like It’s 1999’”. Kevin showed a lot of development over the past ten years. He explained about how the techniques over the years have changed and how bottlenecks changed for cpu, memory and I/O. He talked about experiences with Oracle Database File System (DBFS) and how he actually had installed on this file system a complete database including Oracle Home. So he built a database in a database, from a geek point of view like me that is pretty cool. While presenting he had a little buddy with him, the pics will show you how…

The final presentation of the day was for me Kerry Osborne with “Controlling Execution Plans (Without Touching the Code)”. In his presentation he talked about the possibilities of influencing the execution plan of a query, without actual change the query itself. A suggestion could be changing database parameters, well this can be seen as “Big Knob Tuning” and would be very unadvisable.  Some other ideas are start adding new access paths, or remove some, in plain Oracle language this means add indexes or remove some. The other idea could be by start changing stats on objects by the DBMS_STATS package. Well all these suggestion will not be very specific and a lot of different queries are influenced. Lucky Oracle did some other things too. Kerry told that Oracle made three ways, Outlines, Sql profiles and Sql Baselines. These three ways where created with different goals by Oracle, but do all basically the same, with some small twists. The use of outlines was a half baked product, one of the issues was to set every time “use_stored_outlines” to true, with every restart of the database instance. SQL profiles were three quarter baked but are not perfect either, one of the advantages over outlines is the fact that they can be applied to multiple statements. The SQL Tuning Advisor (STA) Profiles (SQL Baselines), are still not perfect but do multiple things for the stability, first of all they can add hints and second they can store metadata for the query to give the optimizer a better understanding of the data and data distribution.

The way Kerry showed the possibilities were very nice and a very good closing of HOTSOS day 1.

Regards,

Gerwin

SELECT *

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm

FROM scott.dept d,

scott.emp e

WHERE e.deptno (+) = d.deptno

)

/

 

 

 

 

 

 

 

 

 

Based on this base query the following devisions can be made

prompt — Divide into two cases

prompt — departments whit employees

prompt — departments whitout employees

SELECT *

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm

, 1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

union

SELECT d.deptno,

d.dname,

d.loc,

null,

null,

null,

null,

null,

null

,2

FROM scott.dept d

)

/

 

 

 

 

 

 

 

This gives of course double records. The trick I did was giving an extra column to the queries to know from which query the retrieved record is comming from (see above). Further more I had to get rid of the double records, by using an analytic function:

prompt — Too many rows

prompt — records from query id 1 will selected again in query id 2

prompt — Analyic functions to the resue?

SELECT deptno

, deptno_prev

, id

, dname

, loc

, empno

, job

, mgr

, hiredate

, sal

, comm

FROM

(SELECT a.deptno

, a.dname

, a.loc

, a.empno

, a.job

, a.mgr

, a.hiredate

, a.sal

, a.comm

, a.id

, lag(a.deptno,1,-99) over (partition by a.deptno order by a.deptno, a.id) deptno_prev

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm ,

1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

UNION

SELECT d.deptno,

d.dname,

d.loc,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL ,

2

FROM scott.dept d

WHERE 1=1

) a

)

WHERE 1=1

order by deptno, id, deptno_prev

/

prompt — Now with where clause

SELECT deptno

, deptno_prev

, id

, dname

, loc

, empno

, job

, mgr

, hiredate

, sal

, comm

FROM

(SELECT a.deptno

, a.dname

, a.loc

, a.empno

, a.job

, a.mgr

, a.hiredate

, a.sal

, a.comm

, a.id

, lag(a.deptno,1,-99) over (partition by a.deptno order by a.deptno, a.id) deptno_prev

FROM

(SELECT d.deptno,

d.dname,

d.loc,

e.empno,

e.job,

e.mgr,

e.hiredate,

e.sal,

e.comm ,

1 id

FROM scott.dept d,

scott.emp e

WHERE e.deptno = d.deptno

UNION

SELECT d.deptno,

d.dname,

d.loc,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL ,

2

FROM scott.dept d

WHERE 1=1

) a

)

WHERE 1=1

AND ( id = 1 — department has employees

OR

( id = 2 AND NOT (deptno_prev = deptno) ) — Previous department in partition is either -99 (first record) or another deparment

)

order by deptno, id, deptno_prev

/

 

 

 

 

 

 

So now we have written our base query with an outerjoin to a query without an outerjoin. In some cases the described method can help you enhance such queries in a very significant way.

Regards, Gerwin

SQL> drop table xxx
2 /
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

Due to the fact that this looked a bit weird, I did a small test on my 11.1.0.7 database. The following test case I did:

login as: oracle
oracle@ghperfsuite’s password:
Last login: Wed Apr  1 20:37:27 2009 from 10.10.1.1
[oracle@ghperfsuite ~]$ . oraenv
ORACLE_SID = [oracle] ? ghperf
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@ghperfsuite ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Apr 2 05:27:57 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> set pagesize 1000
SQL> select * from dual
2  /

D
-
X

SQL> create table xxx (id number);

Table created.

SQL> select count(*) from dual;

COUNT(*)
———-
1

SQL> insert into dual values (’A');

1 row created.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
———-
1

SQL> drop table xxx
2  /
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

SQL> desc dual
Name                                      Null?    Type
—————————————– ——– —————————-
DUMMY                                              VARCHAR2(1)

SQL> delete from dual where dummy = ‘A’;

1 row deleted.

SQL>  select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
———-
1

SQL> drop table xxx
2  /

Table dropped.

SQL>

The test case shows that even that you can’t see the extra row in the dual table it still can give lots of trouble. One of the important issues is that you can’t drop a table anymore.

See also the entry at the OTN forum.

Regards, Gerwin

OPERATION            OPTIONS                 OBJECT# NAME                         

——————– ——————– ———- ———————————–

SELECT STATEMENT                                     .                          

SORT                 ORDER BY                        .                          

TABLE ACCESS         BY INDEX ROWID           120713 XXX.TABLE_WITH_MANY_COLUMNS

INDEX                RANGE SCAN               121558 XXX.INDEX_WITH_FEW_COLUMNS

In a lot of cases we deal with a query for example which is returning fewer columns than exists in the involved table like four, from an involved table having twenty columns. Although the execution plan looks already pretty “OK”, it still results in a query which is in top three most resource taking queries.

To enhance the query we can use “Fat Indexes” or nowadays better known “Tapio Indexes” (I call them like that nowadays). What are these kind of indexes… In principle nothing really new, but for me a couple of years ago an (re)eye opener by Tapio Lahdenmaki. This Finish independent database performance consultant and instructor gave a presentation at Miracle Open World 2007 in Denmark, I attended. Tapio can present this topic in a very special way, and in my opinion “his message” should be known by all developers and dba’s.

The most important thing of “his message” is the fact that we should create an index which is highly selective and contains all the columns asked in the query. So in principle we create an index with forexample two selective columns, with the most selective one in front and behind these two columns the other selected columns. When creating such an index an execution plan would look like:

OPERATION            OPTIONS                 OBJECT# NAME                        

——————– ——————– ———- ——————————–

SELECT STATEMENT                                     .                            

SORT                 ORDER BY                        .                           

INDEX                RANGE SCAN               128140 XXX.IND_WITH_ALL_SELECT_COLS

The big advantage is that only the index is accessed and not the table anymore, what can result in a decrease of elapsed time for the query with a factor four or even more. Why this is much better? Let we go back how indexes and tables are stored on the physical storage.

An index is stored in an ordered way on the storage, so an index range scan will mean that data is retrieved from the storage in a sequential read from a storage point of view and so a read-head from a spindle can stay on almost the same place. This eliminates seek times and latency on the physical storage spindles.

A Table is stored in a non ordered way on the storage, so accessing table rows will almost always result in accessing the data in a scattered read from a storage point of view. This implies that a lot of seek and read times are wasted.

Of course the existence of caches makes it a bit different but still the basic principles are valid, this means also for striping.

So when only an index is accessed instead of index and table, we don’t waste read times for columns we don’t need (table rows), and we eliminate a lot of seek times for retrieving the different table rows.

If you want to know more about this kind of index design, you can read the book “Relational Database Index Design and the Optimizers” from Tapio Lahdenmaki.

In a lot of cases it is very interesting to be able to trace Self Service Applications (CRM) in Oracle Applications 11i. Most of the time it is very hard to understand what is going on in such a session and in that case it might be very handy to get the SQL behind it. To do this there is a very nice not really known feature of the profile option “Initialization SQL Statement - Custom”. With this profile option it is possible to trace any session of a given Apps user on any level. To ensure you don’t end up with a hanging apps login (quote’s should be placed the right place) you can use the following scripts just on the SQL plus prompt to activate and stop the tracing.

Start:

whenever sqlerror exit failure rollback;

set heading off
define un = &username
define deftrace=’begin fnd_ctl.fnd_sess_ctl(””””,””””,””TRUE””,””TRUE””,””LOG””, ””ALTER SESSION SET EVENTS=””||””””””””||””10046 TRACE NAME CONTEXT FOREVER, LEVEL 12””||””””””””);end;’

select ‘Inserting profile option value’
from dual;

insert into fnd_profile_option_values
select 0,profile_option_id, 10004, user_id,sysdate,0,sysdate,0,0, ‘&deftrace’, null, null
from fnd_profile_options, fnd_user
where profile_option_name = ‘FND_INIT_SQL’
and user_name = ‘&un’
/

commit;

Stop:

select ‘Press ENTER to disable trace for’, ‘&un’
from dual;

select ‘&ENTER’
from dual;

delete fnd_profile_option_values
where application_id=0 and level_id=10004
and level_value = (select user_id
                   from fnd_user
                   where user_name = ‘&un’)
and profile_option_id = (select profile_option_id
                         from fnd_profile_options
                         where profile_option_name = ‘FND_INIT_SQL’) —> What is the name in Applications.
/
select ‘Type commit; or exit; to delete the inserted profile option value’
from dual;

Just to use the scripts the following steps and instructions should be used:

• start the script in sqlplus and provide the application username to be  traced in Self Service and give an Enter (this session stays open now)
• Login with the application user, and reproduce the to be traced functions.
• Logout with your application user.
• stop in the sqlplus session the tracing for the user.

* Important for the script:

• Check if define deftrace is one line ‘…..’
• Level 12 (waits&binds) can be changed to 8(waits), 4(binds) and 1(SQL_TRACE)
• Be aware that using the profile option will turn on the trace for *ALL* processes executing for that user, including any scheduled concurrent processes.

To identify the tracefiles created in the user dump destination better you can add a ‘tracefile_identifier’ to the SQL. In that case use the deftrace command below:

BEGIN FND_CTL.FND_SESS_CTL(”,”,”,”,”,’ALTER SESSION SET EVENTS=’||””||’ 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ‘||””|| ‘ TRACEFILE_IDENTIFIER = ‘ || ”” || ” || ””); END;

The above procedure I needed for a customer I was working for recently. I hope if you have comments you will not hesitate to comment.

Regards, Gerwin

To investigate I tried to create a errorstack on the error, but without luck. So I started to do a sqltrace and found out that the ORA-25330 is actually on the server translated as ORA-40206. After I found that I created the following small test in TOAD:

alter session set sql_trace=true;

alter session set events ‘40206 trace name ERRORSTACK level 3′;

select ‘REPRODUCING ORA-25330 BY GERWIN HENDRIKSEN’ from dual;

DECLARE
v_accuracy NUMBER(10,9);
BEGIN
DBMS_PREDICTIVE_ANALYTICS.PREDICT(
accuracy => v_accuracy,
data_table_name => ‘GHH_RESULT’,
case_id_column_name => ‘TIME’,
target_column_name => ‘ELAT’,
result_table_name => ‘GHH_PREDICT_ORA25330′);
DBMS_OUTPUT.PUT_LINE(’Accuracy = ‘ || v_accuracy);
END;
/

select ‘REPRODUCED ORA-25330 BY GERWIN HENDRIKSEN’ from dual;

exit TOAD

Then the following information could be found in the trace file (use 11.1.0.6 by the way):

----- Error Stack Dump -----
ORA-40206: ongeldige instellingswaarde voor instellingsnaam SVMS_CONV_TOLERANCE
----- Current SQL Statement for this session (sql_id=394st4vxujtxa) -----
SELECT dm_mod_build ('DM$TANNWBZBBEUCBGVRNYNRTE',CURSOR(SELECT "HDISKPOWER10_HOT600", "RLFREE_HOT720", "HDISK35_HOT600", "HOT737_CPU", "HDISK48_HOT600", "HDISK0_HOT600", "HDISK40_HOT600", "HDISK23_HOT600", "RUNABLE_HOT720", "PGIN_HOT720", "WAIT_HOT720", "HDISK2_HOT720", "HOT600_CPU", "HDISK28_HOT600", "HDISK6_HOT600", "CYCLES_HOT720", "RT_HOT720", "SYS_HOT730", "HDISK54_HOT600", "PGSIN_HOT730", "HDISK51_HOT600", "HOT606_CPU", "RT_HOT730", "HDISK11_HOT600", "HDISK10_HOT600", "SYS_HOT720", "HDISK0_HOT730", "USER_HOT600", "PRLFREE_HOT720", "FAULTS_HOT720", "HDISK8_HOT600", "RT_HOT600", "PGSOUT_HOT600", "HOT604_CPU", "VTFREE_HOT730", "HDISK64_HOT600", "HDISK46_HOT600", "WAIT_HOT730", "HDISK29_HOT600", "CYCLES_HOT600", "HDISK66_HOT600", "HDISK12_HOT600", "HDISK56_HOT600", "HDISK14_HOT600", "PRLFREE_HOT730", "VT_HOT720", "HDISKPOWER7_HOT600", "HOT601_CPU", "VTFREE_HOT720", "RLFREE_HOT730", "HDISK60_HOT600", "HDISK15_HOT600", "HDISK19_HOT600", "HDISK59_HOT600", "VT_HOT600", "HDISK22_HOT600", "PGOUT_HOT720", "RUNABLE_HOT600", "HDISK47_HOT600", "HDISK37_HOT600", "HOT730_CPU", "HDISK30_HOT600", "HDISK13_HOT600", "HDISK4_HOT720", "HOT705_CPU", "HDISK61_HOT600", "PGSOUT_HOT730", "HDISK5_HOT600", "HDISK63_HOT600", "HOT727_CPU", "HDISK42_HOT600", "HOT721_CPU", "WAIT_HOT600", "PGOUT_HOT730", "HDISKPOWER1_HOT600", "HDISK55_HOT600", "HDISK9_HOT600", "RECLAIMS_HOT600", "HDISK65_HOT600", "HDISK57_HOT600", "HDISK2_HOT730", "PVTFREE_HOT730", "HDISK21_HOT600", "HDISK62_HOT600", "RECLAIMS_HOT730", "PVTFREE_HOT600", "HDISKPOWER9_HOT600", "HDISKPOWER2_HOT600", "HDISK67_HOT600", "HDISK0_HOT720", "PGSIN_HOT720", "HDISK3_HOT600", "HDISK17_HOT600", "HDISK3_HOT730", "HDISK1_HOT600", "HDISK3_HOT720", "PGIN_HOT730", "HDISK34_HOT600", "HDISK24_HOT600", "RLFREE_HOT600", "HDISK20_HOT600", "HDISK53_HOT600", "RECLAIMS_HOT720", "HDISK26_HOT600", "HOT602_CPU", "VTFREE_HOT600", "HDISK27_HOT600", "HDISK32_HOT600", "HOT603_CPU", "HDISK4_HOT600", "HDISK2_HOT600", "HDISK31_HOT600", "HDISK1_HOT720", "FAULTS_HOT730", "PVTFREE_HOT720", "PGSOUT_HOT720", "CYCLES_HOT730", "PRLFREE_HOT600", "ELAT", "HDISKPOWER0_HOT600", "HOT738_CPU", "HDISK50_HOT600", "HDISKPOWER3_HOT600", "HDISK36_HOT600", "HOT722_CPU", "HDISK41_HOT600", "HDISK43_HOT600", "HDISK16_HOT600", "SCANS_HOT730", "USER_HOT720", "HDISK1_HOT730", "SYS_HOT600", "SCANS_HOT600", "HDISK25_HOT600", "HDISK52_HOT600", "HDISK44_HOT600", "HDISKPOWER8_HOT600", "PGIN_HOT600", "HOT707_CPU", "HOT720_CPU", "PGSIN_HOT600", "HDISKPOWER6_HOT600", "HDISK49_HOT600", "RUNABLE_HOT730", "HDISK18_HOT600", "HDISK38_HOT600", "PGOUT_HOT600", "HDISK39_HOT600", "HDISK45_HOT600", "HDISK7_HOT600", "VT_HOT730", "FAULTS_HOT600", "SCANS_HOT720", "HDISK33_HOT600", "HDISKPOWER4_HOT600", "USER_HOT730", "HDISK58_HOT600", "HDISKPOWER5_HOT600" FROM "HOTSOS"."DM$TXFM_FGLJPVFEXMNNHTQRDVJDBV" ORDER BY ora_hash("TIME"),"TIME"),CURSOR(SELECT setting_name, setting_value FROM TABLE(CAST(:1 AS DM_Model_Settings))), 2) FROM dual
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x2d168b34 61 SYS.DMMODBIMP
0x2d168b34 58 SYS.DMMODBIMP
0x2d1d2374 941 package body SYS.DBMS_DATA_MINING_INTERNAL
0x2d1d2374 2729 package body SYS.DBMS_DATA_MINING_INTERNAL
0x2d1c8bac 1627 package body SYS.DBMS_DATA_MINING
0x2d1daef0 524 package body SYS.DBMS_PREDICTIVE_ANALYTICS
0x2d1daef0 1292 package body SYS.DBMS_PREDICTIVE_ANALYTICS
0x2a65c2e8 4 anonymous block

—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
skdstdst()+38 call kgdsdst() BFE0F18C ? 2 ?
ksedst1()+88 call skdstdst() BFE0F18C ? 0 ? 1 ? A5A89EE ?
853D37A ? A5A89EE ?
ksedst()+33 call ksedst1()+8 BFE104A8 ? B6E564 ? 2050033 ?
0 ? FF ? 2004 ?
dbkedDefDump()+1046 call ksedst() 0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksedmp()+47 call dbkedDefDump() 3 ? 0 ?
dbkdaKsdActDriver() call 00000000 3 ? 110000 ? 4048CF ? 11 ?
+798 4048CF ? 11 ?
dbgdaExecuteAction( call 00000000 B6E564 ? BFE10914 ?
)+61
dbgdaRunAction()+33 call dbgdaExecuteAction( B6E564 ? ED37DD0 ? 20C0002 ?
7 ) BFE10914 ?
dbgdRunActions()+64 call dbgdaRunAction() B6E564 ? 6FF874 ?
dbgdProcessEventAct call dbgdRunActions() D8FFC92 ? D8FFC9B ?
ions()+580
dbgdChkEventKgErr() call dbgdProcessEventAct B6E564 ? F563360 ? 6FF984 ?
+369 ions() 118 ? 6FF984 ? 1 ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() B6E564 ? 6FFF04 ? 9D0E ?
r()+23
__PGOSF28_ksfpec()+ call dbkdChkEventRdbmsEr 9D0E ? 800 ? 0 ? 100000 ? 0 ?
183 r() 6FFF04 ?
dbgePostErrorKGE()+ call 00000000 F563360 ? 9D0E ?
825
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() F563360 ? 6D3E88 ? 9D0E ?
43
kgesev()+256 call 00000000 F563360 ? 6D3E88 ? 9D0E ?
ksesec1()+29 call kgesev() F563360 ? 6D3E88 ? 9D0E ? 1 ?
BFE11294 ?
dmsvmbLSettings()+6 call ksesec1() 9D0E ? 1 ? 13 ? EBDEB50 ?
85
dmsvm_build()+644 call dmsvmbLSettings() BFE114B4 ? BFE114E4 ?
CD175CC ? CD1759F ?
dmmodbftch()+679 call dmsvm_build() 10E429C ? BFE114E4 ? 6E4BC4 ?
6E4CE0 ?
spefcpfa()+85 call 00000000 BFE122BC ? 104729C ?
1047208 ? 6E4CC4 ? 0 ?
6E4BC4 ? 6E4CE0 ?
spefmccallstd()+734 call spefcpfa() BFE11FF4 ? 7 ? 6E4C80 ?
BFE11780 ? BFE1178C ? 0 ?
pextproc()+70 call spefmccallstd() BFE12244 ? BFE1206C ?
BFE11EEC ? BFE11FF4 ? 0 ?
__PGOSF294_peftrust call pextproc() BFE12244 ? BFE1206C ?
ed()+114 BFE11EEC ? BFE11FF4 ?
psdexsp()+186 call 00000000 BFE12244 ? BFE11848 ?
924DB1F ? F563360 ? 10470CC ?
BFE11848 ?
rpiswu2.()+404 call 00000000 BFE11E50 ? 1 ? 10F9A40 ?
F563360 ? 0 ? 36 ?
psdextp()+544 call rpiswu2() 324538D4 ? 59 ? 32453918 ?
2 ? BFE11E6C ? 59 ?
pefccal()+484 call 00000000 10E3578 ? BFE1206C ?
BFE11EEC ? 0 ? 20003 ?
BFE12244 ? 98E1266 ?
pefcal()+175 call pefccal() BFE122BC ? 0 ? 926CB7F ?
F4B2504 ? 1 ? EF483C4 ?
pevm_FCAL.()+140 call pefcal() BFE12244 ? 8 ? 35 ? 0 ? 0 ?
BFE1227C ?
pfrinstr_FCAL()+56 call pevm_FCAL() 10E3698 ? 26F94620 ?
pfrrun_no_tool()+40 call 00000000 10E3698 ? 26F93C5A ?
10E36D8 ?
pfrrun()+783 call pfrrun_no_tool() 0 ? E5A3135 ? BFE12520 ?
plsql_run()+742 call pfrrun() 10E3698 ? 0 ? 0 ? 0 ? 0 ? 0 ?
peidxr_run()+205 call plsql_run() 10E3698 ? 2 ? 101AEDC ?
peidxexe()+55 call peidxr_run() 10E3530 ? 1010002 ? 101AEDC ?
A95818 ? 6C00 ? BFE12CF4 ?
kkxdexe()+306 call peidxexe() 10E3530 ? EF483E0 ? F4B2504 ?
7A ? 11 ? 0 ?
kkxmpexe()+167 call kkxdexe() 10E3530 ? A966D8 ? A95818 ?
kgmexwi()+575 call 00000000 F563360 ? 26F3395C ? 9BAA58 ?
10E3530 ? 2D168B34 ? A95818 ?
A95838 ?
kgmexec()+1243 call kgmexwi() F563360 ? 1 ? BFE132DC ?
26F3395C ? 9BAA58 ? FD30AC ?
BFE10002 ? 2D168B34 ?
A95818 ? BFE1315C ?
evapls()+597 call kgmexec() F563360 ? 1 ? BFE132DC ?
2E68937C ? 2AE928F0 ?
A95900 ? A95818 ? 0 ?
A958EC ? 0 ? 79 ?
evaopn2()+436 call 00000000 2C2B89A8 ? BFE14024 ?
EB3352C ? A981D8 ? BFE13B74 ?
E459135 ?
qximeop_fbk.()+397 call 00000000 2C2B89A8 ? 3D9CADBD ? 0 ? 0 ?
0 ? 0 ?
qximeop()+67 call qximeop_fbk.() 2C2B89A8 ? 0 ? C0 ?
BFE13988 ? FCE010 ?
BFE1398C ?
qerocifc()+501 call qximeop() 2C2B89A8 ? 0 ? C0 ?
BFE13988 ?
qerocplFetch()+915 call qerocifc() 2AFD0B0C ? F7EA60 ? 1 ?
qerocFetch()+294 call qerocplFetch() 324538D4 ? 0 ? BFE13A30 ? 0 ?
1 ? F7ED5C ?
qerstFetch()+322 call 00000000 2AFD0B0C ? F7EA60 ? 0 ? 0 ?
1 ? BFE13A2C ?
qervwFetch()+119 call 00000000 2C2B858C ? F7ED5C ? 0 ? 0 ?
1 ? 0 ?
qerstFetch()+322 call 00000000 2C2B8540 ? F7EDD4 ? 0 ? 0 ?
1 ? 3E8 ?
kokbscl()+442 call 00000000 2C2B8228 ? F7EE04 ? 0 ? 0 ?
1 ?
evaopn3.()+1445 call 00000000 2ADCB04C ? 0 ? 0 ? 0 ?
2D0000 ? 403CAD ?
evaopn2()+382 call evaopn3.() 2ADCAFBC ? F7EEE4 ? 101D6CC ?
__PGOSF316_opifcr() call 00000000 2ADCA99C ? 0 ? 0 ? 0 ? 0 ?
+5010 0 ?
qerstRowP()+425 call 00000000 BFE14A70 ? 2 ?
qerfiFetch()+185 call 00000000 2 ? 8E86781 ?
qerstFetch()+176 call 00000000 2ADCAF64 ? F7EEEC ? B19518E ?
BFE14764 ? 2 ? C ?
opifch2()+6022 call 00000000 2ADCAF0C ? F7EF0C ? E4D5180 ?
BFE14A70 ? 2 ?
opiefn0()+523 call opifch2() 89 ? 7 ? BFE14E1C ?
opipls()+3313 call opiefn0() 4E ? 7 ? BFE151B8 ? 0 ? 0 ?
0 ? 0 ? 0 ? 0 ? 0 ?
opiodr()+991 call 00000000 66 ? 7 ? BFE161E8 ?
__PGOSF106_rpidrus( call opiodr() 66 ? 7 ? BFE161E8 ? A ?
)+163
skgmstack()+117 call 00000000 BFE15AE0 ? 0 ? BFE15FFC ?
BFE16204 ? BFE15B04 ?
BFE15AFC ?
rpidru()+95 call skgmstack() BFE15AC4 ? F563120 ? F618 ?
E480FE8 ? BFE15AE0 ?
rpiswu2.()+404 call 00000000 BFE15FFC ? 0 ? E8C1604 ?
6FEC50 ? E8C1604 ? 0 ?
rpidrv.()+1154 call rpiswu2.() 324538D4 ? 59 ? 32453918 ?
16 ? 6EF7B8 ? 59 ?
psddr0()+450 call rpidrv() A ? 66 ? BFE161E8 ? 39 ?
psdnal()+333 call psddr0() A ? 66 ? BFE161E8 ? 31 ?
pevm_EXIM()+183 call 00000000 BFE16E1C ? BFE16F3C ?
F571340 ? 9C5734 ? 2D1D2374 ?
pfrinstr_EXIM()+39 call pevm_EXIM() 713648 ? 6DCED0 ?
pfrrun_no_tool()+40 call 00000000 713648 ? 2B7BB9B8 ? 713688 ?
pfrrun()+783 call pfrrun_no_tool() 2 ? 7136F4 ? BFE16778 ?
plsql_run()+742 call pfrrun() 713648 ? 1 ? EF48A20 ?
EF48A18 ? EF48A10 ? EF489F4 ?
peicnt.()+269 call plsql_run() 713648 ? 1 ? 0 ?
kkxexe()+440 call peicnt() BFE16E1C ? 713648 ? F563360 ?
32918CBC ?
opiexe()+12530 call kkxexe() FA6D68 ? C2870CC ? 1C8CE8 ?
A56BDD7 ? C28708E ?
BFE16FE8 ?
opiall0.()+1450 call opiexe() 49 ? 3 ? BFE17D5C ?
opial7()+444 call opiall0() 3E ? 22 ? BFE18400 ?
BFE1ABF4 ? BFE181B8 ? 0 ?
opiodr()+991 call 00000000 47 ? F ? BFE1ABF4 ?
ttcpip()+1066 call 00000000 47 ? F ? BFE1ABF4 ? 8 ?
opitsk()+1275 call ttcpip() F571340 ? 47 ? BFE1ABF4 ? 0 ?
BFE1A884 ? BFE1AD1C ?
opiino()+882 call opitsk() 0 ? 0 ?
opiodr()+991 call 00000000 3C ? 4 ? BFE1BC38 ?
opidrv()+462 call opiodr() 3C ? 4 ? BFE1BC38 ? 0 ?
sou2o()+91 call opidrv() 3C ? 4 ? BFE1BC38 ?
opimai_real()+109 call sou2o() BFE1BC1C ? 3C ? 4 ?
BFE1BC38 ?
ssthrdmain()+142 call opimai_real() 2 ? BFE1BD30 ?
main()+116 call ssthrdmain() 2 ? BFE1BD30 ?
__libc_start_main() call 00000000 2 ? BFE1BDF4 ? BFE1BE00 ?
+220 BC7A20 ? 0 ? B5D4F0 ?
_start()+33 call __libc_start_main() 85399C4 ? 2 ? BFE1BDF4 ?
A56F5B0 ? A56F5A0 ? BC82D0 ?

So within the code wich ran, the error was raised:

40206, 00000, "invalid setting value for setting name %s"
// *Cause: The input value for the given setting name was invalid.
// *Action: Consult the documentation for the settings table and provide
// a valid setting value.

The involved setting name was “SVMS_CONV_TOLERANCE”. The problem is caused by the NLS setting of your seperator, so:

alter session set NLS_NUMERIC_CHARACTERS=”.,”;

does the trick, but be careful with TOAD, it can be possible that the alter session still gives issues. So a better thing is to ensure it goes well, is to change the seperator settings in TOAD itself and to put as NLS_LANG in the registry “AMERICAN_AMERICA.AL32UTF8″.

Regards, Gerwin

Time and Labor application System

In the GAPP analysis which consists of four important steps, Data Collection, Data Synchronization, Data Mining and Data Interpretation, it became clear that the investigated business process from the Time and Labor application was not able to be modelled in GAPP when only the direct involved LPAR data (factors) was used in the analysis…

GAPP analyses model only with direct involved factors

Only after adding the data from the other LPAR’s made it clear that the competition in resources where from high importance, the model in GAPP made sense now…

GAPP analyses model also with indirect involved factors

Last week I received unexpectedly an email from Cary Millsap with a very special opportunity within. Cary asked me if I was still interested in presenting at the comming symposium and although very little time I said of course YES. So although late I was added to the HOTSOS 2009 symposium agenda to give a presentation called “Practical Use of Method Gapp to Find Performance Bottlenecks for Business Processes in Complex Architectures”

I hope personal that I will give a presentation wich will be equally or even more interresting than the one I gave at the HOTSOS Symposium 2008. As a last word I really like to thank Cary Millsap for this opportunity.

Regards, Gerwin

I really like to congratulate Dr. Neil Gunther with this very big achievement. For further reading you can check out the blog post from Dr. Neil Gunther on his website. Don’t forget to check out the speech presentation with at the end a nice picture of Dr. Neil Gunther and his father.