db_file_sequential_read как бороться

Непрофесионал
Дата: 04.10.2004 09:45:59
после разнесения таблиц и идексов на разные физ. диски база как то странно себя ведет когда сессий около 30 все нормально когда увеличивается до 150 начинается ожидание db_file_sequential_read (до разнесения такого большого показателя не было) и если смотреть в Perfomance Manager то диск на котором лежит ТП с таблицам постоянно читается
STATSPACK прилагается

STATSPACK report for

DB Name        DB Id     Instance    Inst Num  Release     OPS   Host         
----------  -----------  ----------  --------  ----------  ----  ----------   
DBOFFICE     3657224743  dboffice           1  8.1.6.3.0   NO    WIZARD       

                                                                Snap Length   
Start Id    End Id       Start Time             End Time         (Minutes)    
--------  --------  --------------------  --------------------  -----------   
       1         2  04-Окт-04 11:10:58    04-Окт-04 11:15:45           4.78   


Cache Sizes                                                                   
~~~~~~~~~~~                                                                   
           db_block_buffers:       65920                                      
              db_block_size:        8192                                      
                 log_buffer:     2048000                                      
           shared_pool_size:   170914560                                      
                                                                              

Load Profile                                                                  
~~~~~~~~~~~~                                                                  
                                       Per Second      Per Transaction        
                                  ---------------      ---------------        
                  Redo size:             9,639.11             1,776.77        
              Logical reads:            24,119.03             4,445.83        
              Block changes:                77.74                14.33        
             Physical reads:               680.77               125.49        
            Physical writes:                70.96                13.08        
                 User calls:               105.21                19.39        
                     Parses:                71.58                13.19        
                Hard parses:                 2.20                 0.41        
                      Sorts:                49.00                 9.03        
               Transactions:                 5.43                             
                                                                              
              Rows per Sort:       43.99                                      
  Pct Blocks changed / Read:        0.32                                      
         Recursive Call Pct:       92.83                                      
 Rollback / transaction Pct:        8.22                                      
                                                                              

Instance Efficiency Percentages (Target 100%)                                 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                 
        Buffer Nowait Ratio:      100.00                                      
        Buffer  Hit   Ratio:       97.18                                      
        Library Hit   Ratio:       99.81                                      
        Redo   NoWait Ratio:      100.00                                      
       In-memory Sort Ratio:       99.99                                      
           Soft Parse Ratio:       96.92                                      
            Latch Hit Ratio:       99.84                                      
                                                                              

Top 5 Wait Events                                                             
~~~~~~~~~~~~~~~~~                                             Wait     % Total
Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read                           100,382            0     .00
direct path read                                   17,308            0     .00
latch free                                          5,494            0     .00
db file scattered read                              2,629            0     .00
log file sync                                       1,554            0     .00
         -------------------------------------------------------------        
Wait Events for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -      2     
->cs - centisecond -  100th of a second                                       
->ms - millisecond - 1000th of a second (unit often used for disk IO timings) 
                                                                              
                                                                   Avg        
                                                     Total Wait   wait  Waits 
Event                               Waits   Timeouts  Time (cs)   (ms)   /txn 
---------------------------- ------------ ---------- ----------- ----- ------ 
db file sequential read           100,382          0           0     0   64.5 
direct path read                   17,308          0           0     0   11.1 
latch free                          5,494      4,165           0     0    3.5 
db file scattered read              2,629          0           0     0    1.7 
log file sync                       1,554          0           0     0    1.0 
log file parallel write             1,410          0           0     0    0.9 
SQL*Net more data to client           623          0           0     0    0.4 
direct path write                     489          0           0     0    0.3 
db file parallel write                397          0           0     0    0.3 
db file parallel read                 254          0           0     0    0.2 
buffer busy waits                     122          0           0     0    0.1 
control file parallel write            93          0           0     0    0.1 
file open                              76          0           0     0    0.0 
SQL*Net break/reset to clien           70          0           0     0    0.0 
control file sequential read           36          0           0     0    0.0 
enqueue                                23          0           0     0    0.0 
library cache pin                      22          0           0     0    0.0 
refresh controlfile command             5          0           0     0    0.0 
LGWR wait for redo copy                 1          0           0     0    0.0 
SQL*Net message from client        30,538          0           0     0   19.6 
SQL*Net message to client          30,534          0           0     0   19.6 
pipe get                              180         69           0     0    0.1 
SQL*Net more data from clien           27          0           0     0    0.0 
         -------------------------------------------------------------        
Background Wait Events for DB: DBOFFICE  Instance: dboffice  Snaps:       1 - 
                                                                              
                                                                   Avg        
                                                     Total Wait   wait  Waits 
Event                               Waits   Timeouts  Time (cs)   (ms)   /txn 
---------------------------- ------------ ---------- ----------- ----- ------ 
log file parallel write             1,410          0           0     0    0.9 
db file parallel write                397          0           0     0    0.3 
control file parallel write            93          0           0     0    0.1 
control file sequential read           15          0           0     0    0.0 
db file sequential read                15          0           0     0    0.0 
db file scattered read                  6          0           0     0    0.0 
LGWR wait for redo copy                 1          0           0     0    0.0 
rdbms ipc message                   3,860        268           0     0    2.5 
pmon timer                             93         93           0     0    0.1 
smon timer                              1          1           0     0    0.0 
         -------------------------------------------------------------        

                                                                              
         -------------------------------------------------------------        
Instance Activity Stats for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -
                                                                              
Statistic                                    Total   per Second    per Trans  
--------------------------------- ---------------- ------------ ------------  
background checkpoints completed                 0          0.0          0.0  
background checkpoints started                   0          0.0          0.0  
background timeouts                            294          1.0          0.2  
branch node splits                               0          0.0          0.0  
buffer is not pinned count               5,318,384     18,531.0      3,415.8  
buffer is pinned count                   7,047,100     24,554.4      4,526.1  
bytes received via SQL*Net from c        2,743,091      9,557.8      1,761.8  
bytes received via SQL*Net from d                0          0.0          0.0  
bytes sent via SQL*Net to client         4,107,045     14,310.3      2,637.8  
bytes sent via SQL*Net to dblink                 0          0.0          0.0  
calls to get snapshot scn: kcmgss          326,638      1,138.1        209.8  
calls to kcmgas                              1,477          5.2          1.0  
calls to kcmgcs                                 88          0.3          0.1  
cleanouts and rollbacks - consist               50          0.2          0.0  
cleanouts only - consistent read               109          0.4          0.1  
cluster key scan block gets                    269          0.9          0.2  
cluster key scans                              121          0.4          0.1  
commit cleanout failures: block l            1,160          4.0          0.8  
commit cleanout failures: buffer                 0          0.0          0.0  
commit cleanout failures: callbac                3          0.0          0.0  
commit cleanout failures: cannot                 0          0.0          0.0  
commit cleanouts                             2,619          9.1          1.7  
commit cleanouts successfully com            1,456          5.1          0.9  
consistent changes                           8,710         30.4          5.6  
consistent gets                          6,833,343     23,809.6      4,388.8  
CR blocks created                              132          0.5          0.1  
current blocks converted for CR                                               
cursor authentications                         318          1.1          0.2  
data blocks consistent reads - un              659          2.3          0.4  
db block changes                            22,311         77.7         14.3  
db block gets                               88,876        309.7         57.1  
DBWR buffers scanned                        10,024         34.9          6.4  
DBWR checkpoint buffers written                 15          0.1          0.0  
DBWR checkpoints                                 0          0.0          0.0  
DBWR free buffers found                      9,736         33.9          6.3  
DBWR lru scans                                 361          1.3          0.2  
DBWR make free requests                        506          1.8          0.3  
DBWR summed scan depth                      10,024         34.9          6.4  
DBWR transaction table writes                    7          0.0          0.0  
DBWR undo block writes                         100          0.4          0.1  
deferred (CURRENT) block cleanout              869          3.0          0.6  
dirty buffers inspected                        407          1.4          0.3  
enqueue conversions                             11          0.0          0.0  
enqueue releases                             8,470         29.5          5.4  
enqueue requests                             8,473         29.5          5.4  
enqueue timeouts                                 0          0.0          0.0  
enqueue waits                                    0          0.0          0.0  
execute count                              317,003      1,104.5        203.6  
free buffer inspected                          425          1.5          0.3  
free buffer requested                      127,257        443.4         81.7  
hot buffers moved to head of LRU           101,719        354.4         65.3  
immediate (CR) block cleanout app              159          0.6          0.1  
immediate (CURRENT) block cleanou              330          1.2          0.2  
index fast full scans (full)                    24          0.1          0.0  
leaf node splits                                18          0.1          0.0  
logons cumulative                                9          0.0          0.0  
Instance Activity Stats for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -
                                                                              
Statistic                                    Total   per Second    per Trans  
--------------------------------- ---------------- ------------ ------------  
messages received                            2,504          8.7          1.6  
messages sent                                2,504          8.7          1.6  
no buffer to keep pinned count             966,325      3,367.0        620.6  
no work - consistent read gets           3,216,169     11,206.2      2,065.6  
opened cursors cumulative                   14,981         52.2          9.6  
parse count (hard)                             632          2.2          0.4  
parse count (total)                         20,543         71.6         13.2  
physical reads                             195,381        680.8        125.5  
physical reads direct                       70,977        247.3         45.6  
physical writes                             20,365         71.0         13.1  
physical writes direct                      19,657         68.5         12.6  
physical writes non checkpoint              20,357         70.9         13.1  
pinned buffers inspected                         7          0.0          0.0  
prefetched blocks                           21,141         73.7         13.6  
prefetched blocks aged out before              250          0.9          0.2  
recursive calls                            391,050      1,362.5        251.2  
redo blocks written                          6,010         20.9          3.9  
redo buffer allocation retries                   0          0.0          0.0  
redo entries                                 8,600         30.0          5.5  
redo log space requests                          0          0.0          0.0  
redo ordering marks                              0          0.0          0.0  
redo size                                2,766,424      9,639.1      1,776.8  
redo synch writes                            1,354          4.7          0.9  
redo wastage                               310,612      1,082.3        199.5  
redo writes                                  1,406          4.9          0.9  
rollback changes - undo records a               41          0.1          0.0  
rollbacks only - consistent read               126          0.4          0.1  
rows fetched via callback                  753,754      2,626.3        484.1  
session logical reads                    6,922,163     24,119.0      4,445.8  
session pga memory                      24,801,296     86,415.7     15,928.9  
session pga memory max                  30,118,964    104,944.1     19,344.2  
session uga memory max                  13,434,000     46,808.4      8,628.1  
sorts (disk)                                     1          0.0          0.0  
sorts (memory)                              14,062         49.0          9.0  
sorts (rows)                               618,640      2,155.5        397.3  
SQL*Net roundtrips to/from client           30,486        106.2         19.6  
SQL*Net roundtrips to/from dblink                0          0.0          0.0  
summed dirty queue length                       18          0.1          0.0  
switch current to new buffer                                                  
table fetch by rowid                     5,549,857     19,337.5      3,564.5  
table fetch continued row                       11          0.0          0.0  
table scan blocks gotten                   125,779        438.3         80.8  
table scan rows gotten                   8,345,505     29,078.4      5,360.0  
table scans (cache partitions)                  23          0.1          0.0  
table scans (long tables)                        4          0.0          0.0  
table scans (short tables)                   9,429         32.9          6.1  
total file opens                                76          0.3          0.1  
transaction rollbacks                            1          0.0          0.0  
transaction tables consistent rea                2          0.0          0.0  
transaction tables consistent rea              147          0.5          0.1  
user calls                                  30,195        105.2         19.4  
user commits                                 1,429          5.0          0.9  
user rollbacks                                 128          0.5          0.1  
write clones created in backgroun                0          0.0          0.0  
write clones created in foregroun                0          0.0          0.0  
Instance Activity Stats for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -
                                                                              
Statistic                                    Total   per Second    per Trans  
--------------------------------- ---------------- ------------ ------------  
         -------------------------------------------------------------        
Tablespace IO Summary for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -  
                                                                              
                                      Avg Read                  Total Avg Wait
Tablespace                      Reads   (ms)        Writes      Waits   (ms)  
------------------------- ----------- -------- ----------- ---------- --------
SMDATA                         90,796      0.0         108        111      0.0
SMINDEX                        13,586      0.0         235          0      0.0
SYSTEM                            323      0.0          29          7      0.0
TOOLS                             145      0.0         143          0      0.0
RBS                                46      0.0         107          4      0.0
         -------------------------------------------------------------        
File IO Statistics for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -     
                                                                              
Tablespace               Filename                                             
------------------------ ---------------------------------------------------- 
         Reads Avg Blks Rd Avg Rd (ms)         Writes  Tot Waits Avg Wait (ms)
-------------- ----------- ----------- -------------- ---------- -------------
RBS                      E:\ORACLE\ORADATA\DBOFFICE\RBS01.DBF                 
            46         1.0         0.0            107          4           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA01.DBF              
         9,262         1.2         0.0             13          9           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA02.DBF              
         9,284         1.1         0.0             15         11           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA03.DBF              
        11,635         1.1         0.0             17         10           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA04.DBF              
        18,073         1.2         0.0             16         16           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA05.DBF              
        13,708         1.2         0.0             18         14           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA06.DBF              
         9,431         1.2         0.0             20         11           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA07.DBF              
         9,799         1.2         0.0              3         21           0.0
                                                                              
SMDATA                   C:\ORACLE\ORADATA\DBOFFICE\SMDATA08.DBF              
         9,604         1.2         0.0              6         19           0.0
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX01.DBF             
           612         1.3         0.0             24          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX02.DBF             
         1,077         1.6         0.0             27          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX03.DBF             
         1,603         1.1         0.0              9          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX04.DBF             
         1,274         1.1         0.0             16          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX05.DBF             
           947         1.3         0.0             12          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX06.DBF             
         2,699         1.3         0.0             30          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX07.DBF             
         1,378         1.6         0.0             42          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX08.DBF             
         1,418         2.0         0.0             24          0              
                                                                              
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX09.DBF             
         1,696         1.3         0.0             37          0              
                                                                              
File IO Statistics for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -     
                                                                              
Tablespace               Filename                                             
------------------------ ---------------------------------------------------- 
         Reads Avg Blks Rd Avg Rd (ms)         Writes  Tot Waits Avg Wait (ms)
-------------- ----------- ----------- -------------- ---------- -------------
SMINDEX                  E:\ORACLE\ORADATA\DBOFFICE\SMINDEX10.DBF             
           882         1.3         0.0             14          0              
                                                                              
SYSTEM                   C:\ORACLE\ORADATA\DBOFFICE\SYSTEM01.DBF              
           323         1.2         0.0             29          7           0.0
                                                                              
TOOLS                    C:\ORACLE\ORADATA\DBOFFICE\TOOLS01.DBF               
           145         1.0         0.0            143          0              
                                                                              
         -------------------------------------------------------------        
Buffer wait Statistics for DB: DBOFFICE  Instance: dboffice  Snaps:       1 - 
                                                                              
                                 Tot Wait    Avg                              
Class                    Waits  Time (cs) Time (cs)                           
------------------ ----------- ---------- ---------                           
data block                 118          0         0                           
undo header                  4          0         0                           
         -------------------------------------------------------------        
Rollback Segment Stats for DB: DBOFFICE  Instance: dboffice  Snaps:       1 - 
->A high value for "Pct Waits" suggests more rollback segments may be required
                                                                              
       Trans Table      Pct   Undo Bytes                                      
RBS No     Gets       Waits     Written        Wraps  Shrinks  Extends        
------ ------------ ------- --------------- -------- -------- --------        
     0          2.0    0.00               0        0        0        0        
     1        149.0    0.00           5,304        0        0        0        
     2      4,466.0    0.02          88,016        0        0        0        
     3        485.0    0.21          42,924        0        0        0        
     4          2.0    0.00               0        0        0        0        
     5        656.0    0.00         339,926        1        0        0        
     6      2,890.0    0.00               0        0        0        0        
     7        366.0    0.00          57,134        0        0        0        
     8        454.0    0.00          45,082        0        0        0        
     9        241.0    0.00          79,186        0        0        0        
    10        616.0    0.00          85,608        0        0        0        
    11      1,103.0    0.09           1,968        0        0        0        
    12        390.0    0.00               0        0        0        0        
    13      2,333.0    0.00          63,718        0        0        0        
         -------------------------------------------------------------        
Rollback Segment Storage for DB: DBOFFICE  Instance: dboffice  Snaps:       1 
->The value of Optimal should be larger than Avg Active                       
                                                                              
RBS No    Segment Size  Avg Active    Optimal Size    Maximum Size            
------ --------------- ----------- --------------- ---------------            
     0         401,408           0                         401,408            
     1      20,963,328   1,511,832      20,480,000      20,963,328            
     2      20,963,328   1,293,112      20,480,000      20,963,328            
     3      20,963,328   1,148,196      20,480,000      20,963,328            
     4      20,963,328     752,626      20,480,000      20,963,328            
     5      19,390,464   1,180,259      20,480,000      19,390,464            
     6      20,963,328   1,120,531      20,480,000      20,963,328            
     7      20,963,328   1,001,214      20,480,000      20,963,328            
     8      20,963,328   1,505,778      20,480,000      20,963,328            
     9      20,963,328     893,392      20,480,000      20,963,328            
    10      20,963,328   1,276,425      20,480,000      20,963,328            
    11      20,963,328     513,789      20,480,000      20,963,328            
    12      13,623,296     396,523      20,480,000      13,623,296            
    13      20,963,328   1,667,032      20,480,000      20,963,328            
         -------------------------------------------------------------        
Latch Activity for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -      2  
->"Pct Misses" should be very close to 0.0                                    
                                                                              
                                                Pct    Avg                Pct 
                                    Get         Get Sleeps      Nowait Nowait 
Latch Name                        Requests     Miss  /Miss    Requests   Miss 
------------------------------- ------------ ------ ------ ----------- ------ 
Active checkpoint queue latch          1,195    0.0                  0        
cache buffer handles                   4,611    0.0                  0        
cache buffers chains              11,470,453    0.1    0.1     152,669    0.0 
cache buffers lru chain               12,115    2.9    0.2     126,517    0.3 
channel handle pool latch                  7    0.0                  8    0.0 
channel operations parent latch           14    0.0                  8    0.0 
Checkpoint queue latch                33,291    0.0    0.0           0        
dml lock allocation                    5,198    0.0                  0        
enqueue hash chains                   16,990    0.0    0.0           0        
enqueues                              28,079    0.0    0.2           0        
event group latch                          7    0.0                  0        
file number translation table          1,126    0.2    0.5           0        
job_queue_processes parameter l            4    0.0                  0        
ktm global data                            1    0.0                  0        
latch wait list                        3,944    0.1    2.5       3,946    0.0 
library cache                      2,747,517    0.6    0.3       1,862    0.1 
library cache load lock                   54    0.0                  0        
list of block allocation               2,982    0.0                  0        
loader state object freelist              20    0.0                  0        
longop free list                         256    0.0                  0        
messages                              11,323    0.0                  0        
multiblock read objects                8,358    0.0    1.0           0        
ncodef allocation latch                    4    0.0                  0        
process allocation                         7    0.0                  7    0.0 
process group creation                    15    0.0                  0        
redo allocation                       11,461    0.0                  0        
redo writing                           8,550    0.0    0.0           0        
row cache objects                    405,922    0.1    0.0          80    0.0 
sequence cache                           599    0.0                  0        
session allocation                   278,118    0.0    0.1           0        
session idle bit                      67,297    0.0    0.6           0        
session switching                          4    0.0                  0        
shared pool                           71,740    0.0    0.2           0        
sort extent pool                       6,912    0.0                  0        
temporary table state object al            9    0.0                  0        
Token Manager                             76    0.0                 80    0.0 
transaction allocation                 7,656    0.0    1.0           0        
transaction branch allocation              4    0.0                  0        
undo global data                      16,112    0.0    0.3           0        
user lock                                 30    0.0                  0        
         -------------------------------------------------------------        
Latch Sleep breakdown for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -  
                                                                              
                                 Get                              Spin &      
Latch Name                     Requests        Misses      Sleeps Sleeps 1-4  
---------------------------- ------------ ----------- ----------- ------------
library cache                   2,747,517      16,286       4,959 12474/3024/4
                                                                  42/346/0    
                                                                              
cache buffers chains           11,470,453       7,328         430 6945/340/40/
                                                                  3/0         
                                                                              
cache buffers lru chain            12,115         347          84 285/40/22/0/
                                                                  0           
                                                                              
row cache objects                 405,922         236           4 233/2/1/0/0 
session allocation                278,118          41           5 36/5/0/0/0  
shared pool                        71,740          33           8 25/8/0/0/0  
enqueues                           28,079          11           2 10/0/1/0/0  
session idle bit                   67,297           5           3 2/3/0/0/0   
undo global data                   16,112           4           1 3/1/0/0/0   
file number translation tabl        1,126           2           1 1/1/0/0/0   
latch wait list                     3,944           2           5 0/0/1/1/0   
multiblock read objects             8,358           1           1 0/1/0/0/0   
transaction allocation              7,656           1           1 0/1/0/0/0   
         -------------------------------------------------------------        
Latch Miss Sources for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -     
                                                                              
                                                          No Wait             
Latch Name                     Where                       Misses      Sleeps 
------------------------------ -------------------------- ------- ----------- 
cache buffers chains           kcbgtcr: kslbegin                0         414 
cache buffers chains           kcbrls: kslbegin                 0          10 
cache buffers chains           kcbgtcr                          0           3 
cache buffers chains           kcbgcur: kslbegin                0           1 
cache buffers chains           kcbzgb: scan from tail. no       0           1 
cache buffers chains           kcbzib: finish free bufs         0           1 
cache buffers lru chain        kcbzgb: multiple sets nowa       0          84 
enqueues                       ksqgtl2                          0           2 
file number translation table  kftts2a                          0           1 
latch wait list                kslfre                           1           5 
library cache                  kglhdgn: child:                  0       2,211 
library cache                  kglic                            0       1,600 
library cache                  kglget: child: KGLDSBRD          0         251 
library cache                  kglpnal: child: alloc spac       0         202 
library cache                  kglpin                           0         194 
library cache                  kgllkdl: child: cleanup          0         139 
library cache                  kglpnal: child: before pro       0          90 
library cache                  kglpnc: child                    0          72 
library cache                  kglupc: child                    0          49 
library cache                  kglget: child: KGLDSBYD          0          31 
library cache                  kglhdgc: child:                  0          24 
library cache                  kglpnp: child                    0          21 
library cache                  kgllkdl: child: free pin         0          16 
library cache                  kglobpn: child:                  0           7 
library cache                  kglidp: parent                   0           4 
library cache                  kglnti                           0           3 
library cache                  kglati                           0           2 
library cache                  kgldte: child 0                  0           1 
library cache                  kglpndl: parent: purge           0           1 
library cache                  kgldti: not under latch          0           1 
multiblock read objects        kcbzib: MBRGET                   0           1 
row cache objects              kqrpre: find obj                 0           2 
row cache objects              kqrso                            0           2 
session allocation             ksuprc                           0           3 
session allocation             ksucri                           0           1 
session allocation             ksudlc                           0           1 
session idle bit               ksupuc: set busy                 0           3 
shared pool                    kghfrunp: parent clatch: w       0          35 
shared pool                    kghupr1                          0           3 
shared pool                    kghfnd: get next extent          0           1 
shared pool                    kghfre                           0           1 
shared pool                    kghfrunp: alloc: clatch no       0           1 
shared pool                    kghfrunp: alloc: wait            0           1 
shared pool                    kghfrunp: clatch: nowait         0           1 
shared pool                    kghfrunp: clatch: wait           0           1 
transaction allocation         ktcxba                           0           1 
undo global data               ktubnd                           0           1 
         -------------------------------------------------------------        
Buffer Pool Sets for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -      2
                                                                              
                                                        Free    Write   Buffer
Set      Buffer  Consistent    Physical    Physical   Buffer Complete     Busy
 Id        Gets        Gets       Reads      Writes    Waits    Waits    Waits
--- ----------- ----------- ----------- ----------- -------- -------- --------
  1      81,081   1,282,225      81,017           9        0        0       82
  2           2           2           0           0        0        0        0
  3      46,344   2,625,348      43,559         700        0        0       40
         -------------------------------------------------------------        
Dictionary Cache Stats for DB: DBOFFICE  Instance: dboffice  Snaps:       1 - 
->"Pct Misses"  should be very low  (< 2% in most cases)                      
->"Cache Usage" is the number of cache entries being used                     
->"Pct SGA"     is the ratio of usage to allocated size for that cache        
                                                                              
                           Get        Pct     Scan   Pct      Mod  Final  Pct 
Cache                   Requests     Miss Requests  Miss      Req  Usage  SGA 
---------------------- ----------- ------ -------- ----- -------- ------ ---- 
dc_constraints                   0               0              0    151   97 
dc_database_links                0               0              0      2   67 
dc_files                         0               0              0     39   87 
dc_free_extents                 70    0.0        1   0.0        1     72   48 
dc_global_oids                   0               0              0      2    9 
dc_histogram_data                0               0              0      0    0 
dc_histogram_data_valu           0               0              0      0    0 
dc_histogram_defs            9,992    0.0        0              0  1,414  100 
dc_object_ids               17,812    0.0        0              1    992   99 
dc_objects                   4,375    0.0        0              2  2,225  100 
dc_outlines                      0               0              0      0    0 
dc_profiles                      7    0.0        0              0      1    8 
dc_rollback_segments            32    0.0        0              0     17   77 
dc_segments                  8,868    0.0        0              1  1,137  100 
dc_sequence_grants               0               0              0      0    0 
dc_sequences                     9    0.0        0              9     18   67 
dc_synonyms                    103    0.0        0              0     87   96 
dc_tablespace_quotas             6    0.0        0              1      8   24 
dc_tablespaces               2,302    0.0        0              0     14   88 
dc_used_extents                  1  100.0        0              1     56   74 
dc_user_grants              60,356    0.0        0              0     68   89 
dc_usernames                 4,140    0.0        0              0    121   92 
dc_users                    67,576    0.0        0              0    139   91 
         -------------------------------------------------------------        


Library Cache Activity for DB: DBOFFICE  Instance: dboffice  Snaps:       1 - 
->"Pct Misses"  should be very low                                            
                                                                              
                    Get      Pct       Pin      Pct                Invali-    
Namespace        Requests    Miss   Requests    Miss      Reloads  dations    
--------------- ----------- ------ ----------- ------ ----------- --------    
BODY                    176    0.0         112    0.0           0        0    
CLUSTER                   2    0.0           4    0.0           0        0    
INDEX                    18    0.0          18    0.0           0        0    
OBJECT                    0                  0                  0        0    
PIPE                    775    0.8         886    0.7           0        0    
SQL AREA             19,256    3.0     358,764    0.3          68       37    
TABLE/PROCEDURE      10,931    0.0     320,340    0.0          13        0    
TRIGGER                 795    0.0         795    0.1           1        0    
         -------------------------------------------------------------        
SGA Memory Summary for DB: DBOFFICE  Instance: dboffice                       
                                                                              
SGA regions                       Size in Bytes                               
------------------------------ ----------------                               
Database Buffers                    540,016,640                               
Fixed Size                               70,580                               
Redo Buffers                          2,056,192                               
Variable Size                       193,122,304                               
                               ----------------                               
sum                                 735,265,716                               
         -------------------------------------------------------------        


SGA breakdown difference for DB: DBOFFICE  Instance: dboffice  Snaps:       1 
                                                                              
Name                                 Begin value    End value   Difference    
----------------------------------- ------------ ------------ ------------    
Checkpoint queue                         885,168      885,168            0    
db_block_buffers                     548,981,760  548,981,760            0    
db_block_hash_buckets                  1,185,864    1,185,864            0    
db_files                                 370,988      370,988            0    
db_handles                               100,000      100,000            0    
dictionary cache                       2,634,912    2,638,932        4,020    
DML locks                                114,608      114,608            0    
enqueue_resources                        146,304      146,304            0    
event statistics per sess                756,000      756,000            0    
file # translation table                  65,572       65,572            0    
fixed allocation callback                    640          640            0    
fixed_sga                                 70,580       70,580            0    
free memory                           34,117,568   26,575,396   -7,542,172    
KGFF heap                                 30,676       30,676            0    
KGK heap                                  18,116       18,116            0    
KQLS heap                              5,070,428    5,051,976      -18,452    
ktlbk state objects                      105,716      105,716            0    
library cache                         47,518,732   47,812,648      293,916    
log_buffer                             2,112,000    2,112,000            0    
long op statistics array                  99,000       99,000            0    
message pool freequeue                   231,152      231,152            0    
miscellaneous                          1,414,016    1,410,036       -3,980    
PLS non-lib hp                             2,096        2,096            0    
PL/SQL DIANA                           4,278,072    4,286,400        8,328    
PL/SQL MPCODE                          9,496,700    9,438,340      -58,360    
processes                                160,000      160,000            0    
sessions                                 479,700      479,700            0    
sql area                              73,842,180   81,160,860    7,318,680    
State objects                            287,944      287,944            0    
SYSTEM PARAMETERS                         61,720       61,720            0    
table columns                             28,104       28,728          624    
table definiti                            13,596       14,816        1,220    
temporary tabl                           307,352      302,640       -4,712    
transactions                             220,324      220,324            0    
trigger defini                            27,552       28,500          948    
trigger inform                             1,748        1,688          -60    
view columns d                             2,544        2,544            0    
         -------------------------------------------------------------        
init.ora Parameters for DB: DBOFFICE  Instance: dboffice  Snaps:       1 -    
                                                                              
                                                                  End value   
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
background_dump_dest          C:\ORACLE\admin\DBOFFICE\bdump                  
buffer_pool_keep              35900                                           
buffer_pool_recycle           20000                                           
compatible                    8.1.0                                           
control_files                 C:\ORACLE\oradata\DBOFFICE\contro               
db_block_buffers              65920                                           
db_block_lru_latches          12                                              
db_block_size                 8192                                            
db_file_multiblock_read_count 16                                              
db_files                      1024                                            
db_name                       DBOFFICE                                        
distributed_transactions      10                                              
global_names                  TRUE                                            
hash_area_size                5012000                                         
instance_name                 DBOFFICE                                        
java_pool_size                32768                                           
job_queue_interval            600                                             
job_queue_processes           3                                               
large_pool_size               7614400                                         
log_buffer                    2048000                                         
log_checkpoint_interval       10000                                           
log_checkpoint_timeout        1800                                            
max_dump_file_size            10240                                           
max_enabled_roles             30                                              
open_cursors                  200                                             
open_links                    14                                              
open_links_per_instance       14                                              
oracle_trace_collection_name                                                  
os_authent_prefix                                                             
parallel_max_servers          5                                               
processes                     200                                             
remote_login_passwordfile     EXCLUSIVE                                       
service_names                 DBOFFICE                                        
shared_pool_size              170914560                                       
sort_area_retained_size       2048000                                         
sort_area_size                2048000                                         
user_dump_dest                C:\ORACLE\admin\DBOFFICE\udump                  
utl_file_dir                  c:\sql                                          
         -------------------------------------------------------------        

End of Report

Подскажите пожалуста что такое может быть? (я начинающий)
Scott Tiger
Дата: 04.10.2004 10:34:40
Наверное, "сломались" планы как следствие переноса таблиц в другие таблеспейсы без последующего пересбора статистики, как следствие - очень много логических чтений.

Кстати, поставь timed_statistics=true.

Требую отставки Президента РФ
Непрофесионал
Дата: 04.10.2004 11:03:36
статистика собрана
Scott Tiger
Дата: 04.10.2004 11:12:02
Тады не знаю. Есть статспаковые репорты от предыдущего варианта расположения датафайлов?

Требую отставки Президента РФ
killed
Дата: 04.10.2004 11:17:39
расскажите, как организована подсистема ВВ.
И какой она была до разнесения.
Непрофесионал
Дата: 04.10.2004 11:20:30
не нету. там все данные в одном ТП вместе с RBS, TEMP и SYSTEM лежали на 5-ом рейде в одном ТП оно кое как все работало решили разнести на разне диски и заодно ТП вместо DMT LMT сделать
Scott Tiger
Дата: 04.10.2004 11:21:51
А сейчас диски - просто по одному, не в массиве?

Требую отставки Президента РФ
Непрофесионал
Дата: 04.10.2004 11:25:45
сейчас:
данные, RBS, system - RAID5
Индексы - scsi без рейда
TEMP - IDE
Непрофесионал
Дата: 04.10.2004 11:44:28
и еще как можно посмотреть что таблица находться в КЭШЕ?
Scott Tiger
Дата: 04.10.2004 11:53:48
Короче, Склифосовский, как соотносится производительность массивов с данными, которые лежат в таблеспейсах SMDATA и SMINDEX с производительностью массива, на которых находились эти же данные до "разнесения".

Требую отставки Президента РФ