Feed aggregator

ORA-01652: Unable to extend temp segment by 2048 in table space TEMP

Tom Kyte - Tue, 2018-03-06 15:26
Hi Team, We have facing the issue with 1 query is consuming 331GB of TEMP tablespace. WE have configured 340GB of TEMP TS and have some limitation to add more temp files on DB server. due to it the other session get starved with TEMP tablespace an...
Categories: DBA Blogs

Load External table trimmed with varchar2 without null

Tom Kyte - Tue, 2018-03-06 15:26
Hi all, i have this CTL FILE: <code>CREATE TABLE EXT_T_SI_EG_RAI_170630E1166A ( WAEHRUNG CHAR(3) ,GESCHAEFT_ID VARCHAR2(48) ,GESCHAEFTSKATEGORIE CHAR(1) ,UNTERGESCHAEFT_ID VARCHA...
Categories: DBA Blogs

Index size got doubled after rebuild

Tom Kyte - Tue, 2018-03-06 15:26
Hi, We did index rebuild for the partition table. Index is also partitioned one. Before rebuilding index, the size of the index was 170 GB. But after rebuilding the size got doubled and it was 327GB. Used below command: alter index index_na...
Categories: DBA Blogs

How to trace PL/SQL procedure for tuning

Tom Kyte - Tue, 2018-03-06 15:26
Hi tom, There is an procedure which is running very long/ taking time So, how can i trace that procedure to check where is the issue. like - tkprod or trace session
Categories: DBA Blogs

Need help to select dates in Israel time from a database server located in US

Tom Kyte - Tue, 2018-03-06 15:26
select example_dt from tab where tabid='123'--example_dt is a date column in the table 'tab' In this above query how can I get/convert example_dt in Israel time if I am running this query from my US database server?
Categories: DBA Blogs

TDE and rekey master key

Tom Kyte - Tue, 2018-03-06 15:26
Hello, I want to use TDE tablespace encryption on my database. One part of my study is "how can I manage my Master key" : my security officer ask me to change regularly the key. I read in oracle documentation (https://docs.oracle.com/cd/E11882_0...
Categories: DBA Blogs

Training Class Manuals For Sale

Pete Finnigan - Tue, 2018-03-06 14:26
I have previously offered spare printed training manuals last year for sale here and these were snapped up. I have just found one manual for my two day class - how to perform a security audit of an Oracle database....[Read More]

Posted by Pete On 06/03/18 At 02:51 PM

Categories: Security Blogs

Pushing predicates into MySQL subqueries

Bobby Durrett's DBA Blog - Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

Pushing predicates into MySQL subqueries

Bobby Durrett's DBA Blog - Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

REST Paging Support by Oracle Offline Persistence in JET

Andrejus Baranovski - Tue, 2018-03-06 12:32
Oracle Offline Persistence query handler - Oracle Rest Query Handler supports pagination for Oracle ADF BC REST service out of the box. Check my previous post to see how querying works through offline persistence toolkit for ADF BC REST service - Shredding and Querying with Oracle Offline Persistence in JET.

Pagination is a must for large REST resources, its great that Oracle offline persistence toolkit supports it. Let's see it in action.

I navigate through the data with left/right arrows, this triggers REST call with pagination parameters - limit and offset. These are standard parameters supported by ADF BC REST. Requests are executed online:


All pages of data are cached by offline toolkit, if while offline we try to access previously cached page by executing REST request with paging parameters - we will get data from offline toolkit. Now I switch offline and try to navigate to the one of cached pages - data is retrieved from cache automatically:


If I navigate to the page, which was not cached (meaning - not accessed while online) - no results returned. In such situation I can navigate back (paging parameters will be updated) and cached data will be displayed for the page which was cached:


Paging navigation control buttons are calling JS functions to update startIndex:


Sample application is using JET Collection API to execute fetch requests. Collection is extended with getURL function which sets limit and offset parameters to execute paging request:


Once again, make sure to use Oracle Rest Query Handler in offline persistence toolkit configuration:


Fetch function is called through JET Collection API. Start index value is calculated dynamically - this allows to execute paging requests. Same function works online and offline, no need to worry about connection status, all online/offline logic is handled by persistence toolkit:


Sample application for this post is available on GitHub.

Oracle Container Services for use with Kubernetes(1.9.1) 1.1.9

Wim Coekaerts - Tue, 2018-03-06 11:23

We just released Oracle Container Services for use with Kubernetes 1.1.9. This is based on Kubernetes 1.9.1.

There are also docker images to get going easily. You can download them from the Oracle Container Registry using standard docker commands. Please remember that we have OCR mirrors that provide fast performance (ocr-phx.oracle.com ocr-ash.oracle.com ocr-fra.oracle.com - I suggest using one of those alternative mirrors... at some point we will do traffic routing but right now it's still manual for this). For users trying out our OCSK8s (let me shorten it to that) in Oracle Cloud Infrastructure, do use the mirrors as they are hosted inside the OCI datacenters.

The individual packages are released in the Oracle Linux 7 add_ons channel  on yum.oracle.com.

Documentation can be found here. This release is also formally supported as part of Oracle Linux support.

Also of note, we are a certified platform/distribution in the Kubernetes Conformance program. See here.

Study Finds Precision Medicine Initiatives Active in Broader Spectrum of Disease Areas and Datasets, But Not Without Technological Challenges

Oracle Press Releases - Tue, 2018-03-06 10:00
Press Release
Study Finds Precision Medicine Initiatives Active in Broader Spectrum of Disease Areas and Datasets, But Not Without Technological Challenges

HIMSS CONFERENCE - LAS VEGAS, NV—Mar 6, 2018

Oracle Health Sciences today announced the results of a new study that explores the current and planned status of precision medicine initiatives, associated challenges and the future impact of this discipline on life sciences and healthcare communities.

Conducted by GenomeWeb and sponsored by Oracle Health Sciences, the study revealed perspectives on the state of precision medicine from 316 scientists, researchers and other members of the life sciences and healthcare industries. While these communities have largely embraced the concept behind precision medicine—using the molecular makeup of patients in order to improve the diagnosis and treatment of disease—the survey captured a deeper look into the current and future statuses, approaches and implementations of precision medicine initiatives.

Among its numerous findings, the survey discovered that the majority of organizations are active with precision medicine initiatives; 62 percent of the respondents participate in research activities to drive biomarker discovery or translational research, while another 12 percent would like to participate or are planning to do so in the next 12-24 months. Not surprisingly, oncology is still considered the disease area where precision medicine will have greatest impact. However, respondents also believe precision medicine will benefit several other disease areas, including cardiovascular disease, neurology and pediatrics, which are already included in some organizations’ current initiatives.

Early initiatives in precision medicine leveraged commercial partners to generate testing and results, but the new research highlights a shift of bringing more functionality in-house, with over 50 percent of respondents claiming to use either a hybrid or in-house approach.

More than 72 percent of respondents from organizations with ongoing precision medicine initiatives said they are using next-generation sequencing (NGS) variant panels. There is a growing interest in additional data types, including more complex NGS, whole genome sequencing, and other omics data types. The study also found that nearly 80 percent hope they will be able to fully leverage large, more complex datasets to identify new insights and improve treatment recommendations by implementing these technologies in the future.

The survey also found that the vast majority of respondents who are not currently planning such an effort indicated that they would see a benefit in pursuing one, and only 18 percent of the respondents indicated an insufficient benefit to their business. However, there are still a variety of challenges in developing a precision medicine initiative, with insufficient technical structures cited as a huge obstacle in organizations’ ability to pursue precision medicine initiatives.

"Precision medicine is vital to advancing medicine, and critical to its success is the underlying technology needed to manage the large volumes of data it requires," said Andy Alasso, Global Vice President, Oracle Health Sciences. "The findings revealed that while there is widespread interest in using more complex and diverse clinical and genomics data, there is also anxiety among researchers about deficiencies in their current technical infrastructure to handle such data. This underscores the importance of platforms like Oracle Healthcare Foundation (OHF) to ensure the pace of innovation in precision medicine is not slowed by a lack of technical support."

The study was conducted in the fall of 2017 by GenomeWeb and sponsored by Oracle Health Sciences. Data was gathered via an online survey taken by GenomeWeb readers from a variety of organizations including academic institutes, hospitals/medical centers, government agencies, pharmaceutical companies, contract research organizations, genomics service providers and clinical/reference labs. The total number of respondents surveyed was 316 and included primarily scientists and researchers, followed by people in executive or corporate management positions.

Contact Info
Valerie Beaudett
Oracle
+1 650.400.7833
valerie.beaudett@oracle.com
Phebe Shi
Burson Marsteller
+1 415.216.3067
phebe.shi@bm.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

About Oracle Health Sciences

Oracle Health Sciences breaks down barriers and opens new pathways to unify people and processes, helping to bring new drugs to market faster. As the number one vendor in Life Sciences (IDC, 2017) and the number one provider of eClinical solutions (Everest Group, 2017), powered by the number one data management technology in the world (Gartner, 2017), Oracle Health Sciences is trusted by 29 of the top 30 pharma, 10 of the top 10 biotech, and 10 of the top 10 CROs for clinical trial and safety management around the globe.

Trademarks

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

Talk to a Press Contact

Valerie Beaudett

  • +1 650.400.7833

Phebe Shi

  • +1 415.216.3067

I’m Jason Fletcher and this is how I work

Duncan Davies - Tue, 2018-03-06 07:00

To anyone who frequents the PSAdmin.io slack community (and that’s almost 500 people) the name Jason Fletcher (or fl3tchr) will be one that’s instantly familiar. He is not only seemingly ever-present, but also dishes out rapid-fire PeopleSoft advice, humour, gifs and good-natured sarcasm in more-or-less equal measure. I’m thrilled that he has added his profile to our ‘How I Work‘ series.

Name: Jason Fletcher

Occupation: It breaks, I fix. (AKA administrator for the City of Albuqerque)
Location: Albuquerque, NM
Current computer: My workstation is a HP ZBook 15″
Current mobile devices: iPhone 6s Plus (gotta have the larger screen for when I have to RDP into work)
I work: Humorously

What apps/software/tools can’t you live without?
1) Beyond Compare – So much better than Compare Reports.  So much.

2) EditPad Pro – It’s been my text editor for over a decade.  Handles gig sized files without much issue.

Besides your phone and computer, what gadget can’t you live without?
None.  I’ve been accused of loving my computer more than my wife.

What’s your workspace like?
Workstation is an i7 with 8 GB RAM and docked to 2×24” monitors. Yes, it is a nice view.

What do you listen to while you work?
Depends on the intensity of the concentration.  Routine work is subject to podcasts (a large amount of the TWiT network & Tom Merritt) followed by rock and instrumental music.  Concentration requires instrumental or even white noise. (SimplyRain is a great app for rain noise.  Which I don’t hear much of in the high desert.)

What PeopleSoft-related productivity apps do you use?
1) Slack – psadmin.io.  It’s like a responsive MOS.

2) SQL Developer is my go-to SQL client.

3) Oracle Enterprise Manager for troubleshooting database performance.

Do you have a 2-line tip that some others might not know?
I’ve put this one in psadmin’s slack: in Windows Explorer, typing cmd (or powershell) in the address bar opens a command prompt at that location.

What SQL/Code do you find yourself writing most often?
A lot of effdt/seq SQL or SQL that joins psoprdefn & psroleuser.

What would be the one item you’d add to PeopleSoft if you could?
git or some other integrated versioning system.

What everyday thing are you better at than anyone else?
Beats me.

How do you keep yourself healthy and happy?
I’m currently a 1 gup in taekwondo (red belt with black stripe), which is the first rank below a 1st degree black belt. The outdoors are great in New Mexico most of the year so I do quite a bit of hiking. At nights I spend a lot of time playing Overwatch.

What’s the best advice you’ve ever received?
Love the Lord God with all your heart and soul and love your neighbour as yourself.

Match_recognise – 2

Jonathan Lewis - Tue, 2018-03-06 01:59

In my previous post I presented a warning about the potential cost of sorting and the cost of failing to find a match after each pass of a long search. In a comment on that post Stew Ashton reminded me that the cost of repeatedly trying to find a match starting from “the next row down” could be less of a threat than the cost of “back-tracking” before moving to the next row down.

Taking the example from the previous posting to explain – the requirement was for customers who had executed a transaction in September but not October, and a match_recognize() clause suggested on the ODC (formerly OTN) database forum to implement this requirement was as follows:

match_recognize
(
        partition by cust_id
        order by trans_dt
        measures
                padding as t1_padding,
        pattern(x+ y* $) 
        define
                x as mth  = 9,
                y as mth != 10
);

In the blog post I raised the issue of an extreme case where there were 100,000 transactions for a single customer of which all but the last was a September transaction and the last was an October transaction. This would have two effects – first that we could have to sort 100,000 rows, including the cust_id that appeared in the “partition by” clause and the 1000-character padding column that was listed in the measures clause, leading to a huge dump to, and constant re-read of, the temporary tablespace; secondly that having failed to find a match starting from row 1 Oracle would go back to row 2 and try again, then to row 3, and so on.

The point that Stew Ashton made was that Oracle doesn’t just “go back to” row 2, it will be unwinding a stack, or reversing out a recursive descent to get there. What this means is that Oracle will fail as it reaches the October at row 100,000 and say “no more X rows, is this a Y row ? no”, backtrack to row 999,998 and say “what if I stop collecting X rows here and start looking for Y rows?”, so it reads row 999,999 as a Y row (since 9 != 10), then finds row 100,000 and fails the match. So it backtracks again to row 999,997 and says “what if I stop collecting X rows here and start looking for Y rows?”, and this time it finds identifies 999,998 and 999,999 as Y rows, then fails on row 100,000.

Remember, this is still part of the attempt to match the pattern starting at row 1 – and there are 999,996 more steps backwards still to go, and the further we go back the further we come forward again until we fail — and there are 999,998 steps we have to back-track before we start to consider a pattern starting are row 2..

To demonstrate the costs I’ve got three variants of the original query. First, the query as it was but limited to just 1,000 rows for a single customer; second a changed pattern that highlights the cost of trying to use back-tracking to match the pattern just once, starting from row 1 (the pattern doesn’t actually meet the original requirement because it would only find customers whose first transaction of the year was in September); finally a changed pattern that achieves the required result much more efficiently than the original (but still very slowly) by adding some human intelligence to the implementation.

Here’s version 1 – which took 257 CPU seconds to handle just 1,000 rows:

select  *
from    (
        select
                t1.*,
                extract(year from trans_dt) yr,
                extract(month from trans_dt) mth
        from
                t1
        )
match_recognize
(
        partition by cust_id
        order by trans_dt
        measures
                padding as t1_padding,
                classifier() cl,
                match_number() as mn
        pattern(x+ y* $)
        define
                x as mth  = 9,
                y as mth != 10
);

You’ll see that I’ve included the “debug” functions of classifier() and match_number() in the SQL above – these are particularly useful with the options “all rows per match” and “with unmatched rows” when you’re trying to figure out why your match_recognize() clause is not producing the right results, so I’ve left them there purely for reference.

Then there’s a version where I’ve made the modification suggested by Stew Ashton to demonstrate the full cost of an attempt to match only if the pattern starts on the first row of the partition. This took just 0.83 CPU seconds to complete. This may sound fairly reasonable, but if you compare that to the time it might take simply to sort and walk once through 1,000 rows you’ll realise that it’s actually pretty expensive – and it’s not surprising that when we had to do the same thing 1,000 times (on a slowly decreasing set, of course, as we work our way down the partition) the original task took 257 CPU seconds.

select  *
from    (
        select
                t1.*,
                extract(year from trans_dt) yr,
                extract(month from trans_dt) mth
        from
                t1
        )
match_recognize
(
        partition by cust_id
        order by trans_dt
        measures
                padding as t1_padding,
                classifier() cl,
                match_number() as mn
        pattern(^ x+ y* $)
        define
                x as mth  = 9,
                y as mth != 10
);

You’ll notice the caret “^” at the start of the pattern – this means the pattern must start at the first row of the partition (just as the “$” means the pattern has to end at the end of the partition).

Finally, thinking of a better way of using match_recognize() for this requirement we realise that we know that November comes after October, and December comes after November so (in the context of our example) the predicate “!= 10” is equivalent to “> 10”. With this code change the original query took 0.82 CPU seconds.


select  *
from    (
        select
                t1.*,
                extract(year from trans_dt) yr,
                extract(month from trans_dt) mth
        from
                t1
        )
match_recognize
(
        partition by cust_id
        order by trans_dt
        measures
                padding as t1_padding,
                classifier() cl,
                match_number() as mn
        pattern(x+ y* $)
        define
                x as mth  = 9,
                y as mth  > 10
);

In this case we still have to do a lot of back tracking, but each time we backtrack one step we then check just one row forward for the match to fail (9 is not greater than 10), whereas with the original if we have backtracked 750 steps (for example) we would then have to check 750 rows before we reached the October row for the match to fail.

Bottom line: back-tracking is a massive cost if you have to take a lot of steps backwards to the previous starting row; and you need the match to fail (or succeed) as fast as possible as you start stepping forward again.

Addendum

Since Stew Ashton had highlighted the omission in the previous blog post I passed him a copy of this post before publishing it, asking him to check whether there were any errors or omissions in the way I had described the work Oracle would do back tracking in this example. He said that he couldn’t think of anything to improve the explanation (though I will still claim responsibility for any errors, omissions, or ambiguities) and then suggested another, far more efficient, way of getting the required answer by (again) re-thinking the question before writing the code. His solution looks like this:


select  *
from    (
        select
                t1.*,
                extract(year from trans_dt) yr,
                extract(month from trans_dt) mth
        from
                t1
        )
match_recognize
(
        partition by cust_id
        order by trans_dt nulls first
        measures
                padding as t1_padding,
                classifier() cl,
                match_number() as mn
        pattern(x{1})
        define
                x as mth  = 9 and (
                             next(mth) is null or next(mth) > 10
                     )
)
;

The pattern here simply says: for the partition find the first “X”-row, but an X-row is defined as “month is september and either there are no more rows or the next row is after October”. You’ll notice that I’ve modified the “order by” clause to put nulls first – there are none in the sample data, but if there were this change to the order would ensure that for a row where “mth = 9″ the “next(mth)” could only be null if the current row were the last in the partition.

If you imagine walking through the pattern-matching process now, you start looking at rows and keep going until you reach the first September, and each time you find a September you check the next row to see if it’s past the end of partition, or a November or December; if it is you report the current row and move to the end of the partition, if it isn’t you just walk to the next row and repeat the process – you never back-track. Effectively the workload here is simply to sort then walk non-stop through the whole list – and Oracle even tells us that we are using this optimum strategy in the execution plan:


---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |      |      1 |        |      0 |00:00:00.01 |     146 |       |       |          |
|   1 |  VIEW                                           |      |      1 |   1000 |      0 |00:00:00.01 |     146 |       |       |          |
|   2 |   MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTO|      |      1 |   1000 |      0 |00:00:00.01 |     146 |  1186K|   567K| 1054K (0)|
|   3 |    VIEW                                         |      |      1 |   1000 |   1000 |00:00:00.01 |     146 |       |       |          |
|   4 |     TABLE ACCESS FULL                           | T1   |      1 |   1000 |   1000 |00:00:00.01 |     146 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

Operation 2 – the Match Recognize Sort operation – is reported as “deterministic finite auto”, which basically means the duration of the process is predictable because Oracle knows it is a straight end to end walk with no back-tracking. This is the ideal thing to see when you try to design code using match_recognize().

nvl from different tables

Tom Kyte - Mon, 2018-03-05 21:06
Hi Tom, Thanks for being a great support for me over the years. I always look for your solutions whenever I am in need. Could not find anything on this hence posting my first question. In my query I am using few big tables with millions of r...
Categories: DBA Blogs

Generate totals based on group while selecting all columns

Tom Kyte - Mon, 2018-03-05 21:06
Hello- I need to create an output broken down by Account numbers and Locations. Here is my sample table: <code> Create table sample_dat ( POSITION_NO number (5), NAME varchar2(50), TITLE varchar2(50), ACCOUNT number(15), GAAP_A...
Categories: DBA Blogs

Analytical function RANK() vs. DENSE_RANK()

Tom Kyte - Mon, 2018-03-05 21:06
Hi, I have a very plain question about Analytical functions RANK() and DENSE_RANK(). Can you pls tell me a real scenario when we would use "RANK() and why not DENSE_RANK()" and vice versa. Something other than the students rank calculation. Thanks i...
Categories: DBA Blogs

Python cx_Oracle 6.2 is out on PyPI

Christopher Jones - Mon, 2018-03-05 19:01

cx_Oracle logo

cx_Oracle 6.2, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features.



This release:

  • Adds support for creating temporary CLOBs, BLOBs or NCLOBs via a new method Connection.createlob().

  • Adds support for binding a LOB value directly to a cursor.

  • Adds support for closing the connection when reaching the end of a 'with' code block controlled by the connection as a context manager. See cx_Oracle.__future__ for more information.

  • Was internally updated to the newest ODPI-C data access layer, which brings numerous stability fixes and code improvements including:

    • Open statements and LOBs are tracked and automatically closed when the related connection is closed; this eliminates the need for users of cx_Oracle to track them, and removes the error "DPI-1054: connection cannot be closed when open statements or LOBs exist".

    • Errors during implicit rollback at connection close are ignored - but if an error does occur, ensure the connection is dropped from the connection pool. This reduces app errors in cases like where a DBA has killed a session.

    • Avoids an unnecessary round trip to the database when a connection is released back to the pool by preventing a rollback from being called when no transaction is in progress.

  • There was also an internal code restructure to simplify maintenance and consolidate transformations to/from Python objects.

See the Release Notes for all the fixes.

To upgrade to cx_Oracle 6.2 most users will be able to run:

python -m pip install cx_Oracle --upgrade

Spread the word!

cx_Oracle References

Home page: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

ODPI-C 2.2 Release: Powering Oracle Database Access

Christopher Jones - Mon, 2018-03-05 16:24

ODPI-C 2.2.1 has been tagged for release.

Oracle Database Programming Interface for C (ODPI-C) is an open source library of C code that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications. The ODPI-C project is open source and maintained by Oracle Corp.

ODPI-C is used as a data access layer in drivers for Node.js, Python, Ruby, Go, Rust, Haskell and more.

Changes in ODPI-C 2.2 from 2.1 include:

  • Open statements and LOBs are tracked and automatically closed when the related connection is closed; this eliminates the need for users of the driver to do so and removes the error "DPI-1054: connection cannot be closed when open statements or LOBs exist".

  • Errors during implicit rollback at connection close are ignored - but if an error does occur, ensure the connection is dropped from the connection pool. This reduces app errors in cases like where a DBA has killed a session.

  • Avoid a round trip to the database when a connection is released back to the pool by preventing a rollback from being called when there is no transaction in progress.
  • A new, optional, way of including the source code in your projects: embed/dpi.c was added. This simply includes all other source files. You can reliably link with just dpi.c and not have to update your projects if, and when, new ODPI-C versions have new source files.

  • Many stability fixes, code improvements, new tests, and documentation updates.

See the release notes for all changes.

In my opinion, the stability fixes justify upgrading immediately.

The eagle-eyed will note that today is a 2.2.1 release but we actually tagged 2.2.0 a few weeks ago. ODPI-C 2.2.0 was tagged solely to give an identifiable base for node-oracledb 2.2 to use. However Anthony had some ODPI-C fixes queued up in areas of code not used by node-oracledb, hence today's "official" ODPI-C 2.2.1 announcement.

ODPI-C References

Home page: oracle.github.io/odpi

Code: github.com/oracle/odpi

Documentation: oracle.github.io/odpi/doc/index.html

Release Notes: oracle.github.io/odpi/doc/releasenotes.html

Report issues and discuss: github.com/oracle/odpi/issues

Installation Instructions: oracle.github.io/odpi/doc/installation.html.

DevOps Meets Monitoring and Analytics

OTN TechBlog - Mon, 2018-03-05 11:33

Much has been said about the role new technologies play in supporting DevOps, like automation and machine learning. My colleague Padmini Murthy wrote “DevOps Meets Next Gen Technologies”. In that post, Padmini does a great job discussing the DevOps ecosystem, partly based on a recent DevOps.com survey.

New technologies are rapidly shaping the way companies address Security and Application Performance Monitoring as well.

The same survey found 57% of companies have already adopted, and another 36% are planning to adopt modern monitoring in the next 12 months. Major reasons are: enhanced security, increased IT efficiency, and faster troubleshooting as shown in the chart below.  

Figure 1: “DevOps Meets Next Gen Technologies” by Devops.com; benefits and adoption profile for security, performance, and analytics monitoring.

Traditional IT practices would suggest application and security monitorings are oil and water, they don’t mix. Those responsible for applications and those responsible for IT security think and work dramatically different.  Here also, the landscape is changing rapidly.  The rapid proliferation of mobile and web applications built on modular microservices architectures or the like means monitoring needs to be agile and automatic.  At the same time, security strategies need to go beyond a good firewall, intrusion detection, and identity management.

What have emerged are commonalities between security and performance monitoring.  Both are using real-time monitoring of transactions through the entire stack.  Both are using machine learning to translate massive amounts of data into IT and security insights in real time.  Both are correlating data across an entire transaction in real time to quickly find performance or security issues.  Both are summarizing normal and abnormal behavior automatically to identify what’s important to view and what’s normal behavior.

This is what’s behind the design for Oracle Management Cloud.  It unifies all the metadata and log files in the cloud.  It normalizes the information on a big data analytics platform and applies machine learning algorithms to deliver IT Ops and Security dashboards pre-built specifically for security and performance teams with insights in real time, and automatically.

Figure 2: Oracle Management Cloud provides an integrated platform for security and performance monitoring.

Here are some lessons we’ve learned working with customers on DevOps efforts:

  1. Stop denying there is a problem. Ops teams are constantly bombarded by “false Signal” alerts.  They want better intelligence sooner about performance and security anomalies and threats. Read this Profit Magazine article to learn more about what Oracle is doing to help customers defend against ever-changing security and performance threats.
  2. Eliminate operational information silos so you eliminate finger pointing. Put your operational data (security, performance, configuration, etc.) in one place, and let today’s machine-learning-powered tools do the heavy lifting for you. You will reduce finger pointing, troubleshoot faster, and you may be able to eliminate the “war room” entirely. Watch this video to hear what one Oracle customer says about the power of machine learning.

Figure 3: Why Machine Learning is a key enabler for cloud-based monitoring.

  1. Monitor what (really) matters – your actual end-users. Over 70% of IT issues are end-user complaints. This can hinder the Ops team’s ability to respond to important issues. Look at this infographic highlighting the value of application and end-user monitoring. Figure 4 pinpoints why traditional monitoring tools miss the mark when it comes to delivering value.

Figure 4: End-user and application performance monitoring are key to a successful monitoring strategy.

  1. It’s in the logs! Logs are everywhere, but most organizations don’t use them because they are overwhelmed with the amount of data involved. Next-generation management clouds that are designed to ingest big data at enterprise-scale can cope with today’s log data volume and velocity. Check out this infographic for more details on Oracle Management Cloud’s Log Analytics service.

Figure 5: Key challenges with using logs to troubleshoot issues.

  1. Planning is an everyday activity. Leverage analytical capabilities against your unified store of operational information to answer a variety of forward-looking questions to improve security posture, application performance and resource utilization. If you’ve followed my advice in steps 1 through 4 above, you have all the data you need already available. Now it’s time to use it.

Further resources on Oracle Management Cloud:

Pages

Subscribe to Oracle FAQ aggregator