variable l_task_id VARCHAR2(64);

exec :l_task_id:=dbms_sqlpa.create_analysis_task(
     sqlset_name => ''
    , sqlset_owner=>''
    ,task_name => '');

select parameter_name,parameter_value 
from dba_advisor_exec_parameters 
where task_name='' and parameter_name='SQLSET_NAME';

sql
ALTER function [dbo].[ISM_fnGetUID_TS] ()
returns @Results table
  (
    UserID int --primary key
  )
as -- body of the function
begin

  insert  @Results
  select  dbo.ISM_fnGetUID_Lite()

  return
end


ALTER FUNCTION [dbo].[ISM_fnGetUID_Lite] ()  
RETURNS int  
AS  
BEGIN  
  return  (
    select UserID = case
      when app_name() like '%@App' then cast(left(app_name(), charindex('@', app_name()) - 1) as int)
      else coalesce(  
        convert(smallint,substring(context_info(),1,2)),
        (select UserID from dbo.TS_User u where (u.UserName = system_user and u.Disabled = 0)))  
    end
  )
END

[mysqld]
user            = mysql
bind-address            = *
mysqlx-bind-address     = 127.0.0.1
key_buffer_size         = 8M
max_allowed_packet      = 128M
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log                = 1
slow_query_log_file   = /var/log/mysql/mysql-slow.log
long_query_time = 2
max_binlog_size   = 100M

log_timestamps=SYSTEM
skip_name_resolve=ON

mysqlslap --concurrency=100 --iterations=20 --number-int-cols=30 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-execute-number=10

Benchmark
        Average number of seconds to run all queries: 0.386 seconds
        Minimum number of seconds to run all queries: 0.345 seconds
        Maximum number of seconds to run all queries: 0.531 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

>>  MySQLTuner 2.2.7
         * Jean-Marie Renouard 
         * Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Using mysql to check login
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 4.4G (Tables: 942)
[OK] Total fragmented tables: 0

[OK] Currently running supported MySQL version 8.0.33-0ubuntu0.22.04.4

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (986B)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 MB
[!!] Log file /var/log/mysql/error.log isn't readable.

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 30m 4s (74K q [41.427 qps], 2K conn, TX: 14G, RX: 62M)
[--] Reads / Writes: 78% / 22%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 704.7M
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 1.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 248M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 613.2M (1.91% of installed RAM)
[OK] Maximum possible memory usage: 704.7M (2.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/74K)
[OK] Highest usage of available connections: 68% (103/151)
[OK] Aborted connections: 0.00% (0/2704)
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 4% (122 temp sorts / 2K sorts)
[!!] Joins performed without indexes: 159
[OK] Temporary tables created on disk: 0% (0 on disk / 1K total)
[!!] Thread cache hit rate: 31% (1K created / 2K connections)
[OK] Table cache hit rate: 94% (69K hits / 73K requests)
[OK] table_definition_cache (2000) is greater than number of tables (1271)
[OK] Open file limit used: 0% (3/10K)
[OK] Table locks acquired immediately: 100% (182 immediate / 182 locks)
[OK] Binlog cache memory access: 99.99% (15178 Memory / 15180 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 248.8M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :8.0M
[--]  +-- KB Used Size :1.5M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 4.4G
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (75%): 100.0M / 128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.95% (10397117 hits / 10402401 total)
[!!] InnoDB Write Log efficiency: 77.34% (100765 hits / 130280 total)
[OK] InnoDB log waits: 0.00% (0 waits / 29515 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    thread_cache_size (> 9)
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_redo_log_capacity should be (=32M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

-- MYSQL PERFORMANCE TUNING PRIMER --
      - By: Matthew Montgomery -

MySQL Version 8.0.33-0ubuntu0.22.04.4 x86_64

Uptime = 0 days 0 hrs 33 min 13 sec
Avg. qps = 40.26
Total Questions = 80240
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 

Visit https://github.com/BMDan/tuning-primer.sh  for the latest version of
this script, or to suggest improvements.

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
Since startup, 0 out of 80261 queries have taken longer than  to complete.
Your long_query_time seems reasonable.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html 

WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 8
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 103
The number of used connections is 68% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 749 M
Current InnoDB data space = 3.64 G
Current InnoDB buffer pool free = 25 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 348 M
Configured Max Per-thread Buffers : 287 M
Configured Max Global Buffers : 152 M
Configured Max Memory Limit : 439 M
Physical Memory : 31.30 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
No key reads.  If you aren't using MyISAM, this is normal.  If you are
using MyISAM, this is very, very bad.
Current MyISAM index space = 0 bytes
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Your server does not support the query cache.  That's probably a good thing.

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 187 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 1090 tables
You have 2109 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 1244 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 84 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 80525
Your table locking seems to be fine

innodb_buffer_pool_size=5G
innodb_redo_log_capacity=1250M

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 5368709120 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_redo_log_capacity | 1310720000 |
+--------------------------+------------+
1 row in set (0.01 sec)

Benchmark
        Average number of seconds to run all queries: 0.387 seconds
        Minimum number of seconds to run all queries: 0.349 seconds
        Maximum number of seconds to run all queries: 0.552 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

2024-09-09 05:22:34 LOG:  checkpoint starting: time
2024-09-09 05:33:37 LOG:  checkpoint complete: wrote 779881 buffers (8.9%); 0 WAL file(s) added, 483 removed, 664 recycled; write=652.802 s, sync=0.165 s, total=662.212 s; sync files=170, longest=0.059 s, average=0.001 s; distance=12326973 kB, estimate=12326973 kB

2024-09-09 05:34:27 LOG:  checkpoint starting: wal
2024-09-09 05:48:27 LOG:  checkpoint complete: wrote 1818364 buffers (20.7%); 0 WAL file(s) added, 0 removed, 1009 recycled; write=837.323 s, sync=0.097 s, total=839.269 s; sync files=175, longest=0.024 s, average=0.001 s; distance=16533226 kB, estimate=16533226 kB

2024-09-09 05:50:24 LOG:  checkpoint starting: wal
2024-09-09 06:06:02 LOG:  checkpoint complete: wrote 1302347 buffers (14.8%); 0 WAL file(s) added, 0 removed, 1010 recycled; write=938.102 s, sync=0.068 s, total=938.629 s; sync files=301, longest=0.011 s, average=0.001 s; distance=16547328 kB, estimate=16547328 kB

2024-09-09 06:08:20 LOG:  checkpoint starting: wal
2024-09-09 06:35:20 LOG:  checkpoint complete: wrote 1015915 buffers (11.6%); 0 WAL file(s) added, 0 removed, 880 recycled; write=1619.413 s, sync=0.038 s, total=1619.779 s; sync files=488, longest=0.020 s, average=0.001 s; distance=16548651 kB, estimate=16548651 kB

2024-09-09 06:38:20 LOG:  checkpoint starting: time
2024-09-09 07:05:20 LOG:  checkpoint complete: wrote 320834 buffers (3.7%); 0 WAL file(s) added, 0 removed, 376 recycled; write=1619.469 s, sync=0.004 s, total=1619.906 s; sync files=755, longest=0.001 s, average=0.001 s; distance=13792920 kB, estimate=16273078 kB

shared_buffers: 67GB
 wal_buffers:    32MB
 full_page_writes: on
 max_wal_size:   30GB
 checkpoint_timeout: 30min
 checkpoint_completion_target: 0.9
 min_wal_size: 512MB
 block_size: 8192
 wal_segment_siz: 16MB

CREATE TABLE trade
(
    id INT PRIMARY KEY NOT NULL,
    symbol VARCHAR(),
    open_time TIMESTAMP,
    end_price INT
)

SELECT * FROM table WHERE symbol=x and open_time>start_time and open_time

CREATE INDEX open_time ON trade(open_time);
CREATE INDEX symbol ON trade(symbol);
CREATE INDEX end_price ON trade(open_price);