Feed aggregator

High Soft Parsing

Tom Kyte - Tue, 2016-07-05 02:06
Hi Tom, We are experiencing high Soft parsing in our databases , though we have enabled session cached cursors and all our SQL/PL SQL blocks using bind variables. We are using Pro C as a host language interact with the back end database. Load...
Categories: DBA Blogs

Can we do a CTAS (Create table as) without the NOT NULL constraints?

Tom Kyte - Tue, 2016-07-05 02:06
Can we do a CTAS (Create table as) and create the new table without the NOT NULL constraints? select * from v$version; Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE ...
Categories: DBA Blogs

Format the Number for display

Tom Kyte - Tue, 2016-07-05 02:06
<code>Hello Guru, Q1) I need to display numbers from a database in a specific format. Table Tn (n number(6,3)); The format is 999.999 SQL> insert into tn values( 123.123) ; 1 row created. SQL> insert into tn values(0) ; 1 row created. SQL...
Categories: DBA Blogs

NOLOGGING

Tom Kyte - Tue, 2016-07-05 02:06
Hi, We have a problem, each day our DR setup is getting about 300 GB of data. We have found this is due to the huge amount of archive logs being written. We have certain bulk operations that are taking place, most of which are from bulk deletes or...
Categories: DBA Blogs

Retreive userid who has taken training more than once

Tom Kyte - Tue, 2016-07-05 02:06
Hi Tom, I have 2 tables user and training User Userid Username Trainingid 1 A 1 2 B 2 3 C 2 4 D 3 5 E 2 Training Trainingid trainername userid countoftrainings Date 1 X 1 2 ...
Categories: DBA Blogs

Script to suggest FK indexes

Yann Neuhaus - Mon, 2016-07-04 10:55

In Oracle, when the referenced key is deleted (by delete on parent table, or update on the referenced columns) the child tables(s) are locked to prevent any concurrent insert that may reference the old key. This lock is a big issue on OLTP applications because it’s a TM Share lock, usually reserved for DDL only, and blocking any modification on the child table and blocking some modifications on tables that have a relationship with that child table. This problem can be be overcome when an index structure which allows to find concurrent inserts that may reference the old value. Here is the script I use to find which index is missing.

The idea is not to suggest to index all foreign keys for three reasons:

  • when there are no delete or update in parent side, you don’t have that locking issue
  • when there is minimal write activity on child side, the lock may not have big consequence
  • you probably have indexes build for performance reasons that can be used to avoid locking even when they have more columns or have different column order

The idea is not to suggest an index for each locking issue but only when blocking locks have been observed. Yes, it is a reactive solution, but proactive ones cannot be automatic. If you know your application well and then you know what you ave to index, then you don’t need this script. If you don’t, then proactive suggestion will suggest too many indexes.

Here is the kind of output that I get with this script:
-- DELETE on APP1.GCO_GOOD has locked APP1.FAL_TASK in mode 5 for 8 minutes between 14-sep 10:36 and 14-sep 10:44
-- blocked statement: DELETE FAL LOT LOT WHERE C FAB TYPE AND EXISTS SELECT
-- blocked statement: UPDATE DOC POSITION DETAIL SET DOC POSITION DETAIL ID B
-- blocked statement: delete from C AP GCO GOOD where rowid doa rowid
-- blocked statement: DELETE FROM FAL LOT WHERE FAL LOT ID B
-- blocked statement: DELETE FROM FAL TASK LINK PROP WHERE FAL LOT PROP ID B
-- blocked statement: INSERT INTO FAL LOT PROGRESS FAL LOT PROGRESS ID FAL LOT
-- blocked statement: insert into FAL TASK LINK FAL SCHEDULE STEP ID
-- FK chain: APP1.GCO_GOOD referenced by(cascade delete) APP1"."GCO_SERVICE referenced by(cascade set null) APP1"."FAL_TASK (APP1.FAL_TASK_S_GCO_SERV) unindexed
-- FK column GCO_GCO_GOOD_ID
-- Suggested index: CREATE INDEX ON "APP1"."FAL_TASK" ("GCO_GCO_GOOD_ID");
-- Other existing Indexes: CREATE INDEX "APP1"."FAL_TASK_S_DIC_FR_TASK_COD7_FK" ON "APP1"."FAL_TASK" ("DIC_FREE_TASK_CODE7_ID")
-- Other existing Indexes: CREATE INDEX "APP1"."FAL_TASK_S_DIC_FR_TASK_COD9_FK" ON "APP1"."FAL_TASK" ("DIC_FREE_TASK_CODE9_ID")
-- Other existing Indexes: CREATE INDEX "APP1"."FAL_TASK_S_PPS_TOOLS13_FK" ON "APP1"."FAL_TASK" ("PPS_TOOLS13_ID")

I’ll detail each part.

ASH

Yes we have to detect blocking issues from the past and I use ASH for that. If you don’t have Diagnostic Pack, then you have to change the query with another way to sample V$SESSION.
-- DELETE on APP1.GCO_GOOD has locked APP1.FAL_TASK in mode 5 for 8 minutes between 14-sep 10:36 and 14-sep 10:44
-- blocked statement: DELETE FAL LOT LOT WHERE C FAB TYPE AND EXISTS SELECT
-- blocked statement: UPDATE DOC POSITION DETAIL SET DOC POSITION DETAIL ID B
-- blocked statement: delete from C AP GCO GOOD where rowid doa rowid
-- blocked statement: DELETE FROM FAL LOT WHERE FAL LOT ID B
-- blocked statement: DELETE FROM FAL TASK LINK PROP WHERE FAL LOT PROP ID B
-- blocked statement: INSERT INTO FAL LOT PROGRESS FAL LOT PROGRESS ID FAL LOT
-- blocked statement: insert into FAL TASK LINK FAL SCHEDULE STEP ID

The first part of the output comes from ASH and detects the blocking situations: which statement, how long, and the statements that were blocked.
This part of the script will probably need to be customized: I join with DBA_HIST_SQL_PLAN supposing that the queries have been captured by AWR as long running queries. I check last 15 days of ASH. You may change those to fit the blocking situation encountered.

Foreign Key

Then, we have to find the unindexed foreign key which is responsible for those locks.

-- FK chain: APP1.GCO_GOOD referenced by(cascade delete) APP1"."GCO_SERVICE referenced by(cascade set null) APP1"."FAL_TASK (APP1.FAL_TASK_S_GCO_SERV) unindexed
-- FK column GCO_GCO_GOOD_ID

Here you see that it’s not easy. Actually, all scripts I’ve seen do not detect that situation where the CASCADE SET NULL cascades the issue. Here “APP1″.”GCO_SERVICE” has its foreign key indexed but the SET NULL, even when not on the referenced column, locks the child (for no reason as far as I know, but it does).
My script goes up to a level 10 using a connect by query to detect this situation.

Suggested Index

The suggested index is an index on the foreign key column:

-- Suggested index: CREATE INDEX ON "APP1"."FAL_TASK" ("GCO_GCO_GOOD_ID");

This is only a suggestion. Any regular index that starts with foreign key column in whatever order can be used to avoid the lock.
Remember to think about performance first. The index may be used to navigate from parent to child.

Existing Index

Finally, when adding an index it’s good to check if there are other indexe that would not be needed anymore, so my script displays all of them.
If you think that some indexes are not required, remember that in 12c you can make them invisible for a while and you will be able to bring them back to visible quickly in case of regression.

Script

Here is the script. Sorry, no comments on it yet and a few display things to change. Just try it, it’s just a query on AWR (need Diag. Pack) and table/index/constraint metadata. You can customize it and don’t hesitate to comment if you have ideas to improve. I used it in several environments and it has always found the chain of foreign key that is responsible for an ‘enq: TM’ blocking situation. And believe me this is not always easy to do just by looking at the data model.


set serveroutput on
declare
procedure print_all(s varchar2) is begin null;
dbms_output.put_line(s);
end;
procedure print_ddl(s varchar2) is begin null;
dbms_output.put_line(s);
end;
begin
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
for a in (
select count(*) samples,
event,p1,p2,o.owner c_owner,o.object_name c_object_name,p.object_owner p_owner,p.object_name p_object_name,id,operation,min(p1-1414332420+4) lock_mode,min(sample_time) min_time,max(sample_time) max_time,ceil(10*count(distinct sample_id)/60) minutes
from dba_hist_active_sess_history left outer join dba_hist_sql_plan p using(dbid,sql_id) left outer join dba_objects o on object_id=p2 left outer join dba_objects po on po.object_id=current_obj#
where event like 'enq: TM%' and p1>=1414332420 and sample_time>sysdate-15 and p.id=1 and operation in('DELETE','UPDATE','MERGE')
group by
event,p1,p2,o.owner,o.object_name,p.object_owner,p.object_name,po.owner,po.object_name,id,operation
order by count(*) desc
) loop
print_ddl('-- '||a.operation||' on '||a.p_owner||'.'||a.p_object_name||' has locked '||a.c_owner||'.'||a.c_object_name||' in mode '||a.lock_mode||' for '||a.minutes||' minutes between '||to_char(a.min_time,'dd-mon hh24:mi')||' and '||to_char(a.max_time,'dd-mon hh24:mi'));
for s in (
select distinct regexp_replace(cast(substr(sql_text,1,2000) as varchar2(60)),'[^a-zA-Z ]',' ') sql_text
from dba_hist_active_sess_history join dba_hist_sqltext t using(dbid,sql_id)
where event like 'enq: TM%' and p2=a.p2 and sample_time>sysdate-90
) loop
print_all('-- '||'blocked statement: '||s.sql_text);
end loop;
for c in (
with
c as (
select p.owner p_owner,p.table_name p_table_name,c.owner c_owner,c.table_name c_table_name,c.delete_rule,c.constraint_name
from dba_constraints p
join dba_constraints c on (c.r_owner=p.owner and c.r_constraint_name=p.constraint_name)
where p.constraint_type in ('P','U') and c.constraint_type='R'
)
select c_owner owner,constraint_name,c_table_name,connect_by_root(p_owner||'.'||p_table_name)||sys_connect_by_path(decode(delete_rule,'CASCADE','(cascade delete)','SET NULL','(cascade set null)',' ')||' '||c_owner||'"."'||c_table_name,' referenced by') foreign_keys
from c
where level<=10 and c_owner=a.c_owner and c_table_name=a.c_object_name
connect by nocycle p_owner=prior c_owner and p_table_name=prior c_table_name and ( level=1 or prior delete_rule in ('CASCADE','SET NULL') )
start with p_owner=a.p_owner and p_table_name=a.p_object_name
) loop
print_all('-- '||'FK chain: '||c.foreign_keys||' ('||c.owner||'.'||c.constraint_name||')'||' unindexed');
for l in (select * from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name) loop
print_all('-- FK column '||l.column_name);
end loop;
print_ddl('-- Suggested index: '||regexp_replace(translate(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name,c.owner),chr(10)||chr(13),' '),'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*','CREATE INDEX ON \1 \3;'));
for x in (
select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),' ')) ddl
from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
and column_name in (select column_name from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name)
)
loop
print_ddl('-- Existing candidate indexes '||x.ddl);
end loop;
for x in (
select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)||chr(13),' ')) ddl
from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
)
loop
print_all('-- Other existing Indexes: '||x.ddl);
end loop;
end loop;
end loop;
end;
/

I didn’t take time to document/comment the script but don’t hesitate to ask what you don’t understand there.

You should not see any ‘enq: TM’ from an OLTP application. If you have them, even short, they will become problematic one day. It’s the kind of thing that can block the whole database.

 

Cet article Script to suggest FK indexes est apparu en premier sur Blog dbi services.

oracle text with order by clause

Tom Kyte - Mon, 2016-07-04 07:46
Dir Sir: I am developing an anti-plagiarism system, I am using Oracle text to search for my text. my corpus contains more than 30 million of records. I want to check my document against this corpus. I want to fetch the highest score only, not all...
Categories: DBA Blogs

Can be differentiate cascade delete or statement(delete from query) inside the table trigger

Tom Kyte - Mon, 2016-07-04 07:46
Hi Tom, i have question about cascade delete, How its work internally and how to differentiate that row by delete from delete statement on child table or row deleted by a cascade delete. because i am facing problem and try to solve Mutating error...
Categories: DBA Blogs

Partitioning Questions

Tom Kyte - Mon, 2016-07-04 07:46
Hello I would like to post a question related to which partitioning I can go for based on the below scenario. I have 2 tables named table1 and table2. Both the tables are having customer I'd and user id column. The hierarchy is multiple users...
Categories: DBA Blogs

the maximum number of logical operators(AND/OR) can be used in where clause

Tom Kyte - Mon, 2016-07-04 07:46
Hi Tom, How many maximum number of logical operators(either AND/OR) are allowed in where clause in select statement. eg select * from employees where first_name = 'abc' or first_name = 'cde' or first_name = 'def' or .... .... .......
Categories: DBA Blogs

Composite partition

Tom Kyte - Mon, 2016-07-04 07:46
Hi Team, We have a requirement to use composite partition for few partition from the list of partition. Please consider below create team where in we are using composite partition using 2 columns RELATED_CLOSED_REQ_STAMP and Request_Id. Main parti...
Categories: DBA Blogs

when to use bitmap or b tree index?

Tom Kyte - Mon, 2016-07-04 07:46
when to use bitmap or b*tree index? And which one is best for containing 1 Milion rows in a single table contain child-parent relationship?
Categories: DBA Blogs

Using existing exception names

Tom Kyte - Mon, 2016-07-04 07:46
You have define an exception type and named it as invalid_number on execution,your plsql code raises exception invalid_number internally.In this case what should you do in order to handle this kind of situation. 1..Exception when invalid_number. 2....
Categories: DBA Blogs

APEX session isolation across multiple browser tabs - Problem Solved (in APEX 5.1)

Joel Kallman - Mon, 2016-07-04 07:32
Since the genesis of Oracle Application Express, customers have asked for a way to open multiple browser tabs (or windows) of an APEX application and have the session state isolated between the respective tabs.  There is one and only one APEX session associated with a client, and because of this behavior in APEX, customers would find that the session state manipulated in one browser tab would collide with the session state of the other browser tab.

This has always been a vexing problem to solve for many years.  Back in 2007, I remember Carl Backstrom had spent countless hours researching for some handle or unique identifier to a browser window that we could correlate with a distinct browser session cookie, but he was never able to identify a feasible solution.  Customers have long asked for a solution, but all we were able to propose were rather cumbersome work arounds (ensure all items necessary for session state were posted with the page, or use the multiple DNS aliases "trick" for each tab).

In October 2015, our friends from BiLog arranged an informal meeting with a couple large enterprise customers from Croatia.  Goran, who was from one of the enterprise customers in the insurance industry, stated that the session management behavior of APEX presented a real problem for them.  Their typical scenario involved a sales representative who would meet with a customer in-person.  Because they wanted to offer insurance quotes or initiate insurance applications on multiple products, the sales representative would open up multiple tabs of their APEX application.  Of course, the session state across all of these tabs would collide and effectively corrupt the quoting process.  As Goran stated at the time, it became more and more difficult to justify the use of APEX because of this troublesome behavior.  I had no immediate answer, but I told him we would redouble our efforts and look at this problem again.

In February of this year, I had one of those lightbulb moments, and realized that we had been thinking about this problem the wrong way, and we needed to turn it inside out.  In APEX, there is always a single browser session cookie associated with an APEX session.  We were always trying to come up with a way to generate a new and differentiated browser session cookie every time a new tab was opened, and then associate this new browser session cookie with a new APEX session.  But the new approach was to simply keep the one and only one browser session cookie, and have this associated with multiple APEX sessions on the server.  I expressed my idea to the supremely intelligent Christian Neumueller of the APEX development team, and he went about with a masterful design and implementation of this feature.

In Application Express 5.1, we are introducing a new request to the APEX engine named APEX_CLONE_SESSION.  When requested from an existing APEX session, this will generate a new APEX session identifier and associate it with the existing browser session cookie.  Additionally, it will copy all of the session state values from the old session to the new session.  You, the developer, would have to provide a link for your end users to open up new browser tabs, and include APEX_CLONE_SESSION in the request of the URL.  So instead of your end users manually opening up a new tab from your APEX application, you would have to give them a prescribed way to open new tabs - could be a dynamic action or a button or a link.  The URL in the new tab should include APEX_CLONE_SESSION in the "Request" portion of the APEX URL.

An example URL would be:
f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APEX_CLONE_SESSION

Because we were a bit paranoid about this feature until we could thoroughly vet the security of it, by default, this capability is turned off.  You can override this setting for a specific workspace by using the Administration API:

apex_instance_admin.set_workspace_parameter(
p_workspace => 'JOELS_WORKSPACE',
p_parameter => 'CLONE_SESSION_ENABLED',
p_value => 'Y');

or you can enable it for the entire instance using:
apex_instance_admin.set_parameter(
p_parameter => 'CLONE_SESSION_ENABLED',
p_value => 'Y');

This feature is enabled instance-wide on the Application Express 5.1 Early Adopter site at https://apexea.oracle.com.  We would welcome your feedback about this feature.  And if you're reading this blog post after APEX 5.1 is generally available, please feel free to try it in your own APEX 5.1 (or later) instance or on https://apex.oracle.com.

What is the DUAL Table in Oracle?

Complete IT Professional - Mon, 2016-07-04 06:00
Have you seen SQL statements with the word DUAL in them? Wondering what it is? Find out what the dual table in Oracle is in this article. The DUAL Table in Oracle You might have seen some SQL statements with the keyword DUAL in them. These would have been SELECT statements, and the DUAL keyword […]
Categories: Development

Persistent storage in Oracle Compute Cloud (IaaS)

&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;lt;span...

We share our skills to maximize your revenue!
Categories: DBA Blogs

SOA Server (12c) not startable from script-created domain.

Darwin IT - Mon, 2016-07-04 04:45
If you're a regular reader of this blog, you've noticed that I've been busy with creating soasuite/osb installations with weblogic domains in a scripted way.

I also optimized my start and stop scripts, that I'll post soon. All this work keeps me from writing on my BPEL book, so for those who are interested in my next episode, I hope you're patient.

But to get to that, it would be nice to have a running SOA Server in my automatically created domain. And unfortunately, that doesn't get up in running mode. Somewhere in the process it fails to start services and switches over to ADMIN mode.

I did some investigation and ran in to the following exception in the server log:

####<Jul 4, 2016 3:30:49 AM EDT> <Error> <Deployer> <darlin-vce-db.darwin-it.local> <SoaServer1> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <f2c434e6-7b67-4a8c-9e2f-8b886152cced-0000000a> <1467617449212> <[severity-value: 8] [rid: 0] [partition-id: 0] [partition-name: DOMAIN] > <BEA-149205> <Failed to initialize the application "OracleBPMBACServerApp" due to error weblogic.management.DeploymentException: Could not find OSGi framework with name bac-svnserver-osgi-framework
weblogic.management.DeploymentException: Could not find OSGi framework with name bac-svnserver-osgi-framework
at weblogic.osgi.internal.OSGiAppDeploymentExtension.deployBundlesIntoFramework(OSGiAppDeploymentExtension.java:126)
at weblogic.osgi.internal.OSGiAppDeploymentExtension.prepare(OSGiAppDeploymentExtension.java:245)
at weblogic.application.internal.flow.AppDeploymentExtensionFlow.prepare(AppDeploymentExtensionFlow.java:25)
at weblogic.application.internal.BaseDeployment$1.next(BaseDeployment.java:730)
at weblogic.application.utils.StateMachineDriver.nextState(StateMachineDriver.java:45)
at weblogic.application.internal.BaseDeployment.prepare(BaseDeployment.java:242)
at weblogic.application.internal.EarDeployment.prepare(EarDeployment.java:67)
...

Apparently there was a problem with the OSGi Framework with the name 'bac-svnserver-osgi-framework' that couldn't be found.

OSGi in a domain with both OSB and BPM was a problem in 12cR1. Several blogs are written about that (like this and this one). However, the problem in those blogs is solved soon after the release of 12cR1. In my case something different is happening.

I took a look and it turns out that the 'bac-svnserver-osgi-framework' was not targetted to the SOAServer, although it was targetted to the AdminServer. To check that, navigate to Services->OSGi Frameworks in the Domain Structure:

Here you see the result of my retargetting. If it does not show the SoaCluster or SOA Server, then click on 'bac-svnserver-osgi-framework' and click on the Targets  tab:

At least check the SoaCluster or, if no cluster the SoaServer1. I unchecked the AdminServer because in the earlier posts unchecking the AdminServer was one proposed solution to resolve the OSB-BPM SharedDomain URL issue. But I'm not sure it's needed.

Click Save and activate the changes and, in my case at least, the SoaServer should be startable.


ACS, SQL Patch and SQL Plan Baseline

Hemant K Chitale - Mon, 2016-07-04 03:49
Marko Sutic's blog post on Adaptive Cursor Sharing and SQL Plan Baselines, with an example of SQL Patch as well.
.
.
.
Categories: DBA Blogs

Host Name Length Restrictions in E-Business Suite 12.1 and 12.2

Steven Chan - Mon, 2016-07-04 02:05

Oracle E-Business Suite Release 12 has a restriction on the lengths of the host names you can use.  The exact restriction depends on which of the following EBS Release Update Packs you have applied:

  • EBS Release Update Pack 12.2.5
  • EBS Release Update Pack 12.2.4, 12.2.3, 12.2.2, 12.1.3, 12.1.1

Each of these will be considered in turn.

Restriction in EBS Release Update Pack 12.2.5
When you run Rapid Install, you must ensure that the host names of your database node and primary applications node do not exceed 30 characters in length. Restriction in EBS Release Update Packs 12.2.4, 12.2.3, 12.2.2, 12.1.3, 12.1.1

The node names returned by the operating system for the database tier and application tier nodes must be no longer than 30 characters. If you configure your system to return only the host name for the node, then the host name must be no longer than 30 characters. If you configure your system to return the fully qualified domain name (FQDN), then the FQDN must be no longer than 30 characters, including the host name, domain name, and periods (.) used as separators.

Affected Rapid Install Screens

The following screens are where you need to ensure you enter host names that meet the current requirements.

Rapid Install Database Node Configuration Screen

Database Node

Rapid Install Primary Applications Node Configuration Screen

Primary Applications Node

This restriction is mentioned in Oracle E-Business Suite Installation Guide: Running Rapid Install and the release notes for the affected releases.

References

Related Articles
Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator