Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

giovedì, agosto 16, 2018

Lock Chains 02: GV$SESSION

In my previous post, I showed the chain of the locks using a query for just an instance. Recently Kaley Crum, an active member of the Orapub community, modified my statement so now you can use it in RAC configuration.

I only modified a little, the output:

col BLOCKING_SESSION for a10
col LAST_LOCKED_SESSION for a10
col PATH_EVENT for a120
set lines 210
set pages 99
with
  snap as (
   select
        inst_id
      , sid
      , blocking_instance
      , blocking_session
      , sql_id
      , prev_sql_id
      , event
      , state
    from
      gv$session)
select
    to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now
  , CONNECT_BY_ISCYCLE cycle
  , CONNECT_BY_ROOT inst_id ||':'||
    CONNECT_BY_ROOT sid blocking_session
  , inst_id ||':'||
    SID last_locked_session
  , SUBSTR(SYS_CONNECT_BY_PATH(
        DECODE(state,
                     'WAITING', sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,
                                sid|| '@' || inst_id ||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')
          ,5) path_event
 from snap 
where
  level > 1
connect by
  nocycle prior sid = blocking_session
       and prior inst_id = blocking_instance;


Picture 01 - Click to enlarge

Note: How to read the output

1. The column BLOCKING_SESSION and LAST_LOCKED_SESSION are composed of 2 members separated by the colon. For example, on the first line, 1:1313 mean that on the instance 1 the session 1313 is the head of the lock chain where the last element is the session 609 on the instance 1.

2. In the PATH_EVENT column, the "session" and "instance" are related by an "at". For example the on the last line, 1377@1 mean that the session 1377 on the instance 2 is locking the session 1441 on the instance 2

martedì, aprile 03, 2018

Info about SCAN

In my company, we have more than 1000 databases and sometimes is very hard to manage all of them.

So I created some scripts that help me to manage some things.

One of this is the information on the SCANs

The SCAN port, the SCAN name and the network interface where the SCAN listeners are configured are different. So, in order to check the information of the SCAN configuration, I wrote following script

The output is something like this

###################################################################
########################### SCAN info #############################
###################################################################
Scan Name     :  nodecl-scan
Scan Port     :  1555
Dedined on    :  Network: 1
Subnet IPv4   :  10.10.226.0/255.255.254.0
Network Card  :  eth0
LISTENER_SCAN1:  10.10.227.38/eth0:2 (ONLINE on node01)
LISTENER_SCAN2:  10.10.227.39/eth0:3 (ONLINE on node02)
LISTENER_SCAN3:  10.10.227.40/eth0:3 (ONLINE on node01)
###################################################################


As you can see, the output shows also the interface where the listener is bound. In this case, on the node01, the listeners are on eth0:2 and eth0:3 with 10.10.267.38 and 10.10.267.40, respectively. On the node02 instead, the SCAN listener 2, with IP address 10.10.267.39, is on eth0:3

In case of the crs is down, the script returns the following error

>>>>> CRS is not working correctly <<<<<
>>>>> Check the crs status <<<<<

CRS-6750: unable to get the active version CRS-6752: Active version query failed

mercoledì, dicembre 20, 2017

Active Session History: Updating the X$ASH, part 03

Part 01
Part 02

Here I try to show you what happens and why Oracle updates the V$ASH view. Please read the pdf by Graham Wood, for more details.

What I analyze is the session "session_id=1732" with "session_serial#=31065" saw in the previous post.

This is what happens

Click for enlarge

The session 1732 (serial# 31065) is sampled 3 times: at sample_id 50243482, 50243483, 50243484. In the last sample_id, there is a change of state of the session. In fact, querying the V$ACTIVE_SESSION_HISTORY, you see

Click for enlarge

But what happens behind the lines is something like this. Pay attention that my conclusions are based on my experiment

Click for enlarge

(1) SAMPLE_ID 50243482/SAMPLE_TIME 15-DEC-17 02:50:40.054

At the 15/12/2017 14:50:40.838429, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:40.054



(2) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:42.064

At the 15/12/2017 14:50:42.016313, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:41.064


(3) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064

At the 15/12/2017 14:50:43.192604, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:42.064


Note that, at this time, the session 1732 is waiting, but Oracle doesn't know yet how match time the session will wait

(4) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064 (the same of the previous one)

At the 15/12/2017 14:50:44.372309 (sample_id 50243485), the last row is updated in X$ASH with SAMPLE_TIME, 14:50:42.064



At sample_id 50243485, the session 1732 finish it works and Oracle know how much time the session waited. So he can update the state of the wait on X$ASH

Here I left the previous row, just to show what happening.

(5) The final result

What you see finally, is a consistent state of the session


lunedì, dicembre 18, 2017

Active Session History: Updating the X$ASH, part 02


Part01
Part03

Starting from previous output, running the following query

set lines 160
col SAMPLE_TIME for a30
set pages 99
select
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , count(*)
  , sample_time 
from 
    my_active_session_history 
group by 
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , sample_time
order by 
    session_id 
  , sample_id
/

you can aggregate the result. The output is here.

I get an extract of this output to try to explain what happens.

What my anonymous PL/SQL (see the previous post) does, is to polling the V$SESSION_ACTIVE_SESSION every second and save the output in MY_ACTIVE_SESSION_HISTORY table.

Because each time, the script read the same rows plus one (the V$ASH is renewed each second), for the same sessions, you see a lot of rows for each loop

Consider, for example, the session 1732/serial 31065.

select
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , count(*)
  , sample_time
from
    my_active_session_history
    where session_id=1732 and session_serial#=31065
group by
    sample_id
  , session_id
  , session_serial#
  , time_waited
  , sample_time
order by
    session_id
  , sample_id
/

Click for enlarge

It appears 50 times for the snap_id 50243482, 49 times for the snap_id 50243483 and 48 times for snap_id 50243484 (see note).

What happens to the session 1732, where for the sample_id=50243484 you see two distinct rows?

The following statement has this output (it is just the detail of the session 1732):

select 
    row_number() over (partition by sample_id order by sample_id, inserting_time) rn
  , inserting_time
  , sample_id
  , wait_time
  , time_waited
  , session_state
  , event
from
    my_active_session_history 
where 
   session_id=1732 and session_serial#=31065
order by 
   sample_id, 1
/

Because I'm interested just in the first rows of each change of "sample_id" and/or change of the "state" inside the same sample_id, I want to see only this changes


with 
  mash as (
    select 
        inserting_time
      , time_waited
      , sample_id
      , sample_time
      , session_id
      , wait_time
      , event
      , session_state
      , row_number() over (partition by sample_id, time_waited, session_state, event order by inserting_time) rn
    from 
      my_active_session_history
    where 
      session_id=1732 and session_serial#=31065
)
select 
    sample_id
  , sample_time
  , inserting_time
  , time_waited
  , session_state
  , event
from 
  mash
where 
  rn =1
order by 
    sample_id
  , inserting_time
/

Click for enlarge

What happens here?


  1. At sample_id =50243482 (14:50:40) the sid 1732 is working (it's ON CPU). This means that TIME_WAITED is 0 (zero)
  2. One second after (14:50:41), sample_id=50243483, the sid 1732 is still working and TIME_WAITED is still 0 (zero)
  3. One second after (14:50:42), sample_id=50243484, the sid 1732 wait on direct path read event, but the TIME_WAITED column is not updated. So while the SESSION_STATE/EVENT columns report the correct state of the session, the TIME_WAITED, not yet (inserting_time=15-DEC-17 02.50.43.192604 PM). Because of this Oracle update on the same SNAP_ID (50243484) this row with the correct value of TIME_WAITED column (inserting_time=15-DEC-17 02.50.44.372309 PM).

In the next session, I try to explain this "update" in a visual way

Note

Is it correct that each next sample_id, have one less? When my PL/SQL script inserts a row the first time, it does at 15-DEC-17 02.50.40.833420 PM (sample_id=50243482). The second time, it inserts at 15-DEC-17 02.50.42.016313 PM (sample_id=50243483): it inserts two seconds after the first time. The third time it inserts at 15-DEC-17 02.50.43.192604.
Anyway, the last record for all samples is the same: 15-DEC-17 02.51.40.488761 PM. Because all samples end at the same time (MX column in the next picture), each of them count one record less.

You can run the following select that show you the min and max timestamp

select * from (
  select
      sample_id
    , session_id
    , session_serial#
    , time_waited
    , sample_time
    , inserting_time
    , max(inserting_time) over (partition by sample_id) mx
    , count(*) over (partition by sample_id) cnt
    , row_number() over (partition by sample_id order by inserting_time) rn_min
    , row_number() over (partition by sample_id order by inserting_time desc) rn_max
  from
      my_active_session_history
  where 
      session_id=1732 and session_serial#=31065
  order by
      session_id
    , sample_id)
where rn_min=1 or rn_max=1
/

Click for enlarge
Please, pay attention. While the inserting_time is the instant when the row is inserted in the MY_ACTIVE_SESSION_HISTORY, the sampling happens each second (in the previous picture I put both SAMPLE_TIME and INSERTING_TIME)

Active Session History: updating the X$ASH, part 01

Part 02
Part 03

I'm a proud member of OraPub community. Some days ago I was speaking with Kaley Crum, another member of the OraPub community.

The context was ASH (Active Session History).

He told me that at some time, Oracle does an update of X$ASH in order to update the values of the TIME_WAITED column.

I didn't trust him because my conviction was that Oracle makes just an insert in this X$.

Well, I was wrong. Kaley right. Thanks to him to learn me something new.

On this argument, what is TIME_WAITED column, and some trouble with it. Kayle wrote a post himself.

Also, and I recommend it to you, a couple of post by Craig Shallahamer. You can find them here and here. And not forget the one wrote by Alberto Dell'Era.

And don't miss the pdf of Graham Wood, slide 35, where he shows the fallacies of the TIME_WAITED column in the V$ACTIVE_SESSION_HISTORY view. This series of posts are based on this slide nr 35.

What I want to do is try to explayn myself the problem, but here, in Part 01, I want just write the test I made.


1) Prepare the enviroment

create table my_active_session_history as select * from v$active_session_history where 1=2;
 

alter table my_active_session_history add (inserting_time timestamp);


2) Run the following anonymous PL/SQL


3) Run following statement, to report the result

col INSERTING_TIME for a30
col SAMPLE_TIME for a30
set lines 120

select 
    inserting_time
  , sample_id
  , sample_time
  , session_id
  , session_serial#
  , event
  , session_state
  , time_waited
from

    my_active_session_history
order by 

    session_id
  , sample_id
  , inserting_time;

You can see the result of this last statement, here.

Part 02
Part 03

lunedì, dicembre 11, 2017

Lock Chains 01: V$SESSION

I always receive a call where a colleague tell me that a session of an application is slow.  Really that session is not slow but locked by another one.

Starting with 10g, Oracle introduced a new column in the V$SESSION performance view, that report the instance number and the session id of the blockers. In this way, the troubleshooting is more simple.

Anyway, after you query the V$SESSION you have to scroll down the result set in order to match the blocking session with the locked session.

Because the relation between the blocker and locked sessions is of "hierarchical" type, I can use the hierarchical query (session 123 is blocking the session 234 and session 345. This last one also is blocking the session 999, for example) to try to picture this relation.

*****
***** Please pay attention. The following statements are not RAC aware yet. So they work just on a single instance *****
*****

col path_state for a30
col path_event for a160
col path_sid for a20
col b_session for 999999
set lines 230
set pages 25
with
  snap as (
   select
        sid
      , blocking_instance
      , blocking_session
      , sql_id
      , prev_sql_id
      , event
      , state
    from
      v$session)
select
    to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') now
  , CONNECT_BY_ISCYCLE cycle
  , CONNECT_BY_ROOT sid b_session
  , SID l_session
  , SUBSTR(SYS_CONNECT_BY_PATH(
        DECODE(state,
                     'WAITING', sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/'||event,
                                sid||'/'||nvl(sql_id, prev_sql_id||'(p)')||'/ON CPU'), ' -> ')
          ,5) path_event
 from
  snap
where
  level >1
connect by
  nocycle prior sid=blocking_session
/


The result is like this

Picture 01 - Click for enlarge

Picture 02 - Click for enlarge

There are a lot of things that seem to be strange.


##### Picture 01

There are sessions that lock each other. They have the column "CYCLE" to 1. Both of them are waiting for "read by other sessions" event. This is strange because it seems to be a deadlock, but there isn't a trace file containing the "ORA-00060: deadlock detected while waiting for resource".

Other curious things are the "enq: TX - row lock contention". In green, I highlighted this. The session 2330 is waiting for the session 2330 that doing...nothing.

 In blue, instead, you can see how a session waiting for "read by other session" depend from a session is reading the same block. 

##### Picture 02

Here you can see how some sessions (in red) are locked by a session that did...nothing (in orange).

In the next post, I'll show a similar statement that you can use on V$ACTIVE_SESSION_HISTOR.

Note: How to read the output

  1. The column CYCLE, tell you if you have a deadlock (like in picture 01). In this case, there will be a "1"
  2. B_SESSION and L_SESSION columns are the blocking session (the head) and the locked session (the tail), respectively 
  3. PATH_EVENT column, show you the lock path. It includes the Session ID, the SQL ID and the wait event if that session in waiting. Otherwise, the wait event is replaced by "ON CPU" 
  4. If the SQL_ID is NULL, then I try to use the PREV_SQL_ID. In this case a "(p)" will appear (for example "2sbqfhnjz6ybw(p)" in orange in the picture 02). Anyway there are some cases where also the previous SQL_ID is NULL. In this case there will be only the "(p)" without the SQL_ID (B_SESSION = 1401, in the picture 02, for example).

mercoledì, ottobre 11, 2017

Using XML: Index

Part One: Introduction to XML

Part Two: From XML to rows and columns

Part Three: From rows and columns to XML






Using XML 17: DBMS_XMLGEN

I started this series on XML because of a query wrote by Connor McDonald. Please read my previous post.

Now there is just a last post where I want to talk about DBMS_XMLGEN.GETXMLTYPE.

First of all the definition of DBMS_XMLGEN package[1].

From [1]: The DBMS_XMLGEN package converts the results of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB. This package is similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the kernel. This package can only be run on the database.

You can find the list of procedure and functions of DBMS_XMLGEN package in [2]

The GETCMLTYPE function definition is taken from [3]: Gets the XML document and returns it as XMLType


As you can see, the first argument is a sqlQuery. So, in order to try a test, I just run a simple query.

First I a run the main statement and check the result


At this point, I run a really simple statement with DBMS_XMLGEN.GETXMLTYPE using the previous select.


So, GETXMLTYPE transforms the resultset of a select in an XML. The root node is "ROWSET" while the children are "ROW". Inside this children, there are other children: one of each column specified in the inner select.

Now I run the part of XML from my previous post (I just modify owner a table name because I run the statement on a different database):


At this point, you have all the instruments to correctly understand the Connor McDonald's select.

Reference

[1] https://docs.oracle.com/cloud/latest/db121/ARPLS/d_xmlgen.htm#ARPLS374
[2] Summary of DBMS_XMLGEN Subprograms
[3] GETXMLTYPE Functions

Part16 Index

Using XML 16: XMLColAttVal

From [1]: Oracle SQL function XMLColAttVal generates a forest of XML column elements containing the values of the arguments passed in.



select XMLElement("User",
          XMLAttributes(USER_ID as "User ID"),
          XMLColAttVal(USERNAME, ORACLE_MAINTAINED as "Is_Ora_Owned", CREATED)
       ) user_details
from all_users fetch first 3 rows only

Click to enlarge

References



[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-F643284F-A6AF-47A9-AF2C-58EF7E88C87F

Part15 Index Part17

lunedì, settembre 25, 2017

Using XML 15: XMLAgg

From [1]: You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements — it is an aggregate function.



Numeric literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead, numeric literals are interpreted as any other literals.

I want just try this select

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username),
      XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),
      XMLElement("Id", USER_ID),
      XMLElement("OracleOwn ", ORACLE_MAINTAINED)
      )
   ) xml_schemas
from all_users fetch first 3 rows only


Ok. It's failed. This is because I have to choose only one argument

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only


So I have modify the the statement

select XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only


Click to enlarge

Update

2017/Oct/11 - Modified the statement because I wrote it badly


Reference

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB

Part14 Index Part16

Using XML 14: XMLConcat

From [1]: You use SQL/XML standard function XMLConcat to construct an XML fragment by concatenating multiple XMLType instances.



Following an example:

select USERNAME, XMLConcat(
   XMLElement("User", username),
   XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),
   XMLElement("Id", USER_ID),
   XMLElement("OracleOwn ", ORACLE_MAINTAINED)
) xml_concat
from all_users fetch first 3 rows only

Click to enlarge

Pay attention to the "blank" in the "OracleOnw" string: it is mantained.

References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-3806853D-2AAA-4D2D-AD98-3E0941F7BE17

Part13 Index Part15

Using XML 13: XMLForest

You use SQL/XML standard function XMLForest to construct a forest of XML elements.


From [1]: Each of the value expressions (value_expr in Figure 8-3) is converted to XML format, and, optionally, identifier "alias" is used as the attribute identifier. For an object type or collection, the AS clause is required. For other types, the AS clause is optional

Click to enlarge

Well, I need to rename the element:

select username,
    XMLForest(username, to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') when, 
    USER_ID, ORACLE_MAINTENED) XML_forest
from all_users fetch first 3 rows only

Click to enlarge

I also can indent the functions. In following example I use XMLElement and XMLForest

Click to enlarge

Just let me reformat the output changing the column size

Click to enlarge

and I can indent the functions

Click to enlarge

References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-D9666485-0383-4AF6-B352-B9A90AE1DCFB

Part12 Index Part14

Using XML 12: XMLAttributes

From [1]: SQL/XML standard function XMLAttributes can be used together with XMLElement, to specify attributes for the generated elements. The first argument to function XMLElement defines an identifier that names the root XML element to be created. The root-element identifier argument can be defined using a literal identifier. If an attribute value expression evaluates to NULL, then no corresponding attribute is created. The data type of an attribute value expression cannot be an object type or a collection.

This is the syntax:


I run following statement

select username, XMLElement("Schema",
   XMLAttributes(username as "User",
   to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss') as "When"
   USER_ID as "Id"
   ORACLE_MANTAINED as "OracleOwn")
   ) xml_user_details
from all_users fetch first 3 rows only


As you can see, the XML in yellow rectangle, is not complete. I have to format the column, changing the value of long


References

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-868E591C-19A1-4E4A-BC2A-013181071B77

Part11 Index Part13