I recently fielded a question about getting data out of Oracle quickly. Without much detail on the systems involved here is my answer: I need to migrate data from Oracle to MySQL quickly (in less than 1 day). What are my options? Oracle does not…
I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it…
This script will generate dbv commands into a shell script and then execute the shell script. run-dbv.sql: set head off set lines 200 set feedback off set define off spool dbv.sh select 'dbv file='||name||' blocksize = '||block_size|| ' LOGFILE=FILE-'||FILE#||'.LOG' from v$datafile; spool off host chmod…
If you want to run AutoTrace using Toad using a non sys or system user, give your user the following select privileges: grant select on v_$session to &traceuser.; grant select on v_$sesstat to &traceuser.; grant select on v_$statname to &traceuser.; Alternately you can run $ORACLE_HOME/sqlplus/admin/plustrce.sql…
I came across a situation where the profiler.sql supplied with Metalink Doc: 243755.1 was giving some incomplete results. Because some of my PL/SQL code was executing very quickly it was rounding down to 0.00 and not even showing up on the 'Top 10' section. Throughout…
If you are experimenting with gathering system statistics it might be helpful to archive your current settings and any intermediate settings you come up with along the way. There is a way to save stats to a table using DBMS_STATS.CREATE_STAT_TABLE and gathering with DBMS_STATS.GATHER into…
#!/usr/bin/bash #--------------------------------------------------------------------------- # Written by : David Mann @ http://ba6.us # Script Name : alldatafiles.sh # Description : Provides list of all datafiles for each running database on # on a machine #--------------------------------------------------------------------------- ORATAB=/etc/oratab echo "INSTANCE_NAME, FILE_NAME" # Step through running instances ps -ef |…
If you ever used the Solaris ps -ef command to view programs that are running you may not always see the arguments the original program was started with. This command usually shows about 80 characters. Try the following commands, they usually give you more information…
I ran across Orion in a Kevin Closson blog post. From the OTN site: "ORION (Oracle I/O Calibration Tool) is a standalone tool for calibrating the I/O performance for storage systems that are intended to be used for Oracle databases. The calibration results are useful…
As I get deeper into tuning with Statistics and the CBO I have been paying a lot more attention to hints. In the past I always tried to avoid hints. After working with the CBO and staistics in recent versions I believe they are necessary…
I have been making use of the GATHER_PLAN_STATISTICS hint more and more lately. In the interest of getting the most accurate data I usually log in as the user that normally executes the query. These are usually non-privileged accounts that just have access to their…
For the past few weeks I have been using the TriVaDis eXtended Tracefile Analysis Tool as a replacement for TKPROF. TKPROF is available with all Oracle installations but is has some drawbacks. Early versions of TKPROF do not provide any information about bind variables. It…
I was recently looking for old (not ancient, just old) install media to support a database migration. The usual collection of download links on OTN usually just has the last 2 releases. If you need older versions (in this case 9i) then hit up the…
On Oracle-L there was a recent discussion about detecting block corruption. One suggestion was to do an Export to /dev/null. While this may get you 95% of the way there it is mainly relying on side effects of data access to detect any issues. If…
Iggy Fernandez covers the topic of who is better suited to tune SQL - Developers or DBAs. Also listed are 5 Dangerous Beliefs which may influence the answer to that question. https://iggyfernandez.wordpress.com/2009/07/12/who-should-tune-sql-the-dba-or-the-developer/ -Dave Dangerous Belief #1: DBAs bear chief responsibility for the performance of SQL…