#StackBounty: #oracle #oracle-12c Oracle 12c query performance varies based on schema/user

Bounty: 50

I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here’s my setup.

Oracle 12c DB 
    SAMPLE schema/user
       WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers)
    USER1 schema/user
    USER2 schema/user
    DBA1 schema/user
--SELECT granted on WIDGET table to USER1 and USER2. 
--DBA1 already has access to all table accross all schemas and has many more privs. 

When I execute either of the following:

select * from SAMPLE.WIDGET;
select count(1) from SAMPLE.WIDGET;

It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.

So I’m looking for the following:

Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?


Get this bounty!!!

#StackBounty: #sql #oracle #oracle11g #common-table-expression #ms-query Oracle CTE failing in one computer

Bounty: 100

I have queries created in Microsoft Query to run in Excel with VBA.

They work in different computers but there’s one computer where it doesn’t work.

In that computer the queries still work except the ones that use CTEs.

A normal query like the following works:

SELECT
  TBL.COL
FROM
  DB.TBL TBL;

But when it has a subquery (CTE) like the following:

WITH
  SUBQUERY AS (
    SELECT
      TBL.COL
    FROM
      DB.TBL TBL
  )
SELECT
  SUBQUERY.COL
FROM
  SUBQUERY;

It runs but doesn’t retrieve any data.

It doesn’t even show the column name like it would if it worked but had 0 records returned.

The query shows the warning message:

SQL Query can’t be represented graphically. Continue anyway?

Which is normal and shows in any computer, but it also shows another warning message after:

SQL statement executed successfully.

Which only appears in that computer when it doesn’t work.

I need to be able to use them for the queries that I have made.

Using temporary tables would maybe work but I don’t have the permissions required to try.

I tried using inline views but they duplicate the data.


Get this bounty!!!

#StackBounty: #.net #oracle #entity-framework #ado.net Entity Framework 6 – StoreGeneratedPattern value not staying

Bounty: 50

We are using Entity Framework 6.2.0 against an Oracle 12c database, and have been following the Database-First approach.

A lot of out tables have an _ID column that is generated by the database upon INSERT. This is down by using a default value on the DB server, TABLE_SEQUENCE.NEXTVAL.

After generating the DB model in Entity Framework, I went through and marked each _ID column as StoreGeneratedPattern="Identity" and we were all set to go. However, when updating the DB model after some DB changes, we noticed an issue. One table in particular forgets this setting and resets this back to None. All other tables remember StoreGeneratedPattern="Identity".

This consistently happens each time we update the DB model from the database.
Visual Studio will still say that PROBLEMATIC_TABLE_ID has Identity set, but the .edmx file does not.
My process has been to update the model, then open the .edmx file and manually paste in StoreGeneratedPattern="Identity" for that one column.

I have compared this table against others in the DB, in the .edmx, in the codebase, and I cannot find anything that would cause this one column on this one table to behave differently upon DB model update.

TL;DR: Upon updating our local DB model from the database, the value for StoreGeneratedPattern on one column/table is always reset, while all others stay the same. VS still says it is Identity, but the XML in the .edmx file disagrees and needs to be updated manually.


Get this bounty!!!

#StackBounty: #sql #oracle #recursion #plsql #hierarchical-data Oracle SQL/PLSQL: Hierarchical recursive query with repeating data

Bounty: 50

I have a recursive function below that works very well but I have now found that some of the data is not unique and I need a way to handle it.

  function calc_cost(
    model_no_ number, 
    revision_ number, 
    sequence_no_ in number, 
    currency_ in varchar2
  ) return number is
    qty_ number := 0;
    cost_ number := 0;
  begin

    select nvl(new_qty, qty), purch_cost 
      into qty_, cost_ 
    from prod_conf_cost_struct_clv
    where model_no = model_no_
      and revision = revision_
      and sequence_no = sequence_no_
      and (purch_curr = currency_ or purch_curr is null);

    if cost_ is null then 
      select sum(calc_cost(model_no, revision, sequence_no, purch_curr)) into cost_ 
      from prod_conf_cost_struct_clv 
      where model_no = model_no_
        and revision = revision_
        and (purch_curr = currency_ or purch_curr is null)
        and part_no in (
          select component_part
          from prod_conf_cost_struct_clv
          where model_no = model_no_
            and revision = revision_
            and sequence_no = sequence_no_);
    end if;
    return qty_ * cost_;
  exception when no_data_found then 
    return 0;
  end calc_cost;`

The following criterion is where this function is failing ...part_no in (select component_part....

Sample data:

rownum., model_no, revision, sequence_no, part_no, component_part, level, cost

 1. 62, 1, 00, XXX, ABC, 1, null
 2. 62, 1, 10, ABC, 123, 2, null
 3. 62, 1, 20, 123, DEF, 3, null
 4. 62, 1, 30, DEF, 456, 4, 100
 5. 62, 1, 40, DEF, 789, 4, 50
 6. 62, 1, 50, DEF, 024, 4, 20
 7. 62, 1, 60, ABC, 356, 2, null
 8. 62, 1, 70, 356, DEF, 3, null
 9. 62, 1, 80, DEF, 456, 4, 100
 10. 62, 1, 90, DEF, 789, 4, 50
 11. 62, 1, 100, DEF, 024, 4, 20

If I was to pass the following values into the function parameters: model_no, revision, sequence_no (ignore currency as it is not relevant to the issue):

62, 1, 20

I want it to summarize rows 4-6 ONLY = 170, however it is summarizing rows 4-6 AND 9-11 = 340.

Ultimately this function will be used in the SQL query below:

select level, sys_connect_by_path(sequence_no, '->') path, 
     calc_cost(model_no, revision, sequence_no, 'GBP') total_gbp
from prod_conf_cost_struct_clv
where model_no = 62
  and revision = 1
connect by prior component_part = part_no
  and prior model_no = 62
  and prior revision = 1
start with sequence_no = 20
order by sequence_no

As you can see this would also introduce the issue of component_part = part_no.

Any assistance would be most appreciated.

Thanks in advance.


Get this bounty!!!

#StackBounty: #oracle #oracle-12c #installation Oracle SQLPLUS: ORA-12547: TNS:lost contact

Bounty: 100

This is a total rewrite of the previous post, where people suggested to start from scratch protocoling my actions.

  1. I used How to install Oracle 18c (Enterprise Edition) on Ubuntu 18.04?
    4-step guide + 1-step for troubleshooting.

  2. My machine is Ubuntu 18.04 (amd64) and distributive 18.3 Linux x86-64 downloaded from here. The distributive is run via ./runInstaller. On top of that I Inserted example database LINUX.X64_180000_examples.zip.

  3. Environmental variables:

    #ORACLE 
    export ORACLE_BASE=/oracle18c/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/18.0.0/dbhome_1
    export PATH=/usr/sbin:/usr/local/bin:$PATH
    export PATH=$ORACLE_HOME/bin:$PATH
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
    export ORACLE_LIBPATH=$ORACLE_HOME/lib
    export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$CLASSPATH
    export ORACLE_HOSTNAME=$HOSTNAME
    export ORA_INVENTORY=/oracle18c/app/oraInventory
    export DATA_DIR=$ORACLE_BASE/oradata
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export ADR_HOME=$ORACLE_BASE/diag
    #--------------------------------------------------------------------
    export ORACLE_SID=orcl
    export ORACLE_UNQNAME=orcl
    export PDB_NAME=pdb
    export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    #--------------------------------------------------------------------
    export TMP=/tmp; export TMPDIR=$TMP; export TEMP=$TMP
    
  4. Listener status:
    oracle@sergey-Bionic:~$ lsnrctl status
    
    LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-APR-2019 17:35:43
    
    Copyright (c) 1991, 2018, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sergey-Bionic)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
    Start Date                25-APR-2019 16:50:53
    Uptime                    0 days 0 hr. 44 min. 49 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /oracle18c/app/oracle/product/18.0.0/dbhome_1/network/admin/listener.ora
    Listener Log File         /oracle18c/app/oracle/diag/tnslsnr/sergey-Bionic/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sergey-Bionic)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    The listener supports no services
    The command completed successfully
    oracle@sergey-Bionic:~$ ss -elpunt | grep -E "^Net|tnslsnr"
    Netid  State    Recv-Q   Send-Q      Local Address:Port      Peer Address:Port                                                                                  
    tcp    LISTEN   0        128                     *:1521                 *:*      users:(("tnslsnr",pid=3394,fd=8)) uid:54321 ino:39696 sk:12 v6only:0 <->       
    oracle@sergey-Bionic:~$ 
    
  5. The error is still the same:
    oracle@sergey-Bionic:/$ strace -f -o /tmp/strace.log $ORACLE_HOME/bin/sqlplus /nolog
    
    SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 25 18:02:09 2019
    Version 18.3.0.0.0
    
    Copyright (c) 1982, 2018, Oracle.  All rights reserved.
    
    SQL> connect sys/oracle18c as sysdba;
    ERROR:
    ORA-12547: TNS:lost contact
    
    
    SQL> 
    

Solution suggested here Troubleshoot ORA-12547: TNS:lost contact error. does not help. What is interesting the value of ulimit -c is not persistent between reboots.

Any help, namely accessing a tutorial database, Oracle Database 18c Examples (18.3) for Linux x86-64, (sha256sum – 405ca9e9341a11f3468b792308c03781fc1988b4278395c1954984f2224a4d32), is highly appreciated!


Get this bounty!!!

#StackBounty: #sql #oracle #split Split text into multiple lines based on pipe and cap delimiter – Oracle PL/SQL Pipelined Function

Bounty: 50

I have a table:

 CREATE TABLE "text_file"
( "SEQ" NUMBER,
"SPLIT_VALUE" CLOB
)

The content of the table is:

SEQ       SPLIT_VALUE
1         MSH|^~&|GHH LAB|ELAB-3|GHH OE|BLDG4|200202150930||ORU^R01
          PID|||555-44-4444||EVERYWOMAN^EVE^E^^^^L|JONES|19620320|F|||153 FERNWOOD DR.^^STATESVILLE^OH^35292|
          OBR|1|845439^GHH OE|1045813^GHH LAB|15545^GLUCOSE|||200202150730
          OBX|1|SN|1554-5^GLUCOSE^POST 12H CFST:MCNC:PT:SER/PLAS:QN||^182|mg/dl|70_105
          OBX|2|SN|1554-5^GLUCOSE^POST 12H CFST:MCNC:PT:SER/PLAS:QN||^172|mg/dl|70_105

2         MSH|^~&|GHH LAB|ELAB-3|GHH OE|BLDG4|200202150930||ORU^R01
          PID|||555-44-4444||EVERYWOMAN^EVE^E^^^^L|JONES|19620320|F|||153 FERNWOOD DR.^^STATESVILLE^OH^35292|
          OBR|1|845439^GHH OE|1045813^GHH LAB|15545^GLUCOSE|||200202150730
          OBX|1|SN|1554-5^GLUCOSE^POST 12H CFST:MCNC:PT:SER/PLAS:QN||^182|mg/dl|70_105
          OBX|2|SN|1554-5^GLUCOSE^POST 12H CFST:MCNC:PT:SER/PLAS:QN||^172|mg/dl|70_105

Please note – the possible segment like MSH, OBR, OBX, LX can be 3 character or 2 characters. So, best way would be to get the segment name before the first pipe.

I am looking to split the string in split_value into multiple rows in the following conditions:

  • SEQ — it would pick from the first column
  • SPLIT_SEQ — it would split based on the first word before |, for ex. MSH, OBR, OBX, LX followed by sequence starting from 00. If there is a cap ^, then it would break down even further, for ex. MSH08-01, MSH08-02

Please note – there is an exception for segment MSH. For MSH – first
element is | and second one is ^~&

SEQ SPLIT_SEQ   SEG_SEQ SPLIT_SEQ_VALUE
1   MSH00       1       MSH
1   MSH01       1       |
1   MSH02       1       ^~&
1   MSH03       1       GHH LAB
1   MSH04       1       ELAB-3
  • SEG_SEQ — if the segment, the first word before | is repeated in the same SEQ, then increase it. So, if OBX is twice, then first OBX values would be 1 and for second OBX, it would be 2 and so on
  • SPLIT_SEQ_VALUE — The value from the message above would be specified here.

Please note – I have around 90,000 rows in text_file table. So the solution should be able to process 90,000 efficiently.

The complete output is:

SEQ SPLIT_SEQ   SEG_SEQ SPLIT_SEQ_VALUE
1   MSH00       1       MSH
1   MSH01       1       |
1   MSH02       1       ^~&
1   MSH03       1       GHH LAB
1   MSH04       1       ELAB-3
1   MSH05       1       GHH OE
1   MSH06       1       BLDG4
1   MSH07       1       200202150930
1   MSH08       1       
1   MSH09-01    1       ORU
1   MSH09-02    1       R01
1   PID00       1       PID
1   PID01       1       
1   PID02       1       
1   PID03       1       555-44-4444
1   PID04       1       
1   PID05-01    1       EVERYWOMAN
1   PID05-02    1       EVE
1   PID05-03    1       E
1   PID05-04    1   
1   PID05-05    1   
1   PID05-06    1   
1   PID05-07    1       L
1   PID06       1       JONES
1   PID07       1       19620320
1   PID08       1       F
1   PID09       1       
1   PID10       1       
1   PID11-01    1       153 FERNWOOD DR.
1   PID11-02    1   
1   PID11-03    1       STATESVILLE
1   PID11-04    1       OH
1   PID11-05    1       35292
1   PID12       1   
1   OBR00       1       OBR
1   OBR01       1       1
1   OBR02-01    1       845439
1   OBR02-02    1       GHH OE
1   OBR03-01    1       1045813
1   OBR03-02    1       GHH LAB
1   OBR04-01    1       15545
1   OBR04-02    1       GLUCOSE
1   OBR05       1   
1   OBR06       1   
1   OBR07       1       200202150730
1   OBX00       1       OBX
1   OBX01       1       1
1   OBX02       1       SN
1   OBX03-01    1       1554-5
1   OBX03-02    1       GLUCOSE
1   OBX03-03    1       POST 12H CFST:MCNC:PT:SER/PLAS:QN
1   OBX04       1       
1   OBX05-01    1       
1   OBX05-02    1       182
1   OBX06       1       mg/dl
1   OBX07       1       70_105
1   OBX00       2       OBX
1   OBX01       2       1
1   OBX02       2       SN
1   OBX03-01    2       1554-5
1   OBX03-02    2       GLUCOSE
1   OBX03-03    2       POST 12H CFST:MCNC:PT:SER/PLAS:QN
1   OBX04       2           
1   OBX05-01    2       
1   OBX05-02    2       182
1   OBX06       2       mg/dl
1   OBX07       2       70_105

2   MSH00       1       MSH
2   MSH01       1       |
2   MSH02       1       ^~&
2   MSH03       1       GHH LAB
2   MSH04       1       ELAB-3
2   MSH05       1       GHH OE
2   MSH06       1       BLDG4
2   MSH07       1       200202150930
2   MSH08       1       
2   MSH09-01    1       ORU
2   MSH09-02    1       R01
2   PID00       1       PID
2   PID01       1       
2   PID02       1       
2   PID03       1       555-44-4444
2   PID04       1       
2   PID05-01    1       EVERYWOMAN
2   PID05-02    1       EVE
2   PID05-03    1       E
2   PID05-04    1   
2   PID05-05    1   
2   PID05-06    1   
2   PID05-07    1       L
2   PID06       1       JONES
2   PID07       1       19620320
2   PID08       1       F
2   PID09       1       
2   PID10       1       
2   PID11-01    1       153 FERNWOOD DR.
2   PID11-02    1   
2   PID11-03    1       STATESVILLE
2   PID11-04    1       OH
2   PID11-05    1       35292
2   PID12       1   
2   OBR00       1       OBR
2   OBR01       1       1
2   OBR02-01    1       845439
2   OBR02-02    1       GHH OE
2   OBR03-01    1       1045813
2   OBR03-02    1       GHH LAB
2   OBR04-01    1       15545
2   OBR04-02    1       GLUCOSE
2   OBR05       1   
2   OBR06       1   
2   OBR07       1       200202150730
2   OBX00       1       OBX
2   OBX01       1       1
2   OBX02       1       SN
2   OBX03-01    1       1554-5
2   OBX03-02    1       GLUCOSE
2   OBX03-03    1       POST 12H CFST:MCNC:PT:SER/PLAS:QN
2   OBX04       1       
2   OBX05-01    1       
2   OBX05-02    1       182
2   OBX06       1       mg/dl
2   OBX07       1       70_105
2   OBX00       2       OBX
2   OBX01       2       1
2   OBX02       2       SN
2   OBX03-01    2       1554-5
2   OBX03-02    2       GLUCOSE
2   OBX03-03    2       POST 12H CFST:MCNC:PT:SER/PLAS:QN
2   OBX04       2           
2   OBX05-01    2       
2   OBX05-02    2       182
2   OBX06       2       mg/dl
2   OBX07       2       70_105

I believe that in as plsql pipelined function would be the best way.

Any help would be appreciated.


Get this bounty!!!

#StackBounty: #sql-server #oracle #reporting-services #ssrs-2008-r2 #ssrs-2017 Oracle Date format exception in SQL Server Reporting Ser…

Bounty: 100

Earlier my client was using SSRS 2008R2 with Oracle as transaction database. Recently they have upgraded to SSRS 2017 and now many reports are throwing following error:

ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
[AbnormalTermination:ReportProcessing],
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException:
An error has occurred during report processing. —>
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Query execution failed for dataset ‘Ds_Main’. —>
Oracle.ManagedDataAccess.Client.OracleException: ORA-01830: date
format picture ends before converting entire input string

After closely looking into report query, I have noticed that this error is for all those reports where oracle function TO_DATE(<Date Value>) has been used without date format. For example:

To_date(:Date_Parameter) -> this syntax throws above mentioned error
To_Date(:Date_Parameter,’MM/DD/YYYY’) -> this syntax works perfectly

Any suggestion to fix this issue without updating bunch of reports, please


Get this bounty!!!

Convert Comma separated String to Rows in Oracle SQL

Many times we need to convert a comma separated list of terms in a single string and convert it rows in SQL query.

for example

 India, USA, Russia, Malaysia, Mexico

Needs to be converted to:

 Country
 India
 USA
 Russia
 Malaysia
 Mexico

The following SQL script can help in this:

just replace the required values with your string and your delimiter.

Apache Commons DbUtils Mini Wrapper

This is a very small DB Connector code in Java as a wrapper class to Apache DBUtils.

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

  • No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
  • Automatically populate Java Bean properties from Result Sets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the Result Set can be represented by one fully populated bean instance.

DbUtils is designed to be:

  • Small – you should be able to understand the whole package in a short amount of time.
  • Transparent – DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast – You don’t need to create a million temporary objects to work with DbUtils.

DbUtils is not:

  • An Object/Relational bridge – there are plenty of good O/R tools already. DbUtils is for developers looking to use JDBC without all the mundane pieces.
  • A Data Access Object (DAO) framework – DbUtils can be used to build a DAO framework though.
  • An object oriented abstraction of general database objects like a Table, Column, or Primary Key.
  • A heavyweight framework of any kind – the goal here is to be a straightforward and easy to use JDBC helper library.

Wrapper:

System Design Interview Prep Material

System design is a very broad topic. Even a software engineer with many years of working experience at top IT company may not be an expert on system design. If you want to become an expert, you need to read many books, articles, and solve real large scale system design problems. This repository only teaches you to handle the system design interview with a systematic approach in a short time. You can dive into each topic if you have time. Of course, welcome to add your thoughts!

Table of Contents

System Design Interview Tips:

  • Clarify the constraints and identify the user cases Spend a few minutes questioning the interviewer and agreeing on the scope of the system. Remember to make sure you know all the requirements the interviewer didn’t tell your about in the beginning. User cases indicate the main functions of the system, and constraints list the scale of the system such as requests per second, requests types, data written per second, data read per second.
  • High-level architecture design Sketch the important components and the connections between them, but don’t go into some details. Usually, a scalable system includes web server (load balancer), service (service partition), database (master/slave database cluster plug cache).
  • Component design For each component, you need to write the specific APIs for each component. You may need to finish the detailed OOD design for a particular function. You may also need to design the database schema for the database.

Basic Knowledge about System Design:

Here are some articles about system design related topics.

Of course, if you want to dive into system related topics, here is a good collection of reading list about services-engineering, and a good collection of material about distributed systems.

Company Engineering Blogs:

If you are going to have an onsite with a company, you should read their engineering blog.

Products and Systems:

The following papers/articles/slides can help you to understand the general design idea of different real products and systems.

Hot Questions and Reference:

There are some good references for each question. The references here are slides and articles.
Design a CDN network Reference:

Design a Google document system Reference:

Design a random ID generation system Reference:

Design a key-value database Reference:

Design the Facebook news feed function Reference:

Design the Facebook timeline function Reference:

Design a function to return the top k requests during past time interval Reference:

Design an online multiplayer card game Reference:

Design a graph search function Reference:

Design a picture sharing system Reference:

Design a search engine Reference:

Design a recommendition system Reference:

Design a tinyurl system Reference:

Design a garbage collection system Reference:

Design a scalable web crawling system Reference:

Design the Facebook chat function Reference:

Design a trending topic system Reference:

Design a cache system Reference:

Good Books:

Object Oriented Design:

Tips for OOD Interview

Clarify the scenario, write out user cases Use case is a description of sequences of events that, taken together, lead to a system doing something useful. Who is going to use it and how they are going to use it. The system may be very simple or very complicated. Special system requirements such as multi-threading, read or write oriented.
Define objects Map identity to class: one scenario for one class, each core object in this scenario for one class. Consider the relationships among classes: certain class must have unique instance, one object has many other objects (composition), one object is another object (inheritance). Identify attributes for each class: change noun to variable and action to methods. Use design patterns such that it can be reused in multiple applications.

Useful Websites

Original Source