Recently, during a generic health check of a critical database at a customer site, I noticed high values of Version Count for 3-4 Queries. I have a blog on this topic, in the month of June 2008 and this is something new that I noticed and thought this to be worth writing. The database version in this case is 10g Release 2.
In this case, there were three type of issues that were contributing to Huge Version Counts for the queries. These are :
Case 1
-------
For a query with sql_id 'dbtm3tanbbru0', the values for column PQ_SLAVE_MISMATCH and BIND_PEEKED_PQ_MISMATCH were 'Y' in v$sql_shared_cursors.
This seems to be a known issue in 10g Release 2 and there is a Patch#6602742 for the same. But the issue can also be eliminated by disabling parallelism for the queries. This needs proper testing. In this case, due to parallelism, the query was doing Full Table Scans of 2 huge tables. I checked for the execution plan and Logical I/O's after disabling parallelism, using following command.
Alter session disable parallel query;
The plan was much better and the I/O's were very less as compared to the Original. Therefore, in this case, it was recommended to remove parallelism. Note, that in this case, optimizer was using parallel slaves since the DEGREE in DBA_TABLES was set to DEFAULT. To eliminate parallelism, we altered this using :
Alter table tablename noparallel;
With this change, the version count for this query came down to 1, a huge benefit.
I should admit here that though after altering the table with noparallel clause the version count for this query came down to 1 and the performance of this alongwith few more on this was better than with parallel, 2 critical processes started taking much of the time and hence, this change had to be reverted back. Therefore, the alternative is to apply the patch and/or, optimize the 2 processes and then make the changes.
Case 2
-------
For some queries, none of the columns in v$sql_shared_cursors were 'Y'. Again, I assume this to be an issue with CURSOR_SHARING set to SIMILAR. Even without Histograms, cursor_sharing similar creates multiple child cursors. Needs investigation on this. As a workaround, the parameter was set to FORCE and this was after confirming that there are no histograms as FORCE can have an impact when using histograms as these will be ignored completely by the optimizer.
By setting Cursor_Sharing to Force, the version count of some of the queries came down drastically.
Case 3
-------
For some of the queries, the value of BIND_MISMATCH in v$sql_shared_cursor was 'Y'. This seems to be easy to diagnose. From v$sql_bind_mismatch, we can get the columns used by a query with different data types or different variable length. (I have a query posted in June 2008 that self joins v$sql_bind_capture and gets the detail of the columns with these mismatch).
For the issue related to Variable Length, there is an Event 10503 that can be set to limit the variable length. V$sql_bind_metadata is very helpful in diagnosing these limits. For Oracle, a variable X defined as varchar2(30) is different than variable X defined as varchar2(33). This is bind variable graduation. It groups the variables into 4 groups as under :
Group 1 upto 32 bytes,
Group 2 from 33 bytes to 128 bytes,
Group 2 from 129 bytes to 2000 bytes,
Group 2 more than 2000 bytes.
Event 10503 can be set to limit the variable size.
At present, we have not applied this event as I believe in One Change at a time. Once we are sure that the impact of the first 2 cases is neglible or ZERO, then we can safely go ahead and apply this final change.
With the 2 changes, the contention or misses on Library Cache Latches have come down drastically. Since, this was a proactive database health check, by reducing the number of misses, we have eliminated a performance problem that could have been triggered by an increase in volume or load.
The scripts and the output about the investigation that we just discussed is pasted below.
.
This Query gets top 10 Queries, in terms of Version Count
.
select * from (
select sql_id, version_count, executions from v$sqlarea
order by 2 desc)
where rownum<=10;
.
SQL_ID VERSION_COUNT EXECUTIONS
------------- ------------- ----------
dbtm3tanbbru0 9208 56572
5h3am43ybmhy2 8945 41176
91hkjwr85hgr6 1161 2666
9z14sb4366jqv 847 912
7s3n1256d21j5 182 502
4p5t54gc63xph 78 84
buybphn36j7fs 71 304
05dxjpjuj93uq 64 7806
bzru91tydqk16 64 69
6r5j07fzu47x1 57 57
.
.
This Query gets top 10 Queries, in terms of Sharable Memory.
It can be seen that first 2 Queries are consuming almost 1 GB of the Shared Pool Memory.
Shared Pool, in this case is 2.1 GB. An Increase in Load or Volume could have triggered ora-04031 or Library Cache Latch Contention.
.
select * from (
select sql_id, sharable_mem, executions from v$sqlarea
order by 2 desc)
where rownum<=10;
.
SQL_ID SHARABLE_MEM EXECUTIONS
------------- ------------ ----------
dbtm3tanbbru0 596892318 56580
5h3am43ybmhy2 507487843 41180
9z14sb4366jqv 16974277 612
91hkjwr85hgr6 15076612 666
buybphn36j7fs 7551983 304
7s3n1256d21j5 4185329 103
bzru91tydqk16 4092758 69
f8pff1d7qmp3q 3993475 1663
4p5t54gc63xph 3933352 84
b6g614ja472k7 3283319 164
.
show parameter shared_pool
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
shared_pool_reserved_size big integer 110729625
shared_pool_size big integer 2112M
.
.
This Query checks the Child Latch the top 2 Queries reside on.
.
SQL> select sql_id, child_latch from v$sqlarea where sql_id in ('dbtm3tanbbru0','5h3am43ybmhy2');
SQL_ID CHILD_LATCH
------------- -----------
5h3am43ybmhy2 12
dbtm3tanbbru0 4
.
.
This is the distribution of Objects protected by each child latches.
The distribution shows that the top 2 latches are the one that protect the 2 queries above.
.
.
select child_latch, cnt, round(ratio_to_report(cnt) over()*100,2) "%age" from (
select child_latch, count(*) cnt from v$sql
group by child_latch
order by 2 desc);
.
CHILD_LATCH CNT %age
----------- ---------- ----------
4 1702 28.02
12 1430 16.74
19 706 3.33
30 644 3.04
35 600 2.83
9 594 2.8
5 557 2.63
6 549 2.59
21 542 2.55
10 541 2.55
33 535 2.52
28 533 2.51
1 529 2.49
.
.
output trimmed...
.
.
There are multiple reasons for high version count of a query and this needs proper investigation. In some cases, these are application related. However, in few cases it is not directly related to the application and requires some tweaking at the database level as well.
Library Cache Latch Contention or ora-04031 can halt the database, therefore, eliminating this worst scenario, by way of proactive tuning, provides much of the relief to the business users by way of scalability. Less the contention or misses on Latches, more scalable is the application.
0 comments:
Post a Comment