From: The Magnet on
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 |


From: Shakespeare on
Op 27-5-2010 21:28, The Magnet schreef:
> 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 |
>
>

Looks like tag_list takes a long time to read from disk, or a lot of CPU
processing time (as you noted yourself, it's a dynamic column, so I
guess it's CPU). If it's not used in the outer query, it will not be
'created' as you suggest. It will be ignored. Do both explain plans have
corresponding 'Bytes' ?

Shakespeare
From: John Hurley on
Magnet:

> 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.

Start with a 10046 trace of both variations of the query. Put them
thru a resource profiler.

What are the difference in resource usages? What are the differences
in waits?

Go from there ...
From: Vladimir M. Zakharychev on
On May 27, 11:28 pm, The Magnet <a...(a)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
From: Vladimir M. Zakharychev on
On May 27, 11:28 pm, The Magnet <a...(a)unsu.com> wrote:

By the way, just noticed:

> ORDER BY article_id DESC, article_id ASC;

Huh???

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com