Location>code7788 >text

Oracle 23ai TPC-H execution status

Popularity：875 ℃/2025-03-09 08:41:15

TPC-H is a widely used benchmark for evaluating the performance of database systems in decision support system (DSS) scenarios.

In yesterday's article, we completed theOracle 23ai TPC-H Test Environment Deployment》, this article will continue to record the real execution of these 22 SQL items without any optimization scenarios.

  • 1. Revise SQL with problematic execution
  • 2. Completely run 22 SQLs
  • 3. Test results statistics (no optimization scenarios were done)

1. Revise the SQL with problematic execution

Because according toOracle 23ai TPC-H Test Environment Deployment》The prepared TPC-H environment, and the actual test found that 5 SQLs need to be fine-tuned, otherwise an error will be reported in the execution.

But don't worry, it's all a minor problem and does not require major changes. It's mainly because it does not comply with Oracle's special syntax rules. Just modify it slightly:

  • Oracle does not allow use before subquery aliasASKeywords
  • Oracle subquery alias and cannot be followed by the column name list. You only need to write the alias directly at the end of the subquery.
  • Oracle does not support this syntax for substring(c_phone from 1 for 2) function

References to specific SQL and rewriting schemes involved in actual testing:

、、

  • Delete the error positionASIf the keyword is not found, it can be executed. The specific location will be reported and the error will be deleted.ASJust keywords.

  • Delete the error positionASKeywords
  • Put the alias specified after(c_custkey,c_count)Remove all column names, column names aliasc_countIt can be specified directly in the subquery.

  • Delete the error positionASKeywords
  • Willsubstring(c_phone from 1 for 2)Rewrite as:substr(c_phone, 1, 2), there are three places in total.

Other optimizations (optional):

If there are extra where rownum statements at the end of the file, it can be quickly removed to avoid unnecessary error echoes in the test:
Query the last line:tail -n 1 query*.sql
I'll remove the last line of the file here:sed -i '$d' query*.sql

2. Completely run 22 SQLs

I need to record the 22 SQL queries that have TPC-H run in full before doing any optimizations and record the execution time of each query.

I'm using the following script herequery_all.shtest:

vi query_all.sh

for i in {1..22}; do
     echo "PROMPT currently executes $i query;" >>
     echo "@query$i" >>
 done

 sqlplus -s tpch/tpch@alfred @ > 2>&1

Because the execution time is relatively long, I chose to put it in the background to execute it, and at the same time I can use the weekend time:

nohup sh query_all.sh &

After the background execution was completed, it was found that this was 811M size...
Well, some queries return too many rows. Don't worry about it for now, just filter the execution time display:

[oracle@dbtest dbgen]$ grep "Elapsed:" 
Elapsed: 00:06:36.20
Elapsed: 00:01:09.51
Elapsed: 00:07:43.89
Elapsed: 00:07:24.58
Elapsed: 00:08:11.85
Elapsed: 00:06:01.30
Elapsed: 00:07:39.31
Elapsed: 00:07:46.35
Elapsed: 00:10:10.19
Elapsed: 00:08:45.04
Elapsed: 00:00:56.25
Elapsed: 00:07:04.59
Elapsed: 00:02:46.57
Elapsed: 00:06:22.81
Elapsed: 00:00:00.06
Elapsed: 00:06:09.22
Elapsed: 00:00:00.38
Elapsed: 00:01:11.31
Elapsed: 00:06:20.15
Elapsed: 00:17:00.94
Elapsed: 00:07:05.97
Elapsed: 00:07:15.76
Elapsed: 00:14:26.95
Elapsed: 00:01:22.08

This is a bit messy. . And why are there 24? Two more than 22 SQLs?
Here is a small trick. After matching the line of the keyword, the following 1 line is displayed:

grep -A 1 "Elapsed:"

This shows the result, the first oneElapsedTime corresponds to the first query, and it is easy to find one-to-one correspondence later, which is convenient for judgment. It turns out that when executing the 15th query, there is the logic of creating the view first, then querying, and then deleting the view, soElapsedThere will be two more, just remove these two time interferences.

3. Test results statistics (no optimization scenarios were done)

LetWhale AssistantHelp me organize the table directly based on the actual test results, so that everyone can see the test results intuitively.

Query execution time statistics table

Query number Execution time (HH:MM:SS) Execution time (seconds) Execute fast and slow ranking
11 00:00:56.25 56.25 1
2 00:01:09.51 69.51 2
16 00:01:11.31 71.31 3
22 00:01:22.08 82.08 4
13 00:02:46.57 166.57 5
15 00:06:09.22 369.22 6
6 00:06:01.30 361.30 7
17 00:06:20.15 380.15 8
14 00:06:22.81 382.81 9
1 00:06:36.20 396.20 10
12 00:07:04.59 424.59 11
19 00:07:05.97 425.97 12
20 00:07:15.76 435.76 13
4 00:07:24.58 444.58 14
7 00:07:39.31 459.31 15
3 00:07:43.89 463.89 16
8 00:07:46.35 466.35 17
5 00:08:11.85 491.85 18
10 00:08:45.04 525.04 19
9 00:10:10.19 610.19 20
21 00:14:26.95 866.95 21
18 00:17:00.94 1020.94 22

TOP 5 Slow SQL execution status

Query number Execution time (HH:MM:SS) Execution time (seconds) Execute fast and slow ranking
18 00:17:00.94 1020.94 22
21 00:14:26.95 866.95 21
9 00:10:10.19 610.19 20
10 00:08:45.04 525.04 19
5 00:08:11.85 491.85 18

Key information

  1. The slowest query：
    • Query number18, execution time1020.94 seconds(about17 minutes), ranked 22nd.
  2. The second slow query：
    • Query number21, execution time866.95 seconds(about14 minutes 27 seconds), ranked 21st.
  3. The third slow query：
    • Query number9, execution time610.19 seconds(about10 minutes 10 seconds), ranked 20th.
  4. Fourth slow query：
    • Query number10, execution time525.04 seconds(about8 minutes 45 seconds), ranked 19th.
  5. Fifth slow query：
    • Query number5, execution time491.85 seconds(about8 minutes 12 seconds), ranked 18th.

illustrate

  • Execute fast and slow rankingIt is sorted from small to large according to the execution time (seconds). The smaller the ranking means the faster the query, and the larger the ranking means the slower the query.
  • The execution time of the above TOP 5 slow SQL exceeds the8 minutes, where the query number18and21The execution time is particularly prominent, exceeding the17 minutesand14 minutes。
  • These slow SQLs may require further optimization, such as checking indexes, optimizing query logic, or tuning database configurations to improve overall performance.

In subsequent articles, we will start optimizing these SQLs, welcome to follow.

<Previous Next>