Feed aggregator

Understanding purpose of undo for Global Temporary Tables DML operation

Tom Kyte - 10 hours 17 min ago
Hi TOM, I know Global Temporary Table (GTT) will generate undo data while any DML operations performed on it within that session before commit. But I want to understand why undo is needed for DML operations on GTT. Is that to support following kind ...
Categories: DBA Blogs

Not being utilized all the processors capacity

Tom Kyte - 10 hours 17 min ago
Dear sir, Thank you for your support I have Oracle 11g Database server which has 2 dual core processors. It is very critical database and very frequently we are getting performance issues. We noticed that the CPU utilization is not crossing 5...
Categories: DBA Blogs

Oracle Recognized Again as a Leader in Gartner Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises

Oracle Press Releases - 16 hours 13 min ago
Press Release
Oracle Recognized Again as a Leader in Gartner Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises Fourth Straight Positioning as a Leader Recognizes Completeness of Vision and Ability to Execute

Redwood Shores, Calif.—Jul 1, 2020

Oracle (NYSE: ORCL) has been named a Leader in Gartner’s “Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises” analysis,[1] with the furthest overall position for Completeness of Vision and highest overall position for Ability to Execute. This is the fourth consecutive year that Gartner has named the company a Leader based on its ability to execute and completeness of vision. A complimentary copy of the report is available here.

According to the report, “Leaders demonstrate a market-defining vision of how core financial management systems and processes can be supported and improved by moving them to the cloud. They couple this with a clear ability to execute this vision through products, services and go-to-market strategies. They have a strong presence in the market and are growing their revenue and market share. In this market, Leaders show a consistent ability to secure deals with enterprises of different sizes and have a good depth of functionality across all areas of core financial management. They have multiple proofs of successful deployments by customers, both in their home region and elsewhere. Their offerings are often used by system integrator partners to support financial transformation initiatives. Leaders typically address a wide market audience by supporting broad market requirements. However, they may fail to meet the specific needs of vertical markets or other, more specialized segments, which might be better addressed by Niche Players in particular.”

“We are once again delighted with Gartner’s recognition of Oracle’s Leader position in the cloud ERP market.” said Rondy Ng, senior vice president, Applications Development, Oracle. “We believe our continued leadership highlights our success in addressing the complex and evolving needs of the world’s leading organizations. Importantly, we further believe the breadth, depth, and continuous innovations of our solutions are a must-have for CFO’s navigating today’s fluid corporate finance environment.”

More than 7,100 organizations across 23 industries and in over 125 countries have adopted Oracle to help drive their finance transformation. Customers are rapidly taking advantage of on-going quarterly innovation updates in machine learning/AI, digital assistants, and world-class analytics to help their finance teams improve decision-making, accelerate processes, and drive efficiencies to deliver the real-time insights needed for competitive advantage.

Over seven years of unparalleled partnership between these customer organizations, implementation partners, and Oracle have also created a mature global ecosystem of expertise to enable fast time-to-value journey to cloud. Oracle and its partner community offer world-class proven services from best practice cloud adoption strategy, to implementation and go-live support, and ultimately ongoing business process optimization and innovation adoption. In addition, customers also benefit from Cloud Customer Connect, a premier, vibrant, 200,000 member strong community, designed to promote peer-to-peer collaboration and engagement with Oracle’s product development experts.

Oracle Fusion Cloud Enterprise Resource Planning (ERP) features a full range of business capabilities including FinancialsProcurementProject Management, Enterprise Performance Management (EPM) and Risk Management & Compliance. With native integration to the broader suite of Oracle Cloud Applications, including supply chain and manufacturing, human resources, and sales, service, and marketing, Oracle provides a complete solution to help customers enhance productivity, reduce costs and improve controls.

Oracle has been named a Leader in more finance and ERP-related Gartner Magic Quadrants. In addition to being recognized as a Leader in Gartner’s 2020 “Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises,1” Oracle was also recently named a Leader in Gartner’s 2020 “Magic Quadrant for Cloud ERP for Product-Centric Enterprises2” and the 2019 “Magic Quadrant for Cloud Financial Close Solutions3” and “Magic Quadrant for Cloud Financial Planning and Analysis Solutions4” reports.

1Gartner Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises, Robert Anderson, John Van Decker, Greg Leiter, 29 June 2020
2Gartner, Magic Quadrant for Cloud ERP for Product-Centric Enterprises, Tim Faith, Duy Nguyen, Denis Torii, Paul Schenck, Christian Hestermann, 22 June 2020
3Gartner, Magic Quadrant for Cloud Financial Close Solutions, Robert Anderson, John Van Decker, Greg Leiter, 21 October 2019
4Gartner, Magic Quadrant for Cloud Financial Planning and Analysis Solutions, Robert Anderson, John Van Decker, Greg Leiter, 8 August 2019

Gartner Disclaimer

Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner’s research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

Contact Info
Rachael Walker
Oracle
+1.704.728.3179
rachael.walker@oracle.com
About Oracle

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

Trademarks

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

Talk to a Press Contact

Rachael Walker

  • +1.704.728.3179

Oracle Ecosystem Ignites Innovation to Accelerate Hospitality Recovery

Oracle Press Releases - 16 hours 43 min ago
Press Release
Oracle Ecosystem Ignites Innovation to Accelerate Hospitality Recovery Oracle Hospitality’s open architecture gives vendor-partners an expedited path to integration helping hoteliers prepare for the new next

Redwood Shores, Calif.—Jul 1, 2020

Oracle Hospitality is committed to helping the hospitality industry get back to business. To support this effort, Oracle, with support from HTNG, recently hosted Innovation Week, a collaborative event dedicated to accelerating the industry’s recovery and accommodating the rapidly evolving demand for low or contactless guest and employee interactions. The inaugural event drew 465 attendees across more than 200 hospitality technology organizations from 48 countries who came together to not only foster dialogue about innovation, but make it a reality. The event offered an expedited pathway to integration with Oracle Hospitality solutions, offering hoteliers more choices in technology to help them prepare for the new next.

During the five-day event, many vendors were able to make significant progress in the development of their integrations.

“We expected to spend weeks building our integration, but with the help of Oracle Hospitality experts, we were able to do it within days,” said Andreas Loeseth, COO of Fácil, which worked on an OPERA Cloud PMS integration enabling self-service check-in and check-out using a mobile app or kiosk. “Innovation Week showed us the level of Oracle’s commitment to open APIs and helping vendors develop new solutions for hoteliers. It’s exactly the type of assistance we need to fuel an industry recovery.”

Learn more about Innovation week and access the on-demand sessions. 

“Oracle Hospitality stands apart in terms of the depth and breadth of the partners who integrate with our flagship platforms OPERA Cloud, OPERA 5 and Simphony. It is unparalleled. We also understand how critical technology is to deliver a great guest experience, especially during this time,” said Alex Alt, senior vice president and general manager of Oracle Hospitality. “Innovation Week successfully paved the way for new and existing vendor-partners to ramp up innovation and help solutions get to market much faster.”

A League of Their Own

In addition to the many hospitality vendors new to the Oracle ecosystem, Innovation Week also featured several Oracle PartnerNetwork (OPN) members with Oracle Validated Integrations focused on low-touch or contactless solutions, including:    

  • ALICE: Offers an all-in-one solution that enables hotel staff to do the most impactful work without all the guesses. The ALICE/Oracle integration is a two-way, real-time interface that connects all hotel operations to the hotel’s PMS, enabling increased visibility across departments. The seamless communication between OPERA and ALICE removes the need for hoteliers to switch between two systems and focus their time and attention on delivering exceptional hospitality. Some of the properties leveraging the ALICE/Oracle integration include Montage Hotels & Resorts, Omni Hotels & Resorts properties, Viceroy Hotels and Resorts and Calamigos Guest Ranch.
  • Hapi: Offers a cloud data hub that enables centralized exposure of data from disparate systems, spanning on premise and hosted. Hapi builds on the Oracle OPERA Cloud’s promise of centralized, open APIs by bringing this functionality to legacy customers now and enabling a smoother transition to the cloud. Integration partners can also use multiple OPERA interface protocols in a single Hapi connection, and large hotel chains can benefit from custom OPERA integrations into their tech ecosystems. The Hapi/ Oracle integration is currently being used by IHG, Accor, Red Lion Hotels, Rosewood Hotels and Resorts, Langham Hotels and Resorts, just to name a few.
  • InnSpire: This 2020 HTNG TechOvation award-winning company offers a contactless, all-in-one technology suite that seamlessly helps drive a world class digital guest experience for some of the world’s most iconic hotels and brands. The key benefits of the company’s integration include accelerating the personalization of the guest experience, leveraging demographics and personal interests to customize in-room digital interfaces, such as Apps, TV-experience and voice. Today, the InnSpire/Oracle integration is being used by LUX* Resorts and Hotels, ME Dubai by Melia and many more.
  • INTELITY: As the leading provider of guest experience management solutions for the hospitality industry, INTELITY unites mobile, in-room and back-office tools into one robust, fully-integrated hospitality platform. INTELITY’s integration with OPERA PMS enhances and streamlines a wide range of guest-facing activities, including contactless check-in, mobile key, touchless service requests and more. INTELITY also integrates with MICROS Simphony, allowing guests to place dining orders from their devices or an in-room tablet. With mutual customers worldwide, some hotels taking advantage of the INTELITY/Oracle integration include Fairmont Hotels & Resorts, Hyatt, YOTEL and Mystic Lake Casino Hotel.
  • Nonius: This 2020 HTNG TechOvation award-winning company offers solutions to help hoteliers provide a great and contactless guest experience. Nonius has integrated its products with Oracle, including Mobile check-in and Key, Guest internet access, TV, Voice & Cast. Some hotels leveraging the Nonius/Oracle integration are from renowned groups such as Belmond, Accor, IHG, Four Seasons, GHM Hotels, Hard Rock Hotels, Marriot, Pestana and many more.
  • Zaplox: Offers contact-free mobile guest journey and mobile key services for the global hotel market. Zaplox solutions save time and costs while providing hotels with a direct channel to communicate with the guests and the opportunity to generate additional revenue. The contact-free mobile guest journey also supports the "Safe Stay” guidelines providing contact-free and touchless options for guests during their stay. The Zaplox/Oracle integration is currently being used by Arlo Hotels, Union Hoteli, ARP Hansen Hotel Group and many other global properties.
 

“Oracle’s architecture is truly open and that directly helps to ignite and accelerate industry innovation,” said Luis Weir, director of product management, Oracle Hospitality integration and APIs. “Our approach allows hoteliers to work directly with our partners to build experiences that are better suited to tackle new challenges and meet evolving guest expectations. And by providing a standardized set of modern, secure APIs, we allow them to do it quickly and cost effectively – delivering innovation by encouraging customization.”

Resources to Accelerate Innovation for the New Next
Contact Info
Christine Allen
Oracle
+1.603.743.4534
christine.allen@oracle.com
About Oracle Hospitality

Oracle Hospitality brings over 40 years of experience in providing technology solutions to independent hoteliers, global and regional chains, gaming, and cruise lines. We provide hardware, software, and services that allow our customers to act on rich data insights that deliver personalized guest experiences, maximize profitability and encourage long-term loyalty. Our solutions include platforms for property management, point-of-sale, distribution, reporting and analytics all delivered from the cloud to lower IT cost and maximize business agility. Oracle Hospitality’s OPERA is recognized globally as the leading property management platform and continues to serve as a foundation for industry innovation. For more information about Oracle Hospitality, please visit www.oracle.com/Hospitality 

About Oracle PartnerNetwork

Oracle PartnerNetwork (OPN) is Oracle’s partner program designed to enable partners to accelerate the transition to cloud and drive superior customer business outcomes. The OPN program allows partners to engage with Oracle through track(s) aligned to how they go to market: Cloud Build for partners that provide products or services built on or integrated with Oracle Cloud; Cloud Sell for partners that resell Oracle Cloud technology; Cloud Service for partners that implement, deploy and manage Oracle Cloud Services; and License & Hardware for partners that build, service or sell Oracle software licenses or hardware products. Customers can expedite their business objectives with OPN partners who have achieved Expertise in a product family or cloud service. To learn more visit: http://www.oracle.com/partnernetwork

About Oracle

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

Trademarks

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

Talk to a Press Contact

Christine Allen

  • +1.603.743.4534

why dba_extents has no answer but dba_segments has results

Tom Kyte - Tue, 2020-06-30 19:06
I have a test environment which has oracle 10.2.0.1 installed, today I want to resize a tablespace named ANNEX which has a size:347921216KB because there is no usable data in it.the usage of the datafile Corresponding this tablespace is 0.7%, ...
Categories: DBA Blogs

Panini Internazionale Achieves Zero Waste Goal with Oracle

Oracle Press Releases - Tue, 2020-06-30 07:00
Press Release
Panini Internazionale Achieves Zero Waste Goal with Oracle Data intelligence helps chain manage inventory to delight customers while balancing the bottom line and eliminating waste

Stockholm, Sweden and Redwood Shores, Calif.—Jun 30, 2020

Panini Internazionale was borne out of a passion for food held by four brothers who opened their first deli in Stockholm, Sweden in 1990. Thirty years later, the fast-food brand delivers fresh and healthy food from 29 shops across Stockholm.

With a focus on contemporary fast-food that is both healthy and green, the only thing fast about Panini is its service. To deliver on its brand promise, goal of zero food waste, and exemplary service, the company relies on Oracle Food and Beverage MICROS Simphony Point-of-Sale (POS).

“We pride ourselves on not only delivering good food and service but also in doing good for our communities,” said Pierre Constantinou, co-founder Panini Internazionale. “With Oracle as our core foundation, we can easily pivot to new channels for our customers to engage how and when they desire while using back-end data to understand our inventory and eliminate waste by sharing our excess stock with those in need.”

Tech Helps Panini Deliver on Brand Values and Bottom Line
embedBrightcove('responsive', false, 'single', '6151962549001');

Panini Cooks Up Innovation and Growth

Panini has been an early adopter of technology, introducing online ordering in the late 1990s. But as the business grew, it realized adding new channels to their POS added complexity and put their zero-waste commitment at risk. Operating a business based on fresh production and same-day consumption requires real-time supply and demand. Panini needed a better way to manage its menu and inventory, and streamline kitchen operations. The company adopted Oracle MICROS Simphony to get the intelligence they needed to manage everything from menu design and supplier relationships to forecasting demand down to the day and time. Panini also used Simphony to identify partnerships that are mutually beneficial to the brand.

Simphony also helped Panini reach its goal of zero waste. It uses technology to allocate remaining stock at a 50 percent discount to students for the last two hours of business, and at the end of the day nearby charities are alerted to pick up final stock. This commitment to zero waste is not only in alignment with their brand values, but it also supports a healthy bottom line.

“Data intelligence will be increasingly important as the restaurant industry rebounds,” said Simon de Montfort Walker, senior vice president and general manager for Oracle Food and Beverage. “Simphony gives Panini Internazionale a single view of their business so they can run operations efficiently, better manage the bottom line, and support their customers and communities.”

Contact Info
Scott Porter
Oracle Corporate Communications
+1 650.274.9519
scott.c.porter@oracle.com
About Oracle

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

Trademarks

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

Talk to a Press Contact

Scott Porter

  • +1 650.274.9519

RMAN backup on Azure Blob Storage

Tom Kyte - Tue, 2020-06-30 00:46
When I deploy Oracle Database on Amazon EC2, I can backup to Object Store with RMAN, using "Oracle Secure Backup Cloud Module for Amazon S3". This is great. When I deploy Oracle Database on Azure, I would like to use Azure Blob Storage with RMA...
Categories: DBA Blogs

Interactive Report pagination automatically stick/unstick to the bottom

Tom Kyte - Tue, 2020-06-30 00:46
Hi, Is there any way to make the interactive report pagination automatically stick and unstick to bottom of page like interactive grid does? I tried the following but it didn't work: <code>$('.a-IRR-paginationWrap--bottom').stickyWidget({ togg...
Categories: DBA Blogs

Most Recent – 2

Jonathan Lewis - Mon, 2020-06-29 07:02

A question arrived in my email a few days ago with the following observations on a statement that was supposed to query the data dictionary for some information about a specified composite partitioned table. The query was wrapped in a little PL/SQL, similar to the following:

declare
        v_src_part      varchar2(30) := null;
        v_tab           varchar2(30)  := 'PT_COMPOSITE_1';
begin

        select
                /*+ qb_name(main) */
                uts1.subpartition_name
        into    v_src_part
        from
                user_tab_subpartitions uts1
        where
                uts1.table_name = v_tab
        and     uts1.last_analyzed is not null
        and     uts1.num_rows = (
                        select
                                /*+ qb_name(max_subq) */
                                max (uts2.num_rows)
                        from
                                user_tab_subpartitions uts2
                        where
                                uts2.table_name = /* v_tab */ uts1.table_name
                )
        and     rownum = 1
        ;

The requirement is simple: identify the subpartitions of a specific table that have the largest number of rows of any subpartition of the table – but report only the first match.

You’ll notice that the where clause of the subquery has a commented “v_tab” in it. This is the PL/SQL variable used in the outer query block to identify the target table, and it shouldn’t really make any difference if I use the PL/SQL variable in the subquery rather than using a correlating column. However, the question that came with this block of code was was follows:

All the partitions and subpartitions had their stats when running the test. On a first run using the correlated subquery the block reported oracle error ORA-01403: no data found. Changing the code to use the PL/SQL variable the block reported a specific subpartition as expected. A few hours later (after changing the code back to use the correlated subquery) the block reported the same subpartition. Have you ever seen anything like this? The Oracle version is 12.1.0.2.

Rule 1, of course, is to be a little sceptical when someone says “Honest, Guv, the stats are all okay”. But I’m going to assume that the statistcs on this table really were complete and that there was no “data-related” reason for this query to behave in such a surprising way.

The email is an invitation to consider two points.

  1. This looks like a bug: the two versions of the query are logically equivalent, they should return the same results if the underlying data had not changed. (In fact, I think the only “legal” way that the query could return ORA-01403 is if there were no stats on any subpartitions of the table in question – any ordinary usage of the dbms_stats package other than delete_table_stats() would have ensured that the query had to find something.) So, the first run of the correlated subquery produced no data while the modified query did get a result. That suggests a problem with some transformation in the 12.1.0.2 code to handle correlated aggregate subqueries.
  2. How could the second execution of the version with the correlated subquery produce a result a few hours later. Here are a couple of possibilities:
    • Someone had gathered dictionary stats (i.e. on the tables used by the query, not on the subpartitioned table) in the “few hours” gap so the optimizer picked a different execution plan which bypassed the bug.
    • (minor variation on previous) Someone had gather dictionary stats when the first execution plan was already in memory but the “auto_invalidate” option for cursor invalidation meant that the query didn’t get re-optimised for a few hours.
    • Nothing changed, but the query had been flushed from the library cache and did need re-optimisation a few hours later. Since the version is 12.1.0.2 this means statistics feedback or automatic SQL directives could have had an impact – which means there may be dynamic sampling during optimisation – and a different set of random samples could have resulted in a different execution plan.
    • Other …
The interesting bit

There is a generic feature about this question that is more interesting than the “what went wrong, how could I get different results”, and it’s in the choice you can make between using a correlation column and repeating a pl/sql variable (or literal value ).

The switch to using a pl/sql variable turns the subquery into a single-row, “standalone”, subquery – one that could be run without any reference to the outer query – and this imposes a dramatic change on what the optimizer can doSometimes that change will make a huge difference to the optimisation time and the run time.

As a correlated subquery the notional “first strategy” for the optimizer is:

“for each row in the outer query execute the inner query as a filter subquery passing in the correlation value

If you take the “standalone” approach the optimizer will be looking for a plan that says (in effect):

“run the subquery once to generate a constant that you will need to execute the rest of the query”

Running the subquery once rather than once per row is likely to be a good idea – on the other hand Oracle can do “scalar subquery caching” so if the value of the correlation column is always the same the correlated subquery will actually run only once anyway.

More importantly, when the optimizer sees a correlated subquery it will consider unnesting it and then transforming it in various other ways; and it might take the optimizer a long time to work out what it can and can’t do, and the plan it finally does produce may be much slower than what it could have done if it had not unnested the subquery.

Some test results

So I ran 3 variations of the PL/SQL block on Oracle 19.3.0.0 with the CBO trace (10053) enabled and picked out a few highlights. The three tests in order were:

  1. Use the pl/sql variable so the subquery could run as a standalone query
  2. Use the correlating column to make the subquery a correlated subquery
  3. Use the correlating column, but add the hint /*+ no_unnest */ to the subquery.

The results were as follows – first the timing, then a critical measure that explains the timing:

  • Case 1 – standalone subquery – total time 0.82 seconds
  • Case 2 – correlated subquery – total time 5.76 seconds
  • Case 3 – correlated subquery with no_unnest hint – total time 0.84 seconds

Where did all that extra time go – a lot of it went in optimisation. How many “Join Orders” were examined for each query

  • Case 1 – standalone subquery – 90 join orders
  • Case 2 – correlated subquery – 863 join orders
  • Case 3 – correlated subquery with no_unnest hint – 90 join orders

If you’re wondering what the 773 extra join orders were about here’s a clue. I extracted all the lines from the case 2 trace file that started with “SU:” – those are the lines tagged for “Subquery Unnest” – using a call to grep -n “^SU:” {tracefile name} and this is the result:


  2945:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
  2947:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
  2948:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
  2949:SU:   Passed validity checks, but requires costing.
  2950:SU: Using search type: exhaustive
  2951:SU: Starting iteration 1, state space = (2) : (1)
  2952:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
  3089:SU: Costing transformed query.
 66112:SU: Considering interleaved complex view merging
 66113:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
 66366:SU: Costing transformed query.
129372:SU: Finished interleaved complex view merging
129373:SU: Considering interleaved distinct placement
129374:SU: Finished interleaved distinct placement
129375:SU: Considering interleaved join pred push down
129376:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
251638:SU: Rejected interleaved query.
251640:SU: Finished interleaved join pred push down
251641:SU: Considering interleaved OR Expansion
251642:SU:   Unnesting subquery query block SEL$4F5F2F29 (#2)Subquery removal for query block SEL$4F5F2F29 (#2)
251651:SU: Finished interleaved OR Expansion
251653:SU: Updated best state, Cost = 19.085153
251654:SU: Starting iteration 2, state space = (2) : (0)
251665:SU: Costing transformed query.
310395:SU: Not update best state, Cost = 20.083998
310396:SU: Will unnest subquery SEL$4F5F2F29 (#2)

The optimizer checks the validity of unnesting (generated) query block SEL$4F5F2F29 at line 2948 of the trace and decides, 308,000 lines later after an exhaustive examination of the possibilities, that it will unnest the subquery. Since this is a recent version of Oracle we take one simple extra step by checking for “TIMER” information, again using a “grep -n” call –

251639:TIMER:  SU: Interleaved JPPD SEL$B73B51DC cpu: 1.263 sec elapsed: 1.263 sec
251652:TIMER: SU: iteration (#1) SEL$B73B51DC cpu: 2.607 sec elapsed: 2.607 sec
310577:TIMER: CBQT SU and CVM SEL$071BB01A cpu: 3.323 sec elapsed: 3.323 sec
433371:TIMER: Cost-Based Join Predicate Push-Down SEL$12B6FE6C cpu: 1.307 sec elapsed: 1.306 sec
433477:TIMER: Cost-Based Transformations (Overall) SEL$12B6FE6C cpu: 4.731 sec elapsed: 4.731 sec
496189:TIMER: SQL Optimization (Overall) SEL$12B6FE6C cpu: 5.306 sec elapsed: 5.306 sec

Of course most of the time spent in this particular example was a result of optimising (and writing the optimizer trace), but for my tiny example (table definition below) the final figures I’ll show are the buffer gets and CPU time reported by a basic 10046 trace file after optimisation with all the relevant data was cached:

  • Case 1 – standalone subquery – 89 buffer gets / 0.00 seconds
  • Case 2 – correlated subquery – 130 buffer gets / 0.53 seconds
  • Case 3 – correlated subquery with no_unnest hint – 121 buffer gets / 0.08 CPU seconds

The sub-centisecond time is a little suspect, of course, but the others seem fairly trustworthy.

Conclusion

The title of this piece is “Most Recent” because the commonest requirement for a query of this shape is find the most recent row matching the following predicates”, even though in this case the interpretation is “find me the row matching the largest value”.

The “standard” pattern for writing a “most recent” query is to use a correlated subquery – but it’s worth remembering that you may reduce optimisation time and run time by “copying down the constant” rather than using the correlation mechanism.

(There are alternative strategies to the subquery approach, of course, and the analytic max() – introduced in Oracle 8i – is gaining traction as one of the popular alternatives.)

Footnote 1

If you want to re-run my test on different platforms and versions of Oracle, here’s the code to generate the table.  (Don’t be surprised if you don’t get completely consistent results – much of the optimization will depend on the size of all the relevant tables (tab$, tabcompart$, etc.) in the data dictionary, rather than on the actual definition of this partitioned table.


em
rem     Script:         most_recent_3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2020
rem
rem     Last tested 
rem             19.3.0.0
rem

create table pt_composite_1 (
        id,
        grp,
        small_vc,
        padding
)
nologging
partition by range(id) 
subpartition by hash (grp)
subpartitions 4
(
        partition p2 values less than (400),
        partition p3 values less than (800),
        partition p4 values less than (1600),
        partition p5 values less than (3200)
)
as
select
        rownum                          id,
        trunc(rownum/50)                grp,
        to_char(trunc(rownum/20))       small_vc,
        rpad('x',100)                   padding
from
        all_objects
where 
        rownum <= 3000 -- > comment to avoid wordpress format issue
;

execute dbms_stats.gather_table_stats(user,'pt_composite_1',granularity=>'ALL')


Footnote 2

For reference, here are the outputs I got from executing egrep -n -e”^SU:” -e”TIMER” against the other two CBO trace files.

First for the “standalone” form – note how line 3130 tells us that “there is no correlation”.


806:SU: Considering subquery unnesting in query block MISC$1 (#0)
2947:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
2952:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
2953:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
2954:SU:     SU bypassed: No correlation to immediate outer subquery.
2955:SU:     SU bypassed: Failed basic validity checks.
2956:SU:   Validity checks failed.
3130:SU:     SU bypassed: No correlation to immediate outer subquery.

Then for the correlated subquery with /*+ no_unnest */ hint; and line 3122 tells us that SU was bypassed because of a hint/parameter:


809:SU: Considering subquery unnesting in query block MISC$1 (#0)
2945:SU: Unnesting query blocks in query block SEL$071BB01A (#1) that are valid to unnest.
2947:SU: Considering subquery unnest on query block SEL$071BB01A (#1).
2948:SU:   Checking validity of unnesting subquery SEL$4F5F2F29 (#2)
2949:SU:     SU bypassed: Not enabled by hint/parameter.
2950:SU:     SU bypassed: Failed basic validity checks.
2951:SU:   Validity checks failed.
3122:SU:     SU bypassed: Not enabled by hint/parameter.

Neither file showed any “TIMER” information since that appears, by default, only for steps that take longer than one second. (If you want to adjust the granularity, see Franck Pachot’s note on parse time that describes bug/fix_control 16923858.

db_16k_cache_size, db_block_size parameter setting

Tom Kyte - Mon, 2020-06-29 06:26
This is about the db_block_size, db_16k_cache_size parameters. Oracle document says, Statement 1: DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE...
Categories: DBA Blogs

Android Oracle Client 2.0

XTended Oracle SQL - Sun, 2020-06-28 07:42

I’ve just released new version of my Simple Android Oracle Client.

New features:

  • Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20.
  • SQL Templates: now you can save and load own script templates
  • Server output (dbms_output)
  • Export results as JSON, CSV and HTML files (long tap on results)
  • Copy results to the Clipboard as JSON or CSV

I use it just for basic troubleshooting and small fixes, but, please, let me know if you need anything else.
Screenshots:

Categories: Development

Our best blogs for Oracle Integration Cloud – OIC

Online Apps DBA - Sun, 2020-06-28 02:30

Wondering why reading about OIC from a lot of resources did not prove to be helpful? Trust us when we say that the blog https://k21academy.com/oic38 will leave you amazed with the simplicity and ease of learning OIC. We have shared with you the best blogs about OIC which will prove to be beneficial in understanding […]

The post Our best blogs for Oracle Integration Cloud – OIC appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Registry in Oracle Cloud(OCI)

Online Apps DBA - Sun, 2020-06-28 01:03

Do you know how Oracle Cloud stores, manages & deploys Docker images securely? Oracle Cloud Infrastructure Registry is an Oracle-managed registry that enables you to store, manage & deploy images in a very safe and secure manner. To know more about OCI Registry, read the blog k21academy.com/1z0108413 and discover how Cloud services keep images safe. […]

The post Registry in Oracle Cloud(OCI) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Foreign Keys

Tom Kyte - Fri, 2020-06-26 23:46
Hi Tom, this question was put to me by a developer/designer and as I couldn't a) Find the Answer or b) make up a convincing answer I thought I would ask you. Basically it is .... HOW does Oracle make the changes to a child table when ...
Categories: DBA Blogs

Remote link "insert into select from" Gets Errors

Tom Kyte - Fri, 2020-06-26 05:26
I am trying to insert data from an Oracle 12c DB into an Oracle 11g DB by remote link. The remote link works fine for selects and normal DML operations, single inserts also work fine. When I use: insert into TableA@RemoteLink (col1,col2,col3) sel...
Categories: DBA Blogs

"ORA-03150: end-of-file on communication channel for database link ORA-02063: preceding line from SAP_INCR_LINK"

Tom Kyte - Fri, 2020-06-26 05:26
DB Link is failing with some connection issue when procedure is running.
Categories: DBA Blogs

OWA_UTIL.GET_CGI_ENV Unavailable

Tom Kyte - Fri, 2020-06-26 05:26
Hello everyone, I've tried to scour the internet and can't find the answer to this anywhere else. I have the use case where I'd like to be able to see the IP_ADDRESS of a user requesting data from an RESTful Service endpoint. Upon my testing, the ...
Categories: DBA Blogs

[AZ-301] Microsoft Azure Architect Design Certification Exam: Everything You Need to Know

Online Apps DBA - Fri, 2020-06-26 04:44

You know what we have been thinking lately? We have been thinking of how Certifications could actually be a HUGE step towards your dream job? Certifications have these magical powers (not any less amazing than magic) to boost up your resume, and recruiters value it enough to choose you amongst the crowd. One such certification […]

The post [AZ-301] Microsoft Azure Architect Design Certification Exam: Everything You Need to Know appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[AZ-900] Microsoft Azure Monitoring & Reporting: Cloud Monitor & Service Health

Online Apps DBA - Fri, 2020-06-26 01:37

Ever wondered how does Microsoft Azure function smoothly? You will be amazed to know that Cloud services need timely monitoring and health service checkups to maintain the smooth functioning of the Cloud Services. Trust us when we say that the blog https://k21academy.com/az90026 will help you explore the Cloud Monitoring and Health Services Checkup in-depth Our […]

The post [AZ-900] Microsoft Azure Monitoring & Reporting: Cloud Monitor & Service Health appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[AZ-900] Microsoft Azure Security Services: Security Center, Key Vault, AIP & ATP

Online Apps DBA - Fri, 2020-06-26 01:18

Ever wondered how secure Microsoft Azure is? You will be amazed to know the Security Services offered by Azure, which includes Azure Security Center, Azure Key Vault, Azure Information Protection(AIP), and Azure Advanced Threat Protection(ATP). Trust us when we say that the blog https://k21academy.com/az90024 will help you discover the Security Services in-depth. Our blog covers: […]

The post [AZ-900] Microsoft Azure Security Services: Security Center, Key Vault, AIP & ATP appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator