Oakies Blog Aggregator

CBO, FIRST_ROWS and VIEW misestimate

There are several bugs with the optimizer in FIRST_ROWS mode. Here is one I encountered during a 10.2.0.4 to 12.2.0.1 migration when a view had an ‘order by’ in its definition.

Here is the test case that reproduces the problem.

A big table:

SQL> create table DEMO1 (n constraint DEMO1_N primary key,x,y) as select 1/rownum,'x','y' from xmltable('1 to 1000000');
Table DEMO1 created.

with a view on it, and that view has an order by:

SQL> create view DEMOV as select * from DEMO1 order by n desc;
View DEMOV created.

and another table to join to:

SQL> create table DEMO2 (x constraint DEMO2_X primary key) as select dummy from dual;
Table DEMO2 created.

My query reads the view in a subquery, adds a call to a PL/SQL function, and joins the result with the other table:


SQL> explain plan for
select /*+ first_rows(10) */ *
from
( select v.*,dbms_random.value from DEMOV v)
where x in (select x from DEMO2)
order by n desc;
 
Explained.

You can see that I run it with FIRST_ROWS(10) because I actually want to fetch the top-10 rows when ordered by N. As N is a number and I have an index on it and there are no nulls (it is the primary key) I expect to read the first 10 entries from the index, call the function for each of them, then nested loop to the other tables.

In the situation I encountered it, this is what was done in 10g but when migrated to 12c the query was very long because it called the PL/SQL function for million of rows. Here is the plan in my example:


SQL> select * from dbms_xplan.display(format=>'+projection');
 
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2046425878
 
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 1 | 21 | | 7 (0)| 00:00:01 |
| 2 | VIEW | DEMOV | 902 | 17138 | | 7 (0)| 00:00:01 |
| 3 | SORT ORDER BY | | 968K| 17M| 29M| 6863 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEMO1 | 968K| 17M| | 1170 (1)| 00:00:01 |
| 5 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEMO2_X | 1 | 2 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
6 - access("X"="V"."X")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
1 - (#keys=0) "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
2 - "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
3 - (#keys=1) INTERNAL_FUNCTION("N")[22], "X"[CHARACTER,1], "Y"[CHARACTER,1]
4 - "N"[NUMBER,22], "X"[CHARACTER,1], "Y"[CHARACTER,1]

A full table scan of the big table, with a call to the PL/SQL function for each row and the sort operation on all rows. Then the Top-10 rows are filtered and the nested loop operates on that. But you see the problem here. The cost of the ‘full table scan’ and the ‘order by’ has been evaluated correctly, but the cost after the VIEW operation is minimized.

My interpretation (but it is just a quick guess) is that the the rowset is marked as ‘sorted’ and then the optimizer considers that the cost to get first rows is minimal (as if it were coming from an index). However, this just ignores the initial cost of getting this rowset.

I can force with a hint the plan that I want – index full scan to avoid a sort and get the top-10 rows quickly:

SQL> explain plan for
select /*+ first_rows(10) INDEX_DESC(@"SEL$3" "DEMO1"@"SEL$3" ("DEMO1"."N")) */ *
from
( select v.*,dbms_random.value from DEMOV v)
where x in (select x from DEMO2)
order by n desc;
 
Explained.

This plan is estimated with an higher cost than the previous one and this is why it was not chosen:

SQL> select * from dbms_xplan.display(format=>'+projection');
PLAN_TABLE_OUTPUT
Plan hash value: 2921908728
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 1 | 21 | 9 (0)| 00:00:01 |
| 2 | VIEW | DEMOV | 902 | 17138 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEMO1 | 968K| 17M| 8779 (1)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| DEMO1_N | 968K| | 4481 (1)| 00:00:01 |
| 5 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEMO2_X | 1 | 2 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
6 - access("X"="V"."X")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
1 - (#keys=0) "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
2 - "V"."N"[NUMBER,22], "V"."X"[CHARACTER,1], "V"."Y"[CHARACTER,1]
3 - "N"[NUMBER,22], "X"[CHARACTER,1], "Y"[CHARACTER,1]
4 - "DEMO1".ROWID[ROWID,10], "N"[NUMBER,22]

This cost estimation is fine. The cost of getting all rows by index access is higher than with a full table scan, but the optimizer knows that the actual cost is proportional to the number of rows fetched and then it adjusts the cost accordingly. This is fine here because the VIEW has only non-blocking operations. The problem in the first plan without the hint, was because the same arithmetic was done, without realizing that the SORT ORDER BY is a blocking operation and not a permanent sorted structure, and must be completed before being able to return the first row.

In this example, as in the real case I’ve encountered, the difference in cost is very small (7 versus 9 here) which means that the plan can be ok and switch to the bad one (full scan, call function for all rows, sort them) with a small change in statistics. Note that I mentioned that the plan was ok in 10g but that may simply be related to the PGA settings and different estimation for the cost of sorting.

 

Cet article CBO, FIRST_ROWS and VIEW misestimate est apparu en premier sur Blog dbi services.

Considerations When Using SQL Server 2016 Dynamic Data Masking

SQL Server 2016 introduced a new security feature called Dynamic Data Masking.  With the General Data Protection Regulations, (GDPR) breathing heavy down most IT in America’s neck, its good timing.

Data Masking” is a term that has a strict definition, but different results when we actually discuss the technology behind it.  The main goal is to obfuscate critical data in a non-reversible process and I deem it separate from encryption technology. With SQL Server’s Dynamic Data Masking, the original data is still present in the database, with a masking rule applied to hide the data, allowing anyone with the unmask privilege to still view it.

grant unmask to ;

As Delphix has a very robust and non-reversible, masking product, I realize that you get what you paid for.  The real question is the cost of data vulnerability in an age of security breaches, (over 1000 reported so far in 2017) worth the loss of customer loyalty, revenue and possibly the company.

So how does Dynamic Data Masking work?  It’s a simple #0000ff;">alter table command.  If you have a table called Employees that contains their social security numbers and you want to mask them, you can add a masking function to the SSNum column:

alter table store.Employees
alter column [SSNum] add masked with (function = 'partial(0,"XXX-XX-",4)');

select SSNum from store.Employees;

XXXX-XX-5426
XXXX-XX-8954
XXXX-XX-0143
XXXX-XX-5499
...

Anyone with the unmask privilege or DB_OWNER will be able to view the data.  As many development and testing environments grant higher privileges to the users and in SQL Server, it’s not rare for a developer to be the DB_OWNER, (I used to come across this all the time when recoveries were performed by the wrong OS user) this leaves this data still quite vulnerable.  I do like that if you were to take a backup and recover it with masking, the obfuscated data is what is recovered physically.  I’m more concerned about those odd environments where compliance hasn’t been put in place on owners of the database that would still view the originally masked data, but unmasked.

Performance isn’t impacted, (i.e. no referential integrity concerns or execution plans) as the optimizer  performs all steps against the real data, which leads me to wonder what happens with some of the newer monitoring tools that state they can display SQL and bind variable data without accessing the database directly.  Would they “sniff” the masked data or unmasked?  Would it matter who the OS User or roles in the database?

The masking can also be reversed on an object with just a simple command:

alter table store.Employees
alter column [SSNum] drop masked;

This seems too easy to get access to critical data.  Understand that having critical data secured is essential, but before choosing a product, make a list of what could happen, the impact to the organization if the data is breached and the value of having a masking or encryption product that is robust enough to handle your requirements.

I’m heading home from Devoxx Morocco tomorrow and will be heading to DOAG in Nuremberg, Germany the beginning of next week.  It appears that jet lag will be a semi-permanent state for me this month…. </p />
</span></p></div></div>

    	  	<div class=

Dynamic Sampling vs. Extended Statistics

On datawarehouse databases, I frequently recommend increasing the level of dynamic sampling because:

  • Queries have complex predicates with AND, OR, IN(), ranges and correlated values for which the optimizer cannot estimate the cardinality properly
  • Queries are long anyway (compared to OLTP) and can afford more parse time to get an optimized execution plan

However, there’s a drawback with this approach because sometimes the dynamic sampling estimation may give bad estimations, and supersedes the static statistics which were better. Here is an example in 12.2.0.1

I run with the following parameters:

SQL> show parameter adaptive;
NAME TYPE VALUE
--------------------------------- ------- -----
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
optimizer_dynamic_sampling integer 4

The Dynamic Sampling level comes from previous version (11g) and the Adaptive Statistics have been disabled because of all the problems seen in 12cR1 with Adaptive Dynamic Sampling bugs.

I have a query with very bad response time for some values, going to nested loops for 50000 rows. The reason is an under-estimate in the following part of the query:

SQL> explain plan for
2 SELECT count(*) FROM "APP_OWNR"."TBL_APPLICATION1_ID" "TBL_APPLICATION1_ID" WHERE upper("TBL_APPLICATION1_ID"."OPRID") =upper ('qwertz');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2187255533
&nbspM
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 964 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TBL_APPLICATION1_ID | 82 | 574 | 964 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(UPPER("OPRID")='QWERTZ')
 
Note
-----
- dynamic statistics used: dynamic sampling (level=4)

The estimation is 82 rows but there are actually 50000 rows. We can see dynamic sampling. The misestimate is probably caused by a sample too small.

Ok, a query with an UPPER() function on the column is not a good idea. Let’s try to gather statistics for the expression:

SQL> exec dbms_stats.gather_table_stats('APP_OWNR','TBL_APPLICATION1_ID',method_opt=>'for all columns size auto for columns (upper(OPRID)) size auto');
PL/SQL procedure successfully completed.
 
SQL> explain plan for
2 SELECT count(*) FROM "APP_OWNR"."TBL_APPLICATION1_ID" "TBL_APPLICATION1_ID" WHERE upper("TBL_APPLICATION1_ID"."OPRID") =upper ('qwertz');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2187255533
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 964 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TBL_APPLICATION1_ID | 82 | 574 | 964 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(UPPER("OPRID")='QWERTZ')
PLAN_TABLE_OUTPUT
 
Note
-----
- dynamic statistics used: dynamic sampling (level=4)

We have the same misestimate. But the problem is not our statistics on expression. This query is still doing dynamic sampling.

Here’s the proof that we have good static statistics:

SQL> alter session set optimizer_dynamic_sampling=2;
Session altered.
 
SQL> explain plan for
2 SELECT count(*) FROM "APP_OWNR"."TBL_APPLICATION1_ID" "TBL_APPLICATION1_ID" WHERE upper("TBL_APPLICATION1_ID"."OPRID") =upper ('qwertz');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2187255533
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 964 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TBL_APPLICATION1_ID | 48594 | 332K| 964 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(UPPER("OPRID")='QWERTZ')

Dynamic Sampling did not occur at level 2. Now the estimation is ok thanks to the extended statistics. I have a top-frequency histogram where the cardinality of popular value is exact.

The problem is that dynamic sampling is supposed to add more information to the optimizer, but in this case, it replaces the static statistics. In level 4, dynamic sampling is done as soon as there is a complex predicate in the where clause. And the use of the UPPER() function is considered as a complex predicate. However, in this case, because I have extended statistics, it should be considered as a simple column=value predicate.

Here I’ve set dynamic sampling manually, but this is also what happens when SQL Plan Directives trigger the use of Dynamic Sampling and the good histogram is ignored. This reminds me a Ludovico Caldara blog post about SPD.

Here, maybe, the solution would be Adaptive Dynamic Sampling which may increase the level of sampling when needed:

SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
 
SQL> explain plan for
2 SELECT count(*) FROM "APP_OWNR"."TBL_APPLICATION1_ID" "TBL_APPLICATION1_ID" WHERE upper("TBL_APPLICATION1_ID"."OPRID") =upper ('qwertz');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2187255533
 
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 964 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TBL_APPLICATION1_ID | 37831 | 258K| 964 (1)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter(UPPER("OPRID")='QWERTZ')
 
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)

In this case, Adaptive Dynamic Sampling is a good approximation. But it would be better to have a level of dynamic sampling that does not consider a predicate as a complex one when the extended statistics exactly match the predicate. Before there is enough artificial intelligence to cope with this, the best recommendation is to focus on design. In this case, ensuring that we have only uppercase values is the best way to keep queries and estimations simple.

 

Cet article Dynamic Sampling vs. Extended Statistics est apparu en premier sur Blog dbi services.

nVision Performance Tuning: 7 Analysis of Tree Usage with the Selector Log

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

Over time, the selector log will build up a picture of how each tree is used in a system. Here are two examples of how it can be used.

You may look at a piece of SQL generated by nVision, it will have a literal value for the selector number, and you want to know about that particular selector.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">REM treeanal.sql
WITH t as (
SELECT DISTINCT d.tree_name, s.dtl_fieldname, d.tree_acc_method, d.tree_acc_Selector, d.tree_acc_sel_opt
FROM pstreedefn d, pstreestrct s
WHERE d.tree_Strct_id = s.tree_strct_id
), l as (
SELECT *
FROM ps_nvs_treeslctlog l
WHERE l.selector_Num = &selector_num
)
SELECT l.*, t.dtl_fieldname, t.tree_acc_method, t.tree_acc_Selector, t.tree_acc_sel_opt
FROM t, l
WHERE t.tree_name = l.tree_name
/

Now you can see various pieces of information about the selector and the report in which it was used.

  • Process instance of the report, although it is also in the ACTION string
  • Length of the selector.  Here it was 10 characters so it was in PSTREESELECT10.
  • The number of rows inserted into the selector.  This is useful if you want to recreate the conditions at runtime by populating the selector table manually.
  • Time at which the selector was populated.
  • Session module, usually the process name.  
  • Session action string, which contains the process instance, report ID and business unit.
  • The client info string, containing the operator ID, database name, the name of the host running nVision and the name of the executable.
    • PSNVS is nVision running on Microsoft Excel
    • PSNVSSRV is nVision running in OpenXML
    • psae is a PS/Query using the IN TREE operator that has been scheduled to run on the Process Scheduler.
    • PSQED is the windows query client running in 2-tier mode and using an IN TREE operator.
    • PSSAMSRV indicates either nVision or PS/Query running either through the PIA or on the windows client in 3-tier mode.
  • Status of the selector.  X indicates that the selector has been deleted and the partition has been dropped.
  • Name of the tree.
  • Name of the schema in which the selector table is located.
  • Partition name in the tree selector table in which the data was held.  This will be blank if the partition has been dropped.
  • The current values of the three tree selector flags on the tree definition are also reported.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 75%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECTOR_NUM PROCESS_INSTANCE Len   NUM_ROWS TIMESTAMP                    MODULE       APPINFO_ACTION
------------ ---------------- --- ---------- ---------------------------- ------------ ----------------------------------------------------------------
CLIENT_INFO S TREE_NAME OWNERID PARTITION_NAME JOB_NO DTL_FIELDNAME T T T
---------------------------------------------------------------- - ------------------ -------- -------------------- ---------- ------------------ - - -
10233 1780069 10 362 10-NOV-17 02.40.50.755038 AM RPTBOOK PI=1780069:UKGL123I:UK001
GBNVISION,PSFINPRD,UKLONWIN001,,PSNVSSRV.EXE, X UKGL_ACCOUNT SYSADM 33052 ACCOUNT J D S

The following query aggregated log entries to report the number of times each tree was used over the last 7 days, and provide various statistics about the numbers of rows extracted from trees into the selector tables, and the current tree performance options.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">REM tree_usage.sql
WITH t AS (
SELECT DISTINCT d.tree_name, s.dtl_fieldname, d.tree_acc_method
, d.tree_acc_Selector, d.tree_acc_sel_opt
FROM pstreedefn d, pstreestrct s
WHERE d.tree_Strct_id = s.tree_strct_id
), l AS (
SELECT tree_name, length
, COUNT(*) num_uses
, MIN(num_rows) min_rows
, AVG(num_rows) avg_rows
, MEDIAN(num_Rows) med_rows
, MAX(num_rowS) max_rows
, STDDEV(num_Rows) stddev_rows
, SUM(num_rows) sum_rows
, COUNT(distinct process_instance) processes
FROM ps_nvs_treeslctlog l
WHERE num_rows>0
AND timestamp >= sysdate-7
GROUP BY tree_name, length
)
SELECT l.*, t.dtl_fieldname, t.tree_acc_method, t.tree_acc_Selector, t.tree_acc_sel_opt
FROM t, l
WHERE t.tree_name = l.tree_name
ORDER BY sum_rows
/

The default recommendation is that all trees should use:

  • Literal values where possible when working with less than about 2000 rows in the selector.  However, where more than 2000 rows it may be better to join the table due to parse and execution overhead of each criterion.
  • Dynamic selectors 
  • Single Value joins 

This report can help to identify trees where extreme volumes mean that different options should be considered.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">                          Num    Min Average Median    Max Std Dev       Sum   Num
TREE_NAME Len Uses Rows Rows Rows Rows Rows Rows Procs DTL_FIELDNAME T T T
------------------ --- ------ ------ ------- ------ ------ ------- --------- ----- ------------------ - - -

CORP_ACCT 10 5 1147 2839 2616 6668 2263 14194 1 ACCOUNT J D S
FUNCTION 10 480 9 32 35 35 8 15474 43 CHARTFIELD2 L D S
INT_SUP 6 7 225 2463 2838 2838 987 17243 1 PRODUCT L D S
STAT_PRODUCT 6 8 2889 2889 2889 2889 0 23112 1 PRODUCT J D S
AFFILIATE 5 43 215 576 509 938 223 24789 15 AFFILIATE L D S
INT_GAAP_CON 5 62 82 486 522 730 225 30153 10 BUSINESS_UNIT L D S
BU_GAAP_CON 5 96 44 619 614 731 115 59461 48 BUSINESS_UNIT L D S
STAT_ACCOUNT 10 45 23 4204 6516 6516 2905 189182 6 ACCOUNT J D S
INT_REP1 10 135 149 1563 1664 1664 379 211005 1 CHARTFIELD1 L D S
COMBO_CODE 10 172 17 1592 1532 2430 809 273846 18 CHARTFIELD1 L D S
UKGL_ACCOUNT 10 2586 2 1713 1147 7797 1793 4430262 110 ACCOUNT J D S
  • The account trees have been set to join the tree to the ledger table rather than literal values because sometimes in excess of 6000 rows are extracted. A query with 6000 literal terms would be extremely large, take time to generate in nVision, and time on the database to parse and execute each criterion. 
  • STAT_PRODUCT has been set to join partly because it is large, it always extracts 2889 rows, but also because the whole tree is extracted every time so it does not cut down the result set.

So good … it will scare your socks off

One of the big releases of 2017 is on our doorstep.  Watch the trailer here

#Accenture #Enkitec Group at #DOAG2017

Yes, it’s that time of the year again when DOAG (Deutsche Oracle Anwender Gruppe) is about to host its annual conference – probably the largest Oracle event in Europe!

DOAG conference 21 - 24 November 2017 in Nuremberg, Germanyhttps://uhesse.files.wordpress.com/2017/11/2017-k-a-banner-600x100-eng_0... 150w, https://uhesse.files.wordpress.com/2017/11/2017-k-a-banner-600x100-eng_0... 300w" sizes="(max-width: 600px) 100vw, 600px" />

The Accenture Enkitec Group is present with five speakers:

Martin Bach: Profiling & Debugging Problems on Modern Linux in the Cloud

Frits Hoogland: Provisioning the Oracle Database in the Cloud

Patrick Hurley: GoldenGate: a Live Introduction

David Kurtz: Practical Active Session History for Developers

And myself: Real-Time Materialized Views in 12c

The conference planner wrongly still lists me as employee of Oracle – an understandable mistake since I have been with Oracle for so long – but I actually changed to the Accenture Enkitec Group five months ago </p />
</p></div>

    	  	<div class=

Friday Philosophy – What Makes a Community?

Earlier this week Daniel Westermann asked a question on twitter: “What is community?”

Daniel was not specific about if this was a work/user group community or a wider consideration of society, but my first thoughts were about the Oracle community (or communities) that I have been involved in. By community I mean anything from a national or regional user group; a regular, geographically agnostic conference; a special interest group; even just a bunch of people who meet socially who share a common interest (such as London Oracle Beers which I help run). You could actually think of it as the world-wide collective of all such smaller Oracle communities.

I’ve thought about this a lot over the years and you can see my answer in the right. Quite obviously an Oracle community needs a shared interest in Oracle, in some aspect of it or a broader view. All tech communities focus on a brand of tech, I don’t think you get a “computers” community as it is just too broad. But the parts that make up the community are, I think, alwyas the same.

1) A large group of people willing to take part
+
2) A medium group of people willing to share
+
3) A small group of people willing to drive the community

Taking a regular conference as an example, the first group are the delegates. If not enough people are willing to pay for it and turn up then your conference will fail. The second group are the speakers and people who will help with organising. The third group are the ones who get the second group involved, manage the effort and sell the idea of the conference.

That third, small group is the key. If you lack that, you have no community. Sometimes, especially for smaller groups, that third group could be very small, even just one person. Delegates and speakers can come and go but it’s not so easy with the drivers of a community.

For several years we had a small but active Oracle user group in the centre of the UK, in Birmingham. It was run by one person, Mike Mckay-dirden. He almost single handedly started it up, organised the venue and corralled some of us speakers into coming over to talk. It ran successfully for several years but then Mike decided he could not keep doing it. He stopped, no one took it over – and the community died.

With larger communities such as UKOUG or DOAG there will be several people driving it all forward and usually, if one drops out you can keep going until another driven person turns up to help. But it is always a very small group of people doing a hell of a lot of work.

Over the years I’ve watched some communities get stronger or weaker and even die off as those key, driving people change. You can tell who they are, they are the ones who look knackered all the time :-). The LOB is in danger of dying as a couple of the driving people are no longer around and I can’t get to London very often now.

The chances are that as you are reading this blog you are part of an Oracle community. If so, I’d encourage you to support the drivers in those communities. If you lose them, it could really badly impact your community. Would I encourage you to become one of those drivers? Well, I would. But you have to want to do it yourself – it’s a lot of hard work and sometimes it feels like none of the first and second group really appreciate what you are doing, which can be very demoralising. And it eats up a lot of time.

https://mwidlake.files.wordpress.com/2017/11/img_1887.jpg?w=600&h=450 600w, https://mwidlake.files.wordpress.com/2017/11/img_1887.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

I went from being one of the large group willing to take part to a member of the medium group willing to share pretty quickly. After a few years I stepped up to being in the smaller group, for a couple of groups actually. I took those steps up because I wanted to, not with any real expectation of gain (I could see it was going to be me working “for free”!). But I am absolutely sure that I would not be where I am now if I had not. I would not be an OakTable member, I would not be an Oracle ACE Director, and I would not be a known international speaker if I had not at least joined the middle group. Joining the band of drivers introduced me to a lot of really nice, really helpful people too.

This blog has been all about Oracle communities but I think the three-group-theory might apply to all communities. I’ll have to think on that a little longer before I voice an opinion. One thing I do know – It’s really nice being part of communities.

Azure SQL Database- Automatic Index Management

As I begin my education in SQL Server 2017, as well as Azure SQL Database, my attention was caught by the optimizer improvements and especially automatic tuning.

Often referred to with features starting with the naming convention of “dynamic” in Oracle, many automatic features either thrill or frustrate DBAs, no matter what the platform.  The frustration enters with the features introduction and often decreases as the feature matures and becomes more stable.  With the introduction of the Automatic Tuning from Microsoft, there’s a sense of concern, but also some very promising ways Microsoft is introducing these features.

With Automatic Tuning, the Database Engine in Azure SQL Database has Automatic Index Management.  Any DBA is going to take the recommendation for an addition of an index with a grain of salt.  The index has to be justified, not that it will only be used by one query, but that it provides repeated value and it can be justified by the pressure added for every insert, update and delete to additional index.

Where we’re hesitant to add an index, the opportunity to drop an unused index is something we’ll take advantage of whenever its offered.  With Azure SQL Database, the Database Engine can drop unused indexes as it tracks index usage.

In Oracle, we’ve been able to track usage of an index, but only within the monitoring period and if you had end of period reports or end of year processing that wasn’t captured, you could drop indexes that were crucial to the system.  Now, with Oracle 12.2, they’ve started to track, not only if indexes are used or unused ongoing, but how many times the index is used:

select name, total_access_count, total_exec_count, last_used from DBA_INDEX_USAGE where name='IDX_1';

NAME      TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT  LAST_USED
--------- ------------------ ---------------- ---------------------
IDX_1                   85                85     11-03-2017 18:58:43

With Azure SQL Database, Automatic Tuning is continuously monitoring and should be trusted for dropping indexes, but upon further research, it became apparent, that like Oracle, there is a window that the monitoring is for and if you have period or yearly processing that relies on the indexes, you could be impacted.  Per the documentation, “indexes that were not used in a longer period of time that could be removed.” Am I going to choose to drop that index its identified that isn’t required?

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

Even a bigger question-  would I ever turn on the automation for the create or drop index in the automatic tuning feature?  Nope and NOPE.

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

I have to admit, when the documentation states that any change that doesn’t improve performance is “immediately reverted” in Automatic Tuning, I’m curious if that the feature is part of index management, as it might be an interesting day in a DBAs life to have indexes disappear and reappear.  I also wonder what that means when we’re talking about a 1TB+ index… </p />
</p></div></div>

    	  	<div class=

East Coast Oracle Conference, (ECO 17)

So this week I’m back on the other coast, Raleigh, NC at ECO17 after last week in Seattle. I’ve switched from SQL Server to Oracle and we won’t discuss how many times I had to correct myself as I said, “transaction log” instead of “archive log” and pointed west instead of east for the ocean…My expression at times was similar to Obi Wan’s-

For the next month, I’ll be sticking to the Oracle side, as this is going to be a crazy month for travel.  My two sessions at ECO this week were well attended and I really enjoyed talking to folks and learning some new schtuff.

Great job on your IoT sessions, Blaine Carter.  Blaine probably has my dream job of playing with all kinds of open source all day, but he actually did a presentation on how he’s set up his smart home.  He took the time to explain how we can do it better and what open source software is out there to centrally manage it and how to use ORDS for the rest APIs.  It was fun and the discussions were interesting and gave me the information I need when I upgrade all my smart home gadgets in the 5th wheel next year.

I presented “DevOps for the DBA” and presented my former keynote session, “Cloudy with a Chance of Databases”, both of which were well attended.  I love how many folks in the Oracle community are beginning to embrace advanced skills and not just content with standard database administration.  There are interesting and exciting areas for us to expand our careers and it isn’t the downhill slope that some people are promoting with the introduction of autonomous database.

The evening was spent having dinner with some awesome folks, even if they decided to photoshop themselves into the picture, (we won’t discuss this abomination of nature….and art.

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

For Day two, I did spend significant time speaking with attendees and still was able to attend a few sessions, including a great Javascript/CSS session from Jorge Rimblas.  He did a great job of introducing these two languages to PL/SQL Developers and hopefully offering some insight of why they might want to do more with both of them.



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [East Coast Oracle Conference, (ECO 17)], All Right Reserved. 2017.

The post East Coast Oracle Conference, (ECO 17) appeared first on DBA Kevlar.

Little things worth knowing: scheduler jobs and Instance Caging

While updating material for a training class about Database Resource Management I briefly wondered if Instance Caging should apply for scheduler jobs as well. The obvious answer is “yes”, but I wanted to find proof. I hope I did in the end, and wanted to share how I got there.

The test environment

My system is based on the shiny new AMD Ryzen 7 1700X Eight-Core Processor, and it shows as 1s8c16t. I really like it! My 12.2.0.1 database named DEMO is patched to August 2017 – 12.2.0.1.170814 to be precise. It’s difficult to test resource management, and specifically Instance Caging, with a dual-core laptop, so this machine should help!

Test setup

Apart from the database workload I am soon going to launch the system is otherwise idle. I set cpu_count to 4 and made sure I had a resource manager plan active:

SQL> select name, value from v$parameter 
  2   where name in ('cpu_count','resource_manager_plan');

NAME                           VALUE                                             
------------------------------ ------------------------------------------------
cpu_count                      4                                                 
resource_manager_plan          SCHEDULER[0x4ABF]:DEFAULT_MAINTENANCE_PLAN        

For this case it doesn’t matter that my resource manager plan is associated with the maintenance window. All I needed was some resource manager plan. In production systems the situation is most often somewhat different and proper resource management is crucial.

The scheduler needs something to work with, and I opted for a stored procedure that needlessly burns CPU. Like this one:

SQL> create or replace procedure martin.burn_proc(
  2   pi_num_iterations number)
  3  as
  4   i number;
  5  begin
  6  for j in 1..pi_num_iterations loop
  7   i := dbms_random.random;
  8  end loop;
  9 end;
 10 /

Procedure created.

Now all I had to do was to create an anonymous block of PL/SQL scheduling a number of jobs. This little piece of code does just that:

SQL> !cat burn_sched.sql 
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') now from dual;

show user

accept num_jobs number prompt 'how many jobs to schedule? '

prompt creating a number of scheduler jobs
declare
        i number;
begin
        for i in 1..&num_jobs loop
                dbms_scheduler.create_job(
                        job_name => 'martin.burn_proc_job' || i, 
                        job_type => 'PLSQL_BLOCK', 
                        job_action => 'begin burn_proc(1000000000); end;', 
                        start_date => systimestamp, 
                        enabled => true);
        end loop;
        dbms_lock.sleep(5);
end;
/

prompt checking for running jobs
select count(*) from dba_scheduler_running_jobs where owner = 'MARTIN';

Now it’s time to run the code!

SQL> @burn_sched

NOW
-------------------
05.11.2017 09:38:49

USER is "MARTIN"
how many jobs to schedule? 3
creating a number of scheduler jobs
old   4:        for i in 1..&num_jobs loop
new   4:        for i in 1..         3 loop

PL/SQL procedure successfully completed.

checking for running jobs

  COUNT(*)
----------
         3

With this first test I wanted to see what happens when keeping the number of jobs lower than the value I defined for cpu_count. Using Active Session History (ASH) as the source for performance data is probably the easiest way to analyse what happened.

Careful: using Active Session History (ASH) requires a license!

Provided you are licensed to use ASH, a query such as the following provides valuable insights:

SQL> select count(*), event, session_state, session_type, username
  2  from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
  3  where module = 'DBMS_SCHEDULER'
  4  and sample_time > to_date('05.11.2017 09:38:49','dd.mm.yyyy hh24:mi:ss')
  5  group by event, session_state, session_type, username;

  COUNT(*) EVENT                SESSION SESSION_TY USERNAME
---------- -------------------- ------- ---------- ----------
        89                      ON CPU  FOREGROUND MARTIN

As you can see, none of the samples in ASH show any waits for CPU. Let’s increase the session count to a value that exceeds my cpu_count of 4:

SQL> @burn_sched

NOW
-------------------
05.11.2017 09:51:45

USER is "MARTIN"
how many jobs to schedule? 5
creating a number of scheduler jobs
old   4:        for i in 1..&num_jobs loop
new   4:        for i in 1..         5 loop

PL/SQL procedure successfully completed.

checking for running jobs

  COUNT(*)
----------
         5

After these 5 jobs completed, I checked ASH again to see if there was a difference:

SQL> select count(*), event, session_state, session_type, username
  2  from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
  3  where module = 'DBMS_SCHEDULER'
  4  and sample_time > to_date('05.11.2017 09:51:45','dd.mm.yyyy hh24:mi:ss')
  5  group by event, session_state, session_type, username;

  COUNT(*) EVENT                          SESSION SESSION_TY USERNAME
---------- ------------------------------ ------- ---------- ----------
       153                                ON CPU  FOREGROUND MARTIN
        66 resmgr:cpu quantum             WAITING FOREGROUND MARTIN

And indeed, there is a number of Resource Manager CPU waits! To me this is proof enough that scheduler jobs also fall into the category of workload that can be caged.