Search

OakieTags

Who's online

There are currently 0 users and 43 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Quiz night

Here’s a script to create a table, with index, and collect stats on it. Once I’ve collected stats I’ve checked the execution plan to discover that a hint has been ignored (for a well-known reason):

create table t2
as
select
        mod(rownum,200)         n1,
        mod(rownum,200)         n2,
        rpad(rownum,180)        v1
from
        all_objects
where
        rownum <= 3000
;

create index t2_i1 on t2(n1);

begin
        dbms_stats.gather_table_stats(
                user,
                't2',
                method_opt => 'for all columns size 1'
        );
end;
/

explain plan for
select  /*+ index(t2) */
        n1
from    t2
where   n2 = 45
;

select * from table(dbms_xplan.display);

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   120 |    15 |
|*  1 |  TABLE ACCESS FULL| T2   |    15 |   120 |    15 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N2"=45)

Of course we don’t expect the optimizer to use the index because we didn’t declare n1 to be not null, so there may be rows in the table which do not appear in the index. The only option the optimizer has for getting the right answer is to use a full tablescan. So the question is this – how come Oracle will obey the hint in the following SQL statement:


explain plan for
select
        /*+
                leading (t2 t1)
                index(t2) index(t1)
                use_nl(t1)
        */
        t2.n1, t1.n2
from
        t2      t2,
        t2      t1
where
        t2.n2 = 45
and     t2.n1 = t1.n1
;

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |   225 |  3600 |  3248 |
|   1 |  NESTED LOOPS                         |       |   225 |  3600 |  3248 |
|   2 |   NESTED LOOPS                        |       |   225 |  3600 |  3248 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2    |    15 |   120 |  3008 |
|   4 |     INDEX FULL SCAN                   | T2_I1 |  3000 |       |     8 |
|*  5 |    INDEX RANGE SCAN                   | T2_I1 |    15 |       |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T2    |    15 |   120 |    16 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."N2"=45)
   5 - access("T2"."N1"="T1"."N1")

I ran this on 11.2.0.4, but it does the same on earlier versions.

Update:

This was clearly too easy – posted at 18:04, answered correctly at 18:21. At some point in it’s evolution the optimizer acquired a rule that allowed it to infer unwritten “is not null” predicates from the join predicate.

 

 

 

LinkedIn Anniversary Messages

Thank you to everyone who has sent congratulations messages via LinkedIn, but I think LinkedIn might have mislead you somewhat… :)

When I left a previous job I switched my LinkedIn profile to say I was employed by ORACLE-BASE.com. That was in August 2008. I’ve never changed it since then, so now it says I’ve been “employed” by ORACLE-BASE.com for 6 years, hence the “anniversary”…

I bought my first domain and started putting articles on the net in July 2000. In August 2001 I renamed the site to oracle-base.com, so depending on how you look at it, the site is a little over 14 years old, or 13 with the current name…

So to everyone that has sent me a message congratulating me on my 6th anniversary, thinking that’s how long the website has existed, I would just like to point out is it the 14 year anniversary. :)

For those that think I’m employed by oracle-base.com, I’d just like to point out I’m not. I’ve got a regular job and I do the website for fun in my spare time. That’s why when you write to me asking for a job at oracle-base.com, I always say no. It’s not a company. It doesn’t employ anyone, not even me! :)

Anyway, sorry if LinkedIn has confused you, but thanks for the messages of support anyway. It’s very kind!

Cheers

Tim…


LinkedIn Anniversary Messages was first posted on August 21, 2014 at 9:05 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

AZORA Rises Like the Phoenix!

The Arizona Oracle User Group (AZORA) was reincarnated Tuesday July 29 by a group of six Oracle specialists interested in focusing the Oracle User Community in Arizona. A board was elected and began review of the organization bylaws (dated 1990) to be completed and presented at AZORA’s inaugural Educational Workshop tentatively scheduled for October. The new board includes: President, John King (King Training Resources); Vice-President Danny Carrizosa (OneNeck IT Solutions), Treasurer, Raj Chotalla (Intel), Secretary, Carlos Aquilar (GoNet USA), and Past-President Stephen Andert (IBM). Two of AZORA’s past presidents Steve Lemme (Oracle) and Stephen Andert (IBM)  are actively involved and lending their experiences and expertise. Visit the “AZORA – Arizona Oracle User Group” LinkedIn page to learn more. AZORA plans an educational activity to be scheduled in October; plans will be publicized, as they become firm. If you are (or know) an Oracle user in Arizona, go to the AZORA – Arizona Oracle User Group meetup page to join the group and keep informed of upcoming events.

Exciting Oracle Developments

The summer has brought us a new version of Oracle’s flagship database and the Introduction of Oracle Mobile Application Framework. Both, truly big news.

Oracle 12c 12.1.0.2 was released in July! This is a full release and includes many new features including:

  • In-Memory Database
  • Big Data SQL
  • JSON Support
  • RESTful Data Services
  • Improved Index Compression
  • Zone Maps
  • Attribute Clustering
  • Full Database Caching
  • Enhanced Multi-Tenant Features
  • Rapid Home Provisioning
  • Approximate Count Distinct

For complete documentation; see Oracle’s web site: http://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT003

In July the Oracle Mobile Framework (MAF 2.0) replaced and enhanced what was formerly known as Oracle ADF Mobile Framework and Oracle JDeveloper (12.3.1). Oracle did more than remove ADF from the name; Oracle Mobile Framework’s (MAF’s) enhancements include many bug fixes and new features like:

  • Java SE 8 support
  • Improved Maven interaction (many bugs fixed)
  • Critical Patches Downloadable from Oracle Update Center
  • REST services support
  • HTTP REST Analyzer
  • WADL Viewer Improvements
  • Web Socket Support
  • New Data Visualization (DVT) Charting improvements: Thematic Maps, New Gauges, New Deck Component, Table First Page Template, Client-Side CSS, Recursive Bounded Task Flows (BTFs), Debugger for Groovy, Groovy UI hints, support for Excel 2013 and Windows 8, New Desktop Components & Properties

For more information see the Oracle Web Sites: http://www.oracle.com/technetwork/developer-tools/maf/overview/index.html
http://www.oracle.com/technetwork/developer-tools/maf/overview/index.html and http://www.oracle.com/technetwork/developer-tools/maf/documentation/index.html”>http://www.oracle.com/technetwork/developer-tools/maf/documentation/index.html

Who deserves the title “Expert” or (even worse) “Guru”?

I wanted to expand on a thread in a LinkedIn group I’m part of, where one of the members wrote “It’s funny when 2 experts are arguing about who is better”, using Tom Kyte and Jonathan Lewis as examples of people they say are “Experts”.

Disclaimer: I have not spoken to Jonathan or Tom in regard to their viewpoints on this subject, so this shouldn’t be taken as them saying any of this, just my interpretation.

My perspective on that is I think you will rarely find two people who deserve the title of “Expert” will argue about “who is better”.  They would discuss the technical merits of a point instead.  In fact, most people I know that are generally accepted as “experts” really don’t believe they are, and most of them REALLY don’t like the term “Guru”.

I remember way back when at one of the early Hotsos conferences I was on a panel with Tom Kyte and someone asked a question on whether auto-extension for datafiles should be turned on or not (note I’m by no means saying I’m up there at Tom’s level!).  Tom and I had different perspectives because of the different situations we were in.  He was looking at the issue from the perspective of managing the AskTom site.  He had little time to do that, so switching on autoextend was a darn sight easier than having the system run out of space and generate errors.  My perspective was more from the viewpoint of sites that DID have DBA’s with time to manage the databases properly, where they could be warned by Enterprise Manager alerts that they were going to run out of space in time to address the issue.

In fact, both of us were right in our individual situations (the good old “It depends” answer!)  I still cherish the feedback we got from one attendee to the effect that it was great to see people respectfully disagreeing and backing up their perspectives with technical reasons, rather than just proclaiming they knew the answer and their answer was right.

Coming back to the original point of why people with deep technical knowledge like Jonathan and Tom don’t like the terms “Expert” or “Guru”, I think it’s because most of them believe (and rightly so) they are still on a life-long journey of learning.  What they ARE really good at is not just having that deep technical knowledge, but also in having the ability to pass that sort of knowledge on to us lesser mortals in terms that WE can understand and then apply in future.

The one place I can sort of understand those terms of “Expert” and “Guru” being applied is from a marketing perspective (well, at least for the term “Expert” – “Guru” sounds like you’re sitting on a mountain top somewhere to me! :) )  It can be much easier to make yourself marketable if you’re truly recognized as an expert.  But let’s face it – there are very few REAL experts out there.  The rest of us are on that journey towards becoming one.

That’s my two banana skins worth.  Feel free to add your own comments here as well!

 

Multitenant : PDB CONTAINERS Clause

I forgot to mention, I put another multitenant article live at the weekend.

I’m not sure I will ever use it, but it’s good to know it’s there.

I was originally working on an article on a completely different multitenant feature, but the examples I was using highlighted a bug, which kind-of scuppered that article. I’ve raised an SR and I’m waiting on the acknowledgement and possible fix. I’ll hold that article back until the fix is in place.

Cheers

Tim…


Multitenant : PDB CONTAINERS Clause was first posted on August 20, 2014 at 9:14 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

New Tips and Techniques page

In case you haven’t noticed it – and chances are you haven’t noticed because I just created it! :) – I’ve added a new page called “Tips and Techniques” to the website. It’s where I keep a random collection of odd items I’ve come across that might be of interest yet don’t deserve a full blog post. It’s sort of like a FAQ list, but not necessarily with the “F”. :)

You can reach it either via the link I’ve just given, or from the menu on the top of each page. I’ve only created a couple of entries so far, but will slowly add to that page as time goes on. Hope you find the tips and techniques of some use!

LOB Length

It’s funny how you can make little savings in work all over the place in Oracle if you’re prepared to look a little closely at what’s going on. Here’s a quirky little example with LOBs and function calls that might just have some greater relevance in other situations. Here’s a little data set, and two queries that I might run against it:


create table tbl(
	c1      clob
)
lob (c1) store as c_lob(
	disable storage in row
	nocache nologging
)
;

begin
	for i in 1..128 loop
		insert into tbl values(rpad('x',4000));
		commit;
	end loop;
end;
/

commit;

-- collect stats, prime dictionary cache and library cache
-- run both queries twice and check stats on second run

select
	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
from
	(
	select
		/*+ no_merge */
		dbms_lob.getlength(c1) len
	from
		tbl
	)
where
	len > 3960
;

select
	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
from
	(
	select
		rownum rn, dbms_lob.getlength(c1) len
	from
		tbl
	)
where
	len > 3960
;

The question that you might ask yourselves when you see these queries is: will they do similar amounts of work. Of course, I wouldn’t be asking the question if the answer were yes. Despite the no_merge() hint, which you might think would have the same effect as the rownum approach, Oracle seems to execute the call to dbms_lob.getlength() twice for each row in the first query, but only once per row for the second query. Here are the stats (from autotrace) on the second run of the two queries when autotrace is enabled:


Statistics (for no_merge)
----------------------------------------------------------
         40  recursive calls
          0  db block gets
        271  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Statistics (for rownum)
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        131  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, the consistent gets for the no_merge() approach is roughly double that for the rownum approach – and since we have 128 rows/LOBs in the table that looks suspiciously like 2 gets vs. 1 get per LOB depending on the approach – which suggests two calls to the function. This is further corroborated by the execution plans, and especially by the predicate sections (how often have I said “always check the predicates”) which show that the predicate has been pushed inside the view that’s been hinted to be non-mergeable, but it hasn’t been pushed inside the view that uses the rownum instantion trick:


Execution Plan for no_merge()
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    13 |            |          |
|   2 |   VIEW              |      |     6 |    78 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TBL  |     6 |   522 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DBMS_LOB"."GETLENGTH"("C1")>3960)

Execution Plan for rownum
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    13 |            |          |
|*  2 |   VIEW               |      |   128 |  1664 |     2   (0)| 00:00:01 |
|   3 |    COUNT             |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TBL  |   128 | 11136 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("LEN">3960)

My first thought on seeing this difference was to apply the /*+ no_push_pred */ hint to block predicate pushing – but then I remembered that the hint is about join predicate pushing and this is a simple filter push. A quick search of the hidden parameters, though, revealed this:

_optimizer_filter_pushdown : enable/disable filter predicate pushdown

Setting this parameter to false – either through a call to ‘alter session’ or through an /*+ opt_param( opt_param(‘_optimizer_filter_pushdown’ , ‘false’) */ hint – allowed the no_merge approach to produce the same plan and resource usage as the rownum approach. Of course, for a production system, I’d probably use the rownum approach rather than mess around with hidden parameters.

Footnote:

I don’t know why the code with the no_merge() approach reported 40 recursive calls (on its first execution with autotrace). A couple of variations on the experiment suggested that it had something to do with the number of rows (or consequential buffer visits) that survived the predicate call – for a sufficiently small number of rows the recursive call count happened to drop to zero; but the phenomenon needs further investigation.

Zone Map Zone ID's

Just a quick follow up to my previous post on how Zone ID's are calculated.

Let's take the following example:

SQL> select rid, sys_op_zone_id(rid) zone_id
2 from
3 (
4 select chartorowid('AAAS5KAAHAAABYDAAA') rid
5 from dual
6 );

RID ZONE_ID
------------------ ------------
AAAS5KAAHAAABYDAAA 324580438021


Recalling that extended ROWID has the following format (a nice picture from Oracle Documentation):



In the binary format that would correspond to:

  • Data Object Number -- 32 bits
  • Relative File Number -- 10 bits
  • Block Number -- 22 bits
  • Row Number -- 16 bits

We know that Row Number is irrelevant for the Zone Maps because they deal with block ranges. With that in mind a simple conversion to a (base 10) number would be:

(Block Number) + (Relative File Number) * 2 ^ 22 + (Data Object Number) * 2 ^ 32

Applying the above formula to our ROWID:



SQL> select
2 dbms_rowid.rowid_block_number(rid) +
3 dbms_rowid.rowid_relative_fno(rid) * power(2,22) +
4 dbms_rowid.rowid_object(rid) * power(2,32) base_10
5 from
6 (
7 select chartorowid('AAAS5KAAHAAABYDAAA') rid
8 from dual
9 );

BASE_10
---------------
332370368534019


What's left after that is to slice the number into Zone Map chunk size (2^10 by default, thanks to Timur for pointing that out in the comments sections for the previous post):

SQL> select
2 trunc((
3 dbms_rowid.rowid_block_number(rid) +
4 dbms_rowid.rowid_relative_fno(rid) * power(2,22) +
5 dbms_rowid.rowid_object(rid) * power(2,32)
6 )/power(2,10)) zone_id,
7 sys_op_zone_id(rid) sys_zone_id
8 from
9 (
10 select chartorowid('AAAS5KAAHAAABYDAAA') rid
11 from dual
12 );

ZONE_ID SYS_ZONE_ID
------------ ------------
324580438021 324580438021


That's all there is to it!






Just Don’t Call It Science!

I’ve had two rather frustrating conversations recently with people who have claimed “science” and “proof” where it is totally inappropriate. One was regarding a 3000 year old religious text. The other was a bunch of medical-related blog posts. In both cases, the people were making very valid points and thought they were backing up their stance with “scientific proof”. The problem was, by inappropriately playing the science card, they made themselves and their beliefs sound stupid. Like I said, in both cases they were making very valid points, but their supporting arguments were just plain dumb!

I really do feel most people out there do not have a clue what science and scientific methods actually are. Just to bring this closer to home, I would just like to make this point. Please read this next line and the following explanation before you feel the need to comment…

I don’t think there is an Oracle blogger that I follow that is writing blog posts worthy of being called “science”.

That comment is not meant to shock and offend. I’m not trying to sound elitist, because I certainly rate much lower on the science scale than many other bloggers. The point of that comment is to put things into perspective. The scientific approach to gathering, validating and presenting information is a very formal affair. If you’ve experienced a research-based (not taught) Masters or PhD you will have just scratched the surface of what I’m talking about. If you’ve not experienced science to at least that level, I’m not sure you are really in a position to judge the extent of what people mean when they say “scientific proof”.

At this point I think the audience response will be split amongst these types of reaction.

  • People who saw the word “religious” in the second sentence, instantly jumped to a conclusion about what this post is about and either stopped reading or started to prepare their counter argument.
  • People who have a “favourite blogger” who *they believe* to have a super-scientific approach to Oracle and are desperate to tell me so.
  • People who follow “popular science” and believe the watered down crap they present in those articles and documentaries resembles real science and therefore think they know about science and think I’m talking crap.
  • People who just don’t give a damn and will happily call what they read in blog posts and wikipedia science, regardless of source, scientific approach etc.
  • People who understand what I’m talking about and realise we all just producing useful and informative content, but are not living up to the rigorous standards that would be required to call our tests and content “science” or “scientific proof”.

I think everyone is entitled to their own opinion, educated or not, but what I find it really hard to tolerate is when people start playing the science card where it is not appropriate. Do that and I’m gonna call bullshit every time!

Cheers

Tim… (Not a “Computer Scientist”, “Data Scientist”, “Software Engineer”, “Data Architect” or any of those other bullshit titles that offend the professions they reference)

 


Just Don’t Call It Science! was first posted on August 19, 2014 at 8:58 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.