Feed aggregator

Connecting PL/SQL Developer

Bar Solutions - Fri, 2016-10-28 10:28

In SQL Developer you have a lot of options when connecting to the database. You can use the TNS entries defined but you can also give the hostname, port and SID or Servicename for instance. PL/SQL Developer doesn’t supply these options, but you can still use them…

In the logonscreen you can choose the database by choosing you TNS entry. But did you know you can put in the complete text of a tnsnames.ora entry here?
So, you can type in:

username: demo
password: <<password>>
database: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DEMO)))

But it can be done even simpler:

username: demo
password: <<password>>
database: LOCALHOST:1521/DEMO

Maybe you already knew this and I have known this for a while, but I thought I would write this down anyway. Hope the helps some of you.

Modernize Employee Engagement: Making Culture Actionable Webcast Q&A

WebCenter Team - Fri, 2016-10-28 08:36
Oracle Corporation Oracle Webcast - Making Culture Actionable

Thank you for those who joined our webcast “Modernize Employee Engagement: Making Culture Actionable.” We hope you found the content presented valuable and enjoyed what the speakers presented. There were some questions that were asked throughout the webcast, so Dave has captured them here for you.

Q: Is there a clear difference between behaviors and the underlying why? Some of the 'why's' could look like behaviors too.
A: Very true. Often we find that leader behaviors very often belong in the “enablers and blockers” category, because leader behaviors are part of the way that an organization sets the tone for what is acceptable and what is not. The rules should not be rigid. The purpose of the Culture Map is to frame a conversation. When the question comes up in a session I ask the team where they think something belongs.

Q: If the execs weren't in the room when the sticky notes were generated, do they 'own' the diagnosis?
A: Great question. Executives are almost always the people who are sponsoring the work. Before we start any culture work, we explain why we will get better results if they are not in the room. We also caution them that if we start doing culture mapping people will start to shift their expectations. People will start to believe that management is listening to them. We caution leaders that if they are not prepared to accept the results, it would be better not to begin the process at all. It’s better to do nothing than to create expectations and hope for change, and then not act on them.

Q: Do you help companies with those 'other things' (systems, etc.), or do you focus on tracking the behavioral change?
A: Great question! Our company’s focus is on creating clarity and alignment in a way that drives impact. Making information clear and actionable. We help our clients gain clarity, understanding and alignment so they can move more quickly to action on many things, including strategy execution, systems, performance improvement, organizational structure and process design.

Q: What changes have you noticed in your own organization since you’ve implemented the Culture Maps?
A: We refresh our culture map every three years, right alongside our strategic planning process. I’ve noticed that this helps us make sure that we are intentionally assessing our culture and how well it serves our current organizational realities. Culture is like the business environment – it’s dynamic, not static, so we need to keep up. 

Another thing I’ve noticed is that having a clearly defined and visualized culture makes it easy for us to call each other out when we act in a way that’s “off culture.” I’ve been called on that myself. When someone can call out the company founder for straying from the behavior we want to see, I think that’s a great sign.

Q: What do you do if/when there is resistance to change when working with companies?
A: This is such a common phenomenon. The first thing we do is try to find out exactly where the resistance is coming from. We have identified 36 causes of resistance and have developed tool for diagnosing the underlying dynamics: The “barriers to change” card deck.

If you’d like to connect with Dave, you can follow him on Twitter @davegray or visit his website for more information. 


Automatic conversion of cursor for loop into set based operation

Tom Kyte - Fri, 2016-10-28 07:26
Hi, We all know that doing things row-by-row ("Cursor For Loops") is a bad idea rather than a set-based approach, however I have read in a number of places that in certain circumstances Oracle will convert a cursor for loop into a set-based operatio...
Categories: DBA Blogs

Spanish trasnlation for amount in words

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I have a requirement by a client that the amount in number currently prnting on checks needs to be converted to spanish, ex one hundred us dollars should be printed as cien dolares americanos Could you please suggest how can w...
Categories: DBA Blogs

How can i incorporate a text to the existing values of a column

Tom Kyte - Fri, 2016-10-28 07:26
Hi, The query is as below, I have a table in which i have the column named as V_QTN_NAME, here in this column the values are of different in each row. Now i am trying to differentiate the values by incorporating the text 'OLD'to the existing va...
Categories: DBA Blogs

Instance and SGA relation

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I would like to know how many SGA Created inside instance when data base starts? Here instance mean instance only i.e to which we mount database. Also please let me know if you answer the questions posted in reviews,perhaps i wil get ...
Categories: DBA Blogs

Insert statement getting locked in database.

Tom Kyte - Fri, 2016-10-28 07:26
Hello Sir, I have a very simple question and I have searched all around but could not find it, can insert statements be locked by other DML sessions ?? And how ? Reference :- I have a process that updates/inserts multiple records into one table ...
Categories: DBA Blogs

RMAN Incremental

Tom Kyte - Fri, 2016-10-28 07:26
Hello Team, We have oracle 12c standard running on centos. Basic backup strategy is developed without using asm/ catalog database as suggested in requirements. Backup Plan is as below: Requirement says full backup has to run every night with ...
Categories: DBA Blogs

Users and Roles

Tom Kyte - Fri, 2016-10-28 07:26
Tom, in your apps 1)Do you normally have an Oracle user for each application user...or a table you create of the users for your app. I was creating real Oracle users w/forms and now use my own tables of users for web enabled apps...but I noticed t...
Categories: DBA Blogs

Oracle 12c – When the RMAN Dummy Instance does not start up …

Yann Neuhaus - Fri, 2016-10-28 06:53

Not too often, but sometimes you might run into a situation when you lose everything, your DB Files, your Controlfiles and even your spfile. In situations like that, you need to restore first your spfile, then your controlfile and then the rest.

For restoring the spfile, RMAN has a chicken/egg issue. To be able to restore the spfile, RMAN needs at least a running instance, but how do we start the instance without having the spfile? There are several methods to do it, one of it, is to let RMAN itself create a dummy instance.

But if you think your situation can’t get worse than the following happens. Oracle raises an ORA-04031 error and even the Dummy Instance does not start.

oracle@oel001:/home/oracle/ [rdbms121] export ORACLE_SID=LAX
oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:45:08 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0.2/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/28/2016 11:45:15
RMAN-04014: startup failed: ORA-04031: unable to allocate 111264 bytes of shared memory 
("shared pool","unknown object","sga heap(1,0)","KEWS sesstat values")

 

The error message means, that the SGA allocated for the dummy instance is to small. This is where the environment variable ORA_RMAN_SGA_TARGET comes into play. The environment variable ORA_RMAN_SGA_TARGET sets the SGA to a value in Megabytes which can be used by RMAN to start the Dummy Instance. In the following example to 1024 MB.

oracle@oel001:/home/oracle/ [LAX] export ORA_RMAN_SGA_TARGET=1024
oracle@oel001:/home/oracle/ [LAX]

oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 13:12:13 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0.2/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                293601384 bytes
Database Buffers             771751936 bytes
Redo Buffers                   5455872 bytes

RMAN>

Behind the scenes, Oracle starts a Dummy Instance with the following parameters, and creates all necessary directories in the DIAG destination.

sga_target               = 1G
compatible               = "12.1.0.2.0"
_dummy_instance          = TRUE
remote_login_passwordfile= "EXCLUSIVE"
_diag_adr_trace_dest='/u00/app/oracle/diag/rdbms/dummy/LAX/trace'
core_dump_dest='/u00/app/oracle/diag/rdbms/dummy/LAX/cdump'
db_name='DUMMY' 
  
oracle@oel001:/u00/app/oracle/diag/rdbms/dummy/LAX/ [LAX] pwd
/u00/app/oracle/diag/rdbms/dummy/LAX
oracle@oel001:/u00/app/oracle/diag/rdbms/dummy/LAX/ [LAX] ls
alert  cdump  hm  incident  incpkg  ir  lck  log  metadata  metadata_dgif  metadata_pv  stage  sweep  trace

 

Now it worked and I can retrieve my spfile.

run {
restore spfile to pfile '/tmp/initLAX.ora' for db_unique_name='LAX' from
'+fra/lax/autobackup/2016_10_28/s_894893708.1292.894893713';
}

From now on, I can use the correct parameter file to continue with the other steps. Another option would have been, to search through your alert.log for “System parameters with non-default values”. Whenever you startup your instance, Oracle dumps out the non-default parameter values into the alert.log. Those values can be used to manually create an init.ora file and then the spfile. The drawback of using the values from alert.log is, that the values might be very old. In case the instance was not bounced for several months or longer (not so unusual), then you miss all the new setting since then.

...
...
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u00/app/oracle/product/12.1.0.2
System name:    Linux
Node name:      oel001
Release:        2.6.32-642.6.1.el6.x86_64
Version:        #1 SMP Tue Oct 4 15:19:03 PDT 2016
Machine:        x86_64
System parameters with non-default values:
  processes                = 300
  _disable_highres_ticks   = TRUE
  event                    = "10720 trace name context forever, level 0x10000000"
  event                    = "10795 trace name context forever, level 2"
  sga_max_size             = 1536M
  use_large_pages          = "ONLY"
  shared_pool_size         = 256M
  _high_priority_processes = "LGWR"
  _highest_priority_processes= "LGWR"
  filesystemio_options     = "SETALL"
  sga_target               = 1536M
  control_files            = "+DATA/LAX/CONTROLFILE/current.265.918392661"
  control_files            = "+FRA/LAX/CONTROLFILE/current.256.918392661"
  control_file_record_keep_time= 32
  db_block_size            = 8192
  compatible               = "12.1.0.2.0"
  log_archive_format       = "%t_%s_%r.dbf"
  db_create_file_dest      = "+DATA"
  db_create_online_log_dest_1= "+DATA"
  db_recovery_file_dest    = "+FRA"
  db_recovery_file_dest_size= 32G
  undo_tablespace          = "UNDOTBS1"
  undo_retention           = 3600
  db_securefile            = "PERMITTED"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=LAXXDB)"
  local_listener           = "LAX_LISTENER"
  session_cached_cursors   = 512
  parallel_max_servers     = 80
  audit_file_dest          = "/u00/app/oracle/admin/LAX/adump"
  audit_trail              = "DB"
  cell_offload_processing  = FALSE
  db_name                  = "LAX"
  open_cursors             = 300
  pga_aggregate_target     = 512M
  _disable_directory_link_check = TRUE
  diagnostic_dest          = "/u00/app/oracle"
...
...

In the end, you have different possibilities to restore the spfile. Either with the RMAN Dummy Instance, or via the alert.log. In case of the Dummy Instance, you might need to play around with the ORA_RMAN_SGA_TARGET environment variable.

By the way … with Oracle 10.2.0.5 your chance to hit the ORA-04031 error during the Dummy Instance startup was much higher, because the default was only 152MB.

oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 28 11:26:09 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/10.2.0.5/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2094736 bytes
Variable Size                 67111280 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6291456 bytes

RMAN>

 

Cheers,
William

 

 

Cet article Oracle 12c – When the RMAN Dummy Instance does not start up … est apparu en premier sur Blog dbi services.

The Times They Are A Changing

David Haimes - Fri, 2016-10-28 05:58

I am focusing on some new things here at Oracle.  I’ll be dedicating a lot more of my time looking into emerging technology trends and how we should be applying them to the enterprise ERP or financial management space.

So how does this effect this blog?

I’ve been blogging for many years on Intercompany, Financial Management and ERP, mixing in some general technology posts.  Now I plan to start writing about new technology trends a lot more.  I have always been interested in this, but now I have more time to get deeper into it and I find blogging about things helps me understand them better and get feedback and insight from others, so it is a win-win.  I have enjoyed all the comments on the blog and words of encouragement that people have given me in person, that has been a huge motivation to continue writing (even tho I have been posting as frequently as I would like the last few years) and respond to comments.  I hope that feedback will continue.

So now I have got this announcement out of the way, I can get started… watch this space.


Categories: APPS Blogs

Installing Developer 10g 32-bit on 64-bit Windows versions

Steven Chan - Fri, 2016-10-28 02:04

E-Business Suite 12.1 and 12.2 require Forms Developer 10g and Reports Designer 10g.  Forms Developer 10g and Reports Designer 10g are part of Oracle Developer Suite 10g.  Oracle Developer Suite 10g is a 32-bit program certified with Windows Vista, 7, 8.1, and 10.

Forcing 32-bit programs to run on 64-bit Windows

Although Windows 32-bit programs will not install by default on 64-bit versions of Windows, there are three possible workarounds:

Workaround 1: You can run the Developer 10g setup.exe installer in Windows compatibility mode > Windows XP (Service Pack 2) or Windows XP (Service Pack 3).  See Section 6 in Note 277535.1.

Workaround 2: You can let Windows determine how to install it automatically via the "Program Compatibility Assistant".  See Section 6 in Note 277535.1.

Windows Program Compatibility Assistant

Workaround 3: Install some additional patches and run the Developer 10g installer with the -ignoreSysPrereqs command line parameter.  See Note 1292919.1 for more details.

Related Articles
Categories: APPS Blogs

Documentum story – SSL Password for the JKS of the DSearch & IndexAgent (xPlore)

Yann Neuhaus - Fri, 2016-10-28 02:00

In a previous blog (click here), I described how to setup the DSearch and IndexAgent(s) in HTTPS using the Groovy script provided by EMC in newer versions of Documentum. This script is quite cool because it will allow you to automatically do some stuff like updating the xml configuration files, put the java keystore in the right location and configure JBoss to use it, aso… It also allows you to quickly apply/revert changes to do some tests for example. Several months ago when I first used this script, I faced some issues. I will present in this blog some of the errors I’ve seen and what was the reason behind that.

 

Actually I have a “funny” story related to these errors: several months after seeing these errors for the first time (and telling EMC about it with complete description of the issue and according solution), I worked on another new issue related to the Full Text Server with the help of EMC. Our EMC contact was trying to replicate our new issue and for that purpose, he had to setup the IndexAgent in SSL and he faced an error with the SSL setup… So he opened a ticket with the EMC Engineering Team to ask them to check why this was happening. In the meantime, I also asked him to share the error he was facing with me because I was curious… Of course, it turned out to be one of the errors I saw and shared with EMC several months before that. Therefore I provided him the solution to correct this bug and he was able to install the IndexAgent in SSL. I found that funny how sometimes you can actually be the one helping EMC with their own products ^^.

 

So let’s start with some of the errors I saw:

1. While configuring a DSearch in HTTPS

[xplore@xplore_server_01 admin]$ ./xplore.sh -f scripts/ConfigSSL.groovy -enable -component IS \
    -alias ft_alias -keystore "/app/xPlore/jboss7.1.1/certs/xplore_server_01.jks" \
    -storepass $JKS_Password -indexserverconfig "/app/xPlore/config/indexserverconfig.xml" \
    -isname PrimaryDsearch

Component: IS
====== Configure Index Server: PrimaryDsearch ======
Copy: /app/xPlore/jboss7.1.1/certs/xplore_server_01.jks to: ./../../jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration/my.keystore
Copy: /app/xPlore/config/indexserverconfig.xml to: /app/xPlore/config/indexserverconfig.xml.bakHttp
Primary instance: PrimaryDsearch
Host name: xplore_server_01
Http port: 9300, https port: 9302
Updated: /app/xPlore/config/indexserverconfig.xml
Copy: ./../../jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration/standalone.xml to: ./../../jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration/standalone.xml.bakHttp
Exception in thread "main" java.lang.IndexOutOfBoundsException: No group 6
        at java.util.regex.Matcher.start(Matcher.java:374)
        at java.util.regex.Matcher.appendReplacement(Matcher.java:831)
        at java.util.regex.Matcher.replaceFirst(Matcher.java:955)
        at java.lang.String.replaceFirst(String.java:2119)
        at java_lang_String$replaceFirst.call(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)
        at ConfigSSL.configIS(ConfigSSL.groovy:624)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
        at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
        at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:361)
        at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:877)
        at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:66)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:46)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
        at ConfigSSL$_run_closure1.doCall(ConfigSSL.groovy:333)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
        at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
        at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:272)
        at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:877)
        at groovy.lang.Closure.call(Closure.java:412)
        at groovy.lang.Closure.call(Closure.java:425)
        at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1376)
        at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:1348)
        at org.codehaus.groovy.runtime.dgm$162.invoke(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271)
        at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
        at ConfigSSL.run(ConfigSSL.groovy:331)
        at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:266)
        at groovy.lang.GroovyShell.run(GroovyShell.java:229)
        at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.callEntrance(DSearchAdminCLI.java:83)
        at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.main(DSearchAdminCLI.java:22)

 

2. While configuring an IndexAgent in HTTPS

[xplore@xplore_server_01 admin]$ ./xplore.sh -f scripts/ConfigSSL.groovy -enable -component IA \
    -alias ft_alias -keystore "/app/xPlore/jboss7.1.1/certs/xplore_server_01.jks" \
    -storepass $JKS_Password -indexserverconfig "/app/xPlore/config/indexserverconfig.xml" \
    -ianame Indexagent -iaport 9200

Component: IA
====== Configure Index Agent: Indexagent ======
Copy: /app/xPlore/jboss7.1.1/certs/xplore_server_01.jks to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/my.keystore
Copy: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml.bakHttp
Exception in thread "main" java.lang.IndexOutOfBoundsException: No group 6
        at java.util.regex.Matcher.start(Matcher.java:374)
        at java.util.regex.Matcher.appendReplacement(Matcher.java:831)
        at java.util.regex.Matcher.replaceFirst(Matcher.java:955)
        at java.lang.String.replaceFirst(String.java:2119)
        at java_lang_String$replaceFirst.call(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)
        at ConfigSSL.configIA(ConfigSSL.groovy:888)
        at ConfigSSL$configIA.callCurrent(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:46)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
        at ConfigSSL.run(ConfigSSL.groovy:338)
        at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:266)
        at groovy.lang.GroovyShell.run(GroovyShell.java:229)
        at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.callEntrance(DSearchAdminCLI.java:83)
        at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.main(DSearchAdminCLI.java:22)

 

3. While configuring an IndexAgent in HTTPS: another one

[xplore@xplore_server_01 admin]$ ./xplore.sh -f scripts/ConfigSSL.groovy -enable -component IA \
    -alias ft_alias -keystore "/app/xPlore/jboss7.1.1/certs/xplore_server_01.jks" \
    -storepass Test4Pass+word$ -indexserverconfig "/app/xPlore/config/indexserverconfig.xml" \
    -ianame Indexagent -iaport 9200
    
Component: IA
====== Configure Index Agent: Indexagent ======
Copy: /app/xPlore/jboss7.1.1/certs/xplore_server_01.jks to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/my.keystore
Copy: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml.bakHttp
Exception in thread "main" java.lang.IllegalArgumentException: Illegal group reference
      at java.util.regex.Matcher.appendReplacement(Matcher.java:808)
      at java.util.regex.Matcher.replaceFirst(Matcher.java:955)
      at java.lang.String.replaceFirst(String.java:2119)
      at java_lang_String$replaceFirst.call(Unknown Source)
      at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
      at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
      at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)
      at ConfigSSL.configIA(ConfigSSL.groovy:888)
      at ConfigSSL$configIA.callCurrent(Unknown Source)
      at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:46)
      at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133)
      at ConfigSSL.run(ConfigSSL.groovy:338)
      at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:266)
      at groovy.lang.GroovyShell.run(GroovyShell.java:229)
      at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.callEntrance(DSearchAdminCLI.java:83)
      at com.emc.documentum.core.fulltext.client.admin.cli.DSearchAdminCLI.main(DSearchAdminCLI.java:22)

 

4. The solution?

You might have noticed above that each time the exception is thrown just after the backup of the file standalone.xml. So what is the Groovy script doing in this file exactly which can cause these errors? Well when configuring JBoss in HTTPS, there is only one thing to do in this file: add a new connector to enable the HTTPS and to point to the correct keystore/password. Based on this information, it is really easy to find the reason for all these exceptions. In addition to that, you might also have noticed that in the two first points above, the password of the keystore was “$JKS_Password” while in the last command, the password is in clear text (Test4Pass+word$). So what is that environment variable $JKS_Password? Well we had to use IQs to install all these environments with logging tools to review and validate the installation and since these logging tools are storing everything displayed to the console, we were using environment variable to store our password so it is not displayed on the console or on the history of the “xplore” user. This is done as follow:

[xplore@xplore_server_01 ~]$ stty -echo; read JKS_Password; stty echo
                                                         => Enter the password here, it will be invisible
[xplore@xplore_server_01 ~]$
[xplore@xplore_server_01 ~]$ echo $JKS_Password
Test4Pass+word$
[xplore@xplore_server_01 ~]$

 

Basically the first command will ask you to type the password and when you press Enter, the prompt is returned and the environment variable $JKS_Password contains your password.

 

Now that this is clear, what is the solution to correct the errors mentioned above? Well the solution is the same for all errors above and this solution is quite simple: don’t use any annoying special characters in your passwords (like “$” or “\”). The problem with these characters is that the Groovy script isn’t able to manage them at the moment and therefore creating a password that contains one of these characters might result is errors and/or strange results as shown above.

 

Even if annoying special characters should be avoided to prevent these errors, you can continue to use some other special characters if you want to use a strong password like “+”, “-“, “?” and you can also continue to use the environment variable if you want. This is an example of the command that is working:

[xplore@xplore_server_01 ~]$ echo $JKS_Password
Test4Pass+word
[xplore@xplore_server_01 ~]$ 
[xplore@xplore_server_01 admin]$ ./xplore.sh -f scripts/ConfigSSL.groovy -enable -component IA \
    -alias ft_alias -keystore "/app/xPlore/jboss7.1.1/certs/xplore_server_01.jks" \
    -storepass $JKS_Password -indexserverconfig "/app/xPlore/config/indexserverconfig.xml" \
    -ianame Indexagent -iaport 9200

Component: IA
====== Configure Index Agent: Indexagent ======
Copy: /app/xPlore/jboss7.1.1/certs/xplore_server_01.jks to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/my.keystore
Copy: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml to: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml.bakHttp
Updated: ./../../jboss7.1.1/server/DctmServer_Indexagent/configuration/standalone.xml
Copy: ./../../watchdog/config/dsearch-watchdog-config.xml to: ./../../watchdog/config/dsearch-watchdog-config.xml.bakHttp
Updated: ./../../watchdog/config/dsearch-watchdog-config.xml
Copy: ./../../jboss7.1.1/server/DctmServer_Indexagent/deployments/IndexAgent.war/WEB-INF/classes/indexagent.xml to: ./../../jboss7.1.1/server/DctmServer_Indexagent/deployments/IndexAgent.war/WEB-INF/classes/indexagent.xml.bakHttp
Updated: ./../../jboss7.1.1/server/DctmServer_Indexagent/deployments/IndexAgent.war/WEB-INF/classes/indexagent.xml
SSL is enabled, clear jboss cache and start server to verify result.

 

Once this execution completed successfully, you can just continue the SSL setup as described in this blog linked at the top and you shouldn’t face any other issue.

 

As soon as you put a “$” in your password and use this password in the command line (or use the environment variable), you will in the end face an error that will depend on whether or not you used the environment variable to hide it. As an additional note, when I was working on these errors, I did a lot of tests and I already saw the script working (or so it seemed) even with a password containing a “$” (depending on how it is written, with single quotes, double quotes, escaped, aso…) when put directly in the command line. But when I tried to start the IndexAgent, it wasn’t able to start… Therefore I took a look at the file standalone.xml and it was corrupted! The Groovy script screwed up my standalone.xml file and I had to revert a backup to be able to restart it. Hopefully the script is smart enough to backup all files before doing anything…

 

In case you absolutely want a “$” in your password, then it is possible but you will have to do one step manually. First you need to execute the script with a DUMMY password without any special character:

[xplore@xplore_server_01 admin]$ ./xplore.sh -f scripts/ConfigSSL.groovy -enable -component IA \
    -alias ft_alias -keystore "/app/xPlore/jboss7.1.1/certs/xplore_server_01.jks" \
    -storepass DUMMY_PASSWORD -indexserverconfig "/app/xPlore/config/indexserverconfig.xml" \
    -ianame Indexagent -iaport 9200
...

 

Once done, you can simply open the file standalone.xml and replace “DUMMY_PASSWORD” with the real password that contains a “$”. Restart the IndexAgent and this time, it should work. :)

 

Cet article Documentum story – SSL Password for the JKS of the DSearch & IndexAgent (xPlore) est apparu en premier sur Blog dbi services.

Links for 2016-10-27 [del.icio.us]

Categories: DBA Blogs

Oracle 12c – When did my row change?

Yann Neuhaus - Fri, 2016-10-28 01:51

My good old DEPT table always had 4 rows with 4 different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened?

Before:

SQL> select  DEPTNO, DNAME, LOC from DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

After:

SQL> select  DEPTNO, DNAME, LOC from DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 IT             L.A.

Unfortunately, I have no auditing in place, and supplemental logging is also not activated. So, auditing is not an option and LogMiner also not. I’m kind of running out of options to get the timestamp when the department 50 was inserted.

My last resort is the ORA_ROWSCN pseudocolumn. Due to the Oracle documentation, for each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn becomes useful, in case we want to determine approximately when a row was last updated.

ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external, and be careful, it is not 100% precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. However, it is better then nothing for my use case.

SQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   9708226         10 ACCOUNTING     NEW YORK
   9708226         20 RESEARCH       DALLAS
   9708226         30 SALES          CHICAGO
   9708226         40 OPERATIONS     BOSTON
   9708226         50 IT             L.A.

To convert now the SCN to a Timestamp, we can use the SCN_TO_TIMESTAMP function. It converts the SCN to a Timestamp with a precision of +/- 3 seconds.

SQL> select scn_to_timestamp(9708226) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
27-OCT-16 11.40.07.000000000 AM

Due to the ORA_ROWSCN pseudocolumn, now I know at least, that the block where the department 50 was inserted was changed at the “27-OCT-16 11.40.07″.

If you want it more accurate, you need a feature called Row Level Dependency Tracking. This feature is activated by the keyword ROWDEPENDENCIES during the CREATE TABLE.

CREATE TABLE "SCOTT"."DEPT_RLDT" 
   (	"DEPTNO" NUMBER(2,0), 
	"DNAME" VARCHAR2(14 BYTE), 
	"LOC" VARCHAR2(13 BYTE), 
	 CONSTRAINT "PK_DEPT_RLDT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ROWDEPENDENCIES ;

With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row.

You cannot change this setting after the table is created. Meaning, there is no “alter table … ROWDEPENDENCIES”.

This setting is useful primarily to allow for parallel propagation in replication environments, however, it can also be used to find out quickly the time, when a row was change.

Like always in life, nothing comes for free. The drawback is, that it increases the size of each row by 6 bytes. That’s why the default is NOROWDEPENDENCIES.

But once this feature is enable, like in the following example for my DEPT_RLDT table, you will get
a different SCN number for the new commited row (department 50).

SQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT_RLDT;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   9701315         10 ACCOUNTING     NEW YORK
   9701315         20 RESEARCH       DALLAS
   9701315         30 SALES          CHICAGO
   9701315         40 OPERATIONS     BOSTON
   9708244         50 IT             L.A.

SQL>

SCN 9701315 for deptno 10,20,30,40 and 9708244 for deptno 50. That’s cool.

SQL> select scn_to_timestamp(9708244) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
27-OCT-16 11.40.24.000000000 AM

Due to the SCN to a Timestamp precision of +/- 3 seconds, it is still not 100%, but very close to it. Another important point is that the ORA_ROWSCN represents an upper bound SCN. We just know it has not changed after this SCN but may have been changed earlier.

So … does the ORA_ROWSCN replace auditing? No, not at all. But it can help to answer questions like the following: Please tell me when ROW xyz has been modified the last time and recover the table (using Flashback techniques) to that point.
Cheers,
William

 

Cet article Oracle 12c – When did my row change? est apparu en premier sur Blog dbi services.

12c GTT private statistics and cursor invalidation

Yann Neuhaus - Thu, 2016-10-27 15:27

Short summary of this post is that rolling invalidation do not occur when you gather statistics on Global Temporary Tables in 12c that have session statistic scope (which is the default) and this may cause too many hard parses. I’m sharing all details and comments are welcome.

When you gather statistics on a table, the goal is to get new plan if statistics have changed, so you can expect cursor invalidation. However, invalidating immediately all cursors that have a dependency with the table may cause a hard parse storm and this is why by default rolling invalidation occurs: invalidation of cursor will be planned randomly in a time window that follows next execution. 12c comes with a new feature, global temporary table private stats where execution plans are not shared between sessions. And there’s another feature where statistics gathering is automatic when you bulk insert into an empty table.

In both cases, by default, invalidation is not rolling but immediate. Let’s see examples.

No GTT -> rolling invalidation

Here is an example with a regular table to show rolling invalidation:

21:14:36 SQL> create
21:14:36 2 table DEMOGTT1 as select * from dual;
Table created.
 
21:14:38 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1');
PL/SQL procedure successfully completed.
 
21:14:39 SQL> insert into DEMOGTT1 select * from dual;
1 row created.
 
21:14:39 SQL> alter session set optimizer_mode=first_rows;
Session altered.
 
21:14:39 SQL> insert into DEMOGTT1 select * from dual;
1 row created.
 
21:14:39 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000008368BB98 3223759815 1 1 N 0
VALID 1 000000008368BB98 3223759815 1 1 N 0
 
21:14:40 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:14:41 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000008368BB98 3223759815 1 1 Y 0
VALID 1 000000008368BB98 3223759815 1 1 Y 0
 
21:14:41 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED 21:14:40

Statistics on non-GTT are shared and the dbms_stats using default no_invalidate do rolling invalidation.

GTT with session private stats -> immediate invalidation

Here is the same example with a Global Temporary Table:

21:13:06 SQL> create
21:13:06 2 global temporary
21:13:06 3 table DEMOGTT1 as select * from dual;
Table created.
...
21:13:09 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000008096DF10 3223759815 1 1 N 0
VALID 1 000000008096DF10 3223759815 1 1 N 0
 
21:13:10 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:13:11 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
INVALID_UNAUTH 0 000000008096DF10 3223759815 1 1 N 1
INVALID_UNAUTH 1 000000008096DF10 3223759815 1 1 N 1
 
21:13:11 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED
DEMOGTT1 SESSION 21:13:10

By default, gathered statistics are private to the session and you see that all cursors have been invalidated immediately. Next execution will need to hard parse.

GTT with shared stats -> no invalidation

When setting shared statistics on the GTT we come back to the 11g behavior:

21:28:52 SQL> create
21:28:52 2 global temporary
21:28:52 3 table DEMOGTT1 as select * from dual;
Table created.
 
21:28:52 SQL> exec dbms_stats.set_table_prefs(user,'DEMOGTT1','GLOBAL_TEMP_TABLE_STATS','SHARED');
PL/SQL procedure successfully completed.
...
21:28:55 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 0000000079782A08 3223759815 1 1 N 0
VALID 1 0000000079782A08 3223759815 1 1 N 0
 
21:28:56 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:28:57 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 0000000079782A08 3223759815 1 1 Y 0
VALID 1 0000000079782A08 3223759815 1 1 Y 0
 
21:28:57 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED 21:28:56

No invalidation: this is rolling invalidation

GTT with session stats but “_optimizer_use_gtt_session_stats”=false

Here is an exemple when disabling the private statistics feature:

21:15:36 SQL> create
21:15:36 2 global temporary
21:15:36 3 table DEMOGTT1 as select * from dual;
Table created.
 
21:15:36 SQL> alter session set "_optimizer_use_gtt_session_stats"=false;
Session altered.
...
21:15:38 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000007A373A08 3223759815 1 1 N 0
VALID 1 000000007A373A08 3223759815 1 1 N 0
 
21:15:39 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:15:41 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000007A373A08 3223759815 1 1 N 0
VALID 1 000000007A373A08 3223759815 1 1 N 0
 
21:15:41 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED
DEMOGTT1 SESSION 21:15:40

No invalidation here as in previous versions. But interesting thing is that I still have session statistics. The setting just disables its usage. But then, there were no invalidation and no rolling invalidation. Not sure how to interpret that…

Invalidation with online statistics gathering

In all those examples I’ve used dbms_stats with default no_invalidate. But in 12c statistics gathering can occur automatically during bulk insert. Let’s try that:

...
21:38:50 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 000000007A9D8860 3223759815 1 1 N 0
VALID 1 000000007A9D8860 3223759815 1 1 N 0
 
21:38:51 SQL> truncate table DEMOGTT1;
Table truncated.
21:38:52 SQL> insert /*+ append */ into DEMOGTT1 select * from dual;
1 row created.
 
21:38:53 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
INVALID_UNAUTH 0 000000007A9D8860 3223759815 1 1 N 1
INVALID_UNAUTH 1 000000007A9D8860 3223759815 1 1 N 1
21:38:53 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED
DEMOGTT1 SESSION 21:38:52

Same behaviour here. The online statistics gathering has gathered private statistics and invalidated all cursors.

NO_INVALIDATE=true

We can explicitly disable invalidation with no_invalidate=>true:

...
21:43:25 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 0000000075873D60 3223759815 1 1 N 0
VALID 1 0000000075873D60 3223759815 1 1 N 0
 
21:43:28 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>true);
PL/SQL procedure successfully completed.
 
21:43:29 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS CHILD_NUMBER ADDRESS HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID 0 0000000075873D60 3223759815 1 1 N 0
VALID 1 0000000075873D60 3223759815 1 1 N 0
 
21:43:29 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME SCOPE LAST_ANA
------------------------------ ------- --------
DEMOGTT1 SHARED
DEMOGTT1 SESSION 21:43:28

Here, as requested, private statistics has been gathered but without cursor invalidation. However I’ll have new hard parse for my query because private statistics prevent sharing another cursor, but it’s not an invalidation of all cursors. The other sessions will continue to re-use their plan.

So what?

With those new features, we have the famous parsing dilemma again: do we want to avoid too many hard parses and share cursor with the risk of executing an execution plan that has been optimized for different data? Or do we prefer to optimize each query at the risk of more CPU consumption and shared pool contention? Given that 12c comes with adaptive dynamic sampling that can make hard parse longer, and sometimes very very long, all those new features may be gauged carefully.

If you want to avoid hard parses, you should set preferences to SHARED statistics and then gather statistics when the GTT is filled with the data you want to optimize for, and then lock it. If you don’t, then you are back to the problem that private statistics tries to solve: sharing a plan optimized for few rows and executed on thousands.

 

Cet article 12c GTT private statistics and cursor invalidation est apparu en premier sur Blog dbi services.

redo 12c and temporary table

Tom Kyte - Thu, 2016-10-27 13:06
Hello there, I am quite curious why redo is still generated when I set temp_undo_enabled=true, having parameter compatible 12.1.0.2.0. I expected redo to be close to 0. The same result I got years ago, when I first introduced with 12.1.0.1 I ...
Categories: DBA Blogs

Left join with filter condition is not working as supposed

Tom Kyte - Thu, 2016-10-27 13:06
Hello, One of my colleagues asked a question like "when I insert query result to table, I found records that meet my criteria, but when I put the same filter on the same query without inserting rows to table, I did not get those records". After li...
Categories: DBA Blogs

exception handling in select a query not pl/sql block

Tom Kyte - Thu, 2016-10-27 13:06
Query; update accounts set product_code=( select product from products); from the query the sub query return multiple rows. So i want to update it with null value. so how could i do this exception handling from this query??
Categories: DBA Blogs

Script for increase sequence by table max pk value

Tom Kyte - Thu, 2016-10-27 13:06
We have sequences created by table name format as seq_<table_name>. Somehow sequence is lower than pl value and need to fix by increasing sequence. Here I just simulate the situation as example below: </> ORACLE@hist > create table t1 (a_id num...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator