Re: Performance Tuning

From: <michael_bialik_at_my-deja.com>
Date: Sat, 09 Oct 1999 11:43:32 GMT
Message-ID: <7tn9p2$8bf$1@nnrp1.deja.com>

Hi.

In your application there 70460 FULL TABLE scans ( table scanned ( long ) ) of tables with more than 5 blocks and 38069 FULL table scans ( table scanned ( short)) with 5 or less blocks.
Total_blocks_scanned : 126911699.
Average FULL table scan of long table is : (Total_blocks_scanned - 5 * Table_scan_short) / Table_scan_long is about 1800 blocks.
It means a LOT of I/O.

Start screening you V$SQLAREA :
SELECT ..., buffer_gets/executions avg_buffer_gets from v$sqlarea
where executions > 0
order by buffer_gets/executions desc;

HTH. Michael

In article <37fe2501_4_at_news1.prserv.net>, "Stephen Gabor" <sgabor_at_removethis.jamesway.com> wrote:
>
> <michael_bialik_at_my-deja.com> wrote in message
> news:7tj198$9rv$1_at_nnrp1.deja.com...
> > Hi.
> >
> > Can you post the result of
> > " SELECT name, value FROM v$sysstat ORDER BY 1" ?
> > It looks like you have performance problem in your
> > application and NOT system.
> >
> > Michael.
>
> The results of your requested query:
>
> NAME

VALUE
> ----------------------------------------------------------------

> CPU used by this

session                                                 0


> CPU used when call


started                                               0

> CR blocks created

8426
> Current blocks converted for

CR                                          2

> DBWR buffers scanned

328484
> DBWR

checkpoints                                                       599


> DBWR cross instance


writes                                               0


> DBWR free buffers


found                                                  0

> DBWR lru scans

6317
> DBWR make free

requests                                                  0

> DBWR summed scan depth

328484
> DBWR timeouts

10697
> SQL*Net roundtrips to/from client

4636131
> SQL*Net roundtrips to/from

dblink                                        0


> Unnecesary process cleanup for SCN


batching                              0


> background checkpoints


completed                                        83


> background checkpoints


started                                          83

> background timeouts

32504
> bytes received via SQL*Net from client
267044622
> bytes received via SQL*Net from

dblink                                   0

> bytes sent via SQL*Net to client

875400586
>
> NAME

VALUE
> ----------------------------------------------------------------

> bytes sent via SQL*Net to

dblink                                         0

> calls to get snapshot scn: kcmgss

1658937
> calls to kcmgas

47208
> calls to kcmgcs

8234
> calls to kcmgrs

1902957
> change write

time                                                        0


> cleanouts and rollbacks - consistent read


gets                         665


> cleanouts only - consistent read


gets                                  590

> cluster key scan block gets

191770
> cluster key scans

28387
> commit cleanout failure: write

disabled                                  0


> commit cleanout failures: buffer being


written                          16


> commit cleanout failures: callback


failure                               0


> commit cleanout failures: hot backup in


progress                         0

> commit cleanout number successfully completed
175674
> consistent changes

10584
> consistent gets

170564331
> cross instance CR

read                                                   0

> cursor authentications

32031
> data blocks consistent reads - undo records applied
10582
> db block changes

965799
>
> NAME

VALUE
> ----------------------------------------------------------------

> db block gets

3491580
> deferred (CURRENT) block cleanout applications
48710
> dirty buffers

inspected                                                  0

> enqueue conversions

13184
> enqueue

deadlocks                                                        0

> enqueue releases

142253
> enqueue requests

142285
> enqueue

timeouts                                                         0


> enqueue


waits                                                            0


> exchange


deadlocks                                                       0

> execute count

1639540
> free buffer

inspected                                                    0

> free buffer requested

598085
> global lock convert

time                                                 0


> global lock converts


(async)                                             0


> global lock converts (non


async)                                         0


> global lock get


time                                                     0


> global lock gets


(async)                                                 0


> global lock gets (non


async)                                             0


> global lock release


time                                                 0


> global lock releases


(async)                                             0

>
> NAME

VALUE
> ----------------------------------------------------------------

> global lock releases (non

async)                                         0


> hash latch wait


gets                                                     0

> immediate (CR) block cleanout applications
1255
> immediate (CURRENT) block cleanout applications
50552
> kcmccs called get current

scn                                            0


> kcmgss read scn without going to


DLM                                     0


> kcmgss waited for


batching                                               0

> logons cumulative

1278
> logons

current                                                         170

> messages received

55074
> messages sent

55074
> next scns gotten without going to

DLM                                    0

> no work - consistent read gets

167680231
> opened cursors cumulative

76509
> opened cursors current

3025
> parse count

219092
> parse time

cpu                                                           0


> parse time


elapsed                                                       0

> physical reads

573913
> physical writes

65822
> process last non-idle

time                                               0

>
> NAME

VALUE
> ----------------------------------------------------------------

> recovery array read

time                                                 0


> recovery array


reads                                                     0


> recovery blocks


read                                                     0

> recursive calls

1578496
> recursive cpu

usage                                                      0

> redo blocks written

415743
> redo buffer allocation

retries                                          22

> redo entries

516967
> redo entries

linearized                                                  0


> redo log space


requests                                                  4


> redo log space wait


time                                                 0


> redo log switch


interrupts                                               0


> redo ordering


marks                                                      0

> redo size

195203159
> redo small copies

139179
> redo synch

time                                                          0

> redo synch writes

45625
> redo wastage

10583181
> redo write

time                                                          0


> redo writer latching


time                                                0

> redo writes

47526
>
> NAME

VALUE
> ----------------------------------------------------------------

> remote instance undo block

writes                                        0


> remote instance undo header


writes                                       0


> remote instance undo


requests                                            0


> rollback changes - undo records


applied                                 19

> rollbacks only - consistent read gets
7761
> serializable

aborts                                                      0


> session connect


time                                                     0

> session cursor cache count

2455
> session cursor cache hits

134863
> session logical reads

174041762
> session pga memory

212072584
> session pga memory max

212837340
> session stored procedure

space                                           0

> session uga memory

23078676
> session uga memory max

133955684
> sorts

(disk)                                                             3

> sorts (memory)

59136
> sorts (rows)

24896164
> summed dirty queue

length                                                0

> table fetch by rowid

37546274
> table fetch continued

row                                               44

>
> NAME

VALUE
> ----------------------------------------------------------------

> table scan blocks gotten

126911699
> table scan rows gotten

1.257E+09
> table scans (cache

partitions)                                           0


> table scans (direct


read)                                                0

> table scans (long tables)

70460
> table scans (rowid

ranges)                                               0

> table scans (short tables)

38069
> total number commit cleanout calls

177102
> transaction lock background get

time                                     0


> transaction lock background


gets                                         0


> transaction lock foreground


requests                                     0


> transaction lock foreground wait


time                                    0


> transaction


rollbacks                                                    0


> transaction tables consistent read


rollbacks                             0


> transaction tables consistent reads - undo records


applied               0

> user calls

4605378
> user commits

43543
> user rollbacks

2569
> write requests

9549.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 09 1999 - 06:43:32 CDT