tying requests to disk io usage

Comments

• Options

  Chris_S

  August 2016

  Hi sld, 

   

  One place to start looking is at query_events, and check for which queries the event_type='GROUP_BY_SPILLED' or other '%SPILLED% events since those queries will be hitting the disk. You can then try to correlate those queries to times of high disk usage. 

   

  You can also try looking at v_monitor.execution_engine_profiles:

  select * from execution_engine_profiles where counter_name like '%disk%' to see where queries used the disk in their execution. This may not be as helpful, however, because the data in this view tends to roll out quickly for large workloads. But if you do suspect a query, you can profile it and check how much bytes the query is actually reading/writing from disk. 

   

  Hope that helps,

  Chris

  0
• Options

  Adrian_Oprea_1

  August 2016

  Hi ,

   Try to use ths information from this query:

   -this query can be written better but is doing the job.

   It will find sessions that spilled, track the used operator and list user and the sql he is running. Ideal would be to have labels on your queries so tracking/pointing to your worst query would be easyer. 

   

    One thing i noticed related with high IO is the use of temporary table and queryes on top of heavy encoded projections. 

   But the biggest vilen of them all is the limited memeory and the enabled spill option, this will limit memory for a user/pool but will kick your arese on the IO use and CPU. (it`s a trade of i guess), unless you opt not to enable spill. 

   

  WITH
      spilled AS
      (
          SELECT
              session_id,
              transaction_id,
              statement_id,
              event_type,
              event_description
          FROM
              v_monitor.query_events
          WHERE
              event_type IN ('JOIN_SPILLED',
                             'GROUP_BY_SPILLED')
      )
      ,
      engineprof AS
      (
          SELECT
              session_id,
              transaction_id,
              statement_id,
              operator_name
          FROM
              execution_engine_profiles
          WHERE
              (
                  session_id,transaction_id,statement_id) IN
              (
                  SELECT
                      session_id,
                      transaction_id,
                      statement_id
                  FROM
                      spilled)
          ORDER BY
              counter_value DESC
      )
      ,
      mix AS
      (
          SELECT
              sp.event_type,
              sp.event_description,
              ep.*
          FROM
              spilled sp
          JOIN
              engineprof ep
          ON
              sp.session_id=ep.session_id
      )
  SELECT
      m.*,
      qr.user_name,
      qr.request_duration_ms,
      qr.request
  FROM
      mix m
  JOIN
      query_requests qr
  ON
      (
          m.session_id,m.transaction_id,m.statement_id)=(qr.session_id,qr.transaction_id,
      qr.statement_id)

  Try to see if you can correlate your high IO after running this script, and if you can make it better make sure oyu share back  

  0