http://www.asktherealtom.ch I don't need friends, I have Oracle! Wed, 25 Aug 2010 08:05:41 +0000 http://backend.userland.com/rss092 en I've wrote a simple SQL script to list all ASM disks with state, size and their diskgroups, if any. set pages 5000 lines 180 set heading on set feedback off set serveroutput on col LABEL for a28 col DISKGROUP for a9 col STATUS for a9 col TOTAL for a11 col MOUNT_STATE for a11 exec dbms_output.put_line('---'); exec dbms_output.put_line('--- DISK OVERVIEW'); exec dbms_output.put_line('---'); SELECT         D.LABEL||' ... http://www.asktherealtom.ch/?p=226 Searches on MySQL with expression like " ... like '%abc%'" can be incredibly slow on large tables. The problem is, MySQL can not use indices for expression starting with a wildcard "%". Neither MySQL supports function based indexes. I will explain a simple solution. But first lets have a look to our ... http://www.asktherealtom.ch/?p=217 To lock the SGA into physical memory you have to set the lock_sga parameter to true. Usually you get following Error message. SQL> startup SQL> ORA-27102: out of memory Linux-x86_64 Error: 12: Cannot allocate memory The problem it ulimit -l You have to set it in /etc/security/limits.conf and relogon to the system and restart database. cat /etc/security/limits.conf ... http://www.asktherealtom.ch/?p=220 Enabling and validating constraints is can take a huge amount of time in big oracle databases. To speed the validaten up, it can be done in parallel. This can save you valuable time in release windows. Enable Constraint with novalidate option Set parallel degree on the table or on session with force ddl parallel Enable ... http://www.asktherealtom.ch/?p=214 Problem: SQL> startup nomount ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device There are 64G physical memoray avaliable. shmmax is set to 52G But still its not possibel to start the Oracle instance with more than 30G SGA. Solution: kernel.shmall is set to small. cat /proc/sys/kernel/shmall 8248733 getconf ... http://www.asktherealtom.ch/?p=207 I tried to run IO calibrate to check what the system is able to handle. But I run into following error (using ASM / ASMlib) ORA-56708: Could not find any datafiles with asynchronous i/o capability ORA-06512: at "SYS.DBMS_RMIN", line 456 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285 ORA-06512: at line 6 The ASM files show ASYNC_ON SELECT name, asynch_io FROM ... http://www.asktherealtom.ch/?p=201 Today a wrote a simple commit sampler for oracle databases. With this simple tool it possible to see the commit rate on the database. It can be usefull to compre the application perfomance on batch processing system and analyze commit related waits. @snapcommit Example @snapcommit 1 20 snapcommit.sql set tab off verify off linesize ... http://www.asktherealtom.ch/?p=199 Just a shorty today: How do you access the anydata_value from dba_hist_sqlbind? Solution: use tha statice converter function of the anydata object type Example select SQL_ID,NAME,DATATYPE_STRING,VALUE_STRING,anydata.AccessTimestamp(VALUE_ANYDATA) from DBA_HIST_SQLBIND where sql_id='f17fd6xuvxc7x' and name=':1' http://www.asktherealtom.ch/?p=197 Situation: The user claims that a oracle web application is slow. The web application is unsing in database java. Test 1: Determine the slow Part The webapplication can be run in sqlplus with following pl/sql set serveroutput on set timing on declare name owa.vc_arr; val owa.vc_arr; res owa.vc_arr; simple_list__ owa_util.vc_arr; complex_list__ ... http://www.asktherealtom.ch/?p=195 Today a application developer asket me how he should partition his index organized table. All query include the primary key, no range, no full scan's. So I told him it doen't make sense to partition this table for perfomance reasons. To profe my statement I did a small Test case. create table part_range_iot ... http://www.asktherealtom.ch/?p=190