Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rass.

Asked: December 01, 2016 - 8:00 am UTC

Last updated: December 04, 2016 - 1:11 pm UTC

Version: Oracle 10g

Viewed 1000+ times

You Asked

Hi team,
I want to ask you some questions.
1. My office using oracle database. We have 9 schemas in one database. Each schemas having branches. Sometimes the database application running heavy so much. When I see the CPU usage of server database it's reach 100% makes the application running so much heavy. According to you team, what my office do in the database 9 schemas in one database is recomended or not ? exaclty oracle can handle to like make situation or not ? if no what is the solution? make the database processor more high or what?

2. When the application is heavy. I want to see and compare the load of all schemas. Which schema having so much load and which query is that. Can I do that ? if yes by which way i can do it ?

I wrote also my question on stackoverflow. But no answer that I got. I hope by asking from here directly i got the best solution for my database performance problem. Thanks for your attention.

and Connor said...

1) There is nothing wrong with multiple schemas in a single database. On apex.oracle.com we have thousands of them. But whether you have 1 schema, or 9 schemas, or 90000 schemas, obviously if the total amount of work being done exceeds the capacity of the hardware...then you have a problem.

2) You can use Statspack, or AWR (if you are licensed for it) to collect information about your system and report on it. A quick and easy way is to look at the most expensive SQL's on your system, eg

select *
from v$sql
where buffer_gets > 1000000
or disk_reads > 100000
or executions > 50000

(adjust the numbers to suit your system)

and track them back to the schema objects that are being referenced.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Database Performance Problem Review

Rass rass, December 02, 2016 - 3:50 am UTC

Hi Connor McDonald,

Thank you so much for your reply. Especially your quick and easy way to look at the most expensive SQL's on my system. It's so wonderful that's what I need. It's like a charm.

I want to ask more. For case no one(1) about hardware. Do you have any idea about the specification on which parts of hardware that must be higher priority than hard-disk for Oracle Database. Is it on memory or processor or all ?

As info. My database hard-disk space is remain 78 GB from 350 GB. Memory 64 GB and Processor E5606 @ 2.13 GHZ 4 CPU.




Connor McDonald
December 04, 2016 - 1:09 pm UTC

Very much depends on the requirement.

If you have a data warehouse, then I/O bandwidth is critical. For OLTP, then latency perhaps is more important.

I like to think of it this way - if you can't max out your CPU, then you have an imbalance somewhere.

More details

A reader, December 02, 2016 - 7:30 am UTC

Q1: what do you mean by "and track them back to the schema objects that are being referenced. "?

Q2: say the qry returns 1 sql Id having gets more than 1000000. And say this sqlid figures in AWR
How investigate on AWR report to find out the reasons of this sql qry slowness? Taking more than 2 hours whereas yesterday finished in 5 min!
Connor McDonald
December 04, 2016 - 1:11 pm UTC

1) Well, if a query says "select * from abc.x" I know it references the ABC schema. And I can look at PARSING_USER_ID as well to see where it cames from.

2) Check the children for that SQL_ID in V$SQL and check the plans for each.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.