Home » SQL & PL/SQL » Client Tools » Can you help me to improve my database? (oracle 10g ,10.2.0.1.0)
Can you help me to improve my database? [message #311798] Sun, 06 April 2008 21:59 Go to next message
bookiant
Messages: 7
Registered: March 2008
Junior Member
I checks my database by TOAD's DATABASE HEALTH CHECK. This is the report,but I don't know how to improve it,can you help me ? Thanks.

********************************************************************
*** ZHONGKE 2008-4-7 10:40:13 ***
********************************************************************
~Time to Connect : 0 seconds.

~Database Version : 10.2.0.1.0
~Database Up Since : 07:29:58 上午, 2月 16 2008
~Statistics Level : TYPICAL
~Undo Management : AUTO
~Buffer Cache Hit Ratio : 99.9945
~Library Cache Miss Ratio : 0.6186
~Dictionary Cache Miss Ratio : 1.1711

[Shared Pool Usage] Exec Time 0 seconds
~ Total Mb Unused : 30.04
~ Total Mb Used : 177.96
~ Total Mb : 208
~ Shared Pool Percent Used : 85.56


[Archive Log Mode Info] Exec Time 0 seconds
! Archiver : STOPPED
! Log Mode : NOARCHIVELOG
! log_archive_start (init.ora param) = FALSE

[Archive Log Info] Exec Time 0 seconds
~ Average Log Switches Per Day : 2.55
~ Hard Drive Storage (in Mb) for this many archive logs : 127.63


! Error using UTL_FILE to examine alert log!
! Error using UTL_FILE
! Possible causes:
! 1) You don't have privileges to execute the UTL_FILE package.
! To verify, try excuting this pl/sql block in Editor.
! If you don't have the required privileges, it will give an error.

declare
afile utl_file.file_type;
begin
if utl_file.is_open(afile) then
null;
end if;
end;

! 2) UTL_FILE_DIR initialization parameter does not include one of these lines:
! utl_file_dir=/home/oracle/admin/zhongke/bdump
! utl_file_dir=*
! To verify, look under DBA -> Oracle Parameters
! 3) alert.log file name is not among the following:
! (if this is the case please inform Quest support)
! alert_zhongke.log
! zhongkealrt.log
! 4) No directory exists in the database for the OS path: /home/oracle/admin/zhongke/bdump,
! or directory exists but privileges have not been granted for it.
! Execute these SQL Statements to remedy:

CREATE OR REPLACE DIRECTORY TOAD_BDUMP_DIR as '/home/oracle/admin/zhongke/bdump';
GRANT READ, WRITE on DIRECTORY TOAD_BDUMP_DIR to <oracle user executing health check>;


[Redo Log Group Sizes and Quantities] Exec Time 0 seconds
~ Number of Log Groups : 3
! Number of Members per Log Group: 1
~ All redo log members are 50M in size.

[Rollback Segments with wait ratios > 1 %] Exec Time 0 seconds
~ None


[Objects with Mixed-Case Names] Exec Time 1 seconds
! Synonym PUBLIC.NameFromLastDDL


[Tables with > 5 % chained rows and > 500 total rows] Exec Time 0 seconds
~ None


[Table Partitions with > 5 % chained rows and > 500 total rows] Exec Time 0 seconds
~ None


[Segments with < 10% of extents remaining and (maxextents > 1)] Exec Time 0 seconds
~ None


[Segments with > 100 extents] Exec Time 0 seconds
~ None


[Objects which can't extend because there is not
enough room in the tablespace] Exec Time 0 seconds
~ None


[Jobs] Exec Time 0 seconds
~ None


[redundant object privs with conflicting grant option] Exec Time 0 seconds
~ None


[Profiles that are not granted to any user] Exec Time 0 seconds
~ None


[FK's w/o Matching Indexes (or an unusable one)] Exec Time 0 seconds
~ None


[Tablespace Fragmentation] Exec Time 0 seconds
~ A common set of extent sizes is a good way to reduce tablespace fragmentation.
~ Sometimes fragmentation can be improved with 'Alter tablespace <tblspace> coalesce'.
~ Tablespace fragmentation should not be a problem if you use locally managed tablespaces.
~

======================================================================
== TableSpace Name % # of # of ==
== Fragmented Extents Holes ==
======================================================================
! BOOK 83 116 562


[Tablespaces with less than 10% free space] Exec Time 0 seconds
~ None


Re: Can you help me to improve my database? [message #311801 is a reply to message #311798] Sun, 06 April 2008 22:21 Go to previous messageGo to next message
bookiant
Messages: 7
Registered: March 2008
Junior Member
I'v started archivelog.Now TOAD's report is :

********************************************************************
*** ZHONGKE 2008-4-7 11:16:02 ***
********************************************************************
~Time to Connect : 0 seconds.

~Database Version : 10.2.0.1.0
~Database Up Since : 10:47:40 上午, 4月 7 2008
~Statistics Level : TYPICAL
~Undo Management : AUTO
~Buffer Cache Hit Ratio : 98.6441
~Library Cache Miss Ratio : 0.1372
!Dictionary Cache Miss Ratio : 19.7976

[Shared Pool Usage] Exec Time 0 seconds
~ Total Mb Unused : 194.02
~ Total Mb Used : 77.98
~ Total Mb : 272
~ Shared Pool Percent Used : 28.67


[Archive Log Mode Info] Exec Time 0 seconds
~ Archiver : STARTED
~ Log Mode : ARCHIVELOG
! log_archive_start (init.ora param) = FALSE

[Archive Log Info] Exec Time 0 seconds
~ Average Log Switches Per Day : 2.54
~ Hard Drive Storage (in Mb) for this many archive logs : 126.79


! Error using UTL_FILE to examine alert log!
! Error using UTL_FILE
! Possible causes:
! 1) You don't have privileges to execute the UTL_FILE package.
! To verify, try excuting this pl/sql block in Editor.
! If you don't have the required privileges, it will give an error.

declare
afile utl_file.file_type;
begin
if utl_file.is_open(afile) then
null;
end if;
end;

! 2) UTL_FILE_DIR initialization parameter does not include one of these lines:
! utl_file_dir=/home/oracle/admin/zhongke/bdump
! utl_file_dir=*
! To verify, look under DBA -> Oracle Parameters
! 3) alert.log file name is not among the following:
! (if this is the case please inform Quest support)
! alert_zhongke.log
! zhongkealrt.log
! 4) No directory exists in the database for the OS path: /home/oracle/admin/zhongke/bdump,
! or directory exists but privileges have not been granted for it.
! Execute these SQL Statements to remedy:

CREATE OR REPLACE DIRECTORY TOAD_BDUMP_DIR as '/home/oracle/admin/zhongke/bdump';
GRANT READ, WRITE on DIRECTORY TOAD_BDUMP_DIR to <oracle user executing health check>;


[Redo Log Group Sizes and Quantities] Exec Time 0 seconds
~ Number of Log Groups : 3
! Number of Members per Log Group: 1
~ All redo log members are 50M in size.

[Rollback Segments with wait ratios > 1 %] Exec Time 1 seconds
~ None


[Objects with Mixed-Case Names] Exec Time 2 seconds
! Synonym PUBLIC.NameFromLastDDL


[Tables with > 5 % chained rows and > 500 total rows] Exec Time 0 seconds
~ None


[Table Partitions with > 5 % chained rows and > 500 total rows] Exec Time 0 seconds
~ None


[Segments with < 10% of extents remaining and (maxextents > 1)] Exec Time 0 seconds
~ None


[Segments with > 100 extents] Exec Time 0 seconds
~ None


[Objects which can't extend because there is not
enough room in the tablespace] Exec Time 1 seconds
~ None


[Jobs] Exec Time 0 seconds
~ None


[redundant object privs with conflicting grant option] Exec Time 0 seconds
~ None


[Profiles that are not granted to any user] Exec Time 0 seconds
~ None


[FK's w/o Matching Indexes (or an unusable one)] Exec Time 1 seconds
~ None


[Tablespace Fragmentation] Exec Time 0 seconds
~ A common set of extent sizes is a good way to reduce tablespace fragmentation.
~ Sometimes fragmentation can be improved with 'Alter tablespace <tblspace> coalesce'.
~ Tablespace fragmentation should not be a problem if you use locally managed tablespaces.
~

======================================================================
== TableSpace Name % # of # of ==
== Fragmented Extents Holes ==
======================================================================
! BOOK 83 116 562


[Tablespaces with less than 10% free space] Exec Time 0 seconds
~ None
Re: Can you help me to improve my database? [message #311827 is a reply to message #311798] Mon, 07 April 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I checks my database by TOAD's DATABASE HEALTH CHECK. This is the report,but I don't know how to improve it,can you help me ?

Don't run this, this is bullshit, and hire a DBA.

Regards
Michel
Re: Can you help me to improve my database? [message #311934 is a reply to message #311827] Mon, 07 April 2008 07:56 Go to previous messageGo to next message
bookiant
Messages: 7
Registered: March 2008
Junior Member
Thank you. I am try to be a DBA. Embarassed

[Updated on: Mon, 07 April 2008 07:59]

Report message to a moderator

Re: Can you help me to improve my database? [message #311941 is a reply to message #311934] Mon, 07 April 2008 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So stop using TOAD to get a healthcheck and start reading the doc.
See How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Can you help me to improve my database? [message #312597 is a reply to message #311941] Wed, 09 April 2008 07:00 Go to previous messageGo to next message
bookiant
Messages: 7
Registered: March 2008
Junior Member
Thanks to Regards Michel for yor help.
Re: Can you help me to improve my database? [message #502163 is a reply to message #311941] Tue, 05 April 2011 01:04 Go to previous messageGo to next message
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
Senior Member
Hi Michel,

Why you suggestion to stop use TOAD tool?
Please tell me what is wrong?
Re: Can you help me to improve my database? [message #502170 is a reply to message #502163] Tue, 05 April 2011 02:20 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Buggy
Always late of at leat one version
Most of those that use it do not understand how Oracle works
Undefined KPI
Those that use it do not understand what it says
Open and left some pending transactions
...


Regards
Michel
Previous Topic: wait tab in toad
Next Topic: how to execute stored Procedure with cursor with in pl/sql
Goto Forum:
  


Current Time: Fri Dec 09 04:05:03 CST 2016

Total time taken to generate the page: 0.18235 seconds