Oakies Blog Aggregator

Kudos to Pass Summit 2017

This week I had the opportunity to present at Microsoft’s Pass Summit, which was one of two events I had on my list for this year, (the other was Oracle Open World.) Although I’m still learning about all the events on the Microsoft side, unlike Oracle, where there’s one, massive event in San Francisco each fall, Microsoft has split their annual events into three to focus on each audience. In less than two months, there was MS Ignite in Florida, focused on tomorrow’s technology, IT Dev Connections, geared towards development and then this week, Pass Summit, for the Data Platform expert, (DBA and Data Scientist…)  I’m unsure of what events are going to have to leave my list I currently attend to fit these ones in, but somethings going to have to give.

Still a Newbie

I attended my first Pass Summit last year and was incredibly impressed with the quality of content, as it geared towards the topics and focus someone like me would be interested in.

Although there are a number of similarities to any technical event, (and I do present at a number of different events specializing in Oracle, SQL Server, Big Data, DevOps, Testing and Agile) there are some unique characteristics to Summit that I want to recognize.

The content is top-notch and focused on database, development, analytics and professional development. I’ve already discussed some of my favorite sessions from the conference and my main failing was not having the opportunity to attend more sessions. Delphix has been a sponsor of Pass Summit for years and this year was no different. I’m always impressed with the attendee interest and traffic in the exhibitor area for Microsoft events. The attendees are just more engaged than other events and want to understand how products can make their companies more successful with technology.

This event is held each year at the Washington Conference Center, which is easy to access, has ton of hotels nearby and makes it easy to make the most of the event.

Diversity and Inclusion

Upon receiving your registration badge, one of the first things that stand out from other events is the Anti-harassment 800 number and policy notification on the back in red. Some of the attendees joke about the need of this notification, but for a woman in technology from other communities, I was struck by the overall behavior differences at Microsoft events and I have to give some of the credit to the importance Microsoft and the Pass community gives to diversity and inclusion.

This acknowledgement is to notify attendees and exhibitors that this event has a no-tolerance policy towards harassment and that all attendees deserve the best event experience. Where a woman in attendance at most tech events often expects one or two awkward incidents to occur, the reminder displayed on the badge decreases the likelihood. It is there for everyone who attends the events and sets expectations. All interactions, no matter the participants, are less apprehensive and I found there were more networking, communication and all attendees’ benefit.

On the topic of diversity and inclusion, there are multiple women in technology happy hour tables, a luncheon and sessions. Men wear kilts in support of the women at least one day of the conference and there is an openness in discussing even difficult topics with a goal if more diversity involvement in technology and in the community. Social media has it’s own handle that highlights the women’s contributions throughout the week and many regional groups have a goal of 50% women speakers for their events. There are still the same challenges when it comes to having more people of color in the technical space, but that just means there are more opportunities for this initiative to grow.

SQL Family

As with most conferences, there are a number of post-day events, but what’s more popular are the niche events that have grown in popularity over the years, like karaoke, (yes, I said karaoke.) I still didn’t get up the nerve or have the time to attend, (I’m pacing and bracing myself for the upcoming month of travel) but I’m told this and post conference dinners are the thing for networking with others.

Gaining intimate knowledge of the MSSQL community or SQLFamily, (you can follow the #SQLFamily hashtag for more) is important as you speak at these events. Quite a few of the events require an invitation, either in the form of tickets or armbands to gain access if necessity demands it. Some of these events are incredibly invaluable to me and my goal is always events that I can communicate with those that are hosting and attending, so if they’re noisy to the point you’re unable to, you’re probably going to lose me quickly.

So what did I feel were opportunities for improvement this year? Often there were multiple invites I wasn’t able to attend. As I stated, with some of the events are just for attendees to unwind and let loose. These aren’t the events for me, where I’m looking to connect and network, but everyone deserves to get their geek on, no matter what form that takes in and Summit ensures that everyone has the chance.

I have an additional, professional goal- I need to spend some serious education time enhancing my Microsoft Data Platform skills. I find that I’m still pulling from my previous life as a DBA and as most people know, I believe in walking the walk, not just talking the talk. The demands of my day job, pull from different technology and platforms have kept me from investing the time I need to in Microsoft post SQL Server 2012 and that has got to change.  If you have any recommendations, please feel free to email me at my dbakevlar address at gmail.  I’d appreciate the feedback and advice.

Until the next SQL Saturday, I’m focused on Oracle for a couple weeks, first at East Coast Oracle Conference, (ECO) this week, next week at Devoxx Morocco, followed by DOAG, (Germany) and UKOUG, (The UK) to finish up the year.  Boy, am I going to be tired this Christmas!

 

 

 

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Kudos to Pass Summit 2017], All Right Reserved. 2017.

The post Kudos to Pass Summit 2017 appeared first on DBA Kevlar.

Buffer cache hit ratio–blast from the past

I was perusing some old content during a hard drive “spring clean” the other day, and I found an old gem from way back in 2001.  A time when the database community were trying to dispel the myth that all database performance issues could be tracked back to,  and solved via, the database buffer cache hit ratio.  Thankfully, much of that folklore has now passed into the realm of fiction, but I remember at the time, as a means of showing how silly some of the claims were, I published a routine that would generate any buffer cache hit ratio you desired.  It just simply ran a query to burn through logical I/O’s (and burn a whole in your CPU!) until the required number of operations bumped up the buffer cache hit ratio to whatever number you liked Smile 

Less performance, more work done…. all to get a nice summary number.

The kinds of statistics that the database collects, and what each one represents has changed over the years and versions of Oracle, but I figured I’d present the routine in original form as a nostalgic reminder that statistics without an understanding behind them are as good as no statistics at all.

Enjoy !


create or replace
procedure choose_a_hit_ratio(p_ratio number default 99,p_show_only boolean default false) is
  v_phy                number;
  v_db                 number;
  v_con                number;
  v_count              number;
  v_additional_congets number;
  v_hit number;
  
  procedure show_hit is
  begin
    select p.value, d.value, c.value
    into v_phy, v_db, v_con
    from 
      ( select value from v$sysstat where name = 'physical reads' ) p,
      ( select value from v$sysstat where name = 'db block gets' ) d,
      ( select value from v$sysstat where name = 'consistent gets' ) c;
    v_hit := 1-(v_phy/(v_db+v_con));
    dbms_output.put_line('Current ratio is: '||round(v_hit*100,5));
  end;
begin
--
-- First we work out the ratio in the normal fashion
--
  show_hit;

  if p_ratio/100 < v_hit or p_ratio > 99.9999999 then
    dbms_output.put_line('Sorry - I cannot help you');
    return;
  end if;
--
-- Flipping the formula we can work out how many more consistent gets
-- we need to increase the hit ratio
--
  v_additional_congets := trunc(v_phy/(1-p_ratio/100)-v_db - v_con);

  dbms_output.put_line('Another '||v_additional_congets||' consistent gets needed...');

  if p_show_only then return; end if;
--
-- Create a simple table to hold 200 rows in a single block
--
  begin
    execute immediate 'drop table dummy';
  exception 
    when others then null;
  end;

  execute immediate 'create table dummy (n primary key) organization index as '||
                    'select rownum n from all_objects where rownum <= 200';
--
-- Turn off any new 9i connect-by features to ensure we still do lots of 
-- logical IO
--
  begin
    execute immediate 'alter session set "_old_connect_by_enabled" = true';
  exception 
    when others then null;
  end;
--
-- Grind away until we do all those additional gets
--
  execute immediate '
    select count(*) 
    from (
      select n
      from dummy
      connect by n > prior n
      start with n = 1 )
    where rownum < :v_additional_congets' into v_count using v_additional_congets;

  show_hit;
end;
/

And some output to keep the hit ratio fanatics happy!


SQL> exec choose_a_hit_ratio(85,true);
Current ratio is: 82.30833
Another 29385 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(85);
Current ratio is: 82.30833
Another 29385 consistent gets needed...
Current ratio is: 86.24548

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90,true);
Current ratio is: 86.24731
Another 79053 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90);
Current ratio is: 86.24731
Another 79053 consistent gets needed...
Current ratio is: 90.5702

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98,true);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...
Current ratio is: 98.02386

PL/SQL procedure successfully completed.

nVision Performance Tuning: 6. Logging Selector Usage

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Static selectors are tracked by entries in the PSTREESELCTL table.  It is maintained after the tree is extracted to the selector table.  The version number on PSTREESELCTL is compared with the corresponding version number on the PSTREEDEFN to determine whether the extract of the tree to the selector table is still valid, or whether it needs to be reextracted because the tree has been updated.  Selectors that do not have an entry in PSTREESELCTL are therefore dynamic.
Static selectors are left in the PSTREESELECTnn table after the report completes so that they can be reused.  However, many customers periodically create new effective dated versions of many trees, and so static selectors on old effective dated versions of the tree will accumulate as there is nothing to purge them.
Dynamic selectors are extracted every time the report runs.  They should normally be deleted by nVision before the report finishes.  However, if a report crashes dynamic selectors can be left behind.  That creates a number of problems

  • The size of the selector tables will tend to increase over time as old static and dynamic selectors are left behind.
  • That in turn affects the statistics on these tables.  The range of values for SELECTOR_NUM will become wider faster than the number of rows in the table grows.  The minimum value will either be the oldest static selector number, or the oldest dynamic selector left after a crash.  The maximum value will be the last selector number inserted when statistics were collected.

Therefore, it is useful to be able to track creation and deletion of dynamic selectors by the various nVision reports and queries.  I have therefore created a logging table PS_NVS_TREESLCTLOG (see nvision_dynamic_selectors.sql), a PL/SQL package XX_NVISION_SELECTORS and compound DML triggers on every tree selector table.
The column names in the log table have been chosen for compatibility with the PeopleSoft data dictionary, so that a record can be defined in Application Designer.

#666666 .5pt; mso-border-themecolor: text1; mso-border-themetint: 153; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
Column Name
Data Type
Description
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
SELECTOR_NUM
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUMBER
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Unique identifier for tree selector records.
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
PROCESS_INSTANCE
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUMBER
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
PeopleSoft process instance number for nVision/Query
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
LENGTH
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUMBER
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Length of tree selector
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUM_ROWS
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUMBER
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Number of rows inserted into tree selector. 
Counted by the AFTER ROW part of the triggers.
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TIMESTAMP
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TIMESTAMP
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Time when rows inserted
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
MODULE
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(64)
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Module attribute of session inserting selector rows. 
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
APPINFO_ACTION
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(64)
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Action attribute of session inserting selector rows
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
CLIENTINFO
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(64)
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
CLIENT_INFO attribute of session inserting selector rows.  This will include:
  • PeopleSoft Operator ID.
  • Name of the application server or process scheduler domain.
  • Name of the machine where the client process is executing.
  • Name of the client executable process.
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
STATUS_FLAG
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(1)
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
I=Selectors Inserted
S=Static Selectors Inserted
D=Selectors Deleted
X=Selectors Deleted and Partition Dropped
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TREE_NAME
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(18)
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Name of the tree from which selector extracted.
Obtained by querying statement from V$SQL.
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
OWNER_ID
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(8)
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Schema under which nVision report run
#666666 1.0pt; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
PARTITION_NAME
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
VARCHAR2(128)
#666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Name of partition where selectors stored
#CCCCCC; border-top: none; border: solid #666666 1.0pt; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
JOB_NO
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
NUMBER
#CCCCCC; border-bottom: solid #666666 1.0pt; border-left: none; border-right: solid #666666 1.0pt; border-top: none; mso-background-themecolor: text1; mso-background-themetint: 51; mso-border-alt: solid #666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Database Job number to collect statistics on the partition.

All of the logic is kept in the PL/SQL package because it is common to the triggers on all the tree selector tables.  Insert triggers track population of selectors and delete triggers track the successful removal of dynamic selectors.  After row triggers track the selector number and count the number of rows inserted.  After statement triggers call the logging procedures.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE OR REPLACE TRIGGER sysadm.pstreeselect10_insert
FOR INSERT ON sysadm.PSTREESELECT10 compound trigger
l_err_msg VARCHAR2(100 CHAR);
AFTER EACH ROW IS
BEGIN
sysadm.xx_nvision_selectors.rowins(:new.selector_num,:new.range_from_10,:new.range_to_10);
EXCEPTION WHEN OTHERS THEN NULL;
END after each row;

AFTER STATEMENT IS
BEGIN
sysadm.xx_nvision_selectors.logins(10,'SYSADM');
EXCEPTION WHEN OTHERS THEN
l_err_msg := SUBSTR(SQLERRM,1,100);
dbms_output.put_line('Error:'||l_err_msg);
END after statement;
END;
/

CREATE OR REPLACE TRIGGER sysadm.pstreeselect10_delete
FOR DELETE ON sysadm.PSTREESELECT10 compound trigger
l_err_msg VARCHAR2(100 CHAR);
AFTER EACH ROW IS
BEGIN
sysadm.xx_nvision_selectors.rowdel(:old.selector_num);
EXCEPTION WHEN OTHERS THEN NULL;
END after each row;

AFTER STATEMENT IS
BEGIN
sysadm.xx_nvision_selectors.logdel(10);
EXCEPTION WHEN OTHERS
THEN
l_err_msg := SUBSTR(SQLERRM,1,100);
dbms_output.put_line('Error:'||l_err_msg);
END after statement;
END;
/

Once the decision to bear the overhead of triggers on the selector tables is made, there are then various pieces of additional information that can be captured and stored in the logging table for very little additional overhead.  It is easy to record the current session attributes such as module, action, and client_info.  The process instance number is captured on startup and can then be read by the psftapi package.  The tree is identified by scanning the V$SQL for the SQL that fired the triggers.
It is also possible to maintain statistics on the selector tables.

Purging Selectors

The selector log can be used to drive purging of selectors not cleared by nVisions that failed to complete.  The package includes a purge procedure to clear selectors that are not already marked as having been deleted either when the logged process instance is no longer running, or if there is no known process instance then if it is more than 2 days since the selector was inserted.  If the selector table is also interval partitioned, then the partition will be dropped.
A trigger on PSTREESELCTL (see pstreeselctl.sql) tracks the logs static selector maintenance.
The purge process is invoked by a trigger on PSPRCSRQST that fires when nVision process status is changed away from processing.  The selector purge process should also be scheduled to run daily.
The selector log itself is not purged as it contains useful information about tree usage.

Recommendations

  • If the tree selector tables are not partitioned create a histogram on SELECTOR_NUM.  However, up to Oracle 11g, this will not be effective as the number of distinct values reaches the maximum number of buckets, 254.  Dynamic selectors should be purged before this happens.  From Oracle 12c the maximum number of buckets is 2048, and hybrid histograms can be used.

UKOUG is coming

Yes it is just a few more weeks until the UKOUG conference swings by.

This has been one of my favourite conferences for years – dating back to my first one in 2002 !!.  You can see from the picture at the tail of this post – whilst times have changed in those 15 years, the basic tenets of community, networking and technical content remain unchanged to this day.

The AskTOM team will all be there and there’s a fantastic agenda spread over 4 days.  This user group really knows how to “bake” a good conference.  Maybe they followed my recipe. Smile

 

 

OakTable area, UKOUG 2002

image

Multitenant internals: INT$ and INT$INT$ views

This month, I’ll talk – with lot of demos – about multitenant internals at DOAG conference. CaptureMultitenantInternals
The multitenant dictionary architecture starts with a simple idea: system metadata and data are in CDB$ROOT and user metadata and data are in PDB. And it could have been that simple. When a session connected to a PDB needs to read some system information, the session context is switched to the CDB$ROOT container and reads from CDB$ROOT SYS tablespace and objects, and then switches back to the PDB container. This is implemented by metadata and data links: the PDB lists the objects for which the session has to switch to CDB$ROOT to get metadata or data.

CaptureMultitenantInternals1But, for compatibility reason, and ease of administration, the dictionary views must display information from both containers, transparently, and then things become a little more complex with common views and extended data views.

At Oracle Open World, the multitenant architects, in the #PDBExpert session, answered questions about the multitenant architecture posted on Twitter. My first question (because I was investigating a bug at that time) was about some views, such as INT$INT$DBA_CONSTRAINTS, introduced to implement the complexity of showing the same information in dictionary views as the ones we had on non-CDB. Of course, the architects didn’t want to go too far on this and had a very accurate answer: INT$ is for internal, and here you have two ‘INT$’ so you shouldn’t look at that.

But I like to understand how things work and here is the explanation of these INT$INT$ views. And I’m not even sure that INT is for ‘internal’ but maybe ‘intermediate’. But for sure, the $ at the end is used by Oracle internal dictionary objects.

INT$ Extended Data views

We are used to seeing all objects, system ones and user ones, listed by the dictionary views. For example, DBA_PROCEDURES shows all procedures, system and user ones, and then have to read from both containers (current PDB and CDB$ROOT) through extended data links. ALL_PROCEDURES shows all procedures accessible by the user, and they also have to switch to CDB$ROOT if the user has been granted to read system objects. USER_PROCEDURES shows only the objects owned by the current user, and then can read from the current container only.

For the ease of the definition, in 12c all the joins on the underlying tables(such as procedureinfo$, user$, obj$) is done by an intermediate view such as INT$DBA_PROCEDURES which is defined as EXTENDED DATA link to read from CDB$ROOT in addition to the local table. Then DBA_PROCEDURES, ALL_PROCEDURES and USER_PROCEDURES are defined on top of it with the required where clause to filter out owner and privilege accessibility.

INT$INT$ Extended Data views

In this post, I’ll detail the special case of DBA_CONSTRAINTS because things are more complex to get the multitenant architecture behaving the same as the non-CDB.

There are several types of constraints which are identified with the CONSTRAINT_TYPE column of DBA_CONSTRAINTS, or the TYPE# of the underlying table CDEF#

Here, I query the underlying table with the CONTAINER() function to see what is stored in each container:

SQL> select decode(type#,1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C',8,'H',9,'F',10,'F',11,'F',13,'F','?') constraint_type,
2 type#,con_id,count(*) from containers(cdef$)
3 group by type#,con_id order by type#,con_id;
 
CONSTRAINT_TYPE TYPE# CON_ID COUNT(*)
--------------- ----- ------ --------
C 1 1 74
C 1 3 74
P 2 1 843
P 2 3 844
U 3 1 238
U 3 3 238
R 4 1 324
R 4 3 324
V 5 1 11
O 6 1 172
O 6 3 26
C 7 1 5337
C 7 3 5337
F 11 1 11
F 11 3 11
? 12 1 3
? 12 3 3

I have very few user objects in this database. CON_ID=1 is CDB$ROOT and CON_ID=3 is my PDB. What we can see here is that we have nearly the same number of rows in both containers for the following constraint types: C (check constraint on a table), P (primary key), U (unique key), R (referential integrity), and other types related to tables. And some types have most of their rows in CDB$ROOT only: V (check option on views), R (read only on views)

That’s an implementation specificity of the multitenant architecture which makes things more complex for the dictionary views. For some objects (such as procedures and views) the metadata is stored in only one container: system objects have all their information in CDB$ROOT and the PDB has only a link which is a dummy row in OBJ$ which mentions the sharing (such as metadata link), owner and name (to match to the object in CDB$ROOT), and a signature (to verify that the DDL creating the object is the same). But other objects (such as tables) have their information duplicated in all containers for system objects (CDB$ROOT, PDB$SEED and all user PDBs). This is the reason why we see rows in both containers for constraint definition when they are related to a table.

Example on view constraint

I’ll take a constraint on system view as an example: constraint SYS_C003357 on table SYS.DBA_XS_SESSIONS


SQL> select owner,object_name,object_type,sharing from dba_objects where owner='SYS' and object_name='DBA_XS_SESSIONS';
 
OWNER OBJECT_NAME OBJECT_TYPE SHARING
----- ----------- ----------- -------
SYS DBA_XS_SESSIONS VIEW METADATA LINK
 
SQL> select owner,table_name,constraint_type,constraint_name from containers(dba_constraints) where owner='SYS' and table_name='DBA_XS_SESSIONS' and rownum=1;
 
OWNER TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME
----- ---------- --------------- ---------------
SYS DBA_XS_SESSIONS O SYS_C003357

I’m looking at the dependencies for the DBA_CONSTRAINTS view:

SQL> select owner,name,referenced_owner,referenced_name from dba_dependencies where owner='SYS' and name='DBA_CONSTRAINTS' and type='VIEW';
 
OWNER NAME REFERENCED_OWNER REFERENCED_NAME
----- ---- ---------------- ---------------
SYS DBA_CONSTRAINTS SYS GETLONG
SYS DBA_CONSTRAINTS SYS INT$DBA_CONSTRAINTS

So the DBA_CONSTRAINT is a view on INT$DBA_CONSTRAINTS as we have seen above. However, this view is not directly reading the tables but another view:

SQL> select owner,name,referenced_owner,referenced_name from dba_dependencies where owner='SYS' and name='INT$DBA_CONSTRAINTS' and type='VIEW';
 
OWNER NAME REFERENCED_OWNER REFERENCED_NAME
----- ---- ---------------- ---------------
SYS INT$DBA_CONSTRAINTS SYS GETLONG
SYS INT$DBA_CONSTRAINTS SYS INT$INT$DBA_CONSTRAINTS

Here is our additional INT$INT$ view which is reading the tables:

SQL> select owner,name,referenced_owner,referenced_name from dba_dependencies where owner='SYS' and name='INT$INT$DBA_CONSTRAINTS' and type='VIEW';
 
OWNER NAME REFERENCED_OWNER REFERENCED_NAME
----- ---- ---------------- ---------------
SYS INT$INT$DBA_CONSTRAINTS SYS USER$
SYS INT$INT$DBA_CONSTRAINTS SYS CDEF$
SYS INT$INT$DBA_CONSTRAINTS SYS OBJ$
SYS INT$INT$DBA_CONSTRAINTS SYS CON$
SYS INT$INT$DBA_CONSTRAINTS SYS _CURRENT_EDITION_OBJ
SYS INT$INT$DBA_CONSTRAINTS SYS _BASE_USER
SYS INT$INT$DBA_CONSTRAINTS SYS GETLONG

In summary, the EXTENDED DATA view which reads the tables on each container (CDB$ROOT and PDB) is here the INT$INT$DBA_CONSTRAINTS and the INT$DBA_CONSTRAINTS is another intermediate one before the DBA_CONSTRAINTS view.


SQL> select owner,object_name,object_type,sharing from dba_objects where object_name in ('DBA_CONSTRAINTS','INT$DBA_CONSTRAINTS','INT$INT$DBA_CONSTRAINTS') order by object_id desc;
 
OWNER OBJECT_NAME OBJECT_TYPE SHARING
----- ----------- ----------- -------
PUBLIC DBA_CONSTRAINTS SYNONYM METADATA LINK
SYS DBA_CONSTRAINTS VIEW METADATA LINK
SYS INT$DBA_CONSTRAINTS VIEW METADATA LINK
SYS INT$INT$DBA_CONSTRAINTS VIEW EXTENDED DATA LINK

In this example, we don’t understand the reason for the additional intermediate view because the return all the same number of rows in each container:


SQL> select con_id,constraint_type,constraint_name from containers(INT$INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
CON_ID CONSTRAINT_TYPE CONSTRAINT_NAME
------ --------------- ---------------
1 O SYS_C003357
3 O SYS_C003357
 
SQL> select con_id,constraint_type,constraint_name from containers(INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
CON_ID CONSTRAINT_TYPE CONSTRAINT_NAME
------ --------------- ---------------
1 O SYS_C003357
3 O SYS_C003357
 
SQL> select con_id,constraint_type,constraint_name from containers(DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
CON_ID CONSTRAINT_TYPE CONSTRAINT_NAME
------ --------------- ---------------
1 O SYS_C003357
3 O SYS_C003357

The difference is only a few additional columns from the object definition (OWNERID,OBJECT_ID,OBJECT_TYPE#,SHARING) in the INT$ and INT$INT$ which are not selected in the final view:

SQL> select * from containers(INT$INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
OWNER OWNERID CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME OBJECT_ID OBJECT_TYPE# SEARCH_CONDITION_VC R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED SHARING ORIGIN_CON_ID CON_ID
----- ------- --------------- --------------- ---------- --------- ------------ ------------------- ------- ----------------- ----------- ------ ---------- -------- --------- --------- --- ---- ----------- ----------- ---------- ------- ------------ ------- ------------- ------
SYS 0 SYS_C003357 O DBA_XS_SESSIONS 10316 4 ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17 1 1 1
SYS 0 SYS_C003357 O DBA_XS_SESSIONS 10316 4 ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17 1 1 3
 
SQL> select * from containers(INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
OWNER OWNERID CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME OBJECT_ID OBJECT_TYPE# SEARCH_CONDITION_VC R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED SHARING ORIGIN_CON_ID CON_ID
----- ------- --------------- --------------- ---------- --------- ------------ ------------------- ------- ----------------- ----------- ------ ---------- -------- --------- --------- --- ---- ----------- ----------- ---------- ------- ------------ ------- ------------- ------
SYS 0 SYS_C003357 O DBA_XS_SESSIONS 10316 4 ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17 1 1 1
SYS 0 SYS_C003357 O DBA_XS_SESSIONS 10316 4 ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17 1 1 3
 
SQL> select * from containers(DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003357'
 
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION_VC R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED ORIGIN_CON_ID CON_ID
----- --------------- --------------- ---------- ------------------- ------- ----------------- ----------- ------ ---------- -------- --------- --------- --- ---- ----------- ----------- ---------- ------- ------------ ------------- ------
SYS SYS_C003357 O DBA_XS_SESSIONS ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17 1 1
SYS SYS_C003357 O DBA_XS_SESSIONS ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 26-JAN-17

If we look at the INT$DBA_CONSTRAINTS definition we see some filters on those object definition:

SQL> ddl INT$DBA_CONSTRAINTS
 
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."INT$DBA_CONSTRAINTS" ("OWNER", "OWNERID", "CONSTRAINT_NAME", "CONSTRAINT_TYPE", "TABLE_NAME", "OBJECT_ID", "OBJECT_TYPE#", "SEARCH_CONDITION", "SEARCH_CONDITION_VC", "R_OWNER", "R_CONSTRAINT_NAME", "DELETE_RULE", "STATUS", "DEFERRABLE", "DEFERRED", "VALIDATED", "GENERATED", "BAD", "RELY", "LAST_CHANGE", "INDEX_OWNER", "INDEX_NAME", "INVALID", "VIEW_RELATED", "SHARING", "ORIGIN_CON_ID") AS
select OWNER, OWNERID, CONSTRAINT_NAME, CONSTRAINT_TYPE,
TABLE_NAME, OBJECT_ID, OBJECT_TYPE#, SEARCH_CONDITION,
SEARCH_CONDITION_VC, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS,
DEFERRABLE, DEFERRED, VALIDATED, GENERATED,
BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME,
INVALID, VIEW_RELATED, SHARING, ORIGIN_CON_ID
from INT$INT$DBA_CONSTRAINTS INT$INT$DBA_CONSTRAINTS
where INT$INT$DBA_CONSTRAINTS.OBJECT_TYPE# = 4 /* views */
OR (INT$INT$DBA_CONSTRAINTS.OBJECT_TYPE# = 2 /* tables */
AND (INT$INT$DBA_CONSTRAINTS.ORIGIN_CON_ID
= TO_NUMBER(SYS_CONTEXT('USERENV', 'CON_ID'))));

For views (OBJECT_TYPE#=4) there is no filter, which explains why we see the same number of rows in the previous example. But for tables (OBJECT_TYPE#=2) there’s an additional filter to keep the row from the current container only.

Example on table constraint

Then, I’ll take another example with a constraint definition for a table:

SQL> select owner,object_name,object_type,sharing from dba_objects where owner='SYS' and object_name='RXS$SESSIONS';
 
OWNER OBJECT_NAME OBJECT_TYPE SHARING
----- ----------- ----------- -------
SYS RXS$SESSIONS TABLE METADATA LINK
 
SQL> select owner,table_name,constraint_type,constraint_name from dba_constraints where owner='SYS' and table_name='RXS$SESSIONS' and rownum=1;
 
OWNER TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME
----- ---------- --------------- ---------------
SYS RXS$SESSIONS C SYS_C003339

From the INT$INT$ view, we have a duplicate when we query on a PDB because for tables the PDB not only holds a dummy row in OBJ$ but full information about the table is duplicated in other tables such as TAB$ and CDEF$:

SQL> select con_id,constraint_type,constraint_name from containers(INT$INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003339'
 
CON_ID CONSTRAINT_TYPE CONSTRAINT_NAME
------ --------------- ---------------
1 C SYS_C003339
3 C SYS_C003339
3 C SYS_C003339

This is the reason for the additional intermediate view: filtering out those duplicate by removing the rows from CDB$ROOT when queried from a PDB.

SQL> select con_id,constraint_type,constraint_name from containers(INT$DBA_CONSTRAINTS) where OWNER='SYS' and constraint_name='SYS_C003339'
 
CON_ID CONSTRAINT_TYPE CONSTRAINT_NAME
------ --------------- ---------------
1 C SYS_C003339
3 C SYS_C003339

Thanks to that, the duplicates are not visible to the end-user views DBA_CONSTRAINTS and PDB_CONSTRAINTS.

You may wonder why only DBA_CONSTRAINTS needs this views and not DBA_TABLES, DBA_INDEXES or DBA_TAB_COLUMNS? That’s because all information about system tables and indexes are replicated in all PDBs and then there is no need for EXTENDED DATA and context switches. DBA_CONSTRAINT has the particularity of showing information about tables and views, which implement the metadata links in a different way.

 

Cet article Multitenant internals: INT$ and INT$INT$ views est apparu en premier sur Blog dbi services.

Multitenant dictionary: what is consolidated and what is not

The documentation says that for Reduction of duplication and Ease of database upgrade the Oracle-supplied objects such as data dictionary table definitions and PL/SQL packages are represented only in the root.

Unfortunately, this is only partly true. System PL/SQL packages are only in root but system table definition are replicated into all PDBs.

This post is an extension of a previous blog post which was on 12cR1. This one is on 12cR2.

As I did at Open World and will do at DOAG, I show multitenant internals by creating a metadata link procedure. When I do a simple ‘describe’ when connected to a PDB, the sql_trace shows that the session switches to the CDB$ROOT to get the procedure information:

*** 2017-11-05T16:17:36.339126+01:00 (CDB$ROOT(1))
=====================
PARSING IN CURSOR #140420856738440 len=143 dep=1 uid=0 oct=3 lid=0 tim=101728244788 hv=2206365737 ad='7f60a7f0' sqlid='9fjf75a1s4y19'
select procedure#,procedurename,properties,itypeobj#, properties2 from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc
END OF STMT

All information about the system PL/SQL procedures is stored in the root only. The PDB has only a dummy row in OBJ$ to mention that it is a metadata link. And this is why you pay for the multitenant option: consolidation of all system dictionary objects into the root only. You save space (on disk and related memory) and you have only one place to upgrade.

But this is implemented only for some objects, like PL/SQL procedures, but not for others like table and indexes. If you ‘describe’ a metadata link table when connected to a PDB you will not see any switch to CDB$ROOT in the sql_trace:

*** 2017-11-05T13:01:53.541231+01:00 (PDB1(3))
PARSING IN CURSOR #139835947128936 len=86 dep=1 uid=0 oct=3 lid=0 tim=98244321664 hv=2195287067 ad='75f823b8' sqlid='32bhha21dkv0v'
select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc
END OF STMT
PARSE #139835947128936:c=0,e=158,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3765558045,tim=98244321664
BINDS #139835947128936:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f2e124fef10 bln=22 avl=03 flg=05
value=747
EXEC #139835947128936:c=1000,e=603,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3765558045,tim=98244322311
FETCH #139835947128936:c=0,e=15,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=3765558045,tim=98244322342
FETCH #139835947128936:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3765558045,tim=98244322356
FETCH #139835947128936:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3765558045,tim=98244322369
STAT #139835947128936 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=3 pr=0 pw=0 str=1 time=16 us cost=3 size=234 card=13)'
STAT #139835947128936 id=2 cnt=2 pid=1 pos=1 obj=21 op='TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 str=1 time=11 us cost=2 size=234 card=13)'
STAT #139835947128936 id=3 cnt=1 pid=2 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 str=1 time=6 us cost=1 size=0 card=1)'
CLOSE #139835947128936:c=0,e=1,dep=1,type=3,tim=98244322439

Here all information about the columns is read from COL$ in the PDB. And if you look at TAB$ (tables), COL$ (table columns), IND$ (indexes), CONS$ and CDEF$ (constraints), you will see that they contain rows in a PDB where no user objects have been created. This is the case for all information related to tables: they are stored in CDB$ROOT and replicated into all other containers: PDB$SEED and all user created PDB. Only the information related to non-data objects, are stored only in one container.

I’ve run a query to count the rows in CDB$ROOT and PDB$SEED and here is the result:
CaptureMultitenantNumRows

All rows in OBJ$ are replicated, which is expected because this is where the metadata link information is stored. But you see also all information related to tables that are also replicated, such as the 100000+ columns in COL$. And this is the reason why you do not see a big consolidation benefit when you look at the size of the SYSTEM tablespace in pluggable databases which do no contain any user data:

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 820 SYSTEM YES /u01/oradata/CDB1A/system01.dbf
3 630 SYSAUX NO /u01/oradata/CDB1A/sysaux01.dbf
4 80 UNDOTBS1 YES /u01/oradata/CDB1A/undotbs01.dbf
5 250 PDB$SEED:SYSTEM NO /u01/oradata/CDB1A/pdbseed/system01.dbf
6 390 PDB$SEED:SYSAUX NO /u01/oradata/CDB1A/pdbseed/sysaux01.dbf
7 5 USERS NO /u01/oradata/CDB1A/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO /u01/oradata/CDB1A/pdbseed/undotbs01.dbf
9 270 PDB1:SYSTEM YES /u01/oradata/CDB1A/PDB1/system01.dbf
10 440 PDB1:SYSAUX NO /u01/oradata/CDB1A/PDB1/sysaux01.dbf
11 100 PDB1:UNDOTBS1 YES /u01/oradata/CDB1A/PDB1/undotbs01.dbf
12 5 PDB1:USERS NO /u01/oradata/CDB1A/PDB1/users01.dbf

Here I have 250MB in PDB$SEED which is supposed to contain only links to the 820GB SYSTEM tablespace, but there is a lot more than that.

So, basically, not all the dictionary is consolidated in multitenant but only the non-data part such as those PL/SQL packages and the dictionary views definition. You can think about the multitenant option consolidation as an extension to sharing the Oracle Home among several databases. It concerns the software part only. But the part of the dictionary which contains data about system objects is replicated into all containers, and is read locally without a context switch. This also means that a patch or upgrade on them has to be run in all containers.

With the fact that some information is replicated and some are not, comes the complexity to manage that in the dictionary views, and this will be the subject of the next blog post about INT$INT$ views.

 

Cet article Multitenant dictionary: what is consolidated and what is not est apparu en premier sur Blog dbi services.

IP CIDR rules and address ranges

I always forget IP address range coverage rules and forget where to look.

It’s the wiki!

https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing

and for good reference here is the table:

/32 is for a single address

/24 is for a range in the last place x.x.x.0

/16 is for a range in the last 2 places x.x.0.0

Screen Shot 2017-11-03 at 11.28.31 AM

Pass Summit 2017, 2nd Day, Thursday

Thursday started out calm, self-assured that I knew where I needed to be all day and was prepared.  I tweeted happily that I had found a lovely spot to work during the keynote, (day job!)

Yeah, this:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/Screen-Shot-2... 251w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/Screen-Shot-2... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/Screen-Shot-2... 974w" sizes="(max-width: 494px) 100vw, 494px" data-recalc-dims="1" />

At the keynote, there was a table with my name on it where I was supposed to be.  Thank you so much to Brent Ozar for letting me know so I could apologize for the miscommunication with the organizers of the blogger table, (which is not the same thing as the blogger meetup-  oops!!)

The rest of the day was crazy busy, but incredibly enjoyable and educational.  I attended Andy Yun’s session on UDFs and Nested Views impact on Optimizer choices to confirm they suffer very similar challenges as Oracle.  I’d always enforced the rule of avoidance from my experience with E-business Suite in Oracle when working with SQL Server just to be safe and his presentation did a great job of using a story and code to explain, although the number of permutations is decided by time vs. quantity like Oracle’s CBO, it does happen and the complexity of next views can impact the ability to choose an acceptable plan.

Too Many Lunches

Next, I played Hobbit and went for first, second and third lunch, (not really, but it was fun to say…)  I stopped into the WIT Birds of a Feather table and hosted a number of fantastic conversations with the table.  I was thrilled to have both men and women at the table and it confirmed that we’re all part of the solution.

I missed the WIT luncheon keynote, as I had to rush over for lunch with my Seattle based fellow Oakies.  Thank you to Kyle Hailey, Jeremiah Wilton and Jeremy Schneider for the wonderful lunch and catching up.  It was great to see you while I was here in the city!

Post lunch, I had an interview with PassTV with SentryOne.  Instead of being in the stream, they made the decision to add my interview into the final broadcast, so I’m looking forward to seeing the final product when completed.

I did get to spend some time in the booth yesterday, but it was only long enough to be told I missed a ton of people who were looking for me, so apologies to all I missed!  Once the booth closed, I headed over to Pass Speaker Idol, which I LOVED!!  It’s an awesome concept-  have folks that have never spoke at Summit, try their hand at speaking in a five minute presentation and judged by five senior speakers from the event.  The winner of each of the rounds goes onto the finals and the winner gets a guaranteed speaking spot at Summit 2018.  This is an incredible way to bring in new speakers and I applaud Pass’ dedication to that goal.

Afterwards, I attended Glenn Berry‘s “Improving SQL Server Performance” session.  Glenn is awesome, (DBA Crush) and he covered many tricks I’d learned over the years to design a Windows server around optimal performance for a SQL Server database, but showed us some great new options with newer servers I was unaware of.  Glenn considers himself more of a hardware guy, (over his SQL Skills peers) but his performance queries have been a standard for me since the beginning of my investment in SQL Server.

To Many Events

The evening was upon me and I bid farewell to my coworkers, who I watched enviously depart for a great company dinner and I went to network, (i.e. do my job…. :))  I attended the Idera party, (everyone was really getting into the band, (I’m not much of a concert goer, so it took me a minute to realize everyone had picked up earplugs as they came in) The band was rocking out and took the term “hair band” to new levels!  I departed after making the rounds and ran into Steve Jones and chatted for a few minutes as I returned to the event center and discussed the Redgate party, which he confirmed would be a smash, but at that point, decided to balance my evening out by proceeding over to game night.  Catherine was nice enough to escort me into the event and I played a lovely game of Scrabble and chatted with Karen Lopez before turning in for the night.

The Friday Don’t Miss

Friday at 10:30am, I have Koffee with Kellyn in the Delphix booth and then I’ll be presenting at the LAST session of the day, (3:15 in room 615), doing a talk on “War of the Indices, Oracle vs. SQL Server”.  This talk will compare deep research done on indexing in the two platforms, then how the engine, memory allocation, optimizer and data storage creates benefits and latency in performance.



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Pass Summit 2017, 2nd Day, Thursday], All Right Reserved. 2017.

The post Pass Summit 2017, 2nd Day, Thursday appeared first on DBA Kevlar.

Quick history on database growth

AWR collects segment statistics, and this can be used to quickly understand an abnormal database growth. Here is a script I use to get, from the AWR history, the segments that have grown by more than 1% of the database size, in one hour.

First I must mention that this uses only the part of AWR which is not subject to additional option. This even works in Standard Edition:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE

So here is the query, easy to modify with different threshold:
set echo on pagesize 1000
set sqlformat ansiconsole
select * from (
select
round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024) GBYTE_ALLOCATED
,trunc(max(end_interval_time),'hh24') snap_time
,round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024*24*(cast(max(end_interval_time) as date)-cast(min(begin_interval_time) as date))) "GB/hour"
,owner,object_name,subobject_name,object_type
from DBA_HIST_SEG_STAT join DBA_HIST_SEG_STAT_OBJ using (dbid,ts#,obj#,dataobj#) join dba_hist_snapshot using(dbid,snap_id)
group by trunc(end_interval_time,'hh24'),owner,object_name,subobject_name,object_type
) where "GB/hour" > (select sum(bytes)/1024/1024/1024/1e2 "one percent of database size" from dba_data_files)
order by snap_time
;

and the sample output, showing only the snapshots and segments where more than 1% of the database size has been allocated within one hour:

GBYTE_ALLOCATED SNAP_TIME GB/hour OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
--------------- --------- ------- ----- ----------- -------------- -----------
4 25-OCT-2017 19:00:00 4 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 20:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 21:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
3 25-OCT-2017 22:00:00 3 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 00:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
6 26-OCT-2017 01:00:00 6 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 02:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 03:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 04:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 05:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047719C00008$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047710C00006$$ LOB

With this, it is easier to ask to the application owners if this growth is normal or not.

 

Cet article Quick history on database growth est apparu en premier sur Blog dbi services.

Pass Summit 2017, 1st Day, Also Known as HumpDay

I’ve plenty of rest after arriving in Seattle yesterday to get my registration for Pass Summit, attended the Women in Technology Happy Hour and then received my Idera ACE!

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/IMG_0213-Full... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/IMG_0213-Full... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/IMG_0213-Full... 1680w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/IMG_0213-Full... 2520w" sizes="(max-width: 366px) 100vw, 366px" data-recalc-dims="1" />

Now Wednesday morning, I spent it reworking my slides for the third time and preparing while the main keynote was going on, (my session was right after and was streamed.) After all that work, I then came to realize after adjusting my resolution, we loaded the previous deck and I had to think fast, skipping some older slides during my presentation. Lucky for me, I had an awesome audience and the questions from the attendees consumed the time lost from the missing slides!

Booth Bacon, (We do not say Booth Babe)

Afterwards, I spent most of the day in the Delphix booth, across from the Microsoft monopoly. I work remote, so was happy to spend some time with the folks from our MSSQL teams.   I still had a few conference attendees who were surprised to see a speaker they knew from the Oracle community at a MSSQL event and a few asked to take selfies with me, (very appreciated, thank you!)

Now We Troubleshoot

I did get to go to two sessions later in the day, SQL Server 2017 on Linux from Bob Ward.  Bob verified why I record my demos and just play them back during events, but as Tanel Poder would say when these things happen in the Oracle world, “Now we troubleshoot!”  There was significant troubleshooting going on during Bob’s session due to VM misbehavior, but still picked up some good tips that the MSSQL community will need as they go to the Linux side and I appreciate his humor during tech challenges!  Awesome job, Bob!

The Day’s End

Someone said I’m supposed to do karaoke tonight and this is “the thing” at Summit, but it may take me another year to get up the nerve and to be honest, I ended up going to dinner with some incredibly high-end rum with coworkers instead …baby steps, baby steps… </p />
</p></div></div>

    	  	<div class=