Friday, July 7, 2017

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;
/

Thursday, July 6, 2017

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.

Thursday, December 22, 2016

Query to examine security manager settings of Noetix Query Users whom are authenticated EBS users accounts (type A)

I wanted to be cognizant of how the security manager setting are set-up in the Noetix schema so I created this query to compare and analyze EBS user accounts (type A).

Here is a query showing EBS users security manager settings:

select
    fu.user_name,
    u.user_name user_id,
    am.attribute_code,
    am.module_code,
    ua.*
from
    n_sm_user_attributes ua join n_sm_attribute_modules am on ua.attribute_module_id = am.attribute_module_id
    join n_security_mgr_users u on ua.sm_user_id = u.sm_user_id and ua.sm_user_id = u.sm_user_id
    join apps.fnd_user fu on to_number(ltrim(u.user_name,'#')) = fu.user_id
where
    1=1
and
    fu.user_name = :b_user_name
and
    u.user_type = 'A'
    ORDER BY 1, 3, 4
    ;

Having this query facilitates viewing/comparing accounts with similar profiles for OBIEE account management.

Wednesday, December 7, 2016

Report fails due to a missing A$ column

The Noetix Views communicates to all of us the indices to driving tables associated with a view through the A$ columns.

We have trained our end users not to use these columns, right?  In any event, things happen and some users with creating reports privileges use these columns in the report.

Now time elapses and Oracle Applications gets updated (in our case, from 12.1.3 to 12.2.5).  To make things even more interesting, Noetix Views gets updated from version 6.0.1. to 6.4.1 to 6.5.1 in the course of 2 years.

Once these changes were implemented, some of the A$ columns no longer exists (views change, Oracle Apps changes) and the reports break.

What can be done?

1. We report in Discoverer (and we are transitioning now to OBIEE). Did you back-up the EUL or RPD from the older version? Did you save the makeview%.sql files from the installation home?  You can "resurrect" the old environment in a development environment with these backups.

2. You can create a temporary hookscript to recreate the missing columns and regenerate the RPD or EUL.

Eventually, I had better success creating a temporary hookscript to recreate the missing columns.  This was quicker and more productive.

Thursday, October 6, 2016

6.5.1 NVA Generation Slow in a 12.2.5 Oracle Applications Planning Instance (ASCP), Needing Fixed Object and Data Dictionary Stats Gathered

We have our 12.2.5 Oracle Applications ERP instances and then we have our 12.2.5 Oracle Applications ASCP, the planning module, instances. 

With the 6.4.1 NVA regeneration process associated with our planning module, my NVA regeneration process (stage 2 through 4) would usually take about 15 to 20 minutes.

With 6.5.1 NVA, my regenerations were taking about 1 hour.

While watching my output from SQL*Plus (used for my NVA regeneration process), I see this text, "Default role prefixes to Oracle user names", which is from xgenall.sql script. It stays at this output line for about 1/2 hour or so.

When I look at the most recent records in v$sql, I see that w_process_user_flag.sql is being ran.


Thinking that there might be a locking issue (seen this before), I queried for the existing locked objects:


SELECT
    a.os_user_name,
    a.oracle_username,
    a.object_id,
    c.object_name,
    c.object_type
FROM
    v$locked_object a
    JOIN
        dba_objects c
    ON
        a.object_id = c.object_id;

After reviewing the lack of locks associated with the NVA as a possible source of impediments, I raised a ticket with Magnitude Software.


Their proposal was to execute running statistics for a special group of objects. Specifically, they suggested to have statistics gathered for fixed objects and data dictionary objects:



exec dbms_stats.gather_fixed_objects_stats();
exec dbms_stats.gather_dictionary_stats;



After this was executed by our dba, the performance reverted back to 15 to 20 minutes to refresh.


From a dba perspective, these objects just are not gathered very often (comes down to need). With us migrating to 12.2.5 Oracle Applications in July of 2016, many things change in the database so you would anticipate that these object could benefit from a gathering of statistics.

Friday, August 5, 2016

Monitoring Edition Based Redefinition in a 12.2.5 Oracle Applications Environment


We migrated from a NoetixViews 6.4.1 to a NoetixViews 6.5.1 environment concurrently with our Oracle Applications 12.1.3 to 12.2.5 migration.  There were many, many changes that had to be made with customizations (referencing of objects in custom NoetixViews) 


Also, the 12.2.5 environment relies on a edition based redefinition model for code moves/patching which is a dramatic departure from previous approaches.  The edition based redefinition approach allows for online patching which is in contrast to the outage dependent NVA regeneration process which Magnitude advocates be ran in the runtime edition of the database.  


One (of many approaches) to be aware of the patching process is to monitor editions associated with the database instance.
This query will provide a hierarchical perspective of the editions associated with a database instance with the last level being the current edition (aka runtime edition):


SELECT level,
de.edition_name,
de.parent_edition_name,
de.usable
FROM dba_editions de
START WITH de.edition_name = ‘ORA$BASE’
CONNECT BY PRIOR de.edition_name = de.parent_edition_name
ORDER BY de.edition_name


Here is an example of this query being ran using apps in a development instance.


apps@dev>SELECT level,
2 de.edition_name,
3 de.parent_edition_name,
4 de.usable
5 FROM dba_editions de
6 START WITH de.edition_name = ‘ORA$BASE’
7 CONNECT BY PRIOR de.edition_name = de.parent_edition_name
8 ORDER BY de.edition_name
9 /

LEVEL     EDITION_NAME     PARENT_EDITION_NAME     USABLE
—————————————————————————————————
1               ORA$BASE                                                             YES
2               V_20160703_2120   ORA$BASE                             YES
3               V_20160725_2132   V_20160703_2120                   YES

Level 3 displays to the current runtime edition.
Applies To:   Patching and code move process

Tuesday, May 24, 2016

Lessons Learned from Migrating to the 6.5.1 NoetixViews in an Oracle Applications 12.2 Environment

I received a copy of the 6.5.1 NoetixViews back in late March of this year. We needed to be on a version of the NoetixViews which will be certified with 12.2.5.

Right now we have completed our 2nd round of UAT and various things are still popping up.

Is there a big lesson learned from this migration?

We have lots of customizations (close to 200) with a lot of custom objects referenced. What I noticed is that my custom scripts (xu2 and xu5) that referenced custom objects needed to be properly referenced in the template tables (where applicable).

My convention up until the 6.5.1 NoetixViews with 12.2.5 Oracle Apps was to create a synonym in APPS and reference the object in n_view_table_templates through this synonym.  With 6.5.1, I just confirmed that the custom schema source was properly registered as an Oracle Application and then referenced the table directly from its schema.

This was my greatest pain point.

The different objects that are now associated with an Oracle Apps table (even a custom table owned by a registered schema) have ancillary objects as part of the edition based redefinition construct which is implemented with Oracle Applications 12.2.

Specifically, each table will have an edition based redefintion view owned in its schema and an APPS owned synonym.

Once the views are instantiated in the 12.2 EBS environment, they will then point at the edition based redefinition view associated with the table. This is true of both standard Oracle Apps tables and custom tables in registered Oracle Applications schemas.

With these new objects, the NVA will expect the Noetix admin database account to have grant select with grant option for all of the custom objects referenced in n_view_templates that are associated with the custom tables.


Last year I performed a 6.0.1 to 6.4.1 NoetixViews migration and this year I performed a 6.4.1 to a 6.5.1 NoetixView migration.  Because we are also upgrading Oracle Apps from 12.1.3 to 12.2.5 in this migration, personally, this was more work (and more risk).