Using vpd security, need a number of Oracle Responsibilities to Write OBIEE Analysis

Since we have vpd security set-up for the NoetixViews, I want to assign myself the appropriate responsibilities to access the appropriate subject areas I need. Next, I execute the Noetix Security Update concurrent program.

Eventually, I will create a FNDLOADER script with all the responsibilities I need in cloned environments, but in the interim; I created an anonymous block to cycle through a set of Oracle responsibilities (minus the responsibilities which I have).



DECLARE
    v_fnd_user_name            fnd_user.user_name%TYPE;
    v_error_code               NUMBER(38);
    v_error_message            VARCHAR2(200);
BEGIN

    v_fnd_user_name := 'EBS_USERNAME';
    FOR usr IN (
        WITH diff AS (
            SELECT
                a.application_short_name,
                r.responsibility_key
            FROM
                fnd_application a
                JOIN fnd_responsibility r ON a.application_id = r.application_id
            WHERE
                    1 = 1
                AND
                    r.responsibility_key LIKE '%DESIRED_PATTERN' --escape '\'
            MINUS
            SELECT
                a.application_short_name,
                r.responsibility_key
            FROM
                fnd_user u
                JOIN fnd_user_resp_groups g ON u.user_id = g.user_id
                JOIN fnd_application a ON g.responsibility_application_id = a.application_id
                JOIN fnd_responsibility r ON a.application_id = r.application_id
            WHERE
                    g.responsibility_id = r.responsibility_id
                AND
                    u.user_name = v_fnd_user_name
        ) SELECT
            diff.application_short_name,
            diff.responsibility_key
        FROM
            diff
    ) LOOP
        BEGIN
            dbms_output.put_line('username => '
             || v_fnd_user_name
             || ','
             || 'resp_app  => '
             || usr.application_short_name
             || ','
             || 'resp_key  => '
             || usr.responsibility_key
             || ','
             || 'security_group => STANDARD');

            fnd_user_pkg.addresp(
                username         => v_fnd_user_name,
                resp_app         => usr.application_short_name,
                resp_key         => usr.responsibility_key,
                security_group   => 'STANDARD',
                description      => NULL,
                start_date       => SYSDATE,
                end_date         => SYSDATE + 100
            );

        EXCEPTION
            WHEN OTHERS THEN
                v_error_code := sqlcode;
                v_error_message := sqlerrm;
                dbms_output.put_line(TO_CHAR(v_error_code) || '- ' || v_error_message);
        END;
    END LOOP;
END;
/

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.