Weblogic Vs. Websphere

Neither WebSphere nor WebLogic is a single product. Both servers represent part of a family of related products, and a user who purchases WebSphere or WebLogic will most likely also use the other products within the family. WebSphere and WebLogic are both enterprise servers, and several similarities and differences exists between these products.

Similarities

IBM’s WebSphere and Oracle’s WebLogic are both scalable servers that use Java. Both companies also classify these servers as high-performance, although WebLogic has long been considered the industry standard According to a white paper by Crimson Consulting Group, a marketing consultancy firm, WebLogic generally offers better support and documentation. However, the group also states that the IBM name brand makes WebSphere the preferable server for many users and developers.

Product Structure

On the most basic level, WebLogic and WebSphere allow users to create applications and integrate those applications with other programs. The greater family of related products is often referred to as “application and integration middleware.” Both IBM and Oracle include a long list of products within these families. IBM takes this a step further and also categorizes these related products according to specific capabilities and what industry the products serve.

Basic Differences

According to Crimson Consulting Group’s white paper comparing WebSphere and WebLogic, a common user complaint about WebSphere is that the server contains several bugs and operates at slow speeds. WebLogic, on the other hand, functions at faster speeds and does not have as many user-reported problems. Additionally, the white paper states that WebLogic is more expensive than WebSphere, and organizations running IBM hardware typically find WebSphere offers better compatibility with existing systems.

Configuration Differences

WebSphere has several compatibility issues making this server more difficult to install and configure, states the Crimson Consulting Group’s white paper. To some extent WebLogic eliminates these compatibility issues by using a separate domain for each deployed application. This results in the ability to make configuration changes for one application without affecting the other applications. Additionally, WebSphere’s configuration setup also creates a deployment queue that takes more time to resolve and deploy than WebLogic.
Advertisements

Siebel SQL cursor crashes

This typically occurs when DB goes through a series of transactions very quickly, typically involving opening and closing of connections/cursors.We had two situations when this happened. When we raised log levels in production environment with a large number of users, Siebel invariably crashed.
Siebel also crashed sometimes when users tried to load a lot of data using VB com object in a high load production environment.

To find out about this, we should first look at the call stack file. Let’s start with the one involving high log levels in a high load environment.
The callstack file would be like this

/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlCursor::Close()+0x27a)[0x4448336a]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbConn::CacheSqlCursor(CSSSqlCursor*)+0x383)[0x44477cb3]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbConn::CachePendingCursors()+0x150)[0x44478ec0]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSLockDbConn::Unlock(CSSModelPhysDef*)+0x8d)[0x44472d3d]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbLock::Unlock()+0x43)[0x44472313]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSDbLock::~CSSDbLock()+0x1f)[0x4447222f]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSLockSqlCursor::DoExecuteStmt(CSSBindVarArray*)+0x111)[0x444822d1]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlCursor::Execute(CSSBindVarArray*)+0xbd)[0x4448356d]
/app/siebel/siebsrvr/lib/libsscfdm.so(CSSSqlObj::Execute(int, int, int)+0x1832)[0x44508732]
/app/siebel/siebsrvr/lib/libsscfom.so(CSSBusComp::SqlExecute(int, int, int)+0x4e)[0x440e842e]
This shows that 1) The sql cursor was closed
2) Db connection was unlocked
3) DB Connection was locked for the cursor transaction
4) SQL was executed
5) Then the logs stopped when this was done and Siebel crashed.
The last 5 points were analyzed from these words

CSSSqlCursor::Close()
CSSLockDbConn::Unlock
CSSLockSqlCursor::DoExecuteStmt
CSSBusComp::SqlExecute
For the com object manager, we did not generate a callstack file. Instead we had to generate an FDR from the corestack.

We can find out the process ID from the core file name.
e.g. if the core file name is core_a12345_siebmtshmw_69318_80758_1336649
Then the crashing process ID will be the last set of numbers, in this case – 1336649
If you run pflags against the core dump, you can get the thread id.
If you type pflags core_a12345_siebmtshmw_69318_80758_1336649

You will get an output with several threads. E.g.

/1045: flags = DETACH|STOPPED pollsys(0x4,0x1,0xd7a7d860,0x0)
why = PR_SUSPENDED

/1046: flags = DETACH
sigmask = 0xfffffeff,0x0000ffff cursig = SIGSEGV
Here the thread ID will be 1046. It is the number before the SIGSEGV or SIGBUS or SIGUSR instance.
After this run this SQL – select srvr_comp_name, srvr_logfile_name:
srvr_name,srvr_user_name from s_srm_task_hist
where srvr_thread_id_val= ‘1046’ and srvr_proc_id_val = ‘1336649’;
srvr_user_name will give you the userid that caused the crash, srvr_logfile_name is the object manager logfile name.
This table exists from Siebel 8x. If you are working with Siebel 7x, to get the object manager log file,
please go to the enterprise log folder where the crash happened and type (for bash)
Grep “1336649” *.log | grep “1046”
Getting the userid is more complex in 7x. You need to convert the FDR file to CSV first.

select all data (Control + A) and then select Filter in the data tab in excel.
After this select all data from the first column – FdrID
Then select Data-> AZ and order id like that.

After that in the 3rd column (ThreadID), filter data so that only this thread shows.Now we need to find out the name of our Application in Tools. It is normally something like Siebel eFinancials or Siebel Power Communication depending on the module.
If your application is named Siebel eFinancials, search for Siebel+eFinancials in this excel sheet.

You should see something like 100J+Siebel+eFinancials.100J will be the row id of the user that caused this crash. Run this SQL
Select login from s_user where row_id = ‘100J’;
This will give you the user login.
After that ask the user what he was doing, the easiest way.
If the user cannot explain, it is more painful to analyze.This is what Oracle says about this

The crash has been reported on 8.1.1.3, and has been reproduced internally in 8.1.1.0 and 8.1.1.4 fix pack, using standard SRF. Hence, this crash may happen with any 8.1.1.x fix pack.

There are two possible workarounds as follows:
1. Disable Database Connection Pooling
The three parameters that control Siebel Database Connection Pooling and Multiplexing are described below:
o Min Number of Dedicated database (DB) Connections (alias is MinTrxDbConns)
o Min Number of Shared DB Connections (alias is MinSharedDbConns)
o Max Number of Shared DB Connections (alias is MaxSharedDbConns)

Setting the above parameters to -1 will disable DB Connection Pooling and Multiplexing. When DB Connection Pooling and Multiplexing are disabled, each user creates a database connection at session creation and releases it at session termination.

Note: Make sure to have enough resources/licenses on the database server to accommodate the number of concurrent database sessions as the database my encounter severe performance impact due to the number of simultaneous sessions with disabled database connection pooling.
2. Reduce the ObjMgrSqlCursorLog event log level to default setting (1)

Keyword – avoid crash keep ObjMgrSqlCursorLog=1

Engineering has found on their investigation that a higher ObjMgrSqlCursorLog event logging level makes the crash happen more frequently. In internal tests, it was not possible to reproduce the crash using the above scenario with ObjMgrSqlCursorLog event log level set to 1.
Long Term Solution:
Below are the fixes currently available at the time of this document edition:

8.1.1.1 QF01CP Patch: 12804472
8.1.1.3 QF03AY Patch: 12764211
8.2.2 (no longer reproducible)

 

Copy Production data to Development environment Part -2-

1) Data can be exported while the Production database server is on if you are using Oracle 11x.
2) To import data, we need to first shut down Siebel server
/install_folder/siebel/8.1/siebsrvr
. ./siebenv.sh
cd bin
./stop_server -r /install_folder/siebel/8.1/siebsrvr -e ENT_NAME SERVER_NAME
After that ask DBA to import this data.
3) DBA should change the passwords for GUESTCST, SADMIN and SIEBEL. GUESTCST is the default anpnymous user, it could be different in your case.
4) Run these commands against the database after that (we used SQL Developer)
Truncate table S_WFA_INST_WAIT;
Truncate table S_WFA_INSTANCE;
Truncate table S_WFA_INSTP_LOG;
Truncate table S_WFA_INST_LOG;
Truncate table S_WFA_INST_PROP;
Truncate table S_ESCL_ACTN_REQ;
Truncate table S_ESCL_LOG;
Truncate table S_ESCL_STATE;
Truncate table S_ESCL_REQ;
Truncate table S_DOCK_TXN_LOG;
Truncate table S_COMM_REQ;
commit;

5) We now need to change SADMIN and SIEBEL passwords in Siebel application
precondition (Siebel server should be in stopped state, gateway should be running)

Changing SADMIN password
log into servermanager

./srvrmgr -g IP_ADDRESS:2320 -u SADMIN -p PASSWORD -e ENT_NAME
change enterprise parameter Password=PASSWORD (new sadmin password)
change enterprise parameter TableOwnPass=PASSWORD (new Siebel password)
IP_ADDRESS is the gateway server i.p.

list enterprise parameter TableOwnPass show PA_SETLEVEL
this value should not be ”Never set”. It was 0 after change

remove, move or rename this file
/install_folder/siebel/8.1/siebsrvr/sys/svc.siebsrvr.ENT_NAME:SERVER_NAME
type (make sure you have siebenv.sh loaded first)
siebctl -r /install_folder/siebel/8.1/siebsrvr -S siebsrvr -i ENT_NAME:SERVER_NAME -a -g “-g IP_ADDRESS:2320 -e ENT_NAME -s SERVER_NAME -u sadmin” -e PASSWORD -L ENU

IP_ADDRESS is the gateway server i.p. address
Now we need to restart gateway.
Go to
/package_folder/siebel/8.1/gtwysrvr

siebel@servername:/install_folder/siebel/8.1/gtwysrvr> . ./siebenv.sh
siebel@servername:/install_folder/siebel/8.1/gtwysrvr> cd bin
siebel@servername:/install_folder/siebel/8.1/gtwysrvr/bin> stop_ns
siebel@servername:/install_folder/siebel/8.1/gtwysrvr/bin> start_ns

to check if gateway is running type
siebel@servername:/install_folder/siebel/8.1/gtwysrvr/bin> ps -ef | grep siebsvc
siebel 19836 29152 0 11:03:53 pts/1 0:00 grep siebsvc
siebel 18536 25550 0 11:01:05 pts/1 0:04 siebsvc -s gtwyns -a /f /install_folder/siebel/8.1/gtwysrvr/sys/siebns.dat /t 232
6) Changing GUESTCST password

first we need to find out where these passwords are stored. the configuration files (eapps.cfg can be renamed or there may be multiple files with this name)
The only file that is constant is magnus.conf files. There could be more than one magnus.conf file that is being used (e.g. one for the ssl and one for non ssl).
Open these files and search for string config-file
Currently the correct configuration files are
for non ssl
config-file=”/apps/servername/javaweb/sweapp/bin/eapps.cfg”
for ssl
config-file=”/apps/servername/javaweb/sweapp/bin/eapps_ssl.cfg”

Get the new password for GUESTCST from DBA. Log into siebel server
cd /install_folder/siebel/8.1/siebsrvr
. ./siebenv.sh
cd bin
apps/package_folder/siebel/8.1/siebsrvr/bin>encryptstring PASSWORD
I3OFbQV5a3gBAAAFCg==apps/package_folder/siebel/8.1/siebsrvr/bin>

Copy the first part i.e. I3OFbQV5a3gBAAAFCg==

Open eapps.cfg and eapps_ssl.cfg files and in the password field paste this new password.
e.g.
[defaults]
EncryptedPassword = True
AnonUserName = GUESTCST
AnonPassword = I3OFbQV5a3gBAAAFCg==
After this restart Web Servers. Go to the web server root for the servers, in this case we have two root folders. One
is for ssl , another for non ssl.

siebel@webservername:/apps/servername/javaweb/https-ta-sfa> ./stop
siebel@webservername:/apps/servername/javaweb/https-ta-sfa-ssl> ./stop

siebel@webservername:/apps/servername/javaweb/https-ta-sfa> ./start
siebel@webservername:/apps/servername/javaweb/https-ta-sfa-ssl> ./start

7) change SRF/Browser Scripts if the deployment version is different

8) Start siebel server and log in as SADMIN

cd /install_folder/siebel/8.1/siebsrvr
. ./siebenv.sh
cd bin
./start_server -r /install_folder/siebel/8.1/siebsrvr -e ENT_NAME -L ENU -g IP_ADDRESS:2320 SERVER_NAME
IP_ADDRESS is the gateway server i.p. address

8) Update triggers in Siebel

First remove triggers, then generate them
To generate DB Triggers perform next steps:
1. Log On to Siebel Application.
2. Through Site Map navigate to view Administration ? Server Management > Jobs
3. In list applet Jobs create new record
4. In field Component Job select value Generate Triggers
5. In bottom applet Job Parameters create new records and set parameters by below name – value key:
a. EXEC ? True
b. Privileged User ? siebel
c. Privileged User Password ? <siebel password>
d. Remove ? TRUE
6. Submit created Job by clicking button Submit on applet Jobs

for the second time, run the same job except for point d. Instead of TRUE, put false and run the job again.
After that from SQL Developer/SQL Plus

update siebel.s_srm_req_param set value = ”removepass” where value = ”siebel_password”;
commit;

This update is needed because of a Siebel security issue. When you generate triggers, Siebel stores the password of user Siebel
in the database without encryption. Any siebel user can view this running an SQL.

9) Go to Administration -> Web Services, select Outbound Web Services

Delete all the URLs in the second applet, change them to http://localhost
If you have active Web Services in Development environment, you need to set the correct URLs for all active web services.

10) After that go to some screens and check if everything is ok.
11) We need to do some other cleaup activities – the ucf file used to generate ddlsync uses both siebel and sadmin passwords.
Deployments will not work if the ddlsync fails. This is how we can generate a new ddlsync file

Log in as siebel, go to siebsrvr directory /install_folder/siebel/8.1/siebsrvr

Load siebenv.sh

siebel@servername:/install_folder/siebel/8.1/siebsrvr/bin> ssincfgw -is:javaconsole -console -args LANG=ENU MODEL_FILE=/dbsrvr.scm
InstallShield Wizard

Initializing InstallShield Wizard…

Preparing Java(tm) Virtual Machine…
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
……………………………..
………………….
Running InstallShield Wizard…

——————————————————————————-
[X] 1 – Configure Product in Live Mode
[ ] 2 – Configure Product for Offline Deployment
[ ] 3 – Exit Configuration Wizard

To select an item enter its number, or 0 when you are finished: [0] 1
[X] 1 – Configure Product in Live Mode
[ ] 2 – Configure Product for Offline Deployment
[ ] 3 – Exit Configuration Wizard

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Directory where the Siebel Server is installed

Siebel Server Directory [/install_folder/siebel/8.1/siebsrvr]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Directory where the Siebel Database Server is installed

Siebel Database Server Directory [/install_folder/siebel/8.1/dbsrvr]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Choose the appropriate database platform

[ ] 1 – IBM DB2 UDB for Linux UNIX Windows
[X] 2 – Oracle Database Enterprise Edition

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
[X] 1 – Install Database
[ ] 2 – Upgrade Database
[ ] 3 – Apply Additive Schema Changes
[ ] 4 – Import/Export Repository
[ ] 5 – Migrate Repository
[ ] 6 – Run Database Utilities

To select an item enter its number, or 0 when you are finished: [0] 6
[ ] 1 – Install Database
[ ] 2 – Upgrade Database
[ ] 3 – Apply Additive Schema Changes
[ ] 4 – Import/Export Repository
[ ] 5 – Migrate Repository
[X] 6 – Run Database Utilities

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Select one of the following Database utilities options

[X] 1 – Synchronize Schema Definition
[ ] 2 – Universal Time Code Conversion
[ ] 3 – Multilingual List of Value Conversion

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Choose whether the Siebel Database you are synchronizing with the physical
schema is UNICODE or not.

[ ] 1 – UNICODE Database
[X] 2 – Non-UNICODE Database

To select an item enter its number, or 0 when you are finished: [0] 1
[X] 1 – UNICODE Database
[ ] 2 – Non-UNICODE Database

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Select the base language (also called primary language) from the list

[ ] 1 – English (American)
[X] 2 – Turkish

To select an item enter its number, or 0 when you are finished: [0] 1
[X] 1 – English (American)
[ ] 2 – Turkish

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Enter a valid ODBC (Open Database Connectivity) Data Source Name to access the
Siebel Database connection. Default value is Siebel_DSN.

ODBC Data Source Name [] DSN_NAME
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Siebel Database User Name
Siebel Database Password

Database User Name []
Siebel Database Password

Database User Name [] SADMIN
Database Password: heslo
Database Password–(confirm): heslo
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Siebel Database Table Owner
Siebel Database Table Owner password

Database Table Owner [] siebel
Database Table Owner Password: heslo
Database Table Owner Password–(confirm): heslo
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Index Table Space Name
Table Space Name

Index Table Space Name [] SIB_INDEX
Table Space Name [] SIB_DATA
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Name of the repository you want to synchronize with the physical schema

Repository Name [Siebel Repository]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Please indicate if the target server will use the Oracle Parallel Indexing
option:

[X] 1 – Does not use the Oracle Parallel Indexing option
[ ] 2 – Uses the Oracle Parallel Indexing option

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Identifies an authorization ID designated for a group of Siebel users defined
in a mainframe security package
Security Group ID / Grantee [SSE_ROLE]
Log Output Directory [ddlsync]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

——————————————————————————-
Configuration is complete: your output will be saved under
$SiebelRoot/siebsrvr/bin/master_<process>.ucf. To deploy the process you
configured to the database please run the below command line:
$SiebelRoot/siebsrvr/bin/srvrupgwiz /m master_<process>.ucf
To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]
title The Siebel Configuration Wizard will execute using the following settings:)

——————————————————————————-
The Siebel Configuration Wizard will execute using the following settings:)

Siebel Server Directory : /install_folder/siebel/8.1/siebsrvr
Siebel Database Server Directory : /install_folder/siebel/8.1/dbsrvr
RDBMS Platform : Oracle Database Enterprise Edition
Siebel Database Operation : Run Database Utilities
Database Utility Selection : Synchronize Schema Definition
Database Encoding : UNICODE Database
Base Language : English (American)
Language Selection : ENU
ODBC Data Source Name : DSN_NAME
Database User Name : SADMIN
Database Password : ********
Database Table Owner : siebel
Database Table Owner Password : ********
Index Table Space Name : SIB_INDEX
Table Space Name : SIB_DATA
Repository Name : Siebel Repository
Oracle Parallel Index : Does not use the Oracle Parallel Indexing option
Security Group ID / Grantee : SSE_ROLE
Log Output Directory : ddlsync
Configuration is complete: your output will be saved under

Press ENTER to read the text [Type q to quit]

$SiebelRoot/siebsrvr/bin/master_<process>.ucf. To deploy the process you
configured to the database please run the below command line:
$SiebelRoot/siebsrvr/bin/srvrupgwiz /m master_<process>.ucf :

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1]

1. Yes
2. No

Do you want to execute configuration? [2] 1

Execution Successful.

——————————————————————————-
[X] 1 – Configure Product in Live Mode
[ ] 2 – Configure Product for Offline Deployment
[ ] 3 – Exit Configuration Wizard

To select an item enter its number, or 0 when you are finished: [0] 3
[ ] 1 – Configure Product in Live Mode
[ ] 2 – Configure Product for Offline Deployment
[X] 3 – Exit Configuration Wizard

To select an item enter its number, or 0 when you are finished: [0]
Press 1 for Next, 3 to Cancel or 4 to Redisplay [1]

Understanding Indexes Concept -II-

Indexes plays and crucial role in the performance tunning of a database . It is very important to know how the index  work i.e, how indexes fetches the data’s from a tables . There is a very good post by  rleishman on the working of indexes . Let’s have a look .
What is an Index ? 

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. It is just as the index in this manual helps us to locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data .

 

Blocks 

First we need to understand a block. A block – or page for Microsoft boffins – is the smallest unit of disk that Oracle will read or write. All data in Oracle – tables, indexes, clusters – is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So we never read “just one row”; we will always read the entire block and ignore the rows we don’t need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.
Oracle uses two different index architectures: b-Tree indexes and bitmap indexes. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the “normal” index .

The “-Tree” in b-Tree 

A b-Tree index is a data structure in the form of a tree – no surprises there – but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book .  Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells us the physical location of the telephone (row in the table).

The names on each page are sorted, and the pages – when sorted correctly – contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered “the flick” – the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

 

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

 

For example : 

To find the name Gallileo in this b-Tree phone book, we:

=> Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.

=> Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.

=> Read page 350, which is a leaf block; we find Gallileo’s address and phone number.

=> That’s it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:
SQL> select index_name,  blevel+1  from  user_indexes  order  by  2 ;

user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells us the number of blocks a unique index scan must read to reach the leaf-block. If we’re really, really, insatiably curious; try this in SQL*Plus:
SQL> accept   index_name  prompt   “Index Name: ”
SQL> alter session set tracefile_identifier=’&index_name’ ;
SQL> column object_id new_value object_id
SQL> select  object_id  from user_objects where object_type = ‘INDEX’  and  object_name=upper(‘&index_name’);
SQL> alter session set events ‘Immediate trace name treedump level &object_id’;
SQL> alter session set tracefile identifier=”” ;
SQL> show parameter user_dump_dest
Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find the trace file – the file name will contain the index name. Here is a sample:
—- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)


leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
—– end tree dump
This index has only a root branch with 323 leaf nodes. Each leaf node contains a variable number of index entries up to 807! A deeper index would be more interesting, but it would take a while to dump.

“B”  is  for…

Contrary to popular belief, b is not for binary; it’s balanced.

As we insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length.
How are Indexes used ?
Indexes have three main uses:

  • To quickly find specific rows by avoiding a Full Table Scan

 

We’ve already seen above how a Unique Scan works. Using the phone book metaphor, it’s not hard to understand how a Range Scan works in much the same way to find all people named “Gallileo”, or all of the names alphabetically between “Smith” and “Smythe”. Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.

  • To avoid a table access altogether

 

If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we’d have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.

Oracle does the same thing. If the information is in the index, then it doesn’t bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.

  • To avoid a sort

 

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

 

Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.
1. GROUP BY : 


SQL> select src_sys, sum(actl_expns_amt), count(*)  from ef_actl_expns
where src_sys = ‘CDW’   and actl_expns_amt > 0
group by src_sys ;
—————————————————————————————–
| Id   |      Operation                                               |     Name             |
—————————————————————————————-
|   0  | SELECT STATEMENT                                     |                           |
|   1  |  SORT GROUP BY NOSORT  <——-           |                           |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID | EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                                 | EF_AEXP_PK       |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————————-
2 – filter(“ACTL_EXPNS_AMT”>0)
3 – access(“SRC_SYS”=’CDW’)
Note the NOSORT qualifier in Step 1.

2. ORDER BY : 


SQL> select *  from ef_actl_expns
where src_sys = ‘CDW’ and actl_expns_amt > 0
order by src_sys
—————————————————————————————-
| Id   | Operation                                                     |     Name            |
—————————————————————————————-
|   0  | SELECT STATEMENT                                     |                           |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID   | EF_ACTL_EXPNS|
|*  2 |   INDEX RANGE SCAN                                   | EF_AEXP_PK      |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
1 – filter(“ACTL_EXPNS_AMT”>0)
2 – access(“SRC_SYS”=’CDW’)

Note that there is no SORT operation, despite the ORDER BY clause. Compare this to the following:
SQL>  select * from ef_actl_expns
where src_sys = ‘CDW’  and actl_expns_amt > 0
order by actl_expns_amt ;
———————————————————————————————
| Id  | Operation                                                      |         Name          |
———————————————————————————————
|   0 | SELECT STATEMENT                                       |                            |
|   1 |  SORT ORDER BY                                            |                            |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID   | EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                                   | EF_AEXP_PK       |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – filter(“ACTL_EXPNS_AMT”>0)
3 – access(“SRC_SYS”=’CDW’)

3. DISTINCT : 


SQL> select distinct src_sys  from ef_actl_expns
where src_sys = ‘CDW’  and actl_expns_amt > 0 ;
———————————————————————————————–
| Id  |          Operation                                             |         Name          |
———————————————————————————————–
|   0 | SELECT STATEMENT                                       |                            |
|   1 |  SORT UNIQUE NOSORT                                 |                            |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID   | EF_ACTL_EXPNS |
|*  3 |    INDEX RANGE SCAN                                   | EF_AEXP_PK       |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – filter(“ACTL_EXPNS_AMT”>0)
3 – access(“SRC_SYS”=’CDW’)

Again, note the NOSORT qualifier.

This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.
Full table Scans are not bad : 

Up to now, we’ve seen how indexes can be good. It’s not always the case; sometimes indexes are no help at all, or worse: they make a query slower.

 

A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.

 

Much more interesting – and important – are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.

 

To explain the problem, we need a new metaphor. Imagine a large deciduous tree in our front yard. It’s Autumn, and it’s our job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac…) would be get down on hands and knees with a bag and work our way back and forth over the lawn, stuffing leaves in the bag as we go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: we would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon. Type this in SQL*Plus:

 

SQL> show parameter  db_file_multiblock_read_count

 

Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), we decide to pick up the leaves in order of size. In support of this endeavour, we take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because we cover much more distance walking from leaf to leaf.

 

So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.
The main reasons for this are :

  • As implied above, reading a table in indexed order means more movement for the disk head.
  • Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.
  • The db_file_multiblock_read_count setting described earlier means FTS requires fewer visits to the physical disk.
  • Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.

 

So what’s the lesson here? Know our data! If our query needs 50% of the rows in the table to resolve our query, an index scan just won’t help. Not only should we not bother creating or investigating the existence of an index, we should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule – there’s always one – is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.

 

Summary : 

Indexes are not a dark-art; they work in an entirely predictable and even intuitive way. Understanding how they work moves Performance Tuning from the realm of guesswork to that of science; so embrace the technology and read the manual.

 

Reference:    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm

http://www.orafaq.com/node/1403

 

Interpreting Raw Sql Trace File

QL_TRACE is the main method for collecting SQL Execution information in Oracle. It records a wide range of information and statistics that can be used to tune SQL operations. The sql trace file contains a great deal of information . Each cursor that is opened after tracing has been enabled will be recorded in the trace file. 

The raw trace file mostly contains the cursor number . Eg, PARSING IN CURSOR #3 . EXECutes, FETCHes and WAITs are recorded against a cursor. The information applies to the most recently parsed statement within that cursor . Firstly,  let’s have a look on “Wait Events”  .

WAIT #6: nam=’db file sequential read’ ela= 8458 file#=110 block#=63682 blocks=1 obj#=221 tim=506028963546

WAIT = An event that we waited for.
nam    = What was being waited for .The wait events here are the same as are seen in view V$SESSION_WAIT .
ela  = Elapsed time for the operation.(microseconds)
p1 = P1 for the given wait event.
p2 = P2 for the given wait event.
p3 = P3 for the given wait event.

 

Example No. 1 : WAIT #6: nam=’db file sequential read’ ela= 8458 file#=110 block#=63682 blocks=1 obj#=221 tim=506028963546

The above line can be translated as  : Completed waiting under CURSOR no 6  for “db file sequential read” . We waited 8458 microseconds i.e. approx. 8.5 milliseconds .For a read of:  File 110, start block 63682, for 1 Oracle block of Object number 221. Timestamp was 506028963546 . 

 

Example no.2 : WAIT #1: nam=’library cache: mutex X’ ela= 814 idn=3606132107 value=3302829850624 where=4 obj#=-1 tim=995364327604

The above line can be translated as : Completed WAITing under CURSOR no 1 for “library cache: mutex X” .We waited 814 microseconds i.e. approx. 0.8 milliseconds .To get an eXclusive library cache latch with  Identifier 3606132107 value 3302829850624 location 4 . It was not associated with any particular object (obj#=-1) Timestamp 995364327604.

 

The trace file also show the processing of the sql statements . Oracle processes SQL statements as follow :
Stage 1: Create a Cursor
Stage 2: Parse the Statement
Stage 3: Describe Results
Stage 4: Defining Output
Stage 5: Bind Any Variables
Stage 6: Execute the Statement
Stage 7: Parallelize the Statement
Stage 8: Fetch Rows of a Query Result
Stage 9: Close the Cursor
Now let’s  move to another important term PARSING IN CURSOR #n . EXECutes, FETCHes and WAITs are recorded against a cursor. The information applies to the most recently parsed statement within that cursor.

PARSING IN CURSOR# : 

Cursor :  In order for Oracle to process an SQL statement, it needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement, a pointer to the parsed representa-tion of the statement (parsing an SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid).

 

A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. Two important features about the cursor are

1.)  Cursors allow you to fetch and process rows returned by a SE-LECT statement, one row at a time.

2.)  A cursor is named so that it can be referenced.

 

Parsing : Oracle Parsing is the first step in processing of any database statement . PARSE record is accompanied by the cursor number. Let’s have a look on “Parsing in Cursor” of a particular trace file .

 

 PARSING IN CURSOR #2 len=92 dep=0 uid=0 oct=3 lid=0 tim=277930332201 hv=1039576264 ad=’15d51e60′ sqlid=’dsz47ssyzdb68′

select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = 12

END OF STMT

PARSE#2:c=31250,e=19173,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=836746634,tim=27930332198

EXEC #2:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=836746634,tim=77930335666

WAIT #2: nam=’SQL*Net message to client’ ela= 10 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930335778

FETCH #2:c=0,e=805,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=836746634,tim=77930336684

WAIT #2: nam=’SQL*Net message from client’ ela= 363 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930337227

FETCH #2:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=836746634,tim=77930337421

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=’NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=152 card=1)’

STAT #2 id=2 cnt=27 pid=1 pos=1 obj=0 op=’MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=156 us cost=0 size=96 card=1)’

STAT #2 id=3 cnt=1 pid=2 pos=1 obj=0 op=’NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=39 card=1)’

STAT #2 id=4 cnt=1 pid=3 pos=1 obj=0 op=’FIXED TABLE FIXED INDEX X$KSLWT (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)’

STAT #2 id=5 cnt=1 pid=3 pos=2 obj=0 op=’FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)’

STAT #2 id=6 cnt=27 pid=2 pos=2 obj=0 op=’BUFFER SORT (cr=0 pr=0 pw=0 time=78 us cost=0 size=57 card=1)’

STAT #2 id=7 cnt=27 pid=6 pos=1 obj=0 op=’FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=130 us cost=0 size=57 card=1)’

STAT #2 id=8 cnt=1 pid=1 pos=2 obj=0 op=’FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=56 card=1)’

WAIT #2: nam=’SQL*Net message to client’ ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77930338248

*** 2012-05-19 15:07:22.843

WAIT #2: nam=’SQL*Net message from client’ ela= 38291082 driver id=1413697536 #bytes=1 p3=0 obj#=116 tim=77968629417

CLOSE #2:c=0,e=30,dep=0,type=0,tim=77968629737

len     = the number of characters in the SQL statement
dep   = tells the application/trigger depth at which the SQL statement was executed. dep=0 indicates that it was executed by the client application. dep=1 indicates that the SQL statement was executed by a trigger, the Oracle optimizer, or a space management call. dep=2 indicates that the SQL statement was called from a trigger, dep=3 indicates that the SQL statement was called from a trigger that was called from a trigger.
uid     = Schema id under which SQL was parsed.
oct = Oracle command type.
lid = Privilege user id
tim    = Timestamp.
hv = Hash id.
ad = SQLTEXT address

PARSE #3:  c=15625,  e=177782,  p=2,  cr=3,  cu=0,  mis=1,  r=0,  dep=0,  og=1,  plh=272002086, tim=276565143470

c      = CPU time (microseconds rounded to centiseconds granularity on 9i & above)
e  = Elapsed time (centiseconds prior to 9i, microseconds thereafter)
p  = Number of physical reads.
cr  = Number of buffers retrieved for CR reads.(Consistent reads)
cu    =Number of buffers retrieved in current mode.
mis  = Cursor missed in the cache.
r  = Number of rows processed.
dep = Recursive call depth (0 = user SQL, >0 = recursive).
og = Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose

From the above Parse line it is very clear that the total time taken in parsing the statement is  0.177 sec and the no. of physical reads done are 2 .

Bind Variables : If the SQL statement does reference bind variables, then the following  SQL statement shown in the cursor can locate a section of text associated with each bind variable. For each bind variable there are a number of attributes listed.  The following are the ones we are interested in here:

mxl      =  the maximum length – ie. the maximum number of bytes occupied by the variable. Eg. dty=2 and mxl=22 denotes a NUMBER(22) column.
scl       = the scale (for NUMBER columns)
pre      = the precision (for NUMBER columns)
value  = the value of the bind variable
dty      = the datatype.  Typical values are:
1       VARCHAR2 or NVARCHAR2
2       NUMBER
8       LONG
11     ROWID
12     DATE
23     RAW
24     LONG RAW
96     CHAR
112   CLOB or NCLOB
113   BLOB
114   BFILE

EXEC :  Execute a pre-parsed statement. At this point, Oracle has all necessary information and resources, so the statement is executed. For example

EXEC #2:c=0,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=282618239403

Fetch : Fetch rows from a cursor . For example
FETCH #4:c=0,e=8864,p=1,cr=26,cu=0,mis=0,r=1,dep=0,og=1,plh=3564694750,tim=282618267037

STAT :  Lines report explain plan statistics for the numbered [CURSOR]. These let us know the ‘run time’ explain plan. For example
STAT #1 id=1 cnt=7 pid=0 pos=1 obj=0 op=’SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=2 size=2128 card=1)’

id       = Line of the explain plan which the row count applies to (starts at line 1).  This is effectively the row source row count for all row sources in the execution tree
cnt =  Number of rows for this row source.
pid =  Parent id of this row source.
pos  =  Position in explain plan.
obj     =  Object id of row source (if this is a base object).
op=’…’   The row source access operation

XCTEND  A transaction end marker. For example  XCTEND rlbk=0, rd_only=1, tim=282636050491
rlbk           =1   if a rollback was performed, 0 if no rollback (commit).
rd_only      =1   if transaction was read only, 0 if changes occurred.

CLOSE  cursor is closed .for example CLOSE #4:c=0,e=32,dep=0,type=0,tim=282636050688
c            = CPU time (microseconds rounded to centiseconds granularity on 9i and above)
e           = Elapsed time (centiseconds prior to 9i, microseconds thereafter)
dep       = Recursive depth of the cursor
type     = Type of close operation

Note : Timestamp are used to determine the time between any 2 operations.
Reference : Metalink [ID 39817.1]