Feed aggregator

Security model for SQL Tuning sets

Tom Kyte - Thu, 2018-01-25 21:46
Hello Tom, Is it possible for a user without DBA priv and Select privs on base tables to create SQL Tuning Tasks? what if we create a procedure under another user with DBA priv and grant the user execute on the procedure? Thx for the Supp...
Categories: DBA Blogs

Exchange partition issue

Tom Kyte - Thu, 2018-01-25 21:46
Hi, We have exchanged partition of the landing table with the history table using the below statement <code>ALTER TABLE lnd.hist_tbl EXCHANGE PARTITION v_partname WITH TABLE lnd.curr_tbl WITHOUT VALIDATION; v_partname - max partition name ...
Categories: DBA Blogs

JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters

Tom Kyte - Thu, 2018-01-25 21:46
JSON_QUERY and JSON_VALUE not able to handle more than 1 lakh characters. I've a big report (size can change at any time) and push it to oracle as JSON and parse there. I'm getting JSON Syntax Error, may be since the JSON is truncated to 4000 bytes. ...
Categories: DBA Blogs

Java 9: JDK without subfolder JRE

Dietrich Schroff - Thu, 2018-01-25 15:09
In older JDK releases the directory structure was:
bin
db
include
jre
libWith JEP 220 the jre folder was removed.


A JDK image no longer contains a jre subdirectory, as noted above. Existing code that assumes the existence of that directory might not work correctly.But this does not mean that there is no JRE any more. You can still download the JRE. There are some minor changes to the JRE like:
The bin directory in a JRE image contains a few commands that were previously found only in JDK images, namely appletvieweridlj,jrunscript, and jstatd. As with the previous item, these changes are a consequence of the way in which components that contain both APIs and tools were modularized.For both version the files rt.jar, tools.jar and dt.jar are removed:
JDK and JRE images no longer contain the files lib/rt.jar,lib/tools.jarlib/dt.jar, and other internal JAR files, as noted above. Existing code that assumes the existence of these files might not work correctly.But this should not be a problem:
Class and resource files previously found in lib/dt.jar and visible only when that file was added to the class path are now visible via the bootstrap class loader and present in both the JRE and the JDK. Everyone who delivers Java applications should read  JEP 220 and check, what has to be changed...

gc buffer busy

Jonathan Lewis - Thu, 2018-01-25 08:12

I had to write this post because I can never remember which way round Oracle named the two versions of gc  buffer busy when it split them. There are two scenarios to cover when my session wants my instance to acquire a global cache lock on a block and some other session is already trying to acquire that lock (or is holding it in an incompatible fashion):

  • The other session is in my instance
  • The other session is in a remote instance

One of these cases is reported as “gc buffer busy acquire”, the other as a “gc buffer busy release” – and I always have to check which is which. I think I usually get it right first time when I see it, but I always manage to convince myself that I might have got it wrong and end up searching the internet for Riyaj Shamsudeen’s blog posting about it.

The “release” is waiting for another instance to surrender the lock to my instance; the “acquire” is waiting for another session in my instance to finish acquiring the lock from the other  instance.

I decided to jot down this note so I didn’t have to keep searching for Riyaj’s and also because a little problem on OTN at the moment showed a couple of AWR reports with an unlikely combination of waits for acquire (180,000,000) and release (2,000) waits.

If you’re wondering why this looks odd – if I’m waiting for an acquire someone else in my instance must be waiting for a release.  Obviously many sessions could be waiting for one release, and if acquirers time out very rapidly (though they’re not reported as doing so) then the ratio could get very high – but 90,000 acquires per release doesn’t look right.

 

Connect to local server 'SYSTEM' Error with sql developer

Tom Kyte - Thu, 2018-01-25 03:26
I install SQL Developer and log in my local server SYSTEM once. but today when I try to log in, is says error: An error was encountered performing the requested operation: ORA-12560: TNS:protocol adapter error 12560. 00000 - "TNS:protocol ada...
Categories: DBA Blogs

Remove SQL Qeury in CSV Output

Tom Kyte - Thu, 2018-01-25 03:26
I have a sql query that needs to spool a select statement in a csv file. Here is the query: <code> set colsep '|' set echo off set feedback off set linesize 1000 set pagesize 0 set sqlprompt '' set trimspool on set headsep off Spool 's...
Categories: DBA Blogs

Career advice for a very frustrated Oracle DBA

Tom Kyte - Thu, 2018-01-25 03:26
Hi, I am a senior Oracle DBA working in a prestigious government department. I have good benefits, a good salary, my job is not so stressful. I have been working for the government for the last nine years before that are used to work for the private...
Categories: DBA Blogs

When replace PACKAGE do I need to recreate GRANT and SYNONYM

Tom Kyte - Thu, 2018-01-25 03:26
I have PACKAGEs that already set the GRANTs and SYNONYMs. I want to make some changes to a PACKAGE: create or replace PACKAGE "AAA_PKG" After that do I need to reset the GRANTs and SYNONYMs? CREATE OR REPLACE SYNONYM ABC.AAA_PKG F...
Categories: DBA Blogs

Oracle, Azure Cloud and licensing with hyperthreading enabled.

Amis Blog - Thu, 2018-01-25 02:11

Until recently, there was one rule when deploying Oracle software in the Azure cloud:

  • count one Azure CPU Core as equivalent to one Oracle Processor license.

But there was somehow what confusion about hyperthreading VM’s. This has been nuanced by Oracle in their cloud-licensing document very recently

The document now states the following about licensing Oracle in Azure:

Microsoft Azure – count two vCPUs as equivalent to one Oracle Processor license if hyperthreading is enabled, and one vCPU as equivalent to one Oracle Processor license if hyperthreading is not enabled.

But.. when you are designing / choosing your VM’s in Azure, how can you tell which VM is using Hyperthreading? Well, that’s a simple rule: all VM’s which are older than the V3 are NOT using hyperthreading. See also this blog of Microsoft.

Other Azure changes, related to this change:

  • When using Standard Edition (2) :

Four or fewer (!) Azure vCPUs, are counted as 1 socket, which is considered equivalent to an Oracle processor license. –> (instead of two).

Oracle Database Standard Edition may only be licensed up to 16 Azure vCPUs

Oracle Standard Edition One and Standard Edition 2 may only be licensed up to eight Azure vCPUs.

Database Standard Edition 2 on NUPS:  the minimums are 10 NUP licenses per 8 Azure vCPUs.

  • Oracle Linux:

For Oracle Linux purposes, each Authorized Cloud Environment instance is counted as a “System”. “Basic Limited” and “Premier Limited” support is not available for Authorized Cloud Environment instances greater than  eight Azure vCPUs.

Resources:

http://www.oracle.com/us/corporate/pricing/cloud-licensing-070579.pdf

https://azure.microsoft.com/nl-nl/blog/introducing-the-new-dv3-and-ev3-vm-sizes/

http://webcache.googleusercontent.com/search?q=cache:http://www.oracle.com/us/corporate/pricing/cloud-licensing-070579.pdf

The post Oracle, Azure Cloud and licensing with hyperthreading enabled. appeared first on AMIS Oracle and Java Blog.

Video: Oracle X$TRACE, Wait Event Internals and Background Process Communication

Tanel Poder - Wed, 2018-01-24 21:33

I have uploaded the the video of my Secret Hacking Session: Oracle X$TRACE, Wait Event Internals and Background Process Communication to my Oracle performance & troubleshooting Youtube channel.

The slides are in Slideshare.

Enjoy!

 

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

New OA Framework 12.2.6 Update 9 Now Available

Steven Chan - Wed, 2018-01-24 12:36

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

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

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

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

Oracle Application Framework (FWK) Release 12.2.6 Bundle 9 (Patch 27113797:R12.FWK.C)

Where is this update documented?

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

Who should apply this patch?

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

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS Release 12.2.6 bundle patches.

In addition, this latest bundle patch includes fixes for the following issues:

  • Radio button doesn't get selected as expected if the radio buttons in a radio group are bonded with View Object.

  • Advanced table failed to render when the advanced table contains an attachment bean which uses shared AK Entity definition.

  • Opening an inline attachment of type URL shows '404 Not Found' error page when the file name has backslash in it.

  • Import/Export personalization page hangs due to performance issue.

  • Message List of Values (LOV) choice bean throws null pointer exception in Screen Reader accessibility mode.

Related Articles

Categories: APPS Blogs

Getting started with Spring Boot microservices. Why and how.

Amis Blog - Wed, 2018-01-24 11:15

In order to quickly develop microservices, Spring Boot is a common choice. Why should I be interested in Spring Boot? In this blog post I’ll give you some reasons why looking at Spring Boot is interesting and give some samples on how to get started quickly. I’ll shortly talk about microservices, move on to Spring Boot and end with Application Container Cloud Service which is an ideal platform to run and manage your Spring Boot applications on. This blog touches many subjects but they fit together nicely. You can view the code of my sample Spring Boot project here. Most of the Spring Boot knowledge has been gained by the following free course by Java Brains.

Microservices

Before we go deeper into why Spring Boot for microservices, we of course first need to know what microservices are. An easy question to ask but a little complex to answer in a few lines in this blog. One of the first people describing characteristics of microservices and actually calling them that was Martin Fowler in 2014. What better source to go back to then the articles he has written. For example here.

‘In short, the microservice architectural style is an approach to developing a single application as a suite of small services, each running in its own process and communicating with lightweight mechanisms, often an HTTP resource API. These services are built around business capabilities and independently deployable by fully automated deployment machinery. There is a bare minimum of centralized management of these services, which may be written in different programming languages and use different data storage technologies.’

— James Lewis and Martin Fowler

Of course there are a lot of terms involved in this definition

  • It is an architectural style for developing a single application.
  • A suite of small services each running in its own process.
  • Communicating with lightweight mechanisms, often HTTP.
  • Build around business capabilities. Look up bounded context.
  • A bare minimum of centralized management of these services. This implies no application server which provides centralized management of the applications running on it.
  • May be written in different programming languages or use different storage technologies.

A microservice architectural style also has several characteristics. It is very interesting to look at such an architecture in more detail like for example the OMESA initiative to help you get started. As is of course obvious and true with all architectural styles, you will gain most benefits when doing it right. It is however often not trivial to determine ‘right’.

Spring Boot microservices Spring Boot features and microservice principles

Spring Boot is based on certain principles which align with microservice architecture. The primary goals of Spring Boot:

  • Provide a radically faster and widely accessible getting started experience for all Spring development.
  • Be opinionated out of the box, but get out of the way quickly as requirements start to diverge from the defaults.
  • Provide a range of non-functional features that are common to large classes of projects (e.g. embedded servers, security, metrics, health checks, externalized configuration).
  • Absolutely no code generation and no requirement for XML configuration.

The features provided by Spring Boot also make it a good fit to implement microservices in.

  • Spring Boot applications can contain an embedded Tomcat server. This is a completely standalone Tomcat container which has its configuration as being part of the application.
  • Spring Boot is very well suited to create light weight JSON/REST services.
  • Features like health checks are provided. Spring Boot offers Actuator. A set of REST services which allow monitoring and management. Look here. Also externalized configuration can be used. Few centralized management features are required.
  • Since different storage techniques can be used, Spring provides Spring Data JPA. JPA is Java Persistence API. This API provides ORM capabilities to make working with relational databases easier (mostly vendor independent, supports EclipseLink, Hibernate and several others).

Example of an Actuator call to request health status

Easy to implement API design patterns

There are plenty of descriptions online to provide API design guidelines. See for example here. An example API URL can be something like: http://api.yourservice.com/v1/companies/34/employees. Notice the structure of the URL which amongst other things contains a version number. Oracle Mobile Cloud Service documentation also has several design recommendations. See here. These design considerations are of course easily implemented in Spring Boot.

See for example the below code sample:

A simple Spring Boot controller

You can see how the HTTP operations are used and the way method calls are mapped to URLs. Added benefit of this sample is that it also shows how to access the body of the request message.

Integration with backend systems

Spring Boot integrates with JPA. JPA provides an API to easily do ORM. It allows you to work with objects in Java which are backed by database data. For basic CRUD operations, the effort required to implement JPA in Spring Boot is minimal.

You only need three things to do simple CRUD operations when using the embedded Derby database.

  • An annotated entity. You only require two annotations inside your POJO. @Entity to annotate the class and @Id to indicate the variable holding primary key.
  • A repository interface extending CrudRepository (from org.springframework.data.repository)
  • Inside your service, you can use the @Autowired annotation to create a local variable with an instance of the repository.

Connection details for the embedded Derby server are not required. They are for external databases though. If you want to connect to an Oracle database, read the following here.

Pretty comparable to microservices on Node

Node or Spring Boot? This is of course a topic which has many opinions. Many blogs have been written to compare the 2. See for example here.

In several aspects, Spring Boot beats Node.js.

  • Performance. Read the following article here. Spring Boot microservices can achieve higher throughput than similar services on Node.js.
  • Maturity. Spring has a long history of running Enterprise Applications. Node.js can also be used but is less mature.
  • Security. Spring and Spring Boot are clearly better than Node.js. For example, Kerberos support in Node is limited while Spring Boot provides easy abstractions for several security implementations amongst which Kerberos tokens.
  • RDBMS. This is more easy to use in Spring Boot because of JPA.

Node.js beats Spring Boot also in several aspects

  • Build/package management. People who have experience with Maven and NPM often prefer NPM
  • UI. JavaScript is of course the language of choice for front-end applications. The Java based frameworks such as the JSF variants by far do not have the productivity as for example a framework like AngularJS.
  • Document databases like MongoDB. When you can work with JSON, JavaScript code running on Node.js makes it very easy to interact with the database.

Spring Boot, being in the Java ecosystem can also be combined with for example Ratpack. See here. Ratpack provides a high throughput, non-blocking web layer. The syntax is similar to how you would code Node.js code. This is of course not so much of an argument for Spring Boot since modules on Node.js provides similar functionality. Both solutions are more alike than you would think on first glance.

It depends probably mainly on the skills you have available and your application landscape if you would choose Node.js or Spring Boot. If you’re from the JavaScript world, you might prefer to write your microservices on Node.js. If you’re from the Java world, you will prefer Spring Boot. It is important to understand there is not an obvious superior choice whether to go for Node.js or Spring Boot.

Getting started with Spring Boot

The easiest way to get started is first watch some online courses. For example this one from Java Brains. I’ll provide some nice to knows below.

Spring Tool Suite (STS)

As for an IDE, every Java IDE will do, however, since Spring Boot is build on top of Spring, you could consider using Spring Tool Suite (STS). This is a distribution of Eclipse with many specific Spring features which make development of Spring applications easier.

Spring Initializr

An alternative way to get your start project is to go to https://start.spring.io/, indicate your dependencies and click the Generate project button. This will generate a Maven or Gradle project for you with the required dependencies already added.

With STS, you can also use the Spring Initializr functionality easily.

Spring Boot CLI

Spring Boot CLI offers features to create and run Groovy Spring Boot applications. Groovy requires less code than Java to do similar things. It is a script language which runs on the JVM and from Groovy you can access regular Java classes/libraries.

You can for example create an application like:

@RestController
class HelloWorldClass {

@RequestMapping(“/”)
String home() {
return “Hello World!”
}
}

Save this as a Groovy script (e.g. app.groovy) and run it with Spring Boot CLI like: spring run app.groovy

Getting actually started

To get started with Spring Boot, you have to add some entries to your pom.xml file and you’re ready to go. Easiest is to use the New Spring Starter project from STS since it will generate a pom, a main and test class for you. That is what I used for my sample project here.

A simple pom.xml to get started with Spring Boot

Spring and Oracle

Spring is a very common Java framework. You can find traces of it in several Oracle products and features. Below some examples. If you look in other Oracle products, especially those who are Java based, I expect you will find many more examples.

SOA Suite

For example in Oracle SOA Suite.

  • SOA Suite itself under the covers uses Spring
  • SOA Suite can use Spring components
Massive Open Online Course

Oracle uses Spring Boot in courses it provides. For example in the Develop RESTful Java Microservices deployable on Oracle Cloud MOOC.

Application Container Cloud Service (ACCS)

ACCS has been around for a while. Together with Spring Boot, they provide an ideal combination to get your microservices developed and running quickly.

Application Container Cloud Service provides the features of The Twelf-Factor App out of the cloudy box your application so you don’t have to develop these yourself. These of course also align with the microservice principles like executing apps as stateless processes.

If you want to use ACCS with Spring Boot, there are two ways you can deploy your Spring Boot application.

  • You can create a WAR file by specifying war in the packaging tag in the pom.xml file. Next you can deploy this WAR file as a Java EE Web Application. This runs WebLogic in the background. Read more here.
  • You can create a JAR file by specifying jar in the packaging tag in the pom.xml file. Next you can run this JAR file directly since you’ll get an embedded Tomcat with it and can run it as a Java SE application. The configuration will be part of the application here.

I’ve not compared both options in detail but I can imagine if you want to run a ‘micro’-service, an entire WebLogic Server might make it more of a ‘macro’-service.

The post Getting started with Spring Boot microservices. Why and how. appeared first on AMIS Oracle and Java Blog.

Explain Plan format

Yann Neuhaus - Wed, 2018-01-24 10:57

The DBMS_XPLAN format accepts a lot of options, which are not all documented. Here is a small recap of available information.

The minimum that is displayed is the Plan Line Id, the Operation, and the Object Name. You can add columns and/or sections with options, such as ‘rows’, optionally starting with a ‘+’ like ‘+rows’. Some options group several additional information, such ‘typical’, which is also the default, or ‘basic’, ‘all’, ‘advanced’. You can choose one of them and remove some columns, with ‘-‘, such as ‘typical -rows -bytes -cost -plan_hash -predicate -remote -parallel -partition -note’. Finally, from an cursor executed with plan statistics, you can show all execution statistics with ‘allstats’, and the last execution statistics with ‘allstats last’. Subsets of ‘allstats’ are ‘rowstats’, ‘memstats’, ‘iostats’, buffstats’.

Of course, the column/section is displayed only if the information is present.

This blog post shows what is display by which option, as of 12cR2, and probably with some missing combinations.

+plan_hash, or BASIC


PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 1338588353

Plan hash value: is displayed by ‘basic +plan_hash’ or ‘typical’ or ‘all’ or ‘advanced’

+rows +bytes +cost +partition +parallel, or TYPICAL


-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 287 | 19516 | 5 (20)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 287 | 19516 | 5 (20)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 287 | 19516 | 5 (20)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 287 | 19516 | 4 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 287 | 19516 | 4 (0)| 00:00:01 | | | Q1,01 | P->P | RANGE |
|* 6 | HASH JOIN | | 287 | 19516 | 4 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 82 | 2460 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST| :TQ10000 | 82 | 2460 | 2 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 12 | REMOTE | DEPT | 82 | 2460 | 2 (0)| 00:00:01 | | | LOOPB~ | R->S | |
-----------------------------------------------------------------------------------------------------------------------------------

Rows or E-Rows: is displayed by ‘basic +rows’ or ‘typical’ or ‘all’ or ‘advanced’
Bytes or E-Bytes: is displayed by ‘basic +bytes’ or ‘typical’ or ‘all’ or ‘advanced’
Cost: is displayed by ‘basic +cost’ or ‘typical’ or ‘all’ or ‘advanced’
TmpSpc or E-Temp: is displayed by ‘basic +bytes’ or ‘typical’ or ‘all’ or ‘advanced’
Time or E-Time: is displayed by ‘typical’ or ‘all’ or ‘advanced’
Pstart/Pstop: is displayed by ‘basic +partition’ or ‘typical’ or ‘all’ or ‘advanced’
TQ/Ins, IN-OUT, PQ Distrib: is displayed by ‘basic +parallel’ or ‘typical’ or ‘all’ or ‘advanced’

The ‘A-‘ and ‘E-‘ prefixes are used when displaying execution statistics, to differentiate estimations with actual numbers

+alias


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
1 - SEL$58A6D7F6
8 - SEL$58A6D7F6 / EMP@SEL$1
12 - SEL$58A6D7F6 / DEPT@SEL$1

Query Block Name / Object Alias: is displayed by ‘basic +alias’ or ‘typical +alias’ or ‘all’ or ‘advanced’

+outline


Outline Data
-------------
 
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(@"SEL$58A6D7F6" "DEPT"@"SEL$1" NONE BROADCAST)
USE_HASH(@"SEL$58A6D7F6" "DEPT"@"SEL$1")
LEADING(@"SEL$58A6D7F6" "EMP"@"SEL$1" "DEPT"@"SEL$1")
FULL(@"SEL$58A6D7F6" "DEPT"@"SEL$1")
FULL(@"SEL$58A6D7F6" "EMP"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE_LEAF(@"SEL$58A6D7F6")
ALL_ROWS
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Outline Data: is displayed by ‘basic +outline’ or ‘typical +outline’ or ‘all +outline’ or ‘advanced’

+peeked_binds


Peeked Binds (identified by position):
--------------------------------------
 
1 - :X (VARCHAR2(30), CSID=873): 'x'

Peeked Binds: is displayed by ‘basic +peeked_binds’ or ‘typical +peeked_binds’ or ‘all +outline’ or ‘advanced’

+predicate


Predicate Information (identified by operation id):
---------------------------------------------------
 
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Predicate Information: is displayed by ‘basic +predicate’ or ‘typical’ or ‘all’ or ‘advanced’

+column


Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
1 - INTERNAL_FUNCTION("DEPT"."DEPTNO")[22], "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14], "DEPT"."LOC"[VARCHAR2,13] 2 - (#keys=0) INTERNAL_FUNCTION("DEPT"."DEPTNO")[22], "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14], "DEPT"."LOC"[VARCHAR2,13] 3 - (#keys=1) INTERNAL_FUNCTION("DEPT"."DEPTNO")[22], "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14], "DEPT"."LOC"[VARCHAR2,13] 4 - INTERNAL_FUNCTION("DEPT"."DEPTNO")[22], "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "DEPT"."DNAME"[VARCHAR2,14], "DEPT"."LOC"[VARCHAR2,13]

Column Projection Information: is displayed by ‘basic +projection’ or ‘typical +projection’ or ‘all’ or ‘advanced’

+remote


Remote SQL Information (identified by operation id):
----------------------------------------------------
 
12 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "DEPT" (accessing 'LOOPBACK' )

Remote SQL Information: is displayed by ‘basic +remote’ or ‘typical’ or ‘all’ or ‘advanced’

+metrics


Sql Plan Directive information:
-------------------------------
 
Used directive ids:
9695481911885124390

Sql Plan Directive information: is displayed by ‘+metrics’

+note

The Note section can show information about SQL Profiles, SQL Patch, SQL Plan Baseline, Outlines, Dynamic Sampling, Degree of Parallelism, Parallel Query, Parallel DML, Create Index Size, Cardinality Feedback, Rely Constraints used for transformation, Sub-Optimal XML, Adaptive Plan, GTT private statistics,…


Note
-----
- Degree of Parallelism is 2 because of table property
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
- this is an adaptive plan (rows marked '-' are inactive)

Note: is displayed by ‘basic +note’ or ‘typical’ or ‘all’ or ‘advanced’

+adaptive


---------------------------------------------------------------------------------------
| Id | Operation | Name |Starts|E-Rows| A-Rows|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |
| 1 | HASH UNIQUE | | 1 | 1 | 0 |
| * 2 | HASH JOIN SEMI | | 1 | 1 | 0 |
|- 3 | NESTED LOOPS SEMI | | 1 | 1 | 7 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 7 |
| * 5 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 7 |
|- * 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 0 | 1 | 0 |
|- * 7 | INDEX RANGE SCAN | EMP_DEP_IX | 0 | 10 | 0 |
| * 8 | TABLE ACCESS FULL | EMPLOYEES | 1 | 1 | 1 |
---------------------------------------------------------------------------------------

Inactive branches of adaptive plan: is displayed by ‘+adaptive’

+report


Reoptimized plan:
-----------------
This cursor is marked for automatic reoptimization. The plan that is
expected to be chosen on the next execution is displayed below.

Reoptimized plan: is displayed by ‘+report’

ALLSTATS


---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------

Starts: is displayed by ‘basic +rowstats’, ‘basic +allstats’
A-Rows: is displayed by ‘basic +rowstats’, ‘basic +allstats’
A-Time: is displayed by ‘typical +rowstats’, ‘basic +allstats’
Buffers, Reads, Writes: is displayed by ‘basic +buffstats’, ‘basic +iostats’, ‘basic +allstats’
OMem, 1Mem, Used-Mem, O/1/M, Used-Mem: is displayed by ‘basic +memstats’, ‘basic +allstats’
Max-Tmp,Used-Tmp is displayed by ‘basic +memstats’, ‘typical +allstats’

With summed stats, O/1/M and Max-Tmp are used for the headers. With last stats, Used-Mem and Used-Tmp.

 

Cet article Explain Plan format est apparu en premier sur Blog dbi services.

Using /*insert*/ and SET SQLFORMAT

Tom Kyte - Wed, 2018-01-24 09:06
Hi Tom, I am interested in using the SQL formatting option - /*insert*/ to create an insert script. Your example of a simple insert script - where one pulls from a single table and creates an insertion script of that table - bypassing having ...
Categories: DBA Blogs

geenrate xml from relational data

Tom Kyte - Wed, 2018-01-24 09:06
I want to export dept/emp table to xml file as follows <envelope> <dept> <deptName> </deptName> <emp> <empNo> </empNo> <lastName> </lastName> <salary > </salary> </emp> </dept> ...... ..... </envelope> And another program to rea...
Categories: DBA Blogs

locking disconnected tables

Tom Kyte - Wed, 2018-01-24 09:06
Hi Tom, I have this problem: I need to "lock for update" records in two disconnected tables. For "disconnected" I mean two tables that haven't any relation, for example the tables EMPLOYEE and FOLDER: when I have to update an employee record I woul...
Categories: DBA Blogs

"APOS;" appearinig in SQL*PLus output. Want actual apostrophe instead.

Tom Kyte - Wed, 2018-01-24 09:06
How can I suppress the appearance of "apos;" and similar web-friendly tags from my SQL*Plus output. In place of "apos;" I'm actually looking for the apostrophe "'". Thank you!! - Mike Kemp
Categories: DBA Blogs

Upgrade 11204 to 12201 with standby in place

Tom Kyte - Wed, 2018-01-24 09:06
We are upgrading 11204 Databases to 12201. We build new linux server, create 12201 DB, expdp from 11204 then impdp into 12201. We don't do "in place" upgrade. To upgrade a master with physical standby, what will you recommend ? I think the be...
Categories: DBA Blogs

clob data conversion

Tom Kyte - Wed, 2018-01-24 09:06
I Have table, which has clob data type, value stored is in hexadecimal data type. Please let me know how can i convert clob data to varchar
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator