Costly SQL Execution Plan when Querying Versioned Content
(Doc ID 1076015.1)
Last updated on FEBRUARY 14, 2025
Applies to:
Oracle WebLogic Portal - Version 10.0.1 to 10.3.0Information in this document applies to any platform.
Symptoms
Using the WLP API com.bea.content.federated.ContentManagerFactory.getVersionManager().search(ctx,search) to query versioned content from a content repository, the following SQL query is fired:
SELECT DISTINCT B.NODE_ID, B.NODE_VERSION_ID, B.CM_MODIFIED_DATE,
B.MODIFIED_BY,B.VERSION_COMMENT, B.LIFECYCLE_STATUS, A.OBJECT_CLASS_ID,
A.REPOSITORY_NAME
FROM CMV_NODE A, CMV_NODE_VERSION B
WHERE A.NODE_ID = B.NODE_ID AND A.NODE_ID = ?;
B.MODIFIED_BY,B.VERSION_COMMENT, B.LIFECYCLE_STATUS, A.OBJECT_CLASS_ID,
A.REPOSITORY_NAME
FROM CMV_NODE A, CMV_NODE_VERSION B
WHERE A.NODE_ID = B.NODE_ID AND A.NODE_ID = ?;
You may observe that the resulting execution plan for this SQL query is carrying out a full table scan on the CMV_NODE table as follows (taken from an Oracle 10G database):
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 610 | 43 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CMV_NODE_VERSION | 10 | 330 | 11 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 610 | 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | CMV_NODE | 1 | 28 | 32 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_CMV_NODE_VER | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("A"."NODE_ID")=48607)
4 - access("A"."NODE_ID"="B"."NODE_ID")
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 610 | 43 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CMV_NODE_VERSION | 10 | 330 | 11 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 610 | 43 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | CMV_NODE | 1 | 28 | 32 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_CMV_NODE_VER | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("A"."NODE_ID")=48607)
4 - access("A"."NODE_ID"="B"."NODE_ID")
This could result in a performance impact, if the application queries versioned content many times using the above API.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |