Tuesday, July 22, 2014

Oracle Database 12.1.0.2 is out

Many interesting new features and improvements:
  • Oracle Database In-Memory
  • Oracle Big Data SQL
  • Oracle JSON Document Store
  • Oracle REST Data Services
  • Improvements to Oracle Multitenant
  • Advanced Index Compression
  • Zone Maps
  • Approximate Count Distinct
  • Attribute Clustering
  • Full Database Caching
  • Rapid Home Provisioning
Available on edelivery at edelivery.oracle.com


Friday, May 30, 2014

How to discover new PDB targets in EM12c

I just created a new Pluggable database in my container, PDB2. I want to discover it in EM12c Rel 3.

Go to Setup -> Add Target -> Add Targets Manually





Choose "Add Targets Using Guided Process", for Targets type choose Oracle Database, Listener and ASM




Specify host, then click next





Input the pass and then Next


Review the findings and click save


The confirmation window appears


Going to Targets -> Databases, you can find the pluggable database in the list.





Thursday, May 8, 2014

EM12c: Job Step Scheduler is shown as down – EM jobs remain in scheduling indefinitely

Problem:
EM12c: Job Step Scheduler is shown as down – EM jobs remain in scheduling indefinitely and don't run

Change made:
Upgrade to Rel 3 12.1.0.3 and apply DB Plugin 12.1.0.5


The repository job called “Job Step Scheduler” is shown as down on Management Services and Repository Page.

Go to Setup > Manage Cloud Control > Repository

In Repository Scheduler Jobs Status section, the "Job Step Scheduler" job is shown as down with a red arrow.

All the other jobs look good and are running successfully.


So, found this metalink note, and applied the same logic
EM 12c : After Upgrade to R2 12.1.0.2 Repository Job Purge Is Shown As Down on Management Services And Repository Page (Doc ID 1496431.1)

SQL>  select dbms_jobname from mgmt_performance_names where display_name = 'Job Step Scheduler';
 EM_JOB_OBSERVER.run_cycle

SQL>  select job from all_jobs where what='EM_JOB_OBSERVER.run_cycle'
 no rows selected

 SQL>  select count(*) from all_jobs
 0

SQL> select owner, job_name, job_action, enabled, state, failure_count, last_start_date, last_run_duration
from dba_scheduler_jobs where job_name LIKE 'EM_JOB%'

SYSMAN              EM_JOB_PURGE_POLICIES         EM_JOB_PURGE.APPLY_PURGE_POLICIES();      TRUE     SCHEDULED        0                08-MAY-14 05.07.11.883712000 AM -05:00            0 0:0:17.949089
SYSMAN              EM_JOBS_STEP_SCHED                EM_JOB_OBSERVER.run_cycle  FALSE    DISABLED            0              24-JAN-14 06.29.08.570318000 PM EST5EDT               0 0:0:0.013817

I can see the job is disabled.
I will enable it

SQL>  BEGIN
      sys.dbms_scheduler.enable(name=>'"SYSMAN"."EM_JOBS_STEP_SCHED"');
END;

SQL>  select owner, job_name, job_action, enabled, state, failure_count, last_start_date, last_run_duration
from dba_scheduler_jobs where job_name LIKE 'EM_JOB%'

SYSMAN              EM_JOBS_STEP_SCHED                EM_JOB_OBSERVER.run_cycle  TRUE     SCHEDULED        0              24-JAN-14 06.29.08.570318000 PM EST5EDT               0 0:0:0.013817
SYSMAN              EM_JOB_PURGE_POLICIES         EM_JOB_PURGE.APPLY_PURGE_POLICIES();      TRUE     SCHEDULED        0                08-MAY-14 05.07.11.883712000 AM -05:00            0 0:0:17.949089

The job is now scheduled.

Will also update mgmt._performance_names

SQL>  UPDATE MGMT_PERFORMANCE_NAMES
 SET display_name = NULL,dbms_jobname = NULL, is_dbmsjob = 'N', is_deleted = 'Y'
 WHERE UPPER(DBMS_JOBNAME) LIKE 'EM_JOB_OBSERVER.RUN_CYCLE';
1 row updated
SQL>  COMMIT;


Now checking the status of the job shows to be up.



The jobs run successfully again.



References
EM 12c : After Upgrade to R2 12.1.0.2 Repository Job Purge Is Shown As Down on Management Services And Repository Page (Doc ID 1496431.1)

EM 12c: The EM Jobs Service Target is Shown with Down Status in the Enterprise Manager 12.1.0.2 Cloud Control Console (Doc ID 1589084.1)



Monday, May 5, 2014

Compliance policies - DBLM

Recently had to retrieve the list of all Compliance Standard Rules in EM12c Rel 3.
I had to build a script to do this as Compliance Standard Rules cannot be exported within the Cloud Control interface (or at least was not able to do it after selecting all rules).

The script I come up with is the following:

SELECT rule_dname,description,rationale,
       target_type,
       decode(SEVERITY,20,'Warning',25,'Critical',18,'Minor Warning') as Severity
FROM EM_RULE
WHERE target_type in ('oracle_database','oracle_listener','rac_database','oracle_pdb','oracle_dbmachine')
order by target_type, rule_dname;

A sample of the output (unformatted) looks like the below:

RULE_NAME
"Domain Users" Group Member of local "Users" Group
$ORACLE_HOME/network/admin Directory Owner
$ORACLE_HOME/network/admin File Permission
$ORACLE_HOME/network/admin File Permission(Windows)
Access to *_CATALOG_* Roles
Access to ALL_SOURCE View

DESCRIPTION
Ensures domain server local Users group does not have Domain Users group
Ensures $ORACLE_HOME/network/admin ownership is restricted to the Oracle software set and DBA group
Ensures the files in $ORACLE_HOME/network/admin ownership is restricted to the Oracle software set, group is restricted to DBA group and  Public does not have write permission
Ensures the files in $ORACLE_HOME/network/admin ownership is restricted to the Oracle software set, group is restricted to DBA group and  Public does not have write permission
Ensure grant of *_CATALOG_* is restricted
Ensures restricted access to ALL_SOURCE view

RATIONALE
Including Domain Users group in local Users group of a domain server can cause serious security issues.
Not restricting ownership of network/admin to the Oracle software set and DBA group may cause security issues by exposing net configuration data to malicious users
Not restricting ownership of network/admin to the Oracle software set and DBA group may cause security issues by exposing net configuration data to malicious users
Not restricting ownership of network/admin to the Oracle software set and DBA group may cause security issues by exposing net configuration data to malicious users
TARGET_TYPE SEVERITY
oracle_database Warning
oracle_database Warning
oracle_database Warning
oracle_database Warning
oracle_database Critical

Friday, January 24, 2014

Predicate N = N-1 returns rows?

Following a tweet from  "Quiz Q for the day: "select * from T where N = N - 1;" returns rows - how?", I did a small setup to try to answer the question.

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--number

SQL> drop table tt
  2  ;

Table dropped.

SQL> create table tt as select 1/0f N from dual;

Table created.

SQL> desc tt;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 N                                                  BINARY_FLOAT

SQL> select * from tt where N=N-1;

         N
----------
       Inf

SQL>

Tuesday, January 7, 2014

EM12c - Deploy the Latest Database Plugin to the OMS in offline mode

As of the date this was written, the latest EM12c database plugin is 12.1.0.5.0.

If you are a fan of command line utilities over the GUI interfaces (like me), you will love emcli which will help accomplish many operations in EM12c.

The steps would be:
1.       Aquire the latest database plug-in software using Self Update
2.       Deploy the plugin on the management server aka OMS

We will now synchronize emcli with the OMS
[oracle@em12c bin]$ ./emcli sync
Error: Session expired. Run emcli login to establish a session.

Let’s check the status:

[oracle@em12c bin]$ ./emcli status
Oracle Enterprise Manager 12c EMCLI12.1.0.3.0.
Copyright (c) 1996, 2013 Oracle Corporation and/or its affiliates. All rights reserved.

Instance Home          : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli
Verb Jars Home         : null
Status                 : Configured
EMCLI Home             : /u01/app/oracle/Middleware2/oms/bin/.
EMCLI Version          : 12.1.0.3.0
Java Home              : /u01/app/oracle/Middleware2/jdk16/jdk/jre
Java Version           : 1.6.0_43
Log file               : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/.emcli.log
EM URL                 : https://em12c.localdomain:7801/em
EM user                : SYSMAN
Auto login             : false
Trust all certificates : true

[oracle@em12c bin]$ ./emcli login -username=SYSMAN -password=***
Login successful


[oracle@em12c bin]$ ./emcli sync
Synchronized successfully

1)      Download the latest updates catalog in offline mode
Login to EM12c -> Setup -> Extensibility -> Self Update
In the page, select Plugins and Click “Check Updates”

A popup with the link to download will appear:
https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

Now we need to import the master catalog with is: Patch 9348486: master catalog

[oracle@em12c bin]$ ./emcli import_update_catalog -file=/home/oracle/p9348486_112000_Generic.zip -omslocal
Processing catalog for Middleware Profiles and Gold Images
Processing catalog for Agent Software
Processing catalog for Management Connector
Processing catalog for Extensibility Development Kit
Processing update: Extensibility Development Kit - 'Extensibility Development Kit (EDK) is a standalone tool designed to help development of Enterprise Manager extensions (For example: Plug-ins)'
Processing catalog for Plug-in
Processing update: Plug-in - Enterprise Manager for Oracle Utilities provides management and monitoring for the Oracle Utilities area.
Processing update: Plug-in - Enterprise Manager for Oracle Enterprise Taxation and Policy Management provides management and monitoring for the Oracle Enterprise Taxation and Policy Management area.
Processing update: Plug-in - Enterprise Manager for Oracle Exadata provides comprehensive management for Oracle Exadata and related targets such as Database Machine,  etc.
Processing update: Plug-in - Enables cloud self service framework and portal in Oracle Enterprise Manager.
Processing update: Plug-in -
        Enterprise Manager Storage Management Framework provides Snap Clone functionality by leveraging the snapshot and Copy-on-Write (CoW) features of the underlying storage.
 
Processing update: Plug-in - Enterprise Manager for Oracle Siebel consists of monitoring and management functionalities for Oracle Siebel.
Processing update: Plug-in - Enterprise Manager for Fusion Apps consists of monitoring and management for Oracle Fusion and diagnostics in fusion application area.
Processing update: Plug-in - Enterprise Manager for Oracle Consolidation Planning and Chargeback provides metering, chargeback and consolidation planning for various Enterprise Manager targets.
Processing update: Plug-in - Enterprise Manager for Fusion Middleware consists of monitoring and management for Oracle Fusion Middleware and diagnostics in middleware area.
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Processing update: Plug-in - Enterprise Manager for Big Data Appliance  provides comprehensive management for Oracle Big Data Appliance and related targets of Hadoop
Processing update: Plug-in - Enterprise Management Plug-in for Oracle ZFS Storage Appliances
Processing update: Plug-in - Enterprise Manager for Oracle Instant Messaging Server is used for monitoring/managing the Oracle Instant Messaging Server
Processing update: Plug-in - Enterprise Manager for Oracle Virtual Networking
Processing update: Plug-in - Enterprise Manager for Ops Center Infrastructure stack
Processing update: Plug-in - Enterprise Manager for Engineered System Healthcheck provides proactive healthcheck alerts for Oracle Engineered Systems
Processing update: Plug-in - Enterprise Manager for Oracle Audit Vault and Database Firewall (AVDF) provides monitoring and management of AVDF system.
Processing update: Plug-in - Enterprise Manager for Oracle Communications enables management of Oracle Communications products
Processing update: Plug-in - Oracle Application Management Pack for Oracle E-Business Suite consists of System Management and Change Management Feature Sets
Processing catalog for Test Data Management templates
Processing catalog for Oracle Virtual Templates and Oracle Virtual Assemblies
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Functional Version 12.3.0.1.400
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Version 12.3.0.1.376
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Agent Version 12.3.0.1.376
Processing catalog for Informational
Processing catalog for Provisioning Bundle

Successfully uploaded the Self Update catalog to Enterprise Manager. Use the Self Update Console to view and manage updates.
[oracle@em12c bin]$

Now Click on Plug-In Updates -> choose Oracle Database (it will show Available) -> Download
You will be prompted with a message like the below.


You shall be downloading it from the URL provided.
Now you can import it to Enterprise Manager using the below command:

 [oracle@em12c bin]$ ./emcli import_update -omslocal -file=/home/oracle/p17628065_112000_Generic.zip
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Let’s now check the existing plugins:

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.4.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0




2)      Deployment
Login to EM12c -> Setup -> Extensibility -> Go to Plugins

Expand the option Databases -> Oracle Databases -> Deploy on -> Choose Management Server
 Input the password for SYS (on the repository database of course)


Wait for <5 minutes for the prerequisites to complete:


Check the box which asks if you have backed up the repository and configuration of first management server and click Deploy:

 As OMS will be taken down during deployment of plug-in, you can monitor the progress using

./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Down

Unable to deploy or undeploy the plug-in. Oracle Management Server is down possibly because the following plug-ins are being deployed or undeployed from it. Try again after some time.
Plug-in Deployment/Undeployment Status

Destination          : Management Server - em12c.localdomain:4890_Management_Service
Plug-in Name         : Oracle Database                        
Version              : 12.1.0.5.0                             
ID                   : oracle.sysman.db                       
Content              : Plug-in                                 
Action               : Deployment                             
Status               : Deploying                              
Steps Info:
---------------------------------------- ------------------------- ------------------------- ----------
Step                                     Start Time                End Time                  Status    
---------------------------------------- ------------------------- ------------------------- ----------
Submit job for deployment                1/7/14 7:20:00 AM EST     1/7/14 7:20:00 AM EST     Success   

Initialize                               1/7/14 7:20:08 AM EST     1/7/14 7:20:26 AM EST     Success   

Install software                         1/7/14 7:20:28 AM EST     1/7/14 7:20:30 AM EST     Success   

Validate plug-in home                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Perform custom preconfiguration          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Check mandatory patches                  1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Generate metadata SQL                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preconfigure Management Repository       1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preregister DLF                          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Stop management server                   1/7/14 7:20:39 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

What interest us are the actions following the stop of management server. We need to rerun the above command; we cannot see the progress as we do with tail –f for example.


Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     N/A                       Running   

Register DLF                             1/7/14 7:22:23 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success    

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

…And the last step of starting management server…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success   

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     1/7/14 7:49:55 AM EST     Success   

Update inventory                         1/7/14 7:49:55 AM EST     1/7/14 7:49:59 AM EST     Success   

Start management server                  1/7/14 7:49:59 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------
The deployment completes after approximately 40 minutes. The OMS is up again.

[oracle@em12c bin]$ ./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Up
Oracle Management Server is Up

We now list the plugins on server

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.5.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0

We can see that Oracle Database shows correctly it is at version 12.1.0.5

In EM, the same information is visible


Reference:
How to Deploy the Latest Database Plugin to the OMS and the Agent in 12C Cloud Control (Doc ID 1388143.1)

Friday, October 18, 2013

DB12c - Session-private statistics for global temporary tables

In previous releases, the database did not maintain statistics for global temporary tables and non-global temporary tables differently.
The database maintained one version of the statistics shared by all sessions, even though data in different sessions could differ.
Starting in Oracle Database 12c, you can set the table-level preference GLOBAL_TEMP_STATS to make statistics on a global temporary table shared or session-specific. If set to session-specific, then you can gather statistics for a global temporary table in one session, and then use the statistics for this session only. Meanwhile, users can continue to maintain a shared version of the statistics.
During optimization, the optimizer first checks whether a global temporary table has session-specific statistics. If yes, the optimizer uses them. Otherwise, the optimizer uses shared statistics if they exist.


SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production


Session 1

SQL> create global temporary table new_gtt on commit delete rows
  2  as
  3  select * from all_objects where 1=2;

Table created.

SQL> insert into new_gtt select * from all_objects;

77381 rows created.

SQL> SELECT DBMS_STATS.GET_PREFS( 'GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')
  2  FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SESSION

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)
----------
     77381

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
SQL_ID  8432nj7acjwpx, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ count(*) from new_gtt



Plan hash value: 1213234781

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |    1313 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |    1313 |
|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  77381 |  77381 |00:00:00.01 |    1313 |
----------------------------------------------------------------------------------------

Note
-----
   - Global temporary table session private statistics used



18 rows selected.




We can see that the session private statistics were used.

When querying the user_tab_Statistics we can see the entry specific to this session, showing correct number of rows.

SQL> select table_name, num_rows, scope
  2  from user_tab_statistics
  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE
------------------------- ---------- -------
NEW_GTT                              SHARED
NEW_GTT                        77384 SESSION


Session 2

SQL> insert into new_gtt select * from all_objects where owner <> 'SYS';

42081 rows created.

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)
----------
     42081

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))
  2  ;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
SQL_ID  8432nj7acjwpx, child number 1
-------------------------------------
select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     722 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     722 |
|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  42081 |  42081 |00:00:00.01 |     722 |
----------------------------------------------------------------------------------------

Note
-----
   - Global temporary table session private statistics used


18 rows selected.

SQL>





We can see that this second session did not share the cursor it used new one.
The optimizer used session-specific statistics.

SQL> select table_name, num_rows, scope
  2  from user_tab_statistics
  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE
------------------------- ---------- -------
NEW_GTT                              SHARED
NEW_GTT                        42084 SESSION

Reference
Oracle® Database
SQL Tuning Guide
12c Release 1 (12.1)
E15858-15