Home » RDBMS Server » Performance Tuning » database is going down very frequently
database is going down very frequently [message #272098] Thu, 04 October 2007 00:55 Go to next message
karismapanda
Messages: 58
Registered: January 2007
Member
Hi,

Our database(10.1.0.3.0) is running in mac os.Sometimes its becoming very slow.We have a single datafile for userdata of 5gb.Below is a sample query.

SQL>  select count(*) from ww_ari;

  COUNT(*)
--------
    234613

Elapsed: 00:02:08.50

Execution Plan
--------------------------------------------------------
Plan hash value: 2564173176

---------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |       |  1070   (1)| 00:00:13 |
|   1 |  SORT AGGREGATE    |        |     1 |       |            |          |
|   2 |   TABLE ACCESS FULL| WW_ARI |   234K|       |  1070   (1)| 00:00:13 |
---------------------------------------------------------------------------


Statistics
--------------------------------------------------------
          1  recursive calls
          0  db block gets
       4848  consistent gets
       4720  physical reads
          0  redo size
        220  bytes sent via SQL*Net to client
        268  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Any help will be appreciated.
Thanks,
Karisma

Report message to a moderator

Re: database is going down very frequently [message #272102 is a reply to message #272098] Thu, 04 October 2007 01:11 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
sometimes the same query takes very less time

SQL> SQL>  select count(*) from ww_ari;

  COUNT(*)
----------
    234613

Elapsed: 00:00:00.33

Report message to a moderator

Re: database is going down very frequently [message #272117 is a reply to message #272102] Thu, 04 October 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First time almos all blocks are physically read, second time not (but you didn't popst the statistics).

Regards
Michel

Report message to a moderator

Re: database is going down very frequently [message #272125 is a reply to message #272117] Thu, 04 October 2007 01:44 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
sorry michel,
i can't reproduce the same case.but i can give you another example which is doing more physical reads than the previous one but its taking very less time


SQL> select count(*) from ww_hos;

  COUNT(*)
----------
    785372

Elapsed: 00:00:06.50

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3284 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'WW_HOS' (TABLE) (Cost=3284 Card=
          785384)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      14866  consistent gets
      14855  physical reads
          0  redo size
        395  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed





And i have noticed from my statspack report from yesterday to today

Buffer Hit %:,Execute to Parse %: have decreased from 97 and 31 to 60 and 22.

db file scattered read and db file sequential read have increased from 2,792 and 166 to 41,396 and 1500

Report message to a moderator

Re: database is going down very frequently [message #272137 is a reply to message #272125] Thu, 04 October 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe the disk are elss busy.
Maybe other ones are less working.
Maybe other ones are not hitting the same blocks as you.
Maybe other ones are hitting the same blocks as you.
Maybe things have changed.
...

Regards
Michel

Report message to a moderator

Re: database is going down very frequently [message #272148 is a reply to message #272098] Thu, 04 October 2007 02:17 Go to previous messageGo to next message
karismapanda
Messages: 58
Registered: January 2007
Member
disk read/write status shows as-

Tablespace IO Stats  DB/Inst: MYDB1/MYDB1  Snaps: 5-6
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MY_TS
        42,662      51    3.2    14.7            9        0        667    2.1
PERFSTAT
           167       0    7.4     1.0          100        0          0    0.0
SYSAUX
             3       0    6.7     1.0          237        0          0    0.0
TEMP
            85       0   10.1    28.5           78        0          0    0.0
SYSTEM
           100       0    6.1     1.0           42        0          0    0.0
UNDOTBS1
             0       0    0.0                   42        0          0    0.0
          -------------------------------------------------------------



Is this the reason(as the load is too much on that perticular t/s).

please clarify me ,if i m missing any point.
should i consider anything else.

Thanks & Regards,
Karisma

Report message to a moderator

Re: database is going down very frequently [message #272151 is a reply to message #272148] Thu, 04 October 2007 02:21 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
should i consider anything else.

At least all the points I mentioned.

Did you read How to Identify Performance Problem and Bottleneck?

Regards
Michel

[Updated on: Thu, 04 October 2007 02:21]

Report message to a moderator

Previous Topic: sql performance tuning
Next Topic: Optimizing Oracle SQL statements - can anyone help?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 17:03:50 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.