We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are really slow compared to 11g. When We used 11g, there was no problem at all. The query finished in less than one second but in 12c it is at least 4 times slower. It is affecting all applications We have and even though there's a workaround (application side), ideally, You'd expect the same performance between 11g and 12c. We have also tried to measure the query cost using Toad. On 12c, the cost is much smaller than 11g. Could this be affecting the query time? Is there anything I can try to solve this problem? PS : Any other queries made for another tables are okay. Performance wise, there's little to no difference between 11g and 12c. Any feedback is welcomed. Thank you very much.

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of `@Id`), it goes nuts and takes a minute or so. This is longer than the app timeout, so the app fails for the user. In the "goes nuts" cases, the returned result set is correctly empty, as it is in many but not all the other cases. Luckily this is reproducible both in the production and development environments. The developer says removing "TOP 1" from the query, then making sure the app consumes the extra rows of the result set, clears up the performance problem. The query planner suggests no indexes when `TOP 1` is present. (in dev). Changing the query and fixing the app is in progress. Rollout takes a while. My question: Is there any DBA-accessible way to tune or tweak the production SQL Server instance to overcome this problem before the app change with the new query rolls out? ``` SELECT TOP 1 subscription_id FROM subscription AS sub JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id JOIN person_group AS apg ON apg.person_id = bi.person_id JOIN pplan ON pplan.plan_id = sub.plan_id JOIN product ON product.product_id = [plan].product_id JOIN product_attribute ON product_attribute.product_id = product.product_id WHERE apg.group_id = @Id AND apg.start_date GETDATE()) AND (sub.end_date IS NULL OR sub.end_date > GETDATE()) AND product_attribute.attribute_type = 'special feature' AND product_attribute.attribute_data = '1' ORDER BY sub.start_date ASC; ```

I am using open source columnar dbms (postgresql+Cstore). I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to 3000 characters per value). A linear look up will take it too long to give results. My queries vary from simple '=' to like, contain and wildcards. I am unable to conclude on what indexing should I use on string columns. I have hash table, trie structure, B+ tree index in mind. The problem with hash table is collisions and even if use perfect hashing, there are chances of returning false positives. Moreover, hash index cannot solve like, contains and wild card queries. Apart from that, my data have duplicate values in columns like company name etc., and building perfect hash is getting difficult over values with duplicates in them. Trie and B+ tree: Trie seems useful for all the queries, but as I mentioned, my strings are of huge length. Can trie be scaled to that extent? I have the same question regarding B+ Tree also. So, to sum up, I have lengthy string columns and I need speed in query execution. Which indexing should I use, or is there any other method to speed up searches, apart from what I've mentioned?

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: ``` # the default config above default_statistics_target = 50 maintenance_work_mem = 960MB constraint_exclusion = on checkpoint_completion_target = 0.9 effective_cache_size = 11GB work_mem = 96MB wal_buffers = 8MB checkpoint_segments = 16 shared_buffers = 4GB max_connections = 200 autovacuum = on log_autovacuum_min_duration = 10000 autovacuum_max_workers = 5 autovacuum_naptime = 1min autovacuum_vacuum_threshold = 50 autovacuum_analyze_threshold = 25 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 200000000 autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = -1 #log_statement='mod' #log_statement='all' logging_collector = on ``` Yet, query performance is very bad at times of high load. At those same times, the total RAM usage on the server is 4GB (out of 16GB available). I can't help to think that these two issues are related. And that performance would increase if pg would only utilize more of the available RAM. The server is Intel(R) Xeon(R) CPU E3-1240 v3 @ 3.40GHz, 16GB RAM. We have 2 disks (WDC WD1003FBYZ-0) but no RAID. The DB with performance issues is split between these disks using tablespaces. OS is Debian 7.5. So, anything obvious I have missed? Am I barking up the wrong tree? Or is there truly something fishy here?

Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why. We have two machines with identical hardware (let's call them A and B): ``` Intel(R) Xeon(R) CPU E5-4640 0 @ 2.40GHz (64 cores) 384 GB RAM 15k SAS, 16 disk RAID 10 array ``` Each machine has essentially identical Postgres clusters with about 100 GB databases, with the following settings: ``` version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit bytea_output: escape checkpoint_completion_target: 0.7 checkpoint_segments: 256 checkpoint_timeout: 30min client_encoding: UTF8 cpu_index_tuple_cost: 0.001 cpu_operator_cost: 0.0005 cpu_tuple_cost: 0.003 DateStyle: ISO, MDY default_text_search_config: pg_catalog.english dynamic_shared_memory_type: posix effective_cache_size: 128GB from_collapse_limit: 4 hot_standby: on join_collapse_limit: 4 lc_messages: en_US.UTF-8 lc_monetary: en_US.UTF-8 lc_numeric: en_US.UTF-8 lc_time: en_US.UTF-8 listen_addresses: * log_destination: stderr log_directory: pg_log log_filename: postgresql-%Y-%m-%d_%H%M%S.log log_line_prefix: log_rotation_age: 1d log_rotation_size: 0 log_timezone: US/Eastern log_truncate_on_rotation: on logging_collector: on maintenance_work_mem: 1GB max_connections: 256 max_replication_slots: 3 max_stack_depth: 2MB max_standby_streaming_delay: 350min max_wal_senders: 5 shared_buffers: 24GB temp_buffers: 8MB TimeZone: US/Eastern wal_buffers: 4MB wal_keep_segments: 5000 wal_level: hot_standby work_mem: 96MB ``` Linux settings: ``` CentOS 6.6 /sys/kernel/mm/redhat_transparent_hugepage/enabled: Always /sys/kernel/mm/redhat_transparent_hugepage/enabled: Always /sys/kernel/mm/redhat_transparent_hugepage/defrag: Always /proc/sys/vm/dirty_background_ratio: 10 /sys/block/sda/queue/scheduler: cfq /sys/block/sda/queue/read_ahead_kb: 128 blockdev --report: RO RA SSZ BSZ

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ and the sql queries are as follows: Sql query to check index fragmentation. ``` IF EXISTS ( SELECT * FROM [tempdb].[dbo].[sysobjects] WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]')) DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details] CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]( [DatabaseName] [nvarchar] (1000) NULL, [ObjectName] [nvarchar] (1000) NULL, [Pagecount] INT, [indexName] [nvarchar] (1000) NULL, [avg_fragmentation_percent] float NULL, ) ON [PRIMARY] DECLARE @dbname varchar(1000) DECLARE @sqlQuery nvarchar(4000) DECLARE dbcursor CURSOR for SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')and state not in('6') OPEN dbcursor FETCH NEXT FROM dbcursor INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlQuery = ' USE [' + @dbname + ']; IF EXISTS ( SELECT compatibility_level FROM sys.databases WHERE name = N'''+ @dbname +''' AND compatibility_level >= 90 ) BEGIN INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details] ( DatabaseName , ObjectName , IndexName , avg_fragmentation_percent , PageCount ) SELECT db_name() as DatabaseName, dbtables.[name], dbindexes.[name], indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.inde

I was looking into my server and I found this: ``` [root@host ~]# cd /var/lib/mongo/journal/ [root@host journal]# ls -lh total 3.1G -rw-------. 1 mongod mongod 1.0G Apr 7 01:18 j._0 -rw-------. 1 mongod mongod 88 Apr 7 01:18 lsn -rw-------. 1 mongod mongod 1.0G Dec 19 23:03 prealloc.1 -rw-------. 1 mongod mongod 1.0G Dec 19 23:06 prealloc.2 ``` but in my db, db.stats() shows: ``` > db.stats() { "db" : "gpstracker", "collections" : 5, "objects" : 59127, "avgObjSize" : 139.84318500854093, "dataSize" : 8268508, "storageSize" : 11198464, "numExtents" : 10, "indexes" : 3, "indexSize" : 1937712, "fileSize" : 201326592, "nsSizeMB" : 16, "dataFileVersion" : { "major" : 4, "minor" : 5 }, "ok" : 1 } ``` And I have only one database, with 3 collections in it. Maybe it is something that I don't know, yet. I searched a bit and found things about `compact`ing a database, can this be of help for this? Or these files are completely normal and they should be this way? What if my database grows, say from ~200MB (which is now) to ~1G. How much does these files grow? Can these files (journals) be optimized somehow? Sorry if my questions seem obvious. I'm a beginner in database department. Thanks in advance

Any rules of thumb for `work_mem`, `maintenance_work_mem`, `shared_buffer`, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own tool for manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out unique user pairs, estimate aggregates for individual users, etc.). All the advice I can find online on tuning (this, this, this etc.) is written for people anticipating lots of concurrent connections. Anyone have basic rules of thumb for someone working on a database alone for data manipulation? UPDATE: - This also means almost no writing, except to creation of new tables based on selections from the main table. (Apparently that's important -- thanks Erwin!) Updated 2: - I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.

Our DB design presently has a `interval` column which will only be storing days (no other interval type) so it is making sense to use `INT2` (`smallint`) instead of `interval`. Reference to documentation. Advantage: 2 bytes instead of 12 bytes (we have many such columns). Is this line of thinking ok or am I overlooking something?

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely. As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well. In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to: - Slow queries - Too many connections - Too many queries per second - Other potential issues I'm not aware of I know about the slow query log, but none of my queries are very slow. What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?