Another Way To See Who Is Blocking

A simple and quick way to see who is producing blocks in the database is to use the query we show below. Of course, there are other ways to perform this task such as query the DBA_BLOCKERS. The next step will see the blocker process, what are it doing?

 

SELECT SID FROM V$LOCK WHERE BLOCK = 1

 

HTH – Antonio NAVARRO

 

 

Advertisements

Enable or Disable Bug Fixes

The hidden parameter _FIX_CONTROL can be used to enable or disable certain bug fixes. This is often done to perform testcases or simulate scenarios and see how can affect a bug.

It is configured for a single hotfix as show below (at session level or system level). in this case (3426050) disable cartesian join on complex views, this is a very useful to reduce the use of cross join;

 

ALTER SESSION SET “_fix_control”=’3426050:OFF’;

 

It is configured for multiple hotfix as show below. In this case we include 6399597, disallow HASH GROUP BY for subquery processing and the 3426050;

 

ALTER SYSTEM SET “_fix_control”= ‘3426050:ON’, ‘6399597:ON’…. ¬†scope=spfile sid=’*’;

 

A full list of bug numbers which are controlled by fix control and theirs status can be found in the V$ views;

 

  • V$SYSTEM_FIX_CONTROL
  • V$SESSION_FIX_CONTROL

 

Because it is a hidden parameter, use of _fix_control must be authorized by MOS. In databases that we all have to make our experiments you can use it without problems.

HTH – Antonio NAVARRO

Where Is CATCDB.sql?

Creating and configurating a CBD in 12c, run the script @?/rdbms/admin/catcdb.sql after the create database with “enable database puggable” clausule but the file does not exist.

Apparently this is a bug that occurs in 12c, and it seems that in this distribution is not included this file. MOS has published a note and a link where you can download the patch.

This link points to the patch

HTH – Antonio NAVARRO

Discovering Hidden Parameters

One way to see the hidden parameters of the database, for instance and session is using the x$ views, of course, the use of these views is not very clear, since they are not documented.

The following example dumps the contents of the variables and generate a html file that you can open with a Web browser. Execute it from sqlplus and logged like sys user;

 

set markup html on spool on

spool db_info.html

set echo on

SELECT a.ksppinm “Parameter”, a.ksppdesc “Description”,

b.ksppstvl “Session Value”, c.ksppstvl “Instance Value”

FROM x$ksppi a, x$ksppcv b, x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE ‘/_%’ escape ‘/’

ORDER BY 1;

spool off

set markup html off

 

HTH – Antonio NAVARRO

Get SQL_ID for my Statement

An old trick to find the SQL_ID or hash_value (used more in the old days) for a statement is write a comment on the sentence to run, more recognizable and more difficult to match with others statements, then we need to seek the statement in the library cache, doing a search on the v$sqlarea or more restricted using the v$open_cursor, of course, there are many more ways to get this value, but this simple trick is very easy and fast.

Generate the establishment with the comment. You can use multiline comments or comments single line, in this case we use multiline

 

SELECT /* OMEGA */  * FROM DUAL;

 

We now proceed to seek the statement

 

SELECT SQL_ID, ADDRESS, PARSING_USER_ID, SQL_TEXT FROM
V$SQLAREA WHERE SQL_TEXT LIKE ‘%OMEGA%’;

 

This will give an output with two tuples, one of which is the statement that we executed and the other is the search query.

HTH – Antonio NAVARRO