#137405 - 04/18/07 02:58 AM
Re: ITSM v7, Slow performance?
[Re: Vincent_RIEDWEG]
|
Stealth Member
   
Registered: 09/26/06
Posts: 267
|
I Understand the whole DB data storing (T B H Tables) In the PDF I don't find the settings you were referring to I only find SQL Scripts on how to update the Request ID, How to Index for FTS etc. Nothing about how to setup LOB. But never mind that, the DBA gave me some stats and unfortunately I don't understand Greek, (or is that Oracle.. ??): Can anybody tell me what this means... In English if possible :-p
Individual database segments responsible for significant user I/O wait were found.
RECOMMENDATION 1: Segment Tuning, 4.3% benefit (1096 seconds) ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000054992C00015$$" with object id 55018. RELEVANT OBJECT: database object with id 55018 ACTION: Investigate application logic involving I/O on LOB "ARADMIN.SYS_LOB0000054992C00015$$" with object id 55018. RELEVANT OBJECT: database object with id 55018 RATIONALE: The I/O usage statistics for the object are: 0 full object scans, 5629 physical reads, 770 physical writes and 5629 direct reads. RATIONALE: The SQL statement with SQL_ID "22qwvwfmk04bm" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 22qwvwfmk04bm
RECOMMENDATION 2: Segment Tuning, 3.7% benefit (933 seconds) ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000089156C00012$$" with object id 89182. RELEVANT OBJECT: database object with id 89182 ACTION: Investigate application logic involving I/O on LOB "ARADMIN.SYS_LOB0000089156C00012$$" with object id 89182. RELEVANT OBJECT: database object with id 89182 RATIONALE: The I/O usage statistics for the object are: 0 full object scans, 4668 physical reads, 1933 physical writes and 4668 direct reads. RATIONALE: The SQL statement with SQL_ID "f4345rw1a1jdu" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID f4345rw1a1jdu
RATIONALE: The SQL statement with SQL_ID "c1sv7qyttcvdn" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID c1sv7qyttcvdn
RECOMMENDATION 3: Segment Tuning, 2.1% benefit (529 seconds) ACTION: Run "Segment Advisor" on LOB "ARADMIN.SYS_LOB0000054992C00016$$" with object id 55020. RELEVANT OBJECT: database object with id 55020 ACTION: Investigate application logic involving I/O on LOB "ARADMIN.SYS_LOB0000054992C00016$$" with object id 55020. RELEVANT OBJECT: database object with id 55020 RATIONALE: The I/O usage statistics for the object are: 0 full object scans, 5627 physical reads, 770 physical writes and 5627 direct reads. RATIONALE: The SQL statement with SQL_ID "22qwvwfmk04bm" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 22qwvwfmk04bm
SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (23% impact [5807 seconds])
_________________________
Mayhem, Chaos and Anarchy My job here is complete!
|
|
Top
|
|
|
|
|
#137408 - 04/18/07 03:53 AM
Re: ITSM v7, Slow performance?
[Re: Shark_7-11]
|
Old Hand
  
Registered: 05/24/05
Posts: 1454
Loc: France
|
Ask your DBA for the SQL statments which consume a lot of I/O. He already gives you the SDL_ID of these statments. So you can check which form and request is behind these statments. The folloging SQL statment can help you and you DBA to find consuming SQL. select a.username,
disk_reads,
executions,
round(disk_reads/decode(executions,0,1,executions)) "Reads/Execs",
sql_text
from dba_users a, v$session, v$sqlarea
where parsing_user_id=user_id
and address=sql_address(+)
and disk_reads > 10000
order by disk_reads desc, executions desc
_________________________
Vincent.
|
|
Top
|
|
|
|
|
#137409 - 04/18/07 04:01 AM
Re: ITSM v7, Slow performance?
[Re: Vincent_RIEDWEG]
|
Stealth Member
   
Registered: 09/26/06
Posts: 267
|
I've got the AWR Report already (Don't want to paste it here though) but here is a snippet:
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN prtsm_growth_monitor; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
AND
INSERT INTO TBL_SPACE_CHECK_INFO SELECT TABLESPACE_NAME , SUM(BYTES) / (1024 * 1024 ) , TRUNC(SYSDATE) , DB.NAME FROM DBA_EXTENTS , V$DATABASE DB GROUP BY TABLESPACE_NAME , TRUNC(SYSDATE) , DB.NAME
Both Of these queries takes 7291.80 Elap per Exec (s) more than 1,233,344,249.00 Gets per Exec 506,334.00 Reads per Exec
They stand out like sore thumbs !!!
_________________________
Mayhem, Chaos and Anarchy My job here is complete!
|
|
Top
|
|
|
|
|
|
|