[Pansophy] Oracle Database

Mon May 4 15:56:56 EDT 2020

Valerie,

The MVs below appear to take a snapshot of the date column for the tables with the same name, possible for a date field select box.  By the names, I'm guessing that this may have been part Data Acquisition, DAQAD project.

Mike Dickey
SRF Inventory Technician
Jefferson Lab
12000 Jefferson Ave
Newport News, VA 23606
(757) 269-7755

From: David Sheppard <sheppard at jlab.org>
Sent: Monday, May 4, 2020 3:29 PM
To: Mike Dickey <mdickey at jlab.org>; Valerie Bookwalter <bookwalt at jlab.org>
Cc: pansophy at jlab.org
Subject: Re: Oracle Database

The following are the materized views that enable the query rewrite feature:

ADAPPS.MV_ULTRAVAC
ADAPPS.MV_HTB_CMTF_DAQ
ADAPPS.MV_PARTICLE
ADAPPS.MV_CLNRMPROCESS
ADAPPS.MV_CMTF_DAQ
ADAPPS.MV_C50_DECOMM

They reside in the FACILITY database.

David Sheppard
________________________________
From: Mike Dickey <mdickey at jlab.org<mailto:mdickey at jlab.org>>
Sent: Monday, May 4, 2020 3:20 PM
To: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>; Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org> <pansophy at jlab.org<mailto:pansophy at jlab.org>>
Subject: RE: Oracle Database

David,

Are these the NIOBIUM_RECEIPT_VIEW & NIOBIUM_WITHDRAWL_VIEW?  If this is the case, we may want to archive the data in a table, since they are no longer in use.  Nb Inventory software has been moved to MIS control (see Bobby).

If there are other materialized views, please provide information on where to find them, so we can review there necessity.

Valerie should be back around 4pm, and may have some thoughts also.

Thanks,

Mike Dickey

SRF Inventory Technician

Jefferson Lab
12000 Jefferson Ave
Newport News, VA 23606
(757) 269-7755

From: Pansophy <pansophy-bounces at jlab.org<mailto:pansophy-bounces at jlab.org>> On Behalf Of David Sheppard
Sent: Monday, May 4, 2020 3:05 PM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org>
Subject: Re: [Pansophy] Oracle Database

Good Afternoon,

In trying to set up a test evironment for Oracle 18c, I encountered that materialized views with the query rewrite feature enabled, thus requiring you to use enterprise edition of the database. This occurred only in the FACILITY database. None of the others use materialized views, as far as I can see. Therefore, we cannot take advantage of reduced costs of the database licenses with standard edition in future maintenance agreements. This will affect how a new database server gets spec'ed out because enterprise edition of the database requires a license for every two cores of the database server, according to Oracle. That can be expensive. We now have one enterprise license. I just wanted to let you know. This is all about being in compliance with Oracle. Let me know how you would like to go forward.

David Sheppard

________________________________

From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Friday, May 1, 2020 2:49 PM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org> <pansophy at jlab.org<mailto:pansophy at jlab.org>>
Subject: Re: Oracle Database

Yes. That would be Oracle 18c.

________________________________

From: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Sent: Friday, May 1, 2020 2:29 PM
To: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org> <pansophy at jlab.org<mailto:pansophy at jlab.org>>
Subject: RE: Oracle Database

Sounds great!

Will this be the upgrade version of Oracle that we are currently getting ready to test?

From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Friday, May 1, 2020 2:25 PM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org>
Subject: Re: Oracle Database

Maybe you can get away with the standard edition of oracle. By doing so, the licensing structure changes. The licensing structure for oracle is complicated. For the enterprise edition, which you have, licenses database servers per core. For each license, the server is allowed two cores. For standard edition, each server licensed per socket. What that all means is that you can get a more powerful server, and standard edition costs less than enterprise edition. Enterprise edition has more features but if you don't need those features, why continue to use it? I can try to put up a test scenario for you to try. It would be scaled down. I could do the adapps schema in all four databases in a test environment. If you could test successfully in that standard edition environment, we could look at that transitioning the databases to that environment. Then we could look at changing your maintenance agreement. That gives us (namely CNI) also a chance of better understanding some new hardware that we could possibly utilize. We (MIS) are also looking to make that switch in hardware.

________________________________

From: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Sent: Friday, May 1, 2020 2:05 PM
To: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Cc: pansophy at jlab.org<mailto:pansophy at jlab.org> <pansophy at jlab.org<mailto:pansophy at jlab.org>>
Subject: RE: Oracle Database

David,

No we do not use Oracle's query rewrite statement.

What options does that give us? Any word on new database server?

Valerie

From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Friday, May 1, 2020 11:13 AM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Subject: Re: Oracle Database

I specifically want to know if you use the query rewrite statement in your code.

________________________________

From: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Sent: Friday, May 1, 2020 11:04 AM
To: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Cc: pansophy <pansophy at jlab.org<mailto:pansophy at jlab.org>>
Subject: RE: Oracle Database

David,

We use VIEWS, Bonnie wrote some and so did a student for our security keys. Is this what you are referring to?

Valerie

From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Friday, May 1, 2020 10:59 AM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Subject: Oracle Database

Good Morning Valerie,

Do you guys use the query rewrite function in oracle? I think you use materialized views, or you did in the past. If you don't use query rewrite, you may be able to save some funds using standard edition instead of enterprise edition. Let me know. Thanks.

David Sheppard
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/pansophy/attachments/20200504/4afead63/attachment-0001.html>