Performance Tuning OBIEE Analysis Pointing at NoetixViews Part 1

One can look at the SQL hitting the Oracle EBS database which the NoetixView's connection pool is pointing at with a query such as this:



SELECT
*
FROM
v$sql
WHERE
parsing_schema_name IN (
'XXNOETIX'
)
AND
module = 'nqsserver@server_name (TNS V1-V3)'
ORDER BY last_active_time DESC;

 I was looking for an analysis I was working on that was querying the qa_collection_plan_rslts view.  With a little perusing of my result set, I identified the query I want to performance tune:

WITH
SAWITH0 AS (select distinct T3343494.Collection_Elem_Result_Value as c1,
T3343494.Collection_Element_Name as c2,
T3343494.Collection_Plan_Active_Flag as c3,
T3343494.Collection_Plan_Name as c4,
T3343494.Job_Name as c5,
T3343494.Organization_Name as c6,
T3343494.Result_Entered_By_User_Name as c7,
T3343494.Result_Entry_Date as c8,
T3343494.Result_Last_Update_Date as c9
from
XXNOETIX.QA_COLLECTION_PLAN_RSLTS T3343494
where ( T3343494.Collection_Element_Name = 'Comments' and T3343494.Organization_Name = 'org_name' and T3343494.Job_Name = 'job_name' and T3343494.Collection_Plan_Name like 'CW%' ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as D1.c9 as c13 from ( select 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4,
D1.c4 as c5,
D1.c5 as c6,
D1.c6 as c7,
D1.c7 as c8,
D1.c8 as c9,
D1.c9 as c10
from
SAWITH0 D1
where rownum <= 100001

;

It is interesting to see the common table expression that OBIEE creates based on the analysis.

The next steps I usually take is to look at the refresh frequency of the statistics of the tables and start looking at the explain plan.