Siebel SQLs/Error Messages >  Overall Siebel Performance check SQLs

Overall Siebel Performance check SQLs

Here are some Siebel SQLS to check overall Siebel Performance

You might need DBA privileges or might need to log in as Siebel in database for some SQLS.

1.Long running SQLs:

SET DEFINE OFF;
alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

select s.LAST_CALL_ET,s.SQL_HASH_VALUE, s.SQL_ID, s.sid, s.serial#, p.spid, s.username, s.schemaname, s.blocking_session,s.seconds_in_wait,
s.program, s.terminal, s.osuser
from v$session s
join v$process p
on s.paddr = p.addr
where s.type != 'BACKGROUND'
and s.LAST_CALL_ET > 4000;
Take the hash value and run this SQL next.

select *
from v$session
where sql_hash_value = 'HASHVALNUMMER';
select t.SQL_TEXT
from v$sqltext t
where t.HASH_VALUE = 'HASHVALNUMMER'
order by t.PIECE;

2. Blocking sessions:

select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
--or
select * from v$lock where block=1;select count(*) from gv$lock where block=1;select sid from v$lock where block=1;

Which session is blocked?

select
(select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
SQL from this session:
SELECT a.sql_text, b.sql_hash_value
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = 'von letztes SQL'
ORDER BY a.piece;

More Details

select distinct
a.sid "waiting sid"
, d.sql_text "Warten SQL"
, a.ROW_WAIT_OBJ# "Locked Objekt"
, a.BLOCKING_SESSION "Blocking sid"
, c.sql_text "SQL von blocking session"
from v$session a, v$active_session_history b, v$sql c, v$sql d
where a.event='enq: TX - row lock contention'
and a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#;
und

select spid
from v$process
where background is null
and addr in (select paddr
from v$session
where sid='session_id');
select s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.process = 'Process ID'
and s.paddr = p.addr;

3. Check with this SQL if there was any Siebel crash.

select SRVR_host_name, srvr_error_msg,srvr_user_name,srvr_comp_name,
srvr_task_id_val, srvr_proc_id_val,created
from siebel.s_srm_task_hist where srvr_status = 'ERROR'
and created > sysdate -1 ;
4. Errors in Siebel with Objects (Account, Sub Account, Opportunities, Orders)?

The logs are in 3 Tables : S_SRM_REQUEST, S_SRM_DATA und S_AUDIT_ITEM

SELECT sr.action_id, sr.COMPLETION_TEXT, sd.data_val , sr.resp_text
FROM SIEBEL.S_SRM_REQUEST sr,
SIEBEL.S_SRM_DATA sd
WHERE sr.ROW_ID = sd.PAR_ID
and (sd.data_val like '%Row_id from Object%' or sr.resp_text like '%Row_id from Object%')
AND sr.STATUS = 'ERROR'
--AND sr.EXEC_SRVR_NAME = 'Servername';
5. Laufende SQLs in Siebel (sofort):

select a.event, a.WAIT_TIME, b.username,b.SQL_HASH_VALUE,c.SQL_TEXT
from v$session_wait a, v$session b, v$sqltext c
where
a.sid=b.SID
and a.event <> 'SQL*Net message from client'
and a.event <> 'SQL*Net message to client'
and a.event <> 'rdbms ipc message'
and a.event not like '%idle wait'
and b.username is not null
and c.hash_value = b.sql_hash_value;

Which SQLs ran in the last 2 hours?

select a.parsing_schema_name, round(a.CPU_TIME_DELTA/86400) as Zeit_Sekunde , a.module, round(a.elapsed_time_total/(disk_reads_total*86400)) as Durchschnitt_Seit,
b.SQL_FULLTEXT
from DBA_HIST_SQLSTAT a, v$sql b , dba_hist_snapshot c
where a.disk_reads_total > 0
and c.begin_interval_time >= (sysdate-2/24)
and a.sql_id = b.SQL_ID
and a.snap_id = c.SNAP_ID
and a.PARSING_SCHEMA_NAME not in ('SYS','NEGMON');

6. Workflow errors in the last 2 hours:

SELECT b.NAME,a.ROW_ID,a.CREATED,a.LAST_UPD,a.START_TS,a.CURR_STEP_NAME,a.DB_LAST_UPD_SRC,a.ROOT_INST_ID_VAL,a.SRM_REQ_ID,a.STATUS_CD,a.WORKITEM_ID
FROM SIEBEL.S_WFA_instance a,
SIEBEL.S_WFA_DPLOY_def b
WHERE a.DEFINITION_ID = b.ROW_ID
AND a.STATUS_CD = 'ERROR'
AND a.CREATED >= (SYSDATE - 2/24);

7. Which Worklows executed and executed successfully :

select count(*),a.name from siebel.S_WFA_DEFN_LOG a,siebel.S_WFA_INST_LOG b
where b.definition_id = a.row_id and a.deploy_status_cd = 'ACTIVE'and b.status_cd='COMPLETED'
AND b.created >= SYSDATE -1 group by a.name;

8. Active Webservices and URL

select name, NAMESPACE, CASE INBOUND_FLG when 'Y' then 'Eingehende' when 'N' then 'Ausgehende' END from siebel.S_WS_WEBSERVICE where status_cd = 'ACTIVE';

9. Errors in Siebel Components in the last 6 hours

SELECT Count(*),srvr_comp_name FROM SIEBEL.S_SRM_TASK_HIST WHERE srvr_status <> 'SUCCESS' AND created >= (SYSDATE - 1/4) group by srvr_comp_name ;

10. Tasks per Component in the last 6 hours

SELECT Count(*),srvr_comp_name FROM SIEBEL.S_SRM_TASK_HIST WHERE created >= (SYSDATE - 1/4) GROUP BY srvr_comp_name;

11. Workflow in Repository in Status 'Completed' but not active

SELECT count(*), PROC_NAME
FROM (

SELECT wp.PROC_NAME
from siebel.S_WFR_PROC wp
where 1=1
and wp.STATUS_CD='COMPLETED'
and wp.repository_id=(select r.row_id from siebel.s_repository r
where r.name='Siebel Repository')

minus
select wdd.name
from siebel.S_WFA_DPLOY_DEF wdd
where 1=1
)
GROUP BY proc_name;