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.

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

martedì, settembre 19, 2017

Using XML 11: XMLElement

From [1]. The SQL/XML standard function XMLElement constructs XML elements from relational data. It takes as arguments an element name, an optional collection of attributes for the element, and zero or more additional arguments that make up the element content. It returns an XMLType instance.

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 and, if it is defined, the identifier must not be NULL or else an error is raised.


This is the syntax:


I want to do a very simple test. I use the ALL_USERS view, in order to the run the first select.


Let's reformat the output


Ok. How you can see, the XMLElement, transform the column in a XML. Let's see more on the ALL_USERS view:


I add a second column in my select (green and red).


Because of DATE data type, I change to output using the TO_CHAR function


Really simple. But I can put an XMLElement (gray and green) inside another one (red)


So I obtain a more complex XML.

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

Part10 Index Part12



lunedì, settembre 11, 2017

Using XML 10: Comparing functions

In this post, I want just compare the EXTRACT and EXTRACTVALUE functions with the new XMLQuery and XMLTable functions, respectively.

What I want to show is summarized in this table

Old Syntax New Syntax
EXTRACT XMLQuery
EXTRACTVALUE XMLTabel


But wait a moment. Really, EXTRACTVALUE extracted the value while in the previous example the XMLTable was the same as XMLQuery, they is EXTRACT function.

The example for XMLTable operator not used all parameters. I not used the "COLUMN" option.

It's the time.

SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg[@pid="65370"]' 
  3> PASSING t.XML_COLUMN COLUMNS message path 'msg', text path 'txt') tx

Click to enlarge

Now you can say that XMLTable is like the EXTRACTVALUE function. So the table I wrote above is more correctly like this one:

Old Syntax New Syntax
EXTRACT(<xmltype>, <row-pattern>) XMLQuery(<row-pattern>
         PASSING <col-name>
         RETURNING CONTENT)
EXTRACTVALUE(<col-name>, <row-pattern>) XMLTabel(<row-pattern>
         PASSING <expr>
         COLUMNS <column> PATH <string>)

At this point I'm (almost) ready to modify a statement wrote by Connor McDonald. Yes. My goal when I decided to write these posts was to understand a particular statement that McDonald wrote in him video [1]


The statement I used is a little different: I just modified some aliases and the way to quote the string (in green the modifications)

with xml 
  as (
     select
       table_name
     , dbms_xmlgen.getxmltype(
          q'[select 
             table_name
           , partition_name
           , high_value
     from 
       dba_tab_partitions
     where 
       table_name=']'||table_name||q'[']') as xml_output
    from dba_tables t
    where table_name in ('COMPOSITE_RNG_LIST')
    and   owner='FRTD_CORP'
    )
select
   extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
   extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition_name,
   extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
   from xml x,  
   table(xmlsequence(extract(x.xml_output, 'ROWSET/ROW'))) rws
order by 1,2,3;

Click to enlarge

What I don't discussed yet is the DBMS_XMLGEN.GETXMLTYPE. I'm going to investigate this function in the 3th part ("From rows/columns To XML" [3]) of this collection on XML.

For now, from [2], we know that "the PL/SQL package DBMS_XMLGEN creates XML documents from SQL query results". It should be enough in order to understand the meaning of the above statement.

Before to go ahead, I need to know the format of my XML source: it comes from CTE (Common Table Expression). So I run just this statement

Click to enlarge

Comparing this output with the one I used in my examples, you can build following map

McDonald case My example
ROWSET alertlog
ROW msg
TABLE_NAME txt
PARTITION_NAME txt
HIGH_VALUE txt

Based on all you saw untill now, I can rewrite the above statement like this one (in a red square the two core pieces)

with xml 
   as (
      select
         table_name
       , dbms_xmlgen.getxmltype(
            q'[select 
               table_name
             , partition_name
             , high_value
      from 
          dba_tab_partitions
      where 
          table_name= ']'||table_name||q'[']') as xml_output
      from dba_tables t
      where table_name in ('COMPOSITE_RNG_LIST')
      and owner='FRTD_CORP'
      )
select 
      rws.table_name
    , rws.partition_name
    , rws.high_value
from 
      xml x
    , xmltable('/ROWSET/ROW'
               PASSING
                       x.xml_output 
               COLUMNS 
                       table_name PATH 'TABLE_NAME'
                     , partition_name PATH 'PARTITION_NAME'
                     , high_value PATH 'HIGH_VALUE') rws
order by 1,2,3;

Click to enlarge

Pay attention on the case of you are searching. If you remember, XML is case sensitive, so you must specify ROWSET, ROW and others in upper case. Infact if you run the previous statement using the search in lower case you found noting:

XML is case sensitive

For semplicity, the following picture I just put together both "select" pieces about old and new syntax.

Click to enlarge

Update

2017/Sep/10 - Added the section about Connor McDonald.
2017/Sep/11 - Added the case sensitive example

Reference

[1] https://www.youtube.com/watch?v=yKHQQXKdfOM&t=82s
[2] https://docs.oracle.com/database/122/ARPLS/DBMS_XMLGEN.htm#ARPLS374
[3] http://orasal.blogspot.com/2017/09/using-xml-argument-index.html

Part09 Index Part11

Using XML 09: SELECT, Part 3b/3

In the previous post I saw that the XQuery_string '/alertlog' return just one row. So my question is "what if I change the row-pattern from '/alertlog' to /alertlog/msg or /alertlog/msg/txt"?

Here the answere

XQuery_string: /alertlog/msg


As you can see, in this case there will be 6 rows: one for each element. And this is exactly what I want

SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg' PASSING t.XML_COLUMN) tx








XQuery_string: /alertlog/msg/txt


Also in this case I have 6 rows. But here I have only the <txt> element (it was my request)

SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg/txt' PASSING t.XML_COLUMN) tx


I skip the output between line 2 and 5.



XQuery_string: more examples


Here other two example: similar to the previous posts

SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg/[@pid="65370"]' PASSING t.XML_COLUMN) tx



SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg/[@pid="65263"]' PASSING t.XML_COLUMN) tx





and if I modify the search

SQL> SELECT rownum, t.*, tx.* from mytab t, 
  2> XMLTable('/alertlog/msg/[@pid="65370"]/txt' PASSING t.XML_COLUMN) tx



Part08 Index Part10