Feed aggregator

Performance Tuning

Tom Kyte - Sat, 2016-09-17 02:06
I'm junior DBA and i join an organization in last month. we are using oracle 11g Standard Edition. we does not have licence of performance tuning so I have a performance tab disable in EM Manager . I want to find out Top SQL , Top Activity , To...
Categories: DBA Blogs

Relation between SCN and checkpoint SCN

Tom Kyte - Sat, 2016-09-17 02:06
Hi Tom, I have gone through many of your threads regarding SCN and I have a few questions regarding them. 1. Is SCN generated only for committed transctions and that is when the transaction is committed not when the transaction is begun, right ...
Categories: DBA Blogs

Need to generate script to delete values from various tables

Tom Kyte - Sat, 2016-09-17 02:06
I want to delete values from various tables. These table name are in a column field. Will this be possible . For example lets say Table A has column as SID. And these SID are the table names too. I want to pick values from SID and delete values f...
Categories: DBA Blogs

Convert an Integer into Years, Month and Days.

Tom Kyte - Sat, 2016-09-17 02:06
Hello- I am working on a seniority report, for which I need to compute number of years in employment but with the deduction in the number of days of un-paid absences. I have all the computations done, and have the number of days a employee is em...
Categories: DBA Blogs

How to zero fill a sum of a count field using SQL

Tom Kyte - Sat, 2016-09-17 02:06
For two records, this part of the select returns 2, would like to return 000000000000002 (sum(count(distinct(RPAD(to_char(spriden_pidm),15,'000000000000000'))))) Record_count
Categories: DBA Blogs

Alternative to global temp tables?

Tom Kyte - Sat, 2016-09-17 02:06
I asked this in a "review" of a related question (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9531768700346881365) but it may have been considered slightly off topic so here it is by itself. I was a SQL Server developer for ...
Categories: DBA Blogs

move tables within same tablespace

Tom Kyte - Sat, 2016-09-17 02:06
Hi Tom, Is it possible to move tables from one datafile to another datafile within the same tablespace..? Could you please suggest me. Thanks, Prakash
Categories: DBA Blogs

PL/SQL how to quote variables?

Tom Kyte - Sat, 2016-09-17 02:06
Hi team askTom: This is not an export/import question, more of a pl/sql and 'quoting' question, and I always have trouble quoting my variables, especially those that get evaluated at run time. Here I am trying to export 2 schemas, so I wrote a ...
Categories: DBA Blogs

Will data returned from "select for update" once row lock is obtained always contain the latest committed values?

Tom Kyte - Sat, 2016-09-17 02:06
Hello Hopefully a quick question to confirm my understanding of "select for update" and data consistency. I have a primary JDBC thread with autocommit false that begins a transaction, selects a batch of row "for update skip locked", and recurse...
Categories: DBA Blogs

Dynamic Flying Dashboard UI in ADF

Andrejus Baranovski - Fri, 2016-09-16 23:43
I took my old sample application implemented in 2010 - Dynamic Flying Dashboard UI Shell and upgraded it to recent ADF It runs pretty well, without major changes - this proves ADF migration between releases can be smooth (well, not always in practice - but let's be positive).

I have recorded gif to give you an impression how it runs and how UI switch works in ADF UI panel dashboard component:

There are four blocks in the dashboard, user can remove each of the blocks or select predefined group of blocks:

It is possible to maximize selected block to occupy more space:

Some of the blocks can be minimized and only a subset of blocks will be displayed:

Such functionality is implemented with out of the box ADF UI components - af:panelStretchLayout, af:panelSplitter and af:panelDashboard. Depending on selection, block is minimized, displayed or maximized:

Toolbar items (minimize, maximize and restore) are implemented in the separate JSF container and reused in the main page:

Hopefully you will find such use case interesting and will have a chance to apply in the project. Download sample application - ADFIntegrationDashboard_v2.zip.

Oracle ACE Director Briefing – Day 2

Tim Hall - Fri, 2016-09-16 22:58

ace-directorToday was Day 2 of the Oracle ACE Director Briefing.

As with yesterday’s post, we had a number of speakers coming in to talk about specific areas of the Oracle stack and Oracle Cloud. Once again, I’m not going to mention any details.

There were a few areas that were super-relevant to what’s going on with me at work, so it was good to make some more contacts in those areas.

The shear volume of stuff going on at Oracle at the moment is actually quite scary. I’m sitting in the sessions wondering how I’m going to do my job, learn all the new regular Oracle stuff and learn all the new Oracle Cloud stuff as well. It’s quite daunting…

Thanks to the folks at the Oracle ACE Program for making this event happen. This last two days has been like a full-on conference, complete with multiple tracks. Your efforts are greatly appreciated. Thanks to all the speakers too. I know we sometimes give you a hard time, but we do appreciate you taking the time to come and speak to us!

I’ve got tomorrow off, then the crazy world of Oracle OpenWorld 2016 starts!



Oracle ACE Director Briefing – Day 2 was first posted on September 17, 2016 at 4:58 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

SQL Server 2016 : Basic availability groups may fit to your HA needs

Yann Neuhaus - Fri, 2016-09-16 13:22

In this blog let’s talk about SQL Server and availability groups in standard Edition. I had some customer questions about existing limitations and this is a good opportunity to review them on this blog post. First of all, let’s say that Microsoft has already documented the restrictions here and from my side, I already written a blog post on the subject at the time of the CTP version of SQL Server 2016. In the meantime, some changes occurred and are now included in the RTM version.

As a reminder, the Standard Edition offers limited capabilities of availability groups and it tends to be a replacement of previous feature called mirroring. This is a good news for customers that may finally upgrade their old environment.

The first main limitation concerns the number of replicas capped to only 2 and with no possibility to use the secondary replica for Reporting and backups offloading as well. But we want to use a Standard Edition and it is supposed to be sold at a lower cost right?

Let’s just have a quick check by using the T-SQL script. Generally speaking, I’m not a big fan of the GUI! So if I try to create an availability group with 3 replicas, I will obviously face the following error message:

Msg 35233, Level 16, State 0, Line 2
Cannot create an availability group containing 3 availability replica(s). 
The maximum number of availability replicas in an availability group  with (basic) is 2. 
Reenter your CREATE AVAILABILITY GROUP command specifying fewer availability replicas.

Let’s try to use a secondary replica as read-only replica (ALLOW_CONNECTIONS = ALL)

-- primary replica
WITH (ENDPOINT_URL = N'TCP://WIN20121SQL16.dbi-services.test:5023',  
    BACKUP_PRIORITY = 50,   
WITH (ENDPOINT_URL = N'TCP://WIN20124SQL16.dbi-services.test:5022',   
    BACKUP_PRIORITY = 50,   

Well another error message I got here:

Msg 41199, Level 16, State 8, Line 2
The specified command is invalid because the Always On Availability Groups allow_connections feature
is not supported by this edition of SQL Server. For information about features supported by the editions
of SQL Server, see SQL Server Books Online.

However, I noticed a big change compared to my first test with a CTP version concerning the possibility to add a listener to my availability group. From my point of view, this is a very good improvement compared to the mirroring feature because no changes will be required at the application layer with additional options at the connection string level. Let me prove it with the following script:

USE [master]
((N'', N'')
, PORT=1433);

We can check the listener creation by using DMVs to get the listener configuration as shown below:

blog 103 - STD AG - listener 1

According to my discussions with some customers at our last SQL Server 2016 event, availability groups in Standard Edition may also suggest that more complex infrastructures like multi-subnet availability groups or new special features like direct seeding will not be supported. A first look at the Microsoft documentation doesn’t provide any clues. You can argue that the Microsoft documentation is black-list oriented and if it is not written explicitly in the documentation it should work and you are probably right. But you know, I’m curious and I was interesting to check myself on my lab environment!

For the both suppositions, I can confirm that I didn’t face any issue when creating an availability group with a different setup scenario on the top of multi-subnet WSFC or when using direct seeding. Here the script used to create my availability group with a listener and two IP addresses on different subnet. I also have enabled the direct seeding feature by configuring the SEEDING_MODE parameter to AUTOMATIC as shown below.

-- Availability groups with SEEDING_MODE = AUTOMATIC
WITH (ENDPOINT_URL = N'TCP://WIN20121SQL16.dbi-services.test:5023',  
    BACKUP_PRIORITY = 50,   
WITH (ENDPOINT_URL = N'TCP://WIN20124SQL16.dbi-services.test:5022',   
    BACKUP_PRIORITY = 50,   

-- Listener with two IP addresses on a different subnet 
((N'', N''),
 (N'', N'')
, PORT=1433);

Now let’s have a look at the availability group configuration and direct seeding parameter value

blog 103 - STD AG - automatic seeding

Here the listener configuration retrieved from the sys.availability_group_listeners DMV:

blog 103 - STD AG - listener 2

Let’s take this opportunity to change the replication type to ASYNCHRONOUS. In fact, I had in mind the mirroring feature when using the asynchronous replication that was only available with Enterprise edition. This is another interesting point and now we may benefit from both asynchronous and synchronous replication mode regardless the edition. By the way this feature is clearly documented in the BOL.

blog 103 - STD AG - async replication

Finally let’s check if direct seeding works as expected. If you don’t know what direct seeding is please refer to my previous blog. Just as reminder, we must first configure direct seeding on the secondary by granting the correct permission to the availability group itself.


Here we go. Let’s finally add the database to my existing availability and let’s see if direct seeding comes into play


To get data from direct seeding session states we may use DMVs like sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats as well.

blog 103 - STD AG - direct seeding

Sounds good right?

Bottom line

Basic availability groups feature is shipped with limited features. Yes, but in a meantime it will still respond to a large number of customer scenarios where we have to find a balance between high availability options and costs. Referring to some discussions I had with some customers, they don’t need special features provided by availability groups like readable secondaries or offloading backups. They just want to benefit from high availability for only some critical applications. I’m looking forward to see basic availability groups at customer shops!





Cet article SQL Server 2016 : Basic availability groups may fit to your HA needs est apparu en premier sur Blog dbi services.

Oracle Openworld SF: Windows and .NET Sessions and demogrounds

Christian Shay - Fri, 2016-09-16 12:00

I'll be at Oracle Openworld San Francisco (Sept 18-22) next week and we have a host of sessions aimed for .NET developers and Oracle Database on Windows administrators. Click the links below for the session times and locations.

Oracle Database on Windows Sessions
 Active Directory and Windows Security Integration with Oracle Database [CON6573]
Best Practices for Oracle Database Performance on Windows [CON6575]
What's New with Oracle Database 12c on Windows: On-Premises and in the Cloud [CON6576]

.NET Development Sessions

Best Practices for Performance Part 1: .NET and Oracle Database [CON6574]
Best Practices for Performance Part 2: .NET and Oracle Database [CON6610]
PL/SQL Programming for .NET Developers: Tips, Tricks, and Debugging [CON6612]
What’s New for .NET Developers in Oracle Database [CON6577]
Best Practices for High Availability: .NET and Oracle Database [CON6580]
Meet the Oracle Programming and Scripting Experts [MTE7200]

I've also uploaded a map showing where the demobooth is located (SDB-003 in Moscone South).
Click here to view the image in full res.

#OOW16: What You Must Add to Your Agenda

WebCenter Team - Fri, 2016-09-16 09:42

We can't believe that OpenWorld is just days away. As you can tell from the past month, we've been working hard on getting sessions, demos, hands on labs and a slough of various other things ready in preparation for the event. We are excited to see familiar faces next week and meet new contacts as well! We wanted to highlight some of the "must add" things to your agenda.

Must Attend Sessions:
Don’t miss the Strategy and Vision sessions for the overall Content and Experience Management portfolio and each of the respective solution and cloud technologies. Attendees will also get an opportunity to hear from customer organizations on implementation successes.

Monday, Sep 19

  • Content and Experience Management: Roadmap and Vision  [CON7256]
    • 11:00 a.m. - 11:45 a.m. | Moscone West—2014
  • Oracle Process Cloud Service: Digital Process Apps Made Simple [HOL7613]
    • 11:00 a.m. - 12:00 p.m. | Hotel Nikko—Nikko Ballroom III (3rd Floor)
  • Content Management in the Cloud: Strategy & Roadmap [CON7257]
    • 12:30 p.m. - 1:15 p.m. | Moscone West—2014
  • Digital Experience in the Cloud: Strategy & Roadmap [CON7258] 
    • 1:45 p.m. - 2:30 p.m. | Moscone West—3000
  • Create an Employee Portal Using Oracle Documents Cloud Service/Oracle Sites Cloud Service [HOL7607]
    • 2:30 p.m. - 3:30 p.m. | Hotel Nikko—Nikko Ballroom II (3rd Floor)

Tuesday, Sep 20

  • Drive Productivity in Your Enterprise with Oracle Documents Cloud Service [HOL7611]
    • 8:00 a.m. - 9:00 a.m. | Hotel Nikko—Nikko Ballroom II (3rd Floor)
  • WebCenter Content, Imaging, Capture & Forms Recognition: Roadmap & Strategy [CON7259]
    • 4:00 p.m. - 4:45 p.m. | Moscone West—2014
  • Oracle WebCenter Digital Experience: Sites & Portal Strategy and Roadmap [CON7260]
    • 5:15 p.m. - 6:00 p.m. | Moscone West—2014

    Wednesday, Sep 21

    • Deep Dive into Oracle's Content and Experience Cloud Platform [HOL7612]
      • 11:30 a.m. - 12:30 p.m. | Hotel Nikko—Nikko Ballroom II (3rd Floor)
    • Cox Enterprises Reimagines the Digital Workplace with Oracle WebCenter [CAS4789] 
      • 3:00 p.m. - 3:45 p.m. | Marriott Marquis—Golden Gate C3

    Thursday, Sep 22

    • Create Rich Solution Templates with Oracle's Content and Experience Cloud Platform [HOL7616]
      • 9:45 a.m. - 10:45 a.m. | Hotel Nikko—Nikko Ballroom II (3rd Floor)

    There are PaaS General Sessions and many more sessions highlighting customer successes, product deep dives, partner discussions, persona and/or industry based discussions, Cloud/PaaS lessons, live product demonstrations and Hands-On-Labs (HOL) sessions so do bookmark the following links for a complete, up to date listing:

    My Schedule is available for Oracle OpenWorld! Customer and partner attendees can use My Schedule to plan and optimize their time during the conferences by building personalized conference schedules beforehand. We recommend adding the sessions mentioned above and the others of interest from the Focus on Documents listed above.

    Content and Experience Management Auxiliary Events at OOW
    In addition to regularly scheduled programs of sessions, hands-on labs and demos, we have planned additional events for our customers and partners to network with their peers. These auxiliary events include:

    • Innovation Awards Ceremony | Tuesday 9/20 | 4:00pm – 6:00pm | YBCA Theater | Event Contact | OOW Registration Required
    • Oracle Appreciation Event at OOW16 with Billy Joel, Sting & Gwen Steffani | Wednesday 9/21 | 7:00pm – 11:00pm | Event Information

    Oracle OpenWorld Content and Experience Management Website
    At this year’s Oracle OpenWorld, you will have a much more simplified and engaging experience, from any device – web, phone, tablet! The Oracle OpenWorld Content and Experience Management website is the ONLY link you will need to keep tabs on all things Digital Experience at Oracle OpenWorld 2016. You will find information on the upcoming sessions, the day’s agenda, ready information on Hands-on-Labs, Demo locations and hours, and more. Live twitter feed and pictures will allow you to experience the conference in real time. The site is mobile-first so you can enjoy a rich, dynamic mobile experience. They say…you have to experience it to believe it, and @ #OOW16 you will get your chance! Please bookmark this website to stay in the know of all things #OracleDX at #OOW16. https://sites.oracle.com/oowdx

    Mobile Challenge App Game
    The #OOW16 Mobile Experience will include an interactive, Pokémon Go-styled, mobile app that was built using Oracle Mobile Cloud Service, through which you can win prizes. More details can be found here!

    Social Media Communications
    We will be highlighting our key sessions and other important information on the Oracle DX blog periodically. In addition, please use the following hashtags to discuss OOW on your respective channels!

    • #OOW16 | #OracleDX 

    And we are asking you to please follow along and join the conversations on our social media channels.

    We are looking forward to a successful #OOW16!

    Wait event

    Tom Kyte - Fri, 2016-09-16 07:46
    Hi team, Can you pls brief me regarding this wait event because i have continuously and every time getting this event. So pls let me know what is the meaning of this event and how to resolve this what is the solution of this event. EVENT ...
    Categories: DBA Blogs

    how to recover table with out backup

    Tom Kyte - Fri, 2016-09-16 07:46
    Hi Tom, one table is dropped, there is no backup and no flashback enable, then how can we restore the table. thanks.
    Categories: DBA Blogs

    Audit commit delay

    Tom Kyte - Fri, 2016-09-16 07:46
    Hi team, Every day we got some alert like - AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail Could you pls let us know what is this and how to resolve this ? thanks
    Categories: DBA Blogs

    The dbi Open Infrastructure Technology Division

    Yann Neuhaus - Fri, 2016-09-16 07:37

    As you already might know dbi (on a technical level) is organized into several technology divisions. Traditionally there is one for Oracle, one for Microsoft and one for Middleware products. Almost a year ago a new technology division was born to reflect the new requirements we see on the market. This does not mean that we did not provide expertise in these areas before but the goal was to more clearly organize ourself for being able to rapidly adopt new technologies and provide the best expertise for our current and future customers. In this post I’ll take a look at our Open Infrastructure Technology Division. What is it about, what do we believe in and what you can expect now and in the future.

    To start with: What do we call a technology division? As the name already adumbrates it is all about technology. You could also say: The technology division are the heart of dbi. This is where people work on the different technologies, organize technical events, do researching, define and plan new workshops, adopt new products and have lots of fun. Each of these technology divisions is led by a so called technology leader who is responsible for his technology division. Currently dbi supports four of these divisions which reflect the areas we work in:


    On the top there is Hervé who is our CTO and then we have the four technology divisions:

    • We have Franck who is responsible for all aspects around the Oracle database
    • We have Stéphane who is responsible for the Microsoft stuff
    • We have Gérard who is responsible for everything around the middleware products we support
    • And finally we have the Open Infrastructure Technology Division which I am happy to represent

    A very important point to notice here is that each of these divisions is cross connected to every other. We are not working in isolated silos but do work together wherever we can. Nowadays you can never be sure what happens and things have recently happened you never thought would be possible in the past, e.g.

    … just to name a few. Do you know if you need a SQL Server expert on Linux tomorrow? Are you running Oracle on Windows? Are you planning to run Docker on Windows? These are just a few examples where you might need expertise which goes beyond the traditional “I only do Windows” or “I only do Oracle” way of working. This is what the technology divisions are there for: Build expertise inside the divisions but even more important build a kind of “backbone” where everyone can get help from others (inside and outside of dbi). Share what you learned, be open, be communicative, be mindful and have an open eye on what is going on around you. No matter which technology division you take: All of them share their knowledge by writing blogs, attending, organizing and speaking at events (e.g. DOAG 2016, Oracle Open World 2016, IT-Tage 2016, SWISS PGDay, PASS SQLSaturdays and many more), writing articles, creating hands-on workshops and even come together twice a year for exchanging knowledge across the technology divisions.

    So far for the overview (which is far longer that it intended to be, sorry). Lets focus on the Open Infrastructure Division from now on. Probably the most important point in any division is what it stands for, so what do we stand for? As you can see from the name the word “Open” plays a central role. We do not only believe in open source software but even more important believe that being and thinking open is key to success. There are plenty of wonderful open source projects out there, each of them having an active community and each of them supporting business critical applications. Our idea is not to favor one over another but rather selecting the right technology for the right requirement. This might range from one specific product to a whole set of products. This could even result in extending (or interfacing) a proprietary solution by open source solutions when it makes sense (remember the cross connected divisions described above).

    On the other hand we can not support the whole open source world because this is just not possible so we have to select a kind of technology set we want to focus on (more on that later). This set is and will be changing over time, we are absolutely aware of that. New technologies will come in, others will go out and some will stay. That’s the way of life. Important for us is the “Open Infrastructure” because when things change you need to adopt. When you keep your infrastructure open it is much more easy to adopt than when you have to adopt your closed systems. When you focus on real open standards, open formats and open software you are free to decide in which direction you want to go and even if it was the wrong decision you are not on a …


    … you are free to adjust the direction :) This is more or less what we believe in and work on every day: Making infrastructures more open, easier to maintain, easier to adapt, faster to profit from and more fun to work with. Yes, work can be fun when you have the right tools and the right people to work with.

    Going deeper into our division what do we actually provide? We had various internal discussion about that and it was not easy to come to a final conclusion. Indeed there is no final set or conclusion but we do provide what we call a “core” set. This are our core technologies we currently work on, provide best practices, provide trainings for (some), provide consulting (all), provide SLAs (some), implement (all) and do reviews or feasibility studies (all). So, finally here they are (bottom up):

    On the Linux level:

    logo_redhat logo_centos logo_suse logo_debian logo_ubuntu

    On the rdbms level:

    logo_mariadb logo_postgresql logo_mysql

    On the NoSQL level:

    logo_mongodb logo_cassandra

    On the provisioning and deployment level:


    On the monitoring level:


    On the DevOps and OpenStack level:

    logo_atomichost logo_docker logo_mirantis

    This are quite a few but not too much technologies. As already stated somewhere above we do not provide everything for every product but we are constantly extending our knowledge and best practices so that products might be fully supported by us in the future which are not today. Other products we are working on are not even listed above (e.g. the E(astic search) L(ogstash) K(ibana) stack). Believe me: It is really hard to select the right tools for the right job: This is what we are here for.


    Cet article The dbi Open Infrastructure Technology Division est apparu en premier sur Blog dbi services.

    Oracle OpenWorld 2016 Delivers the Ultimate Cloud Experience

    Oracle Press Releases - Fri, 2016-09-16 07:00
    Press Release
    Oracle OpenWorld 2016 Delivers the Ultimate Cloud Experience Conference marks 20th year with a positive economic impact totaling $3.2 billion for the City of San Francisco

    ORACLE OPENWORLD, San Francisco, Calif.—Sep 16, 2016

    This weekend, Oracle welcomes thousands of customers, hundreds of partners, and millions of online attendees from more than 141 countries to Oracle OpenWorld 2016. This year’s ultimate cloud experience, which runs through September 22, brings together top technologists, world leaders, and innovators to share ideas about the future of technology and its impact on the way we live and work. A dynamic line-up of visionaries headline the event, spanning today’s brightest thinkers like best-selling author and educator Adam Grant; renowned economists including Moody’s Mark Zandi; and key political strategists Stephanie Cutter and Mike Murphy. At Oracle’s Customer Appreciation event, record-breaking performance artists Gwen Stefani and Sting take the stage at AT&T Park for an exclusive concert. Top-charting band American Authors kick off Oracle OpenWorld in a special performance at Oracle Cloud Plaza on Howard Street.

    On Sunday night, Oracle Executive Chairman of the Board and Chief Technology Officer Larry Ellison opens main stage conversations in a keynote about how Oracle innovations are transforming enterprise cloud computing and outlines his vision for the future of IT. Throughout the week, Ellison, Oracle CEO Safra Catz, Oracle CEO Mark Hurd and Oracle President of Product Development Thomas Kurian, will discuss today’s most critical topics, alongside some of the most powerful names in the world of technology and business.

    “Oracle OpenWorld 2016 focuses on the success of our customers,” said Judy Sim, Oracle’s Chief Marketing Officer. “Our approach is always to put our attendees first, and in doing so, we ensure that every aspect of the conference sparks learning, fosters networking, promotes innovation, and unites our customers and partners.”

    To Learn and Explore:
    • Sessions: choose from 2,240 sessions presented by 2,054 customer and partner speakers, 400 Oracle demos, as well as hundreds of partner and customer exhibitions.
    • Oracle Keynotes:
      • Sunday, 5:00 p.m. – Oracle Executive Chairman and Chief Technology Officer Larry Ellison leads with the future of Oracle Cloud.
      • Monday, 9:15 a.m. – Oracle CEO Mark Hurd shares his perspective on the future of technology and the world ahead, and speaks with leaders from top Oracle customers including Joanna Fielding, Chief Financial Officer, HSBC Technology and Services and Patrick Benson, Chief Information Officer, ClubCorp.
      • Tuesday, 8:30 a.m. – Oracle President of Product Development Thomas Kurian showcases new innovations across Oracle infrastructure, platform, applications and data.
      • Tuesday, 1:30 p.m. – Larry Ellison returns to the mainstage to discuss the future of cloud database and infrastructure.
      • Wednesday, 9:15 a.m. – John Fowler, Oracle Executive Vice President of Systems, leads an engaging session on database platforms of the future, featuring Juan Loaiza, Senior Vice President, Oracle Systems Technology, and Oracle Executive Vice President David Donatelli.
    • Oracle Partner Keynotes:
      • Sunday, 5:15 p.m. – 6:00 p.m. – Diane Bryant, Executive Vice President and General Manager of Intel’s Data Center Group, shares the technology innovations that Intel, Oracle, and Oracle partners are creating to deliver the next wave of enterprise opportunity.
      • Tuesday, 8:30 a.m. – 9:15 a.m. – Bhanu Murthy B.M., President and Chief Operating Officer, Wipro, outlines how cloud-powered enterprises are creating value and gaining competitive advantage in the new digital economy.
      • Tuesday, 1:30 p.m. – 2:15 p.m. – Dr. Vishal Sikka, Chief Executive Officer, Infosys, offers his thoughts on how business leaders can leverage artificial intelligence to drive agility and deliver new kinds of value for their companies and stakeholders.
    • Oracle Leader’s Circle: Learn from today’s top educators, economists, political scientists and industry thought leaders at this empowering two-day executive program hosted by Oracle CEOs Safra Catz and Mark Hurd.
    • Inclusive Leadership Summit: Hear from executives and thought leaders on the cutting edge, including social justice pioneer Billie Jean King, as they connect and share personal perspectives on how inclusive leadership advances employee engagement and increases innovation.
    • Collective Learning Pilot: Experience interactive sessions redesigned by Harvard Graduate School of Education’s Project Zero, which uses discussion, personal reflection and information sharing to solve attendees’ business challenges as a community.
    • Oracle Education Foundation’s Design Realization Garage: Get creative in hands-on, student-led innovation sessions and watch Design Tech High School (d.tech) students demo their technology prototypes created in Oracle Education Foundation workshops.
    To Support the Community and Environment:
    • Sustainability and Giving: Uncover a fresh take on food and beverage while enjoying local, organic, healthy food options. Eighty percent of the food at Oracle OpenWorld is sustainably sourced and prepared within 100 miles of Howard Street. Throughout the week of the event, Oracle will donate 7,275 meals to charities in San Francisco.
    • Plant a Billion Trees: Learn how The Nature Conservancy and Oracle save forests through donations to plant trees in the United States, Brazil and China that will sequester 290K metric tons of carbon. Oracle will donate $1 million over the next four years.
    • JavaOne4Kids: Take part in a one-of-a-kind children’s workshop to inspire the next generation of coders and developers, hosted by Oracle Academy.
    • Education: Participate in one of the many education-themed attractions and activities happening at the event. Learn more about Oracle’s $4 billion annual commitment to education and its positive impact on more than 3 million students in 110 countries annually through Oracle Academy, Oracle Giving, and Oracle Education Foundation.
    To Connect and Play:
    • ORACLE TEAM USA: Meet the America’s Cup champion sailors at Oracle Cloud Plaza on Howard Street and learn how Oracle technology is helping the team defend its title for a second time at the 35th America’s Cup in Bermuda.
    • The Café on Howard: Reserve seats for meetings, relaxation, networking and reflection.
    • Concerts: See best-selling recording artists, Gwen Stefani and Sting, and celebrate with Oracle customers and partners at the Oracle Appreciation Event in AT&T Park. Also, watch American Authors kick off Oracle OpenWorld in a special performance at the welcome reception on Howard Street.

    Following Oracle OpenWorld, Oracle Cloud Days will launch a three-month, 60-city tour showcasing the power of data-driven innovation and digital transformation.

      • Download Oracle OpenWorld (Android | Apple) and JavaOne (Android | Apple) mobile applications
      • Live Stream keynotes live from Oracle OpenWorld
      • Like Oracle OpenWorld on Facebook
      • Follow @OracleOpenWorld on Twitter for the latest #oow16
    Contact Info
    Julie Sugishita
    Oracle Corporate Communications
    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.

    About Oracle OpenWorld

    Oracle OpenWorld, the industry’s most important business and technology conference for the past 20 years, hosts tens of thousands of in-person attendees as well as millions online. Dedicated to helping businesses leverage Cloud for their innovation and growth, the conference delivers deep insight into industry trends and breakthroughs driven by technology. Designed for attendees who want to connect, learn, explore and be inspired, Oracle OpenWorld offers more than 2,200 educational sessions led by more than 2,000 customers and partners sharing their experiences, first hand. With hundreds of demos and hands-on labs, plus exhibitions from more than 400 partners and customers from around the world, Oracle OpenWorld has become a showcase for leading cloud technologies, from Cloud Applications to Cloud Platform and Infrastructure. Oracle OpenWorld 2016 is being held September 18–September 22 at Moscone Center in San Francisco. For more information; to register; or to watch Oracle OpenWorld keynotes, sessions, and more, visit www.oracle.com/openworld. Join the Oracle OpenWorld discussion on Twitter.

    About JavaOne

    The JavaOne conference brings together Java experts and enthusiasts for an extraordinary week of learning and networking focused entirely on all things Java. With more than 440 sessions covering topics that span the breadth of the Java universe, keynotes from foremost Java visionaries, tutorials, and expert-led hands-on learning opportunities, JavaOne is the world's most important event for the Java community. Join the JavaOne discussion on Twitter.


    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

    Julie Sugishita

    • 1.650.506.0076

    Stats collection time

    Jonathan Lewis - Fri, 2016-09-16 06:58

    Yesterday I posted a note about querying dba_optstat_operations to get a quick report of how long calls to dbms_stats had been taking but said I had another script that helped to fill some of the gaps it left. One of my readers points out fairly promptely that 12c enhances the feature considerably, with a view dba_optstat_operation_tasks that (for example) lists all the tables processed during a single call to gather_schema_stats.

    Well, I wrote my script years (if not decades) before 12c came out, so I’m going to publish it anyway.

    It definitely falls into the quick and dirty category. It’s not trying to be clever, makes a critical assumption about the recent past, and isn’t a generally useful tool, but I’ve found it a quick way to highlight a possible problem with expensive stats collection routines (especially those written by 3rd parties who have disabled Oracle’s automatic job.)

    The concept is very simple: list all the tables in the order they were last analyzed and assume that for any “obviously continuous” stretch of time the start of stats collections for one table coincided with the end of stats collection for the previous one. A little fiddling with the lag() function then allows you to report the time spent on gathering the stats.

    rem     stats_check.sql
    rem     Quick and dirty to get an idea of
    rem     where the time went on the most
    rem     recent stats collection job.
    rem     Weakness: It assumes that the start
    rem     of stats collection of a table is the
    rem     end of stats collection for the previous
    rem     table.  This may be far from true.
    define m_schema = '&1'
    set linesize 144
    set pagesize 60
    set trimspool on
    column a_date format a20
    spool stats_check
            blocks, num_rows, sample_size,
            round(100 * sample_size/nullif(num_rows,0),2)   sample_pct,
            to_char(last_analyzed,'dd-mon-yyyy hh24:mi:ss') a_date,
                    round(1440 *
                            last_analyzed -
                            lag(last_analyzed, 1) over (order by last_analyzed)
                            ), 2
            )                        a_time
            owner = upper('&m_schema')
    order by
    spool off

    It’s a very simple script – one detail that’s quite useful is the presence of the data about table size and sample size. But that’s really only the starting point. If you’re gathering stats on a table then most of the time might be spent on gathering stats for histograms or indexes, so there’s plenty of scope to add bits to the script to report number of histograms per table, number of indexes per table, and so on. Personally I tend to do “incremental” trouble-shooting and keep my scripts (and their run time) short, so I’d only worry about specific details if I saw a headline figure that seemed worth a little more effort.

    Here’s some (camouflaged) output – which happens to highlight one of the weaknesses of the approach:

    --------------------- --------  --------- ----------- ---------- --------------------  --------
    TABLE_AAA                    0          0           0            14-jul-2016 01:27:55       .00
    TABLE_BBB              6320666  540356865   108071373         20 14-jul-2016 02:30:45     62.83
    TABLE_CCC              9587372  930770535   186154107         20 14-jul-2016 03:36:13     65.47
    TABLE_DDD              2272319  104698080    20939616         20 14-jul-2016 04:19:45     43.53
    TABLE_EEE              4413696  258746720    51749344         20 14-jul-2016 04:49:13     29.47
    TABLE_FFF                    0          0           0            14-jul-2016 05:02:28     13.25
    TABLE_GGG              4377656  258740995    51748199         20 14-jul-2016 05:02:28       .00
    TABLE_HHH                    0          0           0            14-jul-2016 05:02:28       .00
    TABLE_III                   60         52          52        100 14-jul-2016 05:02:29       .02
    TABLE_JJJ                   60        170         170        100 14-jul-2016 05:02:30       .02
    TABLE_KKK                   60        100         100        100 14-jul-2016 05:02:30       .00
    TABLE_LLL                   60       5548        5021       90.5 14-jul-2016 05:02:31       .02
    TABLE_MMM                 4660     686575      137315         20 14-jul-2016 05:02:32       .02
    TABLE_TTT                    0          0           0            14-jul-2016 05:02:38       .00
    TABLE_UUU                   60        659         659        100 14-jul-2016 05:02:38       .00
    TABLE_VVV               325324   18618685     3723737         20 14-jul-2016 05:04:14      1.60
    TABLE_WWW                   60       1106        1106        100 14-jul-2016 05:06:01      1.78
    TABLE_XXX                24001    2534810      506962         20 14-jul-2016 05:06:13       .20
    TABLE_YYY                    0          0           0            14-jul-2016 05:06:20       .12
    TABLE_ZZZ                    0          0           0            14-jul-2016 05:06:20       .00

    You’ll notice that the zero row FFF table is reported to have taken 13.25 minutes for stats gathering, while the 4.3M row GGG table table took no time at all. That’s the problem of sorting by the last_analyzed time when it’s accurate only to the second and some tables take less than a second to gather stats. Clearly these two time estimates are the wrong way round. (WWW and XXX look fairly suspect too, but their timestamps are different so there must be some other explanation – but the time is too small to worry about at the moment.)

    Tables BBB and CCC are also an example of how variable the results are when you compare cost with time. Although the table and sample had twice the rows table CCC has fewer histograms and indexes. The same variation shows up clearly between EEE and GGG (after allowing for the order error), and slightly less immediately between DDD and all the other large tables.

    That brings me to one more threat of mis-interpretation (and a reason why I’m not terribly keen on publishing scripts that I’ve hacked together). If you see Oracle claiming (through this script) that it took no more than a couple of minutes to gather stats on a 5 billion row table it may be true – but maybe that stats collection was only related to gathering stats for a single, fairly new, partition in a partitioned table. There are, no doubt, several boundary cases that a simple script like this will have overlooked – and when I run it I’ll be at a client site with knowledge of the client database and I’ll spot the anomalies and omissions.


    Subscribe to Oracle FAQ aggregator