Oracle database optimization 10 or 11 g

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Fri, 22 Jun 2012 2:58 pm

Good morning Guys okay?

Then work with delphi and oracle database and where I work has dealt with some pretty big bases and sometimes those get a little slow in some cases.

There are some processes that used to improve the process.And are listed below: [color = # 008040] -creates all the deletes of statistics to run [/color] [color = # 800000] SELECT ' ANALYZE TABLE ', TNAME, ' DELETE STATISTICS; ' FROM TAB WHERE TABTYPE = ' TABLE ' ORDER BY 1, 2, 3 [/color] [color = # 008040] --create the compute for tables [/color] [color = # 800000] SELECT ' ANALYZE TABLE ', TNAME, ' COMPUTE STATISTICS; ' FROM TAB WHERE TABTYPE = ' TABLE ' ORDER BY 1, 2, 3 [/color] [color = # 008040] --create the compute to the index [/color] [color = # 800000] SELECT ' ANALYZE INDEX ' INDEX_NAME COMPUTE STATISTICS, '; '
FROM USER_INDEXES ORDER BY TABLE_NAME, INDEX_NAME;
[/color]
[color = # 008040]--Does the Compute statistics of the entire schema.[/color]

[color = # 800000]EXEC DBMS_STATS.GATHER_SCHEMA_STATS (' USER ', CASCADE = > TRUE);[/color]


I wonder if anyone has some extra process.

Other than that, sometimes I come across tables without index or with a connection without index. I wonder if anyone knows any sql that would show me possible indexs it should be created because it would help in performance too.

Likely that any DBA knows that. I leave in their hands hehe.

Sincerely Skiche
skiche

Poston Fri, 22 Jun 2012 4:04 pm

Hello Skiche follows what is needed to check tables without indexes and without PK very useful to me.

Code: Select all
--> Tabelas SEM indices
select   OWNER,
  TABLE_NAME
from
(
select   OWNER,
  TABLE_NAME
from   dba_tables
minus
select   TABLE_OWNER,
  TABLE_NAME
from   dba_indexes
)
orasnap_noindex
where  OWNER = ('SCHEMA')
order   by OWNER,TABLE_NAME


--> TABELAS SEM PK
select  OWNER,
   TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and    OWNER not in ('SYS','SYSTEM')
order   by OWNER, TABLE_NAME
I hope I helped.

Abs.
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Fri, 22 Jun 2012 6:00 pm

Opa Madmax always helping.
Only a doubt, the first sql checks the index of relationships or only the table has no index?
Agradecidoo hugs.
skiche

Poston Mon, 25 Jun 2012 8:32 am

Skiche speaks the first checks for tables without index.

Abs..
madmax
Location: São Paulo

________________________________
Douglas - Madmax.

Poston Mon, 13 Aug 2012 4:31 pm

Skiche, there are many variants to define whether an index should be created on a particular column of a table. A lot of people creates indexes of type incorrect and often even those indexes are used by the optimizer of Oracle. Create indexes on PK, for example, is a good practice, and can improve performance in some queries and even DELETE CASCADE, so use the script that was passed by madmax without any fear. For other situations I suggest you study better what types of indexes that there are always several in Oracle and when really you must create them. I teach this and much more in my training of SQL Tuning: http://www.fabioprado.net/p/sql-tuning- ... racle.html [] s Fabio Prado http://www.fabioprado.net
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Mon, 18 Jan 2016 9:29 pm

Good evening everyone, I'll take the topic not to create another with a simple question.

When updating the statistics of Oracle 11 g, used for example: EXEC DBMS_UTILITY. ANALYZE_SCHEMA (' OWNER ', ' COMPUTE ');

Is there any risk? I mean, risk to affect anything in the logs, settings, and etc?

I'm new in Oracle, so yes, doubt can be a little beast. = But outside the slowness that can cause if there are too many connected users, there are some other risk?

In advance, thanks
ThiagolRamos

Poston Tue, 19 Jan 2016 9:19 am

James, I don't think it would be recommended to use of DBMS_UTILITY. ANALYZE_SCHEMA, because it seems to me an old routine from earlier versions of ORACLE (ex: 8i).

I would recommend instead the DBMS_STATS. Not necessarily you have to analyze 100% of records.

Code: Select all
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS
               (OWNNAME         => USER,   
                ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
I believe that the ANALYZE_SCHEMA only exists for compatibility reasons and may generate some negative impact on its future performance. I'vê had problems using ANALYZE TABLE and had to remove the statistics before re-creating the same again via DBMS_STAT.

At Sergio
stcoutinho
Location: Sao Paulo - SP

Poston Tue, 19 Jan 2016 10:16 am

stcoutinho wrote: James, I don't think it would be recommended to use of DBMS_UTILITY. ANALYZE_SCHEMA, because it seems to me an old routine from earlier versions of ORACLE (ex: 8i).

I would recommend instead the DBMS_STATS. Not necessarily you have to analyze 100% of records.

Code: Select all
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS
               (OWNNAME         => USER,   
                ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
I believe that the ANALYZE_SCHEMA only exists for compatibility reasons and may generate some negative impact on its future performance. I'vê had problems using ANALYZE TABLE and had to remove the statistics before re-creating the same again via DBMS_STAT.

At Sergio
Understood, in this case can I use EXEC DBMS_STATS. GATHER_SCHEMA_STATS (' OWNER ', estimate_percent = > 20);

Because I need to generate a schema, but in this case only will scan 20%, correct?
Outside the possible slowdown for users so there is no risk?

The previous command I used on a test basis, it worked normal. :D
ThiagolRamos

Poston Tue, 19 Jan 2016 10:48 am

James, I think the risk is at use both methods. Recommend that you use one or the other. If you want to change method, remove the statistics by the old method and apply the new method.

I think it would be more advisable to use DBMS_STATS ". AUTO_SAMPLE_SIZE " instead of a fixed percentage.

One other thing: instead of updating all the tables in the schema, you could for example generate statistics by groups of tables (ex: non-critical), running on different days and with different periodicity. For example, the criticism would be reviewed once every 15 days, the reviews every week, etc.

You can even rule out this analysis the registration tables with fixed number of records.

Hugs, Chris
stcoutinho
Location: Sao Paulo - SP

Poston Tue, 19 Jan 2016 2:13 pm

stcoutinho wrote: James, I think the risk is at use both methods. Recommend that you use one or the other. If you want to change method, remove the statistics by the old method and apply the new method.

I think it would be more advisable to use DBMS_STATS ". AUTO_SAMPLE_SIZE " instead of a fixed percentage.

One other thing: instead of updating all the tables in the schema, you could for example generate statistics by groups of tables (ex: non-critical), running on different days and with different periodicity. For example, the criticism would be reviewed once every 15 days, the reviews every week, etc.

You can even rule out this analysis the registration tables with fixed number of records.

Hugs, Chris
good afternoon, I have not used the 2. In case the: EXEC DBMS_UTILITY. ANALYZE_SCHEMA (' OWNER ', ' COMPUTE ');

I did on a test basis, just to check if it worked, but as you said, and I have researched that the Oracle recommends the use of this you suggested, the idea was to use it in the real base.

Now, I got another question, I'm not very experienced in Oracle as said, but I'm not too lazy to read and learn, so I intend to find more, just need some guidance in the material, and I am researching it.

The question is in relation to the " " schedule for days that you quoted, you can schedule it in the Oracle itself, right? Need to be through scripts or something?

Or in Enterprise Manager I can do?

Once again, sorry about the amount of questions:D
ThiagolRamos

Poston Tue, 19 Jan 2016 3:00 pm

James, You can do: A) A job that periodically autoexecuta (search DBMS_JOB);
http://www.experts-exchange.com/questio ... 2-0-1.html B) by Enterprise Manager (here you can create maintenance Windows). For OEM, take a look at this link: https://docs.oracle.com/database/121/TG ... m#TGSQL397 cheers, Sergio
stcoutinho
Location: Sao Paulo - SP



Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest