PL/Scope – Did you know that?

Hi All!
I decided to make a serie of posts with really quick notes about some really awesome features we simply don’t use. Today’s one is about PL/Scope.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

PL/Scope

PL/Scope is a tool that gathers information about user defined identifiers at compile time. Collection of PL/Scope data is controlled by the PLSCOPE_SETTINGS parameter, which has a default setting of “IDENTIFIERS:NONE”. To enable collection, switch value to “IDENTIFIERS:ALL”. The data is stored in the SYSAUX tablespace, this space needs to be well planned and managed..

The PL/Scope data is available from the %_IDENTIFIERS views. The following query displays data gathered during the compilation of a test procedure:

COLUMN name FORMAT A30
SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
FROM   user_identifiers
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id;

NAME                           TYPE               USAGE         USAGE_ID       LINE        COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
  TEST_PLSCOPE                 PROCEDURE          DECLARATION          1          1         11
    TEST_PLSCOPE               PROCEDURE          DEFINITION           2          1         11
      P_IN                     FORMAL IN          DECLARATION          3          1         25
      L_VAR                    VARIABLE           DECLARATION          4          2          3
      L_VAR                    VARIABLE           ASSIGNMENT           5          4          3
        P_IN                   FORMAL IN          REFERENCE            6          4         12
      L_VAR                    VARIABLE           ASSIGNMENT           7          6          3
        L_VAR                  VARIABLE           REFERENCE            8          6         12

8 rows selected.

(this script was taken from here)

Some additional information about it: The documentation states that some identifiers will not listed unless the STANDARD package is recompiled after the PLSCOPE_SETTINGS parameter is set properly. However there is some opening community discussions about invalidation of basic procedures not being revalidated again, even on use of urlrp procedure. This way, we don’t recommend to use it directly on Production environment without validating before in an equivalent environment and taking all caution measures as possible.

Hope you enjoy it. Cheers!

ORA-02429 on Drop Tablespace

Ok, so here is the error:

SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

Hmm… Sounds pretty clear, right? So how to workaround it?

This is documented on MOS Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (Doc ID 1918060.1)

So, FIRST make sure this is not a problem for your application.
In case it is not, here is my step by step with some better scripting:

1) Disable the Constrains:

SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||';'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');

2) In case of PKs, you may need to disable it with CASCADE clause:

SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||' cascade;'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');

3) Drop the tablespace:

SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;

Hope it helps,
Cheers!

Oracle SQL: Aggregate List – LISTAGG

Know this command?

I think it’s very useful, but not so often I see that in use… More about it:

It was introduced in Oracle 11g Release 2, as an analytic function to make the life easier fot the PLSQL Dev, specially when working to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

In case WM_CONCAT is in use for this end, be aware that WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, once it was completely replaced by LISTAGG.

More info and examples can be found here: https://oracle-base.com/articles/12c/listagg-function-enhancements-12cr2

Hope you enjoy it. Cheers!

dba_registry_sqlpatch/cdb_registry_sqlpatch Empty after Patch

Hi all!
So, I was checking a new environment and noticed the dba_registry_sqlpatch was empty, when it actually shouldn’t:

SQL> select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;

no rows selected

SQL>

The expected output should be (from another CDB in same home):

 PATCH_ID  PATCH_UID VERSION		   ACTION	   ACTION_TIME								       STATUS	       DESCRIPTION
---------- ---------- -------------------- --------------- --------------------------------------------------------------------------- --------------- ----------------------------------------------------------------------------------------------------
  24917972   20791781 12.1.0.2		   APPLY	   37-APR-17 11.19.49.103261 AM 					       SUCCESS	       Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)
  24732082   20904347 12.1.0.2		   APPLY	   17-APR-17 11.19.49.322985 AM 					       SUCCESS	       DATABASE PATCH SET UPDATE 12.1.0.2.170117
  24917972   20791781 12.1.0.2		   ROLLBACK	 29-NOV-17 08.35.57.888426 PM 					       SUCCESS	       Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)
  26635845   21564421 12.1.0.2		   APPLY	   29-NOV-17 08.35.57.890421 PM 					       SUCCESS	       Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017)
  26713565   21602269 12.1.0.2		   APPLY	   29-NOV-17 08.35.57.956378 PM 					       SUCCESS	       DATABASE PATCH SET UPDATE 12.1.0.2.171017
  27338041   22036385 12.1.0.2		   APPLY	   12-JUN-18 01.45.24.163558 PM 					       SUCCESS	       DATABASE PATCH SET UPDATE 12.1.0.2.180417

The result is basically the same if quering cdb_registry_sqlpatch.

Fist found the MOS dba_registry_sqlpatch or registry$sqlpatch View Is Not Reflecting the Complete Updated Information after Patching (Doc ID 2039738.1).
Problem is that is applies to 12.1 and it is caused by a bug in opatch version 12.1.0.1.6, but OPatch version is 12.2.0.1.8.

$ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.8

If this is a match for your, the proposed solution in that note is:

1. Download and use latest opatch version 12.1.0.1.8. (Patch 6880880)
2. Take the backup & delete the contents of dba_registry_sqlpatch to remove the invalid entries:

    SQL>delete 

3. Re-run the datapatch

But what was my problem then?
Well, after a while noticed the MOS Note Datapatch may skip the application of SQL payload for certain patches included in a given bundle in a RAC environment. (Doc ID 2069046.1).
It includes a PLSQL validation script, by the way. Have a look in case it’s a suspect.

And it was a match to me, seems the client used to had problems with opatchauto in the past and had to run the ‘datapatch -verbose’ manually.

The solution? To run this for every CDB contained in the cluster. The Registry$sqlpatch table is now reporting the correct patch history for all CDBs.

Hope it helps!

3rd GUORS Meetup!

Hey all,

I’ll not be speaking this time, but I’ll be there anyway to see some of our very good local experts talking about Oracle stuff.

The event is going to take place at TECNOPUC – Prédio 96I – Auditório Talento Empreendedor, Porto Alegre/RS – Brazil.

When? November 28th, in a few days!
The timeframe is from 1PM to 6PM BRT.

Confirm you presence here!

Let’s be there and incentive the local community.

Cheers,

Matheus

PLSQL: Which code is taking longer?

So you have a slow process calling several PLSQL Codes, including Procedures, Functions and etc, but don’t know what is taking longer?

Your problems has ended… 

In Oracle 11gR1 was introduced the PL/SQL Hierarchical Profiler to help developers by providing hierarchical profiler data analysis for PL/SQL programs.

From Oracle base:
“The hierarchical profiler consists of the DBMS_HPROF package, which feels similar to the DBMS_PROFILER and DBMS_TRACE packages, and the plshprof command line utility to converts the profile information into HTML format.”

For example, we can set a profiler for procedure proc_example_1 and check in all calls and related statistics:

BEGIN
  DBMS_HPROF.start_profiling (
    location => 'ORACLE_DIR',
    filename => 'prof.txt');

  proc_example_1(p_number => 10);

  DBMS_HPROF.stop_profiling;
END;
/

And to see results, a simple output is:

COLUMN owner FORMAT A20
COLUMN module FORMAT A20
COLUMN type FORMAT A20
COLUMN function FORMAT A25
SELECT symbolid,
       owner,
       module,
       type,
       function
FROM   dbmshp_function_info
WHERE  runid = 1
ORDER BY symbolid;

  SYMBOLID OWNER                MODULE               TYPE                 FUNCTION
---------- -------------------- -------------------- -------------------- -------------------------
         1 TEST                 proc_example_1       PROCEDURE            proc_example_1
         2 TEST                 proc_example_2       PROCEDURE            proc_example_2
         3 TEST                 proc_example_3       PROCEDURE            proc_example_3
         4 SYS                  DBMS_HPROF           PACKAGE BODY         STOP_PROFILING
    5 TEST                 proc_example_3       PROCEDURE            __static_sql_exec_line5

5 rows selected.

Which can also be seen with this nice SQL from Oracle Base (Tim Hall):

SET LINESIZE 500 PAGESIZE 1000
COLUMN name FORMAT A100
SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
       a.subtree_elapsed_time,
       a.function_elapsed_time,
       a.calls
FROM   (SELECT fi.symbolid,
               pci.parentsymid,
               RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
               NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
               NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
               NVL(pci.calls, fi.calls) AS calls
        FROM   dbmshp_function_info fi
               LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
        WHERE  fi.runid = 1
        AND    fi.module != 'DBMS_HPROF') a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL;


NAME                                                SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
--------------------------------------------------- -------------------- --------------------- ----------
TEST.proc_example_1                                                31262                    31          1
  TEST.proc_example_2                                              31231                   133         10
    TEST.proc_example_3                                            31098                  3241        100
      TEST.proc_example_3.__static_sql_exec_line5                  27857                 27857       1000

Cheers!

ORA-600 [kggsmGetString:1]

Hi all!
So, checking on this error, found the following relevant reference: ORA-600 [kggsmGetString:1] (Doc ID 1541187.1).
Once it was during a SQL running, the only match could be Bug 17235420 – ORA-600 [kggsmGetString:1] with adaptive cursor sharing (Doc ID 17235420.8).

The problem?
No detailed information, no workaround, only patches to apply. The only information is “ORA-600 [kggsmGetString:1] can occur in 12c if adaptive cursor sharing is used.

So, checking on SQL Plan details, could see it’s using the following adaptive optimizations:

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)
   - 2 Sql Plan Directives used for this statement

MOS note Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance)” ID 2312911.1 recommends to apply Oct 2017 BP or one-off patches to disables adaptive statistics only.

In this case, the easiest solution is to disable both, adaptive plans, as this release only has one parameter to control that – optimizer_adaptive_features which defaults to TRUE.

And it’s online:

SQL> show parameter adaptive

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features	     boolean	 TRUE
optimizer_adaptive_reporting_only    boolean	 FALSE
parallel_adaptive_multi_user	     boolean	 TRUE
SQL> alter system set optimizer_adaptive_features=false scope=both;

System altered.

SQL> show parameter adaptive

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features	     boolean	 FALSE
optimizer_adaptive_reporting_only    boolean	 FALSE
parallel_adaptive_multi_user	     boolean	 TRUE

After that, issue solved. ORA-600 didn’t repeated.

Hope it helps!

IT Platform Career Speak at LaSalle Innovation and Technology Week

Hey all!

This wee we had the opportunity speak to some young minds about IT Platform and a little bit about our daily working routine as DBAs and Application at the LaSalle Innovation and Technology week.

WhatsApp Image 2018-07-04 at 18.57.27 (1).jpeg

We (Dieison Santos, Marcelo Lermen and myself)  gladly had some very interested audience and for that we’d like to thank you very much for the organization for the invitation. Find the complete schedule for the week here.

Also, find our slides here.

Thank you all!

Oracle Pivot and Unpivot Operators

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Pivot and Unpivot Operators

Pivoting tables are now possible in 11g through PIVOT clause. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. Prior to 11g new functions it was possible to accomplish by using DECODE combined with some aggregate function like SUM.

Also, Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, making possible to replace the hard coded IN clause with a subquery, or the ANY commands.

In same way, the UNPIVOT operator converts column-based data into separate rows. Some important considerations about feature:

  • Column names are required. These can be set to any name not currently in the driving table.
  • The columns to be unpivoted must be named in the IN clause.
  • By default the EXCLUDE NULLS clause is used. To override the default behavior use the INCLUDE NULLS clause.
  • The following query shows the inclusion of the INCLUDE NULLS clause.

For more information and examples: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

Cheers!