Feed aggregator
using insert into partition (partition_name) in PLSQL
Partition List Iterator occasionally reading way too many partitions still
I’m triple posting this on oracle-l, my blog and on Oracle community to get some more help.
Apologies if you’ve tried to help before and it didn’t come to anything.
This is a redux of an old issue which I looked at 18 months ago but with a new example of the problem.
https://orastory.wordpress.com/2021/02/22/oracle-19c-upgrade-query-reading-way-more-partitions-than-it-should/
https://community.oracle.com/tech/developers/discussion/4480886/partition-list-iterator-reading-too-many-partitions/
When the problem originally surfaced it was after an upgrade from 11.2.0.4 to 19.6. Now version is 19.12.
Now I have one example which gets executed four or five times a day
The example has two sql ids involved in the process but both are almost identical and have identical sql plans.
Queries in question get executed after specific data loads.
For each data load where it gets executed, it will be doing pretty much the same workload day-on-day.
The query gets supplied a collection which tends to have one or a couple of rows row and each row in the collection provides the values to prune to a specific subpartition.
The issue is that about once a week, an execution will go slow.
Exactly the same sql plan each execution.
When it works, it runs in a couple of seconds max.
When it goes wrong, the partition list iterator decides to read every partition/subpartition.
This is the snippet of the plan in question:
| 4 | NESTED LOOPS | | 39 | 4017 | 334 (2)| 00:00:01 | | |
| 5 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 2 | 29 (0)| 00:00:01 | | |
| 6 | PARTITION LIST ITERATOR | | 3853 | 380K| 305 (2)| 00:00:01 | KEY | KEY |
| 7 | PARTITION LIST ITERATOR | | 3853 | 380K| 305 (2)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS STORAGE FULL | POSITION_BALANCE | 3853 | 380K| 305 (2)| 00:00:01 | KEY | KEY |There was some speculation about possible causes in the OTN thread.
The best guess was that there is some timeout or invalidation or event or contention which means Oracle decides that it cannot reliably prune.
There might be other data loads ongoing.
There could be a partition operation (add new or exchange) which coincides with the degraded iterator performance.
There are > 70k partitions / subpartitions.
select count(*) from dba_tab_partitions where table_name = 'POSITION_BALANCE';
COUNT(*)
----------
60941
select count(*), sum(case when segment_created = 'YES' then 1 else 0 end) from dba_tab_subpartitions where table_name = 'POSITION_BALANCE';
COUNT(*) SUM(CASEWHENSEGMENT_CREATED='YES'THEN1ELSE0END)
---------- -----------------------------------------------
78154 33705I’m reasonably comfortable that what I’ve described is roughly what’s happening.
ASH confirms that it’s reading lots of subpartitions
Just got no idea why and how to figure that out.
The average-ish once-a-week occurence makes it tricker.
Even though when it runs in runs in a couple of seconds, I do have some of the ASH samples that show this.
select /*+ parallel(4) */ instance_number, sql_id, sql_plan_hash_value, sql_full_plan_hash_value, sql_exec_id, sql_exec_start, min(sample_time), max(sample_time), max(sample_time) - min(sample_time) duration, count(*) cnt_samples
, sum(case when in_sql_execution = 'Y' then 1 else 0 end) cnt_in_sql_execution
, sum(case when in_parse = 'Y' then 1 else 0 end) cnt_in_parse
, sum(case when in_hard_parse = 'Y' then 1 else 0 end) cnt_in_hard_parse
from dba_hist_active_sess_history h
where 1=1
and h.user_id IN (select user_id from dba_users where username like '%TODS%')
and sql_id in ('fw985yayy1mfz','0gk5hj9wnp2vs')
--and sql_id = 'ft2prc1xx2hmd'
and sql_exec_id is not null
group by instance_number, sql_id, sql_plan_hash_value, sql_full_plan_hash_value, sql_exec_id, sql_exec_start
order by h.sql_exec_start;
INSTANCE_NUMBER SQL_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_EXEC_START MIN(SAMPLE_TIME) MAX(SAMPLE_TIME) DURATION CNT_SAMPLES CNT_IN_SQL_EXECUTION CNT_IN_PARSE CNT_IN_HARD_PARSE
--------------- ------------- ------------------- ------------------------ ----------- -------------------- ------------------------------- ------------------------------- ------------------- ----------- -------------------- ------------ -----------------
4 0gk5hj9wnp2vs 2221895109 2867134094 67108864 23-MAY-2022 09:18:24 23-MAY-22 09.18.27.378000000 AM 23-MAY-22 09.18.27.378000000 AM +00 00:00:00.000000 1 1 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331648 24-MAY-2022 07:59:49 24-MAY-22 07.59.50.659000000 AM 24-MAY-22 07.59.50.659000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554432 25-MAY-2022 07:09:07 25-MAY-22 07.09.08.709000000 AM 25-MAY-22 07.09.08.709000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554434 25-MAY-2022 09:41:53 25-MAY-22 09.41.59.718000000 AM 25-MAY-22 12.41.49.605000000 PM +00 02:59:49.887000 1054 1054 0 0
2 fw985yayy1mfz 2221895109 2867134094 33554434 25-MAY-2022 10:57:41 25-MAY-22 10.57.49.221000000 AM 25-MAY-22 01.56.37.861000000 PM +00 02:58:48.640000 1048 1048 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331648 25-MAY-2022 11:41:04 25-MAY-22 11.41.05.539000000 AM 25-MAY-22 11.41.05.539000000 AM +00 00:00:00.000000 1 1 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331648 27-MAY-2022 08:01:28 27-MAY-22 08.01.30.371000000 AM 27-MAY-22 08.01.30.371000000 AM +00 00:00:00.000000 1 1 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331648 27-MAY-2022 08:01:38 27-MAY-22 08.01.40.611000000 AM 27-MAY-22 08.01.40.611000000 AM +00 00:00:00.000000 1 1 0 0
1 0gk5hj9wnp2vs 2221895109 2867134094 16777218 01-JUN-2022 06:48:24 01-JUN-22 06.48.27.979000000 AM 01-JUN-22 09.48.17.547000000 AM +00 02:59:49.568000 1054 1054 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331649 01-JUN-2022 09:27:03 01-JUN-22 09.27.10.915000000 AM 01-JUN-22 09.27.10.915000000 AM +00 00:00:00.000000 1 1 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331648 02-JUN-2022 07:11:55 02-JUN-22 07.11.57.315000000 AM 02-JUN-22 07.11.57.315000000 AM +00 00:00:00.000000 1 1 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331648 07-JUN-2022 07:04:13 07-JUN-22 07.04.17.155000000 AM 07-JUN-22 07.04.17.155000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554434 07-JUN-2022 08:17:56 07-JUN-22 08.17.58.693000000 AM 07-JUN-22 11.17.49.285000000 AM +00 02:59:50.592000 1054 1054 0 0
1 0gk5hj9wnp2vs 2221895109 2867134094 16777216 08-JUN-2022 07:09:22 08-JUN-22 07.09.24.427000000 AM 08-JUN-22 07.09.24.427000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554432 08-JUN-2022 08:14:25 08-JUN-22 08.14.26.278000000 AM 08-JUN-22 08.14.26.278000000 AM +00 00:00:00.000000 1 1 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331649 09-JUN-2022 06:51:27 09-JUN-22 06.51.29.219000000 AM 09-JUN-22 06.51.29.219000000 AM +00 00:00:00.000000 1 1 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331648 10-JUN-2022 11:10:13 10-JUN-22 11.10.14.595000000 AM 10-JUN-22 11.10.14.595000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554433 13-JUN-2022 08:45:43 13-JUN-22 08.45.45.509000000 AM 13-JUN-22 08.45.45.509000000 AM +00 00:00:00.000000 1 1 0 0
2 fw985yayy1mfz 2221895109 2867134094 33554433 13-JUN-2022 08:45:55 13-JUN-22 08.45.55.749000000 AM 13-JUN-22 08.45.55.749000000 AM +00 00:00:00.000000 1 1 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331648 15-JUN-2022 07:08:19 15-JUN-22 07.08.22.275000000 AM 15-JUN-22 07.08.22.275000000 AM +00 00:00:00.000000 1 1 0 0
3 0gk5hj9wnp2vs 2221895109 2867134094 50331649 15-JUN-2022 09:08:54 15-JUN-22 09.08.56.387000000 AM 15-JUN-22 12.08.46.083000000 PM +00 02:59:49.696000 1054 1054 0 0
1 fw985yayy1mfz 2221895109 2867134094 16777216 16-JUN-2022 06:54:04 16-JUN-22 06.54.05.259000000 AM 16-JUN-22 06.54.05.259000000 AM +00 00:00:00.000000 1 1 0 0
1 0gk5hj9wnp2vs 2221895109 2867134094 16777217 16-JUN-2022 08:54:13 16-JUN-22 08.54.18.891000000 AM 16-JUN-22 11.54.08.844000000 AM +00 02:59:49.953000 1054 1054 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331649 16-JUN-2022 11:16:29 16-JUN-22 11.16.31.491000000 AM 16-JUN-22 11.16.31.491000000 AM +00 00:00:00.000000 1 1 0 0
4 0gk5hj9wnp2vs 2221895109 2867134094 67108865 17-JUN-2022 07:33:10 17-JUN-22 07.33.11.282000000 AM 17-JUN-22 07.33.11.282000000 AM +00 00:00:00.000000 1 1 0 0
4 fw985yayy1mfz 2221895109 2867134094 67108865 17-JUN-2022 07:33:20 17-JUN-22 07.33.21.522000000 AM 17-JUN-22 07.33.21.522000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554432 21-JUN-2022 06:59:24 21-JUN-22 06.59.25.728000000 AM 21-JUN-22 06.59.25.728000000 AM +00 00:00:00.000000 1 1 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554433 21-JUN-2022 09:14:05 21-JUN-22 09.14.10.464000000 AM 21-JUN-22 12.14.00.096000000 PM +00 02:59:49.632000 1054 1054 0 0
2 0gk5hj9wnp2vs 2221895109 2867134094 33554434 21-JUN-2022 11:07:36 21-JUN-22 11.07.44.480000000 AM 21-JUN-22 02.07.23.680000000 PM +00 02:59:39.200000 1053 1053 0 0
4 fw985yayy1mfz 2221895109 2867134094 67108865 21-JUN-2022 11:43:04 21-JUN-22 11.43.13.010000000 AM 21-JUN-22 02.42.33.651000000 PM +00 02:59:20.641000 1051 1051 0 0
4 fw985yayy1mfz 2221895109 2867134094 67108866 21-JUN-2022 12:06:49 21-JUN-22 12.06.57.586000000 PM 21-JUN-22 03.06.38.514000000 PM +00 02:59:40.928000 1053 1053 0 0
3 fw985yayy1mfz 2221895109 2867134094 50331649 23-JUN-2022 10:05:02 23-JUN-22 10.05.05.667000000 AM 23-JUN-22 01.04.45.251000000 PM +00 02:59:39.584000 1053 1053 0 0
1 0gk5hj9wnp2vs 2221895109 2867134094 16777216 23-JUN-2022 10:57:04 23-JUN-22 10.57.04.857000000 AM 23-JUN-22 10.57.04.857000000 AM +00 00:00:00.000000 1 1 0 0
This is a section of a RTSM report when it was ok:
Status : DONE (ALL ROWS)
SQL ID : 0gk5hj9wnp2vs
SQL Execution ID : 16777216
Execution Started : 06/15/2022 10:45:41
First Refresh Time : 06/15/2022 10:45:44
Last Refresh Time : 06/15/2022 10:45:44
SQL Plan Monitoring Details (Plan Hash Value=2221895109)
===============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +3 | 1 | 2255 | | | . | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | 1 | +3 | 1 | 2255 | | | . | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_5FD9DBC0F_97EDDA7B | | | 1 | +3 | 1 | 1 | | | 1MB | | |
| 3 | FILTER | | | | 1 | +3 | 1 | 2255 | | | . | | |
| 4 | NESTED LOOPS | | 39 | 713 | 1 | +3 | 1 | 243K | | | . | | |
| 5 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 29 | 1 | +3 | 1 | 4 | | | . | | |
| 6 | PARTITION LIST ITERATOR | | 3853 | 684 | 1 | +3 | 4 | 243K | | | . | | |
| 7 | PARTITION LIST ITERATOR | | 3853 | 684 | 1 | +3 | 4 | 243K | | | . | | |
| 8 | TABLE ACCESS STORAGE FULL | POSITION_BALANCE | 3853 | 684 | 1 | +3 | 4 | 243K | 71 | 33MB | . | | |
| 9 | NESTED LOOPS | | 1 | 9 | 1 | +3 | 67382 | 140 | | | . | | |
| 10 | NESTED LOOPS | | 1 | 7 | 1 | +3 | 67382 | 140 | | | . | | |
| 11 | NESTED LOOPS | | 1 | 3 | 1 | +3 | 67382 | 67382 | | | . | | |
| 12 | TABLE ACCESS BY INDEX ROWID | CLASS_SCHEME | 1 | 1 | 2 | +2 | 67382 | 67382 | | | . | | |
| 13 | INDEX UNIQUE SCAN | UK_CLASS_SCHEME_CODE | 1 | | 1 | +3 | 67382 | 67382 | | | . | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | CLASS | 1 | 2 | 1 | +3 | 67382 | 67382 | | | . | | |
| 15 | INDEX RANGE SCAN | UK_CLASS_STATUS2 | 1 | 1 | 1 | +3 | 67382 | 67382 | | | . | | |
| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | BOOK_CLASS | 1 | 4 | 1 | +3 | 67382 | 140 | | | . | | |
| 17 | INDEX RANGE SCAN | UK_BOOK_CLASS_STATUS2 | 2 | 2 | 1 | +3 | 67382 | 336K | 230 | 2MB | . | | |
| 18 | INDEX RANGE SCAN | IDX_BOOK_STATUS_01 | 1 | 2 | 1 | +3 | 140 | 140 | | | . | | |
| 19 | VIEW | | 1 | 2 | 1 | +3 | 1 | 2255 | | | . | | |
| 20 | TABLE ACCESS STORAGE FULL | SYS_TEMP_5FD9DBC0F_97EDDA7B | 1 | 2 | | | 1 | | | | . | | |
===============================================================================================================================================================================================This is a section taken whilst running in the middle of the problem:
Status : EXECUTING
SQL ID : 0gk5hj9wnp2vs
SQL Execution ID : 33554434
Execution Started : 06/21/2022 11:07:36
First Refresh Time : 06/21/2022 11:07:41
Last Refresh Time : 06/21/2022 13:55:15
SQL Plan Monitoring Details (Plan Hash Value=2221895109)
================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | | | | | . | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | . | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9E59B6_25BEB636 | | | | | 1 | | | | . | | | |
| 3 | FILTER | | | | | | 1 | | | | . | | | |
| 4 | NESTED LOOPS | | 39 | 710 | 1 | +5 | 1 | 0 | | | . | | | |
| -> 5 | COLLECTION ITERATOR PICKLER FETCH | | 1 | 29 | 10061 | +5 | 1 | 1 | | | . | | | |
| 6 | PARTITION LIST ITERATOR | | 3853 | 681 | | | 1 | | | | . | | | |
| 7 | PARTITION LIST ITERATOR | | 3853 | 681 | | | 38175 | | | | . | | | |
| -> 8 | TABLE ACCESS STORAGE FULL | POSITION_BALANCE | 3853 | 681 | 10065 | +1 | 44021 | 0 | 625K | 162GB | . | 100.00 | gc cr block remote read (1) | 100% |
| | | | | | | | | | | | | | gc cr grant 2-way (26) | |
| | | | | | | | | | | | | | gc cr multi block request (85) | |
| | | | | | | | | | | | | | gc current grant 2-way (1) | |
| | | | | | | | | | | | | | Cpu (7530) | |
| | | | | | | | | | | | | | cell multiblock physical read (1946) | |
| | | | | | | | | | | | | | cell single block physical read (215) | |
| | | | | | | | | | | | | | cell smart table scan (15) | |
| 9 | NESTED LOOPS | | 1 | 9 | | | | | | | . | | | |
| 10 | NESTED LOOPS | | 1 | 7 | | | | | | | . | | | |
| 11 | NESTED LOOPS | | 1 | 3 | | | | | | | . | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | CLASS_SCHEME | 1 | 1 | | | | | | | . | | | |
| 13 | INDEX UNIQUE SCAN | UK_CLASS_SCHEME_CODE | 1 | | | | | | | | . | | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | CLASS | 1 | 2 | | | | | | | . | | | |
| 15 | INDEX RANGE SCAN | UK_CLASS_STATUS2 | 1 | 1 | | | | | | | . | | | |
| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | BOOK_CLASS | 1 | 4 | | | | | | | . | | | |
| 17 | INDEX RANGE SCAN | UK_BOOK_CLASS_STATUS2 | 2 | 2 | | | | | | | . | | | |
| 18 | INDEX RANGE SCAN | IDX_BOOK_STATUS_01 | 1 | 2 | | | | | | | . | | | |
| 19 | VIEW | | 1 | 2 | | | | | | | . | | | |
| 20 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9E59B6_25BEB636 | 1 | 2 | | | | | | | . | | | |
================================================================================================================================================================================================================================
Potentially I could set system level trace for this sql id and wait for it to reoccur but what events would I be setting to try to capture what causes this runtime decision?
I presume it wouldn’t be an optimizer trace?
Any thoughts or ideas?
保育士への最短ルートは専門学校？
保育士への最短ルートは専門学校？
保育士への最短ルートとしては、短期大学が最短となります。4年制大学に比べて学費も2年分ですむので、コストを節約したい人にもおすすめです。実は、専門学校でも2年制のコースがあります。専門学校では2年制のコースと3年制のコースが選べるようになっています。
3年制のコースでないと幼稚園教諭二種の免許がとれないと考えている人はいますが、実は2年制のコースでも卒業と同時に幼稚園教諭二種の免許をとれることがあります。幼稚園教諭一種の免許をとるなら、4年制の大学へ行かなくてはなりません。保育士への最短ルートとしては、短期大学か2年制の専門学校のどちらかとなるでしょう。学費などのコストはどちらも大きく変わりません。
教材費なども含めると、2年間で200万円～240万円くらいが目安となります。一人暮らしをする人は生活費も負担となるので、そういった点も考えておきましょう。2年間暮らす賃貸マンション・アパート選びなども重要になります。基本的には、学歴などは関係ないので、あくまで保育士を目指しているという人は短期大学や専門学校はおすすめです。
民間企業への就職なども考えているという人は、4年制大学のほうが就職活動が有利になる可能性が高いです。幼稚園の先生とも迷っているという人は、私立の幼稚園では4年制大学を出ている人を優先して採用するところもあるという事情を理解しておきましょう。将来のことをしっかりと考えた上で進学をすることが大切です。
Major PostgreSQL version upgrade in a Patroni cluster
One of my customer recently asked me to upgrade its PostgreSQL instance from version 9.6 to version 14.3. The infrastructure is composed of 4 servers :
– DB02-04 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-05 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-06 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-07 – Ubuntu 20.04 – pgBackRest server
I also had to upgrade Patroni from version 2.0.1 to version 2.1.4 for compatibility reason, and I took the opportunity to upgrade pgBackRest from version 2.24 to version 2.39.
Source versionTarget versionPostgreSQL9.6.1814.3Patroni2.0.22.1.4pgBackRest2.242.39
The purpose of this blog post is to explain all the required steps to achieve this.
Before installing PostgreSQL 14.3, it is necessary to install some required packages.
[postgres@db02-04 ~] $ sudo apt install llvm clang pkg-config liblz4-dev libllvm7 llvm-7-runtime libkrb5-dev libossp-uuid-devI’m used to compile and install PostgreSQL from the source code. Once the archive is downloaded and transferred to the server, we have to extract its content.
[postgres@db02-04 upgrade] $ tar -xzf postgresql-14.3.tar.gz
[postgres@db02-04 upgrade] $ ll postgresql-14.3
total 768
-rw-r--r-- 1 postgres postgres 445 May 9 21:14 aclocal.m4
drwxr-xr-x 2 postgres postgres 4096 May 9 21:24 config
-rwxr-xr-x 1 postgres postgres 587897 May 9 21:14 configure
-rw-r--r-- 1 postgres postgres 85458 May 9 21:14 configure.ac
drwxr-xr-x 58 postgres postgres 4096 May 9 21:24 contrib
-rw-r--r-- 1 postgres postgres 1192 May 9 21:14 COPYRIGHT
drwxr-xr-x 3 postgres postgres 4096 May 9 21:24 doc
-rw-r--r-- 1 postgres postgres 4259 May 9 21:14 GNUmakefile.in
-rw-r--r-- 1 postgres postgres 277 May 9 21:14 HISTORY
-rw-r--r-- 1 postgres postgres 63944 May 9 21:25 INSTALL
-rw-r--r-- 1 postgres postgres 1665 May 9 21:14 Makefile
-rw-r--r-- 1 postgres postgres 1213 May 9 21:14 README
drwxr-xr-x 16 postgres postgres 4096 May 9 21:25 src
[postgres@db02-04 upgrade] $Then the directory where the binaries will be installed must be created.
[postgres@db02-04 upgrade] $ mkdir -p /u01/app/postgres/product/14/db_3Our standard when installing PostgreSQL from the sources is to create and execute a shell script which will automatically compile and install the binaries.
[postgres@db02-04 postgresql-14.3] $ cat compile_from_source.sh
#!/bin/bash
PGHOME=/u01/app/postgres/product/14/db_3
SEGSIZE=2
BLOCKSIZE=8
./configure --prefix=${PGHOME} \
--exec-prefix=${PGHOME} \
--bindir=${PGHOME}/bin \
--libdir=${PGHOME}/lib \
--sysconfdir=${PGHOME}/etc \
--includedir=${PGHOME}/include \
--datarootdir=${PGHOME}/share \
--datadir=${PGHOME}/share \
--with-pgport=5432 \
--with-perl \
--with-python \
--with-openssl \
--with-pam \
--with-ldap \
--with-libxml \
--with-llvm \
--with-libxslt \
--with-segsize=${SEGSIZE} \
--with-blocksize=${BLOCKSIZE} \
--with-systemd \
--with-gssapi \
--with-icu \
--with-lz4 \
--with-uuid=ossp \
--with-system-tzdata=/usr/share/zoneinfo \
--with-extra-version=" dbi services build"
make -j $(nproc) all
make install
cd contrib
make -j $(nproc) install
[postgres@db02-04 postgresql-14.3] $[postgres@db02-04 postgresql-14.3] $ chmod +x compile_from_source.sh
[postgres@db02-04 postgresql-14.3] $ ./compile_from_source.shObviously the steps described above have to be performed on all nodes.
Patroni upgradeBefore upgrading Patroni to the latest version, it is important to upgrade pip and setuptools .
[postgres@db02-04 ~] $ python3 -m pip install --upgrade pip
[postgres@db02-04 ~] $ python3 -m pip install --upgrade setuptoolsThen we can upgrade Patroni.
[postgres@db02-04 ~] $ patronictl version
patronictl version 2.0.2
[postgres@db02-04 ~] $
[postgres@db02-04 ~] $ python3 -m pip install --upgrade --user patroni[etcd]
Requirement already satisfied: patroni[etcd] in ./.local/lib/python3.8/site-packages (2.0.2)
Collecting patroni[etcd]
Downloading patroni-2.1.4-py3-none-any.whl (225 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 225.0/225.0 kB 5.9 MB/s eta 0:00:00
Requirement already satisfied: python-dateutil in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (2.8.1)
Requirement already satisfied: urllib3!=1.21,>=1.19.1 in /usr/lib/python3/dist-packages (from patroni[etcd]) (1.25.8)
Requirement already satisfied: prettytable>=0.7 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (2.1.0)
Requirement already satisfied: ydiff>=1.2.0 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (1.2)
Requirement already satisfied: click>=4.1 in /usr/lib/python3/dist-packages (from patroni[etcd]) (7.0)
Requirement already satisfied: psutil>=2.0.0 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (5.8.0)
Requirement already satisfied: PyYAML in /usr/lib/python3/dist-packages (from patroni[etcd]) (5.3.1)
Requirement already satisfied: six>=1.7 in /usr/lib/python3/dist-packages (from patroni[etcd]) (1.14.0)
Requirement already satisfied: python-etcd<0.5,>=0.4.3 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (0.4.5)
Requirement already satisfied: wcwidth in ./.local/lib/python3.8/site-packages (from prettytable>=0.7->patroni[etcd]) (0.2.5)
Requirement already satisfied: dnspython>=1.13.0 in ./.local/lib/python3.8/site-packages (from python-etcd<0.5,>=0.4.3->patroni[etcd]) (2.1.0)
Installing collected packages: patroni
Attempting uninstall: patroni
Found existing installation: patroni 2.0.2
Uninstalling patroni-2.0.2:
Successfully uninstalled patroni-2.0.2
Successfully installed patroni-2.1.4
[postgres@db02-04 ~] $
[postgres@db02-04 ~] $ patronictl version
patronictl version 2.1.4
[postgres@db02-04 ~] $ patroni --version
patroni version 2.1.4
[postgres@db02-04 ~] $
Again, we have to do this on all nodes.
New cluster creationBelow steps have to be performed on the Leader node only. The following command can be used to check who is the Leader.
[postgres@db02-04 ~] $ patronictl list
+ Cluster: DEMO (6938400030986650439) -----------------------+
| Member | Host | Role | State | TL | Lag in MB |
+---------+-------------+---------+---------+----+-----------+
| db02-04 | 10.0.148.31 | Leader | running | 15 | |
| db02-05 | 10.0.148.32 | Replica | running | 15 | 0 |
| db02-06 | 10.0.148.33 | Replica | running | 15 | 0 |
+---------+-------------+---------+---------+----+-----------+
[postgres@db02-04 ~] $
The new 14.3 cluster is created in this directory.
[postgres@db02-04] $ mkdir -p /u02/pgdata/14/PRODTo create it, we use the initdb utility provided by the new PostgreSQL binaries.
[postgres@db02-04 ~] $ /u01/app/postgres/product/14/db_3/bin/initdb -D /u02/pgdata/14/PROD
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /u02/pgdata/14/PROD ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/u01/app/postgres/product/14/db_3/bin/pg_ctl -D /u02/pgdata/14/PROD -l logfile start
[postgres@db02-04 ~] $Following files have to be taken from the old cluster to the new one.
[postgres@db02-04 ~] $ cp /u02/pgdata/96/PROD/pg_hba.conf /u02/pgdata/14/PROD/
[postgres@db02-04 ~] $ cp /u02/pgdata/96/PROD/patroni.dynamic.json /u02/pgdata/14/PROD/The file patroni.dynamic.json contains a dump of the DCS options. It will be read during a later stage.
In order to apply our best practices for PostgreSQL 14, the following instance parameters are applied to the new cluster.
[postgres@db02-04 ~] $ cat /u02/pgdata/14/PROD/postgresql.conf
listen_addresses = '10.0.148.31'
port=5432
logging_collector = 'on'
log_truncate_on_rotation = 'on'
log_filename = 'postgresql-%a.log'
log_rotation_age = '1440'
log_line_prefix = '%m - %l - %p - %h - %u@%d - %x'
log_directory = 'pg_log'
log_min_messages = 'WARNING'
log_autovacuum_min_duration = '60s'
log_min_error_statement = 'NOTICE'
log_min_duration_statement = '30s'
log_checkpoints = 'on'
log_statement = 'ddl'
log_lock_waits = 'on'
log_temp_files = '0'
log_timezone = 'Europe/Zurich'
log_connections=off
log_disconnections=off
log_duration=off
checkpoint_completion_target=0.9
checkpoint_timeout='5min'
client_min_messages = 'WARNING'
wal_level = 'replica'
hot_standby_feedback = 'on'
max_wal_senders = '10'
cluster_name = 'PROD'
max_replication_slots = '10'
shared_buffers=128MB
work_mem=8MB
effective_cache_size=512MB
maintenance_work_mem=64MB
wal_compression=on
shared_preload_libraries='pg_stat_statements'
autovacuum_max_workers=6
autovacuum_vacuum_scale_factor=0.1
autovacuum_vacuum_threshold=50
autovacuum_vacuum_cost_limit=3000
archive_mode='on'
archive_command='pgbackrest --stanza=PROD archive-push %p'
wal_log_hints='on'
password_encryption='scram-sha-256'
default_toast_compression='lz4'
[postgres@db02-04 ~] $Due to corruption on some data files (invalid page checksums), I was not able to use pg_upgrade to perform the upgrade from 9.6 to 14.3. Therefore, I had no choice to use pg_dumpall to move the data.
[postgres@db02-04 ~] $ pg_dumpall -p 5432 -U postgres -l postgres -f /home/postgres/upgrade/dump/prod.dmpOnce done, Patroni can be stopped on all nodes.
[postgres@db02-04 ~] $ sudo systemctl stop patroni
[postgres@db02-05 ~] $ sudo systemctl stop patroni
[postgres@db02-06 ~] $ sudo systemctl stop patroniBonus : we use the following systemd service definition for Patroni.
[postgres@db02-04 ~] $ cat /etc/systemd/system/patroni.service
#
# systemd integration for patroni
# Put this file under /etc/systemd/system/patroni.service
# then: systemctl daemon-reload
# then: systemctl list-unit-files | grep patroni
# then: systemctl enable patroni.service
#
[Unit]
Description=dbi services patroni service
After=etcd.service syslog.target network.target
[Service]
User=postgres
Group=postgres
Type=simple
ExecStartPre=/usr/bin/sudo /sbin/modprobe softdog
ExecStartPre=/usr/bin/sudo /bin/chown postgres /dev/watchdog
ExecStart=/u01/app/postgres/local/dmk/bin/patroni /u01/app/postgres/local/dmk/etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
Restart=no
TimeoutSec=30
[Install]
WantedBy=multi-user.target
[postgres@db02-04 ~] $(Of course, the ExecStart parameter must be adapted to you environment.)
It’s now time to start the new cluster and to import the dump.
[postgres@db02-04 ~] $ /u01/app/postgres/product/14/db_3/bin/pg_ctl -D /u02/pgdata/14/PROD -l logfile start
[postgres@db02-04 ~] $ /u01/app/postgres/product/14/db_3/bin/psql postgres < /home/postgres/upgrade/dump/prod.dmpOnce the import is done, we must change the parameters data_dir and bin_dir of the Patroni configuration file in order to match to the new cluster.
[postgres@db02-04 ~] $ cat /u01/app/postgres/local/dmk/etc/patroni.yml
...
...
...
postgresql:
listen: 10.0.148.31:5432
connect_address: 10.0.148.31:5432
data_dir: /u02/pgdata/14/PROD/
bin_dir: /u01/app/postgres/product/14/db_3/bin
# config_dir:
pgpass: /u01/app/postgres/local/dmk/etc/pgpass0
authentication:
replication:
username: replicator
password: *****
superuser:
username: postgres
password: *****
parameters:
unix_socket_directories: '/tmp'
...
...
...
[postgres@db02-04 ~] $Before restarting Patroni, the previous configuration information must be removed from the DCS.
[postgres@db02-04 ~] $ patronictl remove PROD
+ Cluster: PROD (6946441255879209913------------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+
Please confirm the cluster name to remove: PROD
You are about to remove all information in DCS for PROD, please type: "Yes I am aware": Yes I am aware
[postgres@db02-04 ~]
Then, Patroni can be restarted on all nodes and the replicas will be built automatically on db02-05 and db02-06.
[postgres@db02-04 ~] $ sudo systemctl start patroni
[postgres@db02-05 ~] $ sudo systemctl start patroni
[postgres@db02-06 ~] $ sudo systemctl start patroni
[postgres@db02-04 ~] $ patronictl list
+ Cluster: PROD (7109360479587211872) ------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+---------+-------------+---------+---------+----+-----------+
| db02-04 | 10.0.148.31 | Leader | running | 2 | |
| db02-05 | 10.0.148.32 | Replica | running | 2 | 0 |
| db02-06 | 10.0.148.33 | Replica | running | 2 | 0 |
+---------+-------------+---------+---------+----+-----------+
[postgres@db02-04 ~] $That’s it ! The PostgreSQL cluster and Patroni have been successfully upgraded.
Switchover testAn important thing to do on the Patroni side is to test the switchover of the new cluster.
[postgres@db02-04 ~] $ patronictl switchover
Master [db02-04]: db02-04
Candidate ['db02-05', 'db02-06'] []: db02-05
When should the switchover take place (e.g. 2022-06-15T14:23 ) [now]: now
Current cluster topology
+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+---------+-------------+---------+---------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Leader | running | 2 | | * |
| db02-05 | 10.0.148.32 | Replica | running | 2 | 0 | * |
| db02-06 | 10.0.148.33 | Replica | running | 2 | 0 | * |
+---------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster PROD, demoting current master db02-04? [y/N]: y
2022-06-15 13:23:28.83611 Successfully switched over to "db02-05"
+ Cluster: PROD (7109360479587211872) ------------+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+---------+-------------+---------+----------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Replica | stopping | | unknown | * |
| db02-05 | 10.0.148.32 | Leader | running | 2 | | * |
| db02-06 | 10.0.148.33 | Replica | running | 2 | 0 | * |
+---------+-------------+---------+----------+----+-----------+-----------------+
[postgres@db02-04 ~] $
[postgres@db02-04 ~] $ patronictl list
+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+---------+-------------+---------+---------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Replica | running | 3 | 0 | |
| db02-05 | 10.0.148.32 | Leader | running | 3 | | * |
| db02-06 | 10.0.148.33 | Replica | running | 3 | 0 | * |
+---------+-------------+---------+---------+----+-----------+-----------------+
[postgres@db02-04 ~] Following packages are mandatory before upgrading pgBackRest to version 2.39.
[postgres@db02-04 ~] $ sudo apt install libpq-dev libyaml-dev libbz2-devI have compiled and installed pgBackRest from the source code.
Once the archive is downloaded and transferred to the server, we have to extract its content.
[postgres@db02-04 upgrade] $ unzip -q pgbackrest-release-2.39.zip
[postgres@db02-04 upgrade] $ ll pgbackrest-release-2.39
total 80
-rw-r--r-- 1 postgres postgres 10374 May 16 12:46 CODING.md
-rw-r--r-- 1 postgres postgres 37765 May 16 12:46 CONTRIBUTING.md
drwxr-xr-x 6 postgres postgres 4096 May 16 12:46 doc
-rw-r--r-- 1 postgres postgres 1168 May 16 12:46 LICENSE
-rw-r--r-- 1 postgres postgres 9607 May 16 12:46 README.md
drwxr-xr-x 11 postgres postgres 4096 May 16 12:46 src
drwxr-xr-x 7 postgres postgres 4096 May 16 12:46 test
[postgres@db02-04 upgrade] $And the the installation can be started.
[postgres@db02-04 upgrade] $ cd pgbackrest-release-2.39/src/
[postgres@db02-04 src] $ ./configure && make
[postgres@db02-04 src] $ sudo mv /usr/bin/pgbackrest /usr/bin/pgbackrest_old
[postgres@db02-04 src] $ sudo cp pgbackrest /usr/bin/
[postgres@db02-04 src] $ pgbackrest version
pgBackRest 2.39
[postgres@db02-04 src] $
The pg1-path parameter of the pgBackRest Stanza configuration must be adapted on each node in order to perform the backups against the new cluster.
[postgres@db02-04 ~] $ cat /etc/pgbackrest.conf
[global]
backup-host=DB02-07
backup-user=postgres
log-level-file=detail
[PROD]
pg1-path=/u02/pgdata/14/PROD
pg1-socket-path=/tmp
pg1-user=postgres
[postgres@db02-04 ~] $
The configuration file of the pgBackRest server must be adapted as well.
[postgres@db02-07 ~] $ cat /etc/pgbackrest.conf
[global]
repo1-path=/networkshare/pgbackrest
repo1-cipher-pass=IUlCfTExDg1x7WBTsl83rrwINn7eCKRMDyi5SsPHUjj+ywULThyRtCWMd5GVZXR4
repo1-cipher-type=aes-256-cbc
log-level-console=info
log-level-file=debug
compress-level=3
repo1-retention-full=2
repo1-retention-diff=7
repo1-type=cifs
archive-timeout=10000
[PROD]
pg1-path=/u02/pgdata/14/PROD
pg1-port=5432
pg1-host=DB02-04
pg1-socket-path=/tmp
pg1-host-user=postgres
pg1-user=postgres
pg2-path=/u02/pgdata/14/PROD
pg2-port=5432
pg2-host=DB02-05
pg2-socket-path=/tmp
pg2-host-user=postgres
pg2-user=postgres
pg3-path=/u02/pgdata/14/PROD
pg3-port=5432
pg3-host=DB02-06
pg3-socket-path=/tmp
pg3-host-user=postgres
pg3-user=postgres
[postgres@db02-07 ~] $
Finally, the Stanza must be upgraded.
[postgres@db02-07 ~] $ pgbackrest stanza-upgrade --stanza=PROD
2022-06-15 08:21:35.308 P00 INFO: stanza-upgrade command begin 2.39: --exec-id=34516-c2b709b7 --log-level-console=info --log-level-file=debug --pg1-host=DB02-04 --pg2-host=DB02-05 --pg3-host=DB02-06 --pg1-host-user=postgres --pg2-host-user=postgres --pg3-host-user=postgres --pg1-path=/u02/pgdata/14/PROD --pg2-path=/u02/pgdata/14/PROD --pg3-path=/u02/pgdata/14/PROD --pg1-port=5432 --pg2-port=5432 --pg3-port=5432 --pg1-socket-path=/tmp --pg2-socket-path=/tmp --pg3-socket-path=/tmp --pg1-user=postgres --pg2-user=postgres --pg3-user=postgres --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/networkshare/pgbackrest --repo1-type=cifs --stanza=PROD
2022-06-15 08:21:38.903 P00 INFO: stanza-upgrade for stanza 'PROD' on repo1
2022-06-15 08:21:39.501 P00 INFO: stanza-upgrade command end: completed successfully (4194ms)
[postgres@db02-07 ~] $
L’article Major PostgreSQL version upgrade in a Patroni cluster est apparu en premier sur dbi Blog.
Ansible: Imagination is the Limit
As you might know, I have discovered Ansible 1 year ago. Since then, I am not using for its main purpose as wikipedia says:
Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code.
Instead, I develop few playbooks for others objectives.
I will present you two of them:
- Search in logs
- Count clients connections
In one customer, they have an application deployed over 12 nodes cluster. For day to day operation, I receive user tickets with an error message, but without knowing on which server they were connected while facing the problem (There is a load balancer in front of clients). Unfortunately, Centralized Log Management is not ready, thus I had to think to another solution. Here is where Ansible could help.
The advantage of Ansible over bash scripting, in this situation, is that all users credentials are already managed by the Ansible environment that was developed:
- name: Include common tasks
tags: [ always ]
include_role:
name: common
apply:
tags: always
These transparently manages:
- Service user name and associated credentials.
- Access to server with admin account (login and password).
Let’s focus on main feature: The search. To do that, first thing is to know what we are looking for:
- name: Prompt pattern
block:
- name: Prompt pattern
register: pattern_input
ansible.builtin.pause:
prompt: |
Enter searched pattern
- name: Set pattern fact
set_fact:
pattern: "{{ pattern_input.user_input }}"
when: pattern is not defined
delegate_to: localhost
run_once: True
What I did is to interactively request the pattern we are looking for if it was not already provided as playbook parameter (ie. not defined). The block will not be executed if pattern is already set.
To avoid requesting same pattern as many times as there are servers in the cluster, this will be run only once (line 14) and it will not be related to a specific host of the inventory, so I kept it local (line 13).
SearchingNow, I am ready to do the actual search:
- name: Search {{ pattern }} in log
find:
paths: /opt/,/u02/app/weblogic/config/domains/{{ weblogic_domain }}/servers/
file_type: file
exclude: '*.gz'
contains: '.*{{ pattern }}.*'
recurse: true
age: -5d
register: findings
become: true
become_user: weblogic
I am using the find module with a regex. This regex requires “.*”, meaning any characters any amount of time, to be added at beginning and end. If not, it will search only files contain exactly the pattern. Not more, not less. Result will be stored (ie. registered) in findings variable. Note that I searched for files not older than 5 days (line 8) and exclude archived logs (line 5) for faster results.
Then, my idea was to provide a list of files containing the pattern:
- name: output the path of the files
set_fact:
path: "{{ findings.files | map(attribute='path') | join('\n - ') }}"
path will be a temporary variable which will be written to a file local to the Ansible controller server.
Finally, writing the file:
- name: Remove {{ ansible_limit }} file
ansible.builtin.file:
path: "{{ ansible_limit }}.out"
state: absent
delegate_to: localhost
run_once: True
- name: Copy list of files in {{ ansible_limit }}
ansible.builtin.lineinfile:
path: "{{ ansible_limit }}.out"
line: "{{inventory_hostname}}:\n - {{ path }}"
create: yes
mode: 0666
delegate_to: localhost
throttle: 1
In first task, I am removing the file and, secondly, I am writing result in file. Initially, results were not ordered as it depends on the completion time of task on each nodes. To avoid that, I added a “throttle: 1” option which will ensure, it is run one task at a time. “order: sorted” is also added at the beginning of the playbook to ensure that.
Count Clients ConnectionsThis second playbook is to get the amount of client connected to each servers to confirm they are correctly load balanced across all nodes.
First task is to get the process ID with a “shell” task:
- name: Getting process ID
shell: ps aux | grep '{{ pattern }}' | grep -v grep | tr -s ' '| cut -d' ' -f2
register: ps_output
“pattern” is a string which will help to find the PID.
Then, I used netstat to find all established connection to that process (pid_string= “{{ ps_output.stdout }}/java”):
- name: netstat
shell: netstat -anpt 2>/dev/null | grep '{{ pid_string }}' | grep ESTABLI | grep -v 1521
register: conn_list
become: true
become_user: weblogic
I filtered out connection to Oracle Database (port 1521) as this process has connections to it as well.
The amount of lines of “conn_list” variable will be the amount of connections:
- name: Set conn_count
set_fact:
conn_count: "{{ conn_list.stdout_lines | length }}"
The same way a previous playbook, I am creating a file local to the Ansible controller where I write a line for each nodes with amount of connections:
- name: Copy result in {{ result_file }}
ansible.builtin.lineinfile:
path: "{{ result_file }}"
line: "{{inventory_hostname}};{{ pattern }};{{ pid_string }};{{ conn_count }}
create: yes
mode: 0666
throttle: 1
I have also included what was the pattern used and the pid of the process on each host. Keep in mind that all tasks related to the local file are delegated to localhost in one block.
Finally, I thought I could add a total of connections for all nodes. This was the difficult part. Initially, I used a sed of the file to do it, but then, I thought “There is nothing that Ansible can’t do!”. So I persevered and found that solution:
- name: Calculate totals in {{ result_file }}
set_fact:
TotalConnLines: "{{ansible_play_hosts_all | map('extract', hostvars, 'conn_count') | map('int') | sum }}"
run_once: True
Let’s detail that jinja template:
- ansible_play_hosts_all
- map(‘extract’, hostvars, ‘conn_count’)
- map(‘int’)
- sum
Part 1 is to get a list of all hosts where Ansible is ran. Then, part 2, I extract from “hostvars“, the variable “conn_count” for each hosts. This is now a list of counts. I could simply pipe it to “sum”, but this failed because elements of the list are strings. So, I had to apply “int” on them with help of map (part 3). Finally, summing up counts at part 4.
Then, I write the total line to the resulting file:
- name: Add total line in {{ result_file }}
ansible.builtin.lineinfile:
path: "{{ result_file }}"
line: ";{{ TotalConnLines }}"
insertbefore: EOF
run_once: True
This is quite a complex jinja template to do it, but we see nothing is impossible.
And Yours?And you, for what are you using Ansible which was not his main purpose?
L’article Ansible: Imagination is the Limit est apparu en premier sur dbi Blog.
Oracle Analytics Summit 2022
Last week I attended the second Oracle Analytics Summit at Skywalker Ranch.
The last Summit was in 2019 and attended by Jon Mead and he summarised the experience here.
The purpose of the event is unchanged, and the idea is to showcase the current analytics products and services and more importantly, get feedback from analytics customers and partners on future developments and strategy.
It was clear during the event lots had changed since 2019 and the initiative has really started to pay off, Oracle was named as visionary in the 2022 Gartner magic quadrant for analytics and Business Intelligence Platforms for the third year in a row. Rittman Mead has certainly seen an uptake of OAC/OAS and positive customer feedback.
The event started well with a recorded opening keynote and two customer success stories. MTN being one of them. We have been working with MTN for the last five years.
The core workshops over the two days focused on three main areas:
1. Analytics (OAC/OAS)
2. Oracle Applications and Fusion Analytics Workspace (FAW)
3. Data
One of the highlights for me was a couple of talks around storytelling by James Richardson. James used to work for Gartner and is now at Oracle. He continues to look at data storytelling and how this is expected to dominate business intelligence in the future, specifically now with Oracle.
The biggest change to OAC is the introduction of web-based semantic modelling tool. This will absolutely revolutionise the way we build semantic models in OAC. I’m sure most of the readers know or have come across the OBIEE Admin tool and RPD.
“The Oracle BI Repository (RPD file) stores BI Server metadata. The metadata defines logical schemas, physical schemas, physical-to-logical mappings, aggregate table navigation, and other constructs. You can edit Oracle BI repositories using the Oracle BI Administration Tool.”
The challenge with the RPD has always been how to do multiuser development (MUD). The RPD is a single binary object and unless you edit in online mode (not advisable) it’s hard to version changes (Enabling OBIEE Concurrent development). The new semantic layer has been completely re-engineered to the latest software engineering practices thanks to the leadership of Bret Grinslade and team. The User Interface (UI) looks modern, and you can use the UI or code directly in Semantic Model Markup Language (SMML). The lineage tool presents well. Keep an eye out for upcoming blogs from our team on the subject as we start testing its current capabilities.
“The current version supports relations objects modelling. Essbase and OLAP are not available, and the tool is only for OAC. The traditional Admin tool will be here for a while.”
In addition there are continued improvements to OAC, focusing on AI/ML and natural language processing.
There is a continued drive to enhance Fusion Analytics Warehouse (FAW) for Fusion application users. Additional external pipelines include SalesForce and EBS as examples.
Summary
There is a continued commitment to supporting customers and building a strong analytics community. The event was certainly filled with thought leaders in the area and hearing about customer experiences gave useful insight and feedback to Oracle product teams and partners.
Look out for our blogs on the new semantic layer and our experiences with FAW.
Customers I spoke to during the event were struggling to increase their users’ adoption of new analytics capabilities. Same reports, different platform. If you are struggling with user adoption, let us know.We have an excellent team of consultants and we have a user engagement service that helps to identify why users aren’t engaged with the reporting tools.
asmcmd find spfile for db and asm
Quick and dirty post.
[oracle@ol7-19-rac1 ~]$ asmcmd find + spfile*
+DATA/CDBRAC/PARAMETERFILE/spfile.272.1107812515
[oracle@ol7-19-rac1 ~]$
[oracle@ol7-19-rac1 ~]$ asmcmd spget
+CRS/ol7-19-cluster/ASMPARAMETERFILE/registry.253.1107809781
[oracle@ol7-19-rac1 ~]$ Facial recognition, between advanced biometrics and needs for privacy… – Part 2 of 4
Continuation of our series on facial recognition. After defining facial recognition among the biometric processes (see here), let’s see the techniques used for its application.
State of the art of the techniques used for its applicationThe technology on this topic is constantly evolving, driven in particular by the web giants, who directly publish their theoretical discoveries in the areas of AI and image recognition, to advance the state of the art as quickly as possible.
Main steps of the processThe facial recognition process can be performed from photos or videos. [6]
It takes place in five major steps:
- Face detection: the system will isolate the faces present in the image from the rest of the image, to prepare them for processing.
- Preparation of the images to align them to a precise standard: the goal is to make variables such as the position of the head, the size of the image and photographic qualities such as lighting or gray level as little influential as possible on the measurements that will follow.
- Facial data extraction. Once the image is prepared, all the facial data that the AI will use to compare the information is extracted from the image.
- A model, called “template”, which represents the biometric characteristics of the face appearing in the image (or video) is made.
- The values of this template are then compared with templates calculated in real time from the stored biometric data.
For authentication, this template is made from the stored data for the identity that the person claims to be.
For identification, the template made in 4. is compared with the different people present in the database, and the AI selects the most matching person, provided that the similarity score is above a predetermined threshold. [6]
Algorithms with “feature-based” approach
The first of the two main approaches for face recognition algorithms is to identify the different features of a face by extracting them from the image. The algorithms will retrieve from the image the different values associated with the criteria listed in the section above (parameters used for comparison).
This approach is also called “geometric”.
The criteria most often used by algorithms with geometric approach are:
- Eye distance
- Nose bridge distance
- Commissures of the lips
- Ears
- Chin
- Face shape
- Shape of the jaw
Algorithms with a “holistic” approach
Holistic approach algorithms aim at normalizing a gallery of face images, compressing the face data, and saving only the part of the data that is useful for facial recognition. This compressed representation of a face gives a template, and the different templates are then used for comparison. [7]
This approach is also called “photometric”.
Photometric algorithms include:
- Eigenfaces (oldest algorithm developed, 1991) [8]
- Fisherfaces
- Elastic bunch graph matching [9]
- Linear discriminant analysis [10]
- Hidden markov model [11]
- Local Binary Patterns Histograms (LBPH), which is one of the most popular today.
To enable automatic human identification at distance (HID) (and thus at low resolution for the human in question shown in the photograph), the initial low resolution is enhanced using a process called “face hallucination”. [12]
This process then precedes the traditional face recognition steps, to prepare the image. It uses either:
- A machine learning AI, trained by face examples. The AI will trace the face in more detail to improve the resolution of the image based on these face examples to determine what that part of the face would probably look like in a more accurate photo.
- A k-nearest neighbor distribution, which is a statistical mathematical function often used in the AI world. This approach aims to mathematically deduce what the neighboring pixels of already known pixels “most likely” look like.
These processes can be enhanced by incorporating information about face characteristics based on age and different demographics into the AI, to help it make the right choice.
This is particularly useful on:
- Images from traditional video surveillance cameras, where the resolution is usually much too low for the image to be used as is for facial recognition.
- In the case of using facial recognition algorithms that require particularly high resolutions, the face hallucination process is also used to achieve a sufficiently high resolution for more standard resolution images, and thus widen the usable database.
- In case of hidden or partially hidden faces. This is one of the methods to recalculate the masked part of the face (by glasses for example).
The use of 3D sensors allows for a more accurate capture of information about the shape of the face. This method of capture has several advantages:
- It is not affected by changes in ambient lighting.
- It can identify more easily photographs taken in profile.
- Using 3D data gives much better performance for facial recognition AI.
Some facial recognition systems already in place use a 3-camera system to capture faces in 3D.
Note that the use of 3D faces makes facial recognition very sensitive to facial expressions that distort it; it becomes necessary to pre-process the image to compensate for this influence and allow good results.
In order to completely bypass any attempt to hide one’s face, facial recognition techniques using thermal cameras have been developed. These methods are not very effective on their own for several reasons.
- The databases of faces taken with thermal imaging cameras are very limited. Where other facial recognition methods can for example feed their databases via an aspiration of content found on the internet, infrared camera databases almost always have to be built from scratch.
- This method does not currently work for photos taken outdoors. It needs a stable temperature environment.
On the other hand, researchers at the ARL (US Army Research Laboratory) have developed a method that allows to compare images taken in infrared with images taken by normal cameras. This solves the problem seen in 1.
Other techniques and associated technologiesSome other techniques are used in facial recognition to tailor it to specific needs.
- Match on card: biometric data is contained on a card in our possession rather than stored in a database. They can then be compared without any data leaving the card.
- Multi-modal biometrics and other authentication factors: when more reliability is needed than facial recognition alone, systems use multiple authentication factors.
- Data Anonymization: A biometric database can avoid linking data to an identity, and instead link it to a string of characters.
________________________________________________________________________
[6] Official site of the CNIL, Facial Recognition: https://www.cnil.fr/fr/definition/reconnaissance-faciale
[7] Wikipedia, Système de reconnaissance faciale: https://en.wikipedia.org/wiki/Facial_recognition_system
[8] Ravi S., 2013, A study on Face Recognition Technique based on Eigenface
[9] Wiskott L., 1997, Face Recognition by elastic bunch graph matching
[10] ETEMAD Kamran and CHELLAPPA Rama, 1997, Discriminant analysis for recognition of human face images: https://www.face-rec.org/algorithms/LDA/discriminant-analysis-for-recognition.pdf
[11] ARA V. Nefian et MONSON H. Hayes III, 1998, Face detection and recognition using hidden markov models: http://www.anefian.com/research/nefian98_face.pdf
[12] XIAOU Tang, 2015, Hallucinating Face by Eigentransformation: https://www.researchgate.net/publication/3421633_Hallucinating_Face_by_Eigentransformation
L’article Facial recognition, between advanced biometrics and needs for privacy… – Part 2 of 4 est apparu en premier sur dbi Blog.
保育士の専門学校の選び方
保育士の専門学校の選び方
保育士になる上で専門学校を選択するメリットはいくつかありますが、中でも就職率の高さは多くの方の関心事でもあります。昨今、保育士不足が指摘されているため、免許があれば就職できる可能性が高いですが専門性の高い知識や技術を磨ける専門学校を選択することで、将来の選択肢が増えていくこともあります。しかし、一口に専門学校といってもその特色は様々であり、しっかりと見極めなければ恩恵を受けにくいことに注意が必要です。保育士として働くことを重視するのであれば実習をたくさん取り入れている専門学校がおすすめです。
保育の仕事は実際に子供と関わって行うものです。いかに幅広い知識を有していても、いかに楽器が弾けたとしても実際に子供たちを相手にしてみなければ分からないことがたくさんあります。しかも、子どもと関われる機会というものは実際に働くまではあまりないこともあります。それゆえ実習を多く取り入れているところにはメリットがあると言えます。
他にも子供を喜ばせやすいスキルを身につけられるかもポイントになります。子どもとはコミュニケーションを取る際に手遊びやお遊戯などができると非常に役立ちます。決して高度な技術を磨ければ良いということではありませんが、思わぬ形で役立つ技術もあります。喜ばせられるスキルがあるとコミュニケーションのとっかかりが作りやすくなります。
自分の得意なことを見つけるかも重要なポイントになっています。
Will Exa@CC change or kill your DBA job?
Exadata Cloud@Customer (Exa@CC) from Oracle is an hybrid solution for customer willing a Cloud-like platform without actually being in the public Cloud. Behind cloud’s concept is a high-level management of complex stuff, like provisioning homes and databases, patching, aso. And this has made classic deployments rather obsolete. Exa@CC brings these Cloud features to a kind of on-prem solution.
What is Exadata?Exadata is the server behind Exa@CC. Actually, this is not a single server, it’s a set of servers and network equipment inside a full rack. There is two kinds of servers inside an Exadata, compute nodes (at least 2) for running databases, and storage cells (at least 3), for running ASM storage. The main difference compared to any other solution is that storage is aware of database queries and can offload part of the job from the compute nodes to the cells.
Exadata is considered to be the highest-end solution for very demanding and highly critical Oracle databases. As you may guess, this is not for you if you only run a couple of instances.
What is Exadata Could@Customer?Key points of the Exa@CC:
- A “classic” Exadata installed by Oracle in your data center
- Paid as a cloud subscription (monthly fee) for a number of years
- Included license: Oracle Enterprise Edition Extreme performance (understand with all options)
- Manageable with the OCI console (Oracle’s public cloud portal)
- Manageable with the OCI REST APIs
- Fully integrated to your network as if it were yours
- Run databases inside Virtual Machines clusters
- Configured to your needs with very few limits (root access to VMs)
- Nothing resides in a public Cloud
This solution is the Oracle masterpiece regarding databases. The promises are:
- First class performance
- Easy setup
- Easy patching
- Easy provisioning
- Easy Data Guard setup
- Easy backup
- Pay-as-you-run cost
That sounds great. Exadata made easy, this is what some of us always expected! For sure this solution comes at a price point that may not fit your purse. But if you work with hundreds of databases and if Oracle database is one of the most important component in your IT infrastructure, it’s definitely a solution to consider.
Reality: what is really easy?Once Exadata is in your data center, and linked to OCI with the correct privileges, the first step is to provision the VM clusters. Exadata is a powerful machine, and you will split it into multiple VM clusters for production, test, certification, … Note that most of the time, you will need at least 2 Exadatas, Disaster Recovery not being addressed by using another VM cluster on the same hardware.
Provisioning VM clusters is quite easy and is not really a DBA task, as it consists of providing CPU sizing, memory, disk space and network configuration. The virtual servers will then be provisioned, and for the RAC setup, because Exadata means RAC, each component is configured automatically during VM cluster provisioning. You can compare this to provisioning an HA Oracle Database Appliance on which you don’t do the RAC setup yourself.
Provisioning DB homes and databases is also very easy once VM clusters are available, straight from the OCI console. You eventually don’t need any DBA knowledge for that, just choose your version, database name and character set and it’s done.
Provisioning PDBs is also included in OCI, this is quite a new feature.
Data Guard configuration is also so easy. Create a Data Guard configuration is choosing target VM cluster for creating the standby and protection mode for your configuration, and everything is done automatically. You can later switchover or failover from OCI. Goodbye broker and command line interface.
Regarding backup, each database can be configured for automatic backup: just provide your usual nfs share and your target backup retention and it will be configured and scheduled automatically. Restore is also done from the OCI console, this is really nice.
Regarding these points, obviously there is less work for you. Or more precisely, these tasks will take you less time to complete.
Reality: limits of the easy stuffEverything would be easy if Oracle and Exadata together were less complex. Underlying complexity on these technologies is still there.
In case of a problem, because problems can happen on this platform too, troubleshooting will be needed. And you will need the knowledge on RAC, Data Guard, Linux, and so on.
Another point is that some easy-to-implement stuff may not fit your needs.
For example, if a specific configuration is needed for Data Guard (basically if some databases need 2 standbys), you will need to configure it yourself, with a classic tools like dgmgrl.
This is the same for backups, if you need to build a complex backup strategy based on a mix of disk backups and tape backups, automatic backup will not allow you to do that, at least not yet. You will then go back to rman and shell scripting, and you will need a scheduler.
Fortunately, your Exa@CC will benefit from new features quite regularly. I’ve only been working 1 year with this platform and I already seen some great improvements. Improvements are mainly new features brought to the OCI console: you can still manage everything manually if you want to.
DBA tasks on Exa@CCThere is still a lot of work for a DBA on this platform:
- Provisioning DB homes, databases and PDBs
- Defining resources between PDB, CDB and VM clusters
- Tuning the databases
- Planning and applying patches, because you will decide when you apply them once they are available
- Monitoring the platform, databases, backups
- Doing the migrations from you current environment
- Leveraging the potential of the platform (using available options, optimizing offload to the cells)
- Managing credits and resources according to planned needs
Managing credits is a hot topic when it comes to resources on the Cloud. Basically every active resource will cost something. On Exa@CC, you should know that you can provision a VM cluster with zero cores, and increase the number of cores when you start to use it. You may also think about stopping some of your VM clusters when they are not absolutely needed. For example during the night. This is also something greener than letting everything running 24h a day.
ConclusionDon’t be afraid of this solution. Exa@CC will not steal your job, it will change it a little bit. There is still a lot to do for DBAs, the few things removed from your to do list will be replaced by other tasks, more interesting ones in my opinion. This is the real challenge. A DBA should continue learning to keep his job. But this is the same for a lot of other jobs now.
L’article Will Exa@CC change or kill your DBA job? est apparu en premier sur dbi Blog.
ORA-29857
This is another little “case study” on solving a problem. It’s based on a question on the MOSC DBA Forum (needs an account) about trying to drop a tablespace and seeing the error “ORA-29857: domain indexes and/or secondary objects exist in the tablespace” when (apparently) there are no domain indexes or secondary objects in the tablespace according to the queries that have been used to try and locate the problem objects.
Not being completely familiar with all the possibilities that might be relevant to that particular error, but having a vague memory of seeing an oddity once before, I thought I’d do a quick check on a couple of possible causes.
- Case 1, which I assumed ought to raise the error because the tablespace held some objects that met the requirements for the error: I created a table in a tablespace called users and, for the domain indexes a couple of Context (Oracle Text) indexes in a tablespace called test_8k_assm. Then I tried to drop the tablespace holding the context indexes. Here’s the relevant cut-n-paste (19.11.0.0):
SQL> drop tablespace test_8k_assm including contents and datafiles;
drop tablespace test_8k_assm including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
- Case 2, which I thought would drop the tablespace because the model didn’t meet the requirement of the error message: I swapped the locations of the table and its indexes, creating the table in the tablespace called test_8k_assm and the Context indexes in the tablespace called users then I tried to drop the tablespace holding only the table. Here’s the relevant cut-n-paste (19.11.0.0):
SQL> drop tablespace test_8k_assm including contents and datafiles;
drop tablespace test_8k_assm including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace
So, the error message is misleading and, possibly, this might explain why the queries the OP had posted weren’t reporting any guilty objects. Whatever the error message says, the domain indexes that you need to deal with may not be in the tablespace you want to drop.
I also have a question-mark over “secondary objects” – I don’t know what objects (other than the bits and pieces that make up domain indexes) are deemed to be secondary.
InvestigatingIt’s at this point that the purpose of the blog note starts to appear – I don’t know the answer to the OP’s question and I’ve discovered a gap in my knowledge so I’m willing to spend a little time extending my knowledge and trying to give a little help to the OP.
We can start with the view dba_secondary_objects (based on secobj$ from script dcore.bsq) which tells us that there are two possible sources of secondary objects – index types and statistics types:
SQL> select dbms_metadata.get_ddl('VIEW','DBA_SECONDARY_OBJECTS') view_text from dual;
-- with a little cosmetic tidying:
VIEW_TEXT
-------------------------------------------------------------------------------------------
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_SECONDARY_OBJECTS" ("INDEX_OWNER", "INDEX_NAME", "SECONDARY_OBJECT_OWNER", "SECONDARY_OBJECT_NAME", "SECONDARY_OBJDATA_TYPE")
AS
select u.name, o.name, u1.name, o1.name,
decode(s.spare1, 0, 'FROM INDEXTYPE',1, 'FROM STATISTICS TYPE')
from sys.user$ u, sys.obj$ o, sys.user$ u1, sys.obj$ o1, sys.secobj$ s
where s.obj# = o.obj# and o.owner# = u.user# and
s.secobj# = o1.obj# and o1.owner# = u1.user#;
With this view definition I’d be tempted to do a little drilling down, with queries that return increasing amounts of data, and then pick subsets to expand to higher precision, e.g:
select secondary_objdata_type, count(*)
from dba_secondary_objects
group by
secondary_objdata_type
/
Are there any secondary objects that aren’t domain indexes – if there are a few then query the view for just those to find out what they are and who owns them.
select *
from dba_secondary_objects
where secondary_objdata_type = 'FROM STATISTICS TYPE'
/
In fact if the total number of secondary objects was small I might report the details about all of them and try to track them down, or if there was only one schema using that tablespace I might restrict my queries to the subset of secondary objects owned by that schema:
select distinct index_owner, index_name
from dba_secondary_objects
where secondary_object_owner = 'TEST_USER'
;
The OP had a rather larger task than my little model – there were 14,499 segments in the tablespace, and a query for (segment_owner, segment_type, count(*)) returned 332 rows – so had taken a similar approach to mine to try and minimise the amount of work needed, starting with the following queries (which I’ve enhanced slightly and given a cosmetic makeover):
prompt ==================================
prompt Domain indexes (in the tablespace)
prompt ==================================
select owner, index_name, table_owner, table_name, tablespace_name
from dba_indexes
where index_type = 'DOMAIN'
-- and tablespace_name = 'TEST_8K_ASSM' -- error: domain indexes don't have tablespaces
;
prompt ==========================================
prompt Domain indexes on tables in the tablespace
prompt ==========================================
select t.table_name, t.tablespace_name, i.index_name, i.tablespace_name
from dba_indexes i, dba_tables t
where t.table_name = i.table_name
and t.owner = i.owner
and i.index_type = 'DOMAIN'
and t.tablespace_name = 'TEST_8K_ASSM'
;
prompt ===================================================
prompt Secondary objects that are tables in the tablespace
prompt ===================================================
select index_owner, index_name, secondary_object_owner, secondary_object_name, secondary_objdata_type
from dba_secondary_objects
where secondary_object_name in (
select table_name
from dba_tables
where tablespace_name = 'TEST_8K_ASSM'
)
;
prompt ======================================================
prompt Indexes in the tablespace that are secondary objects ?
prompt ======================================================
select index_owner, index_name, secondary_object_owner, secondary_object_name, secondary_objdata_type
from dba_secondary_objects
where index_name in (
select index_name
from dba_indexes
where tablespace_name = 'TEST_8K_ASSM'
)
;
The first query had an error – domain indexes do not report a tablespace_name in dba_indexes, so this query inevitably returned no rows.
The second query would report any domain indexes on tables that were in the given tablespace – which would be useful for the simple case where everything was created in the user’s default tablespace.
The third query reports any tables in the given tablespace that are the physical tables instantiating the “logical” domain index – which is particularly important for my example of the indexes being in the given tablespace when the table itself was in some other tablespace.
The final query is trying to report domain indexes that have corresponding physical objects in the given tablespace – but this isn’t going to help (at least with my Context indexes) because the index_name in dba_secondary_indexes will be a name that appears in dba_indexes as index_type = ‘DOMAIN’ and won’t have a value stored in the tablespace_name column.
Here’s the output I get (preceded by a full report of the view dba_secondary_objects in my otherwise empty little database) when I had the table in test_8k_assm and the indexes in tablespace users.
SQL> select * from dba_secondary_objects;
INDEX_OWNER INDEX_NAME SECONDARY_OBJECT_OWNER SECONDARY_OBJECT_NAME SECONDARY_OBJDATA_TY
--------------- -------------------- -------------------------------- -------------------------------- --------------------
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$I FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$K FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$N FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$U FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$I FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$K FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$N FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$U FROM INDEXTYPE
8 rows selected.
==================================
Domain indexes (in the tablespace)
==================================
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
--------------- -------------------- --------------- ------------------------- ------------------------------
TEST_USER SH_IX_NAME TEST_USER SCRIPT_HANDLES
TEST_USER SH_IX_HANDLE TEST_USER SCRIPT_HANDLES
2 rows selected.
==========================================
Domain indexes on tables in the tablespace
==========================================
TABLE_NAME TABLESPACE_NAME INDEX_NAME TABLESPACE_NAME
------------------------- ------------------------------ -------------------- ------------------------------
SCRIPT_HANDLES TEST_8K_ASSM SH_IX_NAME
SCRIPT_HANDLES TEST_8K_ASSM SH_IX_HANDLE
2 rows selected.
===================================================
Secondary objects that are tables in the tablespace
===================================================
no rows selected
=====================================================
Indexes in the tablespace that have secondary objects
=====================================================
no rows selected
As you can see I have two Context indexes, each represented by 4 secondary tables (and that’s not the complete story because those tables have their own indexes and a couple of them have LOB columns, and one of the secondary tables is actually an index-organized table).
And this is the result when the base table is in tablespace users and the indexes are in test_8k_assm:
==================================
Domain indexes (in the tablespace)
==================================
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
--------------- -------------------- --------------- ------------------------- ------------------------------
TEST_USER SH_IX_NAME TEST_USER SCRIPT_HANDLES
TEST_USER SH_IX_HANDLE TEST_USER SCRIPT_HANDLES
2 rows selected
==========================================
Domain indexes on tables in the tablespace
==========================================
no rows selected
===================================================
Secondary objects that are tables in the tablespace
===================================================
INDEX_OWNER INDEX_NAME SECONDARY_OBJECT_OWNER SECONDARY_OBJECT_NAME SECONDARY_OBJDATA_TY
--------------- -------------------- -------------------------------- -------------------------------- --------------------
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$I FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$K FROM INDEXTYPE
TEST_USER SH_IX_NAME TEST_USER DR$SH_IX_NAME$U FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$I FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$K FROM INDEXTYPE
TEST_USER SH_IX_HANDLE TEST_USER DR$SH_IX_HANDLE$U FROM INDEXTYPE
6 rows selected
=====================================================
Indexes in the tablespace that have secondary objects
=====================================================
no rows selected
So one of the middle two queries ought to return some rows if there are any Context indexes in the tablespace or on tables in the tablespace. (And for the final option – i.e. the table and its Context indexes being in the tablespace – both queries will return rows). The OP got no rows returned on all 4 queries.
What next?It looks like the OP must have some secondary objects that aren’t playing by the rules if they’re “from index types”, or there are some secondary objects that are “from statistics types” and don’t get picked up by the queries. There is a view dba_ustat for “user stats types” which has interesting column names, but at this point I decided to follow a strategy that I’ve used many times in the past when Oracle raises an unexpected error on a repeatable process: repeat the process but enable SQL trace and see what Oracle was doing just before it raised the error.
Here’s the critical line in the trace file I produced; with a couple of the precding lines:
CLOSE #140708355472384:c=1860,e=1512,dep=1,type=0,tim=31701605233
EXEC #140708354845632:c=68018,e=68006,p=0,cr=1276,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=31701605472
ERROR #140708354845632:err=29857 tim=31701605485
A search backwards up the trace file showed that cursor #140708354845632 was for the “drop tablespace” statement. Just above the ERROR and EXEC lines we see cursor #140708355472384 closing. It’s reasonably likely (though not 100% certain) that this is the cursor representing the last SQL statement that Oracle executed before raising the error so searching backwards for “PARSING IN CURSOR #140708355472384” was my next step (and if that statement didn’t look promising I’d have search backwards from the ERROR line to find the cursor number from the most recent “EXEC” line). In this case (with my data) the CLOSE took me to the following (cosmetically cleaned) SQL:
select null from (
select o.obj# from obj$ o, tab$ t where t.ts#=:1 and t.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, tabpart$ tp where tp.ts#=:1 and tp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, tabsubpart$ tsp where tsp.ts#=:1 and tsp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, ind$ i where i.ts#=:1 and i.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, indpart$ ip where ip.ts#=:1 and ip.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, indsubpart$ isp where isp.ts#=:1 and isp.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select o.obj# from obj$ o, clu$ c where c.ts#=:1 and c.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all select i.obj# from ind$ i, tab$ t, tabpart$ tp where tp.ts#=:1 and tp.bo# = t.obj# and t.obj#=i.bo# and i.type#=9
union all select i.obj# from ind$ i, tab$ t, tabcompart$ tcp,tabsubpart$ tsp where tsp.ts#=:1 and tsp.pobj#=tcp.obj# and tcp.bo#=t.obj# and t.obj#=i.bo# and i.type#=9
union all select i.obj# from ind$ i, tab$ t where t.ts#=:1 and t.obj#=i.bo# and i.type#=9
)
Every single line of the UNION ALL is addressing the same tablespace by number (:1) looking for objects where bit 5 is set in the obj$.flags column (i.e. secondary object) or indexes where the ind$.type# is 9 (“cooperative index method”).
That looks like a good bet for being the code that Oracle uses to check if there are any objects that should block the drop, so I changed the bind variable to the tablespace number (from v$tablespace) changed the “select null” to “select obj#” and stuck the resulting query block into an IN subquery against dba_objects (this may not be the most efficient of queries):
select
secondary, owner, object_name, object_type, object_id
from dba_objects
where object_id in (
select obj# from (
select o.obj# from obj$ o, tab$ t where t.ts#=6 and t.obj#=o.obj# and (decode(bitand(o.flags, 16), 16, 1, 0)=1)
union all ...
union all select i.obj# from ind$ i, tab$ t where t.ts#=6 and t.obj#=i.bo# and i.type#=9
)
)
order by secondary, owner, object_name
/
S OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
- --------------- -------------------------------- ----------------------- ----------
N TEST_USER SH_IX_HANDLE INDEX 123956
N TEST_USER SH_IX_NAME INDEX 123944
Y TEST_USER DR$SH_IX_HANDLE$I TABLE 123957
Y TEST_USER DR$SH_IX_HANDLE$K TABLE 123960
Y TEST_USER DR$SH_IX_HANDLE$KD INDEX 123966
Y TEST_USER DR$SH_IX_HANDLE$KR INDEX 123967
Y TEST_USER DR$SH_IX_HANDLE$U TABLE 123963
Y TEST_USER DR$SH_IX_HANDLE$X INDEX 123965
Y TEST_USER DR$SH_IX_NAME$I TABLE 123945
Y TEST_USER DR$SH_IX_NAME$K TABLE 123948
Y TEST_USER DR$SH_IX_NAME$KD INDEX 123954
Y TEST_USER DR$SH_IX_NAME$KR INDEX 123955
Y TEST_USER DR$SH_IX_NAME$U TABLE 123951
Y TEST_USER DR$SH_IX_NAME$X INDEX 123953
Y TEST_USER SYS_C0016528 INDEX 123952
Y TEST_USER SYS_C0016536 INDEX 123964
Y TEST_USER SYS_IL0000123945C00006$$ INDEX 123947
Y TEST_USER SYS_IL0000123957C00006$$ INDEX 123959
Y TEST_USER SYS_IOT_TOP_123949 INDEX 123950
Y TEST_USER SYS_IOT_TOP_123961 INDEX 123962
20 rows selected.
This is the list I got when the base table and the Context indexes were all created in the tablespace I wanted to drop – notice that the “logical” indexes (the first two items, flagged as secondary=’N’) are reported along with all the physical objects relating to the context indexes.
When I created the indexes in the target tablespace but the base table in an alternative tablespace the query reported only the physical objects making up the Context indexes; when I created the base table in the target tablespace and the Context indexes in an alternative tablespace the query reported only the “logical” indexes.
ConclusionAs a last resort, when the simple queries you find in the manuals or on MOS (or elsewhere on the internet) don’t seem to supply all the answers the strategy of finding and hacking the query that Oracle runs just before raising the error is a good generic method for getting a better handle on what’s causing the problem.
In this example of Oracle error ORA-29758 you can get a list of the objects (logical or physical or both) that are blocking a “drop tablespace” command and that makes it easier to find out what else you need to drop before you can drop the tablespace.
Bear in mind that I’ve just demonstrated the approach while experimenting with a couple of Context indexes, where the information I needed was available through a couple of ordinary data dictionary views – I only dived down into the internals because the data dictionary views weren’t giving the OP a complete answer.
I don’t know what was in the OP’s database to make this problem happen (it may simply be that the database was version 11.2.0.4 while mine was 19.11.0.0 and there’s a bug that’s been fixed since 11g), so the OP may find that after they’ve cleared all domain indexes indicated by this query they may still see ORA-29758 and find that there’s another query that Oracle uses to check for other conditions (maybe those “from statistics type” secondary objects) that also needs to be hijacked and subverted to complete the job. I know that when I traced a successful “drop tablespace” there were 13 more statements after the one I’ve shown that looked as if they might be checking for the (non-)existence of other types of object before the tablespace could be dropped.
MLUI: Django, Tailwind and Flowbite (Katana ML Sparrow)
専門学校で知識と経験を積んだ保育士になろう
国の未来を支える子ども達の存在は、かけがえのないものです。ここ数年は声高に少子化が叫ばれていますが、歯止めがかかる兆しは見えません。このことは誰もが安心して子どもを産み育てることが出来る環境を整えることが、いかに難しいかを反映しているとも言えます。そうした環境のために絶対に必要とされているのが保育所と同時に保育士です。
近年は、今まで以上に保育士という職業に注目が集まっていると言っても過言ではありません。保護者が安心して子どもを任せることが出来るような保育士が理想ですが、そのためには保育について専門的に学んでいる人材がより理想的です。ただ楽しく子どもの相手をするだけが保育士の仕事ではありません。外からは見えにくいものですが、指導案や日々の日誌の記入や保育の記録を残すことも非常に重要な仕事です。
現場に出る前に、そのような専門的なこともしっかりと学び身に付けておくことも大切です。専門学校は保育の専門家を育てることを専門としていますので、そのような実践的な内容を深く学ぶ事が出来ます。四年制大学や短大と比較しても実習の時間数も多く、教室と現場の両方で保育について深く学んで行くことが出来るため、就職後は即戦力として働くことも無理なことではありません。実践的な内容が多いカリキュラムは、専門学校ならではと言うことが出来るでしょう。
子ども達の命と未来を預かる保育士という素晴らしい職業に就くのならば、保育について専門学校で学ぶ事をおすすめします。
専門学校で知識と経験を積んだ保育士になろう
Case Study
The question “How do you trouble-shoot a performance problem” came up in an online session I did for the AIOUG (All-India OUG) today. It’s a very broad question and the only possible answers are either extremely generic, or very specific – so here’s a specific example that I talked about to give some idea of the processes I go through. It’s an example from the Oracle-l list server asking the (paraphrased) question:
I’ve got a parallel query that take 24 seconds to return 2,500 rows for 2018. .The same query for 2019 should return a similar amount of data but consumes a lot of TEMP space before failing; it takes 45 minutes to complete if I remove the parallel hint. The only difference between the two queries is a change to a single predicate: “opclf.year_number = to_number(‘YYYY’)” and the statistics are up to date – what’s going on?
The ease of diagnosing a problem is dependent on the ease of access to all the right information, and you don’t always know initially what the right information might be. In this case the OP had started by posting to github the SQL Monitor reports for the two queries, which were automatically generated since the queries were using parallel execution.
Click here to show/hide the first few sections for the 2019 output
SQL Text
------------------------------
SELECT /*+ PARALLEL(8) */ DISTINCT rd.document_id AS doc_id ,'firm_id' AS criteria_key ,opf.ultimate_parent_firm_id AS series_id ,period_number FROM ( SELECT /*+ PARALLEL(8) */ DISTINCT rd.document_id ,rd.client_role_id ,( CASE WHEN MONTHS_BETWEEN(TO_DATE('04/28/2022', 'MM/DD/YYYY'), TO_DATE('04/01/2017', 'MM/DD/YYYY')) > 12 THEN TRUNC(dc.date_value, 'YEAR') ELSE TRUNC(dc.date_value, 'MONTH') END ) period_number FROM REPORT_ENGINE.date_code dc ,REPORT_ENGINE.lit_fact_bd rd INNER JOIN
report_engine.firm FIRM ON rd.firm_id = FIRM.firm_id WHERE dc.date_value BETWEEN TO_DATE('04/01/2017', 'MM/DD/YYYY') AND TO_DATE('04/28/2022', 'MM/DD/YYYY') AND rd.publication_date_id = dc.date_id AND rd.year_number = to_number('2019') AND (FIRM.ultimate_parent_firm_id IN (to_number('252094'))) ) rd INNER JOIN report_engine.opposing_counsel op ON rd.client_role_id = op.legal_role_id INNER JOIN report_engine.lit_fact_bd opclf ON opclf.document_id = rd.document_id AND op.opposing_counsel_role_id
= opclf.client_role_id AND opclf.year_number = to_number('2019') INNER JOIN report_engine.firm opf ON opclf.firm_id = opf.firm_id AND opf.firm_id >= 1000 WHERE 1 = 1
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SYS (647:28741)
SQL ID : 3cjg20q2vw41f
SQL Execution ID : 16777216
Execution Started : 06/09/2022 05:08:24
First Refresh Time : 06/09/2022 05:08:25
Last Refresh Time : 06/09/2022 05:08:27
Duration : 221s
Module/Action : sqlplus@c111dhw (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@c111dhw (TNS V1-V3)
Global Stats
====================================================================
| Elapsed | Cpu | IO | Other | Buffer | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
====================================================================
| 222 | 179 | 28 | 15 | 49M | 11624 | 2GB |
====================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
=====================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
=====================================================================================================================================
| PX Coordinator | QC | | 0.26 | 0.26 | | 0.00 | 12144 | | . | |
| p000 | Set 1 | 1 | | | | | | | . | |
| p001 | Set 1 | 2 | | | | | | | . | |
| p002 | Set 1 | 3 | | | | | | | . | |
| p003 | Set 1 | 4 | | | | | | | . | |
| p004 | Set 1 | 5 | | | | | | | . | |
| p005 | Set 1 | 6 | | | | | | | . | |
| p006 | Set 1 | 7 | | | | | | | . | |
| p007 | Set 1 | 8 | | | | | | | . | |
| p008 | Set 2 | 1 | 220 | 177 | 28 | 15 | 48M | 11624 | 2GB | direct path write temp (28) |
| p009 | Set 2 | 2 | | | | | | | . | |
| p010 | Set 2 | 3 | | | | | | | . | |
| p011 | Set 2 | 4 | 1.71 | 1.70 | | 0.01 | 595K | | . | |
| p012 | Set 2 | 5 | | | | | | | . | |
| p013 | Set 2 | 6 | | | | | | | . | |
| p014 | Set 2 | 7 | | | | | | | . | |
| p015 | Set 2 | 8 | | | | | | | . | |
=====================================================================================================================================
You’ll note that I haven’t got as far as the body of the execution plan yet, and I’ve highlighted line 44 – a line in the middle of the summary of activity for the parallel execution processes. There are 8 servers in each of two sets (we’re running parallel 8) and the line I’ve highlighted is the first server of the second set. The reason I’ve highlighted it is that it’s the one server that’s doing almost all the work – effectively the query (at some point in the plan) is running serially.
So, a first thought, maybe we’ve just been unlucky and running parallel 7 (say) would result in a better distribution of data across parallel servers and allow each of 7 processes to do whatever they had to do to a much smaller amount of data. Maybe a change to the distribution method (pq_distribute() hint) would spread the workload more evenly. In either case “N” smaller workload might still use a lot of TEMP, but possibly no individual process would fail, and the job would complete nearly N times as quickly.
Note: the 2018 Monitor report showed an equivalent skew in the data distribution, but the amount of work needed was much smaller with a read/write load of only 143MB compared to 2GB for the 2019 report. The OP did try running parallel 7, but with no change to the overall effect.
Let’s take a quick glance at the plan body (click to view/hide)SQL Plan Monitoring Details (Plan Hash Value=1862006233) ========================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | | (%) | (# samples) | ========================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | | | 3 | | | | | | | | | 1 | PX COORDINATOR | | | | | | 3 | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 781 | 153 | | | | | | | | | | | | 3 | HASH UNIQUE | | 781 | 153 | | | | | | | | | | | | 4 | PX RECEIVE | | 781 | 151 | | | | | | | | | | | | 5 | PX SEND HASH | :TQ10002 | 781 | 151 | | | 2 | | | | | | | | | -> 6 | BUFFER SORT | | 781 | 153 | 219 | +3 | 2 | 0 | 11624 | 2GB | 61M | 2G | 26.21 | Cpu (26) | | | | | | | | | | | | | | | | direct path write temp (28) | | -> 7 | NESTED LOOPS | | 781 | 151 | 219 | +3 | 2 | 39M | | | | | | | | -> 8 | NESTED LOOPS | | 781 | 151 | 219 | +3 | 2 | 39M | | | | | 0.49 | Cpu (1) | | -> 9 | NESTED LOOPS | | 777 | 44 | 219 | +3 | 2 | 39M | | | | | | | | -> 10 | NESTED LOOPS | | 41 | 26 | 219 | +3 | 2 | 6463 | | | | | | | | -> 11 | HASH JOIN | | 41 | 21 | 219 | +3 | 2 | 6463 | | | 6M | | | | | 12 | BUFFER SORT | | | | 1 | +3 | 2 | 36855 | | | | | | | | 13 | PX RECEIVE | | 87 | 19 | 1 | +3 | 2 | 36855 | | | | | | | | 14 | PX SEND HASH | :TQ10000 | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 15 | NESTED LOOPS | | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 16 | NESTED LOOPS | | 87 | 19 | 1 | +3 | 1 | 38694 | | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 2 | 1 | +3 | 1 | 43 | | | | | | | | 18 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | 1 | 1 | +3 | 1 | 43 | | | | | | | | 19 | PARTITION RANGE SINGLE | | | | 1 | +3 | 43 | 38694 | | | | | | | | 20 | BITMAP CONVERSION TO ROWIDS | | | | 1 | +3 | 43 | 38694 | | | | | | | | 21 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | 1 | +3 | 43 | 49 | | | | | | | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 63 | 19 | 3 | +1 | 38694 | 38694 | | | | | 0.49 | Cpu (1) | | 23 | PX RECEIVE | | 20 | 2 | 1 | +3 | 2 | 2 | | | | | | | | 24 | PX SEND HASH | :TQ10001 | 20 | 2 | | | | | | | | | | | | 25 | PX BLOCK ITERATOR | | 20 | 2 | | | | | | | | | | | | 26 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 2 | | | | | | | | | | | | -> 27 | TABLE ACCESS BY INDEX ROWID | DATE_CODE | 1 | | 219 | +3 | 6465 | 6463 | | | | | | | | -> 28 | INDEX UNIQUE SCAN | PK_DATE_CODE | 1 | | 219 | +3 | 6465 | 6465 | | | | | | | | -> 29 | PARTITION RANGE SINGLE | | 19 | | 219 | +3 | 6465 | 39M | | | | | | | | -> 30 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 19 | | 220 | +2 | 6465 | 39M | | | | | 35.92 | Cpu (74) | | -> 31 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 219 | +3 | 6465 | 39M | | | | | 9.22 | Cpu (19) | | -> 32 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 219 | +3 | 39M | 39M | | | | | 10.68 | Cpu (22) | | -> 33 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | | 219 | +3 | 39M | 39M | | | | | 16.99 | Cpu (35) | ===================================================================================================================================================================
You can see from the “->” symbols at the left hand side of the plan that this report was generated while the plan was still running. The thing that leaps out as you glance down the page is the value in the “Rows (Actual)” column at operations 7-9 (which show the rowsources generated by some nested loop joins) and operations 29, 32 and 33 of the plan that tell us something about how those rowsources were generated.
Operation 29 has executed (Execs) 6,465 so far, producing a total of 39M rows, and operations 32 and 33 have both executed 39M times each producing a total of 39M rows by index unique scan.
The plan for the 2018 data was similar though the join order for DATE_CODE, LIT_FACT_BD and FIRM was different (and it was the join to LIT_FACT_BD that increased the row count dramatically – so hinting it to be the last table in the join might help a bit), but the largest rowcount for the 2018 query was only 3M rows, not the 39M that had appeared after only 6,465 rows of a possible driving 39,855 in the 2019 query.
So it’s not surprising that the query could take much longer for 2019. It’s not the volume of output that matters, it’s the volume of input (or, more accurately, throughput or intermediate) data that matters.
Let’s think about that volume, though: the 2018 plan generated 3M rows and then crunched them down to 2,500 rows and the 2019 plan was supposed to produce a similar sized output (from 39M+ rows). Could we rewrite the query in some way that made it do some intermediate aggregation so that the volume of data to be aggregated was never enormous?
Let’s take a look at the plan from the 2018 Monitor report (click to show/hide)SQL Plan Monitoring Details (Plan Hash Value=472871521) ======================================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) | ======================================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +24 | 17 | 2613 | | | | | | | | | | 1 | PX COORDINATOR | | | | 1 | +24 | 17 | 2613 | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 39 | 1 | +24 | 8 | 2613 | | | | | | | | | | 3 | HASH UNIQUE | | 1 | 39 | 9 | +16 | 8 | 2613 | | | | | 9M | | 6.90 | Cpu (2) | | 4 | PX RECEIVE | | 1 | 38 | 9 | +16 | 8 | 3M | | | | | | | | | | 5 | PX SEND HASH | :TQ10002 | 1 | 38 | 12 | +14 | 8 | 3M | | | | | | | 3.45 | Cpu (1) | | 6 | BUFFER SORT | | 1 | 39 | 23 | +2 | 8 | 3M | 4584 | 143MB | 703 | 143MB | 151M | 151M | 34.48 | Cpu (2) | | | | | | | | | | | | | | | | | | direct path read temp (6) | | | | | | | | | | | | | | | | | | direct path write temp (2) | | 7 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 8 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 9 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 10 | NESTED LOOPS | | 1 | 38 | 15 | +2 | 8 | 3M | | | | | | | | | | 11 | HASH JOIN | | 41 | 21 | 15 | +2 | 8 | 19334 | | | | | 7M | | | | | 12 | BUFFER SORT | | | | 13 | +2 | 8 | 19233 | | | | | 1M | | | | | 13 | PX RECEIVE | | 89 | 19 | 13 | +2 | 8 | 19233 | | | | | | | | | | 14 | PX SEND HASH | :TQ10000 | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 15 | NESTED LOOPS | | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 16 | NESTED LOOPS | | 89 | 19 | 1 | +1 | 1 | 19233 | | | | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 2 | 1 | +1 | 1 | 43 | | | | | | | | | | 18 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | 1 | 1 | +1 | 1 | 43 | | | | | | | | | | 19 | PARTITION RANGE SINGLE | | | | 1 | +1 | 43 | 19233 | | | | | | | | | | 20 | BITMAP CONVERSION TO ROWIDS | | | | 1 | +1 | 43 | 19233 | | | | | | | | | | 21 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | 1 | +1 | 43 | 51 | | | | | | | | | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 64 | 19 | 1 | +1 | 19233 | 19233 | | | | | | | | | | 23 | PX RECEIVE | | 20 | 2 | 15 | +2 | 8 | 20 | | | | | | | | | | 24 | PX SEND HASH | :TQ10001 | 20 | 2 | 1 | +14 | 8 | 20 | | | | | | | | | | 25 | PX BLOCK ITERATOR | | 20 | 2 | 1 | +14 | 8 | 20 | | | | | | | | | | 26 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 2 | 1 | +14 | 3 | 20 | | | | | | | | | | 27 | PARTITION RANGE SINGLE | | 1 | | 15 | +2 | 19334 | 3M | | | | | | | | | | 28 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | | 16 | +1 | 19334 | 3M | | | | | | | 17.24 | Cpu (5) | | 29 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 15 | +2 | 19334 | 3M | | | | | | | | | | 30 | TABLE ACCESS BY INDEX ROWID | DATE_CODE | 1 | | 15 | +2 | 3M | 3M | | | | | | | 10.34 | Cpu (3) | | 31 | INDEX UNIQUE SCAN | PK_DATE_CODE | 1 | | 16 | +1 | 3M | 3M | | | | | | | 6.90 | Cpu (2) | | 32 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 23 | +2 | 3M | 3M | | | | | | | 6.90 | Cpu (2) | | 33 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | | 16 | +1 | 3M | 3M | | | | | | | 13.79 | Cpu (4) | =======================================================================================================================================================================================================================================
We see from operations 3 – 7 that the 3M rows generated from the nested loop joins pass up through a buffer sort operation before being crunched down to 2,613 rows. It’s probably the buffer sort (which is buffering but not sorting) that has mostly passed through a single server and spilled to disc in the 2019 report. We just don’t want that 39M+ rows ever to exist.
So how easy will it be to change the SQL (click to view/hide)
SELECT
/*+ PARALLEL(8) */
DISTINCT rd.document_id AS doc_id
,'firm_id' AS criteria_key
,opf.ultimate_parent_firm_id AS series_id
,period_number
FROM (
SELECT
/*+ PARALLEL(8) */
DISTINCT rd.document_id
,rd.client_role_id
,(
CASE
WHEN MONTHS_BETWEEN(TO_DATE('04/28/2022', 'MM/DD/YYYY'), TO_DATE('04/01/2017', 'MM/DD/YYYY')) > 12
THEN TRUNC(dc.date_value, 'YEAR')
ELSE TRUNC(dc.date_value, 'MONTH')
END
) period_number
FROM REPORT_ENGINE.date_code dc
,REPORT_ENGINE.lit_fact_bd rd
INNER JOIN report_engine.firm FIRM ON rd.firm_id = FIRM.firm_id
WHERE dc.date_value BETWEEN TO_DATE('04/01/2017', 'MM/DD/YYYY')
AND TO_DATE('04/28/2022', 'MM/DD/YYYY')
AND rd.publication_date_id = dc.date_id
AND rd.year_number = to_number('2019')
AND (FIRM.ultimate_parent_firm_id IN (to_number('252094')))
) rd
INNER JOIN report_engine.opposing_counsel op ON rd.client_role_id = op.legal_role_id
INNER JOIN report_engine.lit_fact_bd opclf ON opclf.document_id = rd.document_id
AND op.opposing_counsel_role_id = opclf.client_role_id
AND opclf.year_number = to_number('2019')
INNER JOIN report_engine.firm opf ON opclf.firm_id = opf.firm_id
AND opf.firm_id >= 1000
WHERE 1 = 1;
Lines 7-10 and 27 tell us we alredy have an inline view where we’re doing a “select distinct” and, unwinding the mix of “Oracle” and “ANSI” syntax, we can see that it joins DATE_CODE, LIT_FACT_BD and FIRM, and we know that one of those tables explodes the intermediate data size to something enormous. So it looks like the original author of this code had already worked out that the query needed to aggregate early.
Checking back to the original plans we note that there’s only one “hash unique” operation, and there’s no sign of a “view” operation, so maybe the performance problem is a result of the optimizer suddenly deciding it can do complex view merging with this inline view, and perhaps all we need to do is add the hint /*+ no_merge */ to the inline view and see what happens.
Here’s the plan after adding the hint (click to hide/vew)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- | 0 | SELECT STATEMENT | | | | 314 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10005 | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | P->S | QC (RAN D) | | 3 | HASH UNIQUE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | PCWP | | | 4 | PX RECEIVE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,05 | PCWP | | | 5 | PX SEND HASH | :TQ10004 | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,04 | P->P | HASH | | 6 | HASH UNIQUE | | 23 | 2599 | 314 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 7 | NESTED LOOPS | | 23 | 2599 | 313 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 8 | NESTED LOOPS | | 23 | 2599 | 313 (1)| 00:00:06 | | | Q1,04 | PCWP | | | 9 | NESTED LOOPS | | 23 | 2323 | 310 (1)| 00:00:06 | | | Q1,04 | PCWP | | |* 10 | HASH JOIN | | 388 | 21340 | 148 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 11 | PX RECEIVE | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | | | 12 | PX SEND BROADCAST | :TQ10002 | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCA ST | | 13 | PX BLOCK ITERATOR | | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | | |* 14 | TABLE ACCESS FULL | OPPOSING_COUNSEL | 20 | 160 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | | | 15 | VIEW | | 835 | 39245 | 146 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 16 | HASH UNIQUE | | 835 | 63460 | 146 (1)| 00:00:03 | | | Q1,04 | PCWP | | | 17 | PX RECEIVE | | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,04 | PCWP | | | 18 | PX SEND HASH | :TQ10003 | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,03 | P->P | HASH | |* 19 | HASH JOIN BUFFERED | | 835 | 63460 | 145 (0)| 00:00:03 | | | Q1,03 | PCWP | | | 20 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | | | 21 | PX RECEIVE | | 835 | 52605 | 136 (0)| 00:00:03 | | | Q1,03 | PCWP | | | 22 | PX SEND HASH | :TQ10000 | 835 | 52605 | 136 (0)| 00:00:03 | | | | S->P | HASH | | 23 | NESTED LOOPS | | 835 | 52605 | 136 (0)| 00:00:03 | | | | | | | 24 | NESTED LOOPS | | 835 | 52605 | 136 (0)| 00:00:03 | | | | | | | 25 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 2 (0)| 00:00:01 | | | | | | |* 26 | INDEX RANGE SCAN | FIRM_ULT_PARENT_FIRM_IDX1 | 1 | | 1 (0)| 00:00:01 | | | | | | | 27 | PARTITION RANGE SINGLE | | | | | | 30 | 30 | | | | | 28 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | | |* 29 | BITMAP INDEX SINGLE VALUE | LIT_FACT_BD_IDX09 | | | | | 30 | 30 | | | | |* 30 | TABLE ACCESS BY LOCAL INDEX ROWID| LIT_FACT_BD | 598 | 30498 | 136 (0)| 00:00:03 | 30 | 30 | | | | | 31 | PX RECEIVE | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,03 | PCWP | | | 32 | PX SEND HASH | :TQ10001 | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | P->P | HASH | | 33 | PX BLOCK ITERATOR | | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWC | | |* 34 | TABLE ACCESS FULL | DATE_CODE | 1854 | 24102 | 9 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 35 | PARTITION RANGE SINGLE | | 1 | 46 | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 36 | TABLE ACCESS BY LOCAL INDEX ROWID | LIT_FACT_BD | 1 | 46 | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 37 | INDEX RANGE SCAN | LIT_FACT_BD_IDX20 | 1 | | 0 (0)| | 30 | 30 | Q1,04 | PCWP | | |* 38 | INDEX UNIQUE SCAN | PK_FIRM | 1 | | 0 (0)| | | | Q1,04 | PCWP | | | 39 | TABLE ACCESS BY INDEX ROWID | FIRM | 1 | 12 | 0 (0)| | | | Q1,04 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
Note particularly that operations 15 and 16 tell us that we’ve forced the optimizer into treating the inline view as a separate query block and we now have two aggregation steps, one inside the view, and another after joining FIRM (again) and LIT_FACT_BD (again) to the inline view.
Unfortunately the plan shown here is pulled from memory using dbms_xplan.display_cursor() after execution, so it include the various parallel executoin colums (TQ, IN-OUT, PQ Distrib), but doesn’t have the rowsource execution stats enabled so we can’t see the actual workload and data volume, but in the words of the OP: “adding no_merge hint did the trick and now the SQL is just executing fine”.
SummaryThe steps for solving the performance problems of a specific SQL statement are very fluid. For a long-running or parallel statement the SQL Monitor report will automatically be created (though there are limits on the size of the plan that may disable the feature) and this is the easiest source of useful information, though you might also need to pull the execution plan from v$sql_plan to get details about parallel execution and partitioning at the same time.
If you’re not licensed for the diagnostic and performance packs, though, enabling SQL Trace to get the plan and waits gets you a lot of infomation, and querying (g)v$pq_tqstat immediately after running the query can fill in the parallel traffic details.
In this example the SQL Monitor report showed a highly skewed distribution, which might have been fixable by changing the PQ distribution, or even by simply changing the degree of parallelism.
Further examination of the report showed that the query generated an enormous rowsource which it then crunched down to a small result set. Comparing the 2018 and 2019 plans (which were not quite identical but were sufficiently similar to justify comparison) the same skew and explosion of rowsource were visible in both, though the data size involved in the 2018 plan made it seem that the plan was a “good” one which really it wasn’t.
The obvious target for tuning was to see if the explosion in volume could be reduced or eliminated by writing the query with some intermediate non-mergeable view(s), and it turned out that the query had been written with that intent in its original form but without a hint to block complex view merging. After adding the hint the performance was acceptable.
Semantic Layer for Data Scientist
Tracing Data Lineage from Source Column to End User
A while ago I blogged about our Lineage Tool, which back then was limited to diving into the ODI Work Repository and emerging from it with source-to-target data flow diagrams. The task at hand was simple and the design straightforward: show Source and Target database entities and connect them with arrows representing the ELT logic in ODI Mappings. The hierarchy to use to drill down from summary to detail was quite natural: ODI Environment > ODI Model > Datastore (Table) > Attribute (Column).
This 4-level hierarchy works very well with ODI. Here is a view at the Table level:(Similarly we can have views at the Model or the Environment level.)
However, there was a temptation to add more to the lineage: OBIEE (OAS/OAC) RPD and Web Catalog lend themselves nicely to lineage analysis. In fact, in the case of these two, Oracle allows us to generate lineage reports from the Model Administration Tool and Catalog Manager respectively.
Starting with RPD's Physical Model, we have very similar entities there: Physical Tables and their Aliases have Columns. Tables are grouped into Physical Schemas, which in turn are part of Databases. It looks like we have one more hierarchy level here: OBIEE Environment > Database > Physical Schema > Physical Table / Alias > Column. We could acknowledge that different lineage analysis sources will have different hierarchies with their own number of hierarchy levels. However, we also want to see the entire lineage across ODI and OBIEE at a certain hierarchy level - showing ODI lineage at a Column level and OBIEE lineage at a Table level will not work very well. Therefore we had to force all hierarchies to conform to 4 hierarchy levels. Here is the OBIEE RPD Physical Layer:
We have 4 hierarchy levels instead of 5 - Environment and Database are squeezed into a single level.
Notice Physical Tables on the left and their Aliases on the right - table and view aliasing naturally becomes part of the lineage flow.
Further down the lineage line, we have the OBIEE Presentation Layer - we can keep the Lineage more compact by hiding the BMM Layer by going from Physical straight to Presentation. Controversial? Yes, we do not always want as much detail in a diagram as possible. Just like our ODI lineage diagram that shows only Source and Target tables, hiding the inner workings of the ODI Mapping.
Here we have Presentation Tables and their Columns, Subject Area (SA) instead of a Schema, OBIEE RPD Environment at the top. This time there is no need to an extra hierarchy level, but we add (Presentation) after the Environment name to better distinguish it from the Physical Layer.
Adding OBIEE Web Catalog to our Lineage is no harder than adding the RPD.
This time it seems we only really need 3 hierarchy levels: OBIEE Environment > Analysis (Report) > Report Column. However, we can introduce Catalog Folders to the hierarchy to stand at the Schema level.
As we can see, conforming to the 4-level hierarchy can be artificial in some cases, but overall the lineage makes sense and, importantly, we can easily switch between the 4 hierarchy levels to get summary or detailed views, drill down Schemas, Tables and Columns to get a filtered view of those entities.
But does the Lineage end at the BI Report? It depends on what we want to call Lineage. We can argue that the end point of our lineage is not the Report but the BI User. Getting Usage Adoption data from OBIEE is easy, assuming that Usage Adoption data gathering is enabled. But can we fit UA data into our lineage, and make it conform to the 4-level hierarchy?
It is a stretch but yes, we can. Let us note that Usage Adoption is the first lineage source that does not have the concept of a Column and a collection of Columns (e.g. table or report). I chose to assign the user name to the table hierarchy level - because the table and the column hierarchy levels are probably going to be the two most frequently used and I do not want the user to be available at the finest level only. In the example above, the finest detail is Report Access Date. In a Production system that has been used for a few years, it would be substituted with Month or Quarter.
The end result is one large diagram that goes from the source table and column all the way to the BI User accessing that data in a report.
In the diagram, we can select a source column, say, SRC_PRODUCT.PRICE and see which users have accessed reports with Product Price in it. If the column data is deemed sensitive, we can do security audits by using this Lineage diagram. We can see if a certain source Table or Column is currently being used in BI reports and also if those reports are actually being used. We can start our analysis from the other end as well, by selecting a user and seeing what source table and column data that user is or is not accessing.
As of now, the Rittman Mead's Lineage Tool is capable of tracing and visualising data lineage starting with the ODI Mapping's Source Datastore Column and ending with the OBIEE User. The Lineage Tool is constantly evolving and there is still much we want to add to it. The journey so far has been interesting and I am sure we will revisit the compromises we have made to make the many lineage data sources fit together in a coherent hierarchy in a single end-to-end lineage.
Interested in finding out more about the Lineage Tool, contact us.
Cloud Database and Cloud DataLake
Pages
