As you know, NaN is a “Not a Number”.
How do you think, what would be the result of the following query? (0f/0 == NaN)
select count(*) cnt from dual where rownum < 0f/0;
Author Archives: Sayan Malakshinov
select * from table where rownum=1
I never thought I would have to optimize so simple query as
select col1, col2, col4, col7 from table where rownum=1
(even though I read recently “SELECT * FROM TABLE” Runs Out Of TEMP Space)
But a few days ago frequent executions of this query caused big problems on the one of our databases(11.2.0.3) because of adaptive serial direct path reads.
I don’t know why, but I felt intuitively that full table scan with “First K rows” optimization (“_optimizer_rownum_pred_based_fkr“=true) should turn off adaptive serial direct path reads. It seems quite logical to me.
PS. Unfortunately I had a little time, so I didn’t investigate what process and why it was doing that, I just created profile with “index full scan” access, and it completely solved the problem.
INDEX FULL SCAN (MIN/MAX) with two identical MIN()
I’ve just noticed an interesting thing:
Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:
SQL> explain plan for
2 select
3 min(ID) as x
4 from tab1
5 where ID is not null;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 1 | 4 | 3 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TAB1 | 1 | 4 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID" IS NOT NULL)
Test tables
[sourcecode language=”sql”]
create table tab1(id, x, padding)
as
with gen as (select level n from dual connect by level<=1000)
select g1.n, g2.n, rpad(rownum,10,’x’)
from gen g1,gen g2;
create index ix_tab1 on tab1(id, x);
exec dbms_stats.gather_table_stats(”,’TAB1′);
[/sourcecode]
[collapse]
But look what will happen if we add one more “MIN(ID)”:
SQL> explain plan for
2 select
3 min(ID) as x
4 , min(ID)+1000 as x1000
5 from tab1
6 where ID is not null;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3397888171
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3075 (17)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IX_TAB1 | 999K| 3906K| 3075 (17)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID" IS NOT NULL)
Simple Android Oracle client
I am happy to announce, that I’ve just published my first android app – 
Simple oracle client for android!
Since this is only the first version, I’m sure that it contains various UI bugs, so I’ll wait for reviews and bug reports!
Several screenshots:
 |
 |
 |
 |
Just a couple of screenshots of sqlplus+rlwrap+cygwin+console
I previously wrote that I peeped the idea about showing the session information in terminal title from Timur Akhmadeev’s screenshots, and Timur wrote:
I’m using (a bit modified) Tanel Poder’s login.sql available in his TPT scripts library: http://tech.e2sn.com/oracle-scripts-and-tools
Scripts:
Tanel’s i.sql
My title.sql and on_login.sql
View screenshots
Little script for finding tables for which dynamic sampling was used
You can always download latest version here: http://github.com/xtender/xt_scripts/blob/master/dynamic_sampling_used_for.sql
Current source code:
col owner for a30;
col tab_name for a30;
col top_sql_id for a13;
col temporary for a9;
col last_analyzed for a30;
col partitioned for a11;
col nested for a6;
col IOT_TYPE for a15;
with tabs as (
select
to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1)) owner
,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2)) tab_name
,count(*) cnt
,sum(executions) execs
,round(sum(elapsed_time/1e6),3) elapsed
,max(sql_id) keep(dense_rank first order by elapsed_time desc) top_sql_id
from v$sqlarea a
where a.sql_text like 'SELECT /* OPT_DYN_SAMP */%'
group by
to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1))
,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2))
)
select tabs.*
,t.temporary
,t.last_analyzed
,t.partitioned
,t.nested
,t.IOT_TYPE
from tabs
,dba_tables t
where
tabs.owner = t.owner(+)
and tabs.tab_name = t.table_name(+)
order by elapsed desc
/
col owner clear;
col tab_name clear;
col top_sql_id clear;
col temporary clear;
col last_analyzed clear;
col partitioned clear;
col nested clear;
col IOT_TYPE clear;
ps. Or if you want to find queries that used dynamic sampling, you can use query like that:
select s.*
from v$sql s
where
s.sql_id in (select p.sql_id
from v$sql_plan p
where p.id=1
and p.other_xml like '%dynamic_sampling%'
)
PRECOMPUTE_SUBQUERY hint
I’ve just found out that we can specify query block for PRECOMPUTE_SUBQUERY: /*+ precompute_subquery(@sel$2) */
So we can use it now with SQL profiles, SPM baselines and patches.
SQL> select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in (select chr(level) from dual connect by level<=100);
D
-
X
SQL> @last
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c437vsqj7c4jy, child number 0
-------------------------------------
select/*+ precompute_subquery(@sel$2) */ * from dual where dummy in
(select chr(level) from dual connect by level<=100)
Plan hash value: 272002086
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DUMMY"='' OR "DUMMY"='' OR "DUMMY"='♥' OR "DUMMY"='♦'
OR "DUMMY"='♣' OR "DUMMY"='♠' OR "DUMMY"='' OR "DUMMY"=' OR
"DUMMY"=' ' OR "DUMMY"=' ' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=' '
OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='►' OR "DUMMY"='◄' OR
"DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
OR "DUMMY"='' OR "DUMMY"='↑' OR "DUMMY"='↓' OR "DUMMY"='' OR
"DUMMY"=' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"='' OR "DUMMY"=''
OR "DUMMY"=' ' OR "DUMMY"='!' OR "DUMMY"='"' OR "DUMMY"='#' OR
"DUMMY"='$' OR "DUMMY"='%' OR "DUMMY"='&' OR "DUMMY"='''' OR
"DUMMY"='(' OR "DUMMY"=')' OR "DUMMY"='*' OR "DUMMY"='+' OR "DUMMY"=','
OR "DUMMY"='-' OR "DUMMY"='.' OR "DUMMY"='/' OR "DUMMY"='0' OR
"DUMMY"='1' OR "DUMMY"='2' OR "DUMMY"='3' OR "DUMMY"='4' OR "DUMMY"='5'
OR "DUMMY"='6' OR "DUMMY"='7' OR "DUMMY"='8' OR "DUMMY"='9' OR
"DUMMY"=':' OR "DUMMY"=';' OR "DUMMY"='<' OR "DUMMY"='=' OR "DUMMY"='>'
OR "DUMMY"='?' OR "DUMMY"='@' OR "DUMMY"='A' OR "DUMMY"='B' OR
"DUMMY"='C' OR "DUMMY"='D' OR "DUMMY"='E' OR "DUMMY"='F' OR "DUMMY"='G'
OR "DUMMY"='H' OR "DUMMY"='I' OR "DUMMY"='J' OR "DUMMY"='K' OR
"DUMMY"='L' OR "DUMMY"='M' OR "DUMMY"='N' OR "DUMMY"='O' OR "DUMMY"='P'
OR "DUMMY"='Q' OR "DUMMY"='R' OR "DUMMY"='S' OR "DUMMY"='T' OR
"DUMMY"='U' OR "DUMMY"='V' OR "DUMMY"='W' OR "DUMMY"='X' OR "DUMMY"='Y'
OR "DUMMY"='Z' OR "DUMMY"='[' OR "DUMMY"='\' OR "DUMMY"=']' OR
"DUMMY"='^' OR "DUMMY"='_' OR "DUMMY"='`' OR "DUMMY"='a' OR "DUMMY"='b'
OR "DUMMY"='c' OR "DUMMY"='d'))
PS. I’m not sure, but as far as i remember, when I tested it on 10.2, it didn’t work with specifying a query block.
And I have never seen such usage.
How to find out session info about session that comes from remote database through dblink
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.
Standalone sqlplus script for plans comparing
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:
plans_active.sql:
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
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…