Aptare Oracle Specific Performance Shell Script

Instructions

1. Download the attached db_perfinfo_capture.sh

2. Create directory /opt/aptare/perfinfo

3. Run chown aptare:aptare /opt/aptare/perfinfo

4. Place db_perfinfo_capture.sh file into the /opt/aptare/perfinfo directory

5. Run chmod 755 /opt/aptare/perfinfo db_perfinfo_capture.sh

6. su - aptare

7. Run /opt/aptare/perfinfo/db_perfinfo_capture.sh

NOTE: Output files will be location in /opt/aptare/perfinfo directory

How to Interpret the Output and what to Look for in the Performance Script Output:

1)  A good place to start is with a look for the Most I/O Intensive Queries  listed under "************ top 10 Queries by highest execution time*****"

This displays TOP 10 Queries by Query Elapsed Time in microseconds.  This helps see the total duration a Query took to produce results.  Any Queries that take more than anticipated time (i.e. in minutes or even hours)  are candidates for investigation.  

Note down the other columns in the output. Such as PARSING SCHEMA if this is anything other than "portal" or "aptare_ro" that is a indicator that somebody is running Queries in ad-hoc manner. If those queries are showing up with taking several minutes/hours, that need to be reported back to the customer. The onus is on the Customer to eliminate those Queries from running on APTARE Portal or Tune the Queries appropriately.

Look at the LAST_LOAD_TIME (tells you when the Query was loaded in Oracle Cache) and LAST_ACTIVE_TIME (tells you when it was last run).  You can use this information to corelate to the Date/Times that customer reported they were facing Performance problems.

ROWS_PROCESSED  tells you how many ROWS were processed.  If the query is taking lot of time to process few rows that is a potential problem.

The MODULE column may Display Values if the Query was executing at the time of Performance Script run and it can tell you the origin of the Query, such as "SQL Developer"  OR "JDBC Thin Client" . This helps identify which may be one time ad-hoc queries and which may be originating from our Portal.

2) Look at any Waits, Blocking , there are Queries that has Description indicating such checks that get printed in the output file.

Those should be showing "No Records found" to indicate all is well.

3) Next look at the Queries Doing FULL Table Scans listed under ************ Queries doing full scans *****

In general All Queries should be using INDEX scans and not FULL scans.  Imagine FULL Scan on 100 million records table as opposed to direct index scan.  The difference can be huge.  Discuss/Report such Queries with Engineering DB team assuming those are executed by "portal" user making it in all likely hood Queries run from the APTARE Portal.   

Engineering DB Team may either suggest use of some Oracle Hints to speed up the Query or may decided to tune it or modify it differently.  Always open an Engineering Escalation so there is tracking of the issue and its resolution.

If the FULL scan Queries are run by  non "portal" or "aptare_ro" users , again those need to be reported to the Customer. The onus is on the customer to eliminate these Queries.  The Queries will play role in performance degradation  of APTARE Portal without any issue inside APTARE App.

4) Look at the  Queries that are consuming large number or I/O Buffers  ************ Queries with large I/O of data buffers *****

If the large data buffers are for 1 or 2 Executions of the Query.  This is a indicator that its potentially needing Tuning.

On the other hand , If you see large number of Buffers used but also several  Executions of the Query then its a indicator that the large number of Buffers could mainly be a result of are due to the  many times the Query got executed.

5) The rest of the information in the output file can be interpreted by looking at the comments preceding the Query in the output file. They indicate what is that the Query is used for.

Questions like,

a. What is my total Database Size?

b. When were the last STATS generated?

c. Do Some Indexes need Rebuild or when the last and current Index rebuild ran?

d. What sessions are currently ACTIVE , what Queries these ACTIVE session Running?

Can be answered by looking at various other  Performance Query outputs bundled in this report.