OraFAQ Forum: Performance Tuning » database is going down very frequently

Home » RDBMS Server » Performance Tuning » database is going down very frequently

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

database is going down very frequently [message #272098]

Thu, 04 October 2007 00:55

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

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

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

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

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

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

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

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

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 20:09:35 CDT 2024