Sunday, October 28, 2018

Just Barely Good Enough

What is JBGE?

'Just Barely Good Enough', or JBGE, is the idea that there is a sweet spot for task completion in terms of effort and value.  If a task is completed without enough effort, then the value of that effort will be diminished by time spent resolving issues and/or rework needed.  If a task is completed with more effort than what was actually required the value of the added effort suffers diminished returns.



In my 20+ years working in the IT industry (it pains me that I can say that) I have observed there are 2 types of workers that stand out and they are represented in the chart above as 'A' and 'B'.

The A's

The A's tend to be either junior level/less experienced workers or experienced workers that lack passion for quality.   A's can be perceived and highly regarded by management as someone that get's things done.  In reality an A can be a ticking time-bomb.  They routinely throw work over the wall that doesn't qualify as "good enough", but it's not always obvious at the time.  Months later major incidents occur because the lack of effort when the task was delivered.  Some A's learn from these experiences and over time may shift towards a 'B'.  A's that never learn and continue this behavior eventually find themselves near or on the chopping block.

The B's

B's are often your senior level/more experienced workers or a less experienced worker that is just extremely thorough and cautious by nature.  B's are often the individuals with a deep understanding of the environment beyond their specific specialization.  Peers and management alike seek to have a B help drive direction based on their knowledge and experience.  B's are also the reason your 5 month project is still in the design phase at month 4.  B's pride themselves on bullet proof designs and will take the time to plan for handling every possible failure regardless of what the real impact to the company would be.  B's are often accused of over analyzing.

The Others

As I do believe most people gravitate to being either an 'A' or a 'B', there are some workers who are sometimes an 'A' and sometimes a 'B'.  Someone might gravitate to 'B' tenancies when working on a project they have a passion for while behaving like an 'A' when they are not excited about their task at hand.  Maybe you even have the diamond in the rough that is great at finding the JBGE point in their work.

Achieving JGBE with A's and B's

A potential path to achieving JBGE is refining how the workers and their work are managed.  Tasks are often assigned to workers without definitions for success criteria in place.  Without success criteria defined a worker is left to decide that for themselves.  An 'A' will wash a car with cold water and no soap while a 'B' will wax the car and maybe even repair a scratch in the paint and oh!... there's a dent they could pound out. 

The definition of 'good enough' will change from task to task.  Sometimes 'good enough' will require minimal effort while another task may have no room for error.  With proper guidance, both 'A's and 'B's can both be extremely valuable to the success of a project. 




Friday, October 26, 2018

7 Days of Oracle: Day #7 - Generating AWR Reports

Last, but not least, I will show how to generate an AWR (Automatic Workload Repository) report within SQL*Plus.  The ADDM report attempts to guide a user to a solution where the AWR provides loads of data and leaves interpretation up to you.  Much like ADDM, Oracle supplies a script named 'awrrpt.sql' that will generate an AWR report when executed.

Steps to generate an AWR via SQL*Plus

1)      Find the location of the script on your server (found in */product/12.2.0.1/*/rdbms/admin)

2)      Execute the script in SQL*Plus

 

SQL> @/opt/oracle/base/product/12.2.0.1/demo/rdbms/admin/awrrpt.sql

 

3)      Provide the format you would like to use for the generated report (html/text/active-html)

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

AWR reports can be generated in the following formats.  Please enter the

name of the format at the prompt.  Default value is 'html'.

 

'html'          HTML format (default)

'text'          Text format

'active-html'   Includes Performance Hub active report

Enter value for report_type: text

 

4)      Enter the number of days you would like to report on.  If you just want less than a day, just select 1 and then you will be able to refine your selection further in the process

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

 

Enter value for num_days: 1

 

5)      Specify the snap_Id associated with the time you would like to start analyzing.

Listing the last day's Completed Snapshots

Instance     DB Name                Snap Id   Snap Started            Snap Level

------------ ------------                ----------   ------------------         ----------

 

DEMO     DEMODB                  71818      20 Oct 2018 00:00    1

                                                   71819      20 Oct 2018 00:30    1

                                                   71820      20 Oct 2018 01:00    1   

                                                .

                                                .                         

71860      20 Oct 2018 21:00    1

                                                   71861       20 Oct 2018 21:30    1

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 71859

Begin Snapshot Id specified: 71859

 

6)      Specify the snap_Id for the time you would like to end your analysis.

 

Enter value for end_snap: 71861

End   Snapshot Id specified: 71861

 

7)      Specify a file name, or hit enter to accept the default.

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_71859_71861.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

The results from an AWR report are far too immense to display the full output.  This report proves to be extremely valuable when trying to identify a performance issue, however,  the data provided takes time, understanding, and passion to master.   This blog was just meant to show you how to generate the report, so I will not be going into detail on how to read the report.  That could be a week-long training itself!  If you would like more information on AWR reports, you should check out Oracle documentation to learn more as well as check out some other blogs that are written by Oracle experts.

That concludes my 7 days of Oracle blogs.  I know these were very basic concepts for some, but these were meant to help those who stumble into Oracle much like I had when I was a younger DBA.  Thank you for taking the time to read the words I have thrown at you this last week!  I look forward to writing more and perhaps I’ll even write about my true love…. SQL Server J

Thursday, October 25, 2018

7 Days of Oracle: Day #6 - Generating ADDM Reports

Oracle has built-in reports that can give vital information on the performance of your database.  Today I will show you ADDM (Automatic Database Diagnostic Manager).  ADDM analyzes data from the Automatic Workload Repository and reports it’s findings.

Oracle provides a script that will generate an ADDM report.  The script is named ‘addmrpt.sql’ and can be found in */product/12.2.0.1/*/rdbms/admin.  Locate the script on your machine and then execute that script from within SQL*Plus.

Once executed the script will display all snapIDs that are available for analyzing and their associated timestamp.  You need to specify the snapID for the associated start time and end time that you would like to observe.  You will also be prompted to specify a name for the output file, however, you can just accept the default and hit enter.

The generated report will highlight areas of potential concern along with recommendations for each item called out in the report.  Below is an example of generating an ADDM report from within SQL*Plus.   If you take a close look you will see Oracle has called out 4 different findings.  The top finding is SQL Statements.  The ADDM report goes on to provide recommendations for specific SQL_IDs.

The ADDM may not always pinpoint to an exact issue, but it can often point you to the area you need to focus on.  As always, you can learn more by reviewing Oracle documentation on this topic.

Happy reporting!

 

SQL> @/opt/oracle/base/product/12.2.0.1/demo/rdbms/admin/addmrpt.sql

Listing the last 3 days of Completed Snapshots

Instance                DB Name             Snap Id                 Snap Started                     Snap Level

------------             ------------             ----------                ------------------                    ----------

 

DEMO                   DEMODB             71722                   18 Oct 2018 00:01           1

                                                             71723                   18 Oct 2018 00:30           1

                                                             71724                   18 Oct 2018 01:00           1

                                                             71725                   18 Oct 2018 01:30           1

                                                                .

                                                                .

                                                                71859                   20 Oct 2018 20:30           1

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 71834

Begin Snapshot Id specified: 71834

 

Enter value for end_snap: 71859

End   Snapshot Id specified: 71859

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_71834_71859.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

Using the report name addmrpt_1_71834_71859.txt

Running the ADDM analysis on the specified pair of snapshots ...

Generating the ADDM report for this analysis ...

 

          ADDM Report for Task 'TASK_216884'

          ----------------------------------

 

Analysis Period

---------------

AWR snapshot range from 71834 to 71859.

Time period starts at 20-OCT-18 08.00.25 AM

Time period ends at 20-OCT-18 08.30.07 PM

 

Analysis Target

---------------

Database 'DEMODB' with DB ID 110721271.

Database version 12.2.0.1.0.

ADDM performed an analysis of instance DEMO, numbered 1 and hosted at

demo.demo.com.

 

Activity During the Analysis Period

-----------------------------------

Total database time was 28953 seconds.

The average number of active sessions was .64.

 

Summary of Findings

-------------------

   Description                                                    Active Sessions                Recommendations

                                                                             Percent of Activity

   ------------------------------                                -------------------                  ---------------

1  Top SQL Statements                                   .61 | 94.17                         3

2  "User I/O" wait Class                                  .1 | 15.38                           0

3  Undersized SGA                                          .04 | 6.48                          1

4  PGA_AGGREGATE_LIMIT Throttling     .03 | 3.89                           1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

          Findings and Recommendations

          ----------------------------

 

Finding 1: Top SQL Statements

Impact is .61 active sessions, 94.17% of total activity.

--------------------------------------------------------

SQL statements consuming significant database time were found. These

statements offer a good opportunity for performance improvement.

 

   Recommendation 1: SQL Tuning

   Estimated benefit is .56 active sessions, 86.41% of total activity.

   -------------------------------------------------------------------

   Action

      Run SQL Tuning Advisor on the SELECT statement with SQL_ID

      "9w52tbp4nqgtt".

      Related Object

         SQL statement with SQL_ID 9w52tbp4nqgtt.

        <Example only, SQL Text Removed>

   Rationale

      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

      This part of database time may be improved by the SQL Tuning Advisor.

   Rationale

      Database time for this SQL was divided as follows: 100% for SQL

      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

      execution.

   Rationale

      SQL statement with SQL_ID "9w52tbp4nqgtt" was executed 58158 times and

      had an average elapsed time of 0.31 seconds.

   Rationale

      Top level calls to execute the PL/SQL statement with SQL_ID

      "6mcpb06rctk0x" are responsible for 100% of the database time spent on

      the SELECT statement with SQL_ID "9w52tbp4nqgtt".

      Related Object

         SQL statement with SQL_ID 6mcpb06rctk0x.

         <Example only, SQL Text Removed>

 

   Recommendation 2: SQL Tuning

   Estimated benefit is .03 active sessions, 5.12% of total activity.

   ------------------------------------------------------------------

   Action

      Investigate the INSERT statement with SQL_ID "5hwv4fyrrak98" for

      possible performance improvements. You can supplement the information

      given here with an ASH report for this SQL_ID.

      Related Object

         SQL statement with SQL_ID 5hwv4fyrrak98.

         <Example only, SQL Text Removed>  

   Rationale

      The SQL spent only 28% of its database time on CPU, I/O and Cluster

      waits. Therefore, the SQL Tuning Advisor is not applicable in this case.

      Look at performance data for the SQL to find potential improvements.

   Rationale

      Database time for this SQL was divided as follows: 100% for SQL

      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

      execution.

   Rationale

      SQL statement with SQL_ID "5hwv4fyrrak98" was executed 77 times and had

      an average elapsed time of 13 seconds.

   Rationale

      Waiting for event "acknowledge over PGA limit" in wait class "Scheduler"

      accounted for 71% of the database time spent in processing the SQL

      statement with SQL_ID "5hwv4fyrrak98".

   Rationale

      Top level calls to execute the PL/SQL statement with SQL_ID

      "b6usrg82hwsa3" are responsible for 100% of the database time spent on

      the INSERT statement with SQL_ID "5hwv4fyrrak98".

      Related Object

         SQL statement with SQL_ID b6usrg82hwsa3.

         <Example only, SQL Text Removed>

 

   Recommendation 3: SQL Tuning

   Estimated benefit is .02 active sessions, 2.63% of total activity.

   ------------------------------------------------------------------

   Action

      Run SQL Tuning Advisor on the SELECT statement with SQL_ID

      "34cd4y8mbqvsk".

      Related Object

         SQL statement with SQL_ID 34cd4y8mbqvsk.

<Example only, SQL Text Removed>

   Action

      Use bigger fetch arrays while fetching results from the SELECT statement

      with SQL_ID "34cd4y8mbqvsk".

      Related Object

         SQL statement with SQL_ID 34cd4y8mbqvsk.

<Example only, SQL Text Removed

   Rationale

      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

      This part of database time may be improved by the SQL Tuning Advisor.

   Rationale

      Database time for this SQL was divided as follows: 100% for SQL

      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

      execution.

   Rationale

      SQL statement with SQL_ID "34cd4y8mbqvsk" was executed 32322 times and

      had an average elapsed time of 0.016 seconds.

   Rationale

      At least 4 distinct execution plans were utilized for this SQL statement

      during the analysis period.

   Rationale

      Top level calls to execute the PL/SQL statement with SQL_ID

      "6mcpb06rctk0x" are responsible for 100% of the database time spent on

      the SELECT statement with SQL_ID "34cd4y8mbqvsk".

      Related Object

         SQL statement with SQL_ID 6mcpb06rctk0x.

         <Example only, SQL Text Removed>

 

 

Finding 2: "User I/O" wait Class

Impact is .1 active sessions, 15.38% of total activity.

-------------------------------------------------------

Wait class "User I/O" was consuming significant database time.

Waits for I/O to temporary tablespaces were not consuming significant database

time.

The throughput of the I/O subsystem was not significantly lower than expected.

 

   No recommendations are available.

 

Finding 3: Undersized SGA

Impact is .04 active sessions, 6.48% of total activity.

-------------------------------------------------------

The SGA was inadequately sized, causing additional I/O or hard parses.

The value of parameter "sga_target" was "4096 M" during the analysis period.

 

   Recommendation 1: Database Configuration

   Estimated benefit is .01 active sessions, 2.01% of total activity.

   ------------------------------------------------------------------

   Action

      Increase the size of the SGA by setting the parameter "sga_target" to

      4608 M.

 

   Symptoms That Led to the Finding:

   ---------------------------------

      Wait class "User I/O" was consuming significant database time.

      Impact is .1 active sessions, 15.38% of total activity.

 

 

Finding 4: PGA_AGGREGATE_LIMIT Throttling

Impact is .03 active sessions, 3.89% of total activity.

-------------------------------------------------------

Database sessions were consuming significant PGA memory in some database

instances (or PDBs).

 

   Recommendation 1: Database Configuration

   Estimated benefit is .03 active sessions, 3.89% of total activity.

   ------------------------------------------------------------------

   Action

      Sessions were consuming significant PGA memory. Consider increasing the

      PGA limit for these sessions by increasing the value of the parameter

      PGA_AGGREGATE_LIMIT.

 

   Symptoms That Led to the Finding:

   ---------------------------------

      Wait class "Scheduler" was consuming significant database time.

      Impact is .03 active sessions, 3.89% of total activity.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information

          ----------------------

 

Miscellaneous Information

-------------------------

Wait class "Application" was not consuming significant database time.

Wait class "Commit" was not consuming significant database time.

Wait class "Concurrency" was not consuming significant database time.

Wait class "Configuration" was not consuming significant database time.

CPU was not a bottleneck for the instance.

Wait class "Network" was not consuming significant database time.

Session connect and disconnect calls were not consuming significant database

time.

Hard parsing of SQL statements was not consuming significant database time.

 

The database's maintenance windows were active during 100% of the analysis

period.

 

 

End of Report

Report written to addmrpt_1_71834_71859.txt

PASS Summit - Day 5

Wakey Wakey My very last morning waking up in Seattle and I finally sleep until my alarm goes off.  It was a bitter sweet morning.  On one...