The tag has no wiki summary.

learn more… | top users | synonyms

2
votes
2answers
43 views

How do dirty reads at both the transaction and table level provide increased performance?

I ran the following code past our architect as I thought there may have been a tautology between the isolation level and table hint: SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ...
7
votes
5answers
508 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
0
votes
0answers
9 views

Performance tuning in Sybase

Does Sybase have a tool like AWR in Oracle which can be used to identify high load SQL, tell the important waits that are happening, help in understanding the workload? What are the main tools to be ...
1
vote
2answers
108 views

Oracle 11.2 using too few memory

it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012). The problem is that with more than doubled cpu power, ...
1
vote
1answer
53 views

Seeking help to test a use case on sql server memory

This is a question which came out of curiosity to know of how sql server handles memory. Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, ...
1
vote
1answer
44 views

Oracle Compatible Parameter to set

Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?
0
votes
0answers
128 views

Low Query cache efficiency after mariadb 5.5 migration

First of all, many thanks in advance for your creating this nice site/place where people can find help to our "big" issues/problems. I usually use every day and you dont know how many times I fix my ...
2
votes
1answer
76 views

How to monitor load on database server

Could anybody please suggest me a way to monitor the load on my database server? What perfmon counters should I monitor on daily basis and confirm that load on the server is increasing day by day [or] ...
0
votes
3answers
254 views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
0
votes
2answers
113 views

Tips on using SQL access advisor in Oracle

Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.
1
vote
0answers
62 views

DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons

Sorry - I feel like this is a newb question, but I can't find anything on it. In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38. However, on closer examination ...
3
votes
1answer
110 views

Tuning key_reads in MySQL

We are trying to tune our MySQL database (Myisam, 5.1.73) which is getting busy. We investigated Key_reads: $ mysqladmin ext -ri10 | grep Key_reads During a normal load we have the following ...
1
vote
0answers
13 views

MySQL: index statistics dropped when table dropped?

When a table is dropped, are its index statistics also removed? The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over ...
0
votes
2answers
136 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
1
vote
2answers
62 views

Optimizing UDF - Is Using DUAL Expensive

I am new to Oracle technology (primarily versed in SQL Server) and I am having trouble with the performance of a UDF. The purpose of the UDF is to take some inputs for an "event", and look up an ...
0
votes
2answers
807 views

Percona Xtradb Cluster : How to speed up insert?

I recently installed a 3 full master node cluster based on Percona Xtradb (very easy install). But now i need to make some tuning to increase INSERT/UPDATE requests. Actually, i made around 100 ...
3
votes
1answer
463 views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
5
votes
3answers
2k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
0
votes
0answers
110 views

InnoDB tuning with 1G of ram limit

I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
0
votes
0answers
64 views

mysql: how many disk seeks were made for a select query

I will fire a select query. I want to know how many disk seeks does that query take. mysql documentation has given formula for full table scan. ...
4
votes
2answers
966 views

How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?

The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages ...
0
votes
0answers
161 views

PostgreSQL can't start after tuning some options

I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this ...
1
vote
1answer
418 views

Slow queries to remote PostgreSQL db

I am running a Drupal site that is backed by Postgres. This is my first time running something with Postgres; I come from the MySQL world. For doing dev work on the site, I connect to a VPN and ...
1
vote
1answer
1k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
2
votes
1answer
4k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
0
votes
1answer
3k views

Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation. Would it be useful to run -check with this option or does the -optimize already take care of that? I am only ...
1
vote
1answer
79 views

tuning feed item's table query with multiple condition

I have a query like this: SELECT * FROM `rss_item` WHERE `feed_id` = 1 and `timestamp` < 1350698635 ORDER BY `rss_item`.`timestamp` ASC MySQL: 5.1; table engine: innodb Explain result: ...
0
votes
2answers
292 views

Tuning query to remove joins for reporting

This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server. SELECT user_id FROM user_table LEFT OUTER JOIN ( SELECT user_id , ...
0
votes
0answers
599 views

Oracle 10g - Sudden change in execution time for specific query

After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went ...
2
votes
1answer
2k views

InnoDB Performance Tuning

I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem. I'm a RoR developer and our team is currently short a DBA, so we're ...
5
votes
2answers
2k views

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
2
votes
2answers
2k views

MySQL 5.1.63 - using tuning-primer - Table Cache question

tuning_primer says the following: TABLE CACHE Current table_open_cache = 812 tables Current table_definition_cache = 256 tables You have a total of 498 tables You have 812 open tables. Current ...
4
votes
2answers
2k views

How I should fix the following update statement to make it run faster?

I have the following DML: UPDATE rc_usutb011 u SET cod_ant=( SELECT cod_hst FROM rc_pdptb101 p WHERE ocip_hst=ocip_ant AND p.sector=u.sector ); The explain plan ...
6
votes
2answers
7k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
3
votes
1answer
1k views

Pgpool, Postgresql and Apache tuning (1000 concurrent spatial queries) [closed]

I'm trying to configure a load balancing system. I've a python script, invoked through mod_wsgi on Apache, that generates a query and executes it on pgpool: request-> wsgi python -> pgpool -> ...
1
vote
1answer
457 views

Improve performance for my plsql block

First of all let me make kind of a disclaimer here: 1) i'm a developer, not an administrator; 2) and i dont have admin rights on my database. 3) table specs : -- oulp_path_attendance_base_t - ...
6
votes
1answer
1k views

Tuning dedicated Percona Server with XtraDB for OLTP

Hardware specs for an OLTP DB dual quad core xeon with hyperthreading 24GB ram Hardware RAID10 BBU Software specs Standard install of Ubuntu 10.04LTS with default LVM partitioning Installed ...
2
votes
1answer
261 views

SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time

I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error: Error: Can not get ...
29
votes
3answers
36k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
5
votes
2answers
901 views

DTA Recommends to CREATE STATISTICS

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file. My question is, how exactly ...
1
vote
1answer
123 views

Can SQL profiles be deployed to other Oracle Databases?

When using The SQL Tuning Advisor in SQL-Developer. I get often some recommendations to accept a SQL-Profil. I guess it is a good idea to do so. But is this similar to proposed indexes, which I can ...