Skip to Main Content
  • Questions

  • Oracle consuming too much disk resourse! 100%.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Saulo.

Asked: June 13, 2017 - 5:36 pm UTC

Last updated: June 15, 2017 - 12:29 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

My oracle is consuming too much resourses from my disk. Do not matter que querys, even the not much complex, it kills my system. I really need some light to solve it. Thank You.

Using that script:

SELECT *
  FROM (  SELECT ROUND ( ( (cpu_time / 1000000) / 60), 2) AS "Tempo total de CPU",
                 executions AS "Quant. exec.",
                 rows_processed AS "Quant. linhas proc.",
                 disk_reads AS "Leituras no disco",
                 first_load_time AS "Primeira utilização",
                 last_load_time AS "Última utilização",
                 parsing_schema_name AS "Usuário analisado",
                 sql_text AS "SQL exec."
            FROM v$sqlarea
           WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
        ORDER BY 1 DESC)
 WHERE ROWNUM <= 10;

I get the follow results:

Tempo total de CPU  Quant. exec.  Quant. linhas proc.  Leituras no disco  Primeira utilização  Última utilização    
------------------  ------------  -------------------  -----------------  -------------------  ------------------- 
9.17                592           130                  101200621          2017-06-13/10:05:47  2017-06-13 10:05:47  
8.96                591           116                  100884690          2017-06-13/10:06:01  2017-06-13 10:06:01  
0.04                1             0                    116430             2017-06-13/11:24:10  2017-06-13 11:24:26  
0.01                1             15921                1402               2017-06-13/09:54:41  2017-06-13 09:54:41  
0.01                8432          0                    0                  2017-06-13/09:54:43  2017-06-13 09:54:43  
0.01                3             282                  0                  2017-06-13/09:51:20  2017-06-13 09:51:20  
0.01                7296          0                    0                  2017-06-13/09:54:43  2017-06-13 09:54:43  
0.01                8432          8432                 0                  2017-06-13/09:54:42  2017-06-13 09:54:42  
0.01                7684          21                   57                 2017-06-13/09:54:42  2017-06-13 09:54:42  
0.01                7684          0                    0                  2017-06-13/09:54:43  2017-06-13 09:54:43


My disk is almost the full time at 100%"

and Connor said...

Look at those first two rows

592 executions for 101200621 disk reads is ~200,000 disk reads each time you execute that SQL.

That *might* be bad, it *might* be fine - depending on what task the SQL is trying to perform. But they are certainly cause for further investigation.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Fast Answer!

Saulo Lauers, June 14, 2017 - 2:08 pm UTC

The answer was fast and made me try another solution. That solved my problem with index.
Connor McDonald
June 15, 2017 - 12:29 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.