Forum OpenACS Development: Getting info on views created by CR

Posted by Janine Ohmer on 02/19/04 06:34 PM

I am attempting to speed up the following query:

SELECT i.parent_id, r.relation_tag, t.item_id, t.revision_id, t.name
, department, ended, institution, institution_city, institution_company, institu
tion_position, institution_state, location, priv, relation, span_type, started,
text_date
FROM phb_spanx t, cr_items i, cr_child_rels r
WHERE t.item_id in (<long list of ids>)
and t.revision_id = i.latest_revision
and r.parent_id(+) = i.parent_id
and r.child_id(+) = i.item_id

phb_spanx is a view of the phb_span table, which is one of those tables that is created by the CR when you create a new content type. I tried to get the view definition from the ALL_VIEWS view but it's a long column and is truncated by sqlplus.

The execution plan for the above query is a bit scary in places, but without seeing the structure of the view I'm feeling a bit lost. For example, I don't know which column in CR_TEXT is causing the full table scan, so I can't check for an index.

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
77 NESTED LOOPS (OUTER)
78 NESTED LOOPS
78 NESTED LOOPS
78 NESTED LOOPS
78 HASH JOIN
77 MERGE JOIN (CARTESIAN)
2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CR_TEXT'
77 SORT (JOIN)
77 INLIST ITERATOR
77 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'CR_ITEMS'
154 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'CR_ITEMS_PK' (UNIQUE)
77 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'CR_REVISIONS'
154 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'ACS_OBJECTS_PK'
(UNIQUE)
154 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PHB_SPAN'
154 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0081933'
(UNIQUE)
154 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_ITEMS'
154 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_ITEMS_BY_LATEST_REVISION' (UNIQUE)
77 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_CHILD_RELS'
154 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_CHILD_RELS_UNQ_ID' (UNIQUE)

(this was run with just a handful of ids; the times are much worse in real life)

Any suggestions?

2: Re: Getting info on views created by CR (response to 1)

Posted by Don Baccus on 02/19/04 11:51 PM

The view just joins the type tables together ... it shouldn't be the source of the problem per se.

What plan do you get if you just use cr_revisions instead of the phb_spanx view (and doing SELECT * to avoid all those phb_span column reference)?

3: Re: Getting info on views created by CR (response to 1)

Posted by Janine Ohmer on 02/20/04 07:28 PM

Thanks Don - I rewrote the query in terms of phb_span and cr_revisions, plus I created a missing index on cr_revisions, and the query now runs in half the time.

4: Re: Getting info on views created by CR (response to 1)

Posted by Don Baccus on 02/20/04 07:41 PM

What was the missing index on? Something that we should have in the standard datamodel (you say it was on "cr_revisions")?

Did you try with the view after adding the index?

5: Re: Getting info on views created by CR (response to 1)

Posted by Janine Ohmer on 02/20/04 08:16 PM

I did try it after adding the index and it was better, but not by a huge amount. The rewrite really helped.

The missing index.... I'm not sure what to do about this. I was thinking of filing a bug report. The problem is that acs-content-repository/sql/oracle/upgrade-4.6.1-4.6.2.sql doesn't work; it has comments at the ends of the lines, after the semi-colons, and Oracle can't handle that. So our sites here at Sloan didn't have any of those indexes.

I can fix and submit the file easily enough, but that isn't going to help anyone who has already done the upgrade. So what's the right thing to do here - write a new upgrade script?

6: Re: Getting info on views created by CR (response to 1)

Posted by Janine Ohmer on 02/23/04 07:18 PM

I still need to speed this up even more if possible. The query now looks like this:

SELECT i.parent_id, r.relation_tag, rev.item_id, rev.revision_id, i.name, department, ended, institution, institution_city, institution_company, institution_position, institution_state, location, priv, relation, span_type, started, text_date
FROM phb_span t, cr_items i, cr_child_rels r, cr_revisions rev
WHERE rev.item_id in (<list of ids>)
and rev.revision_id = i.latest_revision
and t.span_id = rev.revision_id
and r.parent_id(+) = i.parent_id
and r.child_id(+) = i.item_id

And the execution plan is

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
23 NESTED LOOPS (OUTER)
24 NESTED LOOPS
65 NESTED LOOPS
65 INLIST ITERATOR
87 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_REVISIONS'
87 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'CR_REVISIONS_ITEM_ID_IDX' (NON-UNIQUE)
128 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PHB_SPAN'
128 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0081933'
(UNIQUE)
87 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_ITEMS'
87 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_ITEMS_BY_LATEST_REVISION' (UNIQUE)
23 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CR_CHILD_RELS'
46 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'CR_CHILD_RELS_UNQ_ID' (UNIQUE)

I don't have any of my Oracle books with me today and I've already covered the obvious; there are no more full table scans, at least.

Any suggestions on what else I can do here? TIA!