Questions tagged [tuning]
The tuning tag has no usage guidance.
89
questions
0
votes
1
answer
103
views
AI Tools for Tuning SQL Server? [closed]
Are there any strong and accurate AI-based utilities out there that can help identify problems with and suggest better-tuned alternatives for deficient queries, and that can analyze and provide useful ...
- 157
0
votes
0
answers
37
views
Why my postgresql perfomance getting worse after changing default parametes
I changed my postgresql server parameters to tune it for a query but I get worse result than default settings.Here is my changed parameters and explain result:
# pgTune suggested settings
...
- 1
0
votes
1
answer
208
views
How does different forms of EXISTS() affect performance in MS Sql Server?
I've been told that when checking for the existence of a row in a table, this will work:
EXISTS(SELECT 1 FROM....)
but that a "better" way in regards to speed performance of the query would ...
- 119
0
votes
1
answer
1k
views
How to use UNNEST hint in Oracle Database?
I have encountered Bug 34044661 - Poor Performance Due to SQL Not Unnesting in Oracle 19C (Doc ID 34044661.8).
Oracle provides some workarounds:
use UNNEST hint
or
alter session set "...
- 801
1
vote
1
answer
62
views
Explain plan not showing most columns
I am running an explain plan for a query and it only returns the following columns:
explain plan for SELECT ....
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash ...
- 801
3
votes
1
answer
690
views
Index usage Brent Ozar script
I'm using sp_blitzIndex to check the health of my database indexation, but I don't understand every column used in the output.
I'm talking specifically about "Usage Stats" and "Op Stats&...
- 31
8
votes
1
answer
9k
views
How do you tune innodb_read_io_threads?
The default value of innodb_read_io_threads and innodb_write_io_threads is 4. How do you check if your server load needs a higher number of threads?
As I regularly check show engine innodb status \G, ...
- 4,541
0
votes
1
answer
435
views
Postgres SELECT query produces exact same explain analyze results but different execution times in the results
I have a SELECT query which when running produces the exact same plan in terms of indexes being used when doing an explain analyze however the duration of the run is different (2 seconds vs 30 seconds ...
- 459
1
vote
2
answers
57
views
A SQL query optimzation issue with ORDER BY
An SQL query optimzation issue with ORDER BY
Current status
I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
0
votes
1
answer
69
views
does using optimizer_mode=first_rows_n for application that using Oracle database is a best practice?
So I'm working in application that involving display of data in table/grid. And the grid will invoke paging query to oracle. So does by tuning my application to get the best query cost with ...
0
votes
1
answer
3k
views
How to find historical sql_id executed by users
I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS and DBA_HIST_SNAPSHOTS to try find the ...
- 801
1
vote
2
answers
575
views
Tuning MySQL on GROUP REPLICATION
I'm close to switch from old version of MySQL 5.6 ( master / slave configuration ) to MySQL 8 ( 3 nodes on multi-master configuration ) in GROUP REPLICATION.
The tables are 99% InnoDB.
The webfarm ...
- 11
0
votes
1
answer
64
views
Oracle shrink space release space to operational system in one step?
Does oracle release space to operational system when is issued alter table ... shrink space (one step) or just after move HWM (two steps) alter table ... shrink space compact?
- 801
1
vote
1
answer
44
views
Best way to know if my environment has workload repository license
Usually, when I need to use something that use some resource from awr, I look for LAST_USAGE_DATE at dba_feature_usage_statistics with feature name Automatic Workload Repository, but I'm almost sure ...
- 801
0
votes
1
answer
1k
views
How to get the SQL_ID that is blocking another SQL?
Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL.
Here is my script, if you try this, You never will get ...
- 801
3
votes
1
answer
124
views
Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)
Assume that a query in question has a very wide set of columns. The query looks like the following:
set statistics io,time on;
Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5
From FirstWideTable a
...
- 141
-1
votes
1
answer
641
views
How to know needed UNDO_RETENTION for a specified query of long duration? [closed]
I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence.
In other words, I'd like to know how long my ...
- 801
0
votes
1
answer
611
views
tuned lacks oracle profile. How could I switch to it?
I'm trying to configure tuned using this tutorial
And following it I'm trying to switch to oracle profile:
# tuned-adm profile oracle
Invalid profile. Use 'tuned-adm list' to get all available ...
- 285
4
votes
2
answers
3k
views
Which Plan is Better? WHERE EXISTS Instead of INNER JOIN
Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.
Logical reads plummeted, but plan cost and memory ...
- 81
1
vote
2
answers
855
views
oracle slow query with diferent user [closed]
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 ...
- 149
0
votes
1
answer
236
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 ...
- 135
2
votes
1
answer
8k
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 ...
- 157
1
vote
0
answers
277
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 ...
- 159
4
votes
1
answer
12k
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 ...
user144368
2
votes
1
answer
584
views
sp_BlitzIndex -- 'High value" vs 'High Impact' [closed]
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 ...
- 21
1
vote
3
answers
71
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
1
answer
765
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 ...
- 121
2
votes
1
answer
190
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 ...
- 945
0
votes
1
answer
298
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
11
votes
2
answers
26k
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 ...
- 213
2
votes
1
answer
2k
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 ...
- 1,364
2
votes
1
answer
632
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 ...
- 121
0
votes
1
answer
3k
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 ...
- 231
1
vote
1
answer
250
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,
...
- 2,296
3
votes
2
answers
4k
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 ...
- 813
1
vote
1
answer
5k
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 ...
- 162
1
vote
1
answer
12k
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 ...
- 75
0
votes
1
answer
5k
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 ...
- 169
1
vote
0
answers
235
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 ...
- 933
7
votes
4
answers
4k
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 ...
- 171
0
votes
1
answer
56
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 ...
- 279
0
votes
1
answer
853
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
1
answer
170
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....
- 31
6
votes
1
answer
125
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
1
answer
42
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,
...
- 103
1
vote
2
answers
5k
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 ...
- 11
3
votes
3
answers
3k
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 ...
- 427
4
votes
2
answers
8k
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 ...
- 113
0
votes
1
answer
1k
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 ...
- 113
1
vote
1
answer
2k
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 ...
- 528