Oracle® Fusion Applications Administrator's Troubleshooting Guide 11g Release 6 (11.1.6) Part Number E25450-05 |
|
|
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 Section 2.5.2, "Need to Troubleshoot an Analysis' Query" and "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.