When analyzing oracle dump
kjxocdr: drop duplicate open [0x1530018][0x362c1],[TX].17 0x0x1b834e988 [held 0][req 3]
kjxocdr: drop duplicate open [0x1530018][0x362c1],[TX].17 0x0x1b834e988 [held 0][req 3]
kjddopr: skip converting lock 0x1d96bf530 dd_cnt 1
user session for deadlock lock 0x1d93e8088
pid=575 serial=63890 audsid=150018868 user: 77/MAS
O/S info: user: abcuser, term: unknown, ospid: 1234, machine: MAS2.abcd.com
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894661
Current SQL Statement:
delete from MAS.table_name where app_name = :1 and lst_updt_ts between :2 and :3
ENQUEUE DUMP REQUEST: from 0.27988 on [0x320020][0x182e1a],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x320020][0x182e1a],[TX]
Solution:
Problem: look at the table description …you can see…INITRANS 2 MAXTRANS 255
We need to increase INITRANS to 100 for better performance. When we have more concurrent sessions, this is important.
http://www.dba-oracle.com/t_initrans.htm
Oracle Finetuning
http://www.dba-oracle.com/art_sql_tune.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
Database design is tough Job. We have couple of options.
One thing sure is, using Oracle Data Modeler is painful.
Step 1: Create all tables and relationships easily in Oracle SQL Developer
Step 2: Take DDL and import it back to Oracle Data Modeler. Do naming conversions, …etc
Step 3: Generate DDL and bring it back to Oracle SQL Developer
Editing Tables in Oracle SQL Developer easy. We can compare easily with the help of following two screens.
30 minutes = 24 * 2
10 minutes = 24 * 6
5 minutes = 24 * 12
>select to_char(sysdate, ‘HH24:MI’),to_char(sysdate + 1/(24*6) – mod(sysdate – trunc(sysdate),1/(24*6)), ‘HH24:MI’) from dual;
select to_char(CAST(SYSTIMESTAMP AS DATE), ‘HH24:MI’),to_char(CAST(SYSTIMESTAMP AS DATE) + 1/(24*12) – mod(CAST(SYSTIMESTAMP AS DATE) – trunc(CAST(SYSTIMESTAMP AS DATE)),1/(24*12)), ‘HH24:MI’) from dual;
Thanks to Solomon Yakobson
https://forums.oracle.com/forums/thread.jspa?threadID=1006150
http://download.oracle.com/docs/cd/B19306_01/olap.102/b14346/dml_x_reserved007.htm
Many times we used this query to show total number of pages or some other purpose.
SELECT count(*) from TABLE_NAME
Pros – Provides exact number
Cons: Takes longer time, based on total number of records
SELECT table_name, num_rows FROM ALL_TABLES where table_name = ‘TABLE_NAME’
Cons: Not accurate (may be 1%) numbers in run time, but good enough for business purpose in many cases.
Pros: Pretty fast
SELECT table_name, num_rows FROM ALL_TABLES where OWNER=’asdf’ order by num_rows desc
-o-
Problem Statement: How to get ER Win diagram for existing database?
Search for this in Google “Oracle SQL Developer Data Modeler” and try. It is very good tool.
Sample table is having column as follows.
Column1
category1\subcat1
category1\subcat1
category2\subcat2
category3\subcat1
category2\subcat3
category3\subcat1
category2\subcat2
category1\subcat1
SELECT unique(SUBSTR(Column1, 1 ,INSTR(Column1, '\', 1, 1)-1)) as region
from abcd.table1 where Column1 like '%\%'
This query gives result
category1
category2
category3
SELECT unique(SUBSTR(Column1, INSTR(Column1, '\', -1, 1)+1)) as userid
from abcd.table1 where Column1 like '%\%'
This query gives result
subcat1
subcat2
subcat3
Problem Statement: How to build quick web report?
Requirement: The report cotains same story lines with different data from database.
With the help of JSTL, JSP and Display Tag completed coding with out single java file or configuration.
Create basic structure to build the code
>mvn archetype:create -DgroupId=com.abcd.xyz -DartifactId=project_war_name -DarchetypeArtifactId=maven-archetype-webapp
Technologies Used:
Display Tag, JSTL, JSP
Links:
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jsps/jstlsql.html
http://displaytag.sourceforge.net/1.2/tut_sources.html
http://www.java2s.com/Code/Java/JSTL/JSTLSQLQuery.htm
http://demo.raibledesigns.com/appfuse/demos/users.jsp
HTML Editor: http://www.coffeecup.com/free-editor use to build basic three page layout.
Add these dependencies to pom.xml file
<dependency>
<groupId>displaytag</groupId>
<artifactId>displaytag</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc</artifactId>
<version>11.1.0.7.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.1.2</version>
</dependency>
<!-- standard.jar -->
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
Add following code with real data to index.jsp page
<b>Heading of query information</b>
</br>
<sql:query var="results_abcd" dataSource="${dataSource}">
select col1, col2,col3 from table1 where condition1='asdf'
</sql:query>
<display:table name="pageScope.results_abcd.rows" id="table5" >
<display:column property="col1" sortable="true" />
<display:column property="col2" sortable="true" />
<display:column property="col3" sortable="true" />
</display:table>
</br></br>
more jstl examples: http://academic2.strose.edu/Math_And_Science/avitabij/cis455fall07/jstljdbc/notes.htm
Note: in JSTL we need to use escape charecters when required during copy paste query from SQL editor to JSTL code.
Example: where userid like '%\%'
in JSTL: where userid like '%\\%'
How to find what connections are hanging?
select sess.username, sess.sid,sess.serial#, stat.value cursors, machine
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username like '%db user name%'
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by machine
For more inforamtion visit http://www.shutdownabort.com/dbaqueries/Administration_Session.php
-0-
