With Oracle’s EBR we have different editions for our different application releases with each edition containing a different version of your application code (packages, views, synonyms, etc). By default, all sessions will run in the Default Edition and see the objects in that edition. We have the option of changing the edition, either with: ALTER [...]

I was going to write up a post about researching library cache pin locks, but someone else has done a very thorough job already: https://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/. Especially useful is this query: select distinct ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine, ob.kglnaown obj_owner, ob.kglnaobj obj_name ,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req , w.state, w.event, w.wait_Time, w.seconds_in_Wait from [...]

Identify sessions that are blocking other sessions

Sometimes an application will be plagued by sessions that are abandoned partway through a transaction, leaving locks on rows. Other sessions may try to update/delete these locked rows and will hang while waiting for the locks to be release. Here is a simple query against the ASH view that will find any sessions that have [...]

Read more →

Handy Edition Based Redefinition queries

A few queries that are useful to have on hand:   SELECT sys_context(‘userenv’,’current_edition_name’) current_edition_name from dual; ALTER SESSION SET EDITION = R_ABC; ALTER SESSION SET EDITION = ora$base; SELECT * FROM dba_editions; SELECT * FROM database_properties WHERE property_name = ’DEFAULT_EDITION’; SELECT * FROM dba_objects_ae WHERE edition_name IS NOT NULL ORDER BY object_type,object_name; SELECT * FROM dba_editioning_views_ae ORDER BY owner; SELECT username, editions_enabled FROM dba_users ORDER BY username;

Read more →

ORA-38807: Implementation restriction: an edition can have only one child

When creating a new edition, you get: ORA-38807: Implementation restriction: an edition can have only one child Try this: Instead of CREATE EDITION VERSION_10; Use: CREATE EDITION VERSION_10 AS CHILD OF VERSION_9; The problem seems to be an Oracle bug in 11g and some 12c versions where the pointers to the editions are incorrect, so [...]

Read more →

Query to identify wait events

Just a quick query to run to see if there’s a particular wait event that’s spiking during a timeframe: select COUNT(*), event, sample_time, inst_id from gv$active_session_history where sample_time BETWEEN TRUNC(SYSDATE) + (10/24) + (50/60)/24 AND TRUNC(SYSDATE) + (10/24) + (56/60)/24 HAVING COUNT(*) > 10 GROUP BY event, sample_time, inst_id ORDER BY sample_time, event, instead ; [...]

Read more →

Lawyer and Musician jokes

OK, taking a break from technical stuff… What’s the last thing a drummer says in a band? “Hey, how about we try one of my songs?” What did the drummer say to his new band? “Do you want me to play too fast or too slow?” What do a charging elephant and a guitar solo [...]

Read more →

Identify queries that are *sometimes* slow

When tuning an application that is under heavy load, one of the issues is that some queries perform very well most of the time, but occassionally run very long and create application problems. These queries don’t look suspicious from OEM or the AWR reports, but they cause unexplained blips in the application. If you’re looking [...]

Read more →

Find the plan being used

When researching a slow running query, the first step is looking at the plan being used. Some queries will have multiple plans in the shared pool, so we need a way to figure out which one is being used. Here’s a useful query to SELECT inst_id, address, hash_value, plan_hash_value FROM gv$sqlarea WHERE sql_id = ‘t3y2hc4378gh2′ [...]

Read more →

Ignore accents in Oracle queries

–Set the comparison to LINGUISTIC alter session set NLS_COMP=LINGUISTIC; –Set the SORT to GENERIC_M_AI (AI means Accent insensitive, and case insensitive) alter session set NLS_SORT=GENERIC_M_AI; –Create a table and load some data CREATE TABLE test_query (my_text VARCHAR2(100)) / insert into test_query (my_text) VALUES (‘test-’); insert into test_query (my_text) VALUES (‘TEST-’); insert into test_query (my_text) VALUES [...]

Read more →