Feed aggregator

Example CCA Generator

Angelo Santagata - Mon, 2017-01-09 17:11
Geertjan, a cool JET PM has created a CCA generator which can be used with JET. Now the nice thing about this is not that it allows you to preconfigure and generate JET forms but that its a good example of how you can build a) a JET CCA Generator and b) something reusable. CCAs are a really important feature of JET going forward, they will allow us to embed JET "applets" , in JET projects, ABCS and in the future in other Oracle products
Check it out Jet Builder GITHUB

Reality hits me home, with aging parents and uncertainties of life. I learn about OPLL

Arvind Jain - Mon, 2017-01-09 16:11
I never thought that I will be writing out something way outside my comfort zone of IT technology.

But a recent mishap accident with my father has made me realize that there are far more difficult and significant things in life than I had yet to confront and I was not prepared to handle it alone.


Diwali the festival of lights had just gone by and we were all in a cheerful mood. My brother and his family were back home in India and the grand parents were having a good time with grand kids. My dad has just returned back to India after a pleasant three month stay with me here in USA.  


Then came the scary Halloween trick for me. On a fateful day of Oct 30th 2016, just after Diwali in Bhilai, India my dad slipped and fell and the back of his neck hit the swing base. It was not a major accident by external measures, no external bleeding or wound, but it was severe enough to compress the nerves to a point were he could not move or stand up. He was lying on the floor, lifeless and could not move his both legs and one hand.


He was fortunately able to make an audible alarm and was picked up , lifted and transported to hospital.  He was immediately admitted to Bhilai Sec-9 hospital in Chhattisgarh, India. There it was diagnosed that he already had a case of OPLL () and this fall and immobility are symptoms of  a final breakdown.


OPLL is a calcification of the soft tissues that connect the spinal bones which results in a narrowing of the spinal canal and compression of the cervical spinal cord.


The cause of OPLL is unclear and I could not get a certain answer from anyone, but was told that people of Asian heritage especially Japanese have a higher likelihood of developing OPLL. Also more men seem to be affected than women.
OPLL SymptomsMost patients with OPLL are asymptomatic with no symptoms at all, but others may experience mild pain and numbness in the arms and/or legs to complete numbness in the extremities. The symptoms are similar to those of cervical cord compression.
Treatment of OPLLThis depends on the stage of OPLL and in my case , my dad is at stage 4 and that is the severest stage. Conservative treatment like activity modification and exercises may help relieve the pain caused by OPLL but I was told that open spine surgery is required.


If pressure is not relieved on the spinal cord then future damage and loss of neurological function is very much possible. At sec 1 Bhilai hospital, my brother was told that this may affect respiratory functions as well. So they advised immediate surgery and there is a major risk when you open up the spine.




Since then in last 2 months we have gone through many things and are working on getting this thing taken care of. It is a big help that my brother is back home in India and can execute on many on site things.  Simple things like opening zipper or buttons that we take granted for in life start to seem like mammoth tasks. What you can do in person on site, is a significant thing in such circumstances.


I would like to thanks and credit the following websites for some of my research. Indian Government portal surprisingly had a good amount of research papers at NCBI.


https://www.emoryhealthcare.org/orthopedics/ossification-posterior-longitudinal-ligament.html
http://www.medscape.com/viewarticle/739292
https://www.ncbi.nlm.nih.gov/pubmed/16306836


We have undergone surgery since then and in future articles I will share more details, but if anyone is in similar situation, I will be glad to share detailed notes. I was helped by many known and unknown people so my heartfelt thanks goes out to them and I plan to pay it forward.


Thanks

Arvind

Difference between Rownum and level in connect by (it is giving different result)

Tom Kyte - Mon, 2017-01-09 13:46
Hi, Hope your doing great! Can you kindly guide me with the difference between the rownum and level in connect by, I though it will give same result but it is little confusing. Can you please teach me in steps how it works. Thanks! drop tabl...
Categories: DBA Blogs

Check constraint violated while loading JSON doc into DB

Tom Kyte - Mon, 2017-01-09 13:46
<code>Team, Could you please help me to understand why this JSON document load got failed with check constraint enabled? but this is a Valid JSON document, validated it through http://jsonlint.com/ portal. for time being, we are able to just...
Categories: DBA Blogs

Executing .sql file in Pl/sql procedure

Tom Kyte - Mon, 2017-01-09 13:46
Hi , I am trying to execute a set of insert statements in a pl/sql block. However these insert scripts vary time to time. Hence i would like to save them in some location as .sql file and execute in pl/sql So i would like to know if it is poss...
Categories: DBA Blogs

issue with the usage of the oracle external table

Tom Kyte - Mon, 2017-01-09 13:46
Hi tom, I have an oracle external table in my database and i am running my database in linux operating system.I have a directory where i will be placing the (.csv) file and loading into the external table.But there is an issue with the externa...
Categories: DBA Blogs

Setting up Transport Layer Security/Secure Sockets Layer for Managed ODP.NET

Christian Shay - Mon, 2017-01-09 08:53

[I was chatting with ODP.NET product manager, Alex Keh, and he let me know about some known issues that developers currently face when using managed ODP.NET with Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets. I asked him for more details and he wrote up the step by step walkthrough you see below. If you have questions about the steps provided below, please post to the ODP.NET OTN forum for assistance. -CS]


Setting up managed ODP.NET Transport Layer Security/Secure Sockets Layer (TLS/SSL) wallets correctly can be tricky for those that don't do it on a regular basis. Microsoft Windows now restricts wallets from using the MD5 algorithm. Oracle wallets may have been generated with this algorithm as that was the default option in Oracle Public Key Infrastructure (orapki) utility 12.1 and earlier.

When you setup TLS/SSL and encounter an "ORA-0052: Failure during SSL handshake" error combined with a 0x80004005 error code and first inner exception "A SSPI-call failed" and second inner exception "A token sent to the function is invalid", then it is very likely that Microsoft Security Support Provider Interface (SSPI) rejected your Oracle Wallet, such as when MD5 is used. This is a failure on the handshake. You can resolve this error by using the SHA-2 algorithm instead. We'll describe how shortly.

If the second inner exception instead indicates "The credentials supplied to the package were not recognized", it is possible the user certificate was generated without a certificate authority (CA). You can resolve this error by using orapki to generate a CA/root certificate and then regenerating your user wallet/certificate to point to this new CA/root certificate. More info on how to do this is below.

Orapki refers to orapki.exe. This utility is part of full Oracle client (admininstrator) installations. It is not included with Oracle Instant Client. The utility is only needed to setup up the wallet; it is not necessary to deploy it with the wallet.

The steps below will regenerate your Oracle Wallet using orapki. Any orapki version can be used to generate the wallet with these instructions.


1. Create root wallet, for example, a CA wallet.

orapki wallet create -wallet ./root -pwd yourpassword

2. Add a self-signed certificate (CA certificate) to the root wallet.

orapki wallet add -wallet ./root -dn 'CN=yourroot' -keysize 1024 -self_signed -validity 3650 -pwd yourpassword -sign_alg sha512

3. Export the self-signed certificate from the wallet.

orapki wallet export -wallet ./root -dn 'CN=yourroot' -cert ./root/b64certificate.txt -pwd yourpassword

4. Create a user wallet, for example, a customer wallet.

orapki wallet create -wallet ./user -pwd yourpassword -auto_login

5. Add a certificate request.

orapki wallet add -wallet ./user -dn 'CN=clientshostname' -keysize 1024 -pwd yourpassword -sign_alg sha512

6. Export the certificate request.

orapki wallet export -wallet ./user -dn 'CN=clientshostname' -request ./user/creq.txt -pwd yourpassword

7. Create a certificate issued by a CA.

orapki cert create -wallet ./root -request ./user/creq.txt -cert ./user/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512

8. Add a trusted certificate (CA certificate) to the wallet. This example assumes the same CA for both the client and server wallets.

orapki wallet add -wallet ./user -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword

9. Add a user certificate.

orapki wallet add -wallet ./user -user_cert -cert ./user/cert.txt -pwd yourpassword -sign_alg sha512

10. Display contents of user wallet.

orapki wallet display -wallet ./user -pwd yourpassword

11. Create a server wallet.

orapki wallet create -wallet ./server -pwd yourpassword -auto_login

12. Add a server certificate request.

orapki wallet add -wallet ./server -dn 'CN=servershostname' -keysize 1024 -pwd yourpassword -sign_alg sha512

13. Export the certificate request.

orapki wallet export -wallet ./server -dn 'CN=servershostname' -request ./server/creq.txt -pwd yourpassword

14. Create a server certificate issued by a CA.

orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd yourpassword -sign_alg sha512

15. Add a trusted certificate (CA certificate) to the server wallet. This example assumes the same CA for both the client and server wallets.

orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd yourpassword

16. Add a user_cert certificate for the server wallet.

orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd yourpassword -sign_alg sha512

17. Display contents of server wallet.

orapki wallet display -wallet ./server -pwd yourpassword


Oracle Retail Suite 16 Introduces New Cloud Services to Maximize Retail Profit and Productivity

Oracle Press Releases - Mon, 2017-01-09 07:00
Press Release
Oracle Retail Suite 16 Introduces New Cloud Services to Maximize Retail Profit and Productivity Platform Upgrades Bring Extensive New Services to the Cloud and Mobile POS

Redwood Shores, Calif.—Jan 9, 2017

Oracle announced today the availability of Oracle Retail Release 16, which empowers retailers to deliver unified consumer experiences across ecommerce and brick and mortar locations. The new release reflects insights of our global customer community regarding shared business challenges and more than 100,000 development days invested via Oracle’s renowned research and development team, culminating in the retail industry’s most advanced and mobile-enabled cloud services and on-premise solutions.

“As retailers invest in new markets and offerings for 2017, having the right infrastructure, optimization science and mobile solutions will become a critical competitive advantage,” said Ray Carlin, senior vice president and general manager of Oracle Retail. “Oracle’s Cloud First strategy enables us to deliver a steady cadence of innovation to our global retail customers, and we are taking the industry’s leading retail science and best practices to a new level with Oracle Retail Release 16.”

Expanding Cloud Services for Retailers

Oracle Retail recognizes the industry is in a period of migration to cloud technologies and has put in place a strategy that will support cloud and on-premise solutions, empowering customers to migrate to the cloud at a pace that meets their strategic needs.

“Access to retail-specific cloud services is a critical differentiator for brands to streamline operations, expand offerings and scale at a fast pace,” said Ray Carlin, senior vice president and general manager of Oracle Retail. “With Oracle Retail Release 16, brands of all sizes can access best-in-class industry solutions through our world-class cloud services.”

  • On-Premise to Cloud: Oracle extends its industry-leading Oracle Retail Planning and Optimization technology to the cloud and enables retailers to reduce implementation costs with the Oracle Retail Planning Enterprise Cloud Service platform.  The shift to next generation cloud-based planning capabilities allows retailers to plan by exception and scale when needed by paying for capacity used.
  • The Power of the Cloud: Oracle now offers over 33 Oracle Retail cloud services including its industry leading merchandising solution with documented best practices and automation of key process at a lower cost.

Making Critical Merchandising Operations Mobile

“We optimized Oracle Retail Suite 16 for a mobile environment recognizing that old and new generations want to use consistent technology at home and at work,” said Jeff Warren, vice president product strategy at Oracle Retail. “Our platform is engineered to accelerate critical decision making through a powerful interface for managers and analysts that is built upon machine learning and data analytics.”

  • Reduce Labor Cost: Oracle Retail Release 16 accelerates user productivity by leveraging persona-based dashboards at the core of the solution and highlighting opportunities that will impact service levels, inventory margins and key business metrics. These new persona-based dashboards empower users to be more efficient by surfacing key actions and metrics that are relevant to the user, driving increased productivity and ultimately empowering them.
  • Modern Exception Based Retailing: The new modern, user experience and mobile applications across Oracle Retail Merchandising Release 16 place business intelligence and analytics at the front of every process to enable collaboration & empower associates with context to make better & faster decisions.  With this release, Oracle Retail Merchandising is expanded to support Android and Apple devices which increase managerial productivity through exception-based reporting and transaction approvals. These additions extend existing buying, allocating, inventory management, sales auditing and invoice-matching capabilities to mobile devices.

Giving Store Associates a Fully Informed Mobile Solution

Oracle Retail is also bridging the productivity gaps between online and traditional point of sale functionality, through mobile experiences that inspire store associates to engage consumers away from the register.

  • Converged Commerce: Also now available, the Oracle Retail Extension Module for Oracle Commerce allows retailers to deliver a common brand experience across all touch points by using Oracle Cloud Services to manage orders, optimize fulfillment decisions, and give everyone the same single view of customers across your enterprise. Oracle’s Xstore POS also enables retailers to help customers pick up, complete, or change purchases from any location by integrating with Oracle Retail Customer Engagement Software, Oracle Retail Order Broker and Oracle Retail Order Management System.
  • Better Together: The mobile Oracle Retail Xstore Point of Service is also optimized to provide traditional web functionality like in-depth product details, ratings and reviews, and task management so associates are able to engage consumers away from the register with deep insights operating on the Oracle MICROS Family Workstation.
  • Mobility Everywhere: With this release, Oracle Retail Xstore now offers four different ways for store employees to engage – using a traditional fixed device, dockable tablets, tablets, and mobile.  All use the same POS platform and provide full check out, CRM visibility, cross- and up-sell functionality, fulfillment, search, price check and loyalty program integration from any device.

Oracle Retail at NRF 2017

Oracle Retail will be showcasing the full suite of Oracle Retail solutions and cloud services at the National Retail Federation Big Show Jan. 15-17, 2017, in New York City at the Jacob K. Javitz Convention Center. Oracle Retail will be located at booth #2521. For more information check out: www.oracle.com/nrf2017

Contact Info
Matthew Torres
Oracle
+415 595 1584
matt.torres@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit 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.

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Matthew Torres

  • +415 595 1584

Using Oracle Big Data Lite Virtual Machine with Ravello trial

Big Data Lite Virtual Machine is the most straight-forward way to start learning about Oracle Big Data Stack or even develop demos/proof of concepts on top of it (don't forget however that BD Lite...

We share our skills to maximize your revenue!
Categories: DBA Blogs

What are the Differences Between a Primary Key vs Foreign Key?

Complete IT Professional - Mon, 2017-01-09 05:00
I explain what a primary key and foreign key is, as well as the differences between primary key vs foreign key in this article. What Is a Primary Key? What is the primary key definition?  A primary key is one or more columns in a table that are used to uniquely identify the row. When […]
Categories: Development

Reminder: Upgrade Oracle Access Manager 11.1.2.2 to 11.1.2.3

Steven Chan - Mon, 2017-01-09 02:05

Oracle Fusion Middleware products get new Patch Set updates.  When a new Patch Set has been released, a 12 month Grace Period for the previous Patch Set begins.  Once that Grace Period ends, no new patches for the previous Patch Set will be released.

For more details, see:

Oracle Access Manager is part of a suite of products called "Oracle Identity and Access Management" (IAM).  OAM 11.1.2.2 was released in January 2014.  OAM 11.1.2.3 was released in May 2015, which means that the Grace Period for OAM 11.1.2.2 ended after May 2016. 

All E-Business Suite users running OAM 11.1.2.2 should upgrade to OAM 11.1.2.3 to remain under Error Correction Support. 

Related Articles

Categories: APPS Blogs

12c Adaptive Joins Plus Statistics Feedback For Joins Cardinality Estimate Bug

Randolf Geist - Mon, 2017-01-09 01:00
I've encountered a bug at several clients that upgraded to Oracle 12c - 12.1.0.2 - that requires the combination of several new adaptive features introduced with Oracle 12c.

It needs an execution plan that makes use of adaptive join methods, plus at runtime the activation of the new "statistics feedback for joins" feature that was also introduced with Oracle 12c. Note that in 11.2 there was already the "cardinality feedback" feature that only applies to single table cardinality misestimates, but not to join cardinality misestimates.

In case then the join method used at runtime is a Nested Loop join - not necessarily the join method preferred initially, so a runtime switch from Hash to Nested Loop join also reproduces the problem - the "statistics feedback for joins" feature generates a bad OPT_ESTIMATE hint for the join cardinality that always seems to be one, like the following: OPT_ESTIMATE(... JOIN (...) ROWS=1.000000), no matter what the actual join cardinality observed was.

This can lead to very inefficient execution plans that get generated based on the "statistics feedback" on subsequent executions of the same statement, caused by the misleading join cardinality used to generate the new plan.

The good news is that in 12.2.0.1 and in 12.1, when making use of the new backport available that enables the same optimizer default settings than in 12.2 - for more information see this MOS document: Recommendations for Adaptive Features in Oracle Database 12c Release 1 (2187449.1) and for example Christian Antognini's post - the "statistics feedback for joins" feature is disabled by default (in fact in principle only the "adaptive joins" feature is left enabled, all other adaptive features more or less disabled), so the problem doesn't occur there. So one more good reason why installing this backport in 12.1 is a good idea.

However, when enabling this feature specifically or simply enabling all "adaptive statistics" features (OPTIMIZER_ADAPTIVE_STATISTICS = TRUE in 12.2 / 12.1 with backport) the problem also reproduces in 12.2.

If you don't make use of the recommended optimizer settings backport in 12.1 yet then setting "_optimizer_use_feedback" to FALSE prevents the problem, however this will disable both, the "cardinality feedback for single table" 11.2 feature as well as the "statistics feedback for joins" 12c feature.

In 12.2, there are two parameters related to "cardinality feedback", "_OPTIMIZER_USE_FEEDBACK" that controls the "cardinality feedback for single table" 11.2 feature and "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" that controls the new "statistics feedback for joins" feature. Hence, in 12.2, when enabling the "adaptive statistics" feature, the problem can be avoided by setting specifically "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" to FALSE, which would still leave the "cardinality feedback for single table" 11.2 feature enabled.

For more information regarding the various optimizer related settings and differences between 12.1 and 12.2 / 12.1 backport, see this very informative post by Christian Antognini.

Here is a simplified test case that allows reproducing the problem:

-----------------------------------------------------------------------
-- Adaptive joins combined with statistics feedback for joins
-- lead to join cardinality estimate of 1
-- caused by incorrect statistics feedback OPT_ESTIMATE hint generated
--
-- The problem seems to be related to the following combination:
--
-- - Adaptive join method selected
-- - Statistics feedback for joins kicks in
-- - The runtime join method used is Nested Loop join
--
-- Reproduced: 12.1.0.2
-- 12.2.0.1 (with adaptive statistics features re-enabled)
--
-----------------------------------------------------------------------

set echo on

alter system flush shared_pool;

drop table t1 purge;

drop table t2 purge;

create table t1
as
select
rownum as id
, cast('9999' as varchar2(4)) as hist_ind
, rpad('x', 200) as filler
from
dual
connect by
level <= 30000
order by
dbms_random.value
;

exec dbms_stats.gather_table_stats(null, 't1', method_opt => 'for all columns size 1')

create unique index t1_idx on t1 (id);

create table t2
as
with
generator1 as
(
select /*+
cardinality(1e3)
materialize
*/
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e3
),
generator2 as
(
select /*+
cardinality(1e3)
materialize
*/
rownum as id
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e3
),
generator as
(
select /*+ leading(b a) */
(a.id - 1) * 1e3 + b.id as id
from
generator1 a
, generator2 b
)
select
case when id <= 10000 then 1 when id <= 20000 then -1 else id end as id
, cast('N' as varchar2(1)) as some_ind
, rpad('x', 200) as filler
from
generator
;

exec dbms_stats.gather_table_stats(null, 't2', method_opt => 'for all columns size 1')

create /*unique*/ index t2_idx on t2 (id);

set echo on define on

column sql_id new_value sql_id

alter session set statistics_level = all;

-- Enable statistics feedback for joins in 12.2.0.1
alter session set optimizer_adaptive_statistics = true;

-- Disabling statistics feedback for joins prevents the bug
-- alter session set "_OPTIMIZER_USE_FEEDBACK_FOR_JOIN" = false;

-- Or disabling adaptive joins prevents the bug
-- alter session set "_OPTIMIZER_ADAPTIVE_PLANS" = false;

-- alter session set "_OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN" = false;

-- Not related to NL join optimized plan shapes
--alter session set "_nlj_batching_enabled" = 0;

--alter session set "_table_lookup_prefetch_size" = 0;

-- Expected number of rows, initial join NL, still "statistics feedback" kicks in (is it a bug that statistics feedback kicks in here?)
-- No join method switch
-- Bad OPT_ESTIMATE hint for join (ROWS=1) => bug
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
T1 A0 INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND = '9999'
AND A0.ID between 20000 and 22000
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- More than expected number of rows, initial join NL, "statistics feedback" kicks in
-- Switch from NL to HASH (at runtime and at re-optimization time)
-- Generated OPT_ESTIMATE hints are OK
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
T1 A0 INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND = '9999'
AND A0.ID between 1 and 2
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- Less than expected number of rows, initial join HASH, "statistics feedback" kicks in
-- Switch from HASH to NL (at runtime and at re-optimization time)
-- Bad OPT_ESTIMATE hint for join generated (ROWS=1) => bug
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
T1 A0 INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND = '9999'
AND A0.ID between 2 and 20500
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

-- Expected number of rows, initial join HASH, "statistics feedback" does not kick in
-- No join method switch
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
T1 A0 INNER JOIN T2 A1
ON 1 = 1
AND A1.ID = A0.ID
WHERE 1 = 1
AND A0.HIST_IND = '9999'
AND A0.ID between 20000 and 30000
;

-- Re-execute to have statistics feedback kick in
/

select prev_sql_id as sql_id from v$session where sid = userenv('sid');

select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';
The critical part of the output looks like this:

SQL> -- Expected number of rows, initial join NL, still "statistics feedback" kicks in (is it a bug that statistics feedback kicks in here?)
SQL> -- No join method switch
SQL> -- Bad OPT_ESTIMATE hint for join (ROWS=1) => bug
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
2 T1 A0 INNER JOIN T2 A1
3 ON 1 = 1
4 AND A1.ID = A0.ID
5 WHERE 1 = 1
6 AND A0.HIST_IND = '9999'
7 AND A0.ID between 20000 and 22000
8 ;

COUNT(A1.SOME_IND)
------------------
2000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
2000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
8mqn521y28t58

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8mqn521y28t58, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 20000 and 22000

Plan hash value: 3258782287

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2036 (100)| | 1 |00:00:00.02 | 4044 | 38 |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.02 | 4044 | 38 |
|- * 2 | HASH JOIN | | 1 | 2001 | 34017 | 2036 (1)| 00:00:01 | 2000 |00:00:00.02 | 4044 | 38 |
| 3 | NESTED LOOPS | | 1 | 2001 | 34017 | 2036 (1)| 00:00:01 | 2000 |00:00:00.02 | 4044 | 38 |
| 4 | NESTED LOOPS | | 1 | 2002 | 34017 | 2036 (1)| 00:00:01 | 2000 |00:00:00.02 | 2044 | 38 |
|- 5 | STATISTICS COLLECTOR | | 1 | | | | | 2000 |00:00:00.01 | 2007 | 6 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 2002 | 14014 | 1974 (0)| 00:00:01 | 2000 |00:00:00.01 | 2007 | 6 |
| * 7 | INDEX RANGE SCAN | T2_IDX | 1 | 2002 | | 7 (0)| 00:00:01 | 2000 |00:00:00.01 | 7 | 6 |
| * 8 | INDEX UNIQUE SCAN | T1_IDX | 2000 | 1 | | 0 (0)| | 2000 |00:00:00.01 | 37 | 32 |
| * 9 | TABLE ACCESS BY INDEX ROWID | T1 | 2000 | 1 | 10 | 1 (0)| 00:00:01 | 2000 |00:00:00.01 | 2000 | 0 |
|- * 10 | TABLE ACCESS FULL | T1 | 0 | 1 | 10 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
7 - access("A1"."ID">=20000 AND "A1"."ID"<=22000)
8 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID">=20000 AND "A0"."ID"<=22000))
9 - filter("A0"."HIST_IND"='9999')
10 - filter(("A0"."ID">=20000 AND "A0"."ID"<=22000 AND "A0"."HIST_IND"='9999'))

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

SQL_ID 8mqn521y28t58, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 20000 and 22000

Plan hash value: 3258782287

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2036 (100)| | 1 |00:00:00.02 | 4044 |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.02 | 4044 |
|- * 2 | HASH JOIN | | 1 | 1 | 17 | 2036 (1)| 00:00:01 | 2000 |00:00:00.02 | 4044 |
| 3 | NESTED LOOPS | | 1 | 1 | 17 | 2036 (1)| 00:00:01 | 2000 |00:00:00.02 | 4044 |
| 4 | NESTED LOOPS | | 1 | 2002 | 17 | 2036 (1)| 00:00:01 | 2000 |00:00:00.01 | 2044 |
|- 5 | STATISTICS COLLECTOR | | 1 | | | | | 2000 |00:00:00.01 | 2007 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 2002 | 14014 | 1974 (0)| 00:00:01 | 2000 |00:00:00.01 | 2007 |
| * 7 | INDEX RANGE SCAN | T2_IDX | 1 | 2002 | | 7 (0)| 00:00:01 | 2000 |00:00:00.01 | 7 |
| * 8 | INDEX UNIQUE SCAN | T1_IDX | 2000 | 1 | | 0 (0)| | 2000 |00:00:00.01 | 37 |
| * 9 | TABLE ACCESS BY INDEX ROWID | T1 | 2000 | 1 | 10 | 1 (0)| 00:00:01 | 2000 |00:00:00.01 | 2000 |
|- * 10 | TABLE ACCESS FULL | T1 | 0 | 1 | 10 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
7 - access("A1"."ID">=20000 AND "A1"."ID"<=22000)
8 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID">=20000 AND "A0"."ID"<=22000))
9 - filter("A0"."HIST_IND"='9999')
10 - filter(("A0"."ID">=20000 AND "A0"."ID"<=22000 AND "A0"."HIST_IND"='9999'))

Note
-----
- statistics feedback used for this statement
- this is an adaptive plan (rows marked '-' are inactive)


77 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=1.000000 )

SQL>
SQL> -- More than expected number of rows, initial join NL, "statistics feedback" kicks in
SQL> -- Switch from NL to HASH (at runtime and at re-optimization time)
SQL> -- Generated OPT_ESTIMATE hints are OK
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
2 T1 A0 INNER JOIN T2 A1
3 ON 1 = 1
4 AND A1.ID = A0.ID
5 WHERE 1 = 1
6 AND A0.HIST_IND = '9999'
7 AND A0.ID between 1 and 2
8 ;

COUNT(A1.SOME_IND)
------------------
10000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
10000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
92rttcj6ntzqs

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 92rttcj6ntzqs, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 1 and 2

Plan hash value: 777836357

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 1 |00:00:00.04 | 1262 | 70 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.04 | 1262 | 70 | | | |
| * 2 | HASH JOIN | | 1 | 2 | 34 | 7 (0)| 00:00:01 | 10000 |00:00:00.04 | 1262 | 70 | 2061K| 2061K| 1355K (0)|
|- 3 | NESTED LOOPS | | 1 | 2 | 34 | 7 (0)| 00:00:01 | 10000 |00:00:00.03 | 1258 | 40 | | | |
|- 4 | NESTED LOOPS | | 1 | 3 | 34 | 7 (0)| 00:00:01 | 10000 |00:00:00.02 | 1258 | 40 | | | |
|- 5 | STATISTICS COLLECTOR | | 1 | | | | | 10000 |00:00:00.02 | 1258 | 40 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 3 | 21 | 6 (0)| 00:00:01 | 10000 |00:00:00.02 | 1258 | 40 | | | |
| * 7 | INDEX RANGE SCAN | T2_IDX | 1 | 3 | | 3 (0)| 00:00:01 | 10000 |00:00:00.01 | 23 | 40 | | | |
|- * 8 | INDEX UNIQUE SCAN | T1_IDX | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 | 0 | | | |
|- * 9 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 1 | 10 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | | | |
| * 10 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 10 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | 30 | | | |
| * 11 | INDEX RANGE SCAN | T1_IDX | 1 | 1 | | 0 (0)| | 2 |00:00:00.01 | 2 | 30 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
7 - access("A1"."ID">=1 AND "A1"."ID"<=2)
8 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID"<=2 AND "A0"."ID">=1))
9 - filter("A0"."HIST_IND"='9999')
10 - filter("A0"."HIST_IND"='9999')
11 - access("A0"."ID">=1 AND "A0"."ID"<=2)

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

SQL_ID 92rttcj6ntzqs, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 1 and 2

Plan hash value: 3588347061

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7982 (100)| | 1 |00:00:00.13 | 29516 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.13 | 29516 | | | |
|* 2 | HASH JOIN | | 1 | 10000 | 166K| 7982 (1)| 00:00:01 | 10000 |00:00:00.13 | 29516 | 2061K| 2061K| 1356K (0)|
|* 3 | TABLE ACCESS FULL | T2 | 1 | 10000 | 70000 | 7978 (1)| 00:00:01 | 10000 |00:00:00.12 | 29512 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 2 | 20 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 4 | | | |
|* 5 | INDEX RANGE SCAN | T1_IDX | 1 | 2 | | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
3 - filter(("A1"."ID"<=2 AND "A1"."ID">=1))
4 - filter("A0"."HIST_IND"='9999')
5 - access("A0"."ID">=1 AND "A0"."ID"<=2)

Note
-----
- statistics feedback used for this statement


71 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=10000.000000 )

SQL>
SQL> -- Less than expected number of rows, initial join HASH, "statistics feedback" kicks in
SQL> -- Switch from HASH to NL (at runtime and at re-optimization time)
SQL> -- Bad OPT_ESTIMATE hint for join generated (ROWS=1) => bug
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
2 T1 A0 INNER JOIN T2 A1
3 ON 1 = 1
4 AND A1.ID = A0.ID
5 WHERE 1 = 1
6 AND A0.HIST_IND = '9999'
7 AND A0.ID between 2 and 20500
8 ;

COUNT(A1.SOME_IND)
------------------
500

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
500

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
c55rjg5mdxpph

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c55rjg5mdxpph, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 2 and 20500

Plan hash value: 1011946885

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8227 (100)| | 1 |00:00:00.13 | 30028 |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.13 | 30028 |
|- * 2 | HASH JOIN | | 1 | 20500 | 340K| 8227 (1)| 00:00:01 | 500 |00:00:00.13 | 30028 |
| 3 | NESTED LOOPS | | 1 | 20500 | 340K| 8227 (1)| 00:00:01 | 500 |00:00:00.13 | 30028 |
| 4 | NESTED LOOPS | | 1 | | | | | 500 |00:00:00.13 | 29528 |
|- 5 | STATISTICS COLLECTOR | | 1 | | | | | 500 |00:00:00.13 | 29512 |
| * 6 | TABLE ACCESS FULL | T2 | 1 | 20500 | 140K| 7978 (1)| 00:00:01 | 500 |00:00:00.13 | 29512 |
| * 7 | INDEX UNIQUE SCAN | T1_IDX | 500 | | | | | 500 |00:00:00.01 | 16 |
| * 8 | TABLE ACCESS BY INDEX ROWID| T1 | 500 | 1 | 10 | 248 (0)| 00:00:01 | 500 |00:00:00.01 | 500 |
|- * 9 | TABLE ACCESS FULL | T1 | 0 | 20501 | 200K| 248 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
6 - filter(("A1"."ID"<=20500 AND "A1"."ID">=2))
7 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID"<=20500 AND "A0"."ID">=2))
8 - filter("A0"."HIST_IND"='9999')
9 - filter(("A0"."ID"<=20500 AND "A0"."ID">=2 AND "A0"."HIST_IND"='9999'))

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

SQL_ID c55rjg5mdxpph, child number 1
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 2 and 20500

Plan hash value: 1011946885

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7994 (100)| | 1 |00:00:00.13 | 30028 |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.13 | 30028 |
| 2 | NESTED LOOPS | | 1 | 1 | 17 | 7994 (1)| 00:00:01 | 500 |00:00:00.13 | 30028 |
| 3 | NESTED LOOPS | | 1 | 500 | 17 | 7994 (1)| 00:00:01 | 500 |00:00:00.13 | 29528 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 500 | 3500 | 7978 (1)| 00:00:01 | 500 |00:00:00.13 | 29512 |
|* 5 | INDEX UNIQUE SCAN | T1_IDX | 500 | 1 | | 0 (0)| | 500 |00:00:00.01 | 16 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 500 | 1 | 10 | 1 (0)| 00:00:01 | 500 |00:00:00.01 | 500 |
----------------------------------------------------------------------------------------------------------------------------------

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

4 - filter(("A1"."ID"<=20500 AND "A1"."ID">=2))
5 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID"<=20500 AND "A0"."ID">=2))
6 - filter("A0"."HIST_IND"='9999')

Note
-----
- statistics feedback used for this statement


69 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

HINT_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPT_ESTIMATE (@"SEL$58A6D7F6" JOIN ("A0"@"SEL$1" "A1"@"SEL$1") ROWS=1.000000 )

SQL>
SQL> -- Expected number of rows, initial join HASH, "statistics feedback" does not kick in
SQL> -- No join method switch
SQL> SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM
2 T1 A0 INNER JOIN T2 A1
3 ON 1 = 1
4 AND A1.ID = A0.ID
5 WHERE 1 = 1
6 AND A0.HIST_IND = '9999'
7 AND A0.ID between 20000 and 30000
8 ;

COUNT(A1.SOME_IND)
------------------
10000

SQL>
SQL> -- Re-execute to have statistics feedback kick in
SQL> /

COUNT(A1.SOME_IND)
------------------
10000

SQL>
SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

SQL_ID
-------------
4tj7bn17xcbad

SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', null, format => 'TYPICAL ALLSTATS LAST ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4tj7bn17xcbad, child number 0
-------------------------------------
SELECT /*+ leading(a1) */ count(A1.SOME_IND) FROM T1 A0 INNER JOIN
T2 A1 ON 1 = 1 AND A1.ID = A0.ID WHERE 1 = 1 AND A0.HIST_IND =
'9999' AND A0.ID between 20000 and 30000

Plan hash value: 4274056747

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8227 (100)| | 1 |00:00:00.17 | 30434 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:00.17 | 30434 | | | |
| * 2 | HASH JOIN | | 1 | 10001 | 166K| 8227 (1)| 00:00:01 | 10000 |00:00:00.17 | 30434 | 1969K| 1969K| 1895K (0)|
|- 3 | NESTED LOOPS | | 1 | 10001 | 166K| 8227 (1)| 00:00:01 | 10000 |00:00:00.14 | 29512 | | | |
|- 4 | NESTED LOOPS | | 1 | | | | | 10000 |00:00:00.14 | 29512 | | | |
|- 5 | STATISTICS COLLECTOR | | 1 | | | | | 10000 |00:00:00.13 | 29512 | | | |
| * 6 | TABLE ACCESS FULL | T2 | 1 | 10002 | 70014 | 7978 (1)| 00:00:01 | 10000 |00:00:00.13 | 29512 | | | |
|- * 7 | INDEX UNIQUE SCAN | T1_IDX | 0 | | | | | 0 |00:00:00.01 | 0 | | | |
|- * 8 | TABLE ACCESS BY INDEX ROWID| T1 | 0 | 1 | 10 | 248 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| * 9 | TABLE ACCESS FULL | T1 | 1 | 10001 | 97K| 248 (0)| 00:00:01 | 10001 |00:00:00.01 | 922 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("A1"."ID"="A0"."ID")
6 - filter(("A1"."ID"<=30000 AND "A1"."ID">=20000))
7 - access("A1"."ID"="A0"."ID")
filter(("A0"."ID">=20000 AND "A0"."ID"<=30000))
8 - filter("A0"."HIST_IND"='9999')
9 - filter(("A0"."ID">=20000 AND "A0"."HIST_IND"='9999' AND "A0"."ID"<=30000))

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)


37 rows selected.

SQL>
SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '&sql_id' and hint_text like '%JOIN%';

no rows selected

SQL>
Note how in each case where the Nested Loop join gets used at runtime and "statistics feedback for joins" kicks in, the bad OPT_ESTIMATE hint gets generated.

I've discussed this case also with Nigel Bayliss at Oracle (the Optimizer Product Manager) and a corresponding bug was opened, so hopefully the problem gets addressed in the future.

V$RMAN_BACKUP_JOB_DETAILS, a caveat

Hemant K Chitale - Sun, 2017-01-08 23:46
Building on a previous blog post (you could read it before or after this post), here's a quick demo of a caveat or quirk with V$RMAN_BACKUP_JOB_DETAILS.

This in 11.2.0.4

[oracle@ora11204 Desktop]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 9 13:39:44 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1362461976)

RMAN> backup as compressed backupset
2> incremental level 1 database
3> plus archivelog ;


Starting backup at 09-JAN-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=108 RECID=1245 STAMP=928093294
input archived log thread=1 sequence=109 RECID=1246 STAMP=928093719
input archived log thread=1 sequence=110 RECID=1247 STAMP=928093722
input archived log thread=1 sequence=111 RECID=1248 STAMP=928093724
...
...
...
input archived log thread=1 sequence=163 RECID=1318 STAMP=929802055
input archived log thread=1 sequence=164 RECID=1319 STAMP=932823436
input archived log thread=1 sequence=165 RECID=1320 STAMP=932823439
input archived log thread=1 sequence=166 RECID=1321 STAMP=932823606
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134007_d768kr8l_.bkp tag=TAG20170109T134007 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
using channel ORA_DISK_1
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd0_TAG20170109T134123_d768n3v5_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c552qnsh_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_intermed_c552qpc7_.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd1_TAG20170109T134123_d768ojmm_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=167 RECID=1322 STAMP=932823743
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134223_d768ozv9_.bkp tag=TAG20170109T134223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JAN-17

Starting Control File and SPFILE Autobackup at 09-JAN-17
piece handle=/u02/FRA/ORCL/autobackup/2017_01_09/o1_mf_s_932823745_d768p1jo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-17

RMAN>


What does V$RMAN_BACKUP_JOB_DETAILS tell us ?

SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') Start_At,
2 to_char(end_time,'DD-MON HH24:MI') End_At,
3 input_bytes/1048576 Input_MB, output_bytes/1048576 Output_MB,
4 input_type, status
5 from v$rman_backup_job_details
6 where start_time > trunc(sysdate)
7* order by start_time
SQL> /

START_AT END_AT INPUT_MB OUTPUT_MB INPUT_TYPE
--------------------- --------------------- ---------- ---------- -------------
STATUS
-----------------------
09-JAN 13:40 09-JAN 13:42 2917.06055 491.563477 DB INCR
COMPLETED


SQL>


The view does NOT show how much of the input/output was for ArchiveLogs.  It clubs ArchiveLogs and the controlfile autobackup under the   single entry for "DB INCR".   Anyone reading this row from V$RMAN_BACKUP_JOB_DETAILS would NOT know if ArchiveLogs had been backed-up, would NOT know if a controlfile/spfile autobackup was created.
Furtheremore, if there is a failure (e.g. only the last ArchiveLog backupset failed ?), would you be able to identify what has successfully been backed up.  Also see my previous blog post.
.
.
.


Categories: DBA Blogs

error in packages

Tom Kyte - Sun, 2017-01-08 19:26
<code>i have table and packages like this,but iam getting error while executing it.so please help me: CREATE TABLE LOGGING_DATA_HDR ( LOG_ID NUMBER, TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL, PK_DATA VARCHAR2 (500 BYTE...
Categories: DBA Blogs

Need to Know the process & result of my question

Tom Kyte - Sun, 2017-01-08 19:26
Emp, dept table is present for reference SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO ...
Categories: DBA Blogs

A select query uses Full table Scan. How to force the query using Index Scan option.

Tom Kyte - Sun, 2017-01-08 19:26
A Select query is using Full table Scan. The execution time takes much long. How to force the query to use index scan option.
Categories: DBA Blogs

How do I call MySQL Stored Procedure from oracle using HS link ?

Tom Kyte - Sun, 2017-01-08 19:26
How do I call MySQL Stored Procedure from oracle using HS link ? HS link is correct and I can get the tables data just by adding '@' at the end of the table name ,but I can't call the SP same way ! Help please.
Categories: DBA Blogs

Oracle Enterprise Manager 13c R2 configuration on Win 2008 R2 server stops at 78%

Syed Jaffar - Sun, 2017-01-08 05:13
Oracle Enterprise Manager (OEM) 13cR2 configuration on a Win 2008 R2 was stopping at 78% completion  while performing the BI Publisher Configuration. Apparently, the problem exists pre 13cR2 as well.

All OMS components like (WebTier, Oracle Management Server, JVMD Engine) will stop/start during the BI Publisher configuration operations. Unfortunately, the windows service for OMS was taking too long to complete the start operation of Webtier (HTTP) and installation at 78% stopped (didn't move forward). Initially, I have started looking at WebTier startup issues, in the process, tried to disable firewall, also excluded the installation directory from the anti virus on the Window server, and the result remains the same.




Cleaned-up the previous installation and start-over the OEM 13cR2 installation on the server, but this time I didn't check the BI Publisher configuration option as I wanted to exclude the BI Publisher configuration to ensure I complete the OEM installation without any issues. Despite the fact I didn't check the option, OEM started configuring BI and stopped exactly at 78%, the issue remains.

The error messages in the sysman and other OMS logs didn't provide any useful hints, in fact, it was misleading and took me in the wrong direction.

Came across of a MOS ID( 1943820.1), and after applying the solution, OEM configuration completed successfully.

Here is the excerpt from the MOS ID :

On some occasions, httpd.exe will fail to start, If you are missing or have a damaged Microsoft Visual C++ Redistributable 64-bit package.
It may report the error above, or give <SEVERE> <OHS-0> <Failed to start the server ohs_1>, with 0 bytes of details in the OHS log.
Install the Microsoft Visual C++ Redistributable Package (x64) anyhow.

1. You can obtain this file at:

http://www.microsoft.com/en-us/download/details.aspx?id=2092

2. Download the Microsoft Visual C++ Redistributable Package (x64)

3. Should have a file called vcredist_x64.exe. Run installation.

4. Try starting OHS again.

Note:
I understood why Oracle still does the BI Publisher configuration despite I didn't select the option. When you don't select the option, BI Publisher is confgured, but, will be disabled, so that in the future you can easily enable this option.




References

OHS 12c Fails To Start On Windows Server 2008 X64, with no detailed errors. (Doc ID 1943820.1)

https://community.oracle.com/thread/3889503


Oracle JET Router API Example

Andrejus Baranovski - Sat, 2017-01-07 13:11
One of the examples of JET Router API usage - sign-in/sign-out implementation. After sign-in we need to change menu structure and allow access to application modules, on sign-out menu structure should be changed again. JET Router API allows to manage application navigation and menu structure from JavaScript. Check complete API methods list here - JSDoc: Class: Router.

Sample application code is available on GitHub - JETPlaygroundApp. This application is generated with JET NavDrawer template and is runnable in NetBeans and from command prompt with Grunt. I was using such Yeoman command to generate it:

yo oraclejet JETPlaygroundApp --template=navdrawer

I have changed index page to stretch to entire width and hamburger button to be always available. This is how sign-in module looks like:


After sign-in, user gets hamburger icon to access menu structure:


Oracle Developer Cloud service UI looks similar, it also gets menu list on the left, user can open it with hamburger icon:


Sign-out is available in the drop-down list:


Router API is used in three places in my sample app:

1. Initial sign-in module setup in appController.js. This is executed when application is initialized. Router is configured here with single module - login. Developer should get root instance, configure it with module list and define array with description how each menu item will look like:


2. On sign-in, when login function is called - we get the same root instance of the router. Configure it with new set of modules, one of them is marked as default, define array with descriptions and reset current navigation. At the end we should sync all changes with Router instance, this is done through sync() method call:


3. On sign-out, when logout functionality is called. We reset router configuration with single login module. Login module is set as default. Here we call Router API method go(). Method doesnt need parameter, it will navigate to default module, if no parameter specified. Depending on URL state, if current module before sign-out is default one, URL will not change - this would require to call sync() method to force sign-in module display. If user is on any other module, not the default one - go() method will navigate to sign-in module automatically:

Not Another Post Configuring HugePages for Oracle on Linux (x86-64)

Michael Dinh - Sat, 2017-01-07 00:46

USE_LARGE_PAGES (TRUE/FALSE/ONLY)

Test case is only for one database instance on server.

DB is using memory_target.

SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 0

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1G
memory_target                        big integer 1G
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 0
sga_target                           big integer 0

SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit

Gather memory configuration.

$ grep Huge /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

++++++++++

$ grep PageTables /proc/meminfo
PageTables:        24428 kB

++++++++++

$ free
             total       used       free     shared    buffers     cached
Mem:       1534604    1484476      50128       1512      20352     335916
-/+ buffers/cache:    1128208     406396
Swap:      4194300          0    4194300

Calculate memlock.

SQL> select round(1534604*.875) from dual;

ROUND(1534604*.875)
-------------------
            1342779

Edit /etc/security/limits.conf to add memlock as shown below.
Logout, login, check ulimit -l

$ grep memlock /etc/security/limits.conf
#        - memlock - max locked-in-memory address space (KB)
oracle   soft   memlock    1342779
oracle   hard   memlock    1342779

++++++++++

$ ulimit -l
134217728

Run hugepages_settings.sh ERROR due to memory target being used.

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

***********
** ERROR **
***********
Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured

Remove memory target configuration from DB.
Note reset was used as shown:
alter system reset memory_target scope=spfile sid=’*’;
alter system reset memory_max_target scope=spfile sid=’*’;

oracle@arrow1:HAWKA:/home/oracle
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 6 21:33:31 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 .4/db_1/dbs/spfileHAWKA.ora
SQL> alter system reset memory_target scope=spfile sid='*';

System altered.

SQL> alter system reset memory_max_target scope=spfile sid='*';

System altered.

SQL> alter system set sga_max_size=768M scope=spfile sid='*';

System altered.

SQL> alter system set sga_target=768M scope=spfile sid='*';

System altered.

SQL> alter system set pga_aggregate_target=256M scope=spfile sid='*';

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
Database mounted.
Database opened.
SQL> @show.sql
SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 768M
sga_target                           big integer 768M
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 256M
SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     900
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 256M
sga_target                           big integer 768M
SQL> show sga

Total System Global Area  801701888 bytes
Fixed Size                  2257520 bytes
Variable Size             276827536 bytes
Database Buffers          520093696 bytes
Redo Buffers                2523136 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
oracle@arrow1:HAWKA:/home/oracle
$

Run hugepages_settings.sh – Recommended setting: vm.nr_hugepages = 388

$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 388

Manually calculate vm.nr_hugepages in KB using [sga_max_size(768M) * 1024 * Hugepagesize(2048 kB)]

SQL> select round(768*1024/2048)+1 from dual;

ROUND(768*1024/2048)+1
----------------------
                   385

SQL>

From alert log – vm.nr_hugepages=385

Fri Jan 06 21:34:33 2017
Starting ORACLE instance (normal)
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 128 GB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total System Global Area size is 770 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by at least 385 
     (page size 2048 KB, total size 770 MB) system wide to get 
     100% of the System Global Area allocated with large pages
********************************************************************

Configure Dynamic vm.nr_hugepages=385

[root@arrow1 ~]# sysctl -w vm.nr_hugepages=385
vm.nr_hugepages = 385
[root@arrow1 ~]# grep Huge /proc/meminfo
HugePages_Total:     353
HugePages_Free:      353
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Configure Static vm.nr_hugepages=385 and restart server – Oracle recommended.

[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
[root@arrow1 ~]# vi /etc/sysctl.conf
[root@arrow1 ~]# grep vm.nr_hugepages /etc/sysctl.conf
vm.nr_hugepages=385
[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6
[root@arrow1 ~]#

HugePages on Oracle Linux 64-bit (Doc ID 361468.1)
Modified:Mar 7, 2016

Step 6: Stop all the database instances and reboot the server
(Although settings could have been done dynamically they would not be effective to the extent we require before a reboot.
The best practice is to do a persistent system configuration and perform a reboot to complete the configuration as presented through the steps above)

ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
cat /boot/grub/grub.conf


Pages

Subscribe to Oracle FAQ aggregator