User login

Aggregator

Please use the contact form to include your blog here, suggest a feed, or give feedback.

If your blog is aggregated on OraFAQ, you may want to display this image on your blog:

Feed aggregator

My Oracle Support Community Enhancement Brings New Features

Joshua Solomin - 3 hours 39 min ago

Untitled Document

Be sure to visit our My Oracle Support Community Information Center to see what is new. Choose from the tabs to watch the How to Video Series. You can also enroll for a live webcast on Wednesday, August 6 at 9am PST.

One change, you can now read blogs in My Oracle Support Community. The new Support Blogs space provides access to Support related blogs. The My Oracle Support Blog provides posts on the portal and tools that span all product areas.

Support Blogs also allow you to stay in touch with the latest product-specific news, tools, and troubleshooting tips in a growing list of product blogs maintained by Support engineers. Check back frequently to read new posts and discover new blogs.

My Oracle Support Upgrade

Joshua Solomin - 4 hours 55 min ago

Untitled Document

Over the weekend, we upgraded My Oracle Support. This upgrade brings changes to help you work more effectively with Oracle Support.

Among the areas you will notice enhancements are:

The My Oracle Support customer experience

My Oracle Support Community

Customer User Administration

Knowledge Management

For details about the latest features visit the My Oracle Support User Resource Center.

CCSF Update: Accreditation appeal denied, but waiting for court date

Michael Feldstein - Wed, 2014-07-23 13:30

It looks like I’ll have the California trifecta in the past week, having already posted on Cal State and University of California news recently. Maybe I should find a Stanford or some other private university story.

In my last post on CCSF from January:

Last week, as expected, a California superior court judge ruled on whether to allow the Accrediting Commission for Community and Junior Colleges (ACCJC) to end accreditation for City College of San Francisco (CCSF) as of July 31, 2014. As reported in multiple news outlets, the judge granted an injunction preventing ACCJC from stripping CCSF’s accreditation at least until a court trial based on the city of San Francisco lawsuit, which would occur in the summer 2014 at the earliest. This means that CCSF will stay open for at least another academic term (fall 2014), and it is possible that ACCJC would have to redo their accreditation review.

In the meantime, ACCJC reviewed CCSF’s appeal of the accrediting decision, and ACCJC is sticking to its guns on the decision, as described in the San Francisco Chronicle:

City College of San Francisco remains out of compliance with eight accreditation standards, so the threat to revoke its accreditation stands, said the commission that set July 31 for the action that would shut the college down.

Accreditation won’t be revoked on that date, however, because a judge delayed the deadline until an October trial can determine if the Accrediting Commission for Community and Junior Colleges properly conducted its 2012 evaluation of City College.

In other words, ACCJC has changed its determination that CCSF should lose accreditation. There are only two caveats at this point:

The injunction that prevents ACCJC from revoking accreditation until the October court date; and
A new loophole called “restoration status”.

From the SF Chronicle again:

Besides pinning its hopes on the lawsuit – which could trigger a completely new evaluation – the college has one more option, made possible in June when the U.S. Department of Education firmly explained to the reluctant commission that it had the power to extend the revocation deadline.

As a result of that intervention, the commission created a new “restoration status” for City College – and any other college that finds itself in such a precarious position – giving it two more years to improve and comply with a new range of requirements.

City College would have to apply for the new status by July 31.

But Phil, you say, I am fascinated by the accreditation review process and want more! To keep you going, here is the letter from ACCJC to CCSF rejecting the appeal. In the letter ACCJC calls out the areas where CCSF is still not in compliance:

I.B Improving Institutional Effectiveness

II.A Instructional Programs

II.B Student Support Services

II.C Library and Learning Support Services

III.B Physical Resources

III.C Technology Resources

III.D Financial Resources

IV.B Board and Administrative Organization

For historical context of how we got here, see this post.

The high-profile game of Chicken continues.

The post CCSF Update: Accreditation appeal denied, but waiting for court date appeared first on e-Literate.

Using SaltStack for Configuration Management

Pythian Group - Wed, 2014-07-23 12:40

In my last blog post I mentioned that SaltStack is a fully featured configuration management solution, but we never looked into using the tool in that way. Today we will begin to explore some basic examples of configuration management with SaltStack. We will look at two aspects of configuration management, installing a package, and will manage a service.

The scenario

A great repeatable task which can be automated with configuration management, and one which is faced by many systems administrators is having to add more capacity to an existing front end webserver pool.

Without a configuration management solution, you generally have to rely on an install document that is maintained by your systems administration team. One of those admins gets the job of preparing the new box, and follows the steps in that document to install all of the required packages and configure all of the required services to make that box a “webserver”

This method introduces a high potential for human error. The person following the document might miss step #17 on page 3, and you end up with a webserver in the pool that delivers content to your users in a strange and inconsistent way. Depending on the maturity of your infrastructure, you also may or may not have the tools in place to even identify that the webserver is acting strangely due to this misconfiguration until clients begin to complain that your service delivers an unreliable experience.

From a resourcing point of view, this task can tie up 2 resources. The person doing the box install, and a second person you need to “QA” the box after the install is done to catch the fact that the first person missed step #17 on page 3.

Using a configuration management tool you define what your box should look like (a model) at a higher, abstracted level and the tool knows what is required to bring the server in line with it’s desired state. The tool does not need to be told that on a RedHat based system you use “yum” to install a package and on Debian systems you use “apt” as the operator you just say that the systems needs to have the package and the tool takes it from there.

By modelling your systems the tool can then provide accurate repeatability of the task of bringing your systems into line with the defined specifications of the model. And while this does shift the responsibility of eliminating any human error within the model itself, once it has been tested and validated the result is that each subsequent execution will be done programmatically without error.

Using SaltStack to install a package and manage a service

The first thing that we will need to do is tell the salt master that we would like to start using it for configuration management. We do this by uncommenting, or adding the following to our /etc/salt/master config:


file_roots:

base:
- /srv/salt

in the /srv directory as root make a “salt” subdir.

mkdir -p /srv/salt

Everything else, from this point forward will be written under the assumption that you are working in the /srv/salt dir.

Salt formulas

In SALT the set of instructions, or “model” that you define is known as a formula. Salt uses PyYALM as it’s configuration syntax. The first thing that we need to defile a base formula called “top.sls”


base:

'*':
- motd
'web*':
- apache
- webserver

This tells salt that all boxes should have the motd formula and that minions with hostnames starting with “web” should also get the apache formula.

Our Apache formula (apache.sls) is very basic for the purposes of this post:


httpd:

pkg:
- installed
service:
- running
- require:
- pkg: httpd

This tells the minion that it needs to install the package named httpd (remember the minion knows how to do this) and that the service should be running and that the service has a dependency on the package being installed. That is to say, you can’t manage the service unless the package that provides that server also is there.

When we apply the formula you can see that the minion receives the instruction. The minion installs the package and it’s dependant packages. Then it starts the service.


[root@ip-10-0-0-170 salt]# salt '*' state.sls apache

ip-10-0-0-171.ec2.internal:
----------
ID: httpd
Function: pkg.installed
Result: True
Comment: The following packages were installed/updated: httpd.
Changes:
----------
apr:
----------
new:
1.5.0-2.11.amzn1
old:

apr-util:
----------
new:
1.4.1-4.14.amzn1
old:

apr-util-ldap:
----------
new:
1.4.1-4.14.amzn1
old:

httpd:
----------
new:
2.2.27-1.2.amzn1
old:

httpd-tools:
----------
new:
2.2.27-1.2.amzn1
old:

mailcap:
----------
new:
2.1.31-2.7.amzn1
old:

----------
ID: httpd
Function: service.running
Result: True
Comment: Started Service httpd
Changes:
----------
httpd:
True

Summary
------------
Succeeded: 2
Failed: 0
------------
Total: 2

On subsequent runs, you can see that the package is already installed and the service is already running.


[root@ip-10-0-0-170 salt]# salt '*' state.sls apache

ip-10-0-0-171.ec2.internal:
----------
ID: httpd
Function: pkg.installed
Result: True
Comment: Package httpd is already installed
Changes:
----------
ID: httpd
Function: service.running
Result: True
Comment: The service httpd is already running
Changes:

Summary
------------
Succeeded: 2
Failed: 0
------------
Total: 2

If either was not true, if I were to go onto the box and stop the service:


[root@ip-10-0-0-171 ~]# service httpd stop

Stopping httpd: [ OK ]
[root@ip-10-0-0-171 ~]#

The next salt run would start the service again bringing the box back into compliance with my defined model.


ip-10-0-0-171.ec2.internal:

----------
ID: httpd
Function: pkg.installed
Result: True
Comment: Package httpd is already installed
Changes:
----------
ID: httpd
Function: service.running
Result: True
Comment: Started Service httpd
Changes:
----------
httpd:
True

Summary
------------
Succeeded: 2
Failed: 0
------------
Total: 2
[root@ip-10-0-0-171 ~]# service httpd status
httpd (pid 2493) is running...
[root@ip-10-0-0-171 ~]#

This becomes a powerful auditing tool which can allow you to quickly ensure that all boxes of a specific type match each other, and eliminates the above mentioned problem of missing step #17 on page 3 of your install doc. With the heavy lifting of this task moved from human operators to the tool, and knowing that each node will be built identical to the others you can now scale up much quicker in response to your changing business needs, a task which previously could take a few days is now done in minutes.

Categories: DBA Blogs

Spark: A Discussion

Greg Pavlik - Wed, 2014-07-23 08:36

A great presentation, worth watching in its entirety.

With apologies to my Hadoop friends but this is good for you too.

Categories: Development, Fusion Middleware

The Customer Experience

Steve Karam - Wed, 2014-07-23 07:00

[This entry is part 6 of 6 in the series Grow Your Career]

I’m going to kick this post off by taking sides in a long-standing feud.

Apple is amazing.

There. Edgy, right? Okay, so maybe you don’t agree with me, but you have to admit that a whole lot of people do. Why is that?

Image from AppleFanSite.com

Sure, there’s the snarky few that believe Apple products are successful due to an army of hipsters with thousands in disposable income, growing thick beards and wearing skinny jeans with pipes in mouth and books by Jack Kerouac in hand, sipping lattes while furiously banging away on the chiclet keyboard of their Macbook Pro with the blunt corner of an iPad Air that sports a case made of iPhones. I have to admit, it does make for an amusing thought. And 15 minutes at a Starbucks in SoHo might make you feel like that’s absolutely the case. But it’s not.

If you browse message boards or other sites that compare PCs and Apple products, you’ll frequently see people wondering why someone would buy a $2,000 Macbook when you can have an amazing Windows 8.1 laptop with better specs for a little over half the price. Or why buy an iPad when you can buy a Samsung tablet running the latest Android which provides more freedom to tinker. Or why even mess with Apple products at all when they’re not compatible with Fragfest 5000 FPS of Duty, or whatever games those darn kids are playing these days.

Image from cnet.com

The answer is, of course, customer experience. Apple has it. When you watch a visually stunning Apple commercial, complete with crying grandpas Facetiming with their newborn great-grandson and classrooms of kids typing on Macbook Airs, you know what to expect. When you make the decision to buy said Macbook Air, you know that you will head to the Apple Store, usually in the posh mall in your town, and that it will be packed to the gills with people buzzing around looking at cases and Beats headphones and 27″ iMacs. You know that whatever you buy will come in a sleek white box, and will be placed into a thick, durable bag with two drawstring cords that you can wear like a backpack.

When you get it home and open the box, it’s like looking at a Tesla Model S. Your new laptop, situated inside a silky plastic bed and covered in durable plastic with little tabs to peel it off. The sleek black cardboard wrapped around a cable wound so perfectly that there’s not a single millimeter of space between the coils, nor a plug out of place. The laptop itself will be unibody, no gaps for fans or jiggly CD-ROM trays or harsh textures.

All of which is to say, Apple provides an amazing customer experience. Are their products expensive, sometimes ridiculously so? Of course. But people aren’t just buying into the product, they’re buying into the “Apple life.” And why not? I’d rather pay for experiences than products any day. I may be able to get another laptop with better specs than my Macbook Pro Retina, but there will always be something missing. Maybe the screen resolution isn’t quite so good, maybe the battery doesn’t last as long, or maybe it’s something as simple as the power cord coming wrapped in wire bag ties with a brick the size of my head stuffed unceremoniously into a plastic bag. The experience just isn’t there, and I feel like I’ve bought something that’s not as magnificent as the money I put into it, features and specs be damned.

Customer experience isn’t just a buzz phrase, and it doesn’t just apply to how you deal with angry customers or how you talk to them while making a sale. It also doesn’t mean giving your customer everything they want. Customer experience is the journey from start to finish. It’s providing a predictable, customer-centric, and enjoyable experience for a customer that is entrusting their hard-earned cash in your product. And it applies to every business, not just retail computer sellers and coffee shops. What’s more, it applies to anyone in a service-oriented job.

Customer Experience for IT Professionals

In a previous post I mentioned how important it is to know your client. Even if your position is Sub-DBA In Charge of Dropping Indexes That Start With The Letter Z, you still have a customer (Sub-DBA In Charge Of Dropping Indexes That Start With The Letters N-Z, of course). Not just your boss, but the business that is counting on you to do your job in order to make a profit. And you may provide an exceptional level of service. Perhaps you spend countless hours whittling away at explain plans until a five page Cognos query is as pure as the driven snow and runs in the millisecond range. But it’s not just what you do, but how you do it that is important.

I want you to try something. And if you already do this, good on you. Next time you get a phone call request from someone at your work, or have a phone meeting, or someone sends you a chat asking you to do something, I want you to send a brief email back (we call this an “ack” in technical terms) that acknowledges their request, re-lists what they need in your own words (and preferably with bullets), and lists any additional requirements or caveats. Also let them know how long it will take. Make sure you don’t underestimate, it’s better to quote too much time and get it to them early. Once you’ve finished the work, write a recap email. “As we discussed,” you might say, “I have created the five hundred gazillion tables you need and renamed the table PBRDNY13 to PBRDNY13X.” Adding, of course, “Please let me know if you have any other requests.”

If the task you did involves a new connection, provide them the details (maybe even in the form of a TNSNAMES). If there are unanswered questions, spell them out. If you have an idea that could make the whole process easier next time, run it by them. Provide that level of experience on at least one task you accomplish for your customer if you do not already, and let me know if it had any impact that you can tell. Now do it consistently.

From what I’ve seen, this is what separates the “workers” from the “rockstars.” It’s not the ability to fix problems faster than a speeding bullet (though that helps, as a service that sells itself), but the ability to properly communicate the process and give people a good expectation that they can count on.

There’s a lot more to it than that, I know. And some of you may say that you lack the time to have this level of care for every request that comes your way. Perhaps you’re right, or perhaps you’re suffering from IT Stockholm Syndrome. Either way, just give it a shot. I bet it will make a difference, at least most of the time.

Conclusion

Recently, I became the Director of Customer Education and Experience at Delphix, a job that I am deeply honored to have. Delphix is absolutely a product that arouses within customers an eager want, it solves complex business problems, has an amazing delivery infrastructure in the Professional Services team, and provides top notch support thereafter. A solid recipe for Customer Experience if there ever was one. But it’s not just about the taste of the meal, it’s about presentation as well. And so it is my goal to continuously build an industrialized, scalable, repeatable, and enjoyable experience for those who decide to invest their dollar on what I believe to be an amazing product. Simply put, I want to impart on them the same enthusiasm and confidence in our product that I have.

I hope you have the chance to do the same for your product, whatever it may be.

The post The Customer Experience appeared first on Oracle Alchemist.

Teradata bought Hadapt and Revelytix

Curt Monash - Wed, 2014-07-23 02:29

My client Teradata bought my (former) clients Revelytix and Hadapt.* Obviously, I’m in confidentiality up to my eyeballs. That said — Teradata truly doesn’t know what it’s going to do with those acquisitions yet. Indeed, the acquisitions are too new for Teradata to have fully reviewed the code and so on, let alone made strategic decisions informed by that review. So while this is just a guess, I conjecture Teradata won’t say anything concrete until at least September, although I do expect some kind of stated direction in time for its October user conference.

*I love my business, but it does have one distressing aspect, namely the combination of subscription pricing and customer churn. When your customers transform really quickly, or even go out of existence, so sometimes does their reliance on you.

I’ve written extensively about Hadapt, but to review:

The HadoopDB project was started by Dan Abadi and two grad students.
HadoopDB tied a bunch of PostgreSQL instances together with Hadoop MapReduce. Lab benchmarks suggested it was more performant than the coyly named DBx (where x=2), but not necessarily competitively with top analytic RDBMS.
Hadapt was formed to commercialize HadoopDB.
After some fits and starts, Hadapt was a Cambridge-based company. Former Vertica CEO Chris Lynch invested even before he was a VC, and became an active chairman. Not coincidentally, Hadapt had a bunch of Vertica folks.
Hadapt decided to stick with row-based PostgreSQL, Dan Abadi’s previous columnar enthusiasm notwithstanding. Not coincidentally, Hadapt’s performance never blew anyone away.
Especially after the announcement of Cloudera Impala, Hadapt’s SQL-on-Hadoop positioning didn’t work out. Indeed, Hadapt laid off most or all of its sales and marketing folks. Hadapt pivoted to emphasize its schema-on-need story.
Chris Lynch, who generally seems to think that IT vendors are created to be sold, shopped Hadapt aggressively.

As for what Teradata should do with Hadapt:

My initial thought Hadapt was to just double down, pushing the technology forward, presumably including a columnar option such as the one Citus Data developed.
But upon reflection, if it made technical sense to merge the Aster and Hadapt products, that would be better yet.

I herewith apologize to Aster co-founder and Hadapt skeptic Tasso Argyros (who by the way has moved on from Teradata) for even suggesting such heresy.

Complicating the story further:

Impala lets you treat data in HDFS (Hadoop Distributed File System) as if it were in a SQL DBMS. So does Teradata SQL-H. But Hadapt makes you decide whether the data is in HDFS or the SQL DBMS, and it can’t be in both at once. Edit: Actually, see Dan Abadi’s comments below.
Impala and Oracle’s new SQL-H competitor have daemons running on every data node. So does one option in Hadapt. But I don’t think SQL-H does that yet.

I was less involved with Revelytix that with Hadapt (although I’m told I served as the “catalyst” for the original Teradata/Revelytix partnership). That said, Teradata — like Oracle — is always building out a data integration suite to cover a limited universe of data stores. And Revelytix’ dataset management technology is a nice piece toward an integrated data catalog.

Related posts

Dan Abadi and Dave DeWitt both drew distinctions among various SQL/Hadoop integrations.
Hadapt was of the original examples for my Cardinal Rules of DBMS Development.

Teradata bought Hadapt and Revelytix

DBMS2 - Wed, 2014-07-23 02:29

I’ve written extensively about Hadapt, but to review:

The HadoopDB project was started by Dan Abadi and two grad students.
HadoopDB tied a bunch of PostgreSQL instances together with Hadoop MapReduce. Lab benchmarks suggested it was more performant than the coyly named DBx (where x=2), but not necessarily competitively with top analytic RDBMS.
Hadapt was formed to commercialize HadoopDB.
After some fits and starts, Hadapt was a Cambridge-based company. Former Vertica CEO Chris Lynch invested even before he was a VC, and became an active chairman. Not coincidentally, Hadapt had a bunch of Vertica folks.
Hadapt decided to stick with row-based PostgreSQL, Dan Abadi’s previous columnar enthusiasm notwithstanding. Not coincidentally, Hadapt’s performance never blew anyone away.
Especially after the announcement of Cloudera Impala, Hadapt’s SQL-on-Hadoop positioning didn’t work out. Indeed, Hadapt laid off most or all of its sales and marketing folks. Hadapt pivoted to emphasize its schema-on-need story.
Chris Lynch, who generally seems to think that IT vendors are created to be sold, shopped Hadapt aggressively.

As for what Teradata should do with Hadapt:

My initial thought Hadapt was to just double down, pushing the technology forward, presumably including a columnar option such as the one Citus Data developed.
But upon reflection, if it made technical sense to merge the Aster and Hadapt products, that would be better yet.

I herewith apologize to Aster co-founder and Hadapt skeptic Tasso Argyros (who by the way has moved on from Teradata) for even suggesting such heresy.

Complicating the story further:

Impala lets you treat data in HDFS (Hadoop Distributed File System) as if it were in a SQL DBMS. So does Teradata SQL-H. But Hadapt makes you decide whether the data is in HDFS or the SQL DBMS, and it can’t be in both at once. Edit: Actually, see Dan Abadi’s comments below.
Impala and Oracle’s new SQL-H competitor have daemons running on every data node. So does one option in Hadapt. But I don’t think SQL-H does that yet.

Related posts

Dan Abadi and Dave DeWitt both drew distinctions among various SQL/Hadoop integrations.
Hadapt was of the original examples for my Cardinal Rules of DBMS Development.

Categories: Other

EID Holidays and things to do

Syed Jaffar - Wed, 2014-07-23 02:07

Looking forward to a much anticipated 9 day EID holiday break to complete the to-do-list which I have been carrying for a while now. Determined to complete some of the writing assignments that I have kept pending for a long period of time now. At the same time, will have to seek the possibilities to exploring the new features of v12.1.0.2 and Exadata as we might we going for the combination in the coming weeks for a Data Warehouse project.

Will surely blog about my test scenarios and will share the inputs on Oracle 12c new features.

I wish everyone a very happy and prosperous EID in advance.

12c Threaded Execution Test

Bobby Durrett's DBA Blog - Tue, 2014-07-22 17:39

I did a quick check of some facts I’m studying about Oracle 12c and its new threaded execution mode. I set this parameter:

alter system set THREADED_EXECUTION=true scope=spfile;

I had to connect SYS as SYSDBA with a password to get the system to bounce.

Then it had these processes only:

oracle    1854     1  0 09:17 ?        00:00:00 ora_pmon_orcl
oracle    1856     1  0 09:17 ?        00:00:00 ora_psp0_orcl
oracle    1858     1  2 09:17 ?        00:00:00 ora_vktm_orcl
oracle    1862     1  3 09:17 ?        00:00:00 ora_u004_orcl
oracle    1868     1 99 09:17 ?        00:00:17 ora_u005_orcl
oracle    1874     1  0 09:17 ?        00:00:00 ora_dbw0_orcl

This differs from some of my 12c OCP study material but agrees with the manuals. Only pmon, psp, vktm, and dbw have dedicated processes.

Also, I found that I needed this value in the listener.ora:

dedicated_through_broker_listener=on

I needed that value to connect using a thread. Before I put that in it spawned a dedicated server process when I connected over the network.

Lastly, contrary to what I had read I didn’t need to set the local_listener parameter to get the new connections to use a thread:

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
local_listener                       string

- Bobby

Categories: DBA Blogs

REGEXP_LIKE: strange unspecified value in parameter “modifier”

XTended Oracle SQL - Tue, 2014-07-22 15:05

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.

Categories: Development