I/O Intensive queries on production
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: I/O Intensive queries on production

  1. #1
    Join Date
    Apr 2002
    Posts
    291
    Hi All,

    Can any one help me in providing the script for finding out first 10 or 20 sql queries which are I/O intensive right from the day the machine was rebooted. My version of Oracle is 9.0.1 and linux 7.1 . Our Production DB is hitting worst by these queries . I need to submit these queries immediately .

    Looking forward for your great help.

    thanks in advance


    PNRDBA

  2. #2
    Join Date
    Jul 2002
    Posts
    132
    Are these queries currently running?
    To work out with such problems, I am using Precise, it is a good software to find such problems.

  3. #3
    Join Date
    Apr 2002
    Posts
    291
    Hi ,
    All those queries might not run currently. Right from the time the machine is rebooted, lets say 25 days back, they will be stored in v$sqlarea, but i want a specific query which will return you the most I/O intensive.

    Any one please help me this is most urgent

    thanks
    PNRDBA

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pnrdba
    Right from the time the machine is rebooted, lets say 25 days back, they will be stored in v$sqlarea.....
    No, they will be not. They will be aged out eventually. Only the recently executed cursors are stored in sqlarea, and the new ones are pushing old ones out of the shared pool. The number of cursors stored in sqlarea depends on the size of the shared pool, the complexity of SQL statements, the frequency of their executions, etc etc...

    For those queries that are in v$sqlarea you can find the most I/O intensive ones by looking at their values in columns: EXECUTIONS, DISK_READS, BUFFER_GETS.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2002
    Posts
    291
    Hi Jmodic,
    You are correct. My shared_pool size is 320 MB. to know the queries, i'm using below script as of now. Is this the correct way to meet my requirements?? am i getting the correct output by this query? Please help me.

    select address, to_char(hash_value, '999999999999') 'hash value",
    disk_reads, executions, disk_reads/executions "reads/executions",
    sql_text
    from v$sqlarea
    where disk_reads > 450 and executions >10
    odrer by disk_reads;



    Thanks

    PNRDBA

  6. #6
    Join Date
    Apr 2002
    Posts
    291
    Gurus,
    Please help me

    thanks
    PNRDBA

  7. #7
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    your query's not bad, I would just order by reads/executions, which is in my opinion, more relevant of a high I/O activity :

    select address, disk_reads/executions, sql_text
    from v$sqlarea
    where executions > 10
    order by 2 desc
    /

  8. #8
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by pnrdba
    Gurus,
    Please help me

    thanks
    Implement statspack and generate reports using spreport.sql.. You will get all those resource intensive queries in the report.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width