Archive for the ‘Databases’ Category
Wednesday, September 16th, 2009
SQL Server JDBC Driver provides misleading error messages and sometimes even locks down processing SQL Query request.
In one of my application tests that used Hibernate framework to abstract database access, SQL Server JDBC Driver went into infinite loop trying to process a simple query on a view such as :
...
Posted in Databases, Programming | No Comments »
Tuesday, September 15th, 2009
Most of the server side applications use database back-end. Being used to Test-Driven Development practices, it is desirable to be able to write a test for numerous stored procedures we put in our code. Building Java code to launch a simple stored procedure, flush database and setting to a predefined ...
Posted in Databases, Java, Programming | No Comments »
Monday, August 24th, 2009
Query number of records in each partition of the table:
SET SERVEROUTPUT ON SIZE 100000;
DECLARE
CURSOR c1
IS
SELECT table_name,
partition_name
FROM all_tab_partitions
WHERE table_name = 'MCP_CDR_HIST' order by partition_name asc;
v_sql VARCHAR2(2000);
temp_var NUMBER:=0;
BEGIN
dbms_output.put_line('Start');
FOR rec IN c1
LOOP
EXECUTE immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' INTO temp_var;
dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||temp_var||' rows');
END ...
Posted in Databases | 1 Comment »
Tuesday, August 18th, 2009
Sliding window is a classical solution for the most historical databases. A partition is switched in and out. The scenario is supported by majority of database providers.
The following article describes partition switching in or partition exchange for Oracle: http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php
Posted in Databases, Programming | No Comments »
Friday, July 24th, 2009
The following script reports space allocated for every index in the Database.
Script is taken from http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server:
SELECT
i.name AS IndexName,
s.used_page_count * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes ...
Posted in Databases, Programming | No Comments »
Monday, July 20th, 2009
SQL Server
The following query allows to verify created partitions per partition function. If a single function is used per table this would show all partitions created for every table:
select * from sys.partition_functions func join sys.partition_range_values range on func.function_id = range.function_id;
Oracle
The following query allows to query list of created partitions per ...
Posted in Databases, Programming | No Comments »
Friday, June 12th, 2009
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm
http://www.psoug.org/reference/select.html
Posted in Databases | No Comments »
Thursday, June 4th, 2009
It is a bit challenging to get UTC time stamp using Oracle PL/SQL. I found the following trick
SELECT (CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400 seconds FROM dual;
On Joels Blog:
http://discuss.joelonsoftware.com/default.asp?joel.3.240795.5
Other way is here:
http://www.lifeaftercoffee.com/2005/10/20/unix-timestamp-to-oracle-date-conversion/
Posted in Databases | No Comments »
Tuesday, May 19th, 2009
Cache is an important aspect of any system. However, cache can be easily saturated and invalidated with a single scan or a large query. With this in mind it is necessary to know your system usage and pick performance capabilities.
Disk I/O is not fast. The following table outlines the limits:
...
Posted in Databases | No Comments »
Friday, May 15th, 2009
The following article provides a good summary of Oracle performance tips:
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
In addition, see an article on extending SGA memory block on Windows 32-bit platform.
Posted in Databases, Programming | No Comments »