Dontcheff

Archive for the ‘PL/SQL’ Category

DBA Statements

In DBA, Oracle database, PL/SQL, SQL on September 5, 2017 at 11:51

“A statement is persuasive and credible either because it is directly self-evident or because it appears to be proved from other statements that are so.” Aristotle

In Oracle 12.2, there is a new view called DBA_STATEMENTS. It can helps us understand better what SQL we have within our PL/SQL functions, procedures and packages.

There is too little on the Internet and nothing on Metalink about this new view:

PL/Scope was introduced with Oracle 11.1 and covered only PL/SQL. In 12.2, PL/Scope was enhanced by Oracle in order to report on the occurrences of static and dynamic SQL call sites in PL/SQL units.

PL/Scope can help you answer questions such as:
– Where and how a column x in table y is used in the PL/SQL code?
– Is the SQL in my application PL/SQL code compatible with TimesTen?
– What are the constants, variables and exceptions in my application that are declared but never used?
– Is my code at risk for SQL injection and what are the SQL statements with an optimizer hint coded in the application?
– Which SQL has a BULK COLLECT or EXECUTE IMMEDIATE clause?

Details can be found in the PL/Scope Database Development Guide or at Philipp Salvisberg’s blog.

Here is an example: how to find all “execute immediate” statements and all hints used in my PL/SQL units? If needed, you can limit the query to only RULE hints (for example).

1. You need to set the PLSCOPE_SETTINGS parameter and ensure SYSAUX has enough space:


SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS 
WHERE OCCUPANT_NAME='PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
              1984

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      IDENTIFIERS:NONE

SQL> alter system set plscope_settings='STATEMENTS:ALL' scope=both;

System altered.

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      STATEMENTS:ALL

2. You must compile the PL/SQL units with the PLSCOPE_SETTINGS=’STATEMENTS:ALL’ to collect the metadata. SQL statement types that PL/Scope collects are: SELECT, UPDATE, INSERT, DELETE, MERGE, EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE and FETCH.


-- start
SQL> select TYPE, OBJECT_NAME, OBJECT_TYPE, HAS_HINT, 
SUBSTR(TEXT,1,LENGTH(TEXT)-INSTR(REVERSE(TEXT), '/*') +2 ) as "HINT" 
from DBA_STATEMENTS
where TYPE='EXECUTE IMMEDIATE' or HAS_HINT='YES';

TYPE              OBJECT_NAME   OBJECT_TYPE  HAS HINT
----------------- ------------- ------------ --- ------------------
EXECUTE IMMEDIATE LASKE_KAIKKI  PROCEDURE    NO  
SELECT            LASKE_KAIKKI  PROCEDURE    YES SELECT /*+ RULE */

-- end

Check also the DBA_STATEMENTS and ALL_STATEMENTS documentation. And the blog post by Jeff Smith entitled PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer.

But finally, here is a way how to regenerate the SQL statements without the hints:


-- start
SQL> select TEXT from DBA_STATEMENTS where HAS_HINT='YES';

TEXT
------------------------------------------------------------
SELECT /*+ RULE */ NULL FROM DUAL WHERE SYSDATE = SYSDATE

SQL> select 'SELECT '||
TRIM(SUBSTR(TEXT, LENGTH(TEXT) - INSTR(REVERSE(TEXT), '/*') + 2))
as "SQL without HINT"
from DBA_STATEMENTS where HAS_HINT='YES';

SQL without HINT
-------------------------------------------------------------
SELECT NULL FROM DUAL WHERE SYSDATE = SYSDATE

-- end

Advertisements

Reducing “library cache: mutex X” concurrency with dbms_shared_pool.markhot

In Database tuning, DBA, Oracle database, Oracle internals, PL/SQL on February 12, 2013 at 16:41

Mutexes or mutual exclusion algorithms are lighter and more granular concurrency mechanism than latches and are used in concurrent programming to avoid the simultaneous use of a common resource.

Oracle started using mutexes from 10g for certain operations in the library cache but from 11g all library cache latches were replaced by mutexes which lead to the common to all DBAs “library cache: mutex X” concurrency event:

library_cache_mutex_X_top5

The irony here is that the reason for obtaining a mutex is to ensure that certain operations are properly managed for concurrency but the management itself leads to concurrency. These are the top 3 differences between mutexes and latches:

– A mutex can protect a single structure, latches often protect many structures
– A mutex get is about 30-35 instructions in the algorithm, compared to 150-200 instructions for a latch get
– A mutex is around 16 bytes in size, compared to 112-200 bytes for a latch

Last month, I found a very interesting and detailed article by Anju Garg on latches and mutexes. It is worth reading.

The aim of this post is to suggest on how to proceeded if you hit the “library cache: mutex X” concurrency event which simply means that a library cache operation is being performed and Oracle is trying to get the library cache mutex in eXclusive mode.

Do the following:

1. Run a query against the x$kglob table to find the names of the objects being pinned most of the time. x$kglob is the resource structure table for library cache locks and pins. Jagjeet’s note gives a very good example.

select * from (
 select case when (kglhdadr = kglhdpar) then 'Parent' else 'Child '||kglobt09 end cursor,
 kglhdadr ADDRESS, substr(kglnaobj,1,20) NAME, kglnahsh HASH_VALUE, kglobtyd TYPE, 
 kglobt23 LOCKED_TOTAL, kglobt24 PINNED_TOTAL,kglhdexc EXECUTIONS, kglhdnsp NAMESPACE
 from x$kglob -- where kglobtyd != 'CURSOR'
order by kglobt24 desc)
where rownum <= 20;

2. If you have issues with PL/SQL packages and procedures, use dbms_shared_pool.markhot() to mark them as hot. As suggested by Andrey Nikolaev, Oracle creates multiple copies of marked objects in the library cache, but frequently uses only one of them. That is why, in order to achieve uniform distribution, mark the PL/SQL objects as hot immediately after the instance restart.

The documentation for the dbms_shared_pool.markhot procedure can be found here.

3. If the issue is sequence related, then either verify with Oracle that it is not a bug, get rid of the sequence if possible or at least modify its parameters (cache, order, etc.).

As you can see from Dom Brook’s article, issues are often package related. Have a look at my screenshot from ASH. The data source is V$ACTIVE_SESSION_HISTORY.

You may review on Metalink “Waitevent: library cache: mutex X” (Document ID 727400.1) for the list of known Oracle bugs but for all I have seen, sometimes patches do not help, so finding a workaround is all the DBA is left with. An ASH report will be very helpful:

library_cache_mutex_X_plsql

If you see “library cache: mutex X” as a top event, note that ADDM will first find that there is contention for latches related to the shared pool that were consuming significant
database time (no surprise). For the top SQL statements, ADDM will claim something like “Waiting for event library cache: mutex X in wait class ‘Concurrency’ accounted for 97% of the database time spent in processing the SQL statement with SQL_ID d2svmdafangyq.” Look then at the “Rationale” sections and hunt for the names of the PL/SQL packages 🙂

Table Annotations in 11.2.0.2

In DBA, Oracle database, PL/SQL, SQL on April 22, 2011 at 10:12

11.2.0.2 is not just a patchset of 11gR2. It is more like 11gR3. Last month, I presented my favorite top 10 new features for DBAs. However, the new table annotation feature deserves special attention.

After a new release of Oracle, it is always worthwhile taking a look into DBA_TABLES (or USER_TABLES). Now, the last new column is called RESULT_CACHE. It has for now two possible values: DEFAULT and FORCE.

FORCE means that the table is annotated. That is, if all table names in a given SQL query have this setting, then the result set returned by the query is always considered for caching unless the NO_RESULT_CACHE hint is specified for the query. If one or more tables named in the query are set to DEFAULT, then the effective table annotation for that query is DEFAULT. Which means the result set will not be cached.

Let us see how it works by first creating a table with 15000 rows and run a full table scan query:

If you want to flush the result cache, just run: exec DBMS_RESULT_CACHE.FLUSH;

Here are the objects related to the topic, it is worth checking them:

Caching is not guaranteed by Oracle even if all tables in the query are annotated. The actual caching depends on internal restrictions for client and server cache, query cache worthiness and space available in the cache. Try to run in a row for instance sql with SYSDATE in it and have a look:

Queries that are not Cached:

• Remote objects
• Complex types in the select list
• Snapshot-based or flashback queries
• Queries executed in a serializable, read-only transaction, or inside a flashback session
• Queries that have PL/SQL functions in them
• Queries that have VPD policies enabled on the tables

How many records can Oracle store? All depends on two init.ora parameters: RESULT_CACHE_MAX_SIZE & RESULT_CACHE_MAX_RESULT:

RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory that can be used by the result cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled. The default value is derived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGET. By default, the cache takes 0.025% of the memory_target parameter’s value. However, the max allocation cannot exceed 75% of the size of the shared pool. With my current memory_target of 384M, the result cache max size does not even reach 1M. For production systems, I would recommend that you set it to at least 32M.

RESULT_CACHE_MAX_RESULT specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use. The default value is 5% of the RESULT_CACHE_MAX_SIZE. This should be OK in most cases.

And finally, look at the execution plan when annotated tables are involved:

A good question is: so which tables from the database should I annotate? I would probably go through few AWR reports and try to find the repetitive queries run at least 100s of times. Then force annotate the tables used in the queries. And test first plus follow on regular basis what comes from V$RESULT_CACHE_OBJECTS! With Oracle at times you get the opposite of what is expected.

P.S. I often see in AWR reports how WebLogic runs 1000s of times per hour “select * from dual”. In a recent report, I saw the DUAL table as number one segment in terms of logical reads. You can annotate even system tables:

SQL> alter table dual result_cache (mode force);

Table altered.

Larry Ellison at the Churchill Club

In DBA, Grid Control, Oracle database, PL/SQL on March 21, 2011 at 11:02

Today is exactly one and half years after Larry Ellison was interviewed by Ed Zander at the Churchill Club.

Ben Rockwood wrote a must-read article about the event and I would not like to repeat what he said but just highlight few things looking retrospectively into this probably most interesting and flamboyant LJE interview ever.

Here are 5 interesting IBM related quotes:

1. The company that does more Oracle installations than any other company in the world: that will be IBM.
2. IBM is who we’re targeting, its who we feel we need to compete with to be successful ourselves. We are happy to compete with IBM on level plain field.
3. I’d like IBM to explain what they meant when they said they took 250 customers away from Sun. I don’t think there is a single example of any Sun customer who’s replaced all of their machines with IBM computers.
4. I will be very clear: Solaris is way better than AIX.
5. Sun machines run faster than IBM machines and cost less.

How do you like the part on Cloud Computing? I just can’t disagree with him 🙂

Do you remember also this one?

And finally the part from last year’s OOW (2010):

Poll: which concept do you least trust, which one do you find most fake?

11g Grid Control: PL/SQL execution monitoring in SQL Monitoring

In DBA, Grid Control, PL/SQL, SQL on September 6, 2010 at 11:43

One of the most useful new features of 11g Grid Control is PL/SQL execution monitoring. Wonder now if a developer wants to know how his PL/SQL procedure did in terms of its SQL and DML statements? What calls did it make and how long it took per call? Beats me is what I would have said before 🙂 Have a look at what Oracle did with 11g Grid Control:

Not only we see the duration of the PL/SQL procedure but we can also see its activites and metrics per SQL call:

We can monitor LIVE either the execution details compared to a previous run with a real time 15 second refresh:

or view its execution details with real time 10 second refresh:

giving us time, wait and io statistics. You can view it or save it in a report format or even mail it directly to the developer!

Good one from Oracle.

How did we do similar things in 8i?

Remove old Grid Control warnings

In Grid Control, PL/SQL on July 21, 2010 at 08:09

If you use Grid Control and want to delete some old warning messages and the “clear alert” button does not help, then use the following PL/SQL block. Run it as SYSMAN. Depending on the number of alerts the cursor fetches, the delete might take up to a minute or so.

You can do masive deletes based on the message text, for example:


CREATE OR REPLACE PROCEDURE DELETE_ALERTS_LIKE(P_LIKE in varchar2)
is
V_LIKE varchar2(22);
   CURSOR c1 IS
     SELECT s.target_guid,
            s.metric_guid,
            s.key_value
       FROM mgmt_targets t JOIN mgmt_current_severity s
            ON s.target_guid = t.target_guid
      WHERE s.message LIKE '%'||v_like||'%'; 
BEGIN
V_LIKE := P_LIKE;
    FOR r IN c1 LOOP
       em_severity.delete_current_severity(r.target_guid,r.metric_guid,r.key_value);
       DELETE from sysman.mgmt_severity
       WHERE
         target_guid = r.target_guid AND
         metric_guid = r.metric_guid AND
         key_value = r.key_value;
       COMMIT;
    END LOOP;
END;
/

Now, let us remove all warnings about invalid objects:

DELETE_ALERTS_LIKE('invalid');

You can even make a procedure which will remove with just one command all old alerts having ‘job’,’invalid’,’status’ or ‘resource’ in the text message:

CREATE or REPLACE TYPE t_greshki IS TABLE OF VARCHAR2(22);
/

CREATE OR REPLACE PROCEDURE DELETE_ALERTS
is
l_greshki t_greshki := t_greshki(,'job','invalid','status','resource');
BEGIN
    FOR i IN l_greshki.first .. l_greshki.last LOOP
       DELETE_ALERTS_LIKE(l_greshki(i));
       COMMIT;
    END LOOP;
END;
/

exec DELETE_ALERTS;

Pretty useful for me at least.