Start a new topic

tablespace datafiles

[This topic is migrated from our old forums. The original author name has been removed]

Just a comment about using the tablespace datafiles tab, Extremely slow, about 10 minutes before it returned list of datafiles, in window always shows me following error. An error occurred while performing the operation: Your connection with the database server has been interrupted! Please to re-establish the connection.

[This reply is migrated from our old forums.]

Re: tablespace datafiles
Hi, The query executed for this is: SELECT d.file_name "Name", v.status "Status", d.tablespace_name "Tablespace", ROUND(NVL(d.bytes / 1024 / 1024, 0), 2) "Size MB", ROUND(NVL((d.bytes - NVL(s.bytes, 0))/1024/1024, 0), 2) "Used MB", ROUND(NVL((d.bytes - NVL(s.bytes, 0)) / d.bytes * 100, 0), 2) "Used %", NVL(d.autoextensible, 'NO') "Auto Extensible" FROM sys.dba_data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id) s, sys.dba_tablespaces ts WHERE s.file_id (+)= d.file_id AND d.file_name = v.name AND d.tablespace_name = ts.tablespace_name AND ts.tablespace_name like '%' AND ts.contents like 'PERMANENT' ORDER BY 2,1; Hopefully looking at the query helps you figure out why it runs so slowly with your database. If you see a way to rewrite it to perform better, we would appreciate your suggestions. In my test database it completes within a few seconds. The error message is most likely a side-effect of the long execution time. By default, DbVisualizer assumes that the connection has been dropped if a query that is executed to fill a Object View tab takes more than 60 seconds. You can increase this time-out value in Tool Properties, in the Database Connection category under the General tab. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: tablespace datafiles
Hi Hans, this qry looks very familiar .... I made a sql profile (need sql tuning pack for this :-( ) for this qry and now it runs in 0,5 instead of in 130 seconds. (175 datafiles) In an other database it took 380 seconds for 450 datafiles. With the profile it's about a second... This won't help someone without the tuning pack but with a handfull of good hints it should be possible to get the same. Point is finding them ... Sorry I can not give real help on this at the moment, Ronald.
[This reply is migrated from our old forums.]

Re: tablespace datafiles
Hi Ronald, Thanks for your input. I'm not familiar with the "tuning pack." Is it just needed to find out how to optimize the query, or is it also needed to get it to run more efficiently? When you have a chance to suggest some changes to make it perform faster, we would really appreciate it. Best Regards, Hans
[This reply is migrated from our old forums.]

Re: tablespace datafiles
Hi drhammster, Can you please compare the execution time for this query with the one I posted earlier. NOTE! Use SQL -> Execute Buffer (or the corresponding toolbar button) to execute each query so that the optimizer hints are not stripped off by the editor: SELECT /*+ OPT_ESTIMATE(@"SEL$13", JOIN, ("FI"@"SEL$13", "F"@"SEL$13"), SCALE_ROWS=279.94) OPT_ESTIMATE(@"SEL$13", JOIN, ("FI"@"SEL$13", "F"@"SEL$13", "TS"@"SEL$13"), SCALE_ROWS=301.0673427) OPT_ESTIMATE(@"SEL$2134B70D", JOIN, ("TS"@"SEL$16", "S"@"SEL$1", "D"@"SEL$1"), SCALE_ROWS=0.753998696) OPT_ESTIMATE(@"SEL$2134B70D", TABLE, "TS"@"SEL$16", SCALE_ROWS=108.9290323) OPTIMIZER_FEATURES_ENABLE(default) IGNORE_OPTIM_EMBEDDED_HINTS ALL_ROWS OPT_ESTIMATE(@"SEL$2134B70D", JOIN, ("TS"@"SEL$16", "D"@"SEL$1"), SCALE_ROWS=211.0970149) */ d.file_name "Name", v.status "Status", d.tablespace_name "Tablespace", ROUND(NVL(d.bytes / 1024 / 1024, 0), 2) "Size MB", ROUND(NVL((d.bytes - NVL(s.bytes, 0))/1024/1024, 0), 2) "Used MB", ROUND(NVL((d.bytes - NVL(s.bytes, 0)) / d.bytes * 100, 0), 2) "Used %", NVL(d.autoextensible, 'NO') "Auto Extensible" FROM sys.dba_data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id) s, sys.dba_tablespaces ts WHERE s.file_id (+)= d.file_id AND d.file_name = v.name AND d.tablespace_name = ts.tablespace_name AND ts.tablespace_name like '%' AND ts.contents like 'PERMANENT' If this version performs better for you, we will replace the current query in the profile. Kind Regards, Hans
[This reply is migrated from our old forums.]

Re: tablespace datafiles
Hi, This has been fixed in the latest 7.0.1 version. http://www.dbvis.com/products/dbvis/doc/relnotes/index.jsp Best Regards Roger