tying requests to disk io usage

sld

Trying to identify what might tie high disk io usage to individual requests.  Getting some very high disk io usage and trying to track down what requests might be doing that.  Metadata tables like v_monitor.io_usage and v_monitor.system_resource_usage show node level info but no ties to requests.    Meta tables like query_requests show memory_acquired_mb but not the disk usage data.

Chris_S

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

Adrian_Oprea_1

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