I noticed this new class from MIT:
It is about machine learning and is free. I think it has some built in exercises with automatic grading but no instructor to interact with.
Since ML is such a hot topic I thought I would share it. I have not taken the class.
Bobby
Just a quick note. I have an application that is generating thousands of inactive sessions and with the default dedicated server configuration we are having to add more and more memory to our virtual host to support the connections. We estimate that the application may need 45,000 mostly inactive sessions once the application is fully rolled out. So, I thought about how much memory would be required to support 45,000 sessions using shared servers. In an earlier post I mentioned how I got the sessions up to about 11,000 so I just took the Java program from that post and tried to adjust memory parameters to support over 45,000. I got it up to 0ver 60,000 so the test was essentially successful. I don’t think I would want to run a system with 60,000 sessions on a single node, but it is nice to see that it is to some degree possible.
I used a 64 gigabyte Linux VM and set these parameters:
sga_max_size=52G
sga_target=52G
shared_pool_size=36G
dispatchers='(PROTOCOL=TCP)(DISPATCHERS=64)'
max_shared_servers=16
shared_servers=16
large_pool_size=512MPretty sure that the large pool grew dynamically to fill the sga space not taken up by the shared pool. 52-36=16 gigabyte large pool.
Anyway, I don’t have time to write this up carefully now, but I wanted to publish the parameters.
Here is the previous post with the Java program I used to open 1000 connections:
I ended up running 30 of these on 3 servers for a total of 90,000 potential logins and got up to over 63,000.
Bobby
I ran this query with a hint:
SQL> select /*+ full(my_tables) */ blocks
2 from my_tables
3 where
4 owner = 'SYS' and
5 table_name = 'TAB$';
BLOCKS
----------
1625
I ran this select to get the plan:
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));I was getting this error:
Column Projection Information (identified by operation id): 1 - "BLOCKS"[NUMBER,22] ORA-00904: : invalid identifier
I found that my user or public needed an execute grant for DBMS_LOB to fix this:
SQL> grant execute on DBMS_LOB to PUBLIC;
Grant succeeded.I am not sure why this grant was not in place on this database but it took a while to figure this out so I thought I would put it out there. I found the error in a trace and I suspected the issue was due to permissions. The trace was like:
PARSE ERROR ... err=904
SELECT ST.* FROM XMLTABLE('/hint_usage/... DBMS_LOB.SUBSTR...
So that gave me the idea that I needed an execute grant on DBMS_LOB. EXECUTE ANY PROCEDURE did not do it.
After the grant it shows the hint report. This is on 19c:
Column Projection Information (identified by operation id):
1 - "BLOCKS"[NUMBER,22]
Hint Report (identified by...
Total hints for statement: 1
1 - SEL$1 / MY_TABLES@SEL$1
- full(my_tables)
Bobby
P.S. Full log of the script that got the error:
Full log of the working script:
Full length trace lines:
I want to document some recent steps that I have been taking to support new development on a transactional system. Every time the system has a new release, if that release includes Oracle SQL changes, I am asked to create and review an AWR report covering the time of a load test (usually several tests) and to see if I see any problems. In the past I looked for longer running application SQL but recently I changed to look at anything that averages over .1 seconds and that has been helpful. So, that is what this post is about. Obviously, if you have faster hardware or different workloads this rule of thumb will not help you. But maybe other higher-volume transactional systems will follow similar patterns.
Here is the top elapsed SQL from a recent load test:
I have only shown some of the columns to hide some details that I do not want to make public. Every SQL here whose “Elapsed Time per Exec (s)” value is above .1 seconds is not part of the application except the last one, 6kmnq0uj99a3c. This was a load test on a non-production system that ramped up a bunch of activity over several hours. This problem query only ran 664 times so if that is representative of how often it runs in production it may not really matter that it is inefficient. But you never know for sure, so I reviewed it anyway. All the queries listed that run in .03, .01, .02, and .00 seconds are representative of the typical queries with good plans on this system. So, that is why .1 ended up standing out. Also, not long ago I found two or three running in production with more than .1 seconds average runtime and they needed new indexes to bring them back under the .1 second threshold. So, for me .1 seconds is the current magical number.
To test it I used two of my scripts.
I replaced the bind variables with constants and ran the query in my test2.sql script and found that most of the time was on a certain range scan on what looked like the correct index. But on closer inspection I realized that a type conversion had prevented the last column of the index from being used. Here is what it looked like with the columns renamed to hide the production names.
filter((TO_NUMBER(MY_NUMBER)="XYZ"."MY_NUMBER" ...
One table has the “MY_NUMBER” column as a character string and the other as a number. So, it was doing a range scan and not a unique scan. I changed the query to convert the number to a character string and the plan used a unique scan.
ABC.MY_NUMBER= to_char(XYZ.MY_NUMBER)
Table ABC was the one that was doing a range scan on three columns and not on MY_NUMBER, the last column in the index. MY_NUMBER is a character column on ABC. XYZ was the other table with MY_NUMBER as a NUMBER type column. I am forcing the conversion of XYZ.MY_NUMBER to a character for the comparison instead of letting the optimizer choose to convert ABC.MY_NUMBER to a number which would suppress the use of the last column of the index on table ABC.
My point was not to talk about implicit type conversions preventing indexes from being used although that is very interesting. My point is that a plan like this that is pretty efficient could run less than .1 seconds if the index was used correctly. And if the application users end up scaling the use of this query way up to thousands or tens of thousands of executions per hour that unique scan could make a huge difference over the range scan without the last column of the index. Your CPUs might be 10 times faster than mine so your threshold might be lower than .1 seconds, but I think the idea is the same. There is some threshold that indicates a simple, frequently used, transactional SQL may not be using the right indexes. Does not apply in all cases but at the moment this is a useful rule of thumb for me.
I had just written the previous paragraphs before getting an email that our QA team had run another load test with the to_char explicit type conversion in place. It did not make as great of an improvement as I expected. Here are some edited outputs from my sqlstat.sql script:
Original query 6kmnq0uj99a3c:
END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms 09-MAR-21 11.00.08 AM 79 171.306696 09-MAR-21 12.00.35 PM 84 176.152667 09-MAR-21 01.00.03 PM 80 178.420588 09-MAR-21 02.00.32 PM 80 171.877913 09-MAR-21 03.00.01 PM 81 174.509975 09-MAR-21 04.00.29 PM 83 180.367157
New query 2ndfgypwp3qf0 with the to_char to allow the unique index scan:
END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms 12-MAR-21 09.00.15 AM 80 107.822088 12-MAR-21 10.00.44 AM 83 104.453446 12-MAR-21 11.00.12 AM 81 105.34042 12-MAR-21 12.00.42 PM 80 103.05625 12-MAR-21 01.00.12 PM 79 106.738557 12-MAR-21 02.00.42 PM 82 101.285183 12-MAR-21 03.00.12 PM 81 105.172531
Kind of disappointing. I expected a greater improvement based on my testing. Still, .1 seconds per execution is better than .17. Maybe if the tables grow with more data over time this improvement will be greater.
Even though this query did not turn out to have a dramatic improvement I did find a way to improve the plan. My .1 seconds cutoff pointed me to a query that did not have the ideal use of indexes and lead to an improvement in performance. In other cases, in the past I have seen 20x improvements so it is worth reviewing the ones over .1 seconds.
Bobby
Just a quick note about two tools I use:
TextPad – my favorite text editor. I know everyone has their own, but this is mine.
UnixUtls – Unix like tools on a Windows command line.
Bobby
P.S. I need to do a post about my approach to doing database work. I work from a Windows 10 laptop supporting Linux and HP Unix Oracle database servers. I am command line oriented and I like my Windows text editor. But some things work better on the database server itself such as a query that dumps out a ton of output. Or if you want to get a timing on a query without including the network time from my laptop (now at home over the VPN). But usually it is easier to work on my laptop without any network between me and my files. Plus, my 64-bit editor can open very large files. I use Python as my main programming language, and I have a 32-bit Python installed on my laptop. The UnixUtls let me do things like ls, find, and diff on my laptop. I probably use diff the most. All of this is in the Windows command prompt. I am a dinosaur I know with my command line orientation. Anyway, there is a ton I could say so rather than post a huge discussion I at least wanted to mention the two tools above.
I have been having trouble using WordPress on this blog. I keep having long lines wrapped instead of having a slider that readers can use to see the end of the lines. Also, pictures that looked fine when I posted them later look squished together. Yuck.
First, I will try to put some longer lines of output here using the preformatted type of block:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed 6kmnq0uj99a3c 65249283 09-MAR-21 01.00.03.869 PM 80 178.420588 163.875 0 0 0 0 13345.9375 0 0 829.6 6kmnq0uj99a3c 65249283 09-MAR-21 02.00.32.536 PM 80 171.877913 159.875 0 0 0 0 13122.1375 0 0 816.0125 6kmnq0uj99a3c 65249283 09-MAR-21 03.00.01.160 PM 81 174.509975 159.876543 0 0 0 0 13145.2346 0 0 818.111111 6kmnq0uj99a3c 65249283 09-MAR-21 04.00.29.556 PM 83 180.367157 164.939759 0 0 0 0 13286.4337 0 0 825.843373 6kmnq0uj99a3c 65249283 09-MAR-21 05.00.56.089 PM 40 26.11575 21.5 1.9689 0 0 0 915.7 3.425 0 51
Notice how it wraps around and looks unreadable. I could swear that either a preformatted or a code block did not wrap in the recent past. Here is the same text in a code block:
SQL_ID PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average disk write megabytes Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ---------------------------- ----------------------
6kmnq0uj99a3c 65249283 09-MAR-21 01.00.03.869 PM 80 178.420588 163.875 0 0 0 0 13345.9375 0 0 829.6
6kmnq0uj99a3c 65249283 09-MAR-21 02.00.32.536 PM 80 171.877913 159.875 0 0 0 0 13122.1375 0 0 816.0125
6kmnq0uj99a3c 65249283 09-MAR-21 03.00.01.160 PM 81 174.509975 159.876543 0 0 0 0 13145.2346 0 0 818.111111
6kmnq0uj99a3c 65249283 09-MAR-21 04.00.29.556 PM 83 180.367157 164.939759 0 0 0 0 13286.4337 0 0 825.843373
6kmnq0uj99a3c 65249283 09-MAR-21 05.00.56.089 PM 40 26.11575 21.5 1.9689 0 0 0 915.7 3.425 0 51
Basically, the same problem although font and background are different. One thing I have done in the past is use a GitHub Gist. I would paste the text into a gist and put the URL inline like this:
https://gist.github.com/bobbydurrett/792f10405a7c4c6acbf965abc31ad3c6
This no longer seems to work. I had to go back and change a bunch of posts with links like this to embed the gist in the posts. To do that I had an amusing set of steps:
Example of what I have to paste in:
<p><script src="https://gist.github.com/bobbydurrett/792f10405a7c4c6acbf965abc31ad3c6.js"></script></p>Here are the long lines as an embedded gist:
It would be great if there were a simpler way to do this. Maybe there is.
The second challenge is that when I paste in screenshots, they get all squished. Here is a graphical version of the same type data:
The picture is not square, so it gets squished in. It is nice that you can click on it and see the big version, but I would like it to not be so ugly beforehand.
Thumbnail is 150 x 150 and very small.
75% is still squished
50% is not squished but the text is small. At least you can click on it and the big version pops up.
As I am writing this I realize there is a guide that you can click on to manually size the picture and it shows you have far to the right you can size it before it starts getting squished.
So, I guess for now I am stuck with either making my text lines short enough to fit or sticking them in a Gist. For images I just need to size them with the little tool to keep them within the margins, so they do not get pushed in to fit.
Bobby
I posted a question about this and didn’t find an answer. I searched both Oracle’s support site and the internet in general. I ran the DBVERIFY utility dbv against a bigfile datafile that had corrupt blocks and wanted to relate the blocks back to a table using the DBA_EXTENTS view. For smallfile datafiles I could have used dbms_utility.data_block_address_block. But the manual says that it does not work with bigfiles. I did a small test and found that with bigfiles the address output by dbv is just the block_id within the data file. With a smallfile tablespace it was some combination of block_id and file_id. Really, it is more helpful for dbv to spit out the block_id if you are running it against a datafile because you already know which datafile you have. I will include some of the output of the test below.
Steps of my test:
1 - create a small bigfile tablespace
create bigfile tablespace big datafile '/home/oracle/product/oradata/ORCL/big.dbf' size 10M;
[oracle@ora19 bigfiletests]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 3 07:46:15 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> create bigfile tablespace big datafile '/home/oracle/product/oradata/ORCL/big.dbf' size 10M;
Tablespace created.
This was on a small 19c test database on a Linux VM.
2 - create empty table - maybe ctas and truncate select * from dba_tables;
ORCL:SYSTEM>create table bigfiletest tablespace big nologging as select * from dba_tables where 1=2;
Table created.Had to create table with NOLOGGING to make the insert append below unrecoverable.
3 - take a rman backup
[oracle@ora19 ORCL]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 3 08:11:29 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1534990846)
RMAN> backup database;
Starting backup at 03-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/product/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/home/oracle/product/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/home/oracle/product/oradata/ORCL/undotbs01.dbf
input datafile file number=00005 name=/home/oracle/product/oradata/ORCL/big.dbf
input datafile file number=00007 name=/home/oracle/product/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAR-21
channel ORA_DISK_1: finished piece 1 at 03-MAR-21
piece handle=/home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1 tag=TAG20210303T081136 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
Finished backup at 03-MAR-21
Starting Control File and SPFILE Autobackup at 03-MAR-21
piece handle=/home/oracle/product/db/19.0.0/dbs/c-1534990846-20210303-02 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-21
Just a regular backup before doing the unrecoverable insert append into nologging table.
4 - do a nologging update - insert append select * from dba_tables commit
ORCL:SYSTEM>insert /*+ append */ into bigfiletest
2 select * from dba_tables where rownum < 2;
1 row created.
ORCL:SYSTEM>
ORCL:SYSTEM>commit;
Commit complete.Just one row – should be one corrupt block.
5 - delete tablespace/datafile
[oracle@ora19 ORCL]$ cd /home/oracle/product/oradata/ORCL
[oracle@ora19 ORCL]$ ls -altr
total 2813440
drwxr-x---. 3 oracle oinstall 17 Jul 30 2019 ..
-rw-r-----. 1 oracle oinstall 209715712 Mar 3 07:50 redo02.log
-rw-r-----. 1 oracle oinstall 209715712 Mar 3 07:50 redo03.log
-rw-r-----. 1 oracle oinstall 57679872 Mar 3 07:51 temp01.dbf
drwxr-x---. 2 oracle oinstall 4096 Mar 3 08:02 .
-rw-r-----. 1 oracle oinstall 1142956032 Mar 3 08:11 system01.dbf
-rw-r-----. 1 oracle oinstall 692068352 Mar 3 08:11 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 356524032 Mar 3 08:11 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 Mar 3 08:11 users01.dbf
-rw-r-----. 1 oracle oinstall 10493952 Mar 3 08:14 big.dbf
-rw-r-----. 1 oracle oinstall 209715712 Mar 3 08:15 redo01.log
-rw-r-----. 1 oracle oinstall 10600448 Mar 3 08:15 control01.ctl
-rw-r-----. 1 oracle oinstall 10600448 Mar 3 08:15 control02.ctl
[oracle@ora19 ORCL]$ rm big.dbfNow the unrecoverable nologging insert append change is lost. It is not on the backup and not on the redo or archived redo logs.
6 - restore and recover it
[oracle@ora19 ORCL]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 3 08:16:07 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1534990846)
RMAN> alter tablespace big offline immediate;
using target database control file instead of recovery catalog
Statement processed
RMAN> restore tablespace big;
Starting restore at 03-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/product/oradata/ORCL/big.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1
channel ORA_DISK_1: piece handle=/home/oracle/product/db/19.0.0/dbs/04voq09o_4_1_1 tag=TAG20210303T081136
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-MAR-21
RMAN> recover tablespace big;
Starting recover at 03-MAR-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-MAR-21
RMAN> alter tablespace big online;
Statement processed
Simple tablespace restore and recovery. Had to alter tablespace offline immediate because the file was not there.
7 - verify corruption exists
ORCL:SYSTEM>select * from bigfiletest;
select * from bigfiletest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 787)
ORA-01110: data file 5: '/home/oracle/product/oradata/ORCL/big.dbf'
ORA-26040: Data block was loaded using the NOLOGGING optionThis just shows that the block is corrupt. It also gives us the file number (which we already knew) and the block id which would relate back to DBA_EXTENTS.
8 - run dbv to get DBAs - block addresses
dbv file=/home/oracle/product/oradata/ORCL/big.dbf blocksize=8192
[oracle@ora19 ORCL]$ dbv file=/home/oracle/product/oradata/ORCL/big.dbf blocksize=8192
DBVERIFY: Release 19.0.0.0.0 - Production on Wed Mar 3 08:21:45 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /home/oracle/product/oradata/ORCL/big.dbf
DBV-00201: Block, DBA 787, marked corrupt for invalid redo application
This was kind of the moment of truth. The DBA from the DBVERIFY utility dbv was 787 which is the same as the block number in the error from the select.
RMAN VALIDATE has the same block number – 787.
Seems to be the forth block. The extent starts at block 784 but block 787 is corrupt.
I had a larger test database with many corrupt blocks due to the way we had populated it with an RMAN restore and recover. I knew which table was corrupt because I ran select count(*) queries against every table on the database and only found one corrupt. Using the DBA value from dbv against the DBA_EXTENTS view for over 300 sample corrupt blocks that all pointed back to the table I knew was corrupt. I queried it like this:
SELECT
tablespace_name, segment_type, owner, segment_name
FROM my_extents
WHERE file_id = 29 and 15340893 between block_id AND block_id + blocks - 1;
I created the my_extents table from dba_extents to speed up these queries:
create table my_extents as
select * from dba_extents;
create index my_extents_i1 on my_extents(block_id);
execute dbms_stats.gather_table_stats('MYOWNER','MY_EXTENTS');
Anyway, I do not know if this holds true in every situation, but it appears that the DBA values from dbv for bigfiles correspond to the block_id values in DBA_EXTENTS.
Bobby
It looks like this blog is costing me about $6 per month on AWS which is cool. I was paying about $200/year or about $17 per month on iPage. I am not sure what I am missing. This blog is much faster on AWS even with a minimal size virtual machine.
I did pay a fixed, up-front $12 to switch my domain registrar to AWS and that is not included in the $6/month. The compute was $123.97 including tax for 3 years or 36 months. That buys use one 2.5 gigahertz processor and 1 gig of memory. Disk is extra as is a couple of other things. The extras were $2.27 for February which of course only has 28 days. If the site were to get busy, then the monthly price would go up but that seems unlikely.
Not sure what I am missing. iPage did more for you and could be used by a non-technical person but if you can handle the technical part AWS seems better and cheaper.
Bobby
Most of my work is with Oracle databases but I have been starting to learn a little bit about MySQL. Where I work, we have a bunch of on premises Oracle databases including large and active ones with various challenging performance tuning problems that I enjoy working on. But for the last couple or so years we have been using MySQL on RDS within Amazon’s cloud (AWS). I have not had many chances to work on a MySQL performance issue, but I want to be ready when the time comes. Back in 2019 I read several chapters of the MySQL 5.7 reference manual and started looking at tuning. Last week I picked it up again. I was mainly looking at Amazon’s Performance Insights that we have running on our RDS databases and seeing what information it gave us and how to link that back to other tables/views that MySQL uses for performance. Anyway, I have made notes on our internal web pages, but I also decided to create a public GitHub repository with some of the SQL and Python scripts that I have created so far. It is very basic, and I am sure that other people have more sophisticated tools, but I think it is helpful to me to create the repository and to update it as I learn more. So, if you are interested here it is:
https://github.com/bobbydurrett/MySQL-Scripts
Bobby
I was using Centos Linux for Nethack programming and playing on my home laptop. Evidently Centos is going away at the end of 2021 and Tim Hall had a post about moving Centos to Oracle Linux. So, I did it. The theory was that I would still be able to get updates from Oracle in 2022 and beyond.
I followed the instructions from here: https://linux.oracle.com/switch/centos/
I did this while logged in as root:
git clone https://github.com/oracle/centos2ol
cd centos2ol
bash centos2ol.shI got this error:
Error: Package: tkinter-2.7.5-88.el7.x86_64 (@base/7)
Requires: python = 2.7.5-88.el7I do not need tkinter so I just removed it:
yum remove tkinter-2.7.5-88.el7.x86_64Then I just did a yum update and it seemed fine. The only odd thing is that when I reboot, I get an error like this:
integrity: Unable to open file: /etc/keys/x509_ima.der (-2)I looked up this message and it seems like I cannot fix it but can just ignore it. Not a big deal but would be cleaner without it.
It was easy to switch but I just thought I would document the two error messages, even though they did not cause any problems.
Bobby
