Feed aggregator

When Less Is Best

Floyd Teter - Mon, 2020-02-17 17:47
Late in 2019, Apple obsoleted my trusty iPad Mini II.  No more OS updates, may not run the latest apps, etc.  So I figured it was time for a new iPad... right up until the time I saw the prices. Yikes!!!

The expense of a new iPad, even a Mini, made me step back and reconsider my approach.  I started by considering my uses of the iPad.  I don't really create much with an iPad - it's just too limited for the type of work I do. And I don't communicate much with it, as I have an iPhone for calls, txt, and video chats. I use my iPad to consume:  books, movies, social media and news.

Once I understood my own use cases for the iPad, I realized I could fill my needs with a much less expensive device.  I settled on an Amazon Fire HD 8 (thanks to my kids for the nifty Christmas present).  And it works great for what I do, especially after setting it up to side load from Google Play.  My old Mini has been retired to a life as a digital photo frame and I'm really happy with the Fire.  Is the Fire as elegant of an experience as the Mini?  Not by a long shot.  But it gets the job done quite well.  And, at last check, the Fire is about 20% of the price of a new iPad Mini.  A great example of less being best.

I see this quite a bit in the way users work with enterprise applications - in my specific case, observations are from helping customers with Oracle HCM Cloud Enterprise Applications.  Users expend significant costs in terms of time and labor facilitating some very complicated use cases, many of which are either unnecessary or rarely encountered.  When we collaborate with those customers on those use cases, the end result is often that less is best.  Often they're focused on a specific use case because they have not considered another process for getting the job done. Other times it's a matter of the use case not being as critical to the business as originally thought.  And sometimes it's due to a legal or industry requirement that no longer exists.  And there are many more causes.  But it always makes my day when the lightbulb comes on and people realize they can make do quite well with much less than they originally thought.

I've seen this surface frequently in working with customers on the mobile-enabled Responsive UX.  Simplification was a driving design concept in building out Responsive UX, which means some less used and less important features were culled from the product.  Less is best, based on the feedback we've received from customers who have adopted the new UX.

So the upshot here?  Step back, take a breath, and think about how you use your enterprise apps.  Can you find instances where less would be best?  I'll bet you find more than one.

Join Elimination bug

Jonathan Lewis - Mon, 2020-02-17 09:37

It is possible to take subquery factoring (common table expressions / CTEs) too far. The most important purpose of factoring is to make a complex query easier to understand – especially if you can identify a messy piece of text that is used in more than one part of the query – but I have seen a couple of patterns appearing that make the SQL harder to read.

  • In one pattern each table is given its own factored subquery holding the non-join predicates (and, possibly, filter subqueries) and then the main query is just a simple join of the factored subqueries with nothing but join (ON) predicates.
  • In another pattern each factored subquery consists of the previous subquery with one more table added to it, so every subquery is no more that a “two-table” query block and the final subquery is a simple “select from last_factored_subquery”.

Neither of these patterns is helpful – but today’s blog note is not going to be about going to extremes with subquery factoring; instead it’s an example of a fairly reasonable use of subquery factoring that ran into a “wrong results” bug.

Consider a system that collects data from some type of meters. Here’s a two-table definition for meters and meter readings:


create table meters (
        meter_id        number,
        meter_type      varchar2(10),  -- references meter_types
        date_installed  date,
        padding         varchar2(100),
        constraint met_pk primary key(meter_id)
)
;

create table meter_readings (
        meter_id        number,
        date_read       date,
        reading         number(10,3),
        padding         varchar2(100),
        constraint      mrd_pk primary key(meter_id, date_read),
        constraint      mrd_fk_met foreign key (meter_id) references meters
)
;

insert into meters
select
        1e6 + rownum,
        case mod(rownum,3)
                when 0 then 'A'
                when 1 then 'B'
                       else 'C'
        end,
        trunc(sysdate) - mod(rownum,5),
        rpad('x',100,'x')
from
        dual 
connect by 
        level <= 10
;

execute dbms_stats.gather_table_stats(null,'meters')

insert into meter_readings 
select
        met.meter_id,
        met.date_installed - v.id + 2,
        dbms_random.value,
        rpad('x',100,'x')
from
        meters met,
        (select rownum id from dual connect by level <= 4) v
;

commit;

execute dbms_stats.gather_table_stats(null,'meter_readings')

I’ve picked the obvious primary keys for the two tables and defined the appropriate referential integrity constraint – which means the optimzer should be able to choose the best possible strategies for any query that joins the two tables.

I’ve created a very small data set – a few meters installed in the last few days, and a few readings per meters over the last few days. So lets report the readings for the last 48 hours, and include in the output any meters that haven’t logged a reading in that interval.

Here’s the query I wrote, with its output, running on a 19.3 instance on 17th Feb 2020:


with mrd_cte as (
        select 
                meter_id, date_read, reading
        from 
                meter_readings
        where 
                date_read in (trunc(sysdate), trunc(sysdate)+1)
)
select
        met.meter_id, met.date_installed, mrd_cte.date_read, reading
from
        meters met
left join 
        mrd_cte
on      mrd_cte.meter_id = met.meter_id
;

  METER_ID DATE_INST DATE_READ    READING
---------- --------- --------- ----------
   1000001 16-FEB-20 17-FEB-20       .063
   1000002 15-FEB-20
   1000003 14-FEB-20
   1000004 13-FEB-20
   1000005 17-FEB-20 18-FEB-20        .37
   1000005 17-FEB-20 17-FEB-20       .824
   1000006 16-FEB-20 17-FEB-20       .069
   1000007 15-FEB-20
   1000008 14-FEB-20
   1000009 13-FEB-20
   1000010 17-FEB-20 17-FEB-20       .161
   1000010 17-FEB-20 18-FEB-20       .818

12 rows selected.

The query returns 12 rows – which SQL*Plus can report because it counts them as it fetches them so it can give you the total at the end of the query.

Of course, sometimes people write preliminary queries to find out how big the result set would be before they run the query to acquire the result set itself. In cases like that (where they’re just going to select a “count(*)” the optimizer may a choose different execution path from the base query – perhaps finding a way to do an index-only execution, and maybe eliminating a few table joins from the query. So let’s execute a count of the above query:


rem
rem     Script:         join_elimination_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem

with mrd_cte as (
        select 
                meter_id, date_read, reading
        from 
                meter_readings
        where 
                date_read in (trunc(sysdate), trunc(sysdate)+1)
)
select count(*) from (
        select
                met.meter_id, met.date_installed, mrd_cte.date_read, mrd_cte.reading
        from
                meters met
        left join 
                mrd_cte
        on      mrd_cte.meter_id = met.meter_id
)
;

  COUNT(*)
----------
        10

1 row selected.

You’ll have to take my word for it, of course, but no-one else was using this database while I was running this test, and no-one else has access to the schema I was using anyway. Unfortunately when I count the 12 rows instead of reporting them Oracle thinks there are only 10 rows. Oops!

Step 1 in investigating the problem – check the execution plans to see if there are any differences in the structure of the plan, the use of predicates, or the outline information. I won’t bother with the plan for the base query because it was very obvious from the count query where the problem lay.


-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| MET_PK |    10 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$69B21C86")
      ELIMINATE_JOIN(@"SEL$00F67CF8" "METER_READINGS"@"SEL$1")
      OUTLINE(@"SEL$00F67CF8")
      MERGE(@"SEL$C43CA2CA" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$C43CA2CA")
      MERGE(@"SEL$D28F6BD4" >"SEL$E6E74641")
      OUTLINE(@"SEL$E6E74641")
      ANSI_REARCH(@"SEL$4")
      OUTLINE(@"SEL$D28F6BD4")
      MERGE(@"SEL$1" >"SEL$006708EA")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$006708EA")
      ANSI_REARCH(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$3")
      INDEX(@"SEL$69B21C86" "MET"@"SEL$3" ("METERS"."METER_ID"))
      END_OUTLINE_DATA
  */

This is the plan as pulled from memory by a call to dbms_xplan.display_cursor(). We note particularly the following: meter_readings doesn’t appear in the plan, there is no predicate section (and no asterisks against any of the operations that would tell us there ought to be some predicate information), and there’s a very revealing ELIMINATE_JOIN(@”SEL$00F67CF8″ “METER_READINGS”@”SEL$1”) in the outline information.

For some reason the optimizer has decided that it’s okay to remove meter_readings from the query (even though there may be many meter readings for each meter), so it was inevitable that it produced the wrong result.

Despite my opening note, this is not an issue with subquery factoring – it just looked that way when I started poking at the problem. In fact, if you rewrite the query using an inline view you get the same error, if you turn the inline view into a stored view you get the error, and if you turn the whole query into a simple (left) join with the date predicate as part of the ON clause you still get the error.

The problem lies somewhere in the join elimination transformation. If you go back to the outline information from the bad plan you’ll see the line: ELIMINATE_JOIN(@”SEL$00F67CF8″ “METER_READINGS”@”SEL$1”) – by changing this to NO_ELIMINATE_JOIN(…) and adding it to the main query block I got a suitable plan joining the two tables and producing the right result.

The problem appears in 12.2.0.1 and 19.5.0.0 (tested on livesql) – but does not appear in 12.1.0.2 or 11.2.0.4

There is a known bug associated with this problem:

Bug: 29182901
Abstract: WRONG COUNT WAS RETURNED WHEN _OPTIMIZER_ENHANCED_JOIN_ELIMINATION=TRUE
Query with Outer Join Returned a Wrong Result due to Join Elimination (Doc ID 29182901.8)

The bug is fixed in 20.1, with a fix that has been backported into the Jan 2020 patches for 19, 18, and 12.1

Is tech supporting Google? It sure doesn’t appear so.

Oracle Press Releases - Mon, 2020-02-17 08:00
Blog
Is tech supporting Google? It sure doesn’t appear so.

Ken Glueck, Executive Vice President, Oracle—Feb 17, 2020

There is a lot of chatter that “tech” is supportive of Google’s position in Google v. Oracle. That was certainly the impression Google tried to spin when its Amici filed briefs with the Court last month. But a closer inspection of Google’s Amici makes clear that the technology community is not supporting Google’s position. Not even slightly.

As we stated in our prior blog post, the issues of copyrightability of Java software have been settled since the Federal Circuit Decision in 2014. It is Google that is urging the Supreme Court to draw some new magic line between some code that is copyrightable and other code that isn’t. Google tries to create a sense of urgency where none exists and controversy where there is actually agreement. And a close read of Google’s Amicus briefs reveals that Google appears to be virtually alone—at least among the technology community—in seeking to weaken copyright for software.

We also highlighted in our prior blog that Google has not provided a single real-world example of innovation that has suffered due to the pendency of this case. In the intervening six years since 2014—there is zero contemporaneous evidence where anyone has identified the Federal Circuit Decision as a barrier or impediment to innovation. Which is exactly why Google is receiving such scant support from tech.

When you look at the Amici filing on Google’s behalf what’s noteworthy is not who did file, but who didn’t. If you take a quick look at the top 100 technology companies, exactly two of them filed briefs on Google’s behalf. If you look at the leading Silicon Valley-based companies, exactly none of them filed briefs on Google’s behalf. And among the major technology trade associations, same answer. Not one.

This becomes much more significant when you understand just how hard Google tried to get the technology industry’s support… but it was not forthcoming.

The impression that tech is supporting Google comes primarily from the fact that Microsoft and IBM submitted Amicus briefs on Google’s behalf. We suppose one could argue that the Computer and Communications Industry Association (CCIA) also purportedly represents tech, but that’s not entirely correct (more on them later).

We should start by saying that Microsoft and IBM are entitled to their opinions and Microsoft and IBM are both great partners and strong competitors of Oracle. But the fact is both Microsoft and IBM have commercial interests in this litigation that require a little context and perspective.

We address Microsoft, IBM and CCIA, in turn.

Let’s start with Microsoft, the original sinner.

First, let’s go back to U.S. v. Microsoft. We would encourage everyone to take a look at the Findings of Fact in U.S. v. Microsoft, in particular Microsoft’s anticompetitive conduct against Sun’s Java. Long before Google broke Java’s interoperability, Microsoft did exactly the same thing. Java’s “write once, run anywhere” innovation was antithetical to maintaining Microsoft’s barrier of entry to its Windows monopoly. So, it made just enough changes and created just enough dependencies that applications written in Sun-compliant Java wouldn’t run on Windows. In other words, they broke Java’s interoperability. Sound familiar?

Second, Microsoft actually filed an Amicus brief on Oracle’s side of this matter in 2013 before the Federal Circuit. Likewise, Microsoft’s primary trade association, the Business Software Alliance also supported Oracle’s side before the Federal Circuit on both copyrightability and on fair use.

It is essential to read Microsoft’s new brief. How does it reconcile its previous position with its new position? It doesn’t. On the critical question of the copyrightability of “interfaces” (which Microsoft previously supported) Microsoft is now completely silent. Instead, its new brief focusses exclusively on fair use. So, does Microsoft support Google’s position of picking and choosing which lines of software code are copyrightable and which are not? The entirety of the public record is clear that Microsoft is actually on Oracle’s side of this critical component of the case.

Third, let’s not forget that Microsoft itself was once among Google’s strongest antagonists. Remember Microsoft’s 2013 “Scroogled” ad campaign against Google? As Ad Age put it, “the commercials are hard hitting, beating up Google for everything from invasive ads in Gmail to sharing data with app developers to placing paid results on its search page. Google, the ads claim, is "Scroogling" its users, or exploiting their private data to maximize advertising profits.”

Then came 2015 and the commercial agreement between Microsoft and Google to partner on, among other things, intellectual property. What changed Microsoft’s stance in this litigation was that commercial agreement. Microsoft’s position is as principled as that.

Now to IBM, before there was Codebreak (Houston Astros) there was Jailbreak.

Let’s start with the fact that IBM was silent both times Oracle v. Google was argued at the Federal Circuit. No Amicus Brief from IBM whatsoever on either copyrightability or fair use. It’s not as if IBM wasn’t paying attention or lacked the resources to participate. Moreover, IBM is an active member of the Business Software Alliance which, as we stated, filed a brief on both copyrightability and fair use in favor or Oracle’s position at the Federal Circuit. No objection from IBM.

Next, let’s not forget that IBM has been working to control Java since Java’s inception. In fact, as reported in the New York Times, IBM had negotiated a deal to buy Sun for $7 billion which would, among other things, “give I.B.M. more strength in competing against Oracle” and of course would have given them control of Java.

In the end, that transaction didn’t quite work out for IBM because Oracle acquired Sun, which Sun concluded would be a far better fit.

Third, IBM spent years trying to undermine Sun’s stewardship of Java in a scheme known in the industry as “Jailbreak.” Oracle knows well IBM’s efforts here because we were part of the community. The “Jailbreak” initiative as IBM named it was meant to pressure Sun and its leadership into changing the licensing regime for Java to something which would give IBM more control and the ability to “fork” Java for its own commercial purposes.

Ultimately IBM abandoned Jailbreak because it fully understood that creating a fork of Java would require a license from Sun. We were there at the time. Unlike Google, IBM never argued that some code was copyrightable but other code was not. Unlike Google, IBM never thought copying Java was permissible under fair use. IBM knew full well it needed a license and as a result it abandoned the Jailbreak effort.

So, after sitting silent before the Courts for a decade, with a clear understanding of what is permissible and what isn’t, why does IBM all of a sudden decide this case “threatens to undermine and adversely impact a core aspect of IBM’s… business” when it hadn’t at any point in the past?

Because IBM just completed its blockbuster $34 billion bet-the-company acquisition of Red Hat, and is simply running out of time. The stakes are now higher and IBM really wants its own, non-compatible version of Java for its own commercial purposes. After failing to acquire Sun or to Jailbreak Java, IBM is now turning to the Supreme Court in a Hail Mary attempt to get the Court to give it the control it couldn’t achieve in the market.

Again, IBM is entitled to its opinion. But IBM has a long history with Java, and we shouldn’t confuse IBM’s commercial and competitive interests with a new-found interest in the proper balancing of copyright protection for software.

Lastly, let’s address CCIA.

It is hardly a secret that CCIA is completely beholden to Google, financially and otherwise. Now, we don’t want to throw stones, but in a town where “pay to play” is commonplace, CCIA really sets a whole new standard for transactional advocacy. CCIA was founded on the principles of competition and interoperability and was one of the main protagonists in U.S. v. Microsoft, and before that AT&T and IBM. So how does CCIA go from fighting for interoperability to defending Google who admittedly broke interoperability with Java?

This author knows a little bit about the governance of CCIA because Oracle used to support CCIA and I sat on its Board of Directors during this formative about-face. The first step was Microsoft simply wrote CCIA’s CEO a check for $10 million (for his personal benefit, not the organization). Then Google stepped in and is now one of CCIA’s primary financial backers.

On the substance, CCIA’s argument essentially boils down to the point that the U.S. legal framework for IP should be more like Europe. If we care about innovation, we can’t imagine why anyone would trade the U.S. system, the global leader in technology, for Europe’s system, the global laggard? But CCIA is entitled to its views.

With that background, is it really credible to say that when 98 of the top 100 technology companies are absent; when Microsoft and IBM have substantial parochial agendas; and when nearly all of the major technology advocacy groups are silent, that “tech” is on Google’s side?

The answer is of course no. There is no outpouring of support for Google’s views.

And the reason is that despite the sky is falling rhetoric, this case is fact bound. Google stole and copied verbatim more than 11,000 lines of software code. It is now trying to define its’ copying out of the Copyright Act or alternatively claim fair use.

Nice new look and feel to spring.io web site!!!!

Pas Apicella - Sun, 2020-02-16 22:06
Seen the new look and feel for the spring.io? Worth a look

https://spring.io/



Categories: Fusion Middleware

Note to self for how to easily add user to sudoers file

Michael Dinh - Sun, 2020-02-16 07:49

Unable to sudo su -:

[grid@ol7-fpp-fpps ~]$ sudo su -

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for grid:
grid is not in the sudoers file.  This incident will be reported.

[grid@ol7-fpp-fpps ~]$ 

Group wheel has ALL privileges:

[root@ol7-fpp-fpps ~]# grep wheel /etc/sudoers
## Allows people in group wheel to run all commands
%wheel  ALL=(ALL)       ALL
# %wheel        ALL=(ALL)       NOPASSWD: ALL
[root@ol7-fpp-fpps ~]#

Modify user grid:

[root@ol7-fpp-fpps ~]# usermod -aG wheel grid

Or: usermod -a -G sudo user

Test sudo -:

[grid@ol7-fpp-fpps ~]$ sudo su -
[sudo] password for grid:
Last login: Sun Feb 16 08:35:52 -05 2020 on pts/0
[root@ol7-fpp-fpps ~]#

[1Z0-238] Oracle EBS R12 Certified Specialist Everything You Must Know About

Online Apps DBA - Sun, 2020-02-16 02:29

The Oracle E-Business Suite R12 Applications Database Administrator Certified Professional Certification is designed for individuals who possess a strong foundation and expertise in implementing Oracle E-Business Suite solutions. This exam is required as part of earning the new Oracle EBS R12 Applications Database Administrator Certified Professional certification. Check Out K21 Academy’s blog post at https://k21academy.com/appsdba65 […]

The post [1Z0-238] Oracle EBS R12 Certified Specialist Everything You Must Know About appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Left padded String based on sub-string length

Tom Kyte - Fri, 2020-02-14 21:11
Hi Chris, I want to restrict the length of the input string to 8 characters by adjusting all the digits (after 'MFT' in below example) from Input string. Means, want to accommodate all the digits in string. Ex.1. Input String is 'MFT123456' ...
Categories: DBA Blogs

Indexing strategy for dates in a query

Tom Kyte - Fri, 2020-02-14 21:11
Hello, Ask Tom team. I have the following query: <code>SELECT guid, sender_id, doc, status, arrived_date, register_date, last_updated_date FROM user1.table1 WHERE (sender_id=:SENDER OR :SENDER IS NULL ) AND (status=:STATUS OR :status IS NU...
Categories: DBA Blogs

Average of 0 and Value - gives incorrect output. Is there a way to ignore the 0 during the average function.

Tom Kyte - Fri, 2020-02-14 21:11
Hi Tom, I am having a SQL output as follows. <code>A B C D E ---------------- ---------- ---------- ------------ ----------- 2020-02-12 221 68677 99.6...
Categories: DBA Blogs

AWR records top 30 SQLs by default

Bobby Durrett's DBA Blog - Fri, 2020-02-14 16:22

I forget that Oracle’s AWR only records the top 30 SQL statements in each snapshot by default. I am not sure how long this link will last but here is a 19c manual page describing the default: 19c manual – see the topnsql setting. A lot of my query tuning assumes that the problem query is in the AWR but for very efficient queries on active systems they may mysteriously disappear or be absent from the AWR. It sometimes takes me a while to remember that the snapshots only include a fixed number of SQLs.

I use my sqlstat3.sql query to look at a history of a particular sql_id’s executions. Often it shows the query running faster on one plan_hash_value than another. Then I look at why the sql_id changed plans. But what about when the good plan does not show up at all? Several times I have looked at sqlstat3.sql output and thought that a query had not run in the past with an efficient query even though it had. It had run so efficiently that it was not on the report, so it looked like the query was a new, slow, SQL statement.

Often I will fix a query’s plan with a SQL Profile and rerun sqlstat3.sql on a busy system after manually running dbms_workload_repository.create_snapshot to capture the most recent activity and the problem query with the new plan will not show up. Usually I remember that it is not in the top 30 queries and that is why it is missing but sometimes I forget. Here is a partial sqlstat3.sql output showing a long running SQL disappearing after I fixed its plan on Wednesday:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Ave ms
------------- --------------- --------------------- ---------------- ------------------
acn0557p77na2      3049654342 12-FEB-20 05.00.01 AM                1          16733.256
acn0557p77na2      3049654342 12-FEB-20 06.00.03 AM                2           49694.32
acn0557p77na2      3049654342 12-FEB-20 07.00.53 AM                6          47694.527
acn0557p77na2      3049654342 12-FEB-20 08.00.54 AM               11         50732.0651
acn0557p77na2      3049654342 12-FEB-20 09.00.33 AM               15         53416.5183
acn0557p77na2      3049654342 12-FEB-20 10.00.43 AM               21         86904.4385
acn0557p77na2      3049654342 12-FEB-20 11.00.02 AM               27          84249.859
acn0557p77na2      3049654342 12-FEB-20 12.00.20 PM               27         125287.757
acn0557p77na2      3049654342 12-FEB-20 01.00.36 PM               69         156138.176

Sometimes I query the V$ tables to verify it is currently running a good plan. Here is example output from vsqlarea.sql showing the good plan running today.

LAST_ACTIVE         SQL_ID        PLAN_HASH_VALUE Avg Elapsed ms
------------------- ------------- --------------- --------------
2020-02-14 16:11:40 acn0557p77na2       867392646             14

This is just a quick note to me as much as anyone else. A query that is missing from an AWR report or my sqlstat3.sql report may not have run at all, or it may have run so well that it is not a top 30 query.

Bobby

Categories: DBA Blogs

Char problems

Jonathan Lewis - Fri, 2020-02-14 09:25

The semantics of comparing character columns of different types can lead to some confusion, so before I get into the main body of this note here’s a little test based on a table with one row:


create table t1(c2 char(2), c3 char(3), vc2 varchar2(2), vc3 varchar2(3));

insert into t1 values ('XX','XX','XX','XX');
commit;

select count(*) c2_c3   from t1 where c2 = c3;
select count(*) c2_vc3  from t1 where c2 = vc3;
select count(*) c3_vc2  from t1 where c3 = vc2;
select count(*) c3_vc3  from t1 where c3 = vc3;

I’ve inserted one row, using the same value for every single column; then I’ve been counting the row(s) where various pairs of columns match. Which (if any) of the four queries return the value 1 and which (if any) return the value zero ?

To help you, here’s a quote from the relevant Oracle manual about character comparison semantics:

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

The first two queries return 1, the second two return zero.

  1. Query 1: c2 is blank padded to match c3 in length before the comparison, so the values are ‘XX {space}’
  2. Query 2: c2 is not padded, so the compared values are both ‘XX’
  3. Query 3: c3 is three characters long, vc2 is only 2 characters long and does not get padded to match c3
  4. Query 4: c3 is three characters long, vc3 is only 2 characters long and does not get padded to match c3

One interesting by-product of this example is this:

  • c3 = c2 and c2 = vc3 but c3 != vc3     whatever happened to transitive closure!

So we come to the point of the article, which is this:

Be very careful about using char() (or nchar) types in your tables – especially if you’re thinking of using columns of type [n]char() in join predicates (or predicates that don’t start life as join predicates but become join predicates through transitive closure).

Here’s an interesting bug that has appeared (I think) as a side effect of the need for blank-padded semantics. We start with two tables that I’ll be joining with a hash join – one of them will be a small table that will be used as the “build” table, the other will be (faked to look like) a large table that will be used as the “probe” table.


rem
rem     Script:         bloom_prune_char_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

create table test_probe(status char(3)) partition by list(status) (partition st_1 values('00','BI'));

create table test_build(status char(2)); 

insert into test_build values('00');
insert into test_probe values('00');
insert into test_build values('BI');
insert into test_probe values('BI');

commit;
 
prompt  =====================================
prompt  Fake large table stats for test_probe
prompt  =====================================

exec dbms_stats.set_table_stats(null,'test_probe',numrows=>2000000);

spool bloom_prune_char_bug
set linesize 156
set pagesize 60

set serveroutput off

select  /*+ 
                gather_plan_statistics 
        */
        * 
from 
        test_build b,
        test_probe a 
where 
        a.status = b.status
;

select * from table(dbms_xplan.display_cursor(null,null,'projection partition allstats last'))
/


The two tables have a pair of matching rows – so the query should return two rows. But it doesn’t – it returns no rows, and the clue about why not is in the execution plan (which I’ve pulled from memory with lots of extra bits and pieces). Here’s the output from running this script (from the query onwards) on an instance of 12.2.0.1:


no rows selected


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID  2295z4p6m4557, child number 0
-------------------------------------
select /*+   gather_plan_statistics  */  * from  test_build b,
test_probe a where  a.status = b.status

Plan hash value: 177769189

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |      1 |        |       |       |      0 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN               |            |      1 |   2000K|       |       |      0 |00:00:00.01 |       7 |  2078K|  2078K|  766K (0)|
|   2 |   PART JOIN FILTER CREATE| :BF0000    |      1 |      2 |       |       |      2 |00:00:00.01 |       7 |       |       |          |
|   3 |    TABLE ACCESS FULL     | TEST_BUILD |      1 |      2 |       |       |      2 |00:00:00.01 |       7 |       |       |          |
|   4 |   PARTITION LIST SINGLE  |            |      1 |   2000K|KEY(AP)|KEY(AP)|      0 |00:00:00.01 |       0 |       |       |          |
|   5 |    TABLE ACCESS FULL     | TEST_PROBE |      0 |   2000K|     1 |     1 |      0 |00:00:00.01 |       0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."STATUS"="B"."STATUS")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "B"."STATUS"[CHARACTER,2], "A"."STATUS"[CHARACTER,3]
   2 - INTERNAL_FUNCTION("B"."STATUS")[2], INTERNAL_FUNCTION("B"."STATUS")[2], "B"."STATUS"[CHARACTER,2]
   3 - "B"."STATUS"[CHARACTER,2]
   4 - (rowset=256) "A"."STATUS"[CHARACTER,3]
   5 - (rowset=256) "A"."STATUS"[CHARACTER,3]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The optimizer has used a Bloom filter to do partition pruning, and while we can see operation 4 reporting a “partition list single” operation using “and pruning” (AP), we can see that operation 5 reports zero starts. This is because the Bloom filter has been used to determine that there are no relevant partitions!

Looking down at the (rarely examined) projection information we can see why – operation 2 (the “part join filter create”) has a strange “Internal Function” in its projection, and references B.STATUS as character[2]. It looks as if the Bloom filter that identifies partitions has been built using a char(2) as the input to its hashing function – which is bad news when the resulting filter is used to check the hash values returned from the partition definition that is hash a char(3).

If my thoughts about the mismatch in how the Bloom filters for the build and probe tables are built then a test that would help to confirm the hypothesis would be disable Bloom filter pruning – which you can only do by setting a hidden parameter, possibly in a hint or SQL Patch):

select 
        /*+ 
                gather_plan_statistics 
                opt_param('_bloom_pruning_enabled','false') 
        */  
        * 
from 
        test_build b,
        test_probe a 
where
        a.status = b.status;

select * from table(dbms_xplan.display_cursor(null,null,'projection partition allstats last'))
/


ST STA
-- ---
00 00
BI BI

2 rows selected.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  9whuurpkm3wpw, child number 0
-------------------------------------
select  /*+   gather_plan_statistics
opt_param('_bloom_pruning_enabled','false')   subquery_pruning(a)  */
* from  test_build b,  test_probe a where  a.status = b.status

Plan hash value: 787868928

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |       |       |      2 |00:00:00.01 |      45 |       |       |          |
|*  1 |  HASH JOIN             |            |      1 |   2000K|       |       |      2 |00:00:00.01 |      45 |  2078K|  2078K|  866K (0)|
|   2 |   TABLE ACCESS FULL    | TEST_BUILD |      1 |      2 |       |       |      2 |00:00:00.01 |       7 |       |       |          |
|   3 |   PARTITION LIST SINGLE|            |      1 |   2000K|     1 |     1 |      2 |00:00:00.01 |      38 |       |       |          |
|   4 |    TABLE ACCESS FULL   | TEST_PROBE |      1 |   2000K|     1 |     1 |      2 |00:00:00.01 |      38 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."STATUS"="B"."STATUS")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "B"."STATUS"[CHARACTER,2], "A"."STATUS"[CHARACTER,3]
   2 - (rowset=256) "B"."STATUS"[CHARACTER,2]
   3 - (rowset=256) "A"."STATUS"[CHARACTER,3]
   4 - (rowset=256) "A"."STATUS"[CHARACTER,3]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Adding the hint opt_param(‘_bloom_pruning_enabled’,’false’) to the query we get the right results and, of course, we can see that there is no operation in the execution plan to generate and use the Bloom filter that is probably causing the problem.

Conclusion

If you are going to use char() types in your tables, and if you are going to compare columns of type char() make sure that the columns are defined to be exactly the same length – or that you include an explicit cast() to guarantee that the shorter column appears to be the same length as the longer column.

Footnote

This bug appeared in my MOS “hot topics”email a couple of days ago as

Bug 27661222: WRONG RESULTS WITH PARTITION PRUNING COMPARING CHAR COLUMNS OF DIFFERENT LENGTH

Reading the bug note the problem is described as a bug in “AND pruning” with a workaround of setting the hidden parameter “_and_pruning_enabled” to false (possibly through the opt_param() hint). I suspect that the underlying problem may be the Bloom filter itself and that disabling Bloom filter pruning for the query may be a slightly less aggressive workaround.

The bug is reported as fixed in 20.1 – but you don’t need to upgrade just yet because, apart from the workarounds, there are various patches available back to 19.x and 12.2.

The sample script above is basically the example in the bug note with a few minor changes.

 

 

 

Want to replace a particular string with a null value

Tom Kyte - Thu, 2020-02-13 21:10
We have 2 types of record_data format in table speedwing table 1st type --> <code>..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20' ..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2...
Categories: DBA Blogs

Roles granted to other roles

Tom Kyte - Thu, 2020-02-13 21:10
Is it true that roles can not be granted to other roles anymore? I am unable to find documentation of this, but was informed that this was taken away in 12c. If this is true, will you please post the document?
Categories: DBA Blogs

Why commit/rollback or any DDL command not allowed in trigger or function?

Tom Kyte - Thu, 2020-02-13 21:10
Hi Tom, Theoretically I know that commit/rollback/DDL or anything that causes transaction to end are not allowed in a trigger and function if calling function in SQL statement. To use any of those in trigger/function we can use PRAGMA AUTONOMOUS T...
Categories: DBA Blogs

DBMS_UTILITY.FORMAT_CALL_STACK Change in 12.2 and later

Bobby Durrett's DBA Blog - Thu, 2020-02-13 15:47

Quick note. During my 11.2.0.4 to 19c upgrade that I have been writing about we found a difference in behavior of DBMS_UTILITY.FORMAT_CALL_STACK. I tested it on several versions, and it switched in 12.2. Now it puts the procedure name within the package in the stack.

Old output:

----- PL/SQL CALL STACK -----
  OBJECT      LINE  OBJECT
  HANDLE    NUMBER  NAME
0X15BFA6930         9  PACKAGE BODY MYUSER.MYPKG
0X10C988058         1  ANONYMOUS BLOCK

New output:

----- PL/SQL CALL STACK -----
  OBJECT      LINE  OBJECT
  HANDLE    NUMBER  NAME
0XA796DF28         9  PACKAGE BODY MYUSER.MYPKG.MYPROC
0X7ADFEEB8         1  ANONYMOUS BLOCK

Test code:

select * from v$version;

CREATE OR REPLACE PACKAGE MYPKG
AS

PROCEDURE MYPROC;

END MYPKG;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY MYPKG
AS

PROCEDURE MYPROC
IS

BEGIN

DBMS_OUTPUT.PUT_LINE(UPPER(dbms_utility.format_call_stack));

END MYPROC;

END MYPKG;
/
SHOW ERRORS;

execute mypkg.myproc;
show errors;

Might be useful to someone else. We had some code that depended on the package name being the last thing on its line, but the new version includes the name of the procedure after the package name.

Bobby

Categories: DBA Blogs

Centra Health Delivers Superior Patient Care with Oracle Cloud Applications

Oracle Press Releases - Thu, 2020-02-13 08:00
Press Release
Centra Health Delivers Superior Patient Care with Oracle Cloud Applications Prominent hospital network in Virginia moves finance, procurement, human resources, and supply chain applications to the cloud to increase efficiency and improve business insights

Redwood Shores, Calif.—Feb 13, 2020

To advance its mission of providing excellent care for life, Centra Health has selected Oracle Cloud Applications. With a complete and integrated suite of applications to manage its finance, procurement, HR and supply chain, Centra Health will be able to increase productivity, improve controls, drive down costs and enhance overall business insights.

Founded in 1987, Centra Health is a regional nonprofit healthcare system with 8,400 staff that serves more than 500,000 people in 70 locations in central and southern Virginia. To continue to deliver on its mission and provide the best possible services for its patients, Centra Health needed to replace its aging and disparate on-premises applications with an integrated suite of applications that would meet its functional needs while driving down costs. After extensive review, Centra selected Oracle Enterprise Resource Planning (ERP) Cloud, Oracle Human Capital Management (HCM) Cloud, and Oracle Supply Chain Management (SCM) Cloud to support its vision for improved, affordable healthcare.

“When determining the appropriate vendor to support our business, we used a multidisciplinary process to ensure our needs were accurately quantified and comprehensively vetted,” said Tom Lawton, vice president and chief resource officer at Centra Health. “Oracle surfaced as the ERP vendor that checked all of the boxes, and our leadership felt confident that Oracle could support both our current and future needs. We are also anticipating additional value from the relationship with Oracle by having a common platform across our HR, finance, and supply chain operations.”

With Oracle ERP Cloud, Oracle HCM Cloud, and Oracle SCM Cloud, Centra Health will be able to take advantage of the cloud to break down organizational silos, standardize processes, and manage data from its finance, supply chain and HR teams on a single integrated cloud platform. In addition, by providing a common user interface across all functional areas, Oracle Cloud Applications will help Centra Health increase employee engagement, collaboration, and performance. To drive better business results and ensure its team stays focused on providing the best possible patient care, Centra Health will also leverage Oracle’s embedded healthcare and technology expertise, best practices, and third-party partnerships.

“Whether its evolving regulations, the shifting payer-provider dynamic, advances in medicine, or increasing customer expectations, hospitals are struggling to stay ahead of accelerating change,” said Rajan Krishnan, group vice president of product development, Oracle. “Oracle Cloud Applications will help Centra Health outpace all of this change and stay ahead of customer expectations by providing proven, best-of-breed applications across every business function.”

Contact Info
Bill Rundle
Oracle
415.990.3348
bill.rundle@oracle.com
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Bill Rundle

  • 415.990.3348

Oracle Advanced Analytics Help Banks Battle Financial Crime

Oracle Press Releases - Thu, 2020-02-13 07:00
Press Release
Oracle Advanced Analytics Help Banks Battle Financial Crime New capabilities increase compliance program effectiveness

LONDON, U.K.—Feb 13, 2020

With $5.7 billion in global money laundering fines issued in 2019[i], growing threat sophistication, and rising compliance costs, financial institutions need advanced analytics to deter financial crime. To help banks meet this challenge, Oracle Financial Crime and Compliance Management (FCCM) suite of products now includes an integrated analytics workbench, 300-plus customer risk indicators, and embedded graph analytics visualizations. These capabilities build on Oracle’s more than 20 years of market leadership and innovation to help financial institutions fight money laundering and achieve compliance.

“Financial crimes are increasingly more sophisticated as technology becomes more advanced,” said John Edison, vice president, Financial Crime & Compliance Products, Oracle Financial Services. “Oracle continues to make strategic investments in the area of anti-money laundering and financial crime compliance management to help financial institutions successfully fight these threats. By seamlessly incorporating advanced analytics capabilities into our enterprise-grade platform, financial institutions can quickly overcome adoption impediments and benefit from cutting-edge innovations at scale. This allows their compliance teams to boost their accuracy and efficiency, which is crucial when fighting financial crime and keeping customers safe.”

These new advanced features can help chief compliance officers and data scientists to increase overall program effectiveness, detection accuracy and investigation efficiency through:

  • An integrated analytics workbench that allows data scientists to run graph analytics, data visualizations, machine modeling, scenario authoring, and testing on all of their data in one place;
  • The ability to augment traditional rules-based behavior detection models with machine-based models and 300-plus out-of-the-box customer risk indicators, which enhance the accuracy of models and reduce false positives;
  • Embedded graph analytics visualizations and 30-plus pre-built graph algorithms for advanced case investigations, entity resolution, and discovery of hidden networks. Graph analytics capabilities also support network pattern analysis and deep learning to automate case decisions and provide recommendations to investigators.
 

Global financial institutions continue to select Oracle for its enterprise-grade, anti-financial crime platform, which is regulator-accepted and based on a common data foundation that takes inputs from any transaction system. This single source of truth enables data sciences’ teams to consume data and leverage the advanced analytics to monitor, detect and investigate as needed. With Oracle FCCM, compliance teams can also increase overall program effectiveness, and optimize compliance operations—at scale.

In 2019, Oracle was named a Category Leader in the Chartis RiskTech Quadrant® for:

 

[i] According to statistics at AMLabc.com

Contact Info
Judi Palmer
Oracle Corporation
+1 650.784.7901
judi.palmer@oracle.com
Jack Rankin
CMG
+44 207 067 0823
JRankin@cmgrp.com
About Oracle Financial Services

Oracle Financial Services provides solutions for retail banking, corporate banking, payments, asset management, life insurance, annuities and healthcare payers. With our comprehensive set of integrated digital and data platforms, banks and insurers are empowered to deliver next generation financial services. We enable customer-centric transformation, support collaborative innovation and drive efficiency. Our data and analytical platforms help financial institutions drive customer insight, integrate risk and finance, fight financial crime and comply with regulations. To learn more visit our website at https://www.oracle.com/industries/financial-services/index.html.

About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Judi Palmer

  • +1 650.784.7901

Jack Rankin

  • +44 207 067 0823

Transform IF ELSE END IF TO CASE WHEN

Tom Kyte - Thu, 2020-02-13 03:10
I need to use PL-SQL or SQL to do my query. First: I want to transform all the IF..END IF used in the body of function F_CALCUL_TAUX to CASE..WHEN..END, and use the result inside my query. Secundo: It's possible to transform that and use it inside ...
Categories: DBA Blogs

Function for alphabetical sequence like a spreadsheet

Tom Kyte - Thu, 2020-02-13 03:10
I need function which convert numeric to alphabet like when I input 1 then it will return 'A', when i input 2 then it will return 'B' please help me on this.
Categories: DBA Blogs

LINESIZE and displaying data on a screen : the biggest part of execution time?

Tom Kyte - Thu, 2020-02-13 03:10
Hello Masters, I have one big question about the SQL*Plus parameter LINESIZE and the display of datas. I read in documentation Oracle 19 SQL*Plus : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/sqlplus-users-guide-and-refe...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator