|
Oracle® Business Intelligence Discoverer Plus User's Guide
10g Release 2 (10.1.2.0.0) Part No. B13915-01 |
|
 Previous |
 Next |
|
Oracle® Business Intelligence Discoverer Plus User's Guide
10g Release 2 (10.1.2.0.0) Part No. B13915-01 |
|
|
Previous |
Next |
This section is aimed at experienced Discoverer users who are familiar with SQL (Structured Query Language) and who are interested in Discoverer's advanced facilities. Discoverer managers will also find this section useful.
This section contains the following topics:
"About viewing the SQL and execution plan with an Oracle8 and later database"
"Looking at an execution plan when using an Oracle 8 and later database"
SQL is a generic programming language used to extract and manipulate data in a database. In other words, SQL enables you to ask a question (known as a query) of the database that the database answers by displaying data.
For example, you might use SQL to ask the question 'Which products sell more than 10,000 per year?'. The database uses SQL to return a list of products that sell more than 10,000, and might also perform other analysis such as sorting, grouping, and totalling of the data.
SQL is a powerful language, but is difficult to learn and use. Although Discoverer itself uses SQL to display and analyze worksheet data, Discoverer users are shielded from underlying SQL.
Because Discoverer shields Discoverer users from underlying SQL, they do not need to know how SQL works. This enables users with no technical database experience and no knowledge of underlying database structures to perform sophisticated data analysis.
However, in some circumstances, you might want to look at SQL being used by Discoverer. For example, to improve Discoverer performance you might need to look at underlying SQL to make sure that queries are being run efficiently.
Summaries are database tables that contain commonly accessed, pre-processed data, which gives the following benefits:
because data is pre-joined and pre-aggregated, Discoverer can access this data more quickly than by using ad hoc query
this also means that the data is processed once and accessed many times, rather than re-processed every time it is needed
A summary folder is how Discoverer represents an underlying summary or materialized view. Summaries and materialized views pre-compute and store aggregated data for use in SQL queries.
Summaries are created by the Discoverer manager to improve the performance of Discoverer, to help do your work more quickly and efficiently. Summary tables and materialized views are created as follows:
a summary table is a table that Discoverer creates.
a materialized view is the Oracle 8 (and later) server's own summary mechanism.
Note: For more information on summaries and materialized views, refer to the Oracle8i Data Warehousing Guide.
An execution plan is a sequence of operations that the Oracle Server performs to execute a SQL statement.
When looking at the underlying SQL that Discoverer is using, use the Discoverer execution plan tab to look the underlying execution plan being used.
You can look at an execution plan to see how a SQL statement is being executed. For example, when using Summaries, you might want to check that a query is using a summary or materialized view created by the Discoverer manager.
When running Discoverer against an Oracle8 and later database, the server controls query redirection by rewriting the SQL to use materialized views. If a server rewrite occurs, the server Execution Plan tells you the name of the materialized view being used.
For more information about materialized views and server rewrites, refer to the Oracle Discoverer Business Intelligence Administration Guide. Contact the Discoverer manager for more information.
Note: With a pre-8.1.6 database, you can look in the SQL tab on the SQL Inspector dialog to see the name of the summary being used.
You view SQL created by Discoverer when you want to see the underlying SQL instructions that Discoverer is using to display the current worksheet.
To view SQL created by Discoverer:
Choose Tools | Show SQL to display the SQL Inspector dialog.
Display the SQL tab to look at the underlying SQL.
(optional) To copy the SQL text into memory, click Copy.
You can then switch to a different application and paste in the text. For example, you might want to paste this text into a text editor, edit the text, then save in a SQL file to that you can execute the file using SQL*Plus.
Click OK to close the SQL Inspector dialog and return to the worksheet.
Notes:
The SQL Inspector dialog might show a shortened version of the SQL that Discoverer sends to the RDBMS. Depending on how Discoverer is configured, inline views might be removed to make the SQL statement more legible. Contact the Discoverer manager for more details about how the SQL Inspector dialog is configured.
You view a SQL Execution Plan when you want to see the underlying instructions that Discoverer is sending to the server.
To view the execution plan used by Discoverer:
Choose Tools | Show SQL to display the SQL Inspector dialog.
Display the Plan tab to look at the underlying execution plan.
(optional) To copy the execution plan text into memory, click Copy.
You can then switch to a different application and paste in the text.
Click OK to close the SQL Inspector dialog and return to the worksheet.
This section contains examples of using the SQL Inspector.
You can use the Execution Plan tab in the SQL Inspector dialog to see the SQL statement that Discoverer sends to the server.
In the figure above, the worksheet contains the City and Profit SUM items. Although the Discoverer manager has created a Summary for these items, the SQL statement displayed in the SQL Inspector: SQL tab does not indicate whether a summary or materialized view is being used. To find out, look at the SQL Inspector: Plan tab (see figure below).
In the figure above, the SQL Inspector Plan: Plan tab shows that a materialized view (called EUL4_MV101264) is being use to retrieve information from the database.
Hint: Names of Materialized views created by Discoverer are prefixed with the EUL name followed by 'MV' and the materialized view ID.
