-
Title
How to use the right Oracle index to resolve post performance issue -
Description
Many times post performance issue happens because tables do not have a good index to use.
-
Cause
Oracle utilizes indexes to update or delete a record from a table. If table does not have index or not have up to date statistic information, Oracle will spend more time to locate the row.
-
Resolution
1. use the following methods to identify a sql statement that causes post to be slow
Option 1 and easiest step to idientify a sql statement.
i. issue “show post sessions detail queue queue name” few times in 1 second interval when post is slow. Look for the activate session for thread ID.
ii. issue “show post sql <queue> <queue_name>” few times in 1 second interval when post is slow. If the same update or delete sql statements returned for the same thread id, these statements are very likely the ones that cause slow post.
Option 2 when option 1 does not provide a complete sql statement.
Issue “show post detail queue <queue_name>” few times in 1 second interval. If the same update or delete sql statements for the same object returned, then this object is very likely the one that causes slow post.
i. set object level debug for the object that found in the above step (b.)
ii. retrieve sql statement from opo log
Option 3 which provides a more detail performance report
c. turn on timing debug when post slow
i. setting the sp_opo_debug_flag parameter to 0x0f000800: sp_ctrl>set param sp_opo_debug_flag queue <queue_name> 0x0f000800
ii. timing script to parse opo log and identify a problem table.
iii. get the object that post has problem posting its messages
iv. set object level debug for the object that found in the above step (iii).
v. retrieve sql statement from the opo log
2. run explain plan for the sql statement on target
3. compare the output of explain plan
a. if the explain plan show execution uses right index:
i. stop post
ii. analyze the table to collect new statistic
iii. start post
b. explain plan show execution uses the wrong index:
i. compare source and target indexes to see if there are any differences.
* select TABLE_NAME, INDEX_NAME, UNIQUENESS, DISTINCT_KEYS, NUM_ROWS from dba_indexes where table_name = <fill in table name>;
ii. ask client to correct the differences
* only ask client to add indexes.
iii. stop post / analyze the table to collect new statistic/ start post
4.if post is still slow after step 3, shareplex may need to use different index.
a. create new index for all the columns except columns which have is_null value in the where clause
b. if there are too many columns in the where clause, create new index on columns that have the most distinct values
select column_name, num_distinct, nullable from dba_tab_columns where table_name = <object_name> order by num_distinct asc;
----------------------------------------
With respect to option 3:
Current SharePlex versions have the feature of running “trace” on a specific Post queue (if using named Post queue) or the general Post queue (if no named Post queues are used). This comes in handy as the command is very simple with no debug to be turned on or off, nor is there a need to run the script post_timing_debug.sh on that opo log generated by manual debug. The command offers an inherent debugging capability with a default debug running time of 15 minutes or as specified by user.
For example, I can trace the actions of Post for 10 minutes as:
sp_ctrl>trace post queue [queue_name] 10
Once the trace finishes, the *trace* file is generated in target's /vardir/log directory
Here is how a typical trace file looks like (which has the same look and feel of the *opo* log generated with the debug setting of 0x0f000800. The debug still works but the trace is much more convenient. There is no separate execute.out generated and the trace file contains the output as given in the erstwhile execute.out file. Here is a sample content of the trace file:
POST TRACE: Started: Tue Aug 16 21:14:20 2016
Completed: Tue Aug 16 21:29:21 2016
Summary
-------
Rate (operations/sec) 239.8
Transaction rate (transactions/sec) 61.5
Average transaction size 3.9
Elapsed time 15:01
Total time spent in SharePlex
-----------------------------------
Read from the queue 0:25
Time spent on SHAREPLEX_TRANS table 0:00
Total time spent in Oracle
--------------------------------
Oracle execute 9:03
Oracle commit 7:35
Update LOB columns 1:06
Session Oracle execute Oracle commit LOB columns
------- -------------- ------------- -----------
2 0:28 0:30 0:03
14 0:27 0:22 0:05
30 0:29 0:21 0:03
10 0:23 0:22 0:04
28 0:23 0:23 0:00
Detail
------
Number Executions Cost
Operation Count Pct Seconds Pct
--------- ----- ---- ------- ----
Insert 88301 32% 227 21%
Multi-row Insert 12672 4% 3 0%
Update 76281 27% 166 15%
Delete 24300 8% 138 13%
Commit 55445 20% 455 42%
Update Lob 1288 0% 36 3%
Insert Lob 2534 0% 29 2%
Batch Insert 1967 0% 2 0%
Batch Update 11024 4% 4 0%
Batch Delete 1474 0% 0 0%
Operation Table Avg(microsec) Count Total(sec)
--------- ----- ------------- ----- ----------
Insert "OWNER"."TABLE_NAME" 56027 14 0
.
.