Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)

Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential. Click to add to Favorites Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1) To BottomTo Bottom In this Document Purpose Troubleshooting Steps References APPLIES TO: Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. ***Checked for Relevance on 10th Sep 2018 *** PURPOSE To use the PL/SQL Profiler please refer to DBMS_PROFILER documentation as per Oracle® Database PL/SQL Packages and Types Reference for your specific release and platform. Once you have executed the PL/SQL Profiler for a piece of your application, you can use script profiler.sql provided in this document. This profiler.sql script produces a nice HTML report with the top time consumers as per your execution of the PL/SQL Profiler. TROUBLESHOOTING STEPS Familiarize yourself with the PL/SQL Profiler documented in the "Oracle® Database PL/SQL Packages and Types Reference" under DBMS_PROFILER. If needed, create the PL/SQL Profiler Tables under your application schema: @?/rdbms/admin/proftab.sql If needed, install the DBMS_PROFILER API, connected as SYS: @?/rdbms/admin/profload.sql Start PL/SQL Profiler in your application: EXEC DBMS_PROFILER.START_PROFILER('optional comment'); Execute your transaction to be profiled. Calls to PL/SQL Libraries are expected. Stop PL/SQL Profiler: EXEC DBMS_PROFILER.STOP_PROFILER; Connect as your application user, execute script profiler.sql provided in this document: @profiler.sql Provide to profiler.sql the "runid" out of a displayed list. Review HTML report generated by profiler.sql. REFERENCES NOTE:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports NOTE:215187.1 - All About the SQLT Diagnostic Tool NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql) NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC) Was this document helpful? Yes No Document Details Email link to this documentOpen document in new windowPrintable Page Type: Status: Last Major Update: Last Update: Language: TROUBLESHOOTING PUBLISHED Sep 10, 2018 Oct 21, 2019 English Related Products Oracle Database Cloud Schema Service Oracle Database Exadata Express Cloud Service Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) Oracle Cloud Infrastructure - Database Service Oracle Database Backup Service Show More Information Centers Oracle Catalog: Service Request Data Collections (SRDCs) for all Products and Services [51.2] Oracle Catalog: Information Centers for All Products and Services [50.2] Information Center: OCI Cloud Guard [2765346.2] Platform as a Service (PaaS) and Oracle Cloud Infrastructure (OCI) Information Center [2048297.2] Index of Oracle Database Information Centers [1568043.2] Show More Document References Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports [1460440.1] All About the SQLT Diagnostic Tool [215187.1] TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql) [224270.1] SQL Tuning Health-Check Script (SQLHC) [1366133.1] Recently Viewed SQL Tuning Health-Check Script (SQLHC) [1366133.1] How to Determine the SQL_ID for a SQL Statement [1627387.1] All About the SQLT Diagnostic Tool [215187.1] SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference [199081.1] * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues [376442.1] Show More Didn't find what you are looking for?Ask in Community... Attachments FILEprofiler.sql script(23.13 KB) HTMLoutput sample(84.27 KB)