Skip navigation.

DBA Blogs

rmoug 2015 presentation ready to go ... makes me ( not ) grumpy

Grumpy old DBA - Sun, 2014-11-09 19:04
All ready to roll with RMOUG 2015 Training days presentation "OS Truth, little white lies, and the Oracle Wait Interface".

Best of course to come to RMOUG 2015 Training days ... but this is link to pdf version of the presentation here: John Hurley RMOUG 2015

If you are out there ( very excited personally my first time at this conference ) please kick me and say hello.  Ok maybe skip the kicking part ...


Categories: DBA Blogs

handy ash queries to look at blocked sessions ( how many when for what event ) ...

Grumpy old DBA - Sun, 2014-11-09 15:13
Licensing may be required ... please check if applicable.

A query like this can be used to check how much blocking and what session it was ... ( so then you can drill into those blockers sessions ).  Probably can be done easily with some kind of rollup query?

Adjust SAMPLE_TIMEs in where clause below.

select ash_data.*, substr(sqlinfo.sql_text,1,70)
from
(SELECT to_char(ash.sample_time,'MM/DD/YYYY HH24:MI:SS') what_time,  count(*) sessions_blocked, ash.event, ash.blocking_session, ash.blocking_session_serial#, ash.sql_id, ash.sql_opname
FROM DBA_HIST_ACTIVE_SESS_HISTORY ash
WHERE ash.SAMPLE_TIME >= TO_DATE('01-NOV-2014 13:00', 'DD-MON-YYYY HH24:MI')
  and ash.sample_time <= to_date('08-NOV-2014 17:00', 'DD-MON-YYYY HH24:MI')
-- and ash.event like 'enq: TX - row%'
and blocking_session is not null
group by to_char(ash.sample_time,'MM/DD/YYYY HH24:MI:SS'), ash.event, ash.sql_id, ash.sql_opname, ash.blocking_session, ash.blocking_session_serial#
order by 1) ash_data,
v$sqlarea sqlinfo
where ash_data.sql_id = sqlinfo.sql_id
and sessions_blocked >= 1
order by what_time

...

For example once you have narrowed it down to something interesting looking ( who is blocked / what sql_id / what event ) ... you can use something like this.  I am now looking at any active history information on what the blockers were doing or waiting on.


select * from DBA_HIST_ACTIVE_SESS_HISTORY where ( session_id, session_serial# ) in (
SELECT blocking_session, blocking_session_serial# FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE SAMPLE_TIME > TO_DATE('06-NOV-2014 14:00', 'DD-MON-YYYY HH24:MI')
AND SAMPLE_TIME < TO_DATE('06-NOV-2014 15:00', 'DD-MON-YYYY HH24:MI')
and event like 'enq: TX - row%'
and sql_id = '0kbzgn17vbfc5' )
and SAMPLE_TIME > TO_DATE('06-NOV-2014 14:30', 'DD-MON-YYYY HH24:MI')
AND SAMPLE_TIME < TO_DATE('06-NOV-2014 15:00', 'DD-MON-YYYY HH24:MI')
order by sample_time
Categories: DBA Blogs

StatsPack and AWR Reports -- Bits and Pieces -- 3

Hemant K Chitale - Sat, 2014-11-08 09:48
This is the third post in this series.
Post 1 is here
Post 2 is here

Note : Some figures / details may be slightly changed / masked to hide the real source.

Identifying Unusual Events / Behaviours / Applications

Here I find a few "unusual" events and mark them out.

Extract A : 11.2 AWR

Snap IdSnap TimeSessionsCursors/SessionBegin Snap:.... 03:00:57107.7End Snap:.... 07:00:07114.9Elapsed:239.17 (mins)DB Time:22.61 (mins)
Top 5 Timed Foreground Events

    EventWaitsTime(s)Avg wait (ms)% DB timeWait ClassDB CPU1,33298.16SQL*Net more data to client49,7012001.50NetworkSQL*Net more data from client213,915500.34Networkdb file scattered read1,159110.08User I/Odb file sequential read7,547100.07User I/O
    The two "SQL*Net more data" sets of waits are the unusual events.The Time on SQL*Net more data to/from client is negligible isn't it ?  So, should I be concerned ?  Over a 4 hour period, only 20seconds were on "SQL*Net more data to client".  Time on "SQL*Net more data from client" is much lower at a total time of 5seconds only.  So "Time based" tuning would ignore these two waits.Foreground Wait EventsEventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% DB timeSQL*Net more data to client49,701020037.781.50SQL*Net more data from client213,915050162.620.34db file scattered read1,1580110.880.08db file sequential read7,5500105.740.07






    SQL*Net message to client652,102000495.890.04













    SQL*Net message from client652,1020183,327281495.89
    Not that Oracle treats "SQL*Net message from client" as an idle wait so the 183,327seconds of wait time do NOT appear in the Top 5 Timed Foreground Events list.

    I would draw attention to the high number of "more data from client" waits and the correlation with the "message from client" waits.  Either extremely large SQL statements or PLSQL blocks are being submitted very frequently or row inserts with very large array sizes (number of rows per insert) are being received.  In this case, further investigation reveals an ETL loader that does bulk inserts of a number of rows per array.  If we need tuning, tuning the SDU may help.
    Similarly the "more data to client" indicates large data sets are being returned.  The numbers of columns and rows per every "send" are high.
    Instance Activity Stats (from the same AWR)StatisticTotalper Secondper Trans


    Requests to/from client654,73845.63497.90



    SQL*Net roundtrips to/from client654,74045.63497.91



    bytes received via SQL*Net from client1,793,072,463124,950.541,363,553.20bytes sent via SQL*Net to client552,048,24738,469.57419,808.55



    logical read bytes from cache762,514,227,20053,135,924.61579,858,727.91



    physical read total bytes8,772,479,488611,311.626,671,087.06


    physical write total bytes25,334,243,3281,765,420.7619,265,584.28



    redo size6,373,204,848444,117.794,846,543.61




    1.793billion bytes received in 654K SQL*Net trips is 2741bytes per trip received at 45 messages per second.  Given that it is still only 2,741bytes per trip, possibly the array size could also be tuned with the SDU and TDU.
    So, this is an AWR that doesn't call for tuning but reveals information about how the database is being used.  Large number of rows (large number of columns) being inserted and retrieved in each call.  The Performance Analyst needs to be aware of the nature of the "application" --- here it is not OLTP users but an ETL job that is the "application".  Although the database had more than a hundred sessions a very small number of sessions (possibly 4 ?) were active doing ETL and checking the status of ETL tables during this window.

    Would you care to analyze the other statistics I've listed -- bytes read and bytes written ?



    Extract B : 10.2 AWR
    Snap IdSnap TimeSessionsCursors/SessionBegin Snap:
     00:00:072284.5End Snap:
     23:00:062324.3Elapsed:1,379.97 (mins)DB Time:11,543.24 (mins)
    I know.  It is really bad and most likely meaningless to get an AWR for a 24hour range.  (I'll not go into the details about why the AWR is for 24hours -- save that I did NOT ask for a 24hour AWR report).
    Top 5 Timed Events
    EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time258,10137.3db file sequential read62,150,655208,148330.1User I/Odb file scattered read28,242,529141,638520.4User I/ORMAN backup & recovery I/O1,597,42137,137235.4System I/Oenq: TX - row lock contention22,27634,9421,5695.0ApplicationThe RMAN Backup load is expected.  (Why ? Given a 24hour report, I expect RMAN to have run at least once during the day).For performance tuning, I would look at the "db file ... read" events and identify and analyze SQL statements and the schema.
    What is the "unusual" event here ?  It is the "enq: TX - row lock contention".  Over a period of 1,380minutes, there were 22,276 Row-Lock Contention waits.  Actually, this application does not have the same load throughout the 23hours.  Most likely, it had load for 15hours only.  So, we had 22,276 Row-Lock Contention waits over 15hours.  That translates to 1,485 waits per hour or one Row-Lock Contention wait every 2.5seconds.  Now, that is a very high frequency.  Either users are locking each other out for a very short while (1.569seconds per wait on average) or there is/are one or more jobs that run at a very high frequency and update a common "reference" table.  I won't reveal my findings here but analysis of the SQL statements indicates what the "problem" is.
    Now, should "Time based performance tuning" be worried about the 5% of time lost on these waits ?  Probably not.  But they do indicate something peculiar in the design of this system.  There are less than 250 user sessions in this OLTP database but there is/are one or more jobs that is/are locking itself every 2.5seconds -- so there is some point of serialisation occurring.  Is that job also accounting for CPU time or 'db file read' time ?  That needs further analysis.
    Both these cases show how a Performance Analyst needs to know how the database is being used.  What sort of jobs are submitted, besides OLTP users ?
    ...


    Categories: DBA Blogs

    Securing Oracle GoldenGate Database Login in a Credential Store

    DBASolved - Fri, 2014-11-07 17:44

    Security is always a big deal.  In setting up Oracle GoldenGate the capture (extract) and apply (replicat) parameter files need to be configured to log in to the database which they will perform operations.  In order to do this the Oracle GoldenGate User name and password need to be provided in the parameter files.  Example 1 shows how the database login is traditionally done in a extract or replicat parameter file.

    Example 1:

    --Oracle Login
    USERID ggate, PASSWORD ggate
    

    To make this process login information more secure, we can create a userid alias that the extract or replicat process can use to log into the database.  In order to create a login alias, a credential store needs to be create.  Below are the steps to create the credential store and associated aliases.

    After logging into the GoldenGate Service Command Interface (GGSCI), a credential store needs to be created.  By default the credential store will be kept in the “dircrd” directory undert the $OGG_HOME.

    Create the credential store:

    
    
    GGSCI (db12cgg.acme.com) 1> add credentialstore
    
    
    Credential store created in ./dircrd/.
    
    

    With the credential store created, now an alias can be created for the gguser.

    
    
    GGSCI (db12cgg.acme.com) 2> alter credentialstore add user ggate, password ggate alias aggate
    
    
    Credential store in ./dircrd/ altered.
    
    

    The extract or replicat parameter files need to be updated to use the new alias.  Once the update is done the associated process needs to be restarted.

    
    --Oracle Login
    
    USERIDALIAS aggate
    
    

    After restarting the process, the Oracle GoldenGate login is secure.

    Note: If the password for the Oracle GoldenGate User changes, the alias in the credential store will need to be updated.

    Enjoy!

    about.me: http://about.me/dbasolved


    Filed under: Golden Gate
    Categories: DBA Blogs

    Partner Webcast – Oracle R Enterprise: Bringing the power of R to the enterprise

    For those of you just getting interested in Big “Data” and/or “Advanced Analytics”, the addition of R through Oracle R Enterprise could leave them wondering "What is R?" R is an...

    We share our skills to maximize your revenue!
    Categories: DBA Blogs

    SQL Translation Framework in Oracle Database 12c

    One of the new Oracle Database 12c features is the SQL Translation Framework. A key part of migrating non-Oracle databases to Oracle Database involves the conversion of non-Oracle SQL statements...

    We share our skills to maximize your revenue!
    Categories: DBA Blogs

    setting pctfree to 0 for regular indexes ... ( not familiar with this theory ) ... help!?

    Grumpy old DBA - Fri, 2014-11-07 12:31
    One of my coworkers has this theory ( habit ) of creating new regular indexes on regular table columns.  Not compressed indexes or anything special.

    He is currently believing ( back from somewhere / not sure of the origin ) that "if a table only gets inserts and deletes" that any indexes created on that table should be set with pctfree set to 0.

    I do not agree.  In my opinion one should stick with the tried and true default values here.  I also am skeptical of this belief.

    It seems to me like a very small amount of potential tangible gain in space usage while causing potential problems down the road.

    Even if this theory is somehow true ( don't think so ) ... what happens in the future when something changes in the application and what one was initially told ( only inserts and deletes ) changes.

    Anyone seen / heard /debunked this theory?  Any idea of where it comes from?

    Thanks John   
    Categories: DBA Blogs

    Watch: The Most Underrated Features of SQL Server 2014 — Part 3

    Pythian Group - Fri, 2014-11-07 10:02

    Since its release back in April, SQL Server experts across the globe are becoming familiar with the top features in Microsoft SQL Server 2014—the In-Memory OLTP engine, the AlwaysOn enhancements, and more. But we couldn’t help but notice that there are a few features that aren’t getting the same attention. Warner Chaves, a Microsoft Certified Master and SQL Server Principal Consultant at Pythian has filmed a video series sharing the most underrated features of SQL Server 2014.

    In his final video in the series, Warner discusses SQL Server 2014’s parallel implementation of the SELECT INTO statement. In previous versions of SQL Server, it was always a one-threaded operation, using only one CPU—the new feature however, uses multiple threads.“This makes the operation a lot faster and a lot more efficient.” Warner says. Learn common use cases and how to enable the new feature by watching his video The Most Underrated Features of SQL Server 2014 — Part 3 down below.

    Watch the rest of the series here:

     

    Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise.

     

    Categories: DBA Blogs

    Log Buffer #396, A Carnival of the Vanities for DBAs

    Pythian Group - Fri, 2014-11-07 08:52

    This Log Buffer Edition lists down some of valuable blog posts from across the arenas of Oracle, SQL Server, and MySQL.

    Oracle:

    OOW Recap: Industrial Innovation with IoT, enabled by Oracle Fusion Middleware and JD Edwards.

    PeopleSoft HCM 9.2 Update Image 9 is Now Available! – Simple and Intuitive User Interface on Multiple Devices.

    API Catalog (OAC) allows organizations to easily build a catalog of their APIs to provide visibility to those APIs for application development.

    Oracle Announces Oracle API Catalog to Simplify API Management.

    Tools For Generating Consistent Loads.

    SQL Server:

    Is Database Mirroring Causing The Transaction Log To Fill Up?

    Free SQL in the City Seminar Washington DC – Dec 5, 2014.

    Stop Relying on Cloud File Stores as a Backup Strategy.

    Stairway to SQL Server Indexes: Level 1, Introduction to Indexes.

    SSRS – Report for Stored Procedure with Parameters.

    MySQL:

    Can you use the binary logs to undo a DELETE? Maybe, if you’re using ROW format logging. The difference between a delete and an insert event could be just one byte – the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT.

    Ubuntu 14.10 LAMP server tutorial with Apache 2, PHP 5 and MySQL (MariaDB).

    Resizing the InnoDB Buffer Pool Online.

    Inside Distributed Architectures: Seeing In-Flight Requests And Blockers in Realtime.

    Stopping a Runaway Rollback due to Failing Mass Import or ALTER TABLE.

    Categories: DBA Blogs

    Partner Webcast – Oracle SOA 12c: BPM 12c integration with OEP 12c

    Increased integration complexity may seem inevitable as organizations are suddenly faced with the requirement to support new integration challenges, such as Cloud, Mobile and Internet of Things....

    We share our skills to maximize your revenue!
    Categories: DBA Blogs

    Pythian at LISA14

    Pythian Group - Wed, 2014-11-05 10:27

    Pythian is a sponsor at the LISA conference this year, where we’ll be participating in a panel discussion, invited talk, and a birds-of-a-feather session.

    Bill Fraser, Principal Consultant in Pythian’s SRE practice notes that this year is different for Pythian. “While members of our team have attended LISA in the past, this marks the second year Pythian will be a sponsor for the event, and the first time we have been accepted to speak.” Bill will be speaking at one of the birds-of-a-feather sessions alongside Pierig Le Saux, another Principal Consultant in Pythian’s SRE practice.

    “One of the longest running technical conferences of its kind (this will be the 28th incarnation), the LISA conference is an opportunity to meet, learn from, and network with some of the most respected technical leaders and researchers in the industry,” Bill says. “The conference program features talks, panels, and tutorials on the topics of DevOps, monitoring and metrics, and security, and provides the attendee with an opportunity to learn about the newest tools and emerging technologies in the field.”

    “For Pythian, the conference provides us with an opportunity to give back to the community, by showing our support of the LISA conference and USENIX organization, and allowing us to share the experiences of members of our team. We look forward to seeing you there. Please stick around after our talks and introduce yourself, and / or stop by our booth and say hi!”

     

    Birds-of-a-Feather Session featuring Bill Fraser and Pierig Le Saux
    Wednesday November 12, 2014 — 7:00-8:00 PM
    Grand Ballroom A

    Bill Fraser and Pierig, Principal Consultants for the SRE practice at Pythian, will be discussing what it really means to be practicing Infrastructure as Code. They will provide examples of concepts, tools and workflows, and encourage attendees to engage in a dialog of how their day-to-day work is changing.

     

    Remote Work panel featuring Bill Lincoln
    Thursday November 13, 2014 — 11:00 AM-12:30 PM

    Bill Lincoln, Service Delivery Manager and business advocate at Pythian, will be participating in the Remote Work panel. This panel will focus on how companies handle the remote workers in Ops roles. “When you’re looking for the best talent in the world, remote work is a requirement—not an option,” Bill says. “Finding ways to effectively manage large, remote, teams across the globe is a challenge for any organization and Pythian has built its entire business around this model.” He will be presenting alongside folks from DigitalOcean, TeamSnap, and Etsy.

     

    Invited Talk presented by Chris Stankaitis
    Friday November 14, 2014 — 9:00-9:45 AM

    Chris Stankaitis, Team Lead for the Enterprise Infrastructure Services practice at Pythian, will be presenting an invited talk called Embracing Checklists as a Tool for Human Reliability.

    “A pilot cannot fly a plane, and a surgeon cannot cut into a person without first going through a checklist.” Chris says. “These are some of the most well educated and highly skilled people in the world, and they have embraced the value of checklists as a tool that can dramatically reduce human error.”

     

    EXPO Hall
    Wednesday November 12, 12:00 -7:00 PM
    Thursday November 13, 10:00 AM-2:00 PM

    The LISA EXPO hall opens at noon on Wednesday, so be sure to stop by Pythian’s booth #204 (we’ll be in good company, right next to Google!) You could win a Sonos Play: 1, and all you have to do is take a selfie. Learn the full contest details at our booth, and follow us on Twitter @Pythian to stay updated!

     

    Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Data Infrastructure expertise.

     

    Categories: DBA Blogs

    Watch: The Most Underrated Features of SQL Server 2014 — Part 2

    Pythian Group - Wed, 2014-11-05 10:09

    Since its release back in April, SQL Server experts across the globe are becoming familiar with the top features in Microsoft SQL Server 2014—the In-Memory OLTP engine, the AlwaysOn enhancements, and more. But we couldn’t help but notice that there are a few features that aren’t getting the same attention. Warner Chaves, a Microsoft Certified Master and SQL Server Principal Consultant at Pythian has filmed a video series sharing the most underrated features of SQL Server 2014.

    In the second video in his series, Warner discusses the significance of the new partition operations, making online index rebuilds and incremental statistics much more efficient. “For many clients, it was really hard to find maintenance windows that were big enough to actually rebuild the entire set [of partition tables] when they had fragmentation issues,” said Warner. “2014 now implements online index rebuild on a partition level.” Learn how incremental statistics became more efficient, and when you can start using the new partition operations by watching his video The Most Underrated Features of SQL Server 2014 — Part 2  down below.

    Watch the rest of the series here:

     

    Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise.

     

    Categories: DBA Blogs

    Oracle DBA job in Tempe, Arizona

    Bobby Durrett's DBA Blog - Tue, 2014-11-04 10:16

    We still have a position open on our Oracle database team here in Tempe, Arizona.  Here is the link with an updated job description: url

    We have a great team and would love to have a new member to join us.

    -Bobby

    Categories: DBA Blogs

    Watch: The Most Underrated Features of SQL Server 2014 — Part 1

    Pythian Group - Mon, 2014-11-03 09:59

    Since its release back in April, SQL Server experts across the globe are becoming familiar with the top features in Microsoft SQL Server 2014—the In-Memory OLTP engine, the AlwaysOn enhancements, and more. But we couldn’t help but notice that there are a few features that aren’t getting the same attention. Warner Chaves, a Microsoft Certified Master and SQL Server Principal Consultant at Pythian has filmed a video series sharing the most underrated features of SQL Server 2014.

    In his first video, Warner emphasizes the importance of Microsoft’s new feature, delayed durability. “Why do we care about delayed durability?” Warner asks. “The reason we care about it is because waits on the transaction log get a lot shorter. It’s a very, very small change on the database that makes a big impact.” Learn about the specific use cases for delayed durability, and how to implement it in Warner’s video The Most Underrated Features of SQL Server 2014 — Part 1 down below.

    What are your thoughts? Are there any features that you feel were quietly announced, but deserve more recognition? Let us know.

    Watch the rest of the series here:

     

    Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise.

     

    Categories: DBA Blogs

    Useful Carlos Sierra post about queries changing performance

    Bobby Durrett's DBA Blog - Mon, 2014-11-03 09:26

    Saw an interesting post related to work I am doing locking in plans and finding plans that have changed: Carlos Sierra post on queries with changing performance.

    Our DBA team uses versions of my sqlstat.sql script to find queries whose elapsed time change and we use a script from Carlos Sierra’s SQLT collection to force the execution plan back to an efficient plan that the optimizer choose in the past.  Carlos Sierra’s new post includes scripts that resemble my sqlstat script but with more functionality including an interesting way of helping people figure out which queries are now running slower.  At this point we just look at the top queries that are running and run sqlstat.sql to see if they have run with less elapsed time in the past.  We eyeball it and usually it is obvious because the bad plan takes 100 times longer than the good one.  But the newly posted Carlos Sierra script seems to use some math to help figure out whether the elapsed time is really bad enough for us to consider it a problem.

    I want to set up a monitoring script that pages us when a plan changes for the worse so we can decide whether to intervene and force on older plan to improve performance.  I have a simple script running on one of our databases but, as I expected, it is returning a lot of false positives.  So, I’m hoping to have some time this week to improve that script so that we get fewer false alarms and I’m going to take a close look at Carlos Sierra’s newly posted script for ideas.

    – Bobby

     




    Categories: DBA Blogs

    Digital Learning: LVC customer quotes

    The Oracle Instructor - Mon, 2014-11-03 03:34

    LVC

    Here’s a collection of customer quotes as a follow-up to my last post about the importance of attitude towards Live Virtual Classes (LVCs). They are from courses that I have taught personally this year with an average delivery score of about 96%:

    Oracle Database 12c: Managing Multitenant Architecture

    Oracle Grid Infrastructure 11g: Manage Clusterware and ASM

    Oracle Database 12c: New Features for Administrators

    Exadata Database Machine Administration Workshop

    Oracle 11g: RAC and Grid Infrastructure Administration Accelerated

    “My first experience of an LVC. Pleased to say it was very positive. Introduction and start on Monday morning was smooth. I would recommend to my colleagues.”

    Always important to make a good first impression!

    “The whole LVC package just worked. From the comfort of my own environment with a great instructor makes for happy learning :)”

    And that is exactly what we strive to deliver.

    “Both, host and producer were very professional and guided the students through the course.”

    An LVC producer takes care for all technical aspects apart from the course itself, like access to the learning platform. The instructor appears as “host” on the learning platform.

    “Instructor professionally answered students’ questions and kept up a positive mood in the community!”

    LVCs can be funny too :-)

    “I appreciate the way how the course was presented. Very well controlled time, organization of presentation, exercises. Interaction with us was great. Always ready to answer a question, give an examples to difficult topic, illustrating topics.”

    So much about allegedly missing interaction in LVCs.

    “I work few years on RAC databases, my knowledge was not so clear regarding some topic on RAC and Grid after completing this training I’m sure that I will handle our RAC and Grid environment differently and for sure will have positive impact in our production environment. Great thank!”

    You cannot top that with a classroom course either :-)

    “LVC is offering great and flexible way to gain knowledge without travel or hotels etc.” “LVCs reduce travel costs and help the students to manage their time on their own, i.e. to join the classes from home and focus on the presented content.”

    Trust me, I didn’t make up the last two although they may sound like your manager talking – or mine, for that matter ;-)


    Tagged: Digital Learning, LVC
    Categories: DBA Blogs

    Does Increasing An Oracle Background Process OS Priority Improve Performance?

    Does Increasing An Oracle Background Process OS Priority Improve Performance?
    Does increasing an Oracle Database background process operating system priority improve performance? As you might expect, the answer is, "It depends."

    In this posting I will explain the results of an experiment where I increase the Oracle Database 12c log writer background processes operating system priority.

    In my experiment I created a clear CPU bottleneck and the top wait event was log file parallel write. I gathered some data. Next I increased all the log writer background process priorities. Again, I gathered some data and then I analyzed the two data sets. The results were disappointing, not surprising, but a fundamental rule in performance tuning was demonstrated and reinforced.

    You can download the "analysis pack" which contains the raw experimental data, histogram, statistical R results and the R statical package commands HERE. You can download total time delta reporting script (ttpctx.sql) I show below, which is contained within my OraPub System Monitor (OSM) Toolkit HERE.

    Before I could gather some data, I needed to create the appropriate system load, the data gather scripts and the data analysis scripts. Here's a quick overview of each.

    Increasing The LGWR Processes PriorityIf you are not familiar with changing Oracle Database background OS process priority, I blogged about how to do this HERE.

    My experiment consisted of creating two situations and statistically comparing them to see if increasing the LGWR background process OS priority increased performance. The only difference in the "two situations" was the instance parameter, _high_priority_processes. For the "normal priority" part of the experiment, the default "LMS*|VKTM" was used. For the "high priority" part of the experiment the parameter was changed to "LMS*|VKTM|LG*". The "LG*" caused the increase in the Linux OS priority of all log writer processes from the default 19 to 41.

    Ready for some version specifics? Oracle continues to make OS priority related instance parameter changes... even within 12c releases. Since this experiment was done with Oracle Database version 12.1.0.1.0 the parameter default was "LMS*|VKTM" not "LMS*" as with version 12.1.0.2.0. Also, in 12.1.0.2.0 VKTM is placed into a new parameter, _highest_priority_processes.

    Generating The DML With CPU Bottleneck Load
    To generate the DML workload, I used my OP Load Generator. You can download it HERE. It's quick, easy and I'm familiar with it. I kept increasing the number of DML processes until the CPU utilization was pegged at 100%. While the database server was clearly out of CPU power, the log writer background processes were rarely seen as the top CPU consuming process. I used the "top" program to monitor the process activity. The top CPU consuming processes were almost always the Oracle server/foreground/shadow processes. As I'll explain in the Results section below, this has significant ramifications on the results.

    Oracle Time Based Analysis Summary
    The data collection script was similar to the one I used back in 2012 when collecting data regarding Oracle's commit write facility. Essentially, I collected multiple three minute samples of the delta elapsed time, user commits, total non-idle wait time and CPU consumption. In both the normal and high priority runs, around 99% of the database time was CPU consumption and 1% of the time Oracle non-idle wait time. Also the top wait event (~70%) was log file parallel write (display name: log file redo write).

    If you want master an Oracle Time Based Analysis, check out my online seminar, Tuning Oracle Using An AWR Report. It will teach you how to using an AWR report to optimize Oracle performance so users will feel the difference.

    The OraPub System Monitor Toolkit script ttpctx.sql report below was taken during the "normal priority" log writer load.

    SQL> @ttpctx

    Database: prod35 16-MAY-14 06:07pm
    Report: ttpctx.sql OSM by OraPub, Inc. Page 1
    Total Time Activity (39 sec interval)

    Avg Time Time Wait
    Time Component % TT % WT Waited (ms) (sec) Count(k)
    ------------------------------------- ------- ------- ----------- ----------- --------
    CPU consumption: Oracle SP + BG procs 98.91 0.00 0.000 238.716 0
    log file redo write 0.73 67.56 8.082 1.770 0
    control file parallel write 0.18 16.41 33.077 0.430 0
    target log write size 0.05 4.20 6.111 0.110 0
    oracle thread bootstrap 0.03 3.05 40.000 0.080 0
    os thread creation 0.02 1.53 20.000 0.040 0
    commit: log file sync 0.01 0.76 10.000 0.020 0
    enq: CR - block range reuse ckpt 0.01 0.76 20.000 0.020 0
    Disk file operations I/O 0.00 0.00 0.000 0.000 0

    The OSM report below was taken during the "high priority" log writer load.

    SQL> @ttpctx

    Database: prod35 16-MAY-14 09:25pm
    Report: ttpctx.sql OSM by OraPub, Inc. Page 1
    Total Time Activity (41 sec interval)

    Avg Time Time Wait
    Time Component % TT % WT Waited (ms) (sec) Count(k)
    ------------------------------------- ------- ------- ----------- ----------- --------
    CPU consumption: Oracle SP + BG procs 98.92 0.00 0.000 238.733 0
    log file redo write 0.83 77.01 8.272 2.010 0
    control file parallel write 0.08 7.28 14.615 0.190 0
    target log write size 0.05 4.98 5.909 0.130 0
    oracle thread bootstrap 0.03 3.07 40.000 0.080 0
    os thread creation 0.02 1.92 25.000 0.050 0
    commit: log file sync 0.01 0.77 10.000 0.020 0
    enq: CR - block range reuse ckpt 0.01 0.77 20.000 0.020 0
    enq: RO - fast object reuse 0.00 0.38 10.000 0.010 0


    Data Collection
    For the normal priority load 27 three minute samples where collected. For the high priority situation there were 30 three minute samples collected. (I forgot why there was only 27 samples collected for the normal priority.)  I collected the elapsed time, total non-idle wait time, total CPU consumption (v$sys_time_model: db_cpu + background cpu time) and total user commits.

    In this experiment more user commits processed per second means better performance.


    Experimental Results
    I used the free statistics package "R" (www.r-project.org) to analyze the data. I demonstrate how to get, install and use "R" in my online video seminar, Using Skewed Data To Your Advantage.

    With the normal log writer process priority, an average of 984.5 commits/sec and a median of 983.0 commits/sec occurred. With the LG* high process priority, an average of 993.6 commits/sec and a median of 991.0 commits/sec occurred. While the "high priority" situation was able to process more commits per second, is this statistically significant?

    The red "smoothed" histogram is the normal priority situation and the blue smoothed histogram is when the log writers were set to the higher priority. The more separated the two histograms the more "different" the sample sets, the more likely there is a statistically significant difference and the more likely a user would feel the difference. Looking at the above histograms plot, there does not appear to be a real difference. But let's do a proper significance test!

    Because both sample sets are normally distributed (details are in the Analysis Pack), I could use a simple t-test. R produced a p-value of 0.04451. To be statistically "different" I want the p-value to be less than 0.05 and it is. What does this mean?

    While statistically and numerically the commit rates are different, I wouldn't expect any special performance tuning award! In fact, the hassles with cycling a production instance and setting underscore/hidden parameters would make it very unlikely I would increase the OS priority of the log writer background processes. I want to see a big performance difference.

    To Summarize... What I Learned... Again
    This situation is a perfect example of focusing on the wrong thing! While there is a clear operating system CPU bottleneck and the top wait event is about redo, the log writers are not suffering from a want/need of CPU resources. I suspect the server processes want more CPU resources, but they are NOT the processes we increased their OS priority.

    If the log writers were suffering from a lack of CPU resources and fighting for CPU resources, I would expect to see them consuming CPU resources along with the Oracle server processes. And I would definitely expect to see them near the top of the "top" process monitor... especially when their priority has been increased!

    Because of this "misguided" tuning effort, this experiment does not build a case for or against changing the log writer priority. What it reinforces is in our quest to optimize performance, make sure we focus on the right thing.

    As a side note, this is a great statistical analysis example for two reasons. First, our samples sets look similar, but statistically they are not. Second, while they are statistically different, the performance impact will not be very different. And my guess is the users won't feel a thing... except frustration and anger.

    To Super Summarize
    When considering increasing a background process's operating system priority, make sure the process is in need of CPU and is not able to get it. In this situation, the DBA could have been mislead by the CPU bottleneck. But upon closer inspection of the log writers from an operating system perspective and knowing the wait event "log file parallel write" is probably more about IO than CPU (Oracle perspective) it would be unlikely that increasing the log writer processes OS priority would help increase the commits per second.

    Thanks for reading!

    Craig.












    Categories: DBA Blogs

    StatsPack and AWR Reports -- Bits and Pieces -- 2

    Hemant K Chitale - Sat, 2014-11-01 08:52
    This is the second post in a series on reading StatsPack and AWR reports.
    (The first is available here)


    Comparing Reports :

    Here are two 9.2 StatsPack extracts from one database:

    Extract A  : 9.2 StatsPack
    Load Profile
    ~~~~~~~~~~~~ Per Second Per Transaction
    --------------- ---------------
    Redo size: 56,031.63 3,084.68
    Logical reads: 68,286.24 3,759.32
    Block changes: 314.88 17.33
    Physical reads: 842.92 46.40
    Physical writes: 134.76 7.42
    User calls: 271.32 14.94
    Parses: 146.46 8.06
    Hard parses: 7.37 0.41
    Sorts: 93.83 5.17
    Logons: 0.33 0.02
    Executes: 296.70 16.33
    Transactions: 18.16

    Extract B : 9.2 StatsPack
    Load Profile
    ~~~~~~~~~~~~ Per Second Per Transaction
    --------------- ---------------
    Redo size: 89,615.76 2,960.48
    Logical reads: 210,302.81 6,947.42
    Block changes: 541.83 17.90
    Physical reads: 1,465.04 48.40
    Physical writes: 161.68 5.34
    User calls: 213.82 7.06
    Parses: 125.28 4.14
    Hard parses: 6.13 0.20
    Sorts: 104.31 3.45
    Logons: 0.35 0.01
    Executes: 664.81 21.96
    Transactions: 30.27

    Extract B shows a higher volume of activity -- Transactions per second, Redo Size per second, Logical Reads per second.
    Actually, although these are both from the same database but for two different time-windows on different dates.  The fact that the time-windows aren't the same make the comparisons difficult.  Extract A is for an 11-hour window on day 1 and Extract B is for a 6-hour window on day 6.  The two windows aren't strictly comparable.  You have to be very careful when comparing StatsPack or AWR reports, even when from the same database if they don't cover comparable time-windows with similar loads.  The first Report includes non-peak hours, the second is for Peak hours only. The level of activity averaged out over 11hours that include non-peak hours isn't strictly comparable with a 6hour peak window.

    Extract C : 10.2 AWR
    Load Profile
    Per SecondPer TransactionRedo size: 520,776.15 50,948.36Logical reads: 353,525.71 34,585.98Block changes: 1,854.93 181.47Physical reads: 14,285.23 1,397.55Physical writes: 295.84 28.94User calls: 1,265.14 123.77Parses: 822.64 80.48Hard parses: 15.40 1.51Sorts: 168.09 16.44Logons: 0.16 0.02Executes: 1,040.31 101.78Transactions: 10.22 
    Extract D : 10.2 AWRLoad Profile
    Per SecondPer TransactionRedo size: 517,862.01 54,681.39Logical reads: 288,341.09 30,446.12Block changes: 1,879.27 198.43Physical reads: 12,820.96 1,353.77Physical writes: 323.90 34.20User calls: 1,115.78 117.82Parses: 719.39 75.96Hard parses: 17.28 1.82Sorts: 95.74 10.11Logons: 0.15 0.02Executes: 935.33 98.76Transactions: 9.47 
    Extracts C and D are from the same database for the same time-window on 2 consecutive workdays.  They are comparable.  A difference of 10% for some statistic may not be significant.  Understand that every business, every activity, every system will have some "normal" fluctuations day-to-day.

    .
    .
    .



    Categories: DBA Blogs

    The First PASS Summit Bloggers’ Meetup

    Pythian Group - Fri, 2014-10-31 13:02

    We are stoked to announce the first ever PASS Summit Bloggers’ Meetup!

    What: PASS Summit Bloggers’ Meetup 2014
    When: Thursday, November 6th, 5pm – 7pm
    Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109
    How: Please comment with “COUNT ME IN” if coming — we need to know attendance numbers.

    We’re excited to meet old friends, and make new ones in Seattle this year. Pythian will sponsor munchies and drinks. There will be a networking contest with some cool prizes, plus everyone will get their very own PASS Summit Bloggers Meetup shirt! Meetups are a great way to make new friends in the community, so come on out — all bloggers are welcome!

    If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Make sure you comment here if you are attending so that we have enough room, food, and drinks.

    The location is perfect to get ready for the appreciation event — minutes walk from EMP Museum! Snacks and drinks before the big event and mingle with fellow bloggers. What can be better?

    Of course, do not forget to blog and tweet about this year’s bloggers meetup using #Summit14 #sqlpass. See you there!

    Categories: DBA Blogs

    Speaking at the Spanish Virtual PASS Chapter

    Pythian Group - Fri, 2014-10-31 10:09

    Title : Recuperación de desastres y soluciones de alta disponibilidad con SQL Server
    Event link: http://globalspanish.sqlpass.org/Inicio.aspx?EventID=1846
    Event description: “Esta presentación presenta las soluciones de recuperacion de desastres (Disaster Recovery) y alta disponibilidad (High Availability) con SQL Server y ofrece escenarios creativos por usar las soluciones para reportages (Reporting), BI y almacen de datos (Datawarehouse). ”

    Please feel free to register!

    Categories: DBA Blogs