| Oracle® Fusion Applications Administrator's Troubleshooting Guide 11g Release 5 (11.1.5) Part Number E25450-04 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Applications Administrator's Troubleshooting Guide 11g Release 5 (11.1.5) Part Number E25450-04 |
|
|
PDF · Mobi · ePub |
This chapter describes common problems that you might encounter when using Oracle Transactional Business Intelligence and explains how to solve them.
This chapter includes the following topic:
This section describes how you to use the NQQuery.log file and the AdminServer-diagnostic.log file to diagnose Oracle Transactional Business Intelligence query issues such as ODBC errors, ORA-errors, other database errors, and query performance issues.
The NQQuery.log file is useful for any analysis that uses the BI Server to query the datasource. The NQQuery.log records the logical SQL sent by the Oracle BI Presentation layer to the BI Server layer and the physical or ANSI SQL sent by the BI Server to the datasource.
This section contains the following topics:
Section 7.1.5, "Using AdminServer-diagnostic.log File to Troubleshoot Oracle WebLogic Server"
Section 7.1.6, "Using View Data to Check the Datasource Connectivity"
You can enable the NQQuery.log file to diagnose issues that you encounter in Oracle Transactional Business Intelligence queries. Use one of the following methods to enable the NQQuery.log file.
You can enable query logging from the Oracle BI Administration Tool. For information and procedures about enabling the query log file, see "Managing the Query Log" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
You can enable the LogLevel system session variable for the repository by accessing the repository and setting the LogLevel system variable to 7. For information and procedures about setting this variable, see "About System Session Variables" section in the Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition).
If you have administrator permissions, you can log into Presentation Services, access the analysis that you want to troubleshoot, and temporarily change its logging level to 2. This will turn on logging for the analysis even when logging is turned off for all analyses. For more information, see "Setting the Query Logging Level" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
If you use this method to enable the NQQuery.log file, look for the following:
An error like "no log found." An error like this indicates that there is a known error. Look in the NQQuery.log file, which contains additional logging.
The timestamp to help you locate the log entry.
The logical SQL as shown in the error message. This is the SQL sent by Presentation Services to the BI Server layer.
The physical SQL related to the above mentioned logical SQL. Search for physical. The physical SQL is the SQL sent by BI Server through ODBC to the database.
For any more detailed errors.
Note that the NQQuery.log file is visible (but not searchable) in the Fusion Applications Control Log Viewer.
After enabling query logging, if the BI Server caching is enabled, you must clear the cache before re-executing the analysis so that the NQQuery.log file will contain the correct information. Use the following procedure to perform this task.
To clear the BI Server cache:
Log into Presentation Services as an administrator.
In the Global Header, click Administration. The Administration page displays.
Click Manage Sessions. The Manage Sessions page displays.
Scroll to the Cursor Cache section of the page and click the Close All Cursors button.
The request query executed from Presentation Services will be shown in the NQQuery.log file as follows:
RqList 0 as c1 GB, Dim - Procurement Item.Category Name as c2 GB, Dim - Procurement Item.Item Description as c3 GB, # of PO Lines:[DAggr(Fact - Purchasing - Order.# of PO Lines by [ Dim - Procurement Item.Category Name, Dim - Procurement Item.Item Description] )] as c4 GBOrderBy: c1 asc, c2 asc, c3 asc
This query trace explains how a measure is calculated. It also shows the logical table source used to render the data. The query should use the logical table sources having priority 5 for the requests executed from the Oracle Transactional Business Intelligence Real Time Subject Areas.
The query trace shows the view object, its view criteria, and its view links that are used to execute the request. See the following:
<ADFQuery mode="SQLBypass" queryid="14604-3902" locale="en"><Parameters></Parameters>
<Projection>
<Attribute><Name><![CDATA[CategoryName]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.
PurchasingItemP]]></ViewObject>
</Attribute>
<Attribute><Name><![CDATA[ItemDescription]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.
PurchasingItemP]]></ViewObject>
</Attribute>
<Attribute><Name><![CDATA[PurchasingDocumentHeaderTypeLookupCode]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
</ViewObject>
</Attribute>
<Attribute><Name><![CDATA[PoLineId]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]>]
</ViewObject>
</Attribute>
<Attribute><Name><![CDATA[ItemNumber]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.PurchasingItemP]]>
</ViewObject>
</Attribute>
</Projection>
JoinSpec>
<ViewObject>
<Name><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.PurchasingItemP]]></Name>
<ViewLink><Name><![CDATA[oracle.apps.prc.po.publicView.analytics.link.
PurchasingDocumentLinePVOToPurchasingItemPVO]]></Name></ViewLink>
</ViewObject>
<ViewObject>
<Name><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]></Name>
<ViewLink><Name><![CDATA[oracle.apps.prc.po.publicView.analytics.link.
PurchasingDocumentLinePVOToPurchasingItemPVO]]></Name></ViewLink>
</ViewObject>
</JoinSpec>
<DetailFilter>
<ViewCriteria>
<ViewCriteriaRow conjunction="VC_CONJ_AND">
<Attribute><Name><![CDATA[PurchasingDocumentVersionCoSequence]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
</ViewObject>
</Attribute>
<Value><![CDATA[0]]></Value>
<Predicate operator="OPER_EQ"/>
</ViewCriteriaRow>
<ViewCriteriaRow conjunction="VC_CONJ_AND" negated="true">
<Attribute><Name><![CDATA[PurchasingDocumentVersionCoCanceledFlag]]></Name>
<ViewObject><![CDATA[FscmTopModelAM.PrcPoPublicViewAM.StandardLinePVO]]>
</ViewObject>
</Attribute>
<Value><![CDATA[Y]]></Value>
<Predicate operator="OPER_EQ"/>
</ViewCriteriaRow>
</ViewCriteria>
</DetailFilter>
</ADFQuery>
You or the administrator can review the query trace to check that the desired View Objects (VOs) and View Links are executed. The execution of the view objects and view must be based on the Logical model code to render the measure or attributes executed in the request.
The SQL Bypass database should be set up in the repository to send the physical query to the database directly, rather than through the Application Development Framework (ADF) Server. The NQQuery.log file shows the physical query sent to the database if the SQL Bypass database is enabled. See the following excerpt from the log file:
WITH
SAWITH0 AS (select T744257.C501728333 as c1,
T744257.C281594243 as c2,
T744257.C469784899 as c3,
T744257.C168071223 as c4
from
(SELECT V110617254.CATEGORY_NAME AS C501728333, V110617254.
ITEM_DESCRIPTION
AS C281594243, V278099157.TYPE_LOOKUP_CODE263 AS C469784899,
V278099157.PO_LINE_ID AS C168071223, V110617254.ITEM_NUMBER AS
C348883104, V278099157.CO_SEQUENCE AS C78750419, V278099157.CO_
CANCELED_FLAG AS C443371219, V278099157.PO_HEADER_ID1 AS PKA_
PurchasingDocumentHeaderP0, V278099157.VERSION_ID AS PKA_
PurchasingDocumentVersion0 FROM (SELECT PurchasingDocumentLine.PO_LINE_ID,
PurchasingDocumentHeader.PO_HEADER_ID AS PO_HEADER_ID1,
PurchasingDocumentHeader.TYPE_LOOKUP_CODE AS TYPE_LOOKUP_CODE263,
PurchasingDocumentVersion.CO_CANCELED_FLAG,
PurchasingDocumentVersion.CO_SEQUENCE,
PurchasingDocumentVersion.VERSION_ID,
(DECODE(PurchasingDocumentLine.ITEM_ID, NULL,
DECODE(PurchasingDocumentLine.VENDOR_PRODUCT_NUM, NULL,
(PurchasingDocumentLine.ITEM_DESCRIPTION || '[' || CategoryTranslation.
CATEGORY_NAME || ']'),(PurchasingDocumentLine.VENDOR_PRODUCT_NUM || '[' ||
PurchasingDocumentHeader.VENDOR_ID || ']')),TO_CHAR(PurchasingDocumentLine.
ITEM_ID))) AS ITEM_NAME FROM PO_LINES_ALL PurchasingDocumentLine,
PO_HEADERS_ALL PurchasingDocumentHeader, PO_VERSIONS PurchasingDocumentVersion
WHERE (PurchasingDocumentLine.PO_HEADER_ID = PurchasingDocumentHeader.
PO_HEADER_ID AND PurchasingDocumentHeader.PO_HEADER_ID =
PurchasingDocumentVersion.PO_HEADER_ID) AND ( (
(UPPER(PurchasingDocumentHeader.TYPE_LOOKUP_CODE) = UPPER('STANDARD') ) ) ))
V278099157, (SELECT DISTINCT ( DECODE(PL.ITEM_ID,NULL,DECODE(PL.
VENDOR_PRODUCT_NUM, NULL,(PL.ITEM_DESCRIPTION || '[' || TL1.CATEGORY
_NAME||']'),(PL.VENDOR_PRODUCT_NUM || '[' || PH.VENDOR_ID ||']')), TO
_CHAR(I.ITEM_NUMBER))) AS ITEM_NUMBER, PL.ITEM_DESCRIPTION,
PL.CATEGORY_ID,TL1.CATEGORY_NAME, DECODE(PL.ITEM_ID, NULL,DECODE
(PL.VENDOR_PRODUCT_NUM, NULL, 'DESCRIPTION BASED ITEMS','SUPPLIER
ITEMS'),'INVENTORY_ITEMS') AS ITEMTYPE FROM PO_LINES_ALL PL,
EGP_CATEGORIES_TL TL1, PO_HEADERS_ALL PH, PO_SYSTEM_PARAMETERS_ALL SP,
EGP_SYSTEM_ITEMS_B I WHERE PL.CATEGORY_ID=TL1.CATEGORY_ID AND PL.CATEGORY_ID IS
NOT NULL AND PL.PO_HEADER_ID=PH.PO_HEADER_ID AND PL.PRC_BU_ID=SP.PRC_BU_ID AND
I.INVENTORY_ITEM_ID=PL.ITEM_ID) V110617254 WHERE V278099157.ITEM_NAME =
V110617254.ITEM_NUMBER( + ) AND ( ( (V278099157.CO_SEQUENCE = 0 ) ) AND
( NOT ( (V278099157.CO_CANCELED_FLAG = 'Y' ) ) ) )) T744257),
SAWITH1 AS (select D1.c1 as c2, D1.c2 as c3,
case when D1.c3 = 'STANDARD' then D1.c4 end as c4
from
SAWITH0 D1), SAWITH2 AS (select D1.c2 as c2, D1.c3 as c3,
D1.c4 as c4,
ROW_NUMBER() OVER (PARTITION BY D1.c2, D1.c3, D1.c4 ORDER BY D1.c2 DESC,
D1.c3 DESC, D1.c4 DESC) as c5from SAWITH1 D1),SAWITH3 AS (select
count(distinct case D1.c5 when 1 then D1.c4 else NULL end )
as c1, D1.c2 as c2, D1.c3 as c3from SAWITH2 D1
group by D1.c2, D1.c3)
select distinct 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4 from
SAWITH3 D1
order by c2, c3
Use this physical SQL to diagnose which tables, columns joins, and filters are being used by the BI Server to gather data. Table names are aliased (for example, T744257). Search for the table alias in the RPD to find the actual table name. Run the SQL to check whether it works in SQL*Plus.
For performance issues, look for filter columns that are not indexed, and known database performance causes such as NOT IN clauses. Also use Oracle Database Control for performance advice.
The NQQuery.log file also indicates the user who executed the analysis, the timestamp, and the OBI Connection Object used for the analysis. From the Connection Object name, you can refer back to the Oracle BI repository to find the set up properties of the connection, such as whether it uses a native connection or ODBC.
The Oracle BI NQQuery.log file contains detailed BI Server errors, so it is recommended to check this log file. You can access this log file with the Fusion Application Control. The NQQuery.log file shows queries executed at the time of starting the BI Server service. Be sure to check this log for any query failures.
Oracle BI EE gets the view object physical query from the Oracle ADF Server. This query involves the view object query and the security predicate associated to it. You or the administrator need to research the WebLogic Server log file if you suspect an issue in the view object query. You will find this information in the AdminServer-diagnostic.log file. This file is located in the WebLogic Server domain associated to Oracle JDeveloper 11g.
The AdminServer-diagnostic.log file shows the user name, view object, attributes, the view link source and destination entities, and the view criteria for view objects (including security view criteria). In this file you will find the roles associated to the user and the security predicate associated to the roles and users in the roles. This file also shows the composite view object API called for the view object, along with view links and view criteria.
The BI Server uses the properties in the connection pool object, which is located in the Oracle BI repository. Use the following procedure to confirm that your connectivity is set up correctly.
To confirm that your connectivity is set up correctly:
Note:
This procedure uses the ADF BC Datasource as an example.
In the Physical layer of the Administration Tool, expand the database object for the ADF Business Component data source.
Right-click a physical table and click View Data.
Check that the appropriate data displays. Note that if you have just imported, you may need to check in the new objects before you perform this test.
|
 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|