Questions tagged [tuning]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
81 views

oracle slow query with diferent user

I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do ...
0
votes
1answer
62 views

Oracle 9i historical long queries since instance startup

please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several ...
1
vote
1answer
3k views

Timeout expired error. Is it because of a query or server settings?

I am getting SQL Server timeout error in my production server, every hour (~ for 5 minutes or more) during night time. This SQL server database is supporting a .NET application. I have scheduled an ...
1
vote
0answers
70 views

Improve qps for postgres write

I am trying to improve the performance of my postgres instance in aws. I am using aws rds db.t2.large Here is my table structure CREATE TABLE public.my_test ( test_index1 text COLLATE ...
3
votes
1answer
3k views

How do you monitor slow query in AWS RDS Postgres

My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU ...
1
vote
0answers
304 views

sp_BlitzIndex — 'High value" vs 'High Impact'

Sorry, I may be missing something super-obvious here, but I've googled this and asked some colleagues and am not finding a satisfactory answer. I've been using sp_BlitzIndex for a few months now ...
1
vote
3answers
59 views

Optimize SQL query in form of a Procedure

I have a Oracle SQL query with multiple "IN" statements. It takes around a minute to execute in an average. My task is to optimize this query in form of PL/SQL procedure so that the user gets the ...
2
votes
1answer
298 views

Effect of over allocating shared_buffers

I have a 9.6 PostgreSQL RDS instance that I am tending to, but did not initially configure, and have been struggling with two different symptoms: the system (a db.r3.2xlarge w/ 61GB memory) would ...
2
votes
1answer
163 views

Ideas how to make this query faster?

I have the following query: SELECT TabPohybyZbozi.ID, TabPohybyZbozi.NazevSozNa2, TabZakazka.Nazev, TabDokladyZbozi.DatPorizeni, TabPohybyZbozi.IDDoklad FROM ...
0
votes
1answer
186 views

Database Engine Tuning Advisor

I analysed a huge query in DTA. The database is kind of badly designed and does not have primary keys on tables, also they dont have clustered indexes on them. I found around 80 tables as such. Now, ...
3
votes
2answers
4k views

Sql Server, Is “Reorganize Files Before Releasing Unused Space” better or worse?

I could not find a satisfactory answer. I fully understand that "Shrink" will cause index defragmentation, and that can worsen performance. But, is not this option to reduce defragmentation? So, to ...
2
votes
1answer
1k views

Table valued function VS Stored procedure

There's a function that : returns only 1 row has only 1 parameter has multiple joins on different tables, each only to return a single scalar value is called only once at each login of a web ...
2
votes
1answer
356 views

PostgreSQL 9.6.2 perfomance

I need help for server side optimization. I think it is a system file cache problem but I'm not sure, maybe I am wrong. I have 2 servers (S1, S2) with PostgreSQL 9.6.2 on both of them, the database ...
0
votes
1answer
1k views

Pt-query-digest date ranges do not work as expected

Analyse Queries with Pt-query-digest I am using pt-query-digest to analyse queries and would like to analyse a specific time range only, in order to measure the effect of adding new indices and ...
1
vote
1answer
154 views

Should I ever use query hints?

SQL Server 2008 R2, Dynamics AX 2009 We have this ugly query DECLARE @P1 NVARCHAR(5), @P2 INT, @P3 INT, @P4 NVARCHAR(11), @P5 INT, @P6 NVARCHAR(5), @P7 NVARCHAR(11)SELECT A.SALESID, A.LINENUM, ...
3
votes
2answers
2k views

TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing Query

TSQL MSSQL 2008r2 I am trying to tune a poor performing query. For 3 out of 4 of the largest tables (several millions of rows) being queried (index seeks), the plan has decided that the estimated ...
1
vote
1answer
1k views

How to get MariaDB (MySql) query time?

I'm using MariaDB 10.1. How to get actual query time for all of executed query? It seems that SHOW PROFILES; was deprecated. Is there a better solution for database tuning? Edit: It's deprecated only ...
0
votes
1answer
6k views

Performance tuning large mysql databases for zabbix

I have been working on tuning a mysql database for zabbix for quite some time now. We do not actually have a DBA to assist with this. We have a Zabbix server running 16k values per second. Our ...
0
votes
1answer
3k views

How to solve log file sync wait in oracle 12c

I have performance issue with Oracle instance. There is some java process that makes massive inserting into database. And the top wait event is "log file sync". I tried to change some Oracle database ...
1
vote
0answers
163 views

Operating system tuning for PostgreSQL

I'm working at the pgconfig.org[1] tool. PgConfig it's a tool for suggest a tuning configuration for a PostgreSQL environment. I'm finding some disk tuning documentations to implement a suggestion ...
7
votes
4answers
2k views

Hash Match inner join in simple query with in statement

I am running the execution plan for the following query: select m_uid from EmpTaxAudit where clientid = 91682 and empuid = 42100176452603 and newvalue in('Deleted','DB-Deleted','Added') Here is ...
-1
votes
1answer
41 views

Is there a way to compare very short periods of activity in Oracle?

I am having a problem with an application that requires very low latency (less than 100 ms per txn.) Every once in a while we will see spikes where transactions take 20-30 SECONDS. We looked at the ...
0
votes
1answer
658 views

Same query same data different server different query plan

I have a query that hits a view, which references other views etc...several levels down. The query plan is a bit complex. I run the query on 2 different servers. On both servers, the number of rows ...
3
votes
1answer
157 views

Sort operation SQL Server 2012

I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated. Query is : SELECT TOP 1 ArchiveType, CONVERT(NVARCHAR(30), A....
6
votes
1answer
114 views

Query tuning issues

I'm in trouble trying to tune this following query. I used to be 100% focused in the infrastructure side of the stuff, but since August this year I'm working for a processing card company that is ...
0
votes
1answer
39 views

Query speed improvements [closed]

Referencing: CREATE TABLE words ( word_id int(11) NOT NULL, word varchar(25) NOT NULL, PRIMARY KEY (word) ) CREATE TABLE synonyms ( source_index int(10) unsigned NOT NULL, ...
0
votes
0answers
259 views

Postgresql configuration for tiny server and append-only data

Currently my database is runing on tiny-sized hardware (digital ocean 5$) and I want to keep this hardware. It is: 512M memory 1 CPU My database works with these conditions: Data is never deleted,...
1
vote
2answers
3k views

Please recommend changes for my.cnf

I am not having any performance problems per se but want to improve my sites' performance if possible. Would appreciate any advice for how I can improve my.cnf. I have just one server, not ...
2
votes
3answers
2k views

Export Query Plans

I know it is very easy to export a single query plan. My question is, when I create a query which returns multiple plans, such as looking at the most costly queries on the server, is there a way to ...
4
votes
2answers
5k views

Database Tuning Issues

I'm not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I've found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end. I've ...
0
votes
1answer
786 views

Confused over table_open_cache

I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables. I ran into an issue ...
1
vote
1answer
1k views

Why the optimizer does not use an Index Unique Scan?

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical ...
4
votes
2answers
1k views

SQL Backup tuning large databases

I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases ...
0
votes
1answer
154 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
0
votes
0answers
671 views

No query cache efficiency mairaDB

I'm totally new to database tuning, and even though I've done well so far, I'm stuck with my database cache query efficiency 0%. Any help with this would much appreciated I've tried increasing ...
0
votes
0answers
397 views

Optimization of an old query multiple unioned subqueries

I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization. Please Reference the ...
2
votes
2answers
238 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 ...
6
votes
5answers
2k 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
2answers
2k 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
1k 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, ...
2
votes
2answers
124 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, ...
3
votes
3answers
2k 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
803 views

Low Query cache efficiency after mariadb 5.5 migration

We change from Percona MySQL server 5.5 to MariaDB but performance is not as good as we think. This graph is from a day in MariaDB. You can see the low performance in terms of cache_hits: We have ...
3
votes
1answer
780 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] ...
9
votes
5answers
47k 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
3k 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
1answer
320 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
2answers
4k 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
23 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 ...
1
vote
4answers
287 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 ...