Re: Understanding this query

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 28 May 2010 02:19:41 -0700 (PDT)
Message-ID: <0f6c81b8-432c-4a01-91df-965e3b1108b6_at_m33g2000vbi.googlegroups.com>



On May 27, 11:28 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> Ok, we have a VERY complex query here which is dynamically made.  The
> issue we are having is this:
>
> Take a look at the inner part of the query, there is a dynamic column
> with some XMLAGG functions called "tag_list".
>
> If I do not place that column in the outer most part of the query, it
> runs in like .1.  However, if I put the "tag_list" column as part of
> the return set it takes 2 seconds.
>
> Why?  The column is already created.  How can adding it to the outer
> query list cause a 2 second increase in execution time?  The explain
> plan is the same.
>
> Here is the query with the explain plan afterwards.  Again, remove the
> "tag_)list" from the outer most query, it runs in .1.  Add it to the
> outer most query and it runs in 2.0 with the same execution plan.
>
> SELECT article_id, publish_date, status_id, user_id, first_name,
> last_name,
>        status_text, tag_list
> FROM (SELECT article_list, article_id, publish_date, status_id,
> user_id, first_name, last_name,
>              status_text, tag_list, ROW_NUMBER() OVER (ORDER BY
> revision_date DESC) article_rows
>       FROM (SELECT a.article_id, a.date_entered, a.publish_date,
> s.status_id, u.user_id,
>                    first_name, last_name, s.status_text,
> revision_date,
>                    ROW_NUMBER() OVER (PARTITION BY a.article_id ORDER
> BY revision_date DESC) article_list,
>                    (SELECT RTRIM (XMLAGG (XMLELEMENT (e,
> t.tag_id||':'||tag_name || ',')).EXTRACT ('//text()'), ',')
>                     FROM tags t, article_tags at
>                     WHERE a.article_id = at.article_id
>                       AND at.tag_id = t.tag_id) tag_list
>              FROM  articles a, users u, revisions r, status s
>              WHERE  a.status_id    = s.status_id
>                AND  a.article_id   = r.article_id
>                AND  a.owner_id     = u.user_id)
>       WHERE article_list = 1)
> WHERE article_rows BETWEEN 1 AND 25
> ORDER BY article_id DESC, article_id ASC;
>
> -----------------------------------------------------------------------------------------------------
> | Id  | Operation                           | Name          | Rows  |
> Bytes | Cost (%CPU)| Time     |
> -----------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT                    |               |     1 |
> 2144 |    62   (5)| 00:00:01 |
> |   1 |  SORT ORDER BY                      |               |     1 |
> 2144 |    62   (5)| 00:00:01 |
> |*  2 |   VIEW                              |               |     1 |
> 2144 |    61   (4)| 00:00:01 |
> |*  3 |    WINDOW SORT PUSHED RANK          |               |     1 |
> 2153 |    61   (4)| 00:00:01 |
> |*  4 |     VIEW                            |               |     1 |
> 2153 |    60   (2)| 00:00:01 |
> |*  5 |      WINDOW SORT PUSHED RANK        |               |     1
> |   177 |    60   (2)| 00:00:01 |
> |   6 |       TABLE ACCESS BY INDEX ROWID   | REVISIONS     |     1
> |    22 |     2   (0)| 00:00:01 |
> |   7 |        NESTED LOOPS                 |               |     1
> |   177 |    59   (0)| 00:00:01 |
> |   8 |         NESTED LOOPS                |               |     1
> |   155 |    57   (0)| 00:00:01 |
> |   9 |          MERGE JOIN CARTESIAN       |               |     1
> |   107 |     5   (0)| 00:00:01 |
> |  10 |           TABLE ACCESS FULL         | STATUS        |     1
> |    40 |     2   (0)| 00:00:01 |
> |  11 |           BUFFER SORT               |               |    80 |
> 5360 |     3   (0)| 00:00:01 |
> |  12 |            TABLE ACCESS FULL        | USERS         |    80 |
> 5360 |     3   (0)| 00:00:01 |
> |* 13 |          TABLE ACCESS BY INDEX ROWID| ARTICLES      |   223 |
> 10704 |    52   (0)| 00:00:01 |
> |* 14 |           INDEX RANGE SCAN          | ARTICLES_IDX3 |   445
> |       |     1   (0)| 00:00:01 |
> |* 15 |         INDEX RANGE SCAN            | REVISIONS_IDX |     1
> |       |     1   (0)| 00:00:01 |

Hmm... I don't see TAGS nor ARTICLE_TAGS being accessed in the plan (and tag_list is built from them.) Is this plan for the query that does not include tag_list in the result set?

Did you measure the response time of the subquery that creates tag_list alone? It probably is the major contributor and most time probably goes to CPU, because you dynamically create an XML fragment, populate it, and aggregate it, which causes it to be parsed and processed and these are all CPU-intensive operations. If your system is CPU-bound, this might be the reason. Another possible cause could be that TAGS or ARTICLE_TAGS or both are large and are not accessed efficiently (for example, because they are not properly indexed.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri May 28 2010 - 04:19:41 CDT

Original text of this message