Oracle Database 19c APPROX_COUNT_DISTINCT Vs COUNT(DISTINCT exp)

APPROX_COUNT_DISTINCT es una función que aparece por primera vez en la versión Oracle Database 12c Release 1 ( 12.1.0 ) que es capaz de devolver el número aproximado de filas que contienen valores distintos de expresión.

Esta función proporciona una alternativa a la función COUNT (DISTINCT expresión), que devuelve el número exacto de filas que contienen valores distintos de la expresión indicada.

APPROX_COUNT_DISTINCT procesa grandes cantidades de datos significativamente más rápido que COUNT, con una desviación insignificante del resultado exacto.

Para la expresión, puede especificar una columna de cualquier tipo de datos escalar que no sea BFILE, BLOB, CLOB, LONG, LONG RAW o NCLOB.

APPROX_COUNT_DISTINCT ignora las filas que contienen un valor nulo para expresión al igual que la función COUNT().

A continuación podrás encontrar un pequeño laboratorio de como funciona la función en la más reciente versión de base de datos 19c.

A manera de inicio de lectura, la función APPROX_COUNT_DISTINCT es más rápida en cualquiera de las situaciones evaluadas.

Con los datos en memoria, es 3 centésimas más rápido. Pero sin los datos en memoria es casi un 80% más eficiente que contar las diferencias con la función COUNT( DISTINCT ).

Al examinar el plan de ejecución de las sentencias utilizando dichas funciones, nos damos cuenta que en el caso de la función APPROX_COUNT_DISTINCT ocupa menos CPU que COUNT y adicionalmente le basta con el FTS sobre la tabla para realizar el conteo de los valores distintos.

Mientras que en el plan de ejecución de COUNT(DISTINCT ) encontramos que la base de datos primero genera un vista dinámica con los valores obtenidos del DISTINCT para luego determinar el número de elementos.

SQL>  select BANNER_FULL from v$version;

BANNER_FULL
-----------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0


SQL> create user testing_user identified by oracle;

User created.

SQL> grant create session to testing_user;

Grant succeeded.

SQL> grant create table to testing_user;

Grant succeeded.

SQL> alter user testing_user quota unlimited on users;

User altered.

SQL> connect sys@pdb as sysdba
Enter password:
Connected.
SQL> grant select on dba_objects to testing_user;

Grant succeeded.

SQL> connect testing_user/oracle@pdb
Connected.
SQL> create table objetos as select object_name, owner, object_type from dba_objects;

Table created.

SQL> select count(*) from objetos;

  COUNT(*)
----------
     72359

SQL> select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

SQL> set timing on
SQL>  select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

Elapsed: 00:00:00.17
SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

Elapsed: 00:00:00.20
SQL> connect sys@pdb as sysdba
Enter password:
Connected.

Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:02.30
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:02.51
SQL> connect testing_user/oracle@pdb
Connected.
SQL> select APPROX_COUNT_DISTINCT(owner) from objetos;

APPROX_COUNT_DISTINCT(OWNER)
----------------------------
                          24

Elapsed: 00:00:00.70
SQL> select count(distinct owner) from objetos;

COUNT(DISTINCTOWNER)
--------------------
                  24

Elapsed: 00:00:01.29
SQL> explain plan for
  2  select APPROX_COUNT_DISTINCT(owner) from objetos;

Explained.

Elapsed: 00:00:00.38
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1430740140
--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |   153   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE APPROX|         |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL   | OBJETOS | 72359 |   353K|   153   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

Elapsed: 00:00:03.71
SQL> explain plan for
  2  select count(distinct owner) from objetos;

Explained.

Elapsed: 00:00:00.10
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 276252159
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    66 |   155   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    66 |            |          |
|   2 |   VIEW               | VW_DAG_0 |    24 |  1584 |   155   (2)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |    24 |   120 |   155   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| OBJETOS  | 72359 |   353K|   153   (1)| 00:00:01 |
---------------------------------------------------------------------------------

11 rows selected.

Elapsed: 00:00:00.17
SQL>

SQL> create table diferencias ( datos varchar2(20));

Table created.

Elapsed: 00:00:01.26
SQL> insert into diferencias values ('&valor');
Enter value for valor: COSTA RICA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('COSTA RICA')

1 row created.

Elapsed: 00:00:00.29
SQL> /
Enter value for valor: GUATEMALA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('GUATEMALA')

1 row created.

Elapsed: 00:00:00.02
SQL> /
Enter value for valor: PANAMA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('PANAMA')

1 row created.

Elapsed: 00:00:00.12
SQL> /
Enter value for valor: COLOMBIA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('COLOMBIA')

1 row created.

Elapsed: 00:00:00.02
SQL> /
Enter value for valor: ARGENTINA
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('ARGENTINA')

1 row created.

Elapsed: 00:00:00.04
SQL> /
Enter value for valor: PARAGUAY
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('PARAGUAY')

1 row created.

Elapsed: 00:00:00.10
SQL> /
Enter value for valor: ECUADOR
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('ECUADOR')

1 row created.

Elapsed: 00:00:00.04
SQL> /
Enter value for valor: MEXICO
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('MEXICO')

1 row created.

Elapsed: 00:00:00.51
SQL> /
Enter value for valor:
old   1: insert into diferencias values ('&valor')
new   1: insert into diferencias values ('')

1 row created.

Elapsed: 00:00:00.63
SQL> commit;

Commit complete.

Elapsed: 00:00:00.06
SQL> select count(*) from diferencias;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.06
SQL> select * from diferencias;

DATOS
--------------------
COSTA RICA
GUATEMALA
PANAMA
COLOMBIA
ARGENTINA
PARAGUAY
ECUADOR
MEXICO


9 rows selected.

Elapsed: 00:00:00.36
SQL> select count(distinct datos ) from diferencias;

COUNT(DISTINCTDATOS)
--------------------
                   8

Elapsed: 00:00:00.11
SQL> select APPROX_COUNT_DISTINCT(datos) from diferencias;

APPROX_COUNT_DISTINCT(DATOS)
----------------------------
                           8

Elapsed: 00:00:00.06
SQL> select count(*) from diferencias;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.03

SQL>

Oracle Linux 7.6 y Oracle Database 19c muy muy lento en VirtualBox con error abrt-cli status timed out

Hola gente, he estado preparando mis laboratorios para mis posibles presentaciones en el GroundBreaker Tour LATAM 2019 utilizando VirtualBox 5.2x

La instalación del Oracle Linux fue relativamente rápida, pero la instalación del Oracle Database 19c utilizando el paquete RPM, fue bastante dolorosa. Algo que realmente no me esperaba.

Ese día simplemente apagué la máquina virtual y me retiré con alguna desazón.

Hoy he tenido un poco más de tiempo y volví a arrancar la VM y validar el motivo de dicha lentitud.

Vaya, me he dado cuenta, después de casi 20 minutos levantando el LISTERNER y el contenedor de la base de datos, que no tengo memoria disponible. He definido mi VM con 8GB de RAM y 4 hilos de procesamiento.

[root@lab2 ~]# uname -a
Linux lab2.oracle.com 4.14.35-1844.4.5.el7uek.x86_64 #2 SMP Tue Apr 9 00:29:47 PDT 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@lab2 ~]#

Entonces me dí la tarea de buscar la causa de dicho desastre.

Sorpresa, OL 7.6 genera algunos problemas con mi HW. ( En instantes les explico esta parte ). He recibido el siguiente mensaje cuando tan sólo deseaba cambiarme al superusuario del sistema operativo.

[oracle@lab2 ~]$ su - root

Password:

Last login: Sun May  5 14:57:01 CST 2019 on pts/1

'abrt-cli status' timed out

He procedido a limpiar el cache de memoria del sistema operativo.

[root@lab2 ~]#

[root@lab2 ~]# sync; echo 3 > /proc/sys/vm/drop_caches

Ahora reviso nuevamente y he logrado recuperar una gran cantidad de mi memoria física.

[root@lab2 ~]# top

top - 14:34:20 up 47 min,  3 users,  load average: 7.02, 15.90, 14.72

Tasks: 311 total,   3 running, 228 sleeping,   0 stopped,   0 zombie

%Cpu(s): 14.2 us, 19.5 sy,  4.7 ni,  4.8 id, 46.2 wa,  0.0 hi, 10.7 si,  0.0 st

KiB Mem :  8158724 total,  5885004 free,  1130388 used,  1143332 buff/cache

KiB Swap:  8257532 total,  8257532 free,        0 used.  6261344 avail Mem

Aún así, la VM aún esta lenta, pero ahora tengo suficiente memoria física disponible. Validando los procesos ejecutándose a nivel de sistema operativo, me encuentro un proceso con el mismo nombre del error que obtuve cuando estaba haciendo el comando "su".

Si efectivamente al "googlelear" dicho proceso, me encuentro varias notificaciones de BUGs operativos en distintos distros de linux.

Proceso a matar el proceso seguidamente.

[root@lab2 ~]# kill -9 3225

[root@lab2 ~]# ps -ef|grep watch

root        11     2  0 13:46 ?        00:00:00 [watchdog/0]

root        14     2  0 13:46 ?        00:00:00 [watchdog/1]

root        20     2  0 13:46 ?        00:00:00 [watchdog/2]

root        26     2  0 13:46 ?        00:00:00 [watchdog/3]

root        47     2  0 13:46 ?        00:00:00 [watchdogd]

root      3228     1  0 13:51 ?        00:00:01 /usr/bin/abrt-watch-log -F Backt

root      9463  9222 37 14:37 pts/1    00:00:00 grep --color=auto watch

Luego he encontrado dentro de la documentación de Red Hat y Fedora, que estos 4 servicios a continuación, deben ser deshabilitados para evitar el problema.

[root@lab2 ~]# service abrt stop

[root@lab2 ~]# service abrt-ccpp stop

Redirecting to /bin/systemctl stop abrt-ccpp.service

[root@lab2 ~]# chkconfig abrt-ccpp off

Note: Forwarding request to 'systemctl disable abrt-ccpp.service'.

Removed symlink /etc/systemd/system/multi-user.target.wants/abrt-ccpp.service.

[root@lab2 ~]# service abrtd stop

Redirecting to /bin/systemctl stop abrtd.service

[root@lab2 ~]# chkconfig abrtd off

Note: Forwarding request to 'systemctl disable abrtd.service'.

Removed symlink /etc/systemd/system/multi-user.target.wants/abrtd.service.

[root@lab2 ~]# service abrt-oops.service stop

Redirecting to /bin/systemctl stop abrt-oops.service

[root@lab2 ~]# chkconfig abrt-oops off

Note: Forwarding request to 'systemctl disable abrt-oops.service'.

Removed symlink /etc/systemd/system/multi-user.target.wants/abrt-oops.service.

[root@lab2 ~]# service abrt-vmcore stop

Redirecting to /bin/systemctl stop abrt-vmcore.service

[root@lab2 ~]# chkconfig abrt-vmcore off

Note: Forwarding request to 'systemctl disable abrt-vmcore.service'.

Removed symlink /etc/systemd/system/multi-user.target.wants/abrt-vmcore.service.

[root@lab2 ~]#

Ahora sí, después de varios minutos, el comportamiento normal a nivel de rendimiento se mantiene en mi máquina virtual y puedo sin problemas continuar con mis laboratorios.

Oracle Hot Topics: ORA-600 KOCDSFR Sporadic error ultimately results in instance crash

Bugs

Bug	Product Area	Bug ID	Last Updated
ORA-600 [KOCDSFR], ORU-10027 WITH KSS_GET_*: BAD CONTROL SECONDARY ERRORS	Oracle Database - Enterprise Edition	29779817	Wed, 22 May 2019 08:33 GMT-06:00

Oracle Hot Topics Requirements for Installing Oracle Database 18c on OL7 or RHEL7 64-bit (x86-64)

Bugs

Bug	Product Area	Bug ID	Last Updated
XTT TABLESPACE PLUGIN FAILS WITH ORA-1647: TABLESPACE X IS READ-ONLY	Oracle Database - Enterprise Edition	17565514	Thu, 23 May 2019 04:31 GMT-06:00

Knowledge Articles

Knowledge Article	Product Area	Last Updated
Requirements for Installing Oracle Database 18c on OL7 or RHEL7 64-bit (x86-64)	Oracle Database Exadata Cloud Machine Oracle Database Exadata Express Cloud Service Oracle Database Cloud Exadata Service Oracle Database Cloud Service Oracle Cloud Infrastructure - Database Service Oracle Database - Standard Edition Oracle Database Cloud Schema Service Oracle Database - Enterprise Edition Oracle Database Backup Service	Wed, 22 May 2019 13:02 GMT-06:00

Support for Oracle Java SE now Included with Oracle Cloud Infrastructure

By Sergio Leunissen

Today we are excited to announce that support for Oracle Java, Oracle’s widely adopted and proven Java Development Kit, is now included with Oracle Cloud Infrastructure subscriptions at no extra cost. This includes the ability to log bugs, to get regular stability, performance, and security updates, and more for Oracle Java 8, 11, and 12. With Oracle Java you can develop portable, high-performance applications for the widest range of computing platforms possible, including all of the major public cloud services. By making Oracle Java available as part of any Oracle Cloud Infrastructure subscription, we are dramatically reducing the time and cost to develop enterprise and consumer applications.

This is an important announcement as Java is the #1 programming language and #1 developer choice for the cloud. It’s used widely for embedded applications, games, web content, and enterprise software. 12 million developers run Java worldwide and its usability is growing as options for cloud deployment of Java increase.

Oracle Java in Oracle Cloud helps developers write more secure applications, with convenient access to updates and a single vendor for support – for cloud and Oracle Java use – same subscription, no additional cost. We also ensure that you will have signed software from Oracle and the latest stability, performance, and security updates addressing critical vulnerabilities. 

All of this is supported on Oracle Linux and on other operating systems you run in your Oracle Cloud Infrastructure Virtual Machine or Bare Metal instance. Microsoft Windows? Of course. Ubuntu? Yep. Red Hat Enterprise Linux? Sure!

Easy Peasy Cloud Developer Image

How can you get the Oracle Java bits? They are a breeze to install on Oracle Linux using Oracle Cloud Infrastructure yum repositories. But with the Oracle Cloud Developer Image available in the Oracle Cloud Marketplace, it’s even easier to get started. Simply click to launch the image on an Oracle Cloud Infrastructure compute instance. The Oracle Cloud Developer Image is a Swiss army knife for developers that includes Oracle Java and a whole bunch of other valuable tools to accelerate development of your next project. You can have this image installed and ready to go within minutes.

Get started with the Oracle Cloud Developer Image

The ‘Unprecedented Challenge’ of Cybersecurity in an Age of Burgeoning Threats

Barbara Darrow, Senior Director, Communications, Oracle—May 20, 2019 

Technological and legal complexities abound in this age of heightened cybersecurity threats—including a rise in state-sponsored hacking. This “unprecedented challenge” was the topic of conversation between Dorian Daley, Oracle executive vice president and general counsel, and Edward Screven, Oracle’s chief corporate architect. Here are five key takeaways from their conversation: 

1. Some good news: Businesses are aware of cybersecurity challenges in a way they were not even just a few years ago, when many considered security, generally, as a priority, but didn’t go much beyond that thought according to Screven. “It’s [become] a front-and-center kind of issue for our customers,” Daley agreed.
2. These same customers would like to make data security “someone else’s problem,” and are right to think that way, Screven added. In this context, that “someone else” is a tech vendor able to design technology that is inherently more secure than what non-tech businesses could design for themselves.
3. Regulations around data privacy are getting more complicated, starting with the European Union’s General Data Protection Regulation, Daley noted. The issues of data privacy and data security constitute slightly different sides to the same problem, she said, adding “what’s happening on the privacy side is really an explosion of regulatory frameworks around the world.”
4. There’s only so much that employees can do—no matter how skilled they may be. Recent research shows that while most companies cite human error as a leading cause of data insecurity, they also keep throwing more people at a problem that can’t really be solved without a level of automation commensurate with the sophistication and volume of attacks. “There is a lack of sufficient awareness about what technology can actually do for customers,” Daley noted. 

Fast, “autonomous” or self-applying software patches and updates are a solid way to mitigate or even prevent data loss from cyber hacks. Many of the attacks and subsequent data leaks over the past few years could have been avoided had available software patches been applied in a timely fashion. 

AI and machine learning tech can catch far more anomalies, like unauthorized system access, which might indicate a security problem much faster than human experts can, eliminating issues before they get serious. 

5. Screven is skeptical that international treaties, if such things could be crafted, would eradicate state-sponsored cyber hacking because much of that activity happens under the covers by contractors that can be disavowed by the states. 

Thus, “the same person who’s out stealing your credit card today is out trying to steal plans for [Hellfire] missiles tomorrow,” Screven said. 

Full video of the talk can be found here. 

Latest Blog Posts from Oracle ACEs: April 14-20, 2019

By Bob Rhubart

In writing the blog posts listed below, the endgame for the Oracle ACE program members is simple: sharing their experience and expertise with the community. That doesn't make them superheroes, but you have to marvel at their willingness to devote time and energy to helping others.

Here's what they used their powers to produce for the week of April 14-20, 2019.

Francisco Munoz Alvarez
CEO, CloudDB
Sydney, Australia

• Playing with Oracle Database Security Assessment Tool (DBSAT)

Guoqiang Gai
CEO, Enmo Tech
China

• No way to escape: Oracle 12.2 BigSCN new features possible DB Link compatibility issues

Ludovico Caldara
Computing Engineer, CERN
Nyon, Switzerland

• First draft of a Common Oracle Environment… for the Cloud Database (and not only)

Martin D'Souza
Director of Innovation, Insum Solutions
Alberta, Canada

• Validating Dates and Numbers in Oracle 12.2

Opal Alapat
Vision Team Practice Lead, interRel Consulting
Arlington, Texas

• Collaborate19: Sizzling San Antonio!

Syed Jaffar Hussain
CTO, eProseed
Riyadh, Saudi Arabia

• What's in Exadata X8 Server 19.2 Exadata Database Machine?

Alfredo Krieg
Senior Principal Consultant, Viscosity North America
Dallas, Texas

• Oracle Autonomous Data Warehouse – What is it?

Marco Mischke
Team Lead, Database Projects, Robotron Datenbank-Software GmbH
Dresden, Germany

• Adding VLANs on an ODA

Noriyoshi Shinoda
Database Consultant, Hewlett Packard Enterprise Japan
Tokyo, Japan

• Try the Multithreaded Oracle Database Process Model (Oracle Database 12c)
• PDBのクローンを試す（Oracle Database 18c)

Patrick Jolliffe
Manager, Li & Fung Limited
Hong Kong

• mysql_clear_password & authentication_ldap_simple password encryption

Phil Wilkins
Senior Consultant, Capgemini
Reading, United Kingdom

• Microservice & APIs Exploiting HTTP Response Codes

Zaheer Syed
Oracle Application Specialist, Tabadul
Riyadh, Saudi Arabia

• Oracle Database pre-Installation package on Solaris 11.4

Talip Hakan Ozturk
Co-Founder, Veridata Information Technologies
Istanbul, Turkey

• Creating the 11.2 OEM (with Oracle Flex) with emca Fails with Message: WARNING: Error determining the nodes associated with cluster database for ASM
• Shut Down, Reset, or Power Cycle the Oracle MiniCluster S7-2 Engineered System

Batmunkh Moltov
Chief Technology Officer, Global Data Engineering Co.
Ulaanbaatar, Mongolia

• Oracle Database autostart REDHAT 7/CentOS 7/Oracle Linux 7

Flora Barriele
Oracle Database Administrator, Etat de Vaud
Lausanne, Switzerland

• Exadata Cloud at Customer : Grid Infrastructure patch under the hood

Related Resources

• Latest Blog Posts from Oracle ACEs: April 7-13
• Latest Blog Posts from Oracle ACEs: March 31 - April 6, 2019

Oracle ACE Program: A High-Five for New Members and Category Climbers