Page 2 of 3 < 1 2 3 >
Topic Options
Rate This Topic
#137395 - 04/17/07 10:52 AM Re: ITSM v7, Slow performance? [Re: Shark_7-11]
Vincent_RIEDWEG Offline

Old Hand
****

Registered: 05/24/05
Posts: 1454
Loc: France
B<schemaid_of_your_form>C<fieldid_of_your_attacment_field>

For example B1234C5678900001 table store the attachment files for the attachment field 5678900001 of the form which have the 1234 schemaID
_________________________
Vincent.

Top
#137405 - 04/18/07 02:58 AM Re: ITSM v7, Slow performance? [Re: Vincent_RIEDWEG]
Shark_7-11 Offline
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
#137406 - 04/18/07 03:02 AM Re: ITSM v7, Slow performance? [Re: Shark_7-11]
Shark_7-11 Offline
Stealth Member
*****

Registered: 09/26/06
Posts: 267
Some more Greek (Sound like RAID required, Bad Performance on disk, or disks to slow):

The throughput of the I/O subsystem was significantly lower than expected.

RECOMMENDATION 1: Host Configuration, 21% benefit (5446 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using the
SAME methodology. You might also need to increase the number of disks
for better performance. Alternatively, consider using Oracle's
Automatic Storage Management solution.
RATIONALE: During the analysis period, the average data files' I/O
throughput was 12 K per second for reads and 10 K per second for
writes. The average response time for single block reads was 254
milliseconds.

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]
Vincent_RIEDWEG Offline

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.

 Code:
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]
Shark_7-11 Offline
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
#137410 - 04/18/07 04:08 AM Re: ITSM v7, Slow performance? [Re: Shark_7-11]
Vincent_RIEDWEG Offline

Old Hand
****

Registered: 05/24/05
Posts: 1454
Loc: France
Are these queries executed by ARADMIN?
_________________________
Vincent.

Top
#137412 - 04/18/07 04:09 AM Re: ITSM v7, Slow performance? [Re: Vincent_RIEDWEG]
Vincent_RIEDWEG Offline

Old Hand
****

Registered: 05/24/05
Posts: 1454
Loc: France
Are your oracle statistics up to date?
_________________________
Vincent.

Top
#137414 - 04/18/07 04:15 AM Re: ITSM v7, Slow performance? [Re: Vincent_RIEDWEG]
Shark_7-11 Offline
Stealth Member
*****

Registered: 09/26/06
Posts: 267
I think it's executed by the OracleAdmin user

AWR Report Start/End Dates:
Start Date: 17-Apr-07 00:00:18
End Date: 17-Apr-07 17:00:44
_________________________
Mayhem, Chaos and Anarchy
My job here is complete!

Top
#137415 - 04/18/07 04:27 AM Re: ITSM v7, Slow performance? [Re: Shark_7-11]
Shark_7-11 Offline
Stealth Member
*****

Registered: 09/26/06
Posts: 267
My personal opinion is that they are having Disk I/O problems with the Database
_________________________
Mayhem, Chaos and Anarchy
My job here is complete!

Top
#137416 - 04/18/07 04:50 AM Re: ITSM v7, Slow performance? [Re: Shark_7-11]
Vincent_RIEDWEG Offline

Old Hand
****

Registered: 05/24/05
Posts: 1454
Loc: France
Are the disks configured in asynchronous mode?
Do you have different tablespaces?
Are your tablespaces correctly stripped onto your disks?
_________________________
Vincent.

Top
Page 2 of 3 < 1 2 3 >