Feed aggregator

oracle database parameters

Tom Kyte - Thu, 2019-02-07 00:46
Dear Tom, I'm trying to test the oracle 12C reporting system, in which i will dump the oracle database transaction database backup to report system. For better performance of completed reporting system, what are the parameters i need to set perfe...
Categories: DBA Blogs

GROUP BY returns incorrect number of rows

Tom Kyte - Thu, 2019-02-07 00:46
Hi A bit of an odd one - possibly a bug. <code>DROP TABLE xxibpc_test; CREATE TABLE xxibpc_test AS WITH data AS (SELECT LEVEL l_no FROM dual CONNECT BY LEVEL <= 10000) SELECT 1 instance_number ,1 + l_no session_id ,10...
Categories: DBA Blogs

Special Tables in OUAF based products

Anthony Shorten - Wed, 2019-02-06 22:47

Long time users of the Oracle Utilities Application Framework might recognize two common table types, recognized by their name suffixes, that are attached to most Maintenance Objects within the product:

  • Language Tables (Suffix _L).  The Oracle Utilities Application Framework is multi-lingual and can support multiple languages at a particular site (for example customers who have multi-lingual call centers or operate across jurisdictions where multiple languages are required). The Language table holds the tags for each language for any fields that need to display text on a screen. The Oracle Utilities Application Framework matches the right language records based upon the users language profile (and active language code).
  • Key Tables (Suffix _K). These tables hold the key values (and the now less used environment code) that are used in the main object tables. The original use for these tables was for key tracking in the original Archiving solution (which has now been replaced by ILM). Now that the original Archiving is not available, the role of these tables changed to be used in a number of areas:
    • Conversion. The conversion toolkit in Oracle Utilities Customer Care and Billing and now in the Cloud Service Foundation, uses the key table for efficient key generation and black listing of identifiers.
    • Key Generation. The Key generation utilities now use the key tables to quickly ascertain the uniqueness of a key. This is far more efficient than using the main table for this, especially with caching support in the database.
    • Information Life-cycle Management. The ILM capability uses the key tables to drive some of its processes including recognizing when something is archived and when it has been restored.

These tables are important for the operation of the Oracle Utilities Application Framework for all types of parts of the product. When you see them now you understand why they are there.

Spring Cloud GCP and authentication from your Spring Boot Application

Pas Apicella - Wed, 2019-02-06 17:18
When using Spring Cloud GCP you will need to authenticate at some point in order to use the GCP services. In this example below using a GCP Cloud SQL instance you really only need to do 3 things to access it externally from your Spring Boot application as follows.

1. Enable the Google Cloud SQL API which is detailed here

  https://cloud.google.com/sql/docs/mysql/admin-api/

2. Ensure that your GCP SDK can login to your Google Cloud SQL. This command will take you to a web page asking which google account you want to use

  $ gcloud auth application-default login

3. Finally some application properties in your Spring Boot application detailing the Google Cloud SQL instance name and database name as shown below.

spring.cloud.gcp.sql.instance-connection-name=fe-papicella:australia-southeast1:apples-db
spring.cloud.gcp.sql.database-name=employees

Now when you do that and your application starts up you will see a log message as follows below clearly warning you this this method of authentication can have implications at some point.

2019-02-07 09:10:26.700  WARN 2477 --- [           main] c.g.a.oauth2.DefaultCredentialsProvider  : Your application has authenticated using end user credentials from Google Cloud SDK. We recommend that most server applications use service accounts instead. If your application continues to use end user credentials from Cloud SDK, you might receive a "quota exceeded" or "API not enabled" error. For more information about service accounts, see https://cloud.google.com/docs/authentication/.

Clearly that's something we have to resolve. To do that we simply can add another Spring Boot application property pointing to a service account JSON file for us to authenticate against to remove the warning.

spring.cloud.gcp.credentials.location=file:/Users/papicella/piv-projects/GCP/fe-papicella-8077fe1126b2.json

Note: You can also use an ENV variable as follows

export GOOGLE_APPLICATION_CREDENTIALS="[PATH]"

You can get a JSON key generated from the GCP console "IAM and Admin -> Service Accounts" page


For more information on authentication visit this link https://cloud.google.com/docs/authentication/getting-started



Categories: Fusion Middleware

Oracle E-Business Suite and Applications Unlimited Continued Oracle Investment—No Upgrades

Chris Warticki - Wed, 2019-02-06 13:13
Continued Oracle Investment for Years to Come

In June 2018, Oracle published the “Applications and Support—an Unbeatable Combination” announcement. This message re-affirmed Oracle’s commitment to the Applications Unlimited products with continuing development and support through at least 2030. The products covered by  Applications Unlimited are Oracle E-Business Suite, PeopleSoft, JD Edwards EnterpriseOne, and Siebel.

The June announcement committed Oracle E-Business Suite Development to move to the continuous innovation release model for the next major release (identified as 12.X), as explained in the Oracle E-Business Suite publication.

  Continuous Innovation Release Announced

In the subsequent October 2018 announcement, Oracle E-Business Suite announced that we will implement continuous innovation on the current 12.2 release. 

 

Significance to Oracle E-Business Suite Customers

This is important to Oracle E-Business Suite customers because the announcement:

  • Restates Oracle’s commitment to Oracle E-Business Suite.
  • Clarifies that the inter-dependency of the applications and technology stack has been uncoupled, and it will be possible to perform “tools only” updates (affecting the application layer), without being forced to update the technology stack at the same time, or conversely, tech stack updates without updating the applications.
  • Means it will be easier for customers to remain current—on the continuous innovation release—and benefit from the latest developments.
  • Removes the justification for 12.1.3 users to delay upgrading, as once they have migrated to 12.2, they can remain on that release for at least through 2030.
  • Sets the record straight—responding to/countering some of the misinformation and rumors, which have been circulated, stating that Oracle E-Business Suite will not be developed or supported.

 

Oracle Applications Unlimited

With continuous innovation now available for Oracle E-Business Suite 12.2, Oracle E-Business Suite joins the other Applications Unlimited products that also have pledged to provide continuous innovation and Oracle Premier Support through at least 2030 for their current continuous innovation releases.

 

 

Committed to Customers | Committed to Innovation | Committed to Products

 

Learn More

 

 

Replacements for OrdDoc

Tom Kyte - Wed, 2019-02-06 06:26
I have been using OrdDoc to store files and attachments for my applications. It looks like Oracle has deprecated this and will no longer support it in its next release (https://mikedietrichde.com/2018/08/10/oracle-multimedia-is-deprecated-in-oracle-1...
Categories: DBA Blogs

CDC on Views

Tom Kyte - Wed, 2019-02-06 06:26
Hi Tom, I have a view created daily on a detailed table set ( n number of tables based on the load) as below CDR_Detail_20190204_view CDR_Detail_20190205_view CDR_Detail_20190206_view To generate a report , i need to pick all the daily views(...
Categories: DBA Blogs

Can I force query to use the varchar index if the condition uses a number value

Tom Kyte - Wed, 2019-02-06 06:26
Hi, I've got the following sql statement: <code>SELECT C.APPLICATION_NUMBER, D.AGREEMENT_TYPE, C.SUPPLIER_REFERENCE FROM APPLICATION_DATA D, APPLICATION C WHERE C.APPLICATION_NUMBER =:b1 AND D.APPLICATION_ID = C.APPLICATION_ID;</code> ...
Categories: DBA Blogs

Windows Oracle11g to Linux Oracle 12c

Tom Kyte - Wed, 2019-02-06 06:26
Hi, hope you're doing great? I wonder if you can help me by giving me some advices or pointing me to some useful docs. In fact, we are planning to migrate a financial application with most of its business logic is written in Pl/SQL, from Windows Orac...
Categories: DBA Blogs

Performance degradation after database restoration

Tom Kyte - Wed, 2019-02-06 06:26
HI ALL, I HAVE A PROBLEM HERE WE are running an application(FOR REPORTING ONLY) with Oracle database 11.2, Data for this oracle database is getting from SQL server ( users are inserting data into SQL server first and finally with the help o...
Categories: DBA Blogs

Max number of processes

Tom Kyte - Wed, 2019-02-06 06:26
hi - we recently had a code change in our production database. Since then we see that every 2 days we have hundereds of inactive sessions in the database and maximum number of processes is reached. We increased this number a couple of times and every...
Categories: DBA Blogs

Storing JSON file in the database

Tom Kyte - Wed, 2019-02-06 06:26
I created a table with two JSON columns. CREATE TABLE USER.JSON_DOCS ( id RAW(16) NOT NULL, file CLOB, CONSTRAINT json_docs_pk PRIMARY KEY (id), CONSTRAINT json_docs_chk CHECK (file IS JSON) ); Then I tried to storage a JSON in...
Categories: DBA Blogs

View that can be used to get last SQL elapsed time

Tom Kyte - Wed, 2019-02-06 06:26
Hello Gentlemen, We currently don't have OEM tool. Is there a data dictionary view that can be queried that will provide the last elapsed time for a particular query that has an elapsed time of less than 5 seconds? v$sql view has a column called...
Categories: DBA Blogs

Create Databases from Automatic Daily Backups - New Feature - Jan 2019 - Oracle Cloud Infrastructure

Senthil Rajendran - Wed, 2019-02-06 03:03
New Feature - Create Databases from Automatic Daily Backups
Services : Block Volume
Release Month : Jan 2019

Automatic Daily Backup feature lets you to create automated backups of OCI DB System Databases on a daily basis. With this feature you can now create a new system out of the automated daily backups.

Here is a DB System in OCI that is created and you can see there is a Automated Backup Running













Once the backup is completed you will see that it is ready for the refresh





From the backup section select Create Backup and fill in the required details





Once you start the database creation process you will see the restore process beginning immediately.



The DB System will have your database in provisioning stage



Once restore is done you will be having your new Database up and running.



This is indeed a cool feature. I bet your DBA will love to see this , he can now start looking into other useful stuffs.



New OA Framework 12.2.7 Update 2 Now Available

Steven Chan - Tue, 2019-02-05 18:00

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure.

We periodically release updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.7 is now available:

Oracle Application Framework (FWK) Release 12.2.7 Bundle 2 (Patch 28963259:R12.FWK.C)

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.7 users should apply this patch. Future OAF patches for EBS Release 12.2.7 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch includes fixes for the following issues:

  • When the LOV search icon is clicked, the link ‘Skip navigation elements to page contents’ takes focus.
  • When adding attachments, an attachment title longer than 256 multibyte characters raises an error.

Related Articles

Categories: APPS Blogs

HP Color LaserJet MFP on Ubuntu: Scan Error during device I/O (code=9)

Dietrich Schroff - Tue, 2019-02-05 13:57
I decided to use a multi function printer including scanner and fax with my ubuntu systems.
First step was to download the hplip package from HP:
https://developers.hp.com/hp-linux-imaging-and-printing/gethplip

The installation process worked like a charme
bash ./hplip-3.19.1.run But running the scan utility ends up with the following error:


$ hp-scan

HP Linux Imaging and Printing System (ver. 3.19.1)
Scan Utility ver. 2.2

Copyright (c) 2001-15 HP Development Company, LP
This software comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to distribute it
under certain conditions. See COPYING file for more details.

warning: No destinations specified. Adding 'file' destination by default.
Using device hpaio:/net/HP_ColorLaserJet_MFP_M278-M281?ip=192.168.178.200
Opening connection to device...
error: SANE: Error during device I/O (code=9)After searching around the solution was the following:
"Install the plugin"?
!

$ hp-plugin





HP Linux Imaging and Printing System (ver. 3.19.1)
Plugin Download and Install Utility ver. 2.1

Copyright (c) 2001-15 HP Development Company, LP
This software comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to distribute it
under certain conditions. See COPYING file for more details.


HP Linux Imaging and Printing System (ver. 3.19.1)
Plugin Download and Install Utility ver. 2.1

Copyright (c) 2001-15 HP Development Company, LP
This software comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to distribute it
under certain conditions. See COPYING file for more details.

Checking for network connection...
Downloading plug-in from:
Plugin is not accessible. Trying to download it from fallback location: [https://developers.hp.com/sites/default/files/hplip-3.19.1-plugin.run]
Receiving digital keys: /usr/bin/gpg --homedir /home/esther/.hplip/.gnupg --no-permission-warning --keyserver pgp.mit.edu --recv-keys 0x4ABA2F66DBD5A95894910E0673D770CDA59047B9
Creating directory plugin_tmp
Verifying archive integrity... All good.
Uncompressing HPLIP 3.19.1 Plugin Self Extracting Archive..............................................................

HP Linux Imaging and Printing System (ver. 3.19.1)
Plugin Installer ver. 3.0

Copyright (c) 2001-15 HP Development Company, LP
This software comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to distribute it
under certain conditions. See COPYING file for more details.

Plug-in version: 3.19.1
Installed HPLIP version: 3.19.1
Number of files to install: 64


Done.
 Plug-in installation successful

Done.After that running hp-scan immediately creates a PNG file with the scan:
 hp-scan

HP Linux Imaging and Printing System (ver. 3.19.1)
Scan Utility ver. 2.2

Copyright (c) 2001-15 HP Development Company, LP
This software comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to distribute it
under certain conditions. See COPYING file for more details.

warning: No destinations specified. Adding 'file' destination by default.
Using device hpaio:/net/HP_ColorLaserJet_MFP_M278-M281?ip=192.168.178.200
Opening connection to device...

Resolution: 300dpi
Mode: gray
Compression: JPEG
Scan area (mm):
  Top left (x,y): (0.000000mm, 0.000000mm)
  Bottom right (x,y): (215.899994mm, 296.925995mm)
  Width: 215.899994mm
  Height: 296.925995mm
Destination(s): file
Output file:
warning: File destination enabled with no output file specified.
Setting output format to PNG for greyscale mode.
warning: Defaulting to '/home/esther/Downloads/hpscan001.png'.

Warming up...


Scanning...
Reading data: [*************************************************************************************************************************] 100%  8.5 MB    
Read 8.5 MB from scanner.
Closing device.

Outputting to destination 'file':

Done.This is really fast. But if you want a GUI just use xsane...

Keeping a column constant

Tom Kyte - Tue, 2019-02-05 12:26
Hi, <code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit</code> We have a need to keep certain values in a table constant in a cloned database. We store email addresses in a table, and use these for generating email commu...
Categories: DBA Blogs

Reintroducing Robert Farrington

Steven Chan - Tue, 2019-02-05 09:22

It is my pleasure to reintroduce you to Robert Farrington, a long-time member of the Oracle E-Business Suite Technology documentation team whose two main (and rather diverse, which he likes!) current areas of coverage are Oracle Application Framework and EBS on Oracle Cloud. In addition, he writes and edits many My Oracle Support knowledge documents. As an Oracle E-Business Suite customer, you have been reading Robert's work for many years.

In addition, Robert has been a key contributor to this blog and our sister Oracle E-Business Suite and Oracle Cloud blog as both editor and author for many years. I look forward to collaborating with Robert to continue to bring you our latest announcements and tips in a timely manner and easy to read but informative style.

Here are a few of Robert's recent blog posts:

Related Articles
Categories: APPS Blogs

What are custom and generic plans in PostgreSQL?

Yann Neuhaus - Tue, 2019-02-05 08:28

I have already written a post about prepared statements in PostgreSQL some time ago. What I did not mention in that post is the concept of generic and custom plans. So lets have a look at those.

As always, we start with creating a demo table and populate that table with some sample data:

pgbench=# create table demo ( a int, b text );
CREATE TABLE
pgbench=# insert into demo select i, 'aaa' from generate_series (1,100) i;
INSERT 0 100
pgbench=# insert into demo select i, 'bbb' from generate_series (101,200) i;
INSERT 0 100
pgbench=# insert into demo select i, 'ccc' from generate_series (201,300) i;
INSERT 0 100
pgbench=# analyze demo;
ANALYZE

Now that we have some data we can prepare a statement we would like to execute with various values:

pgbench=# prepare my_stmt as select * from demo where b = $1;
PREPARE

Btw: You can check for the currently available prepared statements in your session by querying the pg_prepared_statements catalog view:

pgbench=# select * from pg_prepared_statements;
  name   |                      statement                      |         prepare_time          | parameter_types | from_sql 
---------+-----------------------------------------------------+-------------------------------+-----------------+----------
 my_stmt | prepare my_stmt as select * from demo where b = $1; | 2019-02-05 13:15:39.232521+01 | {text}          | t

When we explain(analyze) that statement what do we see?

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.111..0.230 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning time: 0.344 ms
 Execution time: 0.285 ms
(5 rows)

In the “Filter” line of the execution plan we can see the real value (‘aaa’) we passed to our prepared statement. When you see that, it is a so called custom plan. When PostgreSQL goes for a custom plan that means the statement will be re-planned for the provided set of parameters. When you execute that a few times more:

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.045..0.158 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning time: 0.243 ms
 Execution time: 0.225 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.035..0.123 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning time: 0.416 ms
 Execution time: 0.173 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.036..0.124 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning time: 0.195 ms
 Execution time: 0.178 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.035..0.126 rows=100 loops=1)
   Filter: (b = 'aaa'::text)
   Rows Removed by Filter: 200
 Planning time: 0.192 ms
 Execution time: 0.224 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'aaa' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.038..0.130 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning time: 0.191 ms
 Execution time: 0.183 ms
(5 rows)

… you will see that the “Filter” line changes from displaying the actual parameter to a place holder. Now we have a generic plan. This generic plan will not change anymore for the lifetime of the prepared statement no matter which value you pass into the prepared statement:

pgbench=# explain (analyze) execute my_stmt ( 'bbb' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.096..0.219 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning time: 0.275 ms
 Execution time: 0.352 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'ccc' );
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.090..0.132 rows=100 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 200
 Planning time: 0.084 ms
 Execution time: 0.204 ms
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( null );
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.75 rows=100 width=8) (actual time=0.033..0.033 rows=0 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 300
 Planning time: 0.018 ms
 Execution time: 0.051 ms
(5 rows)

When you take a look at the source code (src/backend/utils/cache/plancache.c) you will see why it changes after 5 executions:

/*
 * choose_custom_plan: choose whether to use custom or generic plan
 *
 * This defines the policy followed by GetCachedPlan.
 */
static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{
        double          avg_custom_cost;

        /* One-shot plans will always be considered custom */
        if (plansource->is_oneshot)
                return true;

        /* Otherwise, never any point in a custom plan if there's no parameters */
        if (boundParams == NULL)
                return false;
        /* ... nor for transaction control statements */
        if (IsTransactionStmtPlan(plansource))
                return false;

        /* See if caller wants to force the decision */
        if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
                return false;
        if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
                return true;

        /* Generate custom plans until we have done at least 5 (arbitrary) */
        if (plansource->num_custom_plans < 5)
                return true;

Even if we change the data and analyze the table again we will still get a generic plan once PostgreSQL went for it:

pgbench=# insert into demo select i, 'ddd' from generate_series (201,210) i;
INSERT 0 10
pgbench=# insert into demo select i, 'ee' from generate_series (211,211) i;
INSERT 0 1
pgbench=# analyze demo;
ANALYZE
pgbench=# select b,count(*) from demo group by b order by b;
  b  | count 
-----+-------
 aaa |   100
 bbb |   100
 ccc |   100
 ddd |    10
 ee  |     1
(5 rows)

pgbench=# explain (analyze) execute my_stmt ( 'ddd' );
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on demo  (cost=0.00..5.88 rows=78 width=8) (actual time=0.147..0.151 rows=10 loops=1)
   Filter: (b = $1)
   Rows Removed by Filter: 300
 Planning time: 0.293 ms
 Execution time: 0.190 ms
(5 rows)

The situation changes when we have much more data, data is not uniformly distributed and we have an index on the column “b”:

pgbench=# truncate demo;
TRUNCATE TABLE
pgbench=# insert into demo select i, 'aaa' from generate_series (1,1000000) i;
INSERT 0 1000000
pgbench=# insert into demo select i, 'bbb' from generate_series (1000001,2000000) i;
INSERT 0 1000000
pgbench=# insert into demo select i, 'ccc' from generate_series (2000001,3000000) i;
INSERT 0 1000000
pgbench=# insert into demo select i, 'eee' from generate_series (3000001,3000010) i;
INSERT 0 10
pgbench=# create index i1 on demo (b);
CREATE INDEX
pgbench=# select b,count(*) from demo group by b order by b;
  b  |  count  
-----+---------
 aaa | 1000000
 bbb | 1000000
 ccc | 1000000
 eee |      10
(4 rows)

pgbench=# prepare my_stmt as select * from demo where b = $1;
PREPARE

No matter how often we execute the following statement (which asks for ‘eee’), we never get a generic plan:

pgbench=# explain (analyze) execute my_stmt ('eee');
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Index Scan using i1 on demo  (cost=0.43..4.45 rows=1 width=8) (actual time=0.054..0.061 rows=10 loops=1)
   Index Cond: (b = 'eee'::text)
 Planning time: 0.249 ms
 Execution time: 0.106 ms
(4 rows)

-----> REPEAT THAT HOW OFTEN YOU WANT BUT AT LEAST 10 TIMES

pgbench=# explain (analyze) execute my_stmt ('eee');
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Index Scan using i1 on demo  (cost=0.43..4.45 rows=1 width=8) (actual time=0.054..0.061 rows=10 loops=1)
   Index Cond: (b = 'eee'::text)
 Planning time: 0.249 ms
 Execution time: 0.106 ms

This is because the custom plan (which includes the costs for re-planning) is always cheaper than the generic plan (which does not include the costs for re-planning) when we have a data distribution like that. The documentation is very clear about that: “Using EXECUTE values which are rare in columns with many duplicates can generate custom plans that are so much cheaper than the generic plan, even after adding planning overhead, that the generic plan might never be used”.

Hope that helps.

Cet article What are custom and generic plans in PostgreSQL? est apparu en premier sur Blog dbi services.

Utilities Can Now Manage Diverse, Distributed Energy Resources with Oracle

Oracle Press Releases - Tue, 2019-02-05 07:00
Press Release
Utilities Can Now Manage Diverse, Distributed Energy Resources with Oracle Oracle Network Management, featuring built-in AI, continuously optimizes performance for a more resilient, efficient grid

DISTRIBUTECH, New Orleans, LA.—Feb 5, 2019

Energy distribution is no longer a linear equation. As new energy and data sources continue to grow, utilities must take a more holistic approach to better understand and manage resources and engage increasingly active customers at the edge of the grid. The expanded Oracle Utilities Network Management System (NMS) addresses this market need with a new Distributed Energy Resource Management (DERM) module that enables utilities to monitor situations in real-time and proactively optimize their broader network in concert with this explosion of emerging energy resources, including solar, wind, electric vehicles and more.

By eliminating data and application silos and giving operators real-time visibility and control across all grid and pipeline assets in a single platform, operators can leverage the most comprehensive, real-time model to ensure a more resilient and efficient grid. For example, applying built-in artificial intelligence (AI) and machine learning to a growing library of data, including Advanced Metering infrastructure (AMI), weather forecasts, SCADA and IoT device interaction, NMS enables grid operators to reliably predict future storms and alter supply and demand. Coupled with enhanced mobile tools, field crews can quickly garner the insights they need to limit the impact of outages for customers and speed restoration efforts.

“Utilities today are facing a perfect storm of evolving distributed energy resources across their networks, a barrage of data, and ever-savvy customers who are looking to their utility to be a trusted advisor in this changing energy journey,” said Dan Byrnes, SVP of product development, Oracle Utilities. “As such, resources can no longer be managed in isolation. With the latest innovations in NMS, utilities can improve long-term business planning around infrastructure investments and maintenance schedules, launch new revenue-generating customer-focused energy services, and proactively optimize operational performance.”

End-to-End Visibility and Control Across Resources

With the new enhancements to Oracle Utilities Network Management System, utilities reap the benefits of:

  • Automated Grid Optimization: Offers complete visibility and control from distribution to the connected customer including distributed energy resource management, D-SCADA functionality, and IoT device interactions. This allows utilities to balance supply and demand all in one place, in both real-time and continuously looking ahead to proactively mitigate grid constraints before they happen.
  • Deep Insights for Automated Decision Making: By ingesting data from a variety of sources and harnessing the power of AI and analytics, utilities can understand past weather patterns and predict future outages for automated decision making.
  • Extended Field Crew Access: Leveraging existing mobile device capabilities, field crews can now pull up outage maps, trend data and performance analysis, even in offline mode for faster, more efficient outage resolution.
  • Improved User Experience: Intuitive order-based workflows, simplified tab-based windows, and the ability to have multiple events open simultaneously deliver an enriched user experience with no compromise to safety, reliability and performance.
 

Oracle Utilities NMS combines market-leading outage management technology with distribution management and advanced analytics to empower utilities to maximize grid operation capabilities, shorten outage duration and optimize distribution, all while keeping their customers engaged and informed. To learn more, see the new Oracle NMS system in action today at DistribuTECH booth #10315 or visit here.

Contact Info
Kristin Reeves
Oracle
+1.925.787.6744
kris.reeves@oracle.com
Molly Hardy-Knowles
Hill+Knowlton Strategies
+1.713.752.1931
molly.hardy-knowles@hkstrategies.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.

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

Kristin Reeves

  • +1.925.787.6744

Molly Hardy-Knowles

  • +1.713.752.1931

Pages

Subscribe to Oracle FAQ aggregator