Montag, 21. Oktober 2019

Oracle patches - some basics and good-to-know information

Oracle Patches can occur very complicated and confusing. Most of the time they are zip files with strange and sometimes inconsistent naming convention.
As these patches are processed by a program called opatch (which also can be obtained as a patch - but it does not follow the same structure as regular patches).

Back to basics: In patches words like product, platform or language are used. They are all IDs and their "data dictionary" can easily be obtained. There are these links:
products https://updates.oracle.com/Orion/Services/metadata?table=aru_products
releases https://updates.oracle.com/Orion/Services/metadata?table=aru_releases
platforms https://updates.oracle.com/Orion/Services/metadata?table=aru_platforms
languages https://updates.oracle.com/Orion/Services/metadata?table=aru_languages
product_groups https://updates.oracle.com/Orion/Services/metadata?table=aru_product_groups
product_releases https://updates.oracle.com/Orion/Services/metadata?table=aru_product_releases
component_releases https://updates.oracle.com/Orion/Services/metadata?table=aru_component_releases
aru targets https://updates.oracle.com/Orion/Services/metadata?table=aru_targets
components https://updates.oracle.com/Orion/Services/components?ctype=product,release,platform,platform_version,wildcard-release,config-group,release-component,code_list
certifications https://updates.oracle.com/Orion/Services/components?ctype=certification
patch recommendationshttps://updates.oracle.com/Orion/Services/recommended_patches
ARU stands for Automated Release Update.

All these files can also obtained in a single zip file: https://updates.oracle.com/download/em_catalog.zip
The name of the zip file also shows it's origin purpose: provide Enterprise Managers recommended patches, download etc. with sufficient information. Some parts like platforms and languages are quite stable, others like releases and  recommendations, change regularly.

recommendations brings uns closer to real patches. it starts with
<results md5_sum="212e2bc3336d1aa3e41074e796201aa9">
  <generated_date in_epoch_ms="1571568490000">2019-10-20 10:48:10</generated_date>
  <patches md5_sum="d58c2ef61a596dd7bdef85b61cd06861">

followed by entries for each patch like
<patch uid="3d4a1929407b7e05430e2fd5bbc18481" has_prereqs="n" has_postreqs="n" is_system_patch="n">
<bug>
        <number>30049606</number>
        <abstract><![CDATA[WINDOWS DB BUNDLE PATCH 12.1.0.2.191015]]></abstract>
      </bug>
      <name>30049606</name>
      <type>Patch</type>
      <psu_bundle>Oracle 12.1.0.2.191015 for Windows</psu_bundle>
      <status>Available</status>
      <access id="m">Open access</access>
      <url>
        <patch_readme host="https://updates.oracle.com"><![CDATA[/Orion/Services/download?type=readme&aru=23166562]]></patch_readme>
        <patch_details><![CDATA[/download/30049606.html]]></patch_details>
      </url>
      <request_id>23166562</request_id>
      <urm_components>
        <qparts ctype_id="201">
          <qpart cid="32615" version="Q5"><![CDATA[Oracle Database - Enterprise Edition]]></qpart>
          <qpart cid="42353" version="Q790"><![CDATA[Oracle Server - Standard Edition]]></qpart>
        </qparts>
        <urm_releases ctype_id="5">
          <urm_release cid="86176" version="12.1.0.2.0"><![CDATA[Oracle Database]]></urm_release>
        </urm_releases>
      </urm_components>
      <product id="9480" bugdb_id="5"><![CDATA[Oracle Database Family]]></product>
      <release id="600000000009300" name="12.1.0.2.0" platform_patch_not_required="Y" cc="Y"><![CDATA[Oracle 12.1.0.2.0]]></release>
      <platform id="912" bugdb_id="912"><![CDATA[Microsoft Windows (32-bit)]]></platform>
      <language id="0" iso_code="EN"><![CDATA[American English]]></language>
      <translations_available>No</translations_available>
      <classification id="185">Security</classification>
      <patch_classification id="185">Security</patch_classification>
      <life_cycle id="175">Recommended</life_cycle>
      <support_level id="E">Extended Support</support_level>
      <entitlements>
        <entitlement code="EXS"/>
      </entitlements>
      <target_types>
        <target_type>
          <aru_target><![CDATA[All Database]]></aru_target>
          <em_tag></em_tag>
        </target_type>
      </target_types>
      <fixes_bugs truncated="no">
        <bug>
          <number>12681464</number>
          <abstract><![CDATA[UPDATE STATEMENT  FAILED - ORA7445 [KGGSLDESTROY] ERROR]]></abstract>
        </bug>
        <bug>
          <number>12799826</number>
          <abstract><![CDATA[LOCK TABLE FOR INDEX FAILS WITH ORA-600 [KKBKAUXBLL0]]]></abstract>
        </bug>
... many more
      </fixes_bugs>
      <size>317964815</size>
      <files>
        <file>
          <name>p30049606_121020_WINNT.zip</name>
          <size>317964815</size>
          <download_url host="https://updates.oracle.com"><![CDATA[/Orion/Services/download/p30049606_121020_WINNT.zip?aru=23166562&patch_file=p30049606_121020_WINNT.zip]]></download_url>
          <digest type="SHA-1">784196EE41085E633F1C10C5F12B47717232C3C5</digest>
          <digest type="SHA-256">849D03DCBB4EDCD2F08474371309470E9D3AE172E4BD7296453F31AEAE40E9D7</digest>
        </file>
      </files>
      <updated_date in_epoch_ms="1571299461000">2019-10-17 08:04:21</updated_date>
      <released_date in_epoch_ms="1570628127000">2019-10-09 13:35:27</released_date>
    </patch>        

Many entries in this XML can be interpreted by the information provided in em_catalog.zip. For each patch there is a download link: https://updates.oracle.com/Orion/Services/download/p30049606_121020_WINNT.zip?aru=23166562&patch_file=p30049606_121020_WINNT.zip


As there are many patches which are not mentioned in recommendations, and also for them there is a link to get an equal XML regarding this patch:
https://updates.oracle.com/Orion/Services/search?bug=<patch_number>

Even the URL suggests it is a search for a BUG, it's a search for a PATCH (as a PATCH can contain fixes for multiple BUGs).

If the same information is required in a HTML form, the URL is
https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=<patch_number>

Donnerstag, 10. Oktober 2019

OGB Appreciation Day : manipulating execution plans

Tims call for OGB Appreciation Day 2019 (#ThanksOGB) comes in time this year.

Even unemployed at the moment, I had a chance to help a friend with some problematic SQL statements.

The statements are not that important at all but he possibilities we had to address the problems were great!


First of all an existing execution plan, together with session stats, ASH/AWR and in one case SQLTRACE. None of these sources of information shows all details we required, but all together provided sufficient information to understand the problem.
The probably most comprehensive way to get all these information (except tracing) is sqld360.

Next of course is the system of hints.
There are many discussions if hints are a good and bad thing. It's not me to argue it here.
I appreciate hints exist in Oracle RDBMS as they can be used if arguments in their favor are strong.

Third the possibilities to apply hints to a SQL are huge. Even the statement itself can not be changed, there are stored outlines, SQL Profile, SQL Plan Baselines in SQL Plan Management, SQL Patch, and if some really dirty tricks are required, SQL Translation Framework and other mean tools can be used.

So there are sufficient tools available to improve a statements execution.


The sheer amount of possibilities might be overwhelming or even deterrent. In fact it's not that complicated and results can be achieved in short time.
Writing this, it's always easy to deliver shiny fancy solutions when standing on giants shoulders.
To give some examples, here is a random selection of articles I used during the latest investigations:

Occurence - Jonathan Lewis
Fixing SQL Plans: The hard way – Part 1 - Advait Deo
SQL Profiles - Kerry Osborne
buffer sorts - Jonathan Lewis
Oracle’s OPT_ESTIMATE Hint: Usage Guide - Christo Kutrovsky
Visual SQL Tuning (VST) - Kyle Hailey

I'm grateful for the possibilities we have to fix SQL executions, and I'm also grateful for all those people who share their knowledge, so I can learn from them!