Oracle SQL
  • LICENSE

Monthly Archives: July 2014

How to find out session info about session that comes from remote database through dblink

Posted on July 25, 2014 by Sayan Malakshinov Posted in oracle, remote statements, SQL*Plus 2,711 Page views 6 Comments

It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

First of all we need to start script on local database:

SQL>                                                                                                                                                                   
SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask :
Remote_db mask:

 INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
-------- ---- ---------- -------- --------- ----------- ---------------- ----------- -------------------------------------------------- ---------------------------
       1  275       4469 XTENDER  BAIKAL     1742630060 8.20.7119        FROM REMOTE 4241494B414C2E63616336656437362E382E32302E37313139 SQL*Net message from client
                                                                                                                                                                  

Then we need to copy GLOBALID of interested session and run script on database that shown in column REMOTE_DBID, but with specifieng GLOBALID:

SQL>                                                                                                                                                                                                 
SQL> conn sys/syspass@baikal as sysdba
Connected.

======================================================================
=======  Connected to  SYS@BAIKAL(baikal)(BAIKAL)
=======  SID           203
=======  SERIAL#       38399
=======  SPID          6536
=======  DB_VERSION    11.2.0.4.0
======================================================================

SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask : 4241494B414C2E63616336656437362E382E32302E37313139
Remote_db mask:

INST_ID   SID    SERIAL# USERNAME  REMOTE_DB  REMOTE_DBID TRANS_ID   DIRECTION   GLOBALID                                            STATE                     
------- ----- ---------- --------- ---------- ----------- ---------- ----------- --------------------------------------------------  --------------------------
      1     9      39637 XTENDER   BAIKAL      1742630060 8.20.7119  TO REMOTE   4241494B414C2E63616336656437362E382E32302E37313139  [ORACLE COORDINATED]ACTIVE

It’s quite simple and fast.

dblink global transaction remote sessions

Standalone sqlplus script for plans comparing

Posted on July 25, 2014 by Sayan Malakshinov Posted in oracle 1,939 Page views Leave a comment

I have a couple scripts for plans comparing:

1. https://github.com/xtender/xt_scripts/blob/master/diff_plans.sql
2. http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active.sql

But they have dependencies on other scripts, so I decided to create a standalone script for more convenient use without the need to download other scripts and to set up the sql*plus environment.
I’ve tested it already with firefox, so you can try it now: http://github.com/xtender/xt_scripts/blob/master/plans/diff_plans_active_standalone.sql

Some screenshots:
diff_plans.sql:
diff_plans

plans_active.sql:
plans_active

Usage:
1. plans_active:

SQL> @plans_active 0ws7ahf1d78qa 

2. diff_plans:

SQL> @diff_plans 0ws7ahf1d78qa 
 *** Diff plans by sql_id. Version with package XT_PLANS. 
Usage: @plans/diff_plans2 sqlid [+awr] [-v$sql] 

P_AWR           P_VSQL 
--------------- --------------- 
false           true 

Strictly speaking, we can do it sometimes easier: it’s quite simple to compare plans without first column “ID”, so we can simply compare “select .. from v$sql_plan/v$sql_plan_statistics_all/v$sql_plan_monitor” output with any comparing tool.

Bug with xmltable, xmlnamespaces and xquery_string specified using bind variable

Posted on July 24, 2014 by Sayan Malakshinov Posted in bug, oracle 2,376 Page views 3 Comments

Today I was asked about strange problem: xmltable does not return data, if xquery specified by bind variable and xml data has xmlnamespaces:

SQL> var x_path varchar2(100);
SQL> var x_xml  varchar2(4000);
SQL> col x format a100;
SQL> begin
  2      :x_path:='/table/tr/td';
  3      :x_xml :=q'[
  4                  <table xmlns="http://www.w3.org/tr/html4/">
  5                    <tr>
  6                      <td>apples</td>
  7                      <td>bananas</td>
  8                    </tr>
  9                  </table>
 10                  ]';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  :x_path -- bind variable
  5                  --'/table/tr/td' -- same value as in the variable "X_PATH"
  6                  passing xmltype(:x_xml)
  7                  columns i    for ordinality,
  8                          x    xmltype path '.'
  9                );

no rows selected

But if we comment bind variable and comment out literal x_query ‘/table/tr/td’, query will return data:

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  --:x_path -- bind variable
  5                  '/table/tr/td' -- same value as in the variable "X_PATH"
  6                  passing xmltype(:x_xml)
  7                  columns i    for ordinality,
  8                          x    xmltype path '.'
  9                );

         I X
---------- -------------------------------------------------------------------
         1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
         2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>

2 rows selected.

The only workaround I found is the specifying any namespace in the x_query – ‘/*:table/*:tr/*:td’

SQL> exec :x_path:='/*:table/*:tr/*:td'

PL/SQL procedure successfully completed.

SQL> select
  2        i, x
  3   from xmltable( xmlnamespaces(default 'http://www.w3.org/tr/html4/'),
  4                  :x_path -- bind variable
  5                  passing xmltype(:x_xml)
  6                  columns i    for ordinality,
  7                          x    xmltype path '.'
  8                );

         I X
---------- -------------------------------------------------------------------
         1 <td xmlns="http://www.w3.org/tr/html4/">apples</td>
         2 <td xmlns="http://www.w3.org/tr/html4/">bananas</td>

2 rows selected.

It’s quite ugly solution, but I’m not sure whether there is another solution…

bind variable xmlnamespace xmltable xpath xquery

REGEXP_LIKE: strange unspecified value in parameter “modifier”

Posted on July 23, 2014 by Sayan Malakshinov Posted in bug, CBO, curious, oracle 1,743 Page views Leave a comment

Today I noticed strange thing in predicate section of execution plan for simple query with regexp_like, where 3rd parameter “MODIFIER” was not specified:

SQL> select * from dual where regexp_like(dummy,'.');

D
-
X

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  97xuqf9cmjsta, child number 0
-------------------------------------
select * from dual where regexp_like(dummy,'.')

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( REGEXP_LIKE ("DUMMY",'.',HEXTORAW('F07FD85CFF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              0010000000000000001880D85CFF07000002000000000000000000000081000000') ))


20 rows selected.

It is particularly interesting that the values in HEXTORAW() are always different for different first parameters:

SQL> select * from dual where regexp_like(dummy,'x');
...
   1 - filter( REGEXP_LIKE ("DUMMY",'x',HEXTORAW('3895D330FF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              0011000000000000006895D330FF07000002000000000000000000000081000000') ))
SQL> select * from dual where regexp_like(dummy,'y');
...
   1 - filter( REGEXP_LIKE ("DUMMY",'y',HEXTORAW('00DA3C3FFF0700006A1116
              45010000000000000000000000FC12164501000000000000000000000000000000000000
              00110000000000000030DA3C3FFF07000002000000000000000000000081000000') ))
SQL> select * from dual where regexp_like(dummy||'','x')
...
   1 - filter( REGEXP_LIKE ("DUMMY"||'','x',HEXTORAW('70964F2FFF0700006A
              111645010000000000000000000000FC1216450100000000000000000000000000000000
              0000001100000000000000A0964F2FFF07000002000000000000000000000081000000')
               ))

I don’t know, what does it mean, but it looks like garbage from memory.
When I noticed this, I decided to check how regexp_like will work in function-based indexes:

SQL> create table xtest as
  2    select dummy||level as str
  3    from dual
  4    connect by level<=30;

Table created.

SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
...
12 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  7ztp0k8c1zn2h, child number 0
-------------------------------------
select * from xtest where case when regexp_like(str,'1') then 1 end = 1

Plan hash value: 4207139086

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| XTEST |    12 |   264 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(CASE  WHEN  REGEXP_LIKE
              ("STR",'1',HEXTORAW('68F9CB32FF0700006A111645010000000000000000000000FC1
              216450100000000000000000000000000000000000000110000000000000098F9CB32FF0
              7000002000000000000000000000081000000') ) THEN 1 END =1)

SQL> create index xtest_fbi on xtest(case when regexp_like(str,'1') then 1 end);

Index created.

SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
...
12 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  7ztp0k8c1zn2h, child number 0
-------------------------------------
select * from xtest where case when regexp_like(str,'1') then 1 end = 1

Plan hash value: 1479471124

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| XTEST     |    12 |   300 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | XTEST_FBI |    12 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("XTEST"."SYS_NC00002$"=1)

SQL> select column_expression from user_ind_expressions e where e.index_name='XTEST_FBI';

COLUMN_EXPRESSION
-----------------------------------------------------------------------------------------
CASE  WHEN  REGEXP_LIKE ("STR",'1') THEN 1 END

As you can see it works fine, although the predicate from first execution plan differs from the FBI expression.
Then I dumped 10053 trace and noticed that the HEXTORAW(…) function appeared in “Explain Plan Dump” only, so it looks just like plan output bug.

execution plan HEXTORAW regexp_line
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL