This content has been marked as final. Show 25 replies
Thanks SB for joining back on this thread.
Here are the commands I used:
I would do a describe on the table, but with 520 columns, it is not valuable for this purpose to see them all.
SQL> drop table TEST_T2179_CHAINING; Table dropped. SQL> create table test_T2179_chaining as select * from aradmin.T2179 where rownum < 11; Table created. SQL> exec dbms_stats.gather_table_stats('JWOLFF','test_T2179_chaining') PL/SQL procedure successfully completed. SQL> select table_name, chain_cnt from user_tables where table_name = 'TEST_T2179_CHAINING'; TABLE_NAME CHAIN_CNT -------------------------------- --------------- TEST_T2179_CHAINING 0 1 row selected. SQL> select index_name, index_type from dba_indexes where table_name = 'TEST_T2179_CHAINING'; INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- SYS_IL0000259055C00027$$ LOB SYS_IL0000259055C00151$$ LOB SYS_IL0000259055C00152$$ LOB SYS_IL0000259055C00387$$ LOB SYS_IL0000259055C00388$$ LOB SYS_IL0000259055C00389$$ LOB SYS_IL0000259055C00391$$ LOB SYS_IL0000259055C00392$$ LOB SYS_IL0000259055C00393$$ LOB SYS_IL0000259055C00394$$ LOB SYS_IL0000259055C00395$$ LOB SYS_IL0000259055C00396$$ LOB SYS_IL0000259055C00397$$ LOB SYS_IL0000259055C00398$$ LOB SYS_IL0000259055C00399$$ LOB SYS_IL0000259055C00400$$ LOB SYS_IL0000259055C00401$$ LOB SYS_IL0000259055C00406$$ LOB SYS_IL0000259055C00408$$ LOB SYS_IL0000259055C00435$$ LOB 20 rows selected.
below is what the Fine Manual has to say concerning CHAIN_CNT
" Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table."
below is what the Fine Manual has to say concerning "ANALYZE" statement.
"Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:
To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks"
Funny how those "fine" manuals have so much good information, huh?
Problem is remembering what's in them after you've read so many of them.
Comes with old age I guess.... ha!~
Anyway, so I did as the "Fine Manual" says to do, but it still doesn't show any chained rows from my example above.
See commands below.
So, I guess now I can conclude that just because my table has 520 columns, this is not a source for CHAIN_CNT in dba_tables on the table with so many columns. Rather, it is because of real row chaining or migration. Would you agree?
SQL> analyze table test_T2179_chaining compute statistics; Table analyzed. SQL> @?/rdbms/admin/utlchain Table created. SQL> analyze table test_T2179_chaining list chained rows into chained_rows; Table analyzed. SQL> SELECT owner_name, table_name, head_rowid, analyze_timestamp FROM chained_rows ORDER BY owner_name, table_name, head_rowid, analyze_timestamp; no rows selected
some thougths - without a sensible order:
- when a table has more than 254 columns a row has to be splitted in row pieces - at least the documentation tells us so - http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#BABEEAAE: "Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks."
- But "typically" also suggests that multiple row pieces can be stored in the same block (http://jonathanlewis.wordpress.com/2011/12/16/i-wish-3/) - and perhaps that case is not counted in the chain_cnt.
- trailling NULLs also have some impact (because they are not stored in the block): http://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/.
- why is there a chain_cnt? Has someone used ANALYZE and when happened the analysis? Perhaps the chain_cnt does not represent the current chaining
- I guess a block dump could shed some light on the subject
Hi again Martin.
Thanks for your feedback.
Well, it is strange about the CHAIN_CNT in dba_tables. So I wonder what it correlates to.(?)
As noted above, my copy of the table in the test database shows it has 3367 chained_rows (from the chained_rows table) after analyzing and listing it into chained_rows).
Yet, after gathering table stats using dbms_stats, it still shows it has a CHAIN_CNT of 0 rows.
SQL> select count(*) from chained_rows; 3367 1 row selected. SQL> select LAST_ANALYZED, PARTITIONED, DEGREE, CHAIN_CNT, NUM_ROWS from user_tables where table_name = 'T2179'; LAST_ANAL PAR DEGREE CHAIN_CNT NUM_ROWS --------- --- ---------- --------------- --------------- 29-NOV-12 NO 1 0 104960
SQL> alter index "SYS_IL0000259013C00027$$" rebuild;
alter index "SYS_IL0000259013C00027$$" rebuild
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB
alter table move lob(LobCol) store as ( tablespace TS );
So, back to my original question, do I need to rebuild the LOB indexes, and if so, how do I do that?I do not think you need, unless your lob values have been shrunk a lot and will not be grow back in future, in other words LOB segments have lots of free space that will not be utilized in future.
Reading this thread I see that you stuck to the idea of minimizing of rows chaining.
But I do not see that this idea is supported by real stats that show that rows chaining has impact on performance in your case. Why did you decided that rows chaining is your problem?
Your tuning approach is counterproductive. It should start from identifying queries that run longer than required time. And, firstly, the "required time" should be defined (with users) for these queries.
Then you should look into plan, and stats. And then you will see what is a real issue: rows chaining, lack of indexes, badly crafted SQL or other.
In beginning you declared about some "obviously poor performance", but you did not share SQL, plan and stats.
Jonathan has a couple of interesting points here: Lob Chunk size defaulting to 8192
I would wonder if Remedy is updating those clobs.
I thought I read somewhere someone saying clobs are the exception to the "don't use multiple blocksize for performance" rule, but I don't have time to find it now, it may be purely my imagination or a myth.
Well, you are partly right in that I looked at chained rows first as the culprit of slow performance.
I am new at this job site and I was asked to look at why overall performance is slower than they feel it should be.
Obviously, this could be for many reasons....
The first thing I did was take some AWR snapshots and compared the top queries taking most CPU, buffer gets, I/O, etc.
There are several issues overall, including poor library cache hit ratio meaning they are not using enough bind variables and the optimizer has to do a hard parse each time to compute a new execution plan.
But moving on, next I noticed in almost all (over 95% of all) top queries were all hitting on the same table, "T2179" which is apparently the master table that Remedy uses.
Given this is a COTS software application, we can't change the tables and underlying structures of the database (other than adding some indexes and such which I FULLY intend to do).
BTW: I've found the need for some bit-map indexes on very low cardinality columns which I think may be a silver bullet finding.
Anyway... so, researching the T2179 table which is in every query, I found that it is about 15G and about 3 million records. That's not too big, nor should it be a source of contention for poor performance.
Looking deeper, I found it had about 2 million chained rows (according to DBA_TABLES). But when I did as SB suggested and analyzed the table listing to CHAINED_ROWS table, there are really only about 1.some million chained rows.
That's still far too many in my opinion.
I have seen horrendous performance many times over the past years and to find out it was because of chained rows.
Once rebuilding the table (CTAS, or export/import, or move), it corrected the problem and no more performance problems.
So, to answer your question, this is the main reason I am "stuck" on looking at the chained rows as a possible culprit of the problem, or at least to eliminate it before moving on.
The application code is what it is, and there isn't much I can do to change it because it is not our code. It belongs to Remedy and I'm sure if I start changing their code, they would frown on our support agreement with them. :)
Yes, there are problems with bad stats as well, and I'm addressing that during the next low usage time to run fresh stats.
And, most indexes have not been rebuilt since inception, but interestingly, they have a script that runs gather_index_stats on all the indexes (but it is not a dynamic SQL script and doesn't get the newer indexes that have been added).
But getting back to the chained rows....
Thanks for explanation how you get to the chained rows. :)
It is more clear now.
Anyway... so, researching the T2179 table which is in every query, I found that it is about 15G and about 3 million records. That's not too big, nor should it be a source of contention for poor performance.It can. 15G / 3M rows gives 5M per row. Quite unusual. Definitely it is bad for full table scans.
Is 15G only rows data or rows and LOBs data?
If 5M per row is not a case, you should find out if the table has too much free space in extents. It may happen after direct loads or insert /*+append*/ operations new extents above HWM are created each time. When loads done frequently (and with parallelism), for example as daily load, it may cause many sparsely populated extents. Use DBMS_SPACE for this research.
Looking deeper, I found it had about 2 million chained rows (according to DBA_TABLES). But when I did as SB suggested and analyzed the table listing to CHAINED_ROWS table, there are really only about 1.some million chained rows.I would agree it is high. But can it really be improved? What is average row size?
That's still far too many in my opinion.
If it is more than roughly one block, inter-block chaining is not avoidable.
Also, regarding "bad" 500 col design, I'd say that having one table with 500 columns and 50% chained rows is better than having 2 tables with 250 col each and no chaining rows in the tables. In the latter case you will duplicate PK, and "chain" rows anyway in queries that combine these tables. Internal chain concatenation is more efficient than SQL joining via index.
BTW: I've found the need for some bit-map indexes on very low cardinality columns which I think may be a silver bullet finding.be careful here. bitmap indexes are good for queries, but are evil for DMLs.
The application code is what it is, and there isn't much I can do to change it because it is not our codeOracle has some query and plan substitution features that may help.
It is automatic making bind variables from literals (CURSOR_SHARING), stored outlines and Plan Stability.
And, most indexes have not been rebuilt since inception, but interestingly, they have a script that runs gather_index_stats on all the indexes (but it is not a dynamic SQL script and doesn't get the newer indexes that have been added).Oracle (starting from 10g) gathers stats automatically.
Edited by: user11181920 on Nov 29, 2012 3:56 PM
Yes, the table contains several clob fields (approx. 20) which I'm assuming is used for large text fields, and attaching documents to a ticket, etc.
Is 15G only rows data or rows and LOBs data? If 5M per row is not a case, you should find out if the table has too much free space in extents. It may happen after direct loads or insert /*+append*/ operations new extents above HWM are created each time. When loads done frequently (and with parallelism), for example as daily load, it may cause many sparsely populated extents. Use DBMS_SPACE for this research.
Average space per row is '1933' - I'm assuming that is in bytes, so about 2M each row.
If I did the math correctly, that would mean:
select sum(1933*3000000)/1024/1024/1024 from dual; = 5.4 Gb
Block size is 8K so that equates to about 3 rows per block (leaving some free space).
Interesting point about "Internal chain concatenation is more efficient than SQL joining via index.".
I've never had to deal with this type of intra-chaining before, so I'm not that familiar with its characteristics.
Funny thing is that this is the main table of the entire database and application, and it does not have a primary key, (only a few check constraints).
Yeah, I'm aware about SQL Profiling and I was playing around with it a little, but after creating the profile and applying it, it seemed to perform worse.
I created it using SQL Advisor in OEM (DB Console).
Regarding stats: yes, I'm fully aware, but apparently it isn't working correctly.
I've checked dba_scheduler_jobs and it says it has been running successfully, but in fact, it is NOT gathering stats.
For exampl;e: this particular table shows as having 5Million records in DBA_Tables, and last_analyzed is Oct. 7th, but in reality, I counted the records and it is just a little over 3Million.
That's a new problem for me to start looking at. Meanwhile, I figured I would write a simple script to gather_schema_stats.
Also, they have never gathered system_stats either. So that is next during our peak period usage tomorrow.
I appreciate all your ideas. Thx...