Plan instability for 'subscriptions' API query

[nikmagini]

Reported by Kate from IT-DB.

The query below is used by the 'subscriptions' datasvc API.

---

The query below is experiencing some plan instabilities. For the moment we can propose creating an index on dataset.time_create. It might be useful to create it in the descending order, if that's how it is most frequently used.

Cheers, Kate

SELECT sp.request ,

    ds."level"                ,

    ds.item_id                ,

    ds.item_name              ,

    ds.is_open OPEN           ,

    sp.time_create time_update,

    ds.dataset_id             ,

    ds.dataset_name           ,

    n.id node_id              ,

    n.name node               ,

    n.se_name se              ,

    ds.is_move move           ,

    sp.priority               ,

    sp.is_custodial custodial ,

    g.name "group"            ,

    CASE

            WHEN ds.time_suspend_until > :now

            THEN 'y'

            ELSE 'n'

    END suspended                      ,

    ds.time_suspend_until suspend_until,

    ds.time_create                     ,

    ds.files                           ,

    ds.bytes                           ,

    ds.ds_files                        ,

    ds.ds_bytes                        ,

    ds.node_files                      ,

    ds.node_bytes                      ,

    ds.percent_bytes                   ,

    ds.percent_files                   ,

    rx.time_start

FROM t_dps_subs_param sp

    JOIN

            (

                    SELECT 'DATASET' "level"                                                                  ,

                            sd.param                                                                          ,

                            sd.dataset item_id                                                                ,

                            d.name item_name                                                                  ,

                            d.name dataset_name                                                               ,

                            d.id dataset_id                                                                   ,

                            sd.time_create                                                                    ,

                            sd.time_suspend_until                                                             ,

                            sd.time_complete                                                                  ,

                            sd.time_done                                                                      ,

                            sd.is_move                                                                        ,

                            sd.destination                                                                    ,

                            d.time_update                                                                     ,

                            d.is_open                                                                         ,

                            NULL bytes                                                                        ,

                            NULL files                                                                        ,

                            ds_stat.files ds_files                                                            ,

                            ds_stat.bytes ds_bytes                                                            ,

                            reps.node_bytes                                                                   ,

                            reps.node_files                                                                   ,

                            DECODE(ds_stat.bytes, 0, 0, (reps.node_bytes * 100 / ds_stat.bytes)) percent_bytes,

                            DECODE(ds_stat.files, 0, 0, (reps.node_files * 100 / ds_stat.files)) percent_files

                    FROM    t_dps_subs_dataset sd

                            JOIN t_dps_dataset d

                            ON      d.id = sd.dataset

                            LEFT JOIN

                                    (

                                            SELECT  br.node                      ,

                                                    b.dataset                    ,

                                                    SUM(br.node_bytes) node_bytes,

                                                    SUM(br.node_files) node_files

                                            FROM    t_dps_block_replica br

                                                    JOIN t_dps_block b

                                                    ON      br.block = b.id

                                            GROUP BY br.node,

                                                    b.dataset

                                    )

                                    reps ON reps.node = sd.destination

                                    AND reps.dataset  = d.id

                            JOIN

                                    (

                                            SELECT  d.id id           ,

                                                    SUM(b.files) files,

                                                    SUM(b.bytes) bytes

                                            FROM    t_dps_dataset d

                                                    JOIN t_dps_block b

                                                    ON      b.dataset = d.id

                                            GROUP BY d.id

                                    )

                                    ds_stat ON ds_stat.id = d.id

                    UNION

                    SELECT

                            CASE

                                    WHEN

                                            (

                                                    SELECT DISTINCT param FROM t_dps_subs_dataset sd WHERE sd.param = sb.param

                                            )

                                            IS NULL

                                    THEN 'BLOCK'

                                    ELSE 'DATASET'

                            END "level"                                                         ,

                            sb.param                                                            ,

                            sb.block item_id                                                    ,

                            b.name item_name                                                    ,

                            d.name dataset_name                                                 ,

                            d.id dataset_id                                                     ,

                            sb.time_create                                                      ,

                            sb.time_suspend_until                                               ,

                            sb.time_complete                                                    ,

                            sb.time_done                                                        ,

                            sb.is_move                                                          ,

                            sb.destination                                                      ,

                            b.time_update                                                       ,

                            b.is_open                                                           ,

                            b.bytes                                                             ,

                            b.files                                                             ,

                            ds_stat.files ds_files                                              ,

                            ds_stat.bytes ds_bytes                                              ,

                            br.node_bytes                                                       ,

                            br.node_files                                                       ,

                            DECODE(b.bytes, 0, 0, (br.node_bytes * 100 / b.bytes)) percent_bytes,

                            DECODE(b.files, 0, 0, (br.node_files * 100 / b.files)) percent_files

                    FROM    t_dps_subs_block sb

                            JOIN t_dps_block b

                            ON      b.id = sb.block

                            JOIN t_dps_dataset d

                            ON      d.id = b.dataset

                            LEFT JOIN t_dps_block_replica br

                            ON      br.node      = sb.destination

                                    AND br.block = b.id

                            JOIN

                                    (

                                            SELECT  d.id id           ,

                                                    SUM(b.files) files,

                                                    SUM(b.bytes) bytes

                                            FROM    t_dps_dataset d

                                                    JOIN t_dps_block b

                                                    ON      b.dataset = d.id

                                            GROUP BY d.id

                                    )

                                    ds_stat ON ds_stat.id = d.id

            )

            ds ON ds.param = sp.id

            JOIN t_adm_node n

            ON      ds.destination = n.id

            LEFT JOIN t_req_xfer rx

            ON      rx.request = sp.request

            LEFT JOIN t_adm_group g

            ON      g.id = sp.user_group

    WHERE

            (

                    (

                            sp.request = :sp_request1

                    )

                    AND ds.time_create >= :create_since

            )

    ORDER BY ds.time_create DESC,

            ds.dataset_name DESC,

            ds.item_name DESC   ,

            n.name

[nikmagini]

More info from Kate:

On average once every two weeks this query with a bad plan is being execute by 30 sessions concurrently and causes a load so high it bring one of CMSR instances close to reboot. We are alerted by our monitoring or CMS’es monitoring team and we have to remove those sessions manually, so the impact is quite severe.