Calculate number of concurrent users of an existing instance

The view v$license keeps track of concurrent sessions and users.

SQL> desc v$license

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------

 SESSIONS_MAX                                       NUMBER

 SESSIONS_WARNING                                   NUMBER

 SESSIONS_CURRENT                                   NUMBER

 SESSIONS_HIGHWATER                                 NUMBER

 USERS_MAX                                          NUMBER

 CPU_COUNT_CURRENT                                  NUMBER

 CPU_CORE_COUNT_CURRENT                             NUMBER

 CPU_SOCKET_COUNT_CURRENT                           NUMBER

 CPU_COUNT_HIGHWATER                                NUMBER

 CPU_CORE_COUNT_HIGHWATER                           NUMBER

 CPU_SOCKET_COUNT_HIGHWATER                         NUMBER

select sessions_current from v$license;

The above query will give you the number of concurrent users right now.

You can write a small job which will capture this information every hour for a week.  Once you have this data, you can take an average of this data to get the number of concurrent users. 

FND_STATS doesn't collect statistics on external tables

Recently we ran Gather Schema Statistics for all tables:

1.Log in to Oracle Applications with the System Administrator responsibility.

2.Navigate to the Submit Request window (Request > Run).

3.Submit the Gather Statistics program.

Set the schema name to ALL to gather statistics for all Oracle Applications schemas (having an entry in the FND_PRODUCT_INSTALLATIONS table). In addition to gathering index and table-level statistics, the program gathers column-level histogram statistics for all columns listed in the FND_HISTOGRAM_COLS table.

4. Use GATHER_AUTO option in the program.  This option gathers statistics for objects that are either lacking statistics or whose rows have changed significantly (default 10%) since the last time you gathered statistics.

GSS got stuck when it was collecting statistics on external tables.  GSS is not designed to gather statistics on external tables.  Patch 5876047 needs to be applied to solve this problem.  This patch modifies FND_STATS package to exclude gathering statistics on external tables.  However after applying this patch GSS will again fail with Ora-1 Errors 

Metalink Note:470556.1 gives this workaround:

Take a backup of the FND_STATS_HIST table

Truncate table FND_STATS_HIST

Re-submit Gather Schema Statistics program.

You do NOT need to truncate this table after each run of Gather Schema Statistics. Doing it once will be enough, because each run of GSS will use a new request_id

Bugs introduced in 10.2.0.4 which will be fixed in 10.2.0.5

Metalink Note 555579.1 describes 30 bugs newly introduced in 10.2.0.4. However if I go to metalink advance search and give classification as Any, then I see > 500 patches. Some of these would be fixed in the next point release 10.2.0.5. The list of recommended patches is fewer in number. You should aim to get your instance patched with at least the recommended patches:

7164095 (For RAC instances)

7375644

7496435

7573282 (For RAC instances)

Oracle has since published another article which has list of all the bugs fixed in 10.2.0.5:

10.2.0.5 Patch Set - List of Bug Fixes by Problem Type [ID 1088172.1]

NUMA and 10.2.0.4

NUMA stands for Non uniform memory access.  It is a computer memory design used in multiprocessors, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors.

Here's a real world analogy from www.techgalaxy.net:

Imagine that you are baking a cake. You have a group of ingredients (=memory pages) that you need to complete the recipe(=process). Some of the ingredients you may have in your cabinet(=local memory), but some of the ingredients you might not have, and have to ask a neighbor for(=remote memory). The general idea is to try and have as many of the ingredients in your own cabinet as possible, since this reduces your time and effort in making the cake.

You also have to remember that your cabinets can only hold a fixed amount of ingredients(=physical nodal memory). If you try and buy more, but you have no room to store it, you may have to ask your neighbor to keep it in his/her cabinet until you need it(=local memory full, so allocate pages remotely).

Bug # 7171446 (Metalink Note 7171446.8) describes the problem of 10.2.0.4 with NUMA:

In 10.2.0.4 NUMA features are enabled by default and this can lead to a number of unexpected problems such as  skewed CPU usage and ORA-600 errors.

Workaround:

Unless the system has been specifically set up and tuned for NUMA then disable Oracle NUMA optimizations  by setting the following in the pfile / spfile / init.ora used to start the instances:

   

   _enable_NUMA_optimization=FALSE 

   _db_block_numa=1

Bug # 68680680 (Metalink Note 68680680.8) says:

ORA-4031 can occur when the NUMA option is used due to imbalance between shared pool subpools.  Subpool 1 shows memory allocated to "NUMA pool X" where X is a number.

This is fixed in 10.2.0.5.  Strangely, patch 68680680 is available for 10.2.0.3 but not for 10.2.0.4.

Fatal error: Don't know how to make target

Recently the DBAs faced this issue when they were applying TXK Rollup S patch and it was trying to relink:

Undefined first referenced

symbol in file

wfmlrsumpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

wfmlrgpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

ld: fatal: Symbol referencing errors. No output written to 

$ALR_TOP/bin/ALPPIM

*** Error code 1

make: Fatal error: Command failed for target 

`$ALR_TOP/bin/ALPPIM'

Done with link of alr executable 'ALPPIM' on Mon Nov 17 14:05:36 EST 2008

Relink of module "ALPPIM" failed.

make: Fatal error: Don't know how to make target 

`$ALR_TOP/bin/ALPPNR'

Done with link of alr executable 'ALPPNR' on Tue Nov 18 22:50:26 EST 2008

Relink of module "ALPPNR" failed.

make: Fatal error: Don't know how to make target 

`$ALR_TOP/bin/ALPPWF'

Done with link of alr executable 'ALPPWF' on Tue Nov 18 22:50:26 EST 2008

Relink of module "ALPPWF" failed

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAGDA'

Done with link of fa executable 'FAGDA' on Tue Nov 18 22:50:31 EST 2008

Relink of module "FAGDA" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPRET'

Done with link of fa executable 'FAMPRET' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPRET" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPSLTFR'

Done with link of fa executable 'FAMPSLTFR' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPSLTFR" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMPTFR'

Done with link of fa executable 'FAMPTFR' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMPTFR" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMRCL'

Done with link of fa executable 'FAMRCL' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMRCL" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FAMRST'

Done with link of fa executable 'FAMRST' on Tue Nov 18 22:50:32 EST 2008

Relink of module "FAMRST" failed.

make: Fatal error: Don't know how to make target 

`$FA_TOP/bin/FATAXUP'

Done with link of fa executable 'FATAXUP' on Tue Nov 18 22:50:33 EST 2008

Relink of module "FATAXUP" failed.

$ORACLE_HOME/rdbms/lib/ssbbded.o 

Undefined first referenced

symbol in file

inltev 

$WIP_TOP/lib/libwip.a(wilctv.o)

inltvu 

$WIP_TOP/lib/libwip.a(wilctv.o)

ld: fatal: Symbol referencing errors. No output written to 

$WSM_TOP/bin/WSCMTI

*** Error code 1

make: Fatal error: Command failed for target 

`$WSM_TOP/bin/WSCMTI'

make: Fatal error: Don't know how to make target 

`$WSM_TOP/bin/WSCMTM'

Done with link of wsm executable 'WSCMTM' on Tue Nov 18 22:51:05 EST 2008

Relink of module "WSCMTM" failed.

Following undefined symbol errors were appearing:

make: Fatal error: Don't know how to make target 

Undefined first referenced

symbol in file

inltev 

$WIP_TOP/lib/libwip.a(wilctv.o)

inltvu 

Undefined first referenced

symbol in file

wfmlrsumpl 

$FND_TOP/lib/libfnd.a(wfmlr.o)

wfmlrgpl

When we relinked them with force option, they relinked fine:

adrelink.sh "force=y" "WSM WSCMTI"

adrelink.sh "force=y" "ALR ALPPIM"

The other binaries still failed.  On a hunch we checked the make file used to relink the binaries:

grep FAGDA $FA_TOP/lib/fa.mk

grep WSCMTM $WSM_TOP/lib/wsm.mk

The make files did not have any record of the binaries which were failing relink.  The relink command was unable to relink these binaries because they were not present in make file.

We took a backup of the existing make files and copied higher version of the make files from a different instance:

$ adident Header $FA_TOP/lib/fa.mk.old

fa.mk.old:

$Header fa.mk 115.3 99/07/16 01:53:07 porting ship $

$ adident Header $FA_TOP/lib/fa.mk

fa.mk:

$Header fa.mk 115.9 2002/05/20 03:43:30 pkm ship    $

The new make files had the binaries which were failing relink.

When we relinked again after copying the make files, it went fine.

alter java class "oracle/apps/per/proxy/client/forms/UrlThread" compile hangs

Mohan pinged me today and said:

Two workers are hung and log file says 

Loading contents of archive file into database with arguments

-Xmx512M -XX:+UseParallelGC -XX:ParallelGCThreads=4 -XX:LargePageSizeInBytes=4M oracle.aurora.server.tools.loadjava.LoadJavaMain -f -thin -user "APPS/*****@someserver.justanexample.com:1521:dba" /$APPLCSF/$APPLOUT/p001ldjva.jar 

I asked him to check v$session_wait, and he reported back:

COUNT(*) EVENT

--------- -----------------------------------

1 Streams AQ: waiting for messages in

the queue

2 row cache lock

4 Streams AQ: qmn slave idle wait

3 library cache lock

1 Streams AQ: qmn coordinator idle wa

it

1 Streams AQ: waiting for time manage

ment or cleanup tasks

SQL_TEXT

----------------------------------------------------------------

alter java class "oracle/apps/per/proxy/client/forms/UrlThread" compile 

I asked him to check the DB alert log.  This is what he found:

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! 

There are a lot of metalink notes if you search for the above phrase.  All of them indicate latch contention of some sort.

I asked Mohan to stop the patch.  Bounce the database and restart the patch.

After bouncing the database and restarting the patch, the issue went away.