Оптимальная настройка mysql

My Problem

I wanted even more connections, so I tried setting it to 10000. However, when I did, the database wouldn’t even start properly. When I try to connect to it using sqlplus, I get the following message:

I can’t use the database in any way, and won’t work either. I reinstalled the database, set it to 1000, reboot and that worked. Reinstalling and rebooting without changing a setting also works. Reinstalling it, setting it to 10000 and then rebooting, however, doesn’t work. This leads me to believe that there’s a limit to how high those values can be set and that exceeding the limit will render the database unable to start properly.

So my question is: what are the maximum Processes/Sessions/Transactions limits that I can set for the Oracle Database 11g Express Edition? Are there any factors that influence such a limit that I could control/change? Or am I horribly misunderstanding all of this and the solution is something entirely different? The machine I’m running this on is a fairly powerful machine running Windows 10, so power shouldn’t be an issue. Except maybe if the software is enforcing an artificial limit, and I did find such comments on my search.

What I’m doing

I’m working on an Oracle driver incorporating OCI. To test it (mostly stability and fixing memory leaks), I’m using Oracle Database 11g Express Edition (I’m not an Oracle expert, so I don’t know if this is the right version for the job). For this, it needs to commit a lot of transactions at once: dozens of connections simultaneously, each one maintaining a session pool where dozens of sessions each are simultaneously creating tables, inserting lots of data and dropping the tables.

Anyway, I quickly got the following error doing that:

As it turns out, that’s because the Processes/Sessions/Transaction limits aren’t high enough. Searching on the Internet, I was told that they can be set this way:

Apparently, the truth is more complicated than that because those values get set depending on each other, and how that works seems to be different in each version. According to Oracle’s documentation here it all depends on Processes:

And Processes can NOT be changed:

…while at the same time it is talking about what happens when you change it:

So that’s not very helpful. Or perhaps I’m misunderstanding something here. Anyway, using the calls above I changed the values, and the changes are successful. When I make this call

I get the following result:

It looks like processes could be changed unlike what Oracle’s documentation says, sessions got set to 2 more than the rule described , and transactions seems to be unlimited. The change is effective, I can now make many more connections and sessions without encountering Error . So far, so good.

Which Method To Limit the Number of Rows in Oracle is the Best?

Well, the best method for limiting rows in Oracle will consider performance, flexibility, and actually what database version you have.

If you’re using Oracle 12c, then use the FETCH syntax, as it was built especially for this purpose.

If you’re not using Oracle 12c, I would suggest using the AskTom method, as it was recommended by Tom Kyte and has been used by many Oracle users.

So, there’s how you can write a query to limit the number of rows returned in Oracle. If you have any questions on this, let me know in the comments section.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Image courtesy of xedos4 / FreeDigitalPhotos.net

Вопросы и ответы


Я пытаюсь вытащить некоторую информацию из таблицы. Для простоты, скажем таблица (report_history) имеет 4 колонки: user_name, report_job_id, report_name и report_run_date.

Каждый раз, когда выполняется отчет в Oracle, запись фиксируется в таблице, отмечая вышеперечисленную информацию. То, что я пытаюсь сделать, это вытащить из этой таблицы, когда и кем был запущен последний отдельный отчет.

Мой первоначальный запрос:

Oracle PL/SQL

SELECT report_name, MAX(report_run_date) FROM report_history GROUP BY report_name

1 2 3




работает нормально. Тем не менее, это не дает имя пользователя, запустившего отчет.

Добавление user_name к списку выборки и к оператору GROUP BY возвращает несколько строк для каждого отчета; результаты показывают последнее время, когда каждый человек запускал каждый отчет остается вопросом. (т.е. User1 запускал ОТЧЕТ1 01-Июль-14, User2 запускал ОТЧЕТ1 01-АВГ-14). Я не хочу этого … Я просто хочу знать, кто запускал определенный отчет и когда последний раз он был запущен.

Есть предложения?


Это все становится немного сложнее. В запросе ниже SQL SELECT вернет результаты, которые вы хотите:

Oracle PL/SQL

SELECT rh.user_name, rh.report_name, rh.report_run_date FROM report_history rh, (SELECT MAX(report_run_date) AS maxdate, report_name FROM report_history GROUP BY report_name) maxresults WHERE rh.report_name = maxresults.report_name AND rh.report_run_date= maxresults.maxdate;

1 2 3 4 5 6 7 8 9 10











Во-первых, мы присвоим псевдоним rh первому экземпляру таблицы report_history. Во-вторых, мы включили два компонента в оператор FROM. Первый из них является таблица report_history (псевдоним RH). Второй является подзапрос с псевдонимом maxresults:

Oracle PL/SQL

(SELECT MAX(report_run_date) AS maxdate, report_name FROM report_history GROUP BY report_name) maxresults

1 2 3




Имеем псевдоним MAX(report_run_date) как maxdate и имеем псевдоним всего результирующего набора как maxresults.

Теперь, когда мы составили этот запрос в пределах нашего FROM, Oracle позволит нам объединить эти результаты нашей исходной таблицы report_history. Таким образом, мы объединили поля report_name и report_run_date таблицы с псевдонимами rh и maxresults. Это позволили нам получить report_name, MAX(report_run_date), а также user_name.

MySQL Advanced Functions

Function Description
BIN Returns a binary representation of a number
BINARY Converts a value to a binary string
CASE Goes through conditions and return a value when the first condition is met
CAST Converts a value (of any type) into a specified datatype
COALESCE Returns the first non-null value in a list
CONNECTION_ID Returns the unique connection ID for the current connection
CONV Converts a number from one numeric base system to another
CONVERT Converts a value into the specified datatype or character set
CURRENT_USER Returns the user name and host name for the MySQL account that the server used to authenticate the current client
DATABASE Returns the name of the current database
IF Returns a value if a condition is TRUE, or another value if a condition is FALSE
IFNULL Return a specified value if the expression is NULL, otherwise return the expression
ISNULL Returns 1 or 0 depending on whether an expression is NULL
LAST_INSERT_ID Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table
NULLIF Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
SESSION_USER Returns the current MySQL user name and host name
SYSTEM_USER Returns the current MySQL user name and host name
USER Returns the current MySQL user name and host name
VERSION Returns the current version of the MySQL database

Использование Nested Tables

Вложенная таблица подходит, если:

  • Количество элементов не задано.
  • Значения индекса не являются последовательными.
  • Вы должны удалить или обновить некоторые элементы, но не все элементы одновременно.

Данные Nested Tables хранятся в отдельной хранимой таблице, таблице базы данных, сгенерированной системой. Когда вы обращаетесь к Nested Tables, база данных join (присоединяет) Nested Tables к своей сохраненной таблице. Это делает Nested Tables подходящими для запросов и обновлений, которые затрагивают только некоторые элементы коллекции. Вы должны создать отдельную таблицу поиска с несколькими записями для каждой строки основной таблицы и получить доступ к ней через запросы присоединения.

Getting the rows with the Nth highest value

Suppose you have to get employees whose has 2nd highest salary in the company. To do so, you use the  clauses as follows.

The clause sorts the employees by salary in descending order. And the clause gets the second row from the result set.

This query works with the assumption that every employee has a different salary. It will fail if there are two employees who have the same highest salary. In addition, in case you have two or more employees who have the same 2nd highest salary, the query just returns the first one.

To fix this issue, you can get the second highest salary first using the following statement.

And pass the result to another query:

If you know subquery, you can combine both queries into a single query as follows:

In this tutorial, we have introduced you to the SQL LIMIT and OFFSET clauses that allow you to constrain the number of rows returned by a query.

  • Was this tutorial helpful ?

MySQL Программирование

  • Типы данных MySQL
  • Алиасы в MySQL
  • Sequences (последовательности) MySQL
  • Литералы
  • Объявление переменных
  • Комментарии

MySQL Триггеры

Создать триггер MySQL
Удалить триггер MySQL

MySQL условия

AND логический оператор и
OR логический оператор или
AND и OR логический операторы и & или
LIKE сопоставляет данные с шаблоном
IN определяет, соответствует ли значение или список значений выражению в указанном наборе
NOT логический оператор не
IS NULL проверка значения NULL
IS NOT NULL проверка на значения NOT NULL
BETWEEN используется для получения значений в пределах диапазона
EXISTS используется в сочетании с подзапросом (subquery)
Операторы сравнения MySQL Такие как =, , , !=, >, >=,

SQL Pagination in Oracle


Pagination is a feature in many applications that allows you to show different pages of results.

For example, when searching in Google, you see results 1 to 10. When you click on the next page, you see results 11-20.

This is easy to do in Oracle SQL with the row limiting clause. We can simply use the OFFSET keyword within this clause.

With our sample query, say we wanted to find the top 3 customers by revenue. Our query would look like this:

The results would be:

2 9384760
10 5131750
11 4431791

If we then wanted to find “page 2” of this data, or see results 4 to 6, our query would be:

This includes the words OFFSET 3 ROWS, which means that the “FETCH FIRST 3 ROWS” should start after the first 3 rows, therefore getting rows 4 to 6.

The results would be:

8 4341421
4 3596297
13 3596297

You can change the parameters in the OFFSET and FETCH clause to define how many results you want per page.

For example, if you want 10 results per page, your queries would look like this:

And for the second page:

You can and should use bind variables for the OFFSET and FETCH values, but I’ll write about that in another post.

Row Limiting Without the FETCH Clause


There are a few ways to do row limiting and top-N queries without the FETCH clause in Oracle. This could be because you’re not working on a 12c or 18c database. Perhaps you’re running Oracle Express 11g, or using an 11g database at work.

There are a few ways to do this.

Using an Inline View with ROWNUM

You can use an inline view with the ROWNUM pseudocolumn to perform top-N queries. This is one of the most common ways to do row limiting without the FETCH clause.

The syntax looks like this:

You just need to specify the columns to view, the column to order by, and the number of rows to limit it to.

If we want to see the top 5 customers by revenue using our sample data, our query would look like this:

The results would be the same as the earlier examples:

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297

This works because the data is ordered before ROWNUM is applied.

However, this is the kind of query we want to avoid:

This will perform the limiting on the row number before the ordering, and give us these results:

2 9384760
4 3596297
3 1852828
1 109470
5 82495

It has limited the results to the first 5 rows it has found in the table, and then performed the ordering. This will likely give you results you are not expecting. This is why we use a subquery such as an inline view.

Pagination with ROWNUM in Oracle

We can use this method to perform pagination of records in SQL. While it looks a little messier than the FETCH and OFFSET methods, it does work well:

This query does a few things:

  • It includes two nested inline views. The innermost view gets the data and orders it by the revenue.
  • The next inline view limits the results of the innermost view where the ROWNUM is less than or equal to 10.
  • The inline view then calculates the ROWNUM of these new results and labels it as “rnum”
  • The outer query then limits the entire result set to where the rnum is greater than 5.

So, in theory, it should show us rows 6 to 10. This is the result from this query:

13 3596297
9 3378075
3 1852828
6 935191
7 654796

It shows rows 6 to 10 when ordering by the revenue in descending order.

Using a WITH Clause with ROWNUM

You can use the WITH clause to write the earlier query. For example, to find the top 5 customers by revenue, you can write the query that orders the customers in the WITH clause, and select from that.

This works in a similar way to an inline view.

The query would look like this:

This will show the same results:

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297

Using RANK and DENSE_RANK for Top-N Queries

You can use the RANK function in Oracle to find the top-N results from a query. It’s a bit more complicated, as you’ll need to use RANK as an analytic function, but the query works.

The query to find the top 5 customers by revenue would look like this:

The ordering is done within the parameters of the RANK function, and the limiting is done using the WHERE clause.

The results of this query are:

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297
13 3596297

This shows 6 results because customer_id 4 and 13 have the same revenue. This is because the RANK function does not exclude rows where there are ties.

You can also use the DENSE_RANK function instead of the RANK function.

The difference between these two functions is that the rank numbers that are assigned do not include gaps. However, it still shows us the top 6 rows as there is a tie.

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297
13 3596297

Using ROW_NUMBER For Top-N Queries

You can use the Oracle analytic function ROW_NUMBER to write top-N queries. It’s similar to the RANK function. The ROW_NUMBER function assigns a unique number for each row returned, but can be used over a window of data (just like all analytic queries).

A query to find the top 5 customers by revenue using ROW_NUMBER would look like this:

It looks very similar to the RANK and DENSE_RANK methods.

The results of this query are:

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297

SQL Server SQL Limit Feature: The SQL Top Keyword


The way to perform row limiting in SQL Server is different from doing it in MySQL.

In SQL Server, you use the SQL TOP keyword rather than LIMIT. The SQL TOP keyword goes at the start of the query in the SELECT clause.

The syntax of a SELECT query that uses TOP is:

The word TOP goes just after the word SELECT, just like the DISTINCT keyword.

You can then specify either the number of rows to display or the percentage of rows from the result set to display.

Let’s look at an example using our sample data. If we wanted to find the same data as the earlier example (the top 5 customers ordered by revenue in descending order), our SQL query would look like this:

The output for this query would be:

2 9384760
10 5131750
11 4431791
8 4341421
4 3596297

It’s the same result as the MySQL LIMIT example. It shows the top 5 customers by revenue.

Both the MySQL and SQL Server features for row limiting or showing the top-N queries are simple. What about Oracle?

Многопоточная репликация

До недавнего времени Mysql реплика работала всего в один поток. Тогда, даже если мастер и слейв идентичны по характеристикам, слейв все равно может отставать от мастера.

В версии Mysql 5.6 внедрена поддержка параллельной репликации. Она позволяет слейву обрабатывать бинлог параллельно в несколько потоков. Такой режим включается настройкой в my.cnf на слейве:


slave-parallel-workers = 2

Число задает количество потоков и может принимать значения от 2 до 1024. Значение отключит многопоточную обработку бинлога.

При включении этой настройки Mysql будет распределять обработку бинлога разных баз данных между разными потоками. Это даст возможность Mysql не ждать завершения операций в разных базах, а выполнять их параллельно. На мастере для этого ничего настраивать не нужно.

Понятно, что если у вас всего одна база данных, вы не получите прирост в производительности. Зато в версии Mysql 5.7 для можно изменить тип распределения операций с помощью настройки в my.cnf:

В таком случае, уже все операции (точнее закомиченные транзакции) из бинлога будут обрабатываться параллельно.

Пример — использование ключевого слова LIMIT

Рассмотрим, как использовать в SQLite оператор SELECT LIMIT.



SELECT employee_id, last_name, first_name FROM employees WHERE favorite_website = ‘Google.com’ ORDER BY employee_id DESC LIMIT 5;

1 2 3 4 5 6 7








В этом SQLite примере SELECT LIMIT будут выбраны первые 5 записей из таблицы employees, где любимый веб-сайт — ‘Google.com’

Обратите внимание, что результаты сортируются по employee_id в порядке убывания, поэтому это означает, что 5 самых больших значений employee_id будут возвращены оператором SELECT LIMIT

Если в таблице employees есть другие записи со значением веб-сайта Google.com, они не будут возвращены оператором SELECT LIMIT в SQLite.

Если бы мы хотели выбрать 5 самых минимальных значений employee_id вместо самых больших, мы могли бы изменить порядок сортировки следующим образом:


SELECT employee_id, last_name, first_name FROM employees WHERE favorite_website = ‘Google.com’ ORDER BY employee_id ASC LIMIT 5;

1 2 3 4 5 6 7








Теперь результаты будут отсортированы по employee_id в порядке возрастания, поэтому первые 5 наименьших записей employee_id, которые имеют fav_website ‘Google.com’, будут возвращены этим оператором SELECT LIMIT. Никакие другие записи не будут возвращены этим запросом.

MySQL LIMIT examples

We’ll use the table from the sample database for demonstration.

1) Using MySQL to get the highest or lowest rows

This statement uses the clause to get the top five customers who have the highest credit:

In this example:

  • First, the clause sorts the customers by credits in high to low.
  • Then, the clause returns the first 5 rows.

Similarly, this example uses the clause to find 5 customers who have the lowest credits:

In this example:

  • First, the clause sorts the customers by credits in low to high.
  • Then, the clause returns the first 5 rows.

Because there are more than 5 customers that have credits zero, the result of the query above may lead to an inconsistent result.

To fix this the issue, you need to add more column to the clause to constrain the row in unique order:

2) Using MySQL for pagination

When you display data on applications, you often want to divide rows into pages, where each page contains a certain number of rows like 5, 10, or 20.

To calculate the number of pages, you get the total rows divided by the number of rows per page. For fetching rows of a specific page, you can use the clause.

This query uses the aggregate function to get the total rows from the table:

Suppose that each page has 10 rows, to display 122 customers you have 13 pages. The last 13th page contains two rows only.

This query uses the clause to get rows of page 1 which contains the first 10 customers sorted by the customer name:

This query uses the clause to get the rows of the second page that include row 11 – 20:

In this example, the clause returns 10 rows for the row 11 – 20.

3) Using MySQL to get the nth highest or lowest value

To get the nth highest or lowest value, you use the following clause:

The clause returns row starting at the row .

For example, the following finds the customer who has the second-highest credit:

Let’s double-check the result. This query returns all customers sorted by credits from high to low:

As you can see clearly from the output, the result was correct as expected.

Note that this technique works when there are no two customers who have the same credit limits. To get a more accurate result, you should use the window function.

In this tutorial, you have learned how to use MySQL  clause to constrain the number of rows returned by the  statement.

  • Was this tutorial helpful?


The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s) FROM table_nameWHERE condition;

MySQL Syntax:

SELECT column_name(s) FROM table_nameWHERE condition LIMIT number;

Oracle Syntax:

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

MySQL Numeric Functions

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of one or two numbers
ATAN2 Returns the arc tangent of two numbers
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is >= to a number
CEILING Returns the smallest integer value that is >= to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records returned by a select query
DEGREES Converts a value in radians to degrees
DIV Used for integer division
EXP Returns e raised to the power of a specified number
FLOOR Returns the largest integer value that is <= to a number
GREATEST Returns the greatest value of the list of arguments
LEAST Returns the smallest value of the list of arguments
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number, or the logarithm of a number to a specified base
LOG10 Returns the natural logarithm of a number to base 10
LOG2 Returns the natural logarithm of a number to base 2
MAX Returns the maximum value in a set of values
MIN Returns the minimum value in a set of values
MOD Returns the remainder of a number divided by another number
PI Returns the value of PI
POW Returns the value of a number raised to the power of another number
POWER Returns the value of a number raised to the power of another number
RADIANS Converts a degree value into radians
RAND Returns a random number
ROUND Rounds a number to a specified number of decimal places
SIGN Returns the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Calculates the sum of a set of values
TAN Returns the tangent of a number
TRUNCATE Truncates a number to the specified number of decimal places

Понимание спецификации пакета.

Спецификация пакета содержит публичные объявления элементов. Область этих объявлений является локальной для вашей схемы базы данных и глобальной для пакета. Итак, объявленные элементы доступны из вашего приложения и из любой точки пакета.

В спецификации перечислены элементы пакета, доступные приложениям. Вся информация, необходимая вашему приложению для использования элементов, содержится в спецификации. Например, следующее объявление показывает, что функция с именем fac принимает один аргумент типа INTEGER и возвращает значение типа INTEGER:

Oracle PL/SQL



Это вся информация, необходимая для вызова функции. Вам не нужно рассматривать его базовую реализацию (например, итеративную или рекурсивную).

Только подпрограммы и курсоры имеют базовую реализацию. Таким образом, если спецификация объявляет только типы, константы, переменные, исключения и спецификации вызовов, то тело пакета не требуется. Рассмотрим следующий пакет без тела:

Oracle PL/SQL

CREATE PACKAGE trans_data AS — bodiless package TYPE TimeRec IS RECORD ( minutes SMALLINT, hours SMALLINT); TYPE TransRec IS RECORD ( category VARCHAR2, account INT, amount REAL, time_of TimeRec); minimum_balance CONSTANT REAL := 10.00; number_processed INT; insufficient_funds EXCEPTION; END trans_data;

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATEPACKAGEtrans_dataAS— bodiless package













Пакет trans_data не нуждается в теле, потому что типы, константы, переменные и исключения не имеют базовой реализации. Такие пакеты позволяют вам определять глобальные переменные — используемые подпрограммами и триггерами базы данных — которые сохраняются в течение сеанса.

Ссылка на содержание пакета

Чтобы ссылаться на типы, элементы, подпрограммы и спецификации вызовов, объявленные в спецификации пакета, используйте точечную нотацию следующим образом:





Вы можете ссылаться на содержимое пакета с помощью триггеров базы данных, хранимых подпрограмм, прикладных программ 3GL и различных инструментов Oracle. Например, вы можете вызвать процедуру пакета hire_employee из PL/SQL Developer следующим образом:

Oracle PL/SQL

BEGIN emp_actions.hire_employee(‘VLADIMIR’, ‘MEDIC’, …); END;

1 2 3





Вы не можете ссылаться на удаленные переменные пакета прямо или косвенно. Например, вы не можете вызвать следующую процедуру удаленно, потому что она ссылается на переменную пакета, когда параметр инициализируется:

Oracle PL/SQL

CREATE PACKAGE random AS seed NUMBER; PROCEDURE initialize (starter IN NUMBER := seed, …);

1 2 3




Кроме того, внутри пакета вы не можете ссылаться на переменные хоста.

С этим читают